TopolearnMost analytics, logs and billing data on Google Cloud end up in BigQuery, so every cloud engineer needs the basics.
BigQuery is Google Cloud's serverless data warehouse. There are no clusters to size: you write SQL (GoogleSQL dialect) and it runs on shared compute measured in slots. Storage is columnar and separated from compute.
Organisation: project > dataset (has a location such as EU or europe-west1, and IAM) > table or view. Refer to tables as project.dataset.table in backticks.
Billing has two parts:
- Storage, per GB per month, cheaper for data untouched for 90 days.
- Compute: either on-demand, per TiB scanned (with a monthly free allowance), or capacity pricing through editions and slot reservations with autoscaling.
Because on-demand charges by bytes scanned, select only the columns you need (SELECT * reads every column), and use partitioned and clustered tables so filters prune data. LIMIT does not reduce bytes scanned. A dry run reports bytes before you spend anything.
Data gets in by batch loads from Cloud Storage (free), streaming via the Storage Write API, Pub/Sub BigQuery subscriptions, log sinks and billing export, or federated queries over external tables.
BigQuery is a separate deep topic; this lesson covers what you need as a Google Cloud user.
Example
# Estimate cost first: dry run reports bytes processed
bq query --use_legacy_sql=false --dry_run \
'SELECT name, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE state = "TX" GROUP BY name ORDER BY total DESC LIMIT 3'
# Query successfully validated. ... this query will process ... bytes of data.
bq mk --dataset --location=EU shop-dev-4821:analytics
bq load --source_format=CSV --autodetect analytics.orders \
gs://shop-dev-4821-uploads/exports/orders-*.csv
bq query --use_legacy_sql=false 'SELECT COUNT(*) FROM analytics.orders'Remember
- Serverless: datasets and tables, no clusters
- On-demand pricing is per byte scanned; select fewer columns and partition
- LIMIT does not reduce cost; dry runs do tell you the cost
Watch out for
- Running SELECT * ... LIMIT 10 on a multi-terabyte table to peek at it; use the table preview instead.