BigQuery

​

Concepts

​

BigQuery

​

BigQuery data source: usage in your BigQuery tables

​

Data project and billing project: where usage lives and who pays

​

Datasets: where Zenskar looks for tables

​

Fully qualified table names

SELECT COUNT(*)
FROM `my-data-project.my_dataset.my_table`

​

Service accounts: how Zenskar reads BigQuery

​

How-to guides

​

Prepare Google Cloud

​

Prerequisites

• You need the IDs of the data project and the billing project. Use the project IDs, not the project names. A project ID is unique and permanent. A project name is a display name that can change. Use one project for both unless the query costs should go to another project. See Data project and billing project.
• You need Google Cloud permissions to create a service account and its key, and to grant roles on both projects. This task needs no Zenskar permission.
• Your organization must allow service account keys. The iam.disableServiceAccountKeyCreation policy blocks them, and Google enforces it by default for organizations created on or after May 3, 2024. An organization policy administrator can exempt a project. See Create and delete service account keys.

​

Enable the BigQuery API

1. In the Google Cloud console, select the billing project.
2. Enable the BigQuery API. See Enable the BigQuery API.

​

Create a service account

1. Open the Create service account page, and select the billing project.
2. Enter a service account name.
3. Click Done.
4. Copy the email address of the service account. You need it to grant the roles.

​

Grant the roles

1. Go to the IAM page, and select the billing project.
2. Click Grant Access.
3. Enter the email address of the service account.
4. In Select a role, select BigQuery Job User.
5. Click Save.

1. Go to the BigQuery page.
2. In the Explorer pane, expand the data project, click Datasets, and select the dataset.
3. Click Sharing > Permissions.
4. Click Add principal.
5. In New principals, enter the email address of the service account.
6. In Select a role, select BigQuery Data Viewer.
7. Click Save.

​

Create a JSON key

1. Go to the Service accounts page, and select the billing project.
2. Click the email address of the service account.
3. Click the Keys tab.
4. Click Add key > Create new key.
5. In Key type, select JSON, and click Create.

​

Test the service account

gcloud auth activate-service-account --key-file=KEY_FILE
bq --project_id=BILLING_PROJECT_ID query --nouse_legacy_sql \
  'SELECT COUNT(*) FROM `DATA_PROJECT_ID.DATASET.TABLE`'

​

Add a BigQuery data source

​

Prerequisites

• You need a role with the Can Create Data Source and Can Read Data Source permissions. See Permissions.
• You need the IDs of the data project and the billing project, the name of the dataset, and the JSON key file of the service account. See Prepare Google Cloud.

​

Steps

1. Go to Usage > Data Sources.
2. Click ADD DATA SOURCE. The Add New Data Source page opens.
3. Under Set Up Source, in Source Name, enter a name. The name can contain letters, digits, underscores, hyphens, and spaces.
4. In Source Type, select BigQuery.
5. Under Source Config, fill in the fields:
   • In Project ID, enter the ID of the data project.
   • In Billing Project ID, enter the ID of the billing project. For one project, enter the same ID as in Project ID.
   • In Dataset ID, enter the name of the dataset, without the project.
   • Optional: in Location, enter the location of the dataset, such as US or EU.
   • In Credentials JSON, paste the whole contents of the key file.
6. Under Source Access, keep Directly from Source. It is the only option for BigQuery.
7. Click SAVE SOURCE.

​

Fix a failed save

• Zenskar does not save the data source when Project ID, Billing Project ID, or Dataset ID is empty. Fill in each marked field.
• Zenskar does not save the data source when Credentials JSON is empty or does not hold a JSON object. Paste the whole contents of the key file.

​

Write a billable-metric query for a BigQuery data source

​

Prerequisites

• To create a billable metric, you need a role with the Can Create Billable Metric and Can Read Billable Metric permissions. To change one, you need Can Update Billable Metric and Can Read Billable Metric. See Permissions.
• The BigQuery data source must exist. See Add a BigQuery data source.

​

Write the query

1. Open a new or existing billable metric in the SQL builder. See Define billable metrics.
2. Under SOURCE OF DATA, select Remote Data. In Remote Datasource, select the BigQuery data source.
3. In Data Navigator, hover over a table and click its copy icon. Paste the name into the query.
4. Check the pasted name. When the billing project differs from the data project, the name must be a fully qualified table name. If it is not, add the data project and the dataset in front of it.
5. Write the rest of the query. The query returns a column named quantity.
6. Run the query for a customer and a period. See Define billable metrics.

SELECT COUNT(*) AS quantity
FROM `my-data-project.my_dataset.api_calls`
WHERE customer_id = {{customer.external_id}}
  AND DATE(timestamp) >= DATE({{start_date}})
  AND DATE(timestamp) <= DATE({{end_date}})

​

Fix a failed preview

• When the billing project differs from the data project, a query with a table name that is not fully qualified fails. Write each table name in full.
• When the service account is missing a role, the query fails. See Grant the roles.
• When a value of the BigQuery data source is wrong, every query fails. Examples are a typo in a project ID, or a dataset name with the project in front. Add a new BigQuery data source with the correct values.

​

Move to a separate billing project

​

Prerequisites

• You need a role with the Can Create Data Source, Can Read Data Source, Can Update Billable Metric, and Can Read Billable Metric permissions. See Permissions.
• The service account has BigQuery Job User on the new billing project. See Grant the roles.

​

Add the new data source

1. Add a BigQuery data source with the same Project ID and Dataset ID as the old one, and the new billing project in Billing Project ID. See Add a BigQuery data source.

​

Move each billable metric

1. Go to Usage > Billable Metrics.
2. Click a billable metric that queries the old data source. The billable metric opens in the editor.
3. In Remote Datasource, select the new BigQuery data source.
4. Write each table name in the query as a fully qualified table name.
5. Run the query for a customer and a period. See Define billable metrics.
6. Click SAVE CHANGES.

​

Check which project pays for the queries

​

Prerequisites

• The BigQuery data source has run a query in the last hour, for example in a billable-metric preview. See Define billable metrics.
• In Google Cloud, you need the BigQuery User and BigQuery Resource Viewer roles on the billing project and on the data project. This task needs no Zenskar permission.

​

Check the jobs

1. In the Google Cloud console, go to the BigQuery page, and select the billing project.
2. Run the following query. Replace region-us with the region of the dataset, such as region-eu.
   SELECT
     job_id,
     project_id,
     total_bytes_billed,
     ARRAY(
       SELECT FORMAT('%s.%s.%s', t.project_id, t.dataset_id, t.table_id)
       FROM UNNEST(referenced_tables) AS t
     ) AS tables_read,
     creation_time
   FROM `region-us`.INFORMATION_SCHEMA.JOBS
   WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
     AND job_type = 'QUERY'
   ORDER BY creation_time DESC
   
3. Find the row whose tables_read names the table of the billable metric. In that row, project_id is the billing project.
4. Select the data project, and run the same query. The job is not listed.

​

Reference

​

Add New Data Source page

​

Set Up Source

​

Source Config

​

Source Access

​

Actions

​

Google Cloud roles

​

Permissions

​

API reference

​

Troubleshooting