Free PHPFree PHP scripts classes and info

BigQuery: Declare and use Variables

We can use variables by defining them with a DECLARE statement, e.g.

DECLARE foo STRING DEFAULT "foo";

#DECLARE <variable> <type> DEFAULT <value>;

with <type> being one of the BigQuery's built-in standard-sql data types

This is equivalent to variables of other SQL databases, e.g.

• MySQL variables:

  SET @foo = 'bar';

• PostgreSQL variables:

  foo varchar := 'bar';

Code

DECLARE foo_var STRING DEFAULT "foo";

SELECT foo_var

Working Example

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery
• Answer to "Setting Big Query variables like mysql" on Stackoverflow

Use cases

Hardcoding variables is generally considered a bad practice as it makes it harder to understand and modify a query. A frequent use case for me is the definition of date ranges (from and to dates) that are used for querying partitioned tables:

DECLARE from_date DATE DEFAULT DATE("2018-04-09");
DECLARE to_date DATE DEFAULT DATE("2018-04-10");

WITH data as(
  SELECT
    1 as id,
    DATE("2018-04-08") AS date,
  UNION ALL SELECT 2, DATE("2018-04-09")
  UNION ALL SELECT 3, DATE("2018-04-10")
  UNION ALL SELECT 4, DATE("2018-04-11")
)

SELECT
    id,
    date
FROM
    data
WHERE
    date BETWEEN from_date AND to_date