使用更改历史记录

通过 BigQuery 更改历史记录，您可以跟踪 BigQuery 表的更改历史记录。表的更改历史记录显示为 SQL 表值函数 (TVF)，该函数会显示指定时间范围内执行的特定更改类型。此功能允许您处理对表进行的增量更改。了解对表进行了哪些更改可帮助您在 BigQuery 外部逐步维护表副本，同时避免费用高昂的副本。

所需权限

如需查看某个表的更改历史记录，您需要拥有该表的 bigquery.tables.getData 权限。以下预定义的 Identity and Access Management (IAM) 角色包含此权限：

  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin

如果表具有或以前具有行级访问权限政策，则只有表管理员可以访问表的历史数据。表需要 bigquery.rowAccessPolicies.overrideTimeTravelRestrictions 权限，它包含在预定义的 roles/bigquery.admin IAM 角色中。

如果表具有列级安全性，则您只能查看您有权访问的列的更改历史记录。

APPENDS TVF

APPENDS TVF 返回给定时间范围内附加到表的所有行的表。以下操作会将行添加到 APPENDS 更改历史记录中：

语法

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)
  • table：BigQuery 表名称。它不能是视图、子查询、外部表、具体化视图或通配符表。此参数必须以 TABLE 一词开头。
  • start_timestamp：TIMESTAMP，表示更改包含在输出中的最早时间。如果为 NULL，则返回自表创建以来的所有更改。如果表是在 start_timestamp 之后创建的，则改用实际表创建时间。如果时间早于时间旅行允许的时间，则返回错误。对于标准表，此时间范围为 7 天，但您可以配置时间旅行窗口为小于该时间。
  • end_timestamp：TIMESTAMP，表示输出中包含更改的最新时间（不含边界值）。如果为 NULL，则包含查询开始之前的所有更改。

返回值

APPENDS TVF 返回包含以下列的表：

  • 运行查询时输入表的所有列。如果在 end_timestamp 之后添加列，则该列将显示为 NULL 值，填充在添加列之前插入的任何行中。
  • _CHANGE_TYPE：STRING，表示生成该行的更改类型。对于 APPENDS，唯一支持的值为 INSERT。
  • _CHANGE_TIMESTAMP：TIMESTAMP，表示进行更改的事务的提交时间。

详情

插入的行的记录仍然存在，即使这些数据稍后被删除也是如此。删除操作不会反映在 APPENDS TVF 中。如果复制了表，则对复制的表调用 APPENDS TVF 将返回在创建表时插入的每一行。如果某行因 UPDATE 操作而发生修改，则无效。

示例

此示例展示了对名为 Produce 的表进行各种更改时由 APPENDS 返回的更改历史记录。如果完成的时间超过时间旅行窗口，则此示例可能不起作用。首先，创建表。

CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

其次，向表中插入两行。

INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);

查看附加的完整更改历史记录。使用 NULL 值获取时间旅行窗口内的完整历史记录。

SELECT
  product,
  inventory,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

输出内容类似如下：

+---------+-----------+-------------+--------------------------------+
| product | inventory | change_type | change_time                    |
+---------+-----------+-------------+--------------------------------+
| apples  | 10        | INSERT      | 2022-04-15 20:06:00.488000 UTC |
| bananas | 20        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| carrots | 30        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
+---------+-----------+-------------+--------------------------------+

接着，添加列、插入新值行、更新清单并删除“bananas”行。

ALTER TABLE mydataset.Produce ADD COLUMN color STRING;
INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple");
UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE;
DELETE mydataset.Produce WHERE product = "bananas";

查看新表。

SELECT * FROM mydataset.Produce;

+---------+-----------+--------+
| product | inventory | color  |
+---------+-----------+--------+
| apples  | 15        | NULL   |
| carrots | 35        | NULL   |
| grapes  | 45        | purple |
+---------+-----------+--------+

查看附加的完整更改历史记录。

SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

输出内容类似如下：

+---------+-----------+--------+-------------+--------------------------------+
| product | inventory | color  | change_type | change_time                    |
+---------+-----------+--------+-------------+--------------------------------+
| apples  | 10        | NULL   | INSERT      | 2022-04-15 20:06:00.488000 UTC |
| bananas | 20        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| carrots | 30        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| grapes  | 40        | purple | INSERT      | 2022-04-15 20:07:45.751000 UTC |
+---------+-----------+--------+-------------+--------------------------------+

inventory 列显示最初将行插入表时设置的值；但不会显示通过 UPDATE 语句所做的更改。包含 bananas 相关信息的行仍然存在，因为 APPENDS TVF 仅捕获对表的添加项，但不会捕获删除项。

限制

更改历史记录存在以下限制：

  • 您只能查看有关附加、更新或删除的信息。
  • 数据仅限于表的时间旅行窗口。