How to use variables in Google BigQuery

Posted by Pascal Landau on 2020-05-29 12:00:00

Variables are a good way to keep a query "clean" and separate parameters from code. In Google BigQuery, we can use variables in standard sql 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.

Code

DECLARE foo_var STRING DEFAULT "foo";

SELECT foo_var

Working Example

Run on BigQuery

Open in BigQuery Console

BigQuery Console: How to declare and use variables example

Links

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

Wanna stay in touch?

Since you ended up on this blog, chances are pretty high that you're into Software Development (probably PHP, Laravel, Docker or Google Big Query) and I'm a big fan of feedback and networking.

So - if you'd like to stay in touch, feel free to shoot me an email with a couple of words about yourself and/or connect with me on LinkedIn or Twitter or simply subscribe to my RSS feed or go the crazy route and subscribe via mail and don't forget to leave a comment :)

Subscribe to posts via mail

We use Mailchimp as our newsletter provider. By clicking subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.
Waving bear

Comments