如何安全删除BigQuery数十亿历史数据且不中断每5分钟的新数据插入

针对300亿行的BigQuery表，要保留2022年及之后的数据，同时不中断ML模型的5分钟一次插入，分两种场景处理：

场景1：原表已是时间分区表 #

如果表是按插入时间（_PARTITIONTIME）或业务时间字段分区的，直接通过分区删除操作处理，这是最高效且无锁的方式：

• 执行分区删除命令：

  -- 删除单个分区示例
  ALTER TABLE `your-project.your-dataset.your-table`
  DROP PARTITION IF EXISTS DATE('2021-12-31');
  
  -- 范围删除2022年之前所有分区示例
  ALTER TABLE `your-project.your-dataset.your-table`
  DROP PARTITION WHERE _PARTITIONTIME < TIMESTAMP('2022-01-01 00:00:00');
  

• 该操作仅修改元数据，几乎瞬间完成，完全不会影响ML模型的新数据插入（新数据会写入对应分区）。

场景2：原表非分区表 #

非分区表直接执行DELETE会锁表、产生大量日志，甚至导致任务超时，必须用原子替换表的方式：

1. 创建新分区表：结构与原表一致，建议按_PARTITIONTIME（插入时间）分区，方便后续维护：

   CREATE TABLE `your-project.your-dataset.new-table`
   PARTITION BY _PARTITIONTIME
   AS SELECT * FROM `your-project.your-dataset.original-table`
   WHERE insertion_time >= TIMESTAMP('2022-01-01 00:00:00')
   LIMIT 0; -- 仅复制结构，不导入数据
   

2. 分批迁移历史数据：按时间切片（比如按月份）批量导入2022年及之后的数据，避免一次性处理过多数据：

   -- 示例：导入2022年1月的数据
   INSERT INTO `your-project.your-dataset.new-table`
   SELECT * FROM `your-project.your-dataset.original-table`
   WHERE insertion_time BETWEEN TIMESTAMP('2022-01-01 00:00:00') AND TIMESTAMP('2022-01-31 23:59:59');
   

   重复此步骤，直到所有2022年及之后的历史数据迁移完成。
3. 同步增量数据：因为ML模型持续插入新数据，需要定时（比如每5分钟）同步最近的增量数据到新表：

   INSERT INTO `your-project.your-dataset.new-table`
   SELECT * FROM `your-project.your-dataset.original-table`
   WHERE insertion_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE);
   

   多次同步后，确保新表的数据与原表最新数据完全对齐。
4. 原子替换原表：用元数据重命名操作完成切换，全程无中断：

   -- 先备份原表
   ALTER TABLE `your-project.your-dataset.original-table`
   RENAME TO `your-project.your-dataset.original-table-backup`;
   -- 把新表重命名为原表名
   ALTER TABLE `your-project.your-dataset.new-table`
   RENAME TO `your-project.your-dataset.original-table`;
   

   此操作是原子性的，ML模型的插入请求不会中断，会直接写入新的原表。
5. 清理备份表：确认新表正常运行后，删除备份表释放存储空间。

关键注意事项 #

• 操作前必须备份原表，可通过CREATE TABLE ... AS SELECT或BigQuery快照功能完成，防止数据丢失。
• 分批迁移时监控BigQuery资源配额，避免因任务过多触发限流。
• 如果原表关联了视图、ETL任务等，需同步更新这些依赖的表名指向。
• 增量同步的最后几次，可缩短同步时间窗口（比如1分钟），确保切换瞬间数据完全一致。

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