BigQuery

​

What you get

• A BigQuery catalog for each BigQuery project your Google account can see, up to 50, or exactly the projects your administrator chose. Each catalog is titled after its project, for example BigQuery (Acme Analytics · acme-analytics).
• SQL against any of those catalogs from a notebook or a session, with the project’s datasets, tables and columns in the notebook schema browser.
• Optionally, a semantic model built on a catalog, where every viewer queries with their own Google connection.

​

Prerequisites

• Google Cloud: permission to create an OAuth client and enable the BigQuery API in a Google Cloud project.
• Athena: the workspace owner or administrator role to register the client.
• Members: a Google account with BigQuery roles on the projects they will query (see Google Cloud roles).

​

Administrator: create the Google OAuth client

https://api.athenaintel.com/api/direct-connectors/bigquery_oauth_direct/oauth/callback

​

Administrator: register the client in Athena

​

Everyone: connect your Google account

1

Open the BigQuery card

Navigate to Integrations, find BigQuery under Data Warehousing & BI, and choose Google account.

2

Sign in with Google

Click Connect and sign in with Google in the popup.

3

Approve BigQuery access

Keep View and manage your data in Google BigQuery ticked and approve. Athena creates one BigQuery catalog per project you can see.

​

Google Cloud roles

• BigQuery Job User (roles/bigquery.jobUser) on the project the catalog runs in. Queries run as jobs in that project and bill to it.
• BigQuery Data Viewer (roles/bigquery.dataViewer) on the datasets to read, or on the project.
• BigQuery Data Editor (roles/bigquery.dataEditor) only where the member may change data.

​

Using the connection

• Notebooks: choose one of your BigQuery catalogs as a SQL cell’s connection. The schema browser lists the project’s datasets, tables and columns.
• Sessions: mention a BigQuery catalog with @ and ask a question. Athena writes GoogleSQL, runs it in the catalog’s project and shows the first 100 rows. Statements that change data are refused unless the session is allowed to write, and BigQuery refuses them unless your Google account may make the change.
• Semantic models: choose Create › Semantic Model and pick one of your BigQuery catalogs under Data Source. Everyone who opens the model queries it with their own Google connection, through their own BigQuery catalog for the same project, so their own Google Cloud permissions apply. A teammate who hasn’t connected BigQuery is asked to connect first. Your connection is never used for anyone else’s queries.
• Computers: on a computer with one of your BigQuery catalogs attached, the catalog’s project is in GOOGLE_CLOUD_PROJECT and athena-catalog-token prints a short-lived Google access token for your connection. The computer acts as you, so anyone you share it with can use your BigQuery access from it: attach a Google sign-in catalog only to a computer you don’t share, and use a service-account catalog on a shared one. Pass the token to the client explicitly:
  import os
  import subprocess
  from google.cloud import bigquery
  from google.oauth2.credentials import Credentials
  
  token = subprocess.check_output(
      ["athena-catalog-token", "bigquery_oauth_direct"], text=True
  ).strip()
  client = bigquery.Client(
      project=os.environ["GOOGLE_CLOUD_PROJECT"],
      credentials=Credentials(token),
  )
  
  athena-catalog-token returns the current token while it is fresh and gets a new one otherwise. With more than one BigQuery catalog attached, pass the catalog’s asset ID instead of bigquery_oauth_direct and set project= to that catalog’s own project ID.

​

Automation: connecting with a service account

gcloud iam service-accounts create athena-bigquery --project=PROJECT_ID
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:athena-bigquery@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"
bq query --use_legacy_sql=false --project_id=PROJECT_ID \
  'GRANT `roles/bigquery.dataViewer` ON SCHEMA `PROJECT_ID.DATASET`
   TO "serviceAccount:athena-bigquery@PROJECT_ID.iam.gserviceaccount.com"'
gcloud iam service-accounts keys create athena-bigquery.json \
  --iam-account=athena-bigquery@PROJECT_ID.iam.gserviceaccount.com

​

Known limits

• Google sign-in creates at most 50 catalogs per member unless your administrator limits the workspace to specific projects. Other projects stay reachable with fully-qualified table names.
• Each catalog runs its queries in one project.
• Catalogs cannot be shared. Teammates connect their own Google account, or you use a service account for shared work.
• A query that has not finished after about a minute is cancelled and reported as timed out. Filter on a partition column or select fewer columns, or run very long jobs in the BigQuery console.
• Other query limits and quotas are BigQuery’s.

​

Troubleshooting

​

Disconnecting