如何在BigQuery中无需复制表对比两个版本的数据？

我需要对比某张表的最后修改前版本与最新数据，尝试了两种方法均遇到问题：

方法1：CTE结合FOR SYSTEM_TIME AS OF对比 #

报错信息：

If a 'FOR SYSTEM_TIME AS OF' expression is used, all references of a table should use the same TIMESTAMP value.

对应的SQL脚本：

WITH
before_mod AS (  SELECT *
              FROM `big-query-112.temp.tableB`
              FOR SYSTEM_TIME AS OF TIMESTAMP_SUB({{ lastModification }}, INTERVAL 2 second)),
after_mod AS ( SELECT * FROM `big-query-112.temp.tableB` ),
row_changed AS (
  SELECT *
  FROM before_mod EXCEPT DISTINCT
  SELECT *
  FROM after_mod
)
SELECT * FROM row_changed

方法2：通过视图保存历史快照后对比 #

单独查询视图结果正常，但执行对比时无法显示差异；且无法使用物化视图，报错：

Invalid value: Materialized view query cannot reference historical versions of the table definition

对应的SQL脚本：

CREATE OR REPLACE VIEW `big-query-112.temp.tableB_before_mod_temp` AS (
              SELECT *
              FROM `big-query-112.temp.tableB`
              FOR SYSTEM_TIME AS OF TIMESTAMP_SUB('2023-02-04 13:12:35 UTC', INTERVAL 0 second)
        );
WITH
before_mod AS ( SELECT * FROM `big-query-112.temp.tableB_before_mod_temp`),
after_mod AS ( SELECT * FROM `big-query-112.temp.tableB` ),
row_changed AS (
  SELECT *
  FROM before_mod EXCEPT DISTINCT
  SELECT *
  FROM after_mod
)
SELECT * FROM row_changed

备注：该表无固定ID且难以添加，请问是否存在无需复制表即可对比两个版本数据的方法？

方法1报错的修正方案 #

BigQuery的报错是因为同一查询中多次引用同一张表时，要么全部用FOR SYSTEM_TIME AS OF指定同一个时间戳，要么全部不用。你当前的查询同时引用了表的历史版本和最新版本，触发了该限制。

可以通过给两个版本数据标记区分，再用整行的JSON字符串作为临时标识来找出差异，示例SQL：

WITH table_versions AS (
  -- 标记历史版本
  SELECT *, 'before' AS version
  FROM `big-query-112.temp.tableB`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB({{ lastModification }}, INTERVAL 2 second)
  UNION ALL
  -- 标记最新版本
  SELECT *, 'after' AS version
  FROM `big-query-112.temp.tableB`
),
row_counts AS (
  -- 用整行JSON做分区，统计每行在两个版本中的出现次数
  SELECT *, COUNT(*) OVER (PARTITION BY TO_JSON_STRING(*)) AS cnt
  FROM table_versions
)
-- 筛选只在单个版本出现的差异行
SELECT * EXCEPT(version, cnt)
FROM row_counts
WHERE cnt = 1
ORDER BY version;

方法2视图对比无差异的解决思路 #

视图是虚拟表，每次查询都会重新执行底层的历史快照语句，如果时间戳设置不准确或修改后间隔太短，会导致快照和最新数据无差异。如果不想长期复制表，可以用会话级临时表保存快照：

-- 创建临时表存储历史快照（会话结束后自动删除）
CREATE TEMP TABLE tableB_before_mod_temp AS
SELECT *
FROM `big-query-112.temp.tableB`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB('2023-02-04 13:12:35 UTC', INTERVAL 0 second);

-- 双向对比差异
SELECT * FROM tableB_before_mod_temp
EXCEPT DISTINCT
SELECT * FROM `big-query-112.temp.tableB`
UNION ALL
SELECT * FROM `big-query-112.temp.tableB`
EXCEPT DISTINCT
SELECT * FROM tableB_before_mod_temp;

无需复制表的直接对比方案 #

如果完全不想创建任何临时表/视图，可以给两个版本都指定明确时间戳，结合EXCEPT ALL实现对比：

-- 找出历史有但最新没有的行
SELECT *
FROM `big-query-112.temp.tableB`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB({{ lastModification }}, INTERVAL 2 second)
EXCEPT ALL
SELECT *
FROM `big-query-112.temp.tableB`
FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP()

UNION ALL

-- 找出最新有但历史没有的行
SELECT *
FROM `big-query-112.temp.tableB`
FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP()
EXCEPT ALL
SELECT *
FROM `big-query-112.temp.tableB`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB({{ lastModification }}, INTERVAL 2 second);

这里用EXCEPT ALL保留重复行的差异，同时满足BigQuery对同表多次引用FOR SYSTEM_TIME AS OF的要求。

内容的提问来源于stack exchange，提问作者Indrit Vaka