How much time do you spend repeatedly making small changes to your SQL?
When you find yourself constantly updating a WHERE clause in your query, then it's time to insert variables into your SQL.
Variables (sometimes called parameters) are placeholders for values that can change in your SQL.
They make your queries more readable and allow you to perform quick changes on your query without modifying the SQL itself.
The best part? You can create and modify them in superQuery without using any SQL! No DECLARE or SET clauses required.
In the example below, our query is finding the best-performing products for an eCommerce store, for a given category. Currently, the query is examining the "Drinkware" category.
Let's see how variables can help us here.
Click on the (x) icon to open the variables side panel and create your first variable.
Then click on the purple "Add a variable" button or the "+" Add Variable button to create a new variable.
Since we're exploring categories in our example, we will name our variable "category", and give it an initial value of "Apparel" in order to explore a different category.
Variables can hold the following data types: String, Number, Date, Datetime, Time, and Array.
In our example with the variable representing a product category, we will make our variable a String.
Once created, insert your variable into your variable by replacing a field with it.
To use a variable in your SQL, place an '@' character in front of the variable’s name, like the example below.
In our case, we'll replace "Drinkware" with a variable named "category" that currently holds a value of "Apparel"
There will be cases when you'll want to change your variable's value. For example, we might want to switch our variable's value from "Apparel" to "Office".
To edit your variable's value, simply update the text box corresponding to the variable.
Then re-run the query to have your results reflect the change in variable value, as shown below.
Beyond replacing numbers and strings, variables are great for holding date values — perfect if you're modifying date ranges, as we'll see below.
Using our previous example, we can replace the date range referred to in line 8 below with two variables
SELECTProductCategory__Enhanced_E_commerce AS category,product,ROUND(SUM(revenue),1) AS revenues,COUNT(*) AS totalPurchasesFROM test-data-211411.google_store_data.online_purchasesWHERE ProductCategory__Enhanced_E_commerce = '@category'AND transaction_date BETWEEN '2017-01-01' AND '2017-09-30'GROUP BY 2,1ORDER BY 3 asc;
First, we'll create two Date variables called "begDate" and "endDate" which hold the values "2017-01-01" and "2017-09-30", respectively.
Then insert the two variables in place of the date values that were originally there.
Arrays are multi-value columns in BigQuery, where an array field contains more than one value (of the same data type). You can create variables that contain arrays, and from there, "check" which fields you'd like to include in your query, as shown below.
You can then use your array variable in your queries — specifically after the IN clause.
To create an Array variable, after selecting the Array data type, enter all of the possible values your array will hold.
You can then modify the values the Array holds by checking the boxes next to fields you want or don't want to include, then re-run the query:
Variables are great for replacing table and dataset names in cases where you find yourself modifying table names a lot. Additionally, if you are constantly changing the date portion of a date-partitioned table, variables make it much easier to do that.
First, create a string variable and uncheck the "Add quotes automatically" box. This will let us use our variable between the tick marks where you'd typically write `project.dataset.table`.
In our example below, we're creating a variable called "table" that holds the value of our table "online_purchases".
Finally replace the table name in your query with your variable, in our case "@table".
That's it! The same thing works for datasets and date-partitioned tables. For the latter, you'd create a string variable that contains the date suffix, for example "20200225" and insert the variable after the table name like below:
Watch our 3-minute variables tutorial below: