bq: The BigQuery CLI

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#

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#

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#

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.

Loading data#

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#

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#

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#

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#

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#

# ~/.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#

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:

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#

# .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.