Share on facebook
Share on linkedin
Share on twitter
Share on reddit

How to 10x your BigQuery Productivity with Variables

In this article, you’re going to learn how to create and use variables to 10x your productivity in BigQuery.
 
How much time do you spend repeatedly making small changes to your SQL?
 
Whether you’re updating a column’s value or changing a date in your partition, this can get annoying very fastEven more so when these values appear in multiple places in your code.
 
This is where variables save the day (and your precious time). Variables can help you make quick updates without touching your SQL.
 
The best part? It’s possible to set up variables without writing a single line of SQL.
 
But first…

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 using the Web UI. 

However, you still have to use the DECLARE and SET clauses, which can get tedious if you’re creating lots of variables or repeatedly changing values.

Next, we’re going to show you:

  1. How to quickly set up variables in superQuery without using any SQL.
  2. How to save TONS of time by using variables in your queries.

How to create and use variables in superQuery

In our example below, we’ll look at how to analyze an e-commerce store’s data using variables.

Step 1: Open the superQuery IDE — Make sure you have a superQuery account (it’s free to start)

Step 2: Click the “Add Variable” button to open the Variable Settings pop-up.

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

In the example below, since we’re analyzing e-commerce store data, let’s create a variable for product category.

For this, we’ll create a String variable, naming it “category” and giving it an initial value of “Bags“.

This replaces the variable-creation process of writing DECLARE and SET clauses.

Second Step of Adding Variables in superQuery. Select data type, give your variable a name and initial value.
Easily select your variable's data type, give it a name, and assign it an initial value.

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.

Quick notes on variables in superQuery:

  • You can use multiple variables in a query.
  • In addition, you may also use the same variable across several query tabs.
  • Lastly, variables are stored inside of boards. If you switch to work in another board, it will have a different set of variables (or none at all if you haven’t created any).
How to Edit Variable Values and use them in queries in superQuery
How to edit variable values and insert them into queries.

[Bonus] Step 6: How to use variables as DATE and DATETIME substitutes

Variables are incredibly helpful when you’re working with dates. In our example above, we were dealing with a specific date range — “BETWEEN ‘2017-01-01 AND ‘2017-12-27’“.

In this situation we would create two DATE variables — we’ll call them begDate and endDate — to replace the values. This allows us to dynamically change the date values and quickly examine different date ranges.

Lastly, you have the ability to set dynamic values for your date variables. For instance, if you wanted our endDate variable from above to always represent the current day, you would just select Today as the option.

To give your variable a dynamic value click on a date variable’s value. A calendar dropdown will appear. From there, either choose a date on the calendar itself, or one of the dynamic values below it.

How to set dynamic values for your DATE variables in superQuery
Set dynamic values for your DATE variables in superQuery

Why use variables?

Without variables, if you wanted to update your SQL — like changing the value of “category above — you had to manually find the value in your code and then change the value. Or, if you had multiple occurrences that needed to change, you’d use CTRL-F + Replace All.

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 are also useful when you’re visualizing your queries in superQuery.

When you update your variable(s) values while in Visualize mode and click the “Refresh” icon, any widgets containing a variable  

This is the same as running a new query, and your visualizations will update accordingly.

Wrapping up

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.

To use variables in your query editor, create a superQuery account — it’s as simple as logging with your Google account directly from our website.

  • © 2019 superQuery. All rights reserved.

We use 🍪 to ensure you get the best experience.