BigQuery通过Pub/Sub订阅写入重复行：是否正常及如何避免

现象是否正常？ #

这种现象是正常的。你的Pub/Sub订阅未启用「Exactly once delivery」，而Pub/Sub默认的消息投递语义是至少一次（At-least-once）——当BigQuery订阅未能在确认期限内返回ACK，或是ACK因网络等原因丢失时，Pub/Sub会重新投递对应消息，最终导致BigQuery中出现重复行。即便降低推送速率，只要存在ACK超时、网络波动等场景，重复问题依然可能发生。

如何避免BQ表中出现重复行？ #

1. 基于唯一标识符在BigQuery层去重 #

你已经给消息添加了UUID或递增整数作为唯一ID，可直接利用这个字段处理重复：

• 添加主键约束：用SQL DDL创建表时，给唯一ID字段设置主键（BigQuery支持主键/唯一键约束），这样写入时重复的唯一ID会被自动拒绝，避免脏数据入库。
• 查询时动态去重：如果无法修改表结构，可通过查询过滤重复行，比如用窗口函数保留最新的一条：

  SELECT * EXCEPT(row_num)
  FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY unique_id ORDER BY _PARTITIONTIME DESC) AS row_num
    FROM your_table
  )
  WHERE row_num = 1
  

• 使用物化视图：创建自动去重的物化视图，设置刷新间隔定期维护干净的数据集，后续直接查询物化视图即可。

2. 优化Pub/Sub订阅配置 #

• 调整确认期限：当前「Acknowledgement deadline」设为600秒，若BigQuery写入速度跟不上，可适当延长该时间，减少因处理超时触发的重试。
• 启用死信队列：开启「Dead lettering」，将多次重试仍失败的消息转发到死信主题，避免无效的重复投递。
• 调整重试策略：如果不需要过长的重试周期，可缩小Maximum backoff duration，减少重复投递的次数。

3. 优化消息发布逻辑 #

• 调整批量发布参数：当前BatchSettings(max_messages=1_000, max_bytes=1_000_000, max_latency=1)的批量规模可能过大，导致单批处理超时，可适当调小max_messages或max_bytes，降低单批处理压力。

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