BigQuery

Connect to BigQuery.

Creating a BigQuery Service Account

You must have Google Cloud admin permissions to create a service account. Google has documentation on creating a service account and generating a private key.

1. Open the credentials page in the Google Cloud Platform API Manager and, if necessary, select your project:

   

2. Click CREATE CREDENTIALS and choose Service account:

   

3. Enter a name [Omni BigQuery Service Account] and a description [Omni BigQuery Connection] for the new service account, and click CREATE AND CONTINUE:

   

4. Your service account requires two Google BigQuery predefined roles:

5. BigQuery > BigQuery Job User

6. BigQuery > BigQuery Data Viewer

7. Select the first role in the Select a role field, then click ADD ANOTHER ROLE and select the second role:

   

8. After selecting both roles, click CONTINUE:

   

9. Select Done

10. Select the Service Account in actions Edit service account

    

11. Select KEYS

    

12. Click ADD KEY:

    

13. Select JSON and click CREATE:

    

14. The JSON key will be saved to your computer. NOTE WHERE IT IS SAVED. After noting the download location, click CLOSE:

    

Creating the Connection in Omni

1. In Omni, to add BigQuery as a connection:
   1. Navigate to the Admin tab.
   2. Select Connections menu option on the left hand panel
   3. Select the Add Connection button in the top right
   4. Select the BigQuery button
   

Use this image to follow along with the next steps:

2. Display Name is a required field. This name will appear will appear in the Omni Connections list.

3. Default Dataset is a required field. This should reference the default dataset in your BigQuery database that Omni will connect to.

   1. To find a dataset name in the BigQuery console...

      1. Navigate to your BigQuery console

         

      2. Select the Project your dataset resides in - you can toggle between Projects in the top middle drop-down menu

         

      3. Select a dataset and the info will appear in the “Dataset info” menu. In this example, the name is products1

         

4. Include Other Projects is an optional field. If you want to include multiple projects, input each additional project’s name into a comma-separated list in this field.

5. Include Schemas is an optional field that allows you to limit which datasets to allow Omni to access within the BigQuery project.

   1. List the datasets you want included in a comma-separated list.
   2. If none are listed, Omni will be able to access all schemas within the listed dataset(s).
   3. If you included a secondary project in step 3, be sure to reference the secondary project and the dataset separated by a period, e.g. other_project.dataset.

6. Service Account JSON is a required field.

   1. Upload the JSON file that was downloaded at the end of the Creating a BigQuery Service Account​ section above.

7. Region and Database Timezone are required fields. To find these in BigQuery…

   
   1. Navigate to your BigQuery console
   2. Select the Project your dataset resides in
   3. Select your dataset
   4. Navigate to the “DETAILS” tab
      1. “Data Location” shows the dataset’s region
      2. “Last modified” shows the the dataset’s timezone

8. Query Timezone is a required field.

   1. The default value is “Do not convert”
   2. Otherwise, select the Timezone you want queries to be converted to.

9. Select “Create Connection” and voila! You can see your new connection in action by heading over to the “Make a workbook” or “Browse Model” options in the “Woo hoo!” success modal in the top right.