GCP BigQuery表历史状态查看：时间旅行SQL使用问题及窗口限制

问题说明 #

你在BigQuery中有一张每日被外部数据源覆盖的表，尝试用时间旅行语法查询过去某个时间点的表状态：

SELECT *
FROM `mydataset.mytable`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

但发现只有当查询的时间间隔小于表最后修改时间时才能返回结果，同时了解到时间旅行的最大保留窗口为7天。

核心原因 #

BigQuery的时间旅行功能依赖表的版本历史：当外部数据源覆盖表时，本质是执行了全量替换操作，会生成一个新的表版本。旧版本仅会被保留7天（从新版本创建的时间开始计算）。如果你的查询时间点早于最后一次覆盖操作的时间，且在旧版本的7天保留窗口内，就能查到对应状态；如果查询时间点超出这个窗口，或者早于旧版本的存在时间，就无法返回数据。

解决办法 #

1. 精准查询有效时间范围内的历史版本 #

首先要确认你要查询的历史时间点对应的表版本是否还在保留期内：

• 用以下SQL查询表的修改历史，找到覆盖操作对应的版本时间：

  SELECT
    table_name,
    version_time,
    ddl_statement,
    type
  FROM
    `[你的区域]`.INFORMATION_SCHEMA.TABLE_CHANGES
  WHERE
    table_catalog = '[你的项目ID]'
    AND table_schema = 'mydataset'
    AND table_name = 'mytable'
    AND type = 'REPLACE'
  ORDER BY
    version_time DESC;
  

• 拿到目标版本的version_time后，直接用该时间作为查询参数，比如：

  SELECT *
  FROM `mydataset.mytable`
  FOR SYSTEM_TIME AS OF TIMESTAMP("2024-05-20 09:00:00");
  

2. 长期保留历史数据的方案 #

如果需要保留超过7天的表历史状态，时间旅行无法满足，可采用以下方式：

• 定时创建快照表：在每次外部数据源覆盖表之前，手动或通过Cloud Scheduler定时执行快照创建：

  CREATE SNAPSHOT TABLE `mydataset.mytable_snapshot_20240520`
  CLONE `mydataset.mytable`;
  

• 改用分区表追加模式：调整数据写入逻辑，不再全量覆盖，而是按日期分区，将每日数据追加到对应分区，这样可以永久保留各日期的历史数据，随时查询。
• 启用外部存储版本控制：如果外部数据源是GCS，开启GCS对象版本控制，每次覆盖文件时保留旧版本，再通过BigQuery外部表指向对应版本的GCS文件，即可查看历史数据。

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