如何在BQ分区表中实现追加数据同时覆写最近3天的历史数据

核心逻辑 #

利用BigQuery的按日期分区表能力，把整个大表按下单日期拆成独立的分区文件，每次更新仅操作近3天的分区，历史已确认的订单分区完全不动，既保证数据实时性，又大幅降低存储和计算成本。

操作步骤 #

步骤1：将现有订单表改造为日期分区表（仅需执行1次） #

分区表是后续优化的基础，改造后你修改或查询特定日期的数据时，BQ只会处理对应分区，不会扫描全表：

CREATE OR REPLACE TABLE 你的数据集名称.订单表
-- 把括号内的字段替换为你表中记录下单时间/下单日期的字段名
PARTITION BY DATE(下单时间字段)
OPTIONS(
  description="订单表，按下单日期分区，每日更新覆写近3天数据"
)
-- 替换为你现在用的全量覆写的旧订单表名称
AS SELECT * FROM 你的数据集名称.旧订单表;

如果你的表已经是按下单日期分区的，可以直接跳过这一步。

步骤2：替换原全量更新脚本为新的每日更新逻辑 #

新的逻辑非常简单，每次执行时先清空近3天的旧数据，再插入近3天到最新的全量订单数据即可，既保证订单状态是最新的，又不用修改3天前已经确认的历史数据：

-- 第一步：删除近3天的旧分区数据，避免重复
DELETE FROM 你的数据集名称.订单表
WHERE DATE(下单时间字段) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);

-- 第二步：从业务源表拉取近3天的最新订单数据插入
INSERT INTO 你的数据集名称.订单表
SELECT * FROM 你的业务数据源表名称
WHERE DATE(下单时间字段) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);

注意事项 #

• 不要用数据写入时间作为分区字段，必须用下单时间作为分区依据，避免删除分区时误删数据
• 如果有少量订单超过3天才会修改，直接把脚本里的INTERVAL 3 DAY改成INTERVAL 4 DAY即可，多1天缓冲几乎不会增加额外成本
• 每次更新完成后可以执行下面的语句快速核对数据是否正常：

SELECT DATE(下单时间字段) AS 下单日期, COUNT(*) AS 订单总量
FROM 你的数据集名称.订单表
WHERE DATE(下单时间字段) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY 1
ORDER BY 1 DESC;

• 这个方案比原全量覆写的计算成本最多可以降低90%以上，同时平时查询数据时只要带上日期筛选条件，查询成本也会同步大幅下降。

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