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)
SELECT
stock_ticker,
price,
date,
company
FROM
`marquin-personal-tools.marquinsmith_dot_com.stock_price_daily_update_raw`
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.