● Hive 中累积快照事实表的实现 —— 电商订单履约场景

Hive 累积快照表的核心难点是：Hive 不支持 UPDATE（早期版本），但累积快照本质就是要"多次更新同一行"。所以必须用 Hive 特有的方式来实现 —— 每天用 INSERT OVERWRITE 重建分区，模拟出"更新"的效果。

---

一、核心难点与解决思路

难点

传统数据库（MySQL）实现累积快照：
T1 下单：INSERT INTO order_lifecycle …
T2 支付：UPDATE order_lifecycle SET pay_time = … WHERE order_id = X
T3 发货：UPDATE order_lifecycle SET ship_time = … WHERE order_id = X

Hive 不能 UPDATE！怎么办？

Hive 的解决思路：全量覆盖 + 分区滚动

每日执行的 ETL 逻辑：
Step 1: 取出"昨日尚未结束的订单"+“今日新产生的订单”
Step 2: LEFT JOIN 各业务过程的最新事件，更新对应字段
Step 3: INSERT OVERWRITE 写入今日分区
Step 4: 已结束的订单，迁移到归档分区或在当前分区固化

关键设计点：

1. 分区策略：用"订单创建日"分区（不是 ETL 日期）
2. 存储格式：必须用 ORC + 事务表（如果要真 UPDATE）或 INSERT OVERWRITE 整分区（最常用）
3. 数据流转：今日分区 = 昨日分区数据 + 今日新事件 merge

---

二、电商订单履约场景实现

业务流程

下单 → 支付 → 商家确认 → 出库 → 揽收发货 → 干线运输 → 派送 → 签收 → 确认收货 → 完结

数据来源（DWD 层的事务事实表）

dwd_trade_order_create_di （下单事件）
dwd_trade_order_pay_di （支付事件）
dwd_trade_order_confirm_di （商家确认事件）
dwd_logi_outstock_di （出库事件）
dwd_logi_shipment_di （发货事件）
dwd_logi_delivery_di （派送事件）
dwd_logi_sign_di （签收事件）
dwd_trade_order_finish_di （订单完结事件）

---

三、累积快照表 DDL

CREATE TABLE IF NOT EXISTS dwd_trade_order_lifecycle (
– 主键
order_id BIGINT COMMENT ‘订单ID’,

-- 维度（退化）
user_id             BIGINT,
shop_id             BIGINT,
item_id             BIGINT,
cate_level1_name    STRING,
province_name       STRING,
city_name           STRING,

-- 度量
order_amount        DECIMAL(16,2),
pay_amount          DECIMAL(16,2),
buy_qty             INT,

-- 关键：各节点时间字段（一行多个时刻）
create_time         TIMESTAMP   COMMENT '下单时间',
pay_time            TIMESTAMP   COMMENT '支付时间',
shop_confirm_time   TIMESTAMP   COMMENT '商家确认时间',
out_stock_time      TIMESTAMP   COMMENT '出库时间',
ship_time           TIMESTAMP   COMMENT '发货时间',
delivery_time       TIMESTAMP   COMMENT '派送到达时间',
sign_time           TIMESTAMP   COMMENT '签收时间',
finish_time         TIMESTAMP   COMMENT '订单完结时间',

-- 各阶段时长（派生度量，行内计算）
create_to_pay_min   INT         COMMENT '下单到支付分钟数',
pay_to_outstock_h   DECIMAL(8,2) COMMENT '支付到出库小时数',
outstock_to_ship_h  DECIMAL(8,2) COMMENT '出库到发货小时数',
ship_to_delivery_h  DECIMAL(8,2) COMMENT '发货到派送小时数',
delivery_to_sign_h  DECIMAL(8,2) COMMENT '派送到签收小时数',
total_lead_time_h   DECIMAL(8,2) COMMENT '全流程总时长',

-- 状态字段
current_status      STRING      COMMENT '当前所处阶段',
is_finished         INT         COMMENT '1已完结 0进行中',
is_timeout          INT         COMMENT '是否超时',

-- 技术字段
etl_time            TIMESTAMP   COMMENT 'ETL时间'

)
COMMENT ‘订单履约累积快照事实表’
PARTITIONED BY (dt STRING COMMENT ‘订单创建日’)
STORED AS ORC
TBLPROPERTIES (‘orc.compress’=‘ZSTD’);

关键设计点：

1. PARTITIONED BY (dt STRING) 用订单的"创建日"分区
   - 不是 ETL 执行日
   - 这样一笔订单的数据永远在它创建那天的分区里
   - 便于按订单创建日查询（“双11当天的订单履约情况”）
2. STORED AS ORC
   - 列式存储 + 压缩
   - 累积快照要频繁全分区重写，ORC 性能必须

---

四、ETL 加工逻辑（核心代码）

思路图解

今日要处理的订单 = 进行中订单 + 今日新订单

历史分区中所有 is_finished=0 的订单
            ↓
            UNION
            ↓
今日新创建的订单（来自 dwd_trade_order_create_di 当日分区）
            ↓
 LEFT JOIN 各业务过程的最新事件
            ↓
     计算各阶段时长 + 当前状态
            ↓
 INSERT OVERWRITE 回到对应的订单创建日分区

完整 ETL SQL

– ============================================
– 每日定时任务：${bizdate} 为业务日期，如 ‘2026-06-11’
– ============================================

INSERT OVERWRITE TABLE dwd_trade_order_lifecycle
PARTITION (dt)
SELECT
base.order_id,

-- 维度
base.user_id,
base.shop_id,
base.item_id,
base.cate_level1_name,
base.province_name,
base.city_name,

-- 度量
base.order_amount,
base.pay_amount,
base.buy_qty,

-- ★ 各节点时间：用 COALESCE 实现"已有值不变，没值则填新事件"
base.create_time,
COALESCE(base.pay_time,          pay.pay_time)            AS pay_time,
COALESCE(base.shop_confirm_time, conf.confirm_time)        AS shop_confirm_time,
COALESCE(base.out_stock_time,    os.out_stock_time)        AS out_stock_time,
COALESCE(base.ship_time,         sh.ship_time)             AS ship_time,
COALESCE(base.delivery_time,     dl.delivery_time)         AS delivery_time,
COALESCE(base.sign_time,         sg.sign_time)             AS sign_time,
COALESCE(base.finish_time,       fn.finish_time)           AS finish_time,

-- ★ 各阶段时长：根据最新时间字段重新计算
CASE WHEN COALESCE(base.pay_time, pay.pay_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.pay_time, pay.pay_time)) - UNIX_TIMESTAMP(base.create_time)) / 60
     END AS create_to_pay_min,

CASE WHEN COALESCE(base.out_stock_time, os.out_stock_time) IS NOT NULL
      AND COALESCE(base.pay_time, pay.pay_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.out_stock_time, os.out_stock_time))
         - UNIX_TIMESTAMP(COALESCE(base.pay_time, pay.pay_time))) / 3600.0
     END AS pay_to_outstock_h,

CASE WHEN COALESCE(base.ship_time, sh.ship_time) IS NOT NULL
      AND COALESCE(base.out_stock_time, os.out_stock_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.ship_time, sh.ship_time))
         - UNIX_TIMESTAMP(COALESCE(base.out_stock_time, os.out_stock_time))) / 3600.0
     END AS outstock_to_ship_h,

CASE WHEN COALESCE(base.delivery_time, dl.delivery_time) IS NOT NULL
      AND COALESCE(base.ship_time, sh.ship_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.delivery_time, dl.delivery_time))
         - UNIX_TIMESTAMP(COALESCE(base.ship_time, sh.ship_time))) / 3600.0
     END AS ship_to_delivery_h,

CASE WHEN COALESCE(base.sign_time, sg.sign_time) IS NOT NULL
      AND COALESCE(base.delivery_time, dl.delivery_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.sign_time, sg.sign_time))
         - UNIX_TIMESTAMP(COALESCE(base.delivery_time, dl.delivery_time))) / 3600.0
     END AS delivery_to_sign_h,

CASE WHEN COALESCE(base.sign_time, sg.sign_time) IS NOT NULL
     THEN (UNIX_TIMESTAMP(COALESCE(base.sign_time, sg.sign_time))
         - UNIX_TIMESTAMP(base.create_time)) / 3600.0
     END AS total_lead_time_h,

-- ★ 当前状态：从后往前判断
CASE
  WHEN COALESCE(base.finish_time,   fn.finish_time)   IS NOT NULL THEN '已完结'
  WHEN COALESCE(base.sign_time,     sg.sign_time)     IS NOT NULL THEN '已签收'
  WHEN COALESCE(base.delivery_time, dl.delivery_time) IS NOT NULL THEN '派送中'
  WHEN COALESCE(base.ship_time,     sh.ship_time)     IS NOT NULL THEN '运输中'
  WHEN COALESCE(base.out_stock_time,os.out_stock_time)IS NOT NULL THEN '已出库'
  WHEN COALESCE(base.shop_confirm_time, conf.confirm_time) IS NOT NULL THEN '商家已确认'
  WHEN COALESCE(base.pay_time,      pay.pay_time)     IS NOT NULL THEN '已支付'
  ELSE '待支付'
END AS current_status,

-- 是否完结
CASE WHEN COALESCE(base.finish_time, fn.finish_time) IS NOT NULL
     THEN 1 ELSE 0 END AS is_finished,

-- 是否超时（举例：72小时未签收即超时）
CASE WHEN COALESCE(base.sign_time, sg.sign_time) IS NULL
      AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(base.create_time)) / 3600 > 72
     THEN 1 ELSE 0 END AS is_timeout,

CURRENT_TIMESTAMP() AS etl_time,

-- ★ 分区字段：永远用订单创建日
DATE_FORMAT(base.create_time, 'yyyy-MM-dd') AS dt

FROM (
– ============ Step 1: 拼出今日要处理的所有订单 ============

-- 1.1 历史分区中所有未完结的订单（要继续推进生命周期）
SELECT * FROM dwd_trade_order_lifecycle
WHERE dt >= DATE_SUB('${bizdate}', 90)  -- 只看近90天，超期订单已稳定
  AND is_finished = 0

UNION ALL

-- 1.2 今日新创建的订单（首次入表）
SELECT
  o.order_id,
  o.user_id, o.shop_id, o.item_id,
  i.cate_level1_name, r.province_name, r.city_name,
  o.order_amount, o.pay_amount, o.buy_qty,

  o.create_time,
  CAST(NULL AS TIMESTAMP) AS pay_time,
  CAST(NULL AS TIMESTAMP) AS shop_confirm_time,
  CAST(NULL AS TIMESTAMP) AS out_stock_time,
  CAST(NULL AS TIMESTAMP) AS ship_time,
  CAST(NULL AS TIMESTAMP) AS delivery_time,
  CAST(NULL AS TIMESTAMP) AS sign_time,
  CAST(NULL AS TIMESTAMP) AS finish_time,

  NULL, NULL, NULL, NULL, NULL, NULL,           -- 时长全空
  '待支付' AS current_status,
  0 AS is_finished,
  0 AS is_timeout,
  CURRENT_TIMESTAMP() AS etl_time

FROM dwd_trade_order_create_di o
LEFT JOIN dim_item_df  i ON o.item_id = i.item_id  AND i.dt = '${bizdate}'
LEFT JOIN dim_region_df r ON o.city_code = r.city_code AND r.dt = '${bizdate}'
WHERE o.dt = '${bizdate}'

) base

– ============ Step 2: LEFT JOIN 各业务过程的最新事件 ============
– 每个 LEFT JOIN 都取截至昨日的最新一次事件

LEFT JOIN (
SELECT order_id, MAX(pay_time) AS pay_time
FROM dwd_trade_order_pay_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) pay ON base.order_id = pay.order_id

LEFT JOIN (
SELECT order_id, MAX(confirm_time) AS confirm_time
FROM dwd_trade_order_confirm_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) conf ON base.order_id = conf.order_id

LEFT JOIN (
SELECT order_id, MAX(out_stock_time) AS out_stock_time
FROM dwd_logi_outstock_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) os ON base.order_id = os.order_id

LEFT JOIN (
SELECT order_id, MAX(ship_time) AS ship_time
FROM dwd_logi_shipment_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) sh ON base.order_id = sh.order_id

LEFT JOIN (
SELECT order_id, MAX(delivery_time) AS delivery_time
FROM dwd_logi_delivery_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) dl ON base.order_id = dl.order_id

LEFT JOIN (
SELECT order_id, MAX(sign_time) AS sign_time
FROM dwd_logi_sign_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) sg ON base.order_id = sg.order_id

LEFT JOIN (
SELECT order_id, MAX(finish_time) AS finish_time
FROM dwd_trade_order_finish_di
WHERE dt <= ‘${bizdate}’
GROUP BY order_id
) fn ON base.order_id = fn.order_id;

---

五、关键设计点详解

1. 为什么用"订单创建日"分区？

方案A：按 ETL 执行日分区 ← ❌ 不推荐
- 同一订单的数据每天换一个分区
- 查询某订单要扫所有分区
- 数据冗余严重

方案B：按订单创建日分区 ← ✅ 推荐
- 同一订单永远在它创建那天的分区
- 查"双11订单履约"，dt=‘2026-11-11’ 一个分区搞定
- 历史完结订单可以单独归档

2. 为什么 ETL 不只处理"昨日的事件"？

错误思路：今天只处理昨天发生的事件，往最新分区追加
问题：
- 累积快照需要"全字段"持续更新
- 一个订单可能 06-09 创建，06-12 才签收
- 06-12 这天要更新的是 dt=‘2026-06-09’ 分区里的数据

正确思路：
- 每天重新计算所有"未完结"订单的完整状态
- INSERT OVERWRITE 重写它们所在的所有分区
- 已完结订单不再处理（数据已稳定）

3. INSERT OVERWRITE PARTITION (dt) 的含义

INSERT OVERWRITE TABLE dwd_trade_order_lifecycle PARTITION (dt)
SELECT …, DATE_FORMAT(create_time, ‘yyyy-MM-dd’) AS dt

这是 Hive 动态分区：

• 数据自动按 dt 字段值写入对应分区
• 涉及到的分区会被整个覆盖
• 不涉及的分区保持不变

例如今天处理后：

• dt=‘2026-06-09’ 分区被重写（包含未完结的6-9订单）
• dt=‘2026-06-10’ 分区被重写
• dt=‘2026-06-11’ 分区被新建
• dt=‘2026-03-01’ 分区不动（90天前的订单已稳定）

4. 性能优化技巧

a. 限制处理范围
WHERE dt >= DATE_SUB(‘${bizdate}’, 90) – 只处理近90天未结束的订单
AND is_finished = 0
绝大多数订单 7 天内完结，90 天足够了。

b. 已完结订单单独归档
– 每月一次，把90天前已完结的订单移到归档表
INSERT OVERWRITE TABLE dwd_trade_order_lifecycle_archive
SELECT * FROM dwd_trade_order_lifecycle
WHERE dt < DATE_SUB(‘${bizdate}’, 90) AND is_finished = 1;

c. 设置合适的 Hive 参数
– 启用动态分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

– 单个MR任务最大动态分区数
SET hive.exec.max.dynamic.partitions=1000;
SET hive.exec.max.dynamic.partitions.pernode=100;

– 启用向量化、CBO
SET hive.vectorized.execution.enabled=true;
SET hive.cbo.enable=true;

– 控制小文件
SET hive.merge.mapfiles=true;
SET hive.merge.mapredfiles=true;
SET hive.merge.size.per.task=256000000;

---

六、典型查询示例

查询1：双11当天订单的履约效率

SELECT
COUNT(*) AS 总订单数,
SUM(is_finished) AS 已完结数,
ROUND(AVG(create_to_pay_min), 1) AS 平均支付分钟,
ROUND(AVG(pay_to_outstock_h), 1) AS 平均出库小时,
ROUND(AVG(outstock_to_ship_h), 1) AS 平均揽收小时,
ROUND(AVG(ship_to_delivery_h), 1) AS 平均运输小时,
ROUND(AVG(total_lead_time_h), 1) AS 平均全程小时
FROM dwd_trade_order_lifecycle
WHERE dt = ‘2026-11-11’
AND is_finished = 1;

查询2：履约漏斗（每个阶段还有多少订单未推进）

SELECT
current_status,
COUNT() AS 订单数,
ROUND(COUNT() * 100.0 / SUM(COUNT(*)) OVER(), 2) AS 占比
FROM dwd_trade_order_lifecycle
WHERE dt = ‘2026-06-11’
GROUP BY current_status
ORDER BY
FIELD(current_status, ‘待支付’,‘已支付’,‘商家已确认’,‘已出库’,‘运输中’,‘派送中’,‘已签收’,‘已完结’);

查询3：分省份的履约时效对比

SELECT
province_name,
COUNT() AS 订单数,
ROUND(AVG(total_lead_time_h), 1) AS 平均全程小时,
SUM(is_timeout) AS 超时单数,
ROUND(SUM(is_timeout) * 100.0 / COUNT(), 2) AS 超时率
FROM dwd_trade_order_lifecycle
WHERE dt BETWEEN ‘2026-06-01’ AND ‘2026-06-11’
AND is_finished = 1
GROUP BY province_name
ORDER BY 平均全程小时 DESC;

---

七、实际生产中的两种实现路线对比

┌───────────────────────────────────┬────────────────────────────────────────────┬──────────────────────────────────────┬────────────────────────┐
│ 方式 │ 优点 │ 缺点 │ 适用规模 │
├───────────────────────────────────┼────────────────────────────────────────────┼──────────────────────────────────────┼────────────────────────┤
│ INSERT OVERWRITE 全量重建（本文） │ 兼容所有 Hive 版本；逻辑简单；数据一致性强 │ 每天要重写大量分区；耗资源 │ 中小规模（千万级订单） │
├───────────────────────────────────┼────────────────────────────────────────────┼──────────────────────────────────────┼────────────────────────┤
│ Hive 事务表 ACID UPDATE │ 真正的 UPDATE 语义；性能更好 │ 需要 Hive 0.14+ 且开启事务；运维复杂 │ 大规模 │
├───────────────────────────────────┼────────────────────────────────────────────┼──────────────────────────────────────┼────────────────────────┤
│ 拉链表 + 视图模拟 │ 性能好；存储省 │ 查询复杂 │ 状态变化稀疏的场景 │
└───────────────────────────────────┴────────────────────────────────────────────┴──────────────────────────────────────┴────────────────────────┘

生产中 90% 选第一种：稳定、简单、容易排查。

---

八、常见踩坑点

坑1：分区错位

错误：用 dt=‘${bizdate}’ 一个固定分区写所有数据
后果：所有订单堆在一个分区，分区无意义

正确：用 DATE_FORMAT(create_time, ‘yyyy-MM-dd’) 动态分区

坑2：忘记处理"重复事件"

源系统可能因重试推送多次相同事件，要在子查询里 GROUP BY 取 MAX：

SELECT order_id, MAX(pay_time) AS pay_time – ← 必须聚合
FROM dwd_trade_order_pay_di
GROUP BY order_id

坑3：时间字段被新事件覆盖

– 错误：直接用新事件的时间
pay.pay_time AS pay_time – ❌ 如果 pay 表里没数据，会变 NULL

– 正确：用 COALESCE 保留已有值
COALESCE(base.pay_time, pay.pay_time) AS pay_time – ✅

坑4：超时订单永不结束，越积越多

– 加个兜底：超过180天的订单强制标记为完结，不再处理
CASE WHEN UNIX_TIMESTAMP() - UNIX_TIMESTAMP(create_time) > 180 * 86400
THEN 1 ELSE is_finished END AS is_finished

---

九、调度配置（DolphinScheduler / Airflow）

任务依赖：
ods_trade_order_di ─┐
ods_trade_payment_di ─┤
ods_logi_shipment_di ─┼─→ dwd_trade_order_lifecycle
…所有事件源表的 DWD ─┘
│
↓
下游 DWS/ADS

调度周期：每日 02:00 启动
失败策略：失败重试 3 次，告警
监控指标：
- 任务时长 < 60 分钟
- 输出行数与昨日对比 ±20% 内
- is_finished=1 比例符合预期

---

十、一句话总结

▎ Hive 累积快照 = 用"INSERT OVERWRITE + 动态分区 + COALESCE 字段合并"模拟 UPDATE。
▎
▎ 核心三步：
▎ 1. 取数：未完结历史订单 + 今日新订单
▎ 2. 合并：LEFT JOIN 各业务过程最新事件，COALESCE 更新时间字段
▎ 3. 覆盖：按订单创建日动态分区写回
▎
▎ 性能要点：限制处理范围（近90天）+ 已完结订单归档 + ORC + 动态分区参数调优。