DECLARE and SET variables in BigQuery

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

DECLARE start_timestamp DEFAULT CURRENT_TIMESTAMP(); -- declare with default value, implicit type

DECLARE fruits DEFAULT (
  SELECT [STRUCT('apple' AS name, 'sweet' AS taste  ), STRUCT('lemon','sour')]
); --- declare with implicit type from subquery
   --- ARRAY<STRUCT<STRING name, STRING taste>>

DECLARE another_timestamp TIMESTAMP; -- declare with explicit type

DECLARE a, b, c DEFAULT 0; -- declare multiple values at once with a single default

SET another_timestamp = TIMESTAMP '2021-01-01 17:00:00'; -- set a previous declared variable


SELECT start_timestamp, another_timestamp, fruits;

---

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

• Parameters in BigQuery