Running SQL Queries on a schedule in BigQuery | Part 1: Scheduled Queries

A short walkthrough guide of setting up a scheduled query in BigQuery. The Google documentation for scheduled queries is here.

Step 1: Create a query in the bigQuery interface that is going to be run a scheduled intervals

INSERT `marquin-personal-tools.marquinsmith_dot_com.daily_stock_price` (stock_ticker, price, date, company) 
WHERE stock_ticker IS NOT null

Step 2: Click the “schedule” button to open the settings form sidebar to configure the schedule

Step 3: Configure the settings for the scheduled query:

Give the scheduled query a name and decide a frequency

If the query to be run regularly returns data (is a SELECT statement query) then it is possible to have those results populate into a table which is configured as part of this set up. With the results either be appended to the table or overwrite any potential data that is there already.

If the query to be run regularly does not return data (as in this case with an INSERT statement) then this section can be left blank.

Failure notifications can be set up (recommended), for instances when the scheduled query does not run for any reason

Step 4: Click Save

This is what the configuration settings look like for the example query in this walkthrough

And that’s it. A scheduled query in BigQuery has now been set up and configured!!

See all scheduled queries

To see all of the scheduled queries that have been set up in the GCP project, navigate to the “Scheduled Queries” section within BigQuery by selecting it from the left hand side menu.

Each row on the table on the “Scheduled Queries” is a scheduled query that has been set up. And at a glance as part of the table you can see the next run time and the name of each one.

Send a Comment

Your email address will not be published.