使用计划查询创建表快照

本文档介绍如何使用运行计划 DDL 查询的服务账号创建表的每月快照。本文档将引导您完成以下示例：

在 PROJECT 项目中，创建一个名为 snapshot-bot 的服务账号。
向 snapshot-bot 服务账号授予创建 TABLE 表（位于 DATASET 数据集）的表快照并将表快照存储到 BACKUP 数据集所需的权限。
编写一个查询，该查询创建 TABLE 表的每月快照并存入 BACKUP 数据集。由于您无法覆盖现有表快照，因此表快照必须具有唯一名称。为此，查询会将当前日期附加到表快照名称中：例如 TABLE_20220521。表快照的有效期为 40 天。
安排 snapshot-bot 服务账号在每月第一天运行该查询。

本文档适用于熟悉 BigQuery 及 BigQuery 表快照的用户。

权限和角色

本部分介绍创建服务账号和安排查询所需的 Identity and Access Management (IAM) 权限，以及包含这些权限的预定义 IAM 角色。

权限

如需使用服务账号，您需要以下权限：

权限	资源	资源类型
`iam.serviceAccounts.*`	`PROJECT`	项目

如需安排查询，您需要以下权限：

权限	资源	资源类型
`bigquery.jobs.create`	`PROJECT`	项目

角色

提供使用服务账号时所需权限的预定义角色如下所示：

角色	资源	资源类型
以下任意一项： `roles/iam.serviceAccountAdmin` `roles/editor` `roles/owner`	`PROJECT`	项目

提供计划查询时所需权限的预定义 BigQuery 角色如下所示：

角色	资源	资源类型
以下任意一项： `roles/bigquery.user` `roles/bigquery.jobuser` `roles/bigquery.admin``	`PROJECT`	项目

创建 `snapshot-bot` 服务账号

请按照以下步骤创建 snapshot-bot 服务账号，并向其授予在 PROJECT 项目中运行查询所需的权限：

控制台

在 Google Cloud 控制台中，转到服务账号页面：

转到“服务账号”
选择 PROJECT 项目。
创建 snapshot-bot 服务账号：
1. 点击创建服务账号。
2. 在服务账号名称字段中，输入 snapshot-bot。
3. 点击创建并继续。
向服务账号授予运行 BigQuery 作业所需的权限：
1. 在向此服务账号授予对项目的访问权限部分，选择 BigQuery User 角色。
2. 点击完成。

BigQuery 使用电子邮件地址 snapshot-bot@PROJECT.iam.gserviceaccount.com 创建服务账号。

如需验证 BigQuery 创建了具有您指定的权限的服务账号，请按以下步骤操作：

控制台

验证 BigQuery 已创建服务账号：

在 Google Cloud 控制台中，转到服务账号页面：

转到“服务账号”
选择 PROJECT 项目。
点击 snapshot-bot@PROJECT.iam.gserviceaccount.com。
验证服务账号状态消息表明服务账号处于活动状态。

验证 BigQuery 已向服务账号授予运行查询所需的权限：

在 Google Cloud 控制台中，转到管理资源页面：

转到“管理资源”
点击 PROJECT。
点击显示信息面板。
在权限标签页中，展开 BigQuery User 节点。
验证您的 snapshot-bot 服务账号已列出。

向服务账号授予权限

本部分介绍如何向 snapshot-bot 服务账号授予在 BACKUP 数据集中创建 DATASET.TABLE 表快照所需的权限。

截取基表快照的权限

如需授予 snapshot-bot 服务账号截取 DATASET.TABLE 表快照所需的权限，请按以下步骤操作：

控制台

在 Google Cloud 控制台中，打开 BigQuery 页面。

转到 BigQuery
在探索器窗格中，展开 PROJECT 项目节点。
展开 DATASET 数据集节点。
选择 TABLE 表。
点击分享。此时会打开共享窗格。
点击添加主账号。 授予访问权限窗格随即会打开。
在新主账号中，输入服务账号的电子邮件地址：snapshot-bot@PROJECT.iam.gserviceaccount.com。
从选择角色下拉菜单中，选择 BigQuery Data Editor 角色。
点击保存。
在共享窗格中，展开 BigQuery Data Editor 节点并验证 snapshot-bot@PROJECT.iam.gserviceaccount.com 服务账号已列出。
点击关闭。

bq

在 Google Cloud 控制台中，激活 Cloud Shell：

激活 Cloud Shell

输入以下 bq add-iam-policy-binding 命令：

bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery 确认已添加新的政策绑定。

在目标数据集中创建表的权限

向 snapshot-bot 服务账号授予在 BACKUP 数据集中创建表快照所需的权限，如下所示：

控制台

在 Google Cloud 控制台中，转到 BigQuery 页面。

转到 BigQuery
在探索器窗格中，展开 PROJECT 项目节点。
点击 BACKUP 数据集节点的菜单，然后选择打开。
点击共享数据集。此时数据集权限窗格会打开。
在添加成员字段中，输入服务账号的电子邮件地址：snapshot-bot@PROJECT.iam.gserviceaccount.com。
从选择角色下拉菜单中，选择 BigQuery Data Owner 角色。
点击添加。
在数据集权限窗格中，验证 snapshot-bot@PROJECT.iam.gserviceaccount.com 服务账号已在 BigQuery Data Owner 节点下列出。
点击完成。

您的 snapshot-bot 服务账号现在具有以下资源的以下 IAM 角色：

角色	资源	资源类型	用途
BigQuery 数据编辑者	`PROJECT:DATASET.TABLE`	表	截取 `TABLE` 表的快照。
BigQuery Data Owner	`PROJECT:BACKUP`	数据集	在 `BACKUP` 数据集中创建和删除表快照。
BigQuery User	`PROJECT`	项目	运行创建表快照的计划查询。

这些角色提供 snapshot-bot 服务账号运行创建 DATASET.TABLE 表快照并将表快照存入 BACKUP 数据集中的查询所需的权限。

编写多语句查询

本部分介绍如何使用 CREATE SNAPSHOT TABLE DDL 语句，编写用于创建 DATASET.TABLE 表的表快照的多语句查询。快照保存在 BACKUP 数据集中，并在一天后过期。

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

安排每月查询

安排查询在每月第一天的凌晨 5:00 运行，如下所示：

bq

在 Google Cloud 控制台中，激活 Cloud Shell：

激活 Cloud Shell

输入以下 bq query 命令：

bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

BigQuery 安排查询。

bp 命令行工具命令中的多语句查询与您在 Google Cloud 控制台中运行的查询的区别如下：

bq 命令行工具查询使用 @run_date 而不是 current_date()。在计划查询中，@run_date 参数包含当前日期。但交互式查询中不支持 @run_date 参数。在安排交互式查询之前，您可以使用 current_date()（而不是 @run_date）来测试交互式查询。
bp 命令行工具查询使用 @run_time 而不是 current_timestamp()，原因与上面类似，即交互式查询中不支持 @run_time 参数，但可以使用 current_timestamp()（而非 @run_time）来测试交互式查询。
bp 命令行工具查询使用斜杠和双引号 \"，而不是单引号 '，因为单引号用于括起查询。

配置服务账号以运行计划查询

该查询目前计划使用您的凭据运行。更新计划查询，以使用 snapshot-bot 服务账号凭据运行，如下所示：

运行 bq ls 命令以获取计划查询作业的身份：
```
bq ls --transfer_config=true --transfer_location=us
```
输出类似于以下内容：

name displayName dataSourceId state

projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

`name`	`displayName`	`dataSourceId`	`state`
`projects/12345/locations/us/transferConfigs/12345`	`Monthly snapshots of the TABLE table`	`scheduled_query`	`RUNNING`

使用 name 字段中的标识符运行以下 bq update 命令：

bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell 会确认计划查询已成功更新。

检查您的工作

本部分介绍如何验证是否已正确计划查询、如何查看查询运行时是否有错误，以及如何验证是否创建了每月快照。

查看计划查询

如需验证 BigQuery 已安排每月表快照查询，请按照以下步骤操作：

控制台

在 Google Cloud 控制台中，转到计划查询页面：

转到“计划查询”
点击 Monthly snapshots of the TABLE table。
点击配置。
验证查询字符串包含您的查询，并且查询安排在每月的第一天运行。

查看计划查询的运行历史记录

计划查询运行后，您可以查看它是否成功运行，如下所示：

控制台

在 Google Cloud 控制台中，转到计划查询页面：

转到“计划查询”
点击查询说明 (Monthly snapshots of the TABLE table)。
点击运行历史记录。

您可以查看查询运行的日期和时间、运行是否成功以及未成功时发生的错误。如需详细了解某个特定运行，请点击其在运行历史记录表中的行。运行详情窗格会显示其他详细信息。

查看表快照

如需验证表快照已创建，请按以下步骤操作：

控制台

在 Google Cloud 控制台中，转到 BigQuery 页面：

转到 BigQuery
在探索器窗格中，打开 BACKUP 数据集并验证已创建 TABLE_YYYYMMDD 快照，其中 YYYYMMDD 为每月的第一天。

例如：
- TABLE_20220601
- TABLE_20220701
- TABLE_20220801

后续步骤

如需详细了解表快照，请参阅使用表快照。
如需详细了解如何安排查询，请参阅安排查询。
如需详细了解 Google Cloud 服务账号，请参阅服务账号。