Datawise
Practical SQL

DECLARE and SET variables in BigQuery

Constantin LunguUpdated 1 min read

Photo by s2 art on Unsplash

Here's a part of procedural language that I use quite a lot in BigQuery.

Whether you're looking to run a query based on a multiple values of a particular parameter, finding out the watermark for incremental loading or just doing some testing, you can DECLARE and SET variables you can reuse across your query.

A couple of ways to do it:
- we can declare a variable with no type, but we need to provide a default, from which the time will be inferred
- a variable can be declared together with the type and choose to assign a value at declaration or later with SET
- a sub query can also provide the implicit value and table of a variable

BigQuery SQL script with DECLARE ... DEFAULT CURRENT_TIMESTAMP() (implicit type), DECLARE fruits DEFAULT from a subquery giving ARRAY<STRUCT<STRING name, STRING taste>>, DECLARE another_timestamp TIMESTAMP, DECLARE a, b, c DEFAULT 0, and SET; the result shows both timestamps and apple/sweet, lemon/sour.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Enjoyed this? Here are some related articles you might find useful: