CODEWITHSUNDEEP
google-bigquery
loadbox
loadbox

Reputation: 656

How can I use a variable on BigQuery SQL Editor?

While writing SQL queries on BigQuery UI, sometimes I do a lot of JOIN over multiple tables using many WHERE clauses with the same date condition for each table. Whenever I need to see the results for a different date, I have to replace it at multiple locations in the query. I wonder if there is a simple way to use a variable in the BQ SQL Editor and pass it just once (top/bottom)?

This is true for all the complicated queries as we have to search throughout the query for variables to change.

Upvotes: 0

Views: 8507

Answers (1)

Alexandre Moraes
Alexandre Moraes

Reputation: 4051

While parameterized queries are not available in the Console. You can use Scripting, instead.

According to your need, you can use DECLARE and/or SET. It is stated in the documentation that:

DECLARE: Declares a variable of the specified type. If the DEFAULT clause is specified, the variable is initialised with the value of the expression; if no DEFAULT clause is present, the variable is initialised with the value NULL

The syntax is as follows: 

#Declaring the variable's type and initialising the variable using DEFAULT
DECLARE variable STRING DEFAULT 'San Francisco';

SELECT * FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_regions` 
#using the variable inside the where clause
WHERE name = variable

SET: Sets a variable to have the value of the provided expression, or sets multiple variables at the same time based on the result of multiple expressions.

And the syntax, as below: 

#Declaring the variable using Declare and its type
DECLARE variable STRING;
#Initialising the variable
SET variable = 'San Francisco';

SELECT * FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_regions` 
#using the variable inside the where clause
WHERE name = variable

In both examples above, I have queried against a public dataset bigquery-public-data.san_francisco_bikeshare.bikeshare_regions. Also, both outputs are the same,

Row region_id   name    
1   3           San Francisco

In addition to the above example, more specifically to your case, when declaring a variable as date you can to it as follows:

DECLARE data1 DATE DEFAULT DATE(2019,02,15);


WITH sample AS(
SELECT DATE(2019,02,15) AS date_s, "Some other field!" AS string UNION ALL
SELECT DATE(2019,02,16) AS date_s, "Some other field!" AS string UNION ALL
SELECT DATE(2019,02,17) AS date_s, "Some other field!" AS string UNION ALL
SELECT DATE(2019,02,18) AS date_s, "Some other field!" AS string 
)
SELECT * FROM sample
WHERE date_s = data1

And the output,

Row    date_s       string1
1      2019-02-15   Some other field!

Upvotes: 4

Related Questions