使用计划查询创建表快照

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

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

注意：如需自定义本文档以在文本和示例中使用您自己的项目、数据集和/或表名称，请修改以下变量：PROJECT、DATASET、BACKUP、TABLE。

本文档适用于熟悉 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 项目中运行查询所需的权限：

控制台

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

   转到“服务账号”

2. 选择 PROJECT 项目。

3. 创建 snapshot-bot 服务账号：

   1. 点击创建服务账号。

   2. 在服务账号名称字段中，输入 snapshot-bot。

   3. 点击创建并继续。

4. 向服务账号授予运行 BigQuery 作业所需的权限：

   1. 在向此服务账号授予对项目的访问权限部分，选择 BigQuery User 角色。

   2. 点击完成。

BigQuery 使用电子邮件地址 [e-mail] 创建服务账号。

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

控制台

验证 BigQuery 已创建服务账号：

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

   转到“服务账号”

2. 选择 PROJECT 项目。

3. 点击 [e-mail]。

4. 验证服务账号状态消息表明服务账号处于活动状态。

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

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

   转到“管理资源”

2. 点击 PROJECT。

3. 点击显示信息面板。

4. 在权限标签页中，展开 BigQuery User 节点。

5. 验证您的 snapshot-bot 服务账号已列出。

向服务账号授予权限

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

截取基表快照的权限

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

控制台

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

   转到 BigQuery

2. 在探索器窗格中，展开 PROJECT 项目节点。

3. 展开 DATASET 数据集节点。

4. 选择 TABLE 表。

5. 点击共享。此时会打开共享窗格。

6. 点击添加主账号。 授予访问权限窗格随即会打开。

7. 在新的主账号中，输入服务账号的电子邮件地址：[e-mail]。

8. 从选择角色下拉菜单中，选择 BigQuery Data Editor 角色。

9. 点击保存。

10. 在共享窗格中，展开 BigQuery Data Editor 节点，然后验证 [e-mail] 服务账号是否已列出。

11. 点击关闭。

bq

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

   激活 Cloud Shell

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

   bq add-iam-policy-binding \
   --member=serviceAccount:[e-mail] \
   --role=roles/bigquery.dataEditor DATASET.TABLE

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

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

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

控制台

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

   转到 BigQuery

2. 在探索器窗格中，展开 PROJECT 项目节点。

3. 点击 BACKUP 数据集节点的菜单，然后选择打开。

4. 点击共享数据集。 此时数据集权限窗格会打开。

5. 在添加成员字段中，输入服务账号的电子邮件地址：[e-mail]。

6. 从选择角色下拉菜单中，选择 BigQuery Data Owner 角色。

7. 点击添加。

8. 在数据集权限窗格中，验证 [e-mail] 服务账号是否已在 BigQuery Data Owner 节点下列出。

9. 点击完成。

您的 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

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

   激活 Cloud Shell

2. 输入以下 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;'

3. 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 服务账号凭据运行，如下所示：

1. 运行 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

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

   bq update --transfer_config --update_credentials \
   --service_account_name=[e-mail] \
   projects/12345/locations/us/transferConfigs/12345

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

检查您的工作

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

查看计划查询

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

控制台

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

   转到“计划查询”

2. 点击 Monthly snapshots of the TABLE table。

3. 点击配置。

4. 验证查询字符串包含您的查询，并且查询安排在每月的第一天运行。

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

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

控制台

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

   转到“计划查询”

2. 点击查询说明 (Monthly snapshots of the TABLE table)。

3. 点击运行历史记录。

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

查看表快照

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

控制台

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

   转到 BigQuery

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

   例如：

   • TABLE_20220601
   • TABLE_20220701
   • TABLE_20220801

后续步骤

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