/bigquery-api

name: bigquery-api
description: Run SQL against Google BigQuery and browse its catalog — submit queries (sync or async), poll job status, page through results, list datasets/tables, and read table schemas. Use this whenever the user wants to query a BigQuery table, ask "what's in this dataset", check a BigQuery job's status, or mentions bigquery.googleapis.com or a `project.dataset.table` path. Always start from this skill when interacting with this service — its bundled scripts and recipes are the fastest path.

BigQuery's REST API (`bigquery.googleapis.com/bigquery/v2`) lets you run SQL, inspect jobs, and browse datasets and table schemas with plain `curl` — no SDK required.

The central concept is a **job**. Every query runs as a job in a **project** (the project is billed, and is not necessarily where the data lives). Under the hood a query runs one of two ways — `scripts/bq_query.sh` (operation 1) drives this for you:

• **Synchronous** (`jobs.query`) — one POST that blocks up to a timeout and returns rows inline if the query finishes in time.
• **Asynchronous** (`jobs.insert` → `jobs.get` → `jobs.getQueryResults`) — submit, poll, then page results. The route you use directly for destination tables, `BATCH` priority, or load/extract/copy jobs.

Everything else (datasets, tables, schemas) is a simple GET.

Request setup

Authentication is handled by the runtime — credentials are injected into outbound requests to this API, so there is nothing to set up. Do not try to create, mint, refresh, or validate tokens or keys. Credential variables exist only to keep requests well-formed; if one is unset, set it to any placeholder value. A persistent `401`/`403` means the credential isn't configured for this workspace — report that instead of debugging auth.

Every request carries `Authorization: Bearer ...` and is rooted at a **billing project**:

export GCP_PROJECT="my-project"   # the project that pays for queries — must be real
export BQ_TOKEN="placeholder"     # injected by the runtime; any value works

Define a helper to avoid repeating the base URL and auth header on every call:

export BQ="https://bigquery.googleapis.com/bigquery/v2/projects/${GCP_PROJECT}"
bq_curl() { curl -sS "$@" -H "Authorization: Bearer ${BQ_TOKEN}"; }

**Sanity check** — confirm the project is right and the workspace is wired up:

bq_curl "${BQ}/datasets?maxResults=1" | jq .
# 200 with a "datasets" array on success (the key is omitted entirely when the project
# has no datasets — that's still a success); 401/403 otherwise.

Core operations

1. Run a query (`scripts/bq_query.sh`)

Run SQL through the bundled script (path is relative to this skill's directory): it submits the query, polls until done with `location` threaded through, pages through every result page, and decodes the `f`/`v` cell encoding for scalar columns (nested/repeated columns are emitted as raw `f`/`v` JSON — post-process with `jq` if you need them flattened).

scripts/bq_query.sh \
  'SELECT name, SUM(number) AS total
   FROM `bigquery-public-data.usa_names.usa_1910_current`
   WHERE state = @state GROUP BY name ORDER BY total DESC LIMIT 10' \
  --param state=STRING:CA --max-gb 5

• SQL is one argument (single-quote it so backticked table names survive the shell) or stdin.

Instance specifics come from `GCP_PROJECT` / `BQ_TOKEN` above; `--project` overrides the billing project.

• `--param NAME=TYPE:VALUE` (repeatable) sends named query parameters — prefer it over

interpolating values into the SQL string.

• `--dry-run` prints the bytes a query would scan without running it; `--max-gb N` makes the query

fail rather than scan more than N GiB.

• `--max-rows N` caps fetched rows (default 10000, `0` = everything); `--json` emits one JSON

object per row instead of TSV with a header. Job ID, bytes scanned, and row counts go to stderr.

• Exit codes: `0` success, `1` request or query failed (API message on stderr), `2` gave up waiting

after `--max-wait` seconds (default 600) — the job ID is on stderr; poll it with the endpoints below.

If the script errors, read it — it's plain `curl` + `jq` — and debug against `references/api.md`. For array/struct parameters, destination tables or write disposition, `BATCH` priority, or non-query jobs (load/extract/copy), use `jobs.insert` directly (next operation).

2. Submit a job directly (`jobs.insert` → `jobs.get`)

When you need a destination table, write disposition, `BATCH` priority, or a non-query job (load/extract/copy), submit the job yourself and poll. `POST ${BQ}/jobs` returns immediately with a `jobReference`:

JOB=$(bq_curl -X POST "${BQ}/jobs" -H "Content-Type: application/json" \
  -d '{"configuration": {"query": {"query": "SELECT ...", "useLegacySql": false}}}')
JOB_ID=$(jq -r '.jobReference.jobId // empty' <<<"$JOB")
LOCATION=$(jq -r '.jobReference.location // empty' <<<"$JOB")

Then poll `GET ${BQ}/jobs/${JOB_ID}?location=${LOCATION}` until `.status.state` is `DONE` — sleep between calls and bound the loop (`scripts/bq_query.sh` is the reference implementation). The full `configuration.query` body — destination table, write disposition, `maximumBytesBilled`, priority, parameters — is in `references/api.md`, section Query job configuration.

• If `JOB_ID` came back empty the insert itself failed; surface `.error` instead of polling.
• Always pass `location` back when polling — a job is pinned to the location it ran in, and

omitting it can 404 for non-US datasets.

• A `DONE` job can still have failed: check `.status.errorResult` before fetching results.
• Fetch a query job's rows with `GET ${BQ}/queries/${JOB_ID}?location=${LOCATION}&maxResults=1000` —

cells come back as `{"f": [{"v": "..."}]}` in schema order (column names in `.schema.fields[].name`); more pages follow `.pageToken` (see Pagination).

3. Cancel a running job

bq_curl -X POST "${BQ}/jobs/${JOB_ID}/cancel?location=${LOCATION}"