针对定期更新的大型数据集，如何在BigQuery中按ID删除旧记录？

针对你提到的场景——插入更新后的选民数据后，删除同一voter_id对应的旧版本记录，以下是BigQuery中最实用的实现方式：

前提准备 #

你的数据集需要包含版本标识字段（比如update_timestamp记录更新时间，或者version递增编号），用来区分同一voter_id的新旧记录。如果没有该字段，建议先添加，否则无法判断记录的版本先后。

方法一：使用MERGE语句（推荐，原子操作） #

MERGE是BigQuery中支持的原子操作，能在一个步骤中完成新记录插入和旧记录删除，避免中间数据不一致，非常适合大型数据集。

示例1：基于新数据批量处理 #

假设目标表为project.dataset.voters，更新后的新数据存放在临时表project.dataset.new_voters，用update_timestamp作为版本判断依据（时间越晚版本越新）：

MERGE `project.dataset.voters` AS target
USING (
  -- 提取新数据中每个voter_id的最新版本时间
  SELECT voter_id, MAX(update_timestamp) AS latest_update_time
  FROM `project.dataset.new_voters`
  GROUP BY voter_id
) AS source
ON target.voter_id = source.voter_id
-- 删除目标表中同ID且版本早于最新版本的旧记录
WHEN MATCHED AND target.update_timestamp < source.latest_update_time THEN
  DELETE
-- 插入新数据（包含新选民和更新后的选民记录）
WHEN NOT MATCHED THEN
  INSERT (voter_id, name, address, update_timestamp, ...)
  VALUES (voter_id, name, address, update_timestamp, ...)

示例2：直接用新数据作为源表 #

如果新数据中已经是每个voter_id的最新版本，可以简化为：

MERGE `project.dataset.voters` AS target
USING `project.dataset.new_voters` AS source
ON target.voter_id = source.voter_id
-- 删除旧版本：目标表记录版本早于源表时删除
WHEN MATCHED AND target.update_timestamp < source.update_timestamp THEN
  DELETE
-- 插入所有新记录
WHEN NOT MATCHED THEN
  INSERT *

方法二：先插入再批量删除（非原子，适合特定场景） #

如果无法使用MERGE，可以分两步操作，建议在事务中执行以保证一致性：

1. 插入更新后的新数据：

INSERT INTO `project.dataset.voters`
SELECT * FROM `project.dataset.new_voters`

2. 删除同一voter_id的旧版本记录：
   用EXISTS关联判断：

DELETE FROM `project.dataset.voters` t1
WHERE EXISTS (
  SELECT 1 FROM `project.dataset.voters` t2
  WHERE t2.voter_id = t1.voter_id
  AND t2.update_timestamp > t1.update_timestamp
)

或者用窗口函数标记需删除的记录：

DELETE FROM `project.dataset.voters`
WHERE (voter_id, update_timestamp) IN (
  SELECT voter_id, update_timestamp
  FROM (
    SELECT 
      voter_id,
      update_timestamp,
      -- 按voter_id分组，按版本倒序排，标记非最新版本
      ROW_NUMBER() OVER(PARTITION BY voter_id ORDER BY update_timestamp DESC) AS row_num
    FROM `project.dataset.voters`
  )
  WHERE row_num > 1
)

性能优化建议 #

• 使用分区表：按update_timestamp字段分区，删除操作只会扫描相关分区，大幅提升效率。
• 添加聚类/索引：为voter_id设置聚类字段或二级索引，加速关联查询的速度。
• 批量拆分：对于超大规模数据集，将删除操作拆分为多个批次执行，避免单次操作占用过多资源。

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