Skip to content

bigquery.mdx

Latest commit

 

History

History
97 lines (65 loc) · 6.24 KB

bigquery.mdx

File metadata and controls

97 lines (65 loc) · 6.24 KB
Raw
title Google BigQuery
description Connect to Google BigQuery with Service Account, ADC, or OAuth auth

import RegistryPlugin from "/snippets/registry-plugin.mdx";

Use Application Default Credentials if gcloud is already on the machine. The other two methods are for machines where it is not: a service account key file, or a browser sign-in against an OAuth client you create. Everything travels over the REST API, so there is no host, port, or tunnel.

Quick setup

Click New Connection…, select BigQuery, pick an auth method, enter your Project ID, and click Save & Connect.

Connection settings

Field Required Notes
Auth Method Yes Service Account Key, Application Default Credentials, or Google Account (OAuth)
Project ID Yes The ID (my-project-123456), not the name or number
Service Account Key Service account only A .json key file path, or the raw JSON
OAuth Client ID, OAuth Client Secret OAuth only From GCP Console > APIs & Services > Credentials
OAuth Refresh Token No OAuth only. Tried before the token saved by sign-in
Location No Processing location (US, EU, us-central1), sent with every job
Max Bytes Billed No Options tab. A query over the cap fails instead of billing

There is no Database field and no URL scheme. Datasets stand in for databases: the sidebar lists them as expandable nodes, Cmd+K and Database > Open Database… move between them, and Database > New Database… runs CREATE SCHEMA. Switching does not move tabs you already have open; each stays on its own dataset. See Tabs.

Authentication

Application default credentials

gcloud auth application-default login --project=my-project

The driver reads ~/.config/gcloud/application_default_credentials.json, accepting the authorized_user, service_account, and impersonated_service_account types. The form then needs only a Project ID.

Service account key

Point Service Account Key at a .json key file from Google Cloud Console (IAM > Service Accounts > Keys), or paste the JSON straight into the field.

Google account (OAuth 2.0)

In [Google Cloud Console](https://console.cloud.google.com/), under APIs & Services > Credentials, click Create Credentials > OAuth client ID and pick Desktop app. Paste the Client ID and Client Secret into the connection form. The first connect stops at **Google Sign-In Required**. Click **Sign In** and approve access in the browser; the connection retries when sign-in finishes. The browser step times out after two minutes.

The refresh token from that sign-in is kept in the macOS keychain, one per OAuth client, so later connects and reconnects need no browser. A background reconnect never opens one. When Google revokes the token, the next connect asks you to sign in again.

One OAuth client signs in one Google account, shared by every connection that uses it. To use a second account, create a second OAuth client, or paste that account's refresh token into OAuth Refresh Token.

Cost

Every query is billed on bytes scanned, so there are three places to control it.

  • Before running, pick Dry Run (Cost) from the Explain dropdown. It asks BigQuery what the query would scan without executing it.
  • After running, the status bar shows bytes processed, bytes billed, and an estimate at the on-demand rate of $6.25 per TB: Processed: 1.5 MB | Billed: 10 MB | ~$0.0001. A cache hit is marked (cached).
  • As a hard ceiling, set Max Bytes Billed: BigQuery rejects a job that would exceed it.

Status bar showing bytes processed, bytes billed, and estimated query cost

Status bar showing bytes processed, bytes billed, and estimated query cost

Querying and editing

Completion, quoting, and formatting follow GoogleSQL: backticks around table names, single quotes around strings. STRUCT and ARRAY columns display as JSON. Cells, inserts, and deletes go through the grid. The Structure tab is read-only, but DDL runs from the editor: CREATE SCHEMA, ALTER TABLE … ADD COLUMN and DROP COLUMN, CREATE OR REPLACE VIEW. The DDL tab reads definitions from INFORMATION_SCHEMA.TABLES; clustering and partitioning appear under Indexes.

Minimum IAM roles: roles/bigquery.user to run queries, roles/bigquery.dataViewer to read, roles/bigquery.dataEditor to write.

Limitations

  • Grid UPDATE and DELETE match a row on its declared primary key. A table without one is matched on every column except ARRAY, JSON and GEOGRAPHY ones (and STRUCT columns that contain them), so identical rows change together. A table whose columns are all of those types cannot be edited in the grid. Declare PRIMARY KEY (…) NOT ENFORCED to match one row exactly.
  • A partitioned table needs a partition filter for UPDATE and DELETE, or the statement is rejected. Write it in the editor.
  • No transactions, no streaming inserts, no SSH tunnel.
  • Deep pagination rescans from the start of the table. Filter rather than paging far in.
  • A job is polled until the query timeout, then cancelled. With No limit, it is polled until it finishes.

Troubleshooting

Application default credentials not found. Run gcloud auth application-default login.: No ADC file was found. Run the command, set GOOGLE_APPLICATION_CREDENTIALS to a key file, or switch the connection to a service account key.

Permission denied: Authenticated but unauthorized. Grant roles/bigquery.user on the project and a data role on the dataset.

Project not found: Enter the Project ID, not the display name and not the numeric project number.

No tables after connect: Tables load when you expand the dataset node. An empty dataset stays empty; open another.