bigquery

name	bigquery
description	Query and manage Google BigQuery datasets using the bq CLI and REST API. Run SQL queries, export results, manage tables and datasets in BigQuery.
version	1.0.0
allowed-tools	Bash
metadata	{"credentials":["GOOGLE_APPLICATION_CREDENTIALS_JSON","BIGQUERY_PROJECT_ID"]}

BigQuery Skill

Query and manage Google BigQuery datasets using the bq CLI tool and REST API.

Quick Start

# Run a query
bq query --use_legacy_sql=false --format=json \
  'SELECT count(*) as total FROM `project.dataset.table`'

# List datasets
bq ls --project_id="$BIGQUERY_PROJECT_ID"

Prerequisites

Variable	Description	Example
GOOGLE_APPLICATION_CREDENTIALS_JSON	Service account JSON key	{"type":"service_account","project_id":"my-project",...}
BIGQUERY_PROJECT_ID	GCP project ID (optional if in credentials)	my-project-123

First-Time Setup

The bq CLI authenticates via service account. Write the credentials to a file and set the path:

# Write credentials to file
echo "$GOOGLE_APPLICATION_CREDENTIALS_JSON" > /tmp/gcp-sa.json
export GOOGLE_APPLICATION_CREDENTIALS=/tmp/gcp-sa.json

# Verify access
bq ls --project_id="$BIGQUERY_PROJECT_ID"

Alternative: REST API (No CLI Needed)

If bq is not installed, use BigQuery REST API directly with curl:

# Get access token from service account
ACCESS_TOKEN=$(python3 -c "
import json, time, base64, hashlib, urllib.request
sa = json.loads('''$GOOGLE_APPLICATION_CREDENTIALS_JSON''')
import jwt
token = jwt.encode({
  'iss': sa['client_email'],
  'scope': 'https://www.googleapis.com/auth/bigquery',
  'aud': 'https://oauth2.googleapis.com/token',
  'iat': int(time.time()),
  'exp': int(time.time()) + 3600
}, sa['private_key'], algorithm='RS256')
data = urllib.parse.urlencode({'grant_type': 'urn:ietf:params:oauth:grant-type:jwt-bearer', 'assertion': token}).encode()
resp = json.loads(urllib.request.urlopen('https://oauth2.googleapis.com/token', data).read())
print(resp['access_token'])
")

# Query via REST API
curl -X POST \
  "https://bigquery.googleapis.com/bigquery/v2/projects/$BIGQUERY_PROJECT_ID/queries" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT count(*) as total FROM `dataset.table`",
    "useLegacySql": false
  }'

Core Recipes (bq CLI)

Run a Query

echo "$GOOGLE_APPLICATION_CREDENTIALS_JSON" > /tmp/gcp-sa.json
export GOOGLE_APPLICATION_CREDENTIALS=/tmp/gcp-sa.json

bq query --use_legacy_sql=false --format=json \
  'SELECT * FROM `my_dataset.users` LIMIT 10'

Run a Query with Parameters

bq query --use_legacy_sql=false --format=json \
  --parameter='name:STRING:John' \
  'SELECT * FROM `my_dataset.users` WHERE name = @name'

List Datasets

bq ls --project_id="$BIGQUERY_PROJECT_ID" --format=json

List Tables in a Dataset

bq ls --format=json "$BIGQUERY_PROJECT_ID:my_dataset"

Show Table Schema

bq show --format=json "$BIGQUERY_PROJECT_ID:my_dataset.users"

Get Table Preview (Head)

bq head --max_rows=10 "$BIGQUERY_PROJECT_ID:my_dataset.users"

Count Rows

bq query --use_legacy_sql=false --format=json \
  'SELECT count(*) as total FROM `my_dataset.users`'

Export & Import

Export Query Results to CSV

bq query --use_legacy_sql=false --format=csv \
  'SELECT * FROM `my_dataset.users` LIMIT 1000' > users.csv

Export Query Results to JSON

bq query --use_legacy_sql=false --format=json \
  'SELECT * FROM `my_dataset.users` LIMIT 1000' > users.json

Export Large Table to GCS (then download)

# Export to Google Cloud Storage
bq extract --destination_format=CSV \
  "$BIGQUERY_PROJECT_ID:my_dataset.users" \
  "gs://my-bucket/exports/users-*.csv"

# Download from GCS
gsutil cp "gs://my-bucket/exports/users-*.csv" ./

Load CSV into BigQuery

bq load --source_format=CSV --autodetect \
  "$BIGQUERY_PROJECT_ID:my_dataset.new_table" \
  ./data.csv

Load JSON into BigQuery

bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect \
  "$BIGQUERY_PROJECT_ID:my_dataset.new_table" \
  ./data.jsonl

Schema & Table Management

Create Dataset

bq mk --dataset "$BIGQUERY_PROJECT_ID:my_new_dataset"

Create Table from Schema

bq mk --table "$BIGQUERY_PROJECT_ID:my_dataset.users" \
  name:STRING,email:STRING,age:INTEGER,created_at:TIMESTAMP

Create Table from Query (CTAS)

bq query --use_legacy_sql=false \
  --destination_table="$BIGQUERY_PROJECT_ID:my_dataset.active_users" \
  'SELECT * FROM `my_dataset.users` WHERE last_login > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)'

Copy Table

bq cp "$BIGQUERY_PROJECT_ID:dataset.source_table" \
  "$BIGQUERY_PROJECT_ID:dataset.destination_table"

Delete Table

bq rm -f -t "$BIGQUERY_PROJECT_ID:my_dataset.old_table"

Common Analytics Queries

Daily Active Users

bq query --use_legacy_sql=false --format=json '
SELECT
  DATE(event_timestamp) as date,
  COUNT(DISTINCT user_id) as dau
FROM `my_dataset.events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date DESC
'

Top Pages by Views

bq query --use_legacy_sql=false --format=json '
SELECT
  page_path,
  COUNT(*) as views,
  COUNT(DISTINCT user_id) as unique_users
FROM `my_dataset.pageviews`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY page_path
ORDER BY views DESC
LIMIT 20
'

Revenue Summary

bq query --use_legacy_sql=false --format=json '
SELECT
  FORMAT_DATE("%Y-%m", date) as month,
  SUM(revenue) as total_revenue,
  COUNT(DISTINCT customer_id) as customers
FROM `my_dataset.transactions`
GROUP BY month
ORDER BY month DESC
LIMIT 12
'

Output Formats

Flag	Format	Best For
--format=json	JSON array	Programmatic use, piping to jq
--format=csv	CSV	Spreadsheets, further processing
--format=pretty	Aligned table (default)	Human reading
--format=sparse	Sparse output	Wide tables

Parse JSON Output with jq

bq query --use_legacy_sql=false --format=json \
  'SELECT name, email FROM `dataset.users` LIMIT 5' | jq '.[].name'

Managed Service Setup

Google Cloud Console

1. Go to BigQuery Console
2. Create a service account: IAM → Service Accounts → Create
3. Grant role: BigQuery Data Viewer (read) or BigQuery Data Editor (read/write)
4. Create JSON key: Service Account → Keys → Add Key → JSON
5. Copy the JSON content to GOOGLE_APPLICATION_CREDENTIALS_JSON env var
6. Set BIGQUERY_PROJECT_ID to your GCP project ID

Required IAM Roles

Role	Permissions
roles/bigquery.dataViewer	Read tables and run queries
roles/bigquery.dataEditor	Read + write tables
roles/bigquery.jobUser	Run query jobs (required for all queries)
roles/bigquery.admin	Full access

Minimum for read-only: bigquery.dataViewer + bigquery.jobUser

Error Handling

Error	Cause	Solution
Not found: Dataset	Dataset doesn't exist	Run bq ls to list available datasets
Access Denied	Insufficient permissions	Check IAM roles, need at least dataViewer + jobUser
Invalid credentials	Bad service account JSON	Verify GOOGLE_APPLICATION_CREDENTIALS_JSON is valid JSON
Quota exceeded	Query size/frequency limit	Add LIMIT, use partitioned tables, check billing
Resources exceeded	Query too large	Break into smaller queries, use LIMIT
Table not found	Wrong project/dataset/table	Use fully qualified name: project.dataset.table
bq: command not found	CLI not installed	Use REST API alternative above, or install Google Cloud SDK

Tips

• Always use --use_legacy_sql=false — standard SQL is more readable and powerful
• Use --format=json for programmatic processing, pipe to jq for filtering
• Use LIMIT when exploring — BigQuery bills by data scanned, not rows returned
• Use partitioned tables — queries on partitioned tables scan less data (cheaper)
• Use --dry_run to check query cost before running: bq query --dry_run --use_legacy_sql=false 'SELECT ...'
• Backtick table names in standard SQL: `project.dataset.table`
• Cache results: BigQuery caches query results for 24 hours — repeated queries are free
• Cost awareness: BigQuery charges $5/TB scanned. Always preview with LIMIT first