When you want to run BigQuery queries on a recurring basis, BigQuery provides an amazing feature called Schedule Queries. Schedule queries can include data definition language (DDL) and data manipulation language (DML) statements and can be written in Standard SQL.
It is pretty easy to create and delete scheduled queries. All you need is BigQuery Data Transfer Service and some permission as mentioned in BigQuery Documentation.
We can store schedule query results in a separate Destination Table and can also append to the same table.
Now Let’s look into how to schedule queries.
First, go to BigQuery UI → write query you want to schedule → click on SCHEDULE → Create new schedule query
And then you’ll be ask for some parameters like,
- Name for scheduled query
- Schedule options
- Repeats – How frequently you need to run that query eg. everyday, every month etc
- Start date and run time – When schedule query should start
- End date – When you want to run schedule query for some predefined period of time, you can set end date
- Destination
- Table name
- Write preference (overwrite or append)
- Overwrite – query results will overwrite the data in the table
- Append – query results will be appended to the data in the table
To modify a scheduled query you can go to Go to BigQuery→ Scheduled queries → click on your schedule query (click on query1) → click on Edit → Write new query in the query editor → SCHEDULE → Update scheduled query.
To Delete/Disable scheduled query, Go to BigQuery → Scheduled queries → Delete/Disable
Note: Schedule Query is only available for projects with billing enabled. It won’t work for SANDBOX account projects.
Reference: