# gcloud CLI Cheat Sheet — bq: The BigQuery CLI

> **Tool:** BigQuery CLI (bq)
> **Category:** Cloud CLIs
> **Verified against:** BigQuery CLI 2.1.27 (bundled with Google Cloud SDK 553.0.0), flags verified via
> `bq help query`, `bq help load`, `bq help mk`, `bq help extract`, `bq help update`, `bq help ls`, `bq help
> show`, a real local `bq init` invocation confirming it's blocked by the Cloud SDK wrapper, and the
> `.bigqueryrc` file-parsing logic read directly from
> `google-cloud-sdk/platform/bq/bq_utils.py`/`bq_flags.py`, 2026-09-18
> **Official docs:** https://cloud.google.com/bigquery/docs/bq-command-line-tool

## What it is and where it fits 🎯

`bq` is BigQuery's own command-line tool, installed as a Cloud SDK component alongside `gcloud` itself
(confirmed by `gcloud components list` showing it as a first-class component, the same way `gsutil` is,
covered on page `12`), but it is **not** a `gcloud` subcommand, it's a separate Python entry point with its
own command syntax, flag conventions, and help system. It exists because BigQuery's actual surface area
(datasets, tables, jobs, SQL queries, ML models, reservations) is deep enough to warrant a dedicated tool
rather than being folded into `gcloud`'s command tree the way smaller services are. This page assumes the
project/IAM basics from page `01`; `bq` deliberately has no authentication or project-selection mechanism
of its own, both are entirely inherited from `gcloud`.

## Core concepts: bq has no identity of its own

```mermaid
flowchart LR
    GcloudAuth["gcloud auth login /<br/>activate-service-account"] --> Creds["Shared credential store<br/>(~/.config/gcloud)"]
    GcloudProject["gcloud config set project"] --> ActiveConfig["Active named configuration"]
    Creds --> BQ["bq (reads the same creds)"]
    ActiveConfig --> BQ
    BQ --> API["BigQuery API"]

    classDef info fill:#e5f0fa,stroke:#1d6fb8,color:#10161c
    classDef accent fill:#f0e9fb,stroke:#6d43c0,color:#10161c
    class GcloudAuth,GcloudProject info
    class BQ accent
```

Running `bq init` today prints "The 'init' command is no longer needed with Google Cloud CLI. To
authenticate, run gcloud auth" and refuses to proceed, confirmed directly against a local install. This is
by design: `bq` reads whichever account and project `gcloud`'s active named configuration (page `00`) has
set, there is no separate `bq login` or `bq config set project` to remember, switching a `gcloud`
configuration switches what `bq` operates against too, with zero extra steps.

## BigQuery's own resource hierarchy: projects → datasets → tables

```bash
bq ls                              # datasets in the current project
bq ls my_dataset                   # tables/views inside a dataset
bq ls -j                           # recent jobs (queries, loads, extracts) in the current project
bq mk my_dataset                   # create a dataset
bq mk --data_location=EU eu_dataset   # region/multi-region is fixed at creation, cannot be changed later
```

A **dataset** is BigQuery's container for tables, views, and models, roughly analogous to a schema in a
traditional database, and it's also the level at which a default table expiration, default encryption key,
and access control are usually set. `--data_location` is a genuinely one-way decision, unlike a Cloud
Storage bucket's location, there's no `bq update --data_location` to move an existing dataset, getting this
wrong means recreating the dataset and copying every table into the new one.

## Running queries

```bash
bq query --use_legacy_sql=false \
  'SELECT customer_id, SUM(amount) AS total FROM `my_project.sales.orders` GROUP BY customer_id LIMIT 10'

bq query --use_legacy_sql=false --dry_run \
  'SELECT * FROM `my_project.sales.orders` WHERE order_date > "2026-01-01"'

bq query --use_legacy_sql=false --maximum_bytes_billed=1000000000 \
  --destination_table=my_dataset.query_results --replace \
  'SELECT * FROM `my_project.sales.orders`'
```

`--dry_run` validates the query and reports exactly how many bytes it *would* scan, without running it or
incurring cost, always worth running before a query against a genuinely large table in an interactive
session. `--maximum_bytes_billed` is a hard cost ceiling, the query fails outright rather than running if
it would scan more than the specified byte limit, the single most useful guardrail against an accidental
full-table scan on a multi-terabyte table from a missing `WHERE` clause.

> [!WARNING]
> **BigQuery bills by bytes scanned, not by query wall-clock time or rows returned.** A query with `LIMIT
> 10` and no `WHERE` clause on a partitioned table can still scan and bill for the entire table, `LIMIT`
> only caps the *output*, not the amount of data read to produce it. Filtering on the table's partitioning
> column (a date column, typically) is what actually reduces bytes scanned; a missing partition filter on a
> large table has produced real, unpleasant billing surprises.

## Loading data

```bash
bq load --source_format=CSV --skip_leading_rows=1 --autodetect \
  my_dataset.customers gs://my-bucket/customers.csv

bq load --source_format=NEWLINE_DELIMITED_JSON \
  my_dataset.events gs://my-bucket/events/*.json schema.json

bq load --source_format=PARQUET my_dataset.events_parquet gs://my-bucket/events/*.parquet
```

`--autodetect` infers a schema from the source file's own header/structure, convenient for a one-off
exploration but worth replacing with an explicit schema file (the third positional argument) for anything
recurring, an inferred schema can silently change shape between runs if the source data's own shape drifts.
The source can be a local file path or a `gs://` URI (or a comma-separated list/wildcard of them), loading
directly from Cloud Storage is the standard path for anything beyond a small local CSV, since it avoids
uploading the file through `bq` itself first.

## Extracting and exporting data

```bash
bq extract --destination_format=CSV \
  my_dataset.customers gs://my-bucket/exports/customers-*.csv

bq extract --destination_format=NEWLINE_DELIMITED_JSON --compression=GZIP \
  my_dataset.events gs://my-bucket/exports/events-*.json.gz
```

The `*` wildcard in the destination URI is required for any table extract that could produce more than
about 1GB of output, BigQuery shards a large export across multiple files automatically and needs the
wildcard to name them, an extract without one on a large table fails with an explicit error asking for it,
rather than silently truncating.

## Table and dataset lifecycle management

```bash
bq update --default_table_expiration=2592000 my_dataset      # every new table in this dataset expires after 30 days
bq update --expiration=604800 my_dataset.temp_results          # this one table expires in 7 days from now

bq update --time_partitioning_type=DAY --time_partitioning_field=order_date \
  --time_partitioning_expiration=7776000 my_dataset.orders

bq show --schema --format=prettyjson my_dataset.orders
bq cp my_dataset.orders my_dataset.orders_backup
bq rm -f -t my_dataset.temp_results   # -f: no confirmation prompt, -t: this is a table (not a dataset)
```

`--default_table_expiration` on a dataset is the single highest-leverage cost control on this page, a
scratch/staging dataset with no expiration set accumulates every table anyone has ever created in it,
forever; setting a sane default (30-90 days is common) means abandoned tables clean themselves up instead
of becoming a permanent, silently-growing storage bill nobody remembers to audit.

## Job management: every query and load is an asynchronous job underneath

```bash
bq ls -j --max_results=10                     # recent jobs, newest first
bq show -j <job_id>                             # full status/statistics for one job
bq wait <job_id> 300                            # block up to 300s for a specific job to finish
bq cancel <job_id>
```

Every `bq query`/`bq load`/`bq extract` invocation is a thin synchronous wrapper around submitting a job and
polling it, the global `--nosync` flag (placed *before* the subcommand: `bq --nosync load ...`) returns the
job ID immediately instead of blocking, useful for kicking off a long-running load and checking on it
separately with `bq wait`/`bq show -j` rather than tying up a terminal or CI step for the whole duration.

## Scheduled queries

```bash
bq mk --transfer_config --project_id=my-project-id \
  --data_source=scheduled_query --target_dataset=reporting \
  --display_name="Daily Revenue Summary" --schedule="every day 06:00" \
  --params='{"query":"SELECT DATE(order_date) AS d, SUM(amount) AS revenue FROM `my-project-id.sales.orders` GROUP BY d","destination_table_name_template":"daily_revenue_{run_date}","write_disposition":"WRITE_TRUNCATE"}'

bq ls --transfer_config --transfer_location=us --project_id=my-project-id
bq update --transfer_config --target_dataset=reporting <transfer-config-id>
```

A scheduled query is really a Data Transfer Service `transfer_config` with `--data_source=scheduled_query`,
the same underlying mechanism BigQuery's own Console UI uses when a query is scheduled from there,
`--schedule` accepts either the plain-English form shown above or a `cron()` expression for more precise
control, and `--params`' embedded `query` string is where the actual SQL lives, worth keeping in version
control as its own `.sql` file and templating into this command rather than hand-editing a long inline JSON
string.

## Config file: `.bigqueryrc`

```ini
# ~/.bigqueryrc — no leading dashes, one flag=value per line
--format=prettyjson
--use_legacy_sql=false

[query]
--maximum_bytes_billed=5000000000

[load]
--source_format=NEWLINE_DELIMITED_JSON
```

`bq` reads `~/.bigqueryrc` by default (confirmed directly from the tool's own source,
`bq_flags.py`/`bq_utils.py`), overridable with the `BIGQUERYRC` environment variable or a `--bigqueryrc`
flag, in that precedence order, an explicit command-line flag always wins over anything in the file. Lines
outside any `[section]` header apply globally to every `bq` command; a `[query]`/`[load]`/`[cp]` section
scopes its flags to just that subcommand, the mechanism behind setting a default `--maximum_bytes_billed`
for every query without having to type it every time, shown above.

## IAM on BigQuery resources

```bash
bq add-iam-policy-binding \
  --member="user:jane@example.com" --role="roles/bigquery.dataViewer" \
  my_dataset.orders

bq show --format=prettyjson my_dataset.orders | grep -A5 '"access"'
```

`bq`'s own `add-iam-policy-binding`/`get-iam-policy`/`set-iam-policy` work at the table/view level; a
dataset's access control is instead read and set via `bq show`/`bq update`'s own `access` structure, a
genuinely inconsistent shape between the two resource levels that's easy to trip over expecting one uniform
IAM interface, `gcloud`'s own newer `bq` resource support (`gcloud alpha bq`) is converging this, but as of
this version the split above is still the reality on the ground.

## Real-world scenario: preventing a runaway analytics query from a shared BI tool

A BI dashboard tool occasionally issues an unfiltered query against a 50TB events table, and the team wants
a hard ceiling rather than relying on every analyst remembering `--maximum_bytes_billed`:

```bash
bq mk --reservation --project_id=my-project-id --location=us bi-tool-reservation
bq update --reservation_id=my-project-id:us.bi-tool-reservation \
  --slots=200 my-project-id:us.bi-tool-reservation

bq mk --reservation_assignment --reservation_id=my-project-id:us.bi-tool-reservation \
  --job_type=QUERY --assignee_type=PROJECT --assignee_id=bi-tool-project
```

A **reservation** caps the compute (slots) available to a specific project or folder rather than relying on
a per-query byte ceiling, the structural fix when the actual risk is a whole class of queries from one
source, not one specific analyst's one-off mistake, complementary to `--maximum_bytes_billed` rather than a
replacement for it.

## Real-world scenario: promoting a validated query result into a permanent table

- [ ] Run the query with `--dry_run` first to confirm the byte-scan estimate is reasonable before running
      it for real
- [ ] Run it with `--destination_table` and `--replace` (shown earlier) rather than manually exporting and
      re-loading the result
- [ ] Set an explicit `--time_partitioning_field` on the destination table if it will be queried by date
      going forward, adding partitioning after the fact requires recreating the table, not a metadata patch
- [ ] Confirm the destination dataset's default expiration doesn't silently apply to what's meant to be a
      permanent table (`bq update --expiration=0 dataset.table` explicitly clears it)

## CI/CD integration recipe: scheduled data-quality check that fails the pipeline on a real anomaly

```yaml
# .github/workflows/data-quality-check.yml
name: Data quality check
on:
  schedule:
    - cron: "0 6 * * *"

permissions:
  id-token: write
  contents: read

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: google-github-actions/auth@v2
        with:
          workload_identity_provider: projects/<project-number>/locations/global/workloadIdentityPools/github-pool/providers/github-provider
          service_account: bq-quality-check@my-project-id.iam.gserviceaccount.com
      - uses: google-github-actions/setup-gcloud@v2
      - run: |
          NULL_COUNT=$(bq query --use_legacy_sql=false --format=csv --nouse_cache --quiet \
            'SELECT COUNT(*) FROM `my_project.sales.orders` WHERE customer_id IS NULL' | tail -1)
          if [ "$NULL_COUNT" -gt 0 ]; then
            echo "Found $NULL_COUNT orders with a null customer_id"
            exit 1
          fi
```

Because `bq` inherits the same Workload Identity Federation-authenticated `gcloud` session every other page
on this site's pipelines use, no separate BigQuery-specific credential ever needs to exist in CI, exactly
the "no stored secret" property page `01` establishes for the whole tool.

## Common pitfalls

- **Trying `bq init` to authenticate.** It's blocked outright on a Cloud SDK install, run `gcloud auth
  login`/`gcloud config set project` instead, see the core-concepts diagram.
- **Assuming `LIMIT` reduces query cost.** It only reduces returned rows; bytes-scanned billing depends on
  what the query reads, not what it returns, see the WARNING above.
- **Loading data with `--autodetect` for a recurring, automated pipeline.** An inferred schema can drift
  silently between runs; use an explicit schema file for anything beyond one-off exploration.
- **Extracting a large table with no wildcard in the destination URI.** Fails outright once the export
  would exceed roughly 1GB, add the `*` from the start.
- **No `--default_table_expiration` on a scratch/staging dataset.** It accumulates every table ever created
  in it indefinitely, a genuine, easy-to-avoid storage cost.

## Exit codes

`0` success, non-zero on any query error, permission error, or a `--maximum_bytes_billed` ceiling being
exceeded, the last of these is a deliberate, expected non-zero exit (the guardrail doing its job), not a
failure to investigate the same way a genuine API error is.

## When to reach for something else

For interactive, visual query exploration and schema browsing, the BigQuery Console UI remains more
convenient than `bq` for a human doing ad hoc analysis; reach for `bq` specifically for scripted,
repeatable, or CI-driven operations. For declarative, reviewable dataset/table/reservation provisioning
across environments, prefer Terraform's `google_bigquery_dataset`/`google_bigquery_table`/
`google_bigquery_reservation` resources over a growing shell script of the commands on this page, consistent
with the IaC guidance on every earlier page.