安排查询

本页面介绍如何在 BigQuery 中安排周期性查询。

概览

您可以安排定期运行的查询。计划查询必须使用标准 SQL 编写,其中可包括数据定义语言 (DDL)数据操纵语言 (DML) 语句。查询字符串和目标表支持参数化,因此您可以按日期和时间整理查询结果。

准备工作

在创建计划查询之前,请先做好以下准备:

  • 计划查询使用 BigQuery Data Transfer Service 的功能。验证您是否已完成启用 BigQuery Data Transfer Service 中要求的所有操作。

  • 如果您使用经典版 BigQuery 网页界面创建计划查询,请在浏览器中允许来自 bigquery.cloud.google.com 的弹出式窗口,这样才能看到权限窗口。您必须允许 BigQuery Data Transfer Service 管理您的计划查询。

所需权限

在安排查询之前,请先做好以下准备:

  • 确保创建转移作业的用户在 BigQuery 中拥有以下所需权限:

    • 创建转移作业所需的 bigquery.transfers.update 权限
    • 目标数据集的 bigquery.datasets.update 权限

    预定义的 Cloud IAM 角色 bigquery.admin 具有 bigquery.transfers.updatebigquery.datasets.update 权限。如需详细了解 BigQuery 中的 Cloud IAM 角色,请参阅访问权限控制

配置选项

查询字符串

查询字符串必须有效,并且必须使用标准 SQL 编写。每次运行计划查询都可以接收以下查询参数

如需在计划查询之前使用 @run_time@run_date 参数手动测试查询字符串,请使用命令行界面。

可用参数

参数 标准 SQL 类型
@run_time TIMESTAMP 以世界协调时间 (UTC) 表示。对于安排为定期执行的查询,run_time 表示预期的执行时间。例如,如果计划查询设置为“每 24 小时”,则连续两次查询之间的 run_time 差值将正好为 24 小时,虽然实际的执行耗时可能略有不同。
@run_date DATE 表示逻辑日历日期。

示例

在本示例查询名为 hacker_news.stories 的公共数据集,其中 @run_time 参数是查询字符串的一部分。

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

目标表

设置计划查询时,如果结果的目标表不存在,BigQuery 会尝试为您创建表。

如果您使用的是 DDL 或 DML 查询,请执行以下操作:

  • 在 GCP Console 中,选择处理位置或区域。创建目标表的 DDL 或 DML 查询需要处理位置。
  • 在经典版 BigQuery 网页界面中,将目标表留空。

如果目标表确实存在,并且您向架构 (ALLOW_FIELD_ADDITION) 添加了列或将列模式从 REQUIRED 放宽为 NULLABLE (ALLOW_FIELD_RELAXATION),则目标表的架构可能会更新(基于查询结果)。在其他所有情况下,如果表架构在多轮运行之间发生更改,则会导致计划查询失败。

查询可以引用不同项目和不同数据集的表。配置计划查询时,您无需在表名称中包括目标数据集。您可以单独指定目标数据集。

写入偏好设置

您选择的写入偏好设置决定了将查询结果写入现有目标表的方式。

  • WRITE_TRUNCATE:如果相关表已存在,BigQuery 将覆盖表数据。
  • WRITE_APPEND:如果相关表已存在,BigQuery 会将数据附加到此表中。

如果您使用的是 DDL 或 DML 查询,请执行以下操作:

  • 在 GCP Console 中,不会显示写入偏好设置选项。
  • 在经典版 BigQuery 网页界面中,将写入偏好设置留空。

仅当 BigQuery 能够成功完成查询时,才会创建、截断或附加目标表。作业完成时,创建、截断或附加操作会作为原子更新发生。

聚簇

当使用 DDL CREATE TABLE AS SELECT 语句创建表时,计划查询只能在新表上创建聚簇。请参阅使用数据定义语言语句页面上的基于查询结果创建聚簇表

分区选项

计划查询可以创建分区或非分区目标表。GCP Console 中不提供分区功能,但在经典版 BigQuery 网页界面、CLI 和 API 设置方法提供此功能。如果您正在使用带分区功能的 DDL 或 DML 查询,请将分区字段留空。

BigQuery 中有两种类型的表分区:

  • 按提取时间分区的表:根据计划查询的运行时间分区的表。
  • 按列分区的表:根据 TIMESTAMPDATE 列分区的表。

对于按列分区的表:

  • 在经典版 BigQuery 网页界面中,如果目标表将根据某一列进行分区,则您需要在设置计划查询时,在 Partitioning field 指定该列的名称。对于按提取时间分区的表和未分区的表,请将 Partitioning field 留空。

对于按提取时间分区的表:

  • 在目标表名称中指明日期分区。请参阅下面所述的表名称模板语法。

分区示例

  • 没有分区的表
    • 目标表 - mytable
    • 分区字段 - 留空
  • 提取时间分区表
    • 目标表 - mytable$YYYYMMDD
    • 分区字段 - 留空
  • 列分区表
    • 目标表 - mytable
    • 分区字段 - 用于对表进行分区的 TIMESTAMPDATE 列的名称

可用参数

设置计划查询时,您可以使用运行时参数指定对目标表进行分区的方式。

参数 模板类型
run_time 带格式的时间戳 采用世界协调时间 (UTC),基于计划。对于安排为定期执行的查询,run_time 表示预期的执行时间。例如,如果计划查询设置为“每 24 小时”,则连续两次查询之间的 run_time 差值将正好为 24 小时,虽然实际的执行耗时可能略有不同。

请参阅 TransferRun.runTime
run_date 日期字符串 run_time 参数的日期采用 %Y%m%d 格式;例如 20180101。此格式与按提取时间分区的表兼容。

模板系统

计划查询支持通过模板语法在目标表名称中采用运行时参数。

参数模板语法

模板语法支持基本的字符串模板和时间偏移量设置。您可以采用以下格式来引用参数:

  • {run_date}
  • {run_time[+\-offset]|"time_format"}
参数 目的
run_date 此参数将替换成格式为 YYYYMMDD 的日期。
run_time 此参数支持以下属性:


offset
时间偏移量按小时 (h)、分钟 (m) 和秒 (s) 的顺序表示。
不支持天 (d)。
允许使用小数,例如:1.5h

time_format
带格式的字符串。最常见的格式参数是年 (%Y)、月 (%m) 和日 (%d)。
对于分区表,YYYYMMDD 是必需的后缀,等效于“%Y%m%d”。

详细了解带格式的 datetime 元素

使用说明:
  • run_time、offset 和 time_format 之间不允许有空格。
  • 如需在字符串中包含文本大括号,您可以将其转义为 ‘\{‘ and ‘\}’
  • 如需在 time_format 中包含文本引号或竖线(例如“YYYY|MM|DD”),您可以在格式字符串中将其转义为:‘\”’‘\|’

参数模板示例

以下示例演示了如何使用不同的时间格式指定目标表名称,以及如何设置运行时间的偏移量。
run_time (UTC) 模板化参数 输出目标表名称
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_{run_time+25h|"%Y%m%d"} mytable_20180216
2018-02-15 00:00:00 mytable_{run_time-1h|"%Y%m%d"} mytable_20180214
2018-02-15 00:00:00 mytable_{run_time+1.5h|"%Y%m%d;%H"}

mytable_{run_time+90m|"%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

设置计划查询

控制台

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 GCP Console

  2. 运行所需查询。如果您对结果感到满意,请点击计划查询创建新的计划查询

    BigQuery 网页界面中的计划查询

  3. 计划查询选项在新建计划查询窗格中打开。

  4. 新建计划查询窗格上:

    • 计划查询的名称 (Name for the scheduled query) 部分,输入名称,例如 My scheduled query。计划查询的名称可以是任何容易识别的值,以便您以后在需要修改计划查询时能够轻松识别该查询。
    • (可选)在计划选项部分,您可以保留默认值每日一次(基于创建时间,每 24 小时一次),或点击计划开始时间更改时间。您还可以将间隔时间更改为“每周”、“每月”或“自定义”。如果选择“自定义”,则需采用类似 Cron 作业使用的时间规范,例如 every 3 hours。允许的最短时长为十五分钟。如需了解其他有效的 API 值,请参阅 TransferConfig 下的 schedule 字段。

      新的计划查询顶部

  5. 对于 DDL/DML 查询,请选择处理位置或区域。

    新的计划查询 DDL/DML

  6. 对于标准 SQL SELECT 查询,请提供目标数据集的相关信息。

    • 数据集名称部分,选择适当的目标数据集。
    • 对于表名称,请输入目标表的名称。
      • 对于 DDL 或 DML 查询,此选项不显示。
    • 对于目标表的写入设置,选择 WRITE_TRUNCATE 覆盖目标表,或选择 WRITE_APPEND 将数据附加到表中。
      • 对于 DDL 或 DML 查询,此选项不显示。
    • (可选)对于高级选项,如果您使用客户管理的加密密钥,则可以在此处选择客户管理的密钥。系统会显示一系列可用的 CMEK 供您选择。

      新的计划查询底部

  7. 对于所有查询:

  8. 点击计划

  9. 如需查看计划查询的状态,请点击导航窗格中的计划查询。刷新页面即可查看计划查询的更新状态。点击其中一项可获取有关该计划查询的详细信息。

    列出计划查询

经典版界面

  1. 转到经典版 BigQuery 网页界面。

    转到经典版 BigQuery 网页界面

  2. 运行所需查询。

    经典版 BigQuery 网页界面中的计划查询

  3. 如果您对结果感到满意,请点击 Schedule Query。计划查询选项随即会在查询框下方打开。

  4. New Scheduled Query 页面上:

    • Destination dataset 部分,选择相应的数据集。
    • Display Name 部分,为计划查询输入名称,例如 My scheduled query。计划查询的名称可以是任何容易识别的值,以便您以后在需要修改计划查询时能够轻松识别该查询。
    • Destination table 部分:
      • 对于标准 SQL 查询,请输入目标表的名称。
      • 对于 DDL 或 DML 查询,请将此字段留空。
    • Write Preference 部分:
      • 对于标准 SQL 查询,请选择 WRITE_TRUNCATE 覆盖目标表,或选择 WRITE_APPEND 将数据附加到表中。
      • 对于 DDL 或 DML 查询,请选择 Unspecified
    • (可选)在 Partitioning Field 中:

      • 对于标准 SQL 查询,如果目标表是按列分区的表,请输入用于对表进行分区的列名称。对于按提取时间分区的表和未分区的表,请将此字段留空。
      • 对于 DDL 或 DML 查询,请将此字段留空。
    • (可选)对于 Destination table KMS key,如果您使用客户管理的加密密钥,可以在此处输入客户管理的加密密钥。

      新建计划查询

    • (可选)对于 Schedule,您可以保留默认值 Daily(基于创建时间,每 24 小时一次),或点击 Edit 以更改时间。您还可以将间隔时间更改为“Weekly”、“Monthly”或“Custom”。如果选择“Custom”,则需采用类似 Cron 作业使用的时间规范,例如 every 3 hours。允许的最短时长为十五分钟。如需了解其他有效的 API 值,请参阅 TransferConfig 下的 schedule 字段。

      查询计划

    • (可选)展开 Advanced 部分,然后配置 notifications

      • Cloud Pub/Sub topic 部分,输入您的 Cloud Pub/Sub 主题名称,例如 projects/myproject/topics/mytopic
      • 勾选 Send email notifications,让系统在转移作业运行失败时发送电子邮件通知。

        Cloud Pub/Sub 主题

  5. 点击 Add

  6. 如需查看计划查询的状态,请点击导航窗格中的 Scheduled queries。刷新页面即可查看计划查询的更新状态。点击其中一项可获取有关该计划查询的详细信息。

    列出计划查询

CLI

第一种方法:使用 bq query 命令。

如果使用此方法,您将向 bq query 命令添加标志 destination_table(或 target_dataset)、--schedule--display_name 选项以创建计划查询。

bq query \
--display_name=name \
--destination_table=table \
--schedule=interval

其中:

  • name 是计划查询的显示名。显示名可以是任何容易辨识的值,让您以后在需要修改时能够轻松识别计划查询。
  • table 是查询结果的目标表
    • --target_dataset 是在搭配 DDL/DML 查询使用时为查询结果命名目标数据集的另一种方法。
    • 您可以使用 --destination_table--target_dataset,但不能同时使用两者。
  • interval,搭配 bq query 使用时,将执行周期性计划查询。需要有关查询运行频率的计划。示例:
    • --schedule='every 24 hours'
    • --schedule='every 3 hours'

可选标志:

  • --project_id 是您的项目 ID。如果未指定 --project_id,系统会使用默认项目。

  • --replace 会截断目标表,并在计划查询每次运行时写入新结果。

  • --append_table 会将结果附加到目标表。

例如,以下命令使用 SELECT 1 from mydataset.test 简单查询创建名为 My Scheduled Query 的计划查询。目标表是数据集 mydataset 中的 mytable。计划查询将在默认项目中创建:

    bq query \
    --use_legacy_sql=false \
    --destination_table=mydataset.mytable \
    --display_name='My Scheduled Query' \
    --replace=true \
    'SELECT
      1
    FROM
      mydataset.test'


第二种方法:使用 bq mk 命令。

计划查询是一种转移作业。如需计划查询,您可以使用 BigQuery Data Transfer Service CLI 进行转移作业配置。

拟安排的计划查询必须使用 StandardSQL 方言。

输入 bq mk 命令并提供转移作业创建标志 --transfer_config。此外,还必须提供以下标志:

  • --data_source
  • --target_dataset(对于 DDL/DML 查询,此为可选项。)
  • --display_name
  • --params

可选标志:

  • --project_id 是您的项目 ID。如果未指定 --project_id,系统会使用默认项目。

  • --schedule 是您希望查询运行的频率。如果未指定 --schedule,则默认基于创建时间“每 24 小时运行一次”。

  • 对于 DDL/DML 查询,您还可以提供 --location 标志以指定要处理的特定区域。如果未指定 --location,将使用全球 Google Cloud Platform 位置。

bq mk \
--transfer_config \
--project_id=project_id \
--target_dataset=dataset \
--display_name=name \
--params='parameters' \
--data_source=data_source

其中:

  • dataset 是转移作业配置的目标数据集
    • 对于 DDL/DML 查询,此参数是可选的。所有其他查询都必须设置此参数。
  • name 是转移作业配置的显示名。显示名可以是任何容易辨识的值,让您以后在需要修改时能够轻松识别计划查询(转移作业)。
  • parameters 包含所创建的转移作业配置的参数(采用 JSON 格式)。例如:--params='{"param":"param_value"}'。对于计划查询,您必须提供 query 参数。
    • destination_table_name_template 参数是目标表的名称。
    • 对于 DDL/DML 查询,此参数是可选的。所有其他查询都必须设置此参数。
    • 对于 write_disposition 参数,您可以选择 WRITE_TRUNCATE 截断(覆盖)目标表,或选择 WRITE_APPEND 将查询结果附加到目标表。
      • 对于 DDL/DML 查询,此参数是可选的。所有其他查询都必须设置此参数。
    • (可选)destination_table_kms_key 参数用于客户管理的加密密钥
  • data_source 是数据源,即 scheduled_query

例如,以下命令使用 SELECT 1 from mydataset.test 简单查询创建名为 My Scheduled Query 的计划查询转移作业配置。每次写入时,目标表 mytable 都将被截断,目标数据集为 mydataset。计划查询将在默认项目中创建:

bq mk \
--transfer_config \
--target_dataset=mydataset \
--display_name='My Scheduled Query' \
--params='{"query":"SELECT 1 from mydataset.test","destination_table_name_template":"mytable","write_disposition":"WRITE_TRUNCATE"}' \
--data_source=scheduled_query

首次运行此命令时,您会收到如下消息:

[URL omitted] Please copy and paste the above URL into your web browser and follow the instructions to retrieve an authentication code.

请按照该消息中的说明操作,并将身份验证代码粘贴到命令行中。

API

使用 projects.locations.transferConfigs.create 方法并提供 TransferConfig 资源的实例。

Python

在尝试此示例之前,请先按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

    from google.cloud import bigquery_datatransfer_v1
    import google.protobuf.json_format

    client = bigquery_datatransfer_v1.DataTransferServiceClient()

    # TODO(developer): Set the project_id to the project that contains the
    #                  destination dataset.
    # project_id = "your-project-id"

    # TODO(developer): Set the destination dataset. The authorized user must
    #                  have owner permissions on the dataset.
    # dataset_id = "your_dataset_id"

    # TODO(developer): The first time you run this sample, set the
    # authorization code to a value from the URL:
    # https://www.gstatic.com/bigquerydatatransfer/oauthz/auth?client_id=433065040935-hav5fqnc9p9cht3rqneus9115ias2kn1.apps.googleusercontent.com&scope=https://www.googleapis.com/auth/bigquery%20https://www.googleapis.com/auth/drive&redirect_uri=urn:ietf:wg:oauth:2.0:oob
    #
    # authorization_code = "_4/ABCD-EFGHIJKLMNOP-QRSTUVWXYZ"
    #
    # You can use an empty string for authorization_code in subsequent runs of
    # this code sample with the same credentials.
    #
    # authorization_code = ""

    # Use standard SQL syntax for the query.
    query_string = """
SELECT
  CURRENT_TIMESTAMP() as current_time,
  @run_time as intended_run_time,
  @run_date as intended_run_date,
  17 as some_integer
"""

    parent = client.project_path(project_id)

    transfer_config = google.protobuf.json_format.ParseDict(
        {
            "destination_dataset_id": dataset_id,
            "display_name": "Your Scheduled Query Name",
            "data_source_id": "scheduled_query",
            "params": {
                "query": query_string,
                "destination_table_name_template": "your_table_{run_date}",
                "write_disposition": "WRITE_TRUNCATE",
                "partitioning_field": "",
            },
            "schedule": "every 24 hours",
        },
        bigquery_datatransfer_v1.types.TransferConfig(),
    )

    response = client.create_transfer_config(
        parent, transfer_config, authorization_code=authorization_code
    )

    print("Created scheduled query '{}'".format(response.name))

设置历史日期的手动运行

除了计划要在将来运行的查询外,您还可以通过手动触发即刻运行。如果查询使用 run_date 参数,并且在先前的运行期间存在问题,则必须触发即刻运行。

例如,您每天 09:00 查询源表,以查找与当前日期匹配的行。但是,您发现过去三天的数据未添加到源表中。在此情况下,您可以设置对指定日期范围内的历史数据运行查询。运行查询时结合使用 run_daterun-time,它们对应于计划查询中配置的日期。

设置计划查询后,您可以根据以下说明,使用历史日期范围运行查询:

控制台

点击计划保存计划查询后,点击计划查询按钮可查看包含当前计划查询的列表。点击任何显示名可查看查询计划的详细信息。在页面右上角,点击安排回填可指定历史日期范围。

“安排回填”按钮

所选的运行时间均在您选择的范围内(包括第一个日期,不包括最后一个日期)。

设置历史日期

示例 1

您设置了 every day 09:00(太平洋时间)运行您的计划查询,但是您缺少了 1 月 1 日、1 月 2 日和 1 月 3 日的数据。选择以下历史日期范围:

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

您的查询将使用对应于以下时间的 run_daterun_time 参数运行:

  • 19/1/1 09:00 太平洋时间
  • 19/1/2 09:00 太平洋时间
  • 19/1/3 09:00 太平洋时间

示例 2

您设置了 every day 23:00(太平洋时间)运行您的计划查询,但是您缺少了 1 月 1 日、1 月 2 日和 1 月 3 日的数据。选择以下历史日期范围(选择较晚的日期,是因为太平洋时间 23:00 已经是 UTC 的次日):

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

您的查询将使用对应于以下时间的 run_daterun_time 参数运行:

  • 19/1/2 09:00 UTC,或 2019/1/1 23:00 太平洋时间
  • 19/1/3 09:00 UTC,或 2019/1/2 23:00 太平洋时间
  • 19/1/4 09:00 UTC,或 2019/1/3 23:00 太平洋时间

设置手动运行后,刷新页面即可在运行列表中查看它们。

经典版界面

点击 Add 保存计划查询后,将显示计划查询的详细信息。在详细信息下方,点击 Start Manual Runs 按钮以指定历史日期范围。

“启动手动运行”按钮

您可以进一步优化日期范围,设置开始和结束时间,也可以将时间字段保留为 00:00:00

设置历史日期

示例 1

如果您的计划查询运行时间设置为 every day 14:00,并且您应用了以下历史日期范围:

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

您的查询会在以下时间运行:

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

示例 2

如果您的计划查询运行时间设置为 every fri at 01:05,并且您应用了以下历史日期范围:

Start Time = 2/1/2018 00:00:00(星期四)
End Time = 2/24/2018 00:00:00 AM(也是星期四)

您的查询会在以下时间运行:

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

CLI

如需在历史日期范围内手动运行查询,请执行以下操作:

输入 bq mk 命令并提供转移作业运行标志 --transfer_run。此外,还必须提供以下标志:

  • --start_time
  • --end_time
bq mk \
--transfer_run \
--start_time='start_time' \
--end_time='end_time' \
resource_name

其中:

  • start_time 和 end_time 是以 Z 结尾或包含有效时区偏移量的时间戳。示例:
    • 2017-08-19T12:11:35.00Z
    • 2017-05-25T00:00:00+00:00
  • resource_name 是计划查询(或转移作业)的资源名称。资源名称也称为转移作业配置。

例如,以下命令将为计划查询资源(或转移作业配置)计划回填: projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

  bq mk \
  --transfer_run \
  --start_time 2017-05-25T00:00:00Z \
  --end_time 2017-05-25T00:00:00Z \
  projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

使用 projects.locations.transferConfigs.scheduleRun 方法并提供 TransferConfig 资源的路径。

配额

定期查询使用创建者的凭据和项目来执行,就像您自己在执行查询一样。计划查询与手动查询受相同的 BigQuery 配额和限制约束。

价格

计划查询与手动 BigQuery 查询的价格相同。

已知问题和限制

区域

不支持跨区域查询,并且计划查询的目标表必须与要查询的数据位于同一区域。如需详细了解区域和多区域,请参阅数据集位置

Google 云端硬盘

您可以通过计划查询来查询 Google 云端硬盘数据。如果您正在安排现有查询,则可能需要点击计划查询详细信息屏幕中的“Update Credentials”。请等候 10-20 分钟的时间让更改生效。您可能需要清除浏览器缓存。对于新的计划查询,凭据会自动更新到最新状态。

更新凭据

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面