BigQuery

Connect Codatum to BigQuery to run SQL and manage data. For an overview of connections in general, see Connection.

Preparing BigQuery

Prepare a service account and service account key, and grant it the roles (or equivalent permissions) it needs on the target project and dataset.

Grant on	Role (or equivalent permission)
Project	BigQuery Job User (or bigquery.jobs.create)
Project	BigQuery Read Session User (or bigquery.readsessions.create / getData / update)
Dataset	BigQuery Data Viewer (or bigquery.tables.getData / bigquery.datasets.get / bigquery.tables.get / bigquery.tables.list)

(Optional) To sync dataset and table information without entering a project ID, grant resourcemanager.projects.get on the target project.

Note the target Project ID for later use.

Configuring Codatum

1. Open global nav > Workspace settings > Data management > Connections, then select New connection.
2. Select Google BigQuery.
3. Enter a Connection name.
4. Select an Access level.
5. Upload the service account key under JSON key file > File upload (Client Email is filled in automatically from the JSON).
6. Enter the Project ID (the JSON's project_id might be filled in as a default; you can change it manually).
7. (Optional) Select a Location (Location).
8. Run Test connect, then save the connection.
9. On the sync targets screen shown after saving, select the datasets to sync (Table metadata sync). You can also skip this step.

Location

When you specify a Location for a connection, SQL that runs on the connection runs in that location.

TIP

We recommend explicitly specifying the location so that SQL doesn't run in an unintended location. If your data is in multiple locations (including replicas), create a connection for each location. If you use the following features to run SQL across multiple locations on one connection, Codatum's cache optimization might not work correctly and can cause errors.

• Cross-region dataset replication
• Global queries

Values you can select

The default is Not specified (auto).

Value	Where SQL runs
Not specified (auto)	BigQuery determines the location from the tables that the SQL references.
US (multi-region)	US
asia-northeast1 (Tokyo)	asia-northeast1
asia-northeast2 (Osaka)	asia-northeast2
asia-northeast3 (Seoul)	asia-northeast3
asia-east1 (Taiwan)	asia-east1
asia-southeast1 (Singapore)	asia-southeast1
australia-southeast1 (Sydney)	australia-southeast1
us-central1 (Iowa)	us-central1
us-east1 (South Carolina)	us-east1
us-east4 (Northern Virginia)	us-east4
us-west1 (Oregon)	us-west1

Table metadata sync

When you specify a location, table metadata sync works as follows.

• A dataset that doesn't exist in the connection's location (including replicas) causes a sync error. Remove it from the sync targets.
  • If the service account is granted BigQuery Data Viewer (or bigquery.datasets.get) on the project, the sync targets screen also shows the locations of dataset replicas.
• When Auto add datasets is enabled, only datasets confirmed to exist in the connection's location are added.

If a location-related error occurs

When you run SQL, you might see errors like the following.

• Access Denied: Project <project>: User does not have bigquery.jobs.createGlobalQuery permission
• Not found: Dataset <project>:<dataset> was not found in location <location>

The first error looks like a permission error, but it's usually caused by a location mismatch between where the SQL runs and a dataset that the SQL references (or the result of another SQL block). When one SQL references data in different locations, BigQuery tries to run it as a global query, which causes this error. Instead of granting the permission, check the following in order.

1. Make sure the dataset and project names in the SQL are correct and that the dataset exists.
2. Check the locations of the datasets that the SQL references. Keep the datasets that one SQL references in the same location (including replicas).
3. Specify a location for the connection, or create a connection for each location.
4. If the error occurs only in SQL that references other SQL blocks, check whether Run with latest data, which doesn't use the cache, resolves it. Even if it does, the settings in step 3 prevent the error from recurring.