What are variables?
Sometimes referred to as parameters, variables are placeholders in SQL for values that can change. Variables store the value you assign them, and pass them on when used in a query.
They’re extremely helpful when you want to update literal values in your queries.
Typically, DECLARE and SET statements are used to create variables and assign them values, with the syntax looking like:
DECLARE @Local_Variable SET<@Local_Variable =
Google allows you to run something similar to variables in BigQuery, called parameterized queries, but it only works via their API and not when querying in the editor.
Next, we’re going to show you:
- How to set up variables without using any SQL.
- How to use variables in your query editor to save you LOADS of time.
How to create a variable in BigQuery
Step 3: Select your variable’s data type.
In superQuery, variables can hold the following data types: STRING, NUMBER, DATE, DATETIME.
Step 4: Give your variable a good name and click “Save”.
For example, if you’re analyzing Facebook ad data, you could create a String variable named “min_cpc” that has an initial value of “3” (i.e. $3)
Step 5: Use your variable in a query.
Use variables in your SQL by placing an “@” character in front of the variable’s name, like in the example below.
To edit your variable’s value, change it in the text box next to your variable’s name. Then run the query again to explore the new results.
You can use multiple variables in a single query. You can also use a single variable across several query tabs
Why use variables?
Without variables, if you wanted to update your SQL — like changing the value of “min_cpc” above — you had to manually find the value in your code and then change the value.
Imagine having to find the specific value you’d like to change when the SQL is 100+ lines.
Now, you can insert a variable in place of any value and update its value without needing to revisit a specific line in your SQL again.
Using variables with visualizations
Variables can also be useful when you’re visualizing your queries in superQuery.
Update your queries while viewing them in Visualize mode by changing the variable’s value and clicking the “Refresh” icon to its left.
This is the same as running a new query, and your visualizations will update accordingly.
Now that you know how to create and use variables, it’s time to try them out on your own BigQuery data.
They’ll give your SQL some flexibility, and save you tons of time when making small changes.