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 Cloud IAM role
includes bigquery.transfers.update and bigquery.datasets.update
permissions. For more information on Cloud IAM roles in
BigQuery, see Predefined roles and permissions.

Available parameters

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 is exactly 24 hours - even though the actual execution time might slightly vary.

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:

In the GCP Console, choose the Processing location or region.
Processing location is required for DDL or DML queries that create the
destination table.

In the classic BigQuery web UI, leave 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.

WRITE_TRUNCATE: If the table exists, BigQuery overwrites the
table data.

WRITE_APPEND: If the table exists, BigQuery appends the data
to the table.

If you are using a DDL or DML query:

In the GCP Console, the write preference option will not appear.

In the classic BigQuery web UI, leave the Write preference blank.

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.
Partitioning is not available in the GCP Console, but is available in
the classic BigQuery web UI, CLI and API setup methods. If you are using a DDL or DML
query with partitioning, 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.

Tables partitioned on a column: Tables that are partitioned based on a
TIMESTAMP
or DATE
column.

For tables partitioned on a column:

In the classic BigQuery web UI, if the destination table will be partitioned on a
column, you'll specify the column name in the Partitioning field when
Setting up a scheduled query. For
ingestion-time partitioned tables and non-partitioned tables, leave the
Partitioning field blank.

For ingestion-time partitioned tables:

Indicate the date partitioning in the destination table's name. See the
table name templating syntax, explained below.

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 is exactly 24 hours - even though the actual execution time may vary slightly.

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:

offsetTime 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_formatA 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".

Setting up a scheduled query

Console

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

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

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 15 minutes.
See the schedule field under TransferConfig
for more valid API values.

For DDL/DML queries, you'll choose the Processing location or region.

For a standard SQL SELECT query, provide information about the
destination dataset.

For Dataset name, choose the appropriate destination dataset.

For Table name, enter the name of your destination table.

For a DDL or DML query, this option is not shown.

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 shown.

(Optional) For Advanced options, if you use
customer-managed encryption keys,
you can select Customer-managed key here. A list of your
available CMEKs will appear for you to choose from.

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 one to get more details about that
scheduled query.

Classic UI

When you are satisfied with your results, click Schedule Query. The
scheduled query options open underneath the query box.

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.

(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 more valid API values.

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 one to get more details about that
scheduled query.

CLI

There are two ways to schedule a query by CLI.
With Option 2, you can schedule the query with more
options.

Option 1: Use the bq query command.

With this method, you will add the flags destination_table (or
target_dataset), --schedule and --display_name options to your
bq query command to create a scheduled query.

--replace will truncate the destination table and write new results with
every run of the scheduled query.

--append_table will append results to the destination table.

If both `--replace` and `--append_table` are not
specified when scheduling the query, no write preference will be set.
Depending on the query, this could cause an error in subsequent scheduled
runs.

For example, the following command creates a scheduled query named
My Scheduled Query using the simple query SELECT 1 from mydataset.test.
The destination table is mytable in the dataset mydataset. The scheduled
query is created in the default project:

Note: To write results to an ingestion-time partitioned table, see the
instructions in Destination table. A scheduled query
fails if you create a transfer configuration with the
destination_table_name_template parameter set to an ingestion-time
partitioned table while also supplying an error if setting
to an ingestion-time partitioned the partitioning_field parameter.Note: Currently, you cannot configure notifications using the command-line
tool.

For example, the following command creates a scheduled query transfer
configuration named My Scheduled Query using the simple query SELECT 1
from mydataset.test. The destination table mytable is truncated for every
write, and the target dataset is mydataset. The scheduled query is created
in the default project:

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.

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.

The date ranges you provide are 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 web 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/19End 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/19End 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.

API

Quotas

A scheduled query is executed with the creator's credentials and project,
as if you were executing the query yourself. A scheduled query is subject to
the same BigQuery Quotas and limits as
manual queries.

Pricing

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.
You might need to clear your browser's cache. Credentials are automatically up
to date for new scheduled queries.