bigquery

Google BigQuery

BigQuery is Google's serverless, highly scalable, and cost-effective multi-cloud data warehouse. It processes terabytes in seconds.

When to Use

• Serverless Enterprise Data Warehousing: Querying petabytes of structured and semi-structured data with zero infrastructure management.
• Real-Time Streaming Analytics: Ingesting and querying millions of events per second with sub-second data freshness.
• In-Database Machine Learning (BigQuery ML): Training and scoring regression, classification, and forecasting models using standard SQL.
• Federated Multi-Cloud Queries (BigQuery Omni): Querying data stored in AWS S3 and Azure Blob Storage directly without egress replication.

Quick Start

-- Standard SQL
SELECT name, COUNT(*) as count
FROM `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY name
ORDER BY count DESC
LIMIT 10;

Core Concepts

Capacitor Columnar Storage & Dremel Query Engine

Data is stored in proprietary Capacitor columnar format with dynamic tree-based Dremel execution slots:

-- Partitioned & Clustered Table DDL
CREATE OR REPLACE TABLE `my_project.analytics.user_events`
(
  event_id STRING,
  user_id STRING,
  event_name STRING,
  event_timestamp TIMESTAMP,
  metadata JSON
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_name
OPTIONS(
  description="Partitioned clickstream events with clustering for point lookups",
  require_partition_filter=true
);

Semi-Structured Native JSON Querying

Queries nested JSON attributes efficiently without schema migrations:

SELECT
  user_id,
  JSON_VALUE(metadata.device.os) AS os_name,
  JSON_EXTRACT_SCALAR(metadata, '$.cart.total') AS cart_total
FROM `my_project.analytics.user_events`
WHERE DATE(event_timestamp) = CURRENT_DATE()
  AND JSON_VALUE(metadata.action) = 'checkout';

BigQuery ML Model Training

Trains and evaluates predictive models entirely inside SQL:

CREATE OR REPLACE MODEL `analytics.churn_prediction_model`
OPTIONS(model_type='logistic_reg', input_label_cols=['has_churned']) AS
SELECT
  total_spend,
  session_count,
  has_churned
FROM `analytics.customer_features`;