ETL数仓实战：为什么阿里数仓的订单表要用增量抽数？

小橙编码日志
77 阅读7分钟

很高兴你能来阅读，过去一年多，我主要从事天猫国际商品以及订单相关数仓开发与数据分析工作。接下来会陆续分享这段经历中的实战问题、对应解决思路，以及数仓基础的进阶学习总结，希望能给有需要的朋友带来参考和帮助～

一、序言

有段时间我们阿里数仓dataworks任务调度老是在凌晨堆积任务，除了本身资源内存等可以拓展外，我们发现，有很多大表，比如两千万以上的数据表，都是全量抽数，基本上抽一次大约一个小时多。在我们优化成增量抽数后，每次抽数时间大约只要10-20分钟，明显任务堆积以及产出延迟的问题得到一定程度的解决。

我们最初也是希望服务器可以扩容，性能可以更好，但是在有限的情况下，我们作为研发，避免自己代码的原因导致无效的资源浪费，尽可能的要将服务器本身的性能发挥到最近。

image.png

1. 增量抽数的业务触发场景

增量抽数并非适用于所有业务场景，其核心触发条件是“数据持续新增/变更、数据量较大、存在明确增量标识”，在电商业务中，以下三类场景是增量抽数的典型应用场景：

  • 高频交易类场景：如订单交易、支付记录等核心业务数据。这类数据具有“实时新增、总量庞大”的特点，以订单表为例，电商平台日均订单量可达数十万至数千万级，双11等大促峰值更是每秒新增数百单，若采用全量抽数，不仅会占用大量数据库读写资源，还会导致抽数延迟超过数小时，无法满足后续实时监控、库存更新等业务需求，因此必须通过增量抽数获取每段时间的新增/状态变更订单。

  • 用户行为类场景：如用户浏览日志、点击记录、收藏/加购操作等。这类数据属于“海量高频写入”类型，单用户每日就可能产生数十条行为记录，全量抽数会造成巨大的网络传输和存储成本，而通过增量抽数（基于时间戳或日志偏移量）仅获取新增行为数据，能大幅提升数据同步效率，支撑个性化推荐、用户画像等实时业务。

  • 状态流转类场景：如商品库存变更、物流状态更新、订单售后进度等。这类数据的核心特点是“存量数据会动态变更”，例如商品库存会随下单、退款实时变化，物流状态会从“待发货”流转至“已签收”，若采用全量抽数，会重复同步大量未变更数据，而增量抽数可精准捕捉状态变更记录，确保数据同步的时效性和准确性。

总结来说，当业务数据满足“数据量级大（通常万级以上）、更新频率高、存在可识别的增量标识（如时间戳、自增ID、状态字段）”三个条件时，增量抽数是优于全量抽数的最优解。

真实场景：

  • 作为一个数仓开发，我们的订单表和商品表都是通过增量抽数来实现的。全量抽，主要是占用大量系统的copy资源，任务资源不足的情况下，会导致其余任务延迟。
  • 如上面说的物流状态或者历史部分数据不会发生变化的场景，重复抽数多余！不如根据更新时间来进行增量抽数。

2. 业务背景

比如我们电商系统中，我们订单表的抽数汇总就是使用增量抽数来实现的， 学习思想即可

电商数仓中：

  • 增量源表：ods.ods_t_order_incr（每日抽取的新增/更新订单，2025-08-01分区，含订单ID、用户ID、金额、更新时间等，order_id为主键）；

  • 老全量表：dwd.dwd_fact_order（订单历史全量数据，含所有历史订单）；

  • 合并规则：

✅ 增量表中存在的订单（新增/更新）→ 优先用增量表数据（覆盖老数据）；

✅ 增量表中不存在的老订单 → 保留老数据；

✅ 过滤无效数据（order_id为空、订单金额≤0）。

3. FULL OUTER JOIN 合并逻辑说明

FULL OUTER JOIN 会返回左表（老全量表）和右表（增量表）的所有记录：

  • 两边order_id匹配 → 取增量表数据（更新老数据）；

  • 增量表有、老表无 → 取增量表数据（新增订单）；

  • 老表有、增量表无 → 取老表数据（未变更订单）；

  • 最终通过COALESCE函数优先取增量表字段，实现“增量覆盖、老数据保留”。

二、具体实现步骤（Hive SQL）

步骤1：增量表去重（前置处理，避免重复数据）

增量抽数可能因源库重复写入导致同order_id多条记录，需先按update_time取最新一条：

-- 创建增量去重临时表
CREATE TEMPORARY TABLE tmp.tmp_order_incr_dedup AS
SELECT 
    order_id,
    user_id,
    goods_id,
    order_amount,
    pay_time,
    create_time,
    update_time,
    dt
FROM (
    SELECT 
        *,
        -- 按订单ID分组，取更新时间最新的一条
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn
    FROM ods.ods_t_order_incr
    WHERE dt = '2025-08-01'  -- 仅处理2025-08-01增量数据
      AND order_id IS NOT NULL 
      AND order_amount > 0  -- 过滤无效数据
) t
WHERE rn = 1;

步骤2：FULL OUTER JOIN 合并增量与老数据

核心是通过FULL JOIN关联老表和去重后的增量表，用COALESCE优先取增量字段：

-- 创建合并后临时表
CREATE TEMPORARY TABLE tmp.tmp_order_full_merge AS
SELECT 
    -- 优先取增量表的订单ID（无则取老表，保证主键非空）
    COALESCE(source.order_id, target.order_id) AS order_id,
    -- 增量表有数据则取增量，无则取老表（更新/保留逻辑）
    COALESCE(source.user_id, target.user_id) AS user_id,
    COALESCE(source.goods_id, target.goods_id) AS goods_id,
    COALESCE(source.order_amount, target.order_amount) AS order_amount,
    COALESCE(source.pay_time, target.pay_time) AS pay_time,
    COALESCE(source.create_time, target.create_time) AS create_time,
    -- 更新时间优先取增量表（即使是老订单，更新后时间也最新）
    COALESCE(source.update_time, target.update_time) AS update_time,
    -- 分区字段：增量数据标当日，老数据保留原分区
    COALESCE(source.dt, target.dt) AS dt
FROM dwd.dwd_fact_order target  -- 左表：老全量表（历史数据）
FULL OUTER JOIN tmp.tmp_order_incr_dedup source  -- 右表：去重后的增量表
    ON target.order_id = source.order_id;  -- 按订单ID关联

步骤3：覆盖更新DWD层全量表

将合并后的临时表数据覆盖写入目标全量表，完成最终合并：

-- 覆盖DWD层订单全量表（保留所有历史+最新增量）
INSERT OVERWRITE TABLE dwd.dwd_fact_order
SELECT 
    order_id,
    user_id,
    goods_id,
    order_amount,
    pay_time,
    create_time,
    update_time,
    dt
FROM tmp.tmp_order_full_merge
-- 最终过滤：防止极端情况（如JOIN后order_id为空）
WHERE order_id IS NOT NULL;

-- 清理临时表（可选）
DROP TABLE tmp.tmp_order_incr_dedup;
DROP TABLE tmp.tmp_order_full_merge;

三、FULL OUTER JOIN 合并逻辑拆解（关键说明）

场景老表（target）增量表（source）COALESCE取值逻辑最终处理结果
老订单状态更新有记录有记录取source字段（增量数据）覆盖老数据
全新订单无记录有记录取source字段新增订单
老订单无变更有记录无记录取target字段保留老数据
异常：两边都无记录无记录无记录被WHERE过滤（order_id空）剔除

四、核心优势与注意事项

1. FULL OUTER JOIN 优势

  • 相比传统“LEFT JOIN + UNION ALL”，只需一次关联即可完成“新增+更新+保留老数据”，逻辑更简洁；

  • 天然支持“双向匹配”，无需拆分“老数据非增量部分”和“增量部分”，减少子查询嵌套。

2. 关键注意事项

  • 去重前置：增量表必须先去重，否则FULL JOIN会因同order_id多条增量记录导致结果重复；

  • COALESCE优先级：必须将增量表字段放在前面（COALESCE(source.字段, target.字段)），保证增量数据覆盖老数据；

  • 分区优化：若老全量表按dt分区，建议按分区执行FULL JOIN（如仅关联老表2025-08-01前的分区），避免全表扫描；

  • 数据校验：合并后校验总行数 = 老表行数 - 老表中被更新的行数 + 增量表行数，确保无漏数/重复。

📣非常感谢你阅读到这里，如果这篇文章对你有帮助，希望能留下你的点赞👍 关注❤️ 分享👥 留言💬thanks！！！

avatar
文章
阅读
粉丝