Scheduling queries

This page describes how to schedule recurring queries in BigQuery.

Beta

Esta es una versión Beta de scheduling queries in BigQuery. En este producto se pueden producir cambios que generen incompatibilidad con versiones anteriores y is sujetas a ningún ANS o política de baja del servicio.

Overview

You can schedule queries to run on a recurring basis. Scheduled queries must be written in standard SQL, which can include Data Definition Language (DDL) and Data Manipulation Language (DML) statements. The query string and destination table can be parameterized, allowing you to organize query results by date and time.

Before you begin

Before you create a scheduled query:

• Scheduled queries use features of the BigQuery Data Transfer Service. Verify that you have completed all actions required in Enabling the BigQuery Data Transfer Service.

• If you are creating the scheduled query by using the classic BigQuery web UI, allow pop-ups in your browser from bigquery.cloud.google.com, so that you can view the permissions window. You must allow the BigQuery Data Transfer Service permission to manage your scheduled query.

Required permissions

Before scheduling a query:

• Ensure that the person creating the transfer has the following required permissions in BigQuery:

  • bigquery.transfers.update permissions to create the transfer
  • bigquery.datasets.update permissions on the target dataset

  The bigquery.admin predefined, project-level IAM role includes bigquery.transfers.update and bigquery.datasets.update permissions. For more information on IAM roles in BigQuery, see Access control.

Configuration options

Query string

The query string must be valid and written in standard SQL. Each run of a scheduled query can receive the following query parameters.

Available parameters

Parameter	Standard SQL Type	Value
@run_time	TIMESTAMP	Represented in UTC time. For regularly scheduled queries, run_time represents the intended time of execution. For example, if the scheduled query is set to "every 24 hours", the run_time difference between two consecutive queries will be exactly 24 hours - even though the actual execution time might slightly vary.
@run_date	DATE	Represents a logical calendar date.

Example

The @run_time parameter is part of the query string in this example, which queries a public dataset named hacker_news.stories.

SELECT @run_time AS time,
  title,
  author,
  text
FROM `bigquery-public-data.hacker_news.stories`
LIMIT
  1000

Destination table

When you set up the scheduled query, if the destination table for your results doesn't exist, BigQuery attempts to create the table for you. If you are using a DDL or DML query, leave the destination table blank.

If the target table does exist, the destination table’s schema might be updated based on the query results if you add columns to the schema (ALLOW_FIELD_ADDITION) or relax a column's mode from REQUIRED to NULLABLE (ALLOW_FIELD_RELAXATION). In all other cases, table schema changes between runs cause the scheduled query to fail.

Queries can reference tables from different projects and different datasets. When configuring your scheduled query, you don't need to include the destination dataset in the table name. You specify the destination dataset separately.

Write preference

The write preference you select determines how your query results are written to an existing destination table. If you are using a DDL or DML query, leave write preference blank.

• WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data.
• WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.

Creating, truncating, or appending a destination table only happens if BigQuery is able to successfully complete the query. Creation, truncation, or append actions occur as one atomic update upon job completion.

Partitioning options

Scheduled queries can create partitioned or non-partitioned destination tables. If you are using a DDL or DML query, leave the partitioning field blank. There are two types of table partitioning in BigQuery:

• Tables partitioned by ingestion time: Tables partitioned based on the scheduled query's run time.
• Partitioned tables: Tables that are partitioned based on a TIMESTAMP or DATE column.

If the destination table will be partitioned on a column, you'll specify the column name when Setting up a scheduled query. Leave the Partitioning field blank for ingestion-time partitioned tables and non-partitioned tables.

Learn more about partitioning tables in Introduction to partitioned tables.

Partitioning examples

• Table with no partitioning
  • Destination table - mytable
  • Partitioning field - leave blank
• Ingestion-time partitioned table
  • Destination table - mytable$YYYYMMDD
  • Partitioning field - leave blank
• Column-partitioned table
  • Destination table - mytable
  • Partitioning field - name of the TIMESTAMP or DATE column used to partition the table

Available parameters

When setting up the scheduled query, you can specify how you want to partition the destination table with runtime parameters.

Parameter	Template Type	Value
run_time	Formatted timestamp	In UTC time, per the schedule. For regularly scheduled queries, run_time represents the intended time of execution. For example, if the scheduled query is set to "every 24 hours", the run_time difference between two consecutive queries will be exactly 24 hours - even though the actual execution time may vary slightly. See TransferRun.runTime.
run_date	Date string	The date of the run_time parameter in the following format: %Y%m%d; for example, 20180101. This format is compatible with ingestion-time partitioned tables.

Templating system

Scheduled queries support runtime parameters in the destination table name with a templating syntax.

Parameter templating syntax

The templating syntax supports basic string templating and time offsetting. Parameters are referenced in the following formats:

• {run_date}
• {run_time[+\-offset]|"time_format"}

Parameter	Purpose
run_date	This parameter is replaced by the date in format YYYYMMDD.
run_time	This parameter supports the following properties: offset Time offset expressed in hours (h), minutes (m), and seconds (s) in that order. Days (d) are not supported. Decimals are allowed, for example: 1.5h. time_format A formatting string. The most common formatting parameters are years (%Y), months (%m), and days (%d). For partitioned tables, YYYYMMDD is the required suffix - this is equivalent to "%Y%m%d". Read more about formatting datetime elements.

Usage notes:

• No whitespace is allowed between run_time, offset, and time format.
• To include literal curly braces in the string, you can escape them as ‘\{‘ and ‘\}’.
• To include literal quotes or a vertical bar in the time_format, such as “YYYY|MM|DD”, you can escape them in the format string as: ‘\”’ or ‘\|’.

Parameter templating examples

These examples demonstrate specifying destination table names with different time formats, and offsetting the run time.

run_time (UTC)	Templated parameter	Output destination table name
2018-02-15 00:00:00	mytable	mytable
2018-02-15 00:00:00	mytable_{run_time|"%Y%m%d"}	mytable_20180215
2018-02-15 00:00:00	mytable_+25h{run_time|"%Y%m%d"}	mytable_20180216
2018-02-15 00:00:00	mytable_-1h{run_time|"%Y%m%d"}	mytable_20180214
2018-02-15 00:00:00	mytable_+1.5h{run_time|"%Y%m%d;%H"} or mytable_+90m{run_time|"%Y%m%d;%H"}	mytable_2018021501
2018-02-15 00:00:00	{run_time+97s|"%Y%m%d"}_mytable_{run_time|"%H%M%s"}	20180215_mytable_000137

Setting up a scheduled query

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Run the query that you're interested in. When you are satisfied with your results, click Schedule query and Create new scheduled query.

   

3. The scheduled query options open in the New scheduled query pane.

4. On the New scheduled query pane:

   • For Name for the scheduled query, enter a name such as My scheduled query. The scheduled query name can be any value that allows you to easily identify the scheduled query if you need to modify it later.
   • (Optional) For Schedule options, you can leave the default value of Daily (every 24 hours, based on creation time), or click Schedule start time to change the time. You can also change the interval to Weekly, Monthly, or Custom. When selecting Custom, a Cron-like time specification is expected, for example every 3 hours. The shortest allowed period is fifteen minutes. See the schedule field under TransferConfig for additional valid API values.
   • For Dataset name, choose the appropriate destination dataset.
   • For Destination table:
     • For a standard SQL query, enter the name of your destination table.
     • For a DDL or DML query, leave this field blank.
   • For Destination table write preference, choose either WRITE_TRUNCATE to overwrite the destination table or WRITE_APPEND to append data to the table. For a DDL or DML query, this option is not available.

   • (Optional) Check Send email notifications to allow email notifications of transfer run failures.

     

5. Click Schedule.

6. To view the status of your scheduled queries, click Scheduled queries in the navigation pane. Refresh the page to see the updated status of your scheduled queries. Click on one to get more details about that scheduled query.

   

Classic UI

1. Go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Run the query that you're interested in. When you are satisfied with your results, click Schedule Query.

   

3. The scheduled query options open underneath the query box.

4. On the New Scheduled Query page:

   • For Destination Dataset, choose the appropriate dataset.
   • For Display Name, enter a name for the scheduled query such as My scheduled query. The scheduled query name can be any value that allows you to easily identify the scheduled query if you need to modify it later.
   • For Destination Table:
     • For a standard SQL query, enter the name of your destination table.
     • For a DDL or DML query, leave this field blank.
   • For Write Preference:
     • For a standard SQL query, choose either WRITE_TRUNCATE to overwrite the destination table or WRITE_APPEND to append data to the table.
     • For a DDL or DML query, choose Unspecified.

   • (Optional) For Partitioning Field:

     • For a standard SQL query, if the destination table is a column-partitioned table, enter the column name where the table should be partitioned. Leave this field blank for ingestion-time partitioned tables and non-partitioned tables.

     • For a DDL or DML query, leave this field blank.

       

   • (Optional) For Schedule, you can leave the default value of Daily (every 24 hours, based on creation time), or click Edit to change the time. You can also change the interval to Weekly, Monthly, or Custom. When selecting Custom, a Cron-like time specification is expected, for example every 3 hours. The shortest allowed period is fifteen minutes. See the schedule field under TransferConfig for additional valid API values.

     

   • (Optional) Expand the Advanced section and configure run notifications for your transfer. Transfer run notifications are currently in Alpha.

     • For Cloud Pub/Sub topic, enter your Cloud Pub/Sub topic name, for example, projects/myproject/topics/mytopic.

     • Check Send email notifications to allow email notifications of transfer run failures.

       

5. Click Add.

6. To view the status of your scheduled queries, click Scheduled queries in the navigation pane. Refresh the page to see the updated status of your scheduled queries. Click on one to get more details about that scheduled query.

   

Setting up a manual run on historical dates

In addition to scheduling a query to run in the future, you can also trigger immediate runs manually. Triggering an immediate run would be necessary if your query uses the run_date parameter, and there were issues during a prior run.

For example, every day at 09:00 you query a source table for rows that match the current date. However, you find that data wasn't added to the source table for the last three days. In this situation, you can set the query to run on historical data within a date range that you specify. Your query is run using combinations of run_date and run-time that correspond to the dates you configured in your scheduled query.

After setting up a scheduled query, here's how you can run the query by using a historical date range:

Console

After clicking Schedule to save your scheduled query, you can click the Scheduled queries button to see the list of currently-scheduled queries. Click any display name to see the query schedule's details. At the top right of the page, click Schedule backfill to specify a historical date range.

The run times chosen are all within your selected range, including the first date and excluding the last date.

Note that the date ranges you provide will be in UTC, but your query's schedule is displayed in your local time zone (see Example 2 below to work around this).

You can use the classic BigQuery UI to avoid this time zone discrepancy, and to set finer-grained time limits.

Example 1

Your scheduled query is set to run every day 09:00 Pacific Time. You're missing data from Jan 1, Jan 2, and Jan 3. Choose the following historic date range:

Start Time = 1/1/19
End Time = 1/4/19

Your query runs using run_date and run_time parameters that correspond to the following times:

• 1/1/19 09:00 Pacific Time
• 1/2/19 09:00 Pacific Time
• 1/3/19 09:00 Pacific Time

Example 2

Your scheduled query is set to run every day 23:00 Pacific Time. You're missing data from Jan 1, Jan 2, and Jan 3. Choose the following historic date ranges (later dates are chosen because UTC has a different date at 23:00 Pacific Time):

Start Time = 1/2/19
End Time = 1/5/19

Your query runs using run_date and run_time parameters that correspond to the following times:

• 1/2/19 09:00 UTC, or 1/1/2019 23:00 Pacific Time
• 1/3/19 09:00 UTC, or 1/2/2019 23:00 Pacific Time
• 1/4/19 09:00 UTC, or 1/3/2019 23:00 Pacific Time

After setting up manual runs, refresh the page to see them in the list of runs.

Classic UI

After clicking Add to save your scheduled query, you'll see the details of your scheduled query displayed. Below the details, click on the Start Manual Runs button to specify a historical date range.

You can further refine the date range to have a start and end time, or leave the time fields as 00:00:00.

Example 1

If your scheduled query is set to run every day 14:00, and you apply the following historic date range:

Start Time = 2/21/2018 00:00:00 AM
End Time = 2/24/2018 00:00:00 AM

Your query runs at the following times:

• 2/21/2018 14:00:00
• 2/22/2018 14:00:00
• 2/23/2018 14:00:00

Example 2

If your scheduled query is set to run every fri at 01:05 and you apply the following historic date range:

Start Time = 2/1/2018 00:00:00(a Thursday)
End Time = 2/24/2018 00:00:00 AM (also a Thursday)

Your query runs at the following times:

• 2/2/2018 01:05:00
• 2/9/2018 01:05:00

Quotas

A scheduled query is executed with the creator's credentials and project, as if you were executing the query yourself. The scheduled query is subject to all BigQuery Quotas and limits on query jobs.

Known issues and limitations

Regions

Cross-region queries are not supported, and the destination table for your scheduled query must be in the same region as the data being queried. See Dataset locations for more information about regions and multi-regions.

Google Drive

You can query Google Drive data in a scheduled query. If you're scheduling an existing query, you might need to click "Update Credentials" in the scheduled query details screen. Allow 10-20 minutes for the change to take effect. Your might need to clear your browser's cache. Credentials are automatically up to date for new scheduled queries.