CodeWalk

数据仓库拉链表(Slowly Changing Dimension Type 2)设计与实现

作者：Yahuda · 2026-05-30 12:55

请解释数据仓库中拉链表（SCD Type 2，也称为缓慢变化维类型2）的设计思想。拉链表如何记录历史变化？请用Hive SQL或Spark SQL给出一个用户维度拉链表的建表和增量更新MERGE语句，并说明查询当前快照和历史快照的方法。

回答

Yahuda

拉链表（SCD Type 2）设计思想：

  • 每条记录包含：业务主键 + 有效开始日期 + 有效结束日期 + 当前是否有效标志
  • 当维度属性变化时，不更新旧记录，而是关旧开新

建表DDL（Hive）：

CREATE TABLE dim_user_zip (
  user_id      INT,
  user_name    STRING,
  address      STRING,
  phone        STRING,
  start_date   STRING COMMENT '有效开始日期',
  end_date     STRING COMMENT '有效结束日期',
  is_current   INT COMMENT '1=当前,0=历史'
) PARTITIONED BY (dt STRING)
STORED AS ORC;

增量更新（MERGE逻辑，Spark SQL）：

-- 1. 关闭有变更的旧记录
UPDATE dim_user_zip SET 
  end_date = '2025-05-24',
  is_current = 0
WHERE is_current = 1 
  AND user_id IN (SELECT user_id FROM new_data);

-- 2. 插入新记录
INSERT INTO dim_user_zip
SELECT 
  user_id, user_name, address, phone,
  '2025-05-25' AS start_date,
  '9999-12-31' AS end_date,
  1 AS is_current,
  '2025-05-25' AS dt
FROM new_data;

查询方法：

  • 当前快照：SELECT * FROM dim_user_zip WHERE is_current=1
  • 历史快照（某时间点）：SELECT * FROM dim_user_zip WHERE '2025-01-15' >= start_date AND '2025-01-15' < end_date

注意事项：

  • 大表需分区（按end_date或dt）
  • Hive不支持非分区表UPDATE，需用INSERT OVERWRITE全量重刷
  • Spark 3.x支持Merge Into语法