BigQuery分区表流更新触发调度查询写入目标表方法

你提到的跨天持续流式写入分区表的增量同步需求，在BigQuery中有成熟落地方案，不需要等全量数据写完再处理，可以做到近实时同步新增数据且不遗漏。

结论是不能单独依赖这个字段实现可靠逻辑，原因如下：

• 表级last modified仅记录整表最后一次发生变更的时间点，无法标记本次变更涉及的具体分区、具体行记录，既没法定位新增数据范围，也无法识别两次触发间隙写入的多批数据，极易出现重复处理或数据遗漏。
• 分区级last modified仅能标记某个分区最近一次被修改的时间，可以用来缩小扫描范围（比如只扫描上次同步后有改动的分区，降低查询成本），但依然无法定位分区内哪些行是上次同步后新写入的，单独使用依然会出现重复/漏数问题。

推荐组合使用「事件触发+定时兜底+水位标记」的架构，完全匹配你的需求：

• 触发层配置：
  • 开启BigQuery审计日志导出，将源表的数据写入事件投递到Pub/Sub，绑定Cloud Function作为消费端，一旦监听到源表有新的流式写入，就触发同步查询运行，做到近实时响应。
  • 额外配置每小时1次的定时调度作为兜底，避免偶发的事件投递失败、函数执行错误导致的数据漏跑，因为有水位标记控制，兜底调度不会产生重复数据。
• 增量识别逻辑：
  • 提前给源表开启BigQuery原生CDC（变更数据捕获）能力，开启后系统会自动给每一行写入的记录附加_CHANGE_TIMESTAMP元数据字段，精确标记该行的实际入库时间，不需要你在业务写入逻辑里额外加字段。
  • 单独建一张水位表，标记上一次同步完成时对应的最大入库时间戳。
• 同步逻辑执行：
  每次触发同步时，只拉取源表中_CHANGE_TIMESTAMP大于水位值的记录，按你指定的规则做筛选后插入目标表，插入完成后把水位值更新为当前源表中最大的_CHANGE_TIMESTAMP即可。
• 首次运行防遗漏处理：
  首次上线时把水位表的初始值设为1970-01-01 00:00:00 UTC，第一次调度就会把源表中所有符合筛选规则的历史数据全量同步到目标表，后续自动切换为增量同步，不会漏数。

参考同步逻辑示例：

-- 增量写入目标表
INSERT INTO your_target_table (event_date, col1, col2, write_time)
SELECT event_date, col1, col2, _CHANGE_TIMESTAMP AS write_time
FROM your_source_table
WHERE 
  _CHANGE_TIMESTAMP > (SELECT last_sync_timestamp FROM sync_watermark WHERE sync_table = 'your_source_table')
  -- 替换为你自己的新增记录筛选规则
  AND <your_custom_filter_rule>;

-- 同步完成后更新水位
UPDATE sync_watermark
SET last_sync_timestamp = (SELECT MAX(_CHANGE_TIMESTAMP) FROM your_source_table)
WHERE sync_table = 'your_source_table';

额外优化点：可以在每次同步前先拉取所有分区的last modified时间，只扫描上次同步后有改动的分区，进一步降低查询扫描的数据量，节省成本。

内容的提问来源于stack exchange，提问作者T.B.