Google BigQuery

Covers one BigQuery dataset per connection, read with a service account that can only read. The gateway stops any query that would bill more than 1 GB.

Before you start

• A Google Cloud project with the dataset, and a person who can create a service account, grant it a role on the project and share the dataset (a project Owner can do all three).
• No entry on the gateway’s allowed hosts. The gateway talks only to Google’s BigQuery API.

Network access

The gateway connects out to Google’s APIs over HTTPS (bigquery.googleapis.com). Nothing needs to be opened.

Create a read-only login

Create a service account, let it run query jobs in the project, and give it BigQuery Data Viewer on the one dataset. Replace acme-analytics with your project and sales with the dataset.

gcloud iam service-accounts create portward-reader --project=acme-analytics \
  --display-name="Portward (read-only)"
gcloud projects add-iam-policy-binding acme-analytics --condition=None \
  --member=serviceAccount:portward-reader@acme-analytics.iam.gserviceaccount.com --role=roles/bigquery.jobUser

Then, in the BigQuery console’s query editor:

GRANT `roles/bigquery.dataViewer` ON SCHEMA `acme-analytics.sales`
TO "serviceAccount:portward-reader@acme-analytics.iam.gserviceaccount.com";

Give it no other role. Setup refuses credentials that can change a table, through the dataset or the project.

Setup needs the service account’s credentials, in one of two forms:

• A workload identity federation config (recommended). It holds no secret, and it works where your organization blocks service account keys. For a gateway on Azure with a managed identity, portward wif-config writes it. Run portward wif-config --help for its settings: your project number, the identity pool, its provider, the service account and the Azure application the managed identity signs in to. Google’s workload identity federation guide covers making the pool.
• A key file. gcloud iam service-accounts keys create portward-reader.json --iam-account=portward-reader@acme-analytics.iam.gserviceaccount.com. Keep the file only until you have pasted it in setup, then delete it.

Connect it in setup

Choose Google BigQuery and fill in Name (the name people and the AI see, such as sales), Project (with a key file, leave it empty to use the key’s project; with a workload identity config, type it), Dataset, Location (optional, such as US or europe-west2) and Credentials (the whole JSON file).

What the setup check tests

Before it saves, setup checks, in this order:

1. The credentials are a service account key or a workload identity federation config (“paste a service-account key or a workload identity federation config, not a user sign-in”).
2. The project, dataset and location are written correctly.
3. It can list the dataset’s tables (“no tables found in the dataset”).
4. The credentials can’t change any of those tables. Setup asks Google whether they hold bigquery.tables.updateData, bigquery.tables.update, bigquery.tables.delete or bigquery.tables.setIamPolicy. That covers grants on both the dataset and the project (“use a read-only service account (BigQuery Data Viewer)”).
5. A token computed inside BigQuery matches the gateway’s own (“in-database tokens don’t match Python”).

To suggest which columns to turn into tokens, setup reads up to 200 rows of the text columns with BigQuery’s table listing, which runs no query and bills nothing.