Skip to main content
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

    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
    BigQuery dataset info

​
Setup

1

Create a BigQuery service account & grant project access

  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
    Service account roles
  5. After adding both roles, click Done. You’ll be redirected to the Credentials page.
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: Key type prompt
  5. The JSON key will be saved to your computer. After noting where the key was downloaded, click Close. Key confirmation
3

Set up a table upload dataset

This step is optional. However, we recommend completing it as part of the initial set up or you won’t be able to use CSV uploads in joins.
In this step, you’ll create a dedicated dataset to use for table uploads. Before you create the dataset, note that:
  • This dataset can’t be used for other modeled tables
  • This dataset must be in the same region as other projects and schemas in the connection, or you may encounter cross-region errors
To create a dataset for table uploads:
  1. Navigate to your BigQuery project.
  2. Create the dataset.
  3. After the dataset is created, open it so that its details display.
  4. Click Sharing > Permissions, located next to the Copy option near the top right corner of the page.
  5. In the dialog that displays, click Add principal.
  6. Fill in the fields as follows:
    • New principals - Add the service account you created in step 1
    • Role - Add the BigQuery Data Editor role
    BigQuery dataset sharing
  7. Click Save.
4

Create the connection in Omni

  1. In Omni, click Settings > Connections.
  2. Click the BigQuery option.
  3. On the connection setup page, fill in the connection details:
    NameRequired?Description
    Display Name YesA user-friendly name for the connection, which will be used throughout Omni
    Default Dataset YesThe default dataset for the connection
    Include Other Projects NoA comma-separated list of other Google Cloud projects to include
    Include Datasets NoA comma-separated list of datasets to include in the connection
    Offloaded Datasets NoA comma-separated list of datasets to include in the connection, which will only load on demand. This setting is good for very large datasets or dbt dev datasets.
    Dataset for CSV Upload NoThe name of the dataset to use for table (CSV) uploads, which you created in step 3. If left blank, you can upload tables but they won’t be pushed to the database or be available for use in joins.
    Service Account JSON YesThe JSON private key you created in step 2
    Region YesThe region the dataset resides in. Refer to the Requirements section if you need help finding this information.
    Database Timezone YesThe timezone used by the dataset. Refer to the Requirements section if you need help finding this information.
    Query Timezone YesThe timezone to use for Omni queries. If a timezone is specified, data will be converted from the Database timezone to the selected timezone.
    Allow User-Specific Timezones NoAllow users to override the connection timezone with their own when querying
    Max Billing Bytes NoThe maximum bytes billed for queries run on this connection. Defaults to 1TB.
  4. When finished, click Create connection.

​
What’s next?

Now that your BigQuery is set up, you can: