BigQuery

# Create a dataset
bq mk --dataset \
  --location=US \
  --description="Analytics data" \
  my-project:my_dataset

# Create a table from schema file
bq mk --table my_dataset.my_table schema.json

# List datasets / tables
bq ls
bq ls my_dataset

# Describe table schema
bq show my_dataset.my_table

# Delete table / dataset
bq rm -f my_dataset.my_table
bq rm -r -f my_dataset             # recursive

[
  { "name": "user_id",    "type": "STRING",    "mode": "REQUIRED" },
  { "name": "event_name", "type": "STRING",    "mode": "NULLABLE" },
  { "name": "ts",         "type": "TIMESTAMP", "mode": "REQUIRED" },
  { "name": "revenue",    "type": "FLOAT64",   "mode": "NULLABLE" }
]

# Run a query (interactive, results to stdout)
bq query --nouse_legacy_sql \
  'SELECT user_id, COUNT(*) AS cnt FROM my_dataset.my_table GROUP BY 1 LIMIT 10'

# Write results to a destination table
bq query --nouse_legacy_sql \
  --destination_table=my_dataset.results \
  --replace \
  'SELECT * FROM my_dataset.my_table WHERE revenue > 100'

# Dry run (estimate bytes scanned without running)
bq query --nouse_legacy_sql --dry_run \
  'SELECT * FROM my_dataset.my_table'

-- Partition pruning (filter on partition column to save cost)
SELECT *
FROM `my-project.my_dataset.events`
WHERE DATE(ts) BETWEEN '2025-01-01' AND '2025-01-31';

-- Clustering query (equality/range on clustered columns is fast)
SELECT user_id, SUM(revenue) AS total
FROM `my-project.my_dataset.events`
WHERE event_name = 'purchase'
GROUP BY user_id;

-- Approximate count distinct (cheaper than COUNT(DISTINCT))
SELECT APPROX_COUNT_DISTINCT(user_id) AS est_users
FROM `my-project.my_dataset.events`;

-- Wildcard tables (query table shards matching a pattern)
SELECT * FROM `my-project.my_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131';

-- ARRAY_AGG + UNNEST pattern
SELECT user_id, tag
FROM `my-project.my_dataset.users`,
UNNEST(tags) AS tag;

# Load CSV from local file
bq load --source_format=CSV \
  --autodetect \
  my_dataset.my_table ./data.csv

# Load NEWLINE_DELIMITED_JSON from GCS
bq load --source_format=NEWLINE_DELIMITED_JSON \
  my_dataset.my_table \
  gs://my-bucket/data/*.json

# Load Parquet (schema auto-detected)
bq load --source_format=PARQUET \
  my_dataset.my_table \
  gs://my-bucket/data/*.parquet

# Load with explicit schema (skip header row)
bq load --source_format=CSV \
  --skip_leading_rows=1 \
  my_dataset.my_table \
  ./data.csv \
  user_id:STRING,event_name:STRING,ts:TIMESTAMP,revenue:FLOAT64

# Export to GCS as CSV
bq extract \
  --destination_format=CSV \
  my_dataset.my_table \
  gs://my-bucket/export/data-*.csv

# Export as Parquet (compressed)
bq extract \
  --destination_format=PARQUET \
  --compression=SNAPPY \
  my_dataset.my_table \
  gs://my-bucket/export/data-*.parquet

# Create a time-partitioned table (by ingestion time)
bq mk --table \
  --time_partitioning_type=DAY \
  my_dataset.events_partitioned \
  schema.json

# Partition by a column (column partitioning)
bq mk --table \
  --time_partitioning_field=ts \
  --time_partitioning_type=DAY \
  my_dataset.events_by_ts \
  schema.json

# Partition + cluster
bq mk --table \
  --time_partitioning_field=ts \
  --time_partitioning_type=DAY \
  --clustering_fields=user_id,event_name \
  my_dataset.events_clustered \
  schema.json

# Grant data viewer on a TABLE or VIEW (bq add-iam-policy-binding
# works on tables/views only — not datasets)
bq add-iam-policy-binding \
  --member=user:alice@example.com \
  --role=roles/bigquery.dataViewer \
  my-project:my_dataset.my_table

# Dataset-level access: edit the dataset's access list
bq show --format=prettyjson my-project:my_dataset > dataset.json
# add {"role": "READER", "userByEmail": "alice@example.com"} to "access", then:
bq update --source dataset.json my-project:my_dataset

# Grant job user at project level (to run queries)
gcloud projects add-iam-policy-binding my-project \
  --member=user:alice@example.com \
  --role=roles/bigquery.jobUser

# Readers query the view without any access to the source dataset.
# 1. Create the view in its own dataset
bq mk --use_legacy_sql=false \
  --view='SELECT user_id, event_name FROM `my-project.raw_dataset.events`' \
  shared_dataset.events_view

# 2. Authorize the view against the SOURCE dataset:
bq show --format=prettyjson my-project:raw_dataset > raw.json
# add to "access":
#   {"view": {"projectId": "my-project", "datasetId": "shared_dataset", "tableId": "events_view"}}
bq update --source raw.json my-project:raw_dataset

# 3. Grant readers dataViewer on the VIEW's dataset only (not the source)
bq show --format=prettyjson my-project:shared_dataset > shared.json
# add {"role": "READER", "groupByEmail": "analysts@example.com"} to "access", then:
bq update --source shared.json my-project:shared_dataset

# Create a scheduled query via bq (or use the Console)
bq mk \
  --transfer_config \
  --data_source=scheduled_query \
  --target_dataset=my_dataset \
  --display_name="Nightly rollup" \
  --schedule="every 24 hours" \
  --params='{"query":"INSERT INTO my_dataset.daily_rollup SELECT DATE(ts), COUNT(*) FROM my_dataset.events GROUP BY 1","destination_table_name_template":"daily_rollup","write_disposition":"WRITE_APPEND"}'