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.
- 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
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 ```
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
