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

How to 10x your BigQuery Productivity with Variables

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.
 
In this article, you’re going to learn how to create and use variables to 10x your productivity in BigQuery.
 
But first…

What are variables?

Put simply, 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 in the editor.

Now, we’re going to show you how to set up variables without using any SQL.

How to create a variable in BigQuery

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.

Location of Add Variable button

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 user event data, you would create a String variable named “event” that has a value of “login”.

Variable Settings in superQuery

Step 5: Use your variable in a query.

Use variables in your SQL by placing an “@” 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.

You can use multiple variables in a single query. You can also use a single variable across several query tabs

Example Variable in superQuery

How can I use variables?

Let’s continue with our example of analyzing usage data for an app. Suppose you filter this data according to various in-app events, a specific user ID, or by date.

As mentioned earlier, if you wanted to update your SQL — like changing the user ID above — you had to manually find the value in your code.

Now, you can insert a variable in place of the user ID value and update its value without needing to revisit 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.

Using variables when visualizing your queries

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 gain access to variables, add superQuery via the Chrome Store or login with your Google account directly from our website.

Ready to run perfect queries?

Free to get started. No credit card required.

  • © 2019 superQuery. All rights reserved.