如何获取BigQuery中每小时新增插入行并同步至MySQL等数据源

• 利用时间戳字段
  如果表里有记录插入时间的字段（比如insert_time），每次同步时记好上次同步的结束时间，下次查询就筛出insert_time大于这个时间的行。示例SQL：

  SELECT * FROM `your-project.your-dataset.your-table`
  WHERE insert_time > TIMESTAMP('2024-05-20 10:00:00')
  

  记得把上次同步的时间存在外部地方，比如MySQL的一个配置表，每次同步完就更新这个时间戳。

• 开启变更捕获（CDC）功能
  给BigQuery表开启变更捕获后，系统会自动记录行的插入、更新、删除操作。可以通过元数据字段_CHANGE_TYPE和_CHANGE_TIMESTAMP筛选新增行：

  SELECT * EXCEPT(_CHANGE_TYPE, _CHANGE_TIMESTAMP)
  FROM `your-project.your-dataset.your-table`
  WHERE _CHANGE_TYPE = 'INSERT'
    AND _CHANGE_TIMESTAMP > TIMESTAMP('2024-05-20 10:00:00')
  

  注意开启后会额外存储变更元数据，要考虑存储成本。

• 基于自增主键/唯一标识
  如果表有自增主键（比如id），每次同步时记录上次同步的最大ID，下次直接查id大于这个值的行。示例SQL：

  SELECT * FROM `your-project.your-dataset.your-table`
  WHERE id > 10000
  

  这种方法只适合主键严格递增的场景，要保证插入时ID不会重复或回退。

• 用时间旅行对比快照
  利用BigQuery的时间旅行功能，对比两次同步时间点的表快照，找出差异的行。假设上次同步时间是t1，这次是t2，可以这么查：

  SELECT * FROM `your-project.your-dataset.your-table` FOR SYSTEM_TIME AS OF TIMESTAMP('t2')
  EXCEPT DISTINCT
  SELECT * FROM `your-project.your-dataset.your-table` FOR SYSTEM_TIME AS OF TIMESTAMP('t1')
  

  这种方法不需要表有额外字段，但要注意时间旅行的保留期限（默认7天，最多可延长到180天），数据量大时查询性能可能受影响。

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