Skip to main content

Connecting Google BigQuery to Omni

In this guide, you'll learn how to connect your Google BigQuery database to Omni.

Requirements​

To follow the steps in this guide, you'll need:

  • Organization Admin permissions in Omni

  • Google Cloud admin permissions, which are required to create a service account and private key.

  • The following details about the default dataset in your BigQuery project:

    • The name of the default dataset
    • Its data location
    • Its timezone

    Need help finding the dataset?

    1. Navigate to your BigQuery console.

    2. Select the project your dataset resides in. Use the top middle drop-down menu to toggle between projects.

    3. Select a dataset and the info will appear in the Dataset info menu. In this example:

      • The dataset name is products
      • The Data location is US
      • The timezone is UTC-7, as indicated in the Last modified field

Step 1: Create a BigQuery service account​

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

  2. Click Create credentials, located near the top center of the page, then Service account.

  3. In the Service account details section:

    1. Enter a name and description for the new service account, such as Omni BigQuery.
    2. Click Create and continue.

  4. In the Grant this service account access to project section, grant the service account access to the following BigQuery roles:

    • BigQuery Job User
    • BigQuery Data Viewer

  5. After adding both roles, click Done. You'll be redirected to the Credentials page.

Step 2: Create a service account private key​

  1. In the Service Accounts section, click the service account you just created in Step 1.
  2. On the service account details page, click the Keys tab near the top of the page.
  3. Click Add Key > Create new key.
  4. When prompted, select JSON as the key type and then click Create:
  5. The JSON key will be saved to your computer. After noting where the key was downloaded, click Close.

Step 3: Create the connection in Omni​

  1. In Omni, click Settings > Connections.

  2. Click Add connection.

  3. Click BigQuery.

  1. Fill in the fields as follows:

    • Display name - Required. Enter a name for the connection, which will display in the connections list in Omni.

    • Default dataset - To include multiple projects, enter the names of the projects as a comma-separated list. For example, project_2, project_3

    • Include schemas - To only include specific datasets, enter the names of the datasets as a comma-separated list. Leaving this field blank will allow Omni to access all datasets in the project.

    Note: If you specified projects in the Include other projects field, reference the projects and datasets using dot notation. For example: other_project.dataset

    • Service account JSON - Add the JSON private key you created in Step 2.

    • Dataset for CSV Upload - Enter the name of the Dataset where CSV uploads will be stored. If left blank, you'll still be able to upload CSVs, but they won't be pushed to the database.

    • Region - Required. Select the region the dataset resides in. This can be found in the BigQuery console, in the dataset's [Data location field]. Refer to the Requirements section if you need help finding this information.

    • Database timezone - Required. Select the timezone used by the dataset. This can be found in the BigQuery console, in the dataset's Last modified field. Refer to the Requirements section if you need help finding this information.

    • Query timezone - Required. If specified, data will be converted from the Database timezone to this timezone when querying. Refer to the Converting timezone data guide for more information.

    • Allow User-Specific Timezones - Check this option to allow individual users' Query timezone settings to be used as the query timezone for the connection.

    • Max Billing Bytes - Enter the maximum number of bytes that are allowed to be processed for a single query made against the connection. The default is 1 TB.

  2. When finished, click Create connection.

What's next?​

Now that BigQuery is set up, you can: