GCP BigQuery Cheat Sheet

SELECT  name,  COUNT(*) AS totalFROM `bigquery-public-data.usa_names.usa_1910_2013`WHERE state = 'CA'GROUP BY nameORDER BY total DESCLIMIT 10;

bq mk my_dataset                              # Create datasetbq load --source_format=CSV \  my_dataset.my_table gs://my-bucket/data.csv \  name:STRING,age:INTEGERbq query --use_legacy_sql=false \  'SELECT COUNT(*) FROM my_dataset.my_table'bq show my_dataset.my_table                   # Table schema/infobq rm -t my_dataset.my_table                  # Delete table

from google.cloud import bigqueryclient = bigquery.Client()query = """    SELECT name, total    FROM `my_dataset.my_table`    ORDER BY total DESC    LIMIT 10"""for row in client.query(query).result():    print(row.name, row.total)

CREATE TABLE my_dataset.events (  event_id STRING,  user_id STRING,  event_type STRING,  event_ts TIMESTAMP)PARTITION BY DATE(event_ts)CLUSTER BY user_id, event_typeOPTIONS (  partition_expiration_days = 90,  require_partition_filter = true);

SELECT  user_id,  event_ts,  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) AS event_seq,  SUM(1) OVER (    PARTITION BY user_id ORDER BY event_ts    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ) AS running_event_countFROM `my_dataset.events`QUALIFY event_seq <= 10;

CREATE TEMP FUNCTION normalize(s STRING)RETURNS STRINGLANGUAGE js AS """  return s.trim().toLowerCase().replace(/[^a-z0-9]/g, '_');""";DECLARE threshold INT64 DEFAULT 100;IF (SELECT COUNT(*) FROM my_dataset.events) > threshold THEN  SELECT normalize(event_type) AS clean_type, COUNT(*) AS n  FROM my_dataset.events  GROUP BY clean_type  ORDER BY n DESC;END IF;

MERGE INTO my_dataset.users_dim TUSING my_dataset.users_staging SON T.user_id = S.user_idWHEN MATCHED AND T.updated_at < S.updated_at THEN  UPDATE SET name = S.name, email = S.email, updated_at = S.updated_atWHEN NOT MATCHED THEN  INSERT (user_id, name, email, updated_at)  VALUES (S.user_id, S.name, S.email, S.updated_at);