未使用时间旅行表达式却触发BigQuery时间旅行无效错误的解决方法

BigQuery的MERGE是原子性操作，当你在SOURCE子查询中同时读取MERGE的目标表${table1}时，BigQuery会自动使用查询启动时刻的快照来读取${table1}，以此保证MERGE操作的一致性。如果这个快照的时间点早于${some_bq_view}依赖的底层表的最早可访问时间（比如底层表是临时表已过期、或表的数据保留期过短导致旧版本被清理），就会触发隐式的时间旅行访问失败，抛出上述错误。

1. 拆分MERGE流程，使用临时表存储SOURCE数据 #

先将SOURCE子查询的结果写入临时表，再用临时表作为源执行MERGE，避免在同一个MERGE操作中同时读写同一张表，彻底规避隐式时间旅行的触发：

第一步：生成临时表 #

CREATE OR REPLACE TEMP TABLE merge_source AS
WITH
  cte1 AS (
  SELECT
    id1,
    id2,
    item_id
  FROM
    ${some_bq_view},
    UNNEST(item_ids) item_id
  GROUP BY
    1,
    2,
    3 )
SELECT
  *
FROM
  ${table1} t1
LEFT JOIN
  cte1 c1
ON
  t1.id1 = c1.id1
  AND t1.id2 = c1.id2
  AND t1.id3 = c1.item_id
WHERE
  DATE(t1.timestamp) = '${date}'
  AND t1.status = '${status1}'
  AND t1.type = '${type1}'
  AND t1.category = '${category1}'

第二步：执行MERGE #

MERGE
  ${table1} AS TARGET
USING
  merge_source AS SOURCE
ON
  target.category = '${category1}'
  AND target.type = '${type1}'
  AND target.id = source.id
  AND target.status = '${status1}'
  
WHEN MATCHED THEN UPDATE SET target.status = CASE
  WHEN source.item_id IS NULL THEN '${status2}' ELSE '${status3}'
END,
target.modified_at = CURRENT_DATETIME('${timezone}');

2. 调整some_bq_view依赖表的数据保留期 #

检查some_bq_view依赖的底层表的配置：

• 如果是临时表，改为永久表；
• 如果是永久表，延长数据保留期（默认是7天，可通过BigQuery控制台或DDL修改），确保保留期覆盖查询可能用到的快照时间范围。

示例DDL修改表保留期：

ALTER TABLE `project.dataset.underlying_table`
SET OPTIONS(
  max_staleness = INTERVAL 30 DAY
);

3. 显式指定使用最新快照 #

在SOURCE子查询中，对${table1}和${some_bq_view}显式指定使用当前时间的快照，强制跳过隐式的旧快照读取：

MERGE
  ${table1} AS TARGET
USING
  (
  WITH
    cte1 AS (
    SELECT
      id1,
      id2,
      item_id
    FROM
      `${some_bq_view}` FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP(),
      UNNEST(item_ids) item_id
    GROUP BY
      1,
      2,
      3 )
  SELECT
    *
  FROM
    `${table1}` FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP() t1
  LEFT JOIN
    cte1 c1
  ON
    t1.id1 = c1.id1
    AND t1.id2 = c1.id2
    AND t1.id3 = c1.item_id
  WHERE
    DATE(t1.timestamp) = '${date}'
    AND t1.status = '${status1}'
    AND t1.type = '${type1}'
    AND t1.category = '${category1}' ) AS SOURCE
ON
  target.category = '${category1}'
  AND target.type = '${type1}'
  AND target.id = source.id
  AND target.status = '${status1}'
  
WHEN MATCHED THEN UPDATE SET target.status = CASE
  WHEN source.item_id IS NULL THEN '${status2}' ELSE '${status3}'
END,
target.modified_at = CURRENT_DATETIME('${timezone}');

内容的提问来源于stack exchange，提问作者Emre TÜrker