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 foo STRING DEFAULT "foo"; #DECLARE <variable> <type> DEFAULT <value>;
<type> being one of the BigQuery's built-in standard-sql data types
This is equivalent to variables of other SQL databases, e.g.
DECLARE foo_var STRING DEFAULT "foo"; SELECT foo_var
Run on BigQuery
- Gist on Github
- Example on BigQuery
- Answer to "Setting Big Query variables like mysql" on Stackoverflow
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 :)