BigQuery主表插入新数据前删除近5天旧数据的实现方案咨询

你所需求的近5天数据重刷逻辑完全可以通过DELETE+INSERT的方式实现，这也是BigQuery分区表场景下处理短周期数据修正需求的主流方案，具体实现和优化建议如下：

• 第一步：删除main_table中近5天的旧数据，仅需针对分区字段过滤即可，BigQuery会直接命中对应分区，不会产生全表扫描成本，SQL示例：

DELETE FROM `你的项目ID.你的数据集.main_table`
WHERE 分区字段名 >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY);

注意：请将SQL中的项目、数据集、分区字段名替换为你实际的字段，通常按日期分区的字段名为dt、date、partition_date等。

• 第二步：将daily_transfer_table的全量数据插入main_table，SQL示例：

INSERT INTO `你的项目ID.你的数据集.main_table`
-- 建议显式列出所有字段，避免两表字段顺序不一致导致错位
SELECT col1, col2, col3, ..., 分区字段名
FROM `你的项目ID.你的数据集.daily_transfer_table`;

如果担心DELETE执行后INSERT失败，导致近5天数据临时缺失，可以用BigQuery的事务包裹两段SQL，或者直接使用分区覆盖写入模式，无需单独执行DELETE：

• 事务写法示例：

BEGIN TRANSACTION;

-- 先删除近5天数据
DELETE FROM `你的项目ID.你的数据集.main_table`
WHERE 分区字段名 >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY);

-- 再插入新数据
INSERT INTO `你的项目ID.你的数据集.main_table`
SELECT col1, col2, col3, ..., 分区字段名
FROM `你的项目ID.你的数据集.daily_transfer_table`;

COMMIT TRANSACTION;

• 分区覆盖写入：如果是用调度工具（比如Airflow、BigQuery定时查询）执行同步，可以将写入目标设置为main_table，写入模式选择WRITE_TRUNCATE，同时指定分区过滤条件分区字段名 >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)，写入时会自动覆盖匹配的分区，操作更简洁，原子性更强。

• 所有针对main_table的操作必须携带分区字段的过滤条件，避免误删全表数据或产生不必要的扫描成本。
• 同步前建议先校验daily_transfer_table的数据完整性，确认包含完整的近5天数据再执行同步，避免写入缺失数据。
• 如果你的数据修正周期后续可能延长，只需修改SQL中的时间间隔参数即可，不需要调整表结构。

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