Google BigQuery SQL
Generate GoogleSQL, not PostgreSQL or MySQL syntax.
Names and query shape
- Address objects as
project.dataset.tableand quote paths containing a project ID with backticks:`my-project.analytics.events`. - Use
QUALIFYfor filters on window-function results. Do not add a subquery only to emulate dialects that lackQUALIFY. - Expand arrays with
UNNEST(array_expression). AccessSTRUCTfields with dot notation. - Prefer
SAFE_CASTandSAFE_DIVIDEwhen invalid input or zero denominators should produceNULLinstead of failing the query.
Time semantics
DATEis a calendar date,DATETIMEis civil time without a zone, andTIMESTAMPis an absolute instant.- Pass an explicit time zone when converting a
TIMESTAMPto a localDATEorDATETIME, for exampleDATE(event_ts, 'Asia/Shanghai'). - Use
CURRENT_DATE('Asia/Shanghai')when the business date is zone-specific.
Cost and partition pruning
- Select only required columns; avoid
SELECT *on wide or nested tables. - Filter directly on the partition column with a constant range. Avoid wrapping the partition column in a function when an equivalent range predicate is possible.
- Use
TABLESAMPLE SYSTEMonly for approximate exploration;LIMITalone does not guarantee fewer bytes scanned. - When querying
INFORMATION_SCHEMA, qualify it with the matching dataset orregion-REGION; job metadata queries must run in that same region.
DDL and migration
- Use BigQuery types such as
INT64,FLOAT64,NUMERIC,BIGNUMERIC,BOOL,STRING,BYTES,JSON,ARRAY, andSTRUCT. - BigQuery has no
AUTO_INCREMENT,SERIAL, storageENGINE,DISTRIBUTED BY, orBUCKETSclause. - Declare primary and foreign keys as
NOT ENFORCED. - Put
PARTITION BYand thenCLUSTER BYafter the column list:
CREATE TABLE `my-project.analytics.events` (
event_id INT64 NOT NULL,
event_ts TIMESTAMP,
attributes JSON,
PRIMARY KEY (event_id) NOT ENFORCED
)
PARTITION BY DATE(event_ts)
CLUSTER BY event_id;