网站导航
> 技术文档 > Hive 中拉链表的使用总结_hive 拉链表

Hive 中拉链表的使用总结_hive 拉链表

网友: 技术文档 2025-09-02 23:23:44

拉链表（Zipper Table）是维度建模中处理 SCD Type 2 的一种技术，用于记录维度表中某条记录的多个历史版本，每个版本都有一个生效时间和失效时间（或状态标识），从而形成一条“变更链”。

举例说明：

比如有一个用户维度表，用户的地址发生了变化：

user_id name address start_date end_date 1 Tom 北京 2025-01-01 2025-03-15 1 Tom 上海 2025-03-16 9999-12-31

这样就可以记录 Tom 的地址变化过程。

一、拉链表的设计要点

1. 字段设计

一个典型的拉链表字段包括：

  • 业务主键（如 user_id）
  • 描述性字段（如 name, address）
  • 开始时间（start_date）
  • 结束时间（end_date），通常用 9999-12-31 表示当前有效版本
  • （可选）是否当前标志（is_current）

2. 分区设计建议

拉链表一般按 start_date 或 end_date 进行分区，方便查询某一时间段的数据。

二、Hive 中拉链表的实现步骤

1. 初始化拉链表

第一次导入数据时，所有记录的 end_date 设置为 9999-12-31。

CREATE TABLE dim_user_zipper ( user_id INT, name STRING, address STRING, start_date STRING, end_date STRING) PARTITIONED BY (dt STRING);

初始加载数据：

INSERT OVERWRITE TABLE dim_user_zipper PARTITION (dt=\'2025-01-01\')SELECT user_id, name, address, \'2025-01-01\' AS start_date, \'9999-12-31\' AS end_dateFROM source_user_data;

2. 增量更新逻辑（每日ETL任务）

步骤说明：
  1. 找出源表中有变化的记录。
  2. 对拉链表中未过期的记录进行判断：
    • 如果字段值不同，则将其 end_date 更新为当天日期，并插入新记录。
    • 如果字段值相同，则保留原记录不变。
  3. 插入新增的记录。
示例SQL（伪代码）：
-- 拉取增量数据WITH new_data AS ( SELECT * FROM source_user_data WHERE dt = \'2025-06-17\'),-- 获取当前有效的记录current_records AS ( SELECT * FROM dim_user_zipper WHERE end_date = \'9999-12-31\'),-- 找出需要关闭的旧记录（有变化的）changed_records_to_close AS ( SELECT cr.* FROM current_records cr JOIN new_data nd ON cr.user_id = nd.user_id WHERE cr.name != nd.name OR cr.address != nd.address),-- 插入新的记录new_records_to_insert AS ( SELECT nd.user_id, nd.name, nd.address, \'2025-06-17\' AS start_date, \'9999-12-31\' AS end_date FROM new_data nd LEFT JOIN current_records cr ON nd.user_id = cr.user_id WHERE cr.user_id IS NULL OR nd.name != cr.name OR nd.address != cr.address)-- 合并写入INSERT OVERWRITE TABLE dim_user_zipper PARTITION (dt=\'2025-06-17\')SELECT user_id, name, address, start_date, CASE WHEN t.end_date = \'9999-12-31\' AND c.user_id IS NOT NULL THEN \'2025-06-17\' ELSE t.end_date END AS end_dateFROM dim_user_zipper tLEFT JOIN changed_records_to_close cON t.user_id = c.user_idWHERE t.dt < \'2025-06-17\' OR t.end_date != \'9999-12-31\'UNION ALLSELECT * FROM new_records_to_insert;

注意：上面 SQL 是简化版，实际中可能需要更复杂的逻辑来处理多天数据合并、性能优化等。

三、拉链表的优缺点

优点：

  • 能完整记录维度属性的历史变化；
  • 查询特定时间点的状态信息准确；
  • 支持审计与趋势分析。

缺点：

  • 存储成本较高（每条记录的变更都会生成新记录）；
  • 查询效率较低（需过滤 end_date）；
  • 实现逻辑较复杂，维护难度大。

四、示例

示例一

  • 源表（ods_user_info）：
user_id name phone address membership_level dt 1 Tom 123456789 北京 Gold 2025-06-17 2 Lucy 987654321 上海 Silver 2025-06-17 1 Tom 123456789 北京 Gold 2025-06-18 2 Lucy 987654321 广州 Platinum 2025-06-18
  • 拉链表更新后（dim_user_zipper）：
user_id name phone address membership_level start_date end_date etl_date 1 Tom 123456789 北京 Gold 2025-06-17 9999-12-31 2025-06-18 2 Lucy 987654321 上海 Silver 2025-06-17 2025-06-18 2025-06-18 2 Lucy 987654321 广州 Platinum 2025-06-18 9999-12-31 2025-06-18 
-- 当前拉链表中有效的用户记录WITH current_records AS ( SELECT * FROM dim_user_zipper WHERE end_date = \'9999-12-31\'),-- 新增或变更的数据（2025-06-18）new_data AS ( SELECT * FROM ods_user_info WHERE dt = \'2025-06-18\'),-- 需要关闭的旧记录（存在字段变更）changed_users_to_close AS ( SELECT cr.user_id FROM current_records cr JOIN new_data nd ON cr.user_id = nd.user_id WHERE cr.name != nd.name OR cr.phone != nd.phone OR cr.address != nd.address OR cr.membership_level != nd.membership_level),-- 插入新记录new_records AS ( SELECT nd.user_id, nd.name, nd.phone, nd.address, nd.membership_level, \'2025-06-18\' AS start_date, \'9999-12-31\' AS end_date FROM new_data nd LEFT JOIN current_records cr ON nd.user_id = cr.user_id WHERE cr.user_id IS NULL -- 新增用户 OR nd.name != cr.name OR nd.phone != cr.phone OR nd.address != cr.address OR nd.membership_level != cr.membership_level)-- 合并更新后的数据写入拉链表INSERT OVERWRITE TABLE dim_user_zipper PARTITION (etl_date=\'2025-06-18\')SELECT t.user_id, t.name, t.phone, t.address, t.membership_level, t.start_date, CASE WHEN t.end_date = \'9999-12-31\' AND c.user_id IS NOT NULL THEN \'2025-06-18\' -- 关闭旧记录 ELSE t.end_date -- 保留未变化记录 END AS end_dateFROM dim_user_zipper tLEFT JOIN changed_users_to_close cON t.user_id = c.user_idWHERE t.etl_date < \'2025-06-18\' -- 保留历史数据 OR t.end_date != \'9999-12-31\' -- 保留已失效记录UNION ALL-- 插入新记录SELECT user_id, name, phone, address, membership_level, start_date, end_dateFROM new_records;

示例二

在2017-01-01这一天表中的数据是：

注册日期 用户编号 手机号码 2017-01-01 001 111111 2017-01-01 002 222222 2017-01-01 003 333333 2017-01-01 004 444444

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

注册日期 用户编号 手机号码 备注 2017-01-01 001 111111 2017-01-01 002 233333 （由222222变成233333） 2017-01-01 003 333333 2017-01-01 004 432432 （由444444变成432432） 2017-01-02 005 555555 （2017-01-02新增）

在2017-01-03这一天表中的数据是， 用户004和005资料进行了修改，006是新增用户：

注册日期 用户编号 手机号码 备注 2017-01-01 001 111111 2017-01-01 002 233333 2017-01-01 003 333333 2017-01-01 004 654321 （由432432变成654321） 2017-01-02 005 115115 （由555555变成115115） 2017-01-03 006 666666 （2017-01-03新增）

说明

  • t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。
  • t_end_date = ‘9999-12-31’表示该条记录目前处于有效状态。
  • 如果查询当前所有有效的记录，则select * from user where t_end_date = ‘9999-12-31’。
  • 如果查询2017-01-02的历史快照，则select * from user where t_start_date = ‘2017-01-02’。

有3种方式拿到或者间接拿到每日的用户增量

  1. 我们可以监听Mysql数据的变化，比如说用Canal，最后合并每日的变化，获取到最后的一个状态。
  2. 假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这种情况下我们可以对所有的字段先进行concat，再取md5，这样就ok了。
  3. 流水表！有每日的变更流水表。

每日的更新语句：

INSERT OVERWRITE TABLE dws.user_hisSELECT * FROM( SELECT A.user_num,  A.mobile,  A.reg_date,  A.t_start_time,  CASE WHEN A.t_end_time = \'9999-12-31\' AND B.user_num IS NOT NULL THEN \'2017-01-01\' ELSE A.t_end_time  END AS t_end_time FROM dws.user_his AS A LEFT JOIN ods.user_update AS B ON A.user_num = B.user_numUNION SELECT C.user_num,  C.mobile,  C.reg_date,  \'2017-01-02\' AS t_start_time,  \'9999-12-31\' AS t_end_time FROM ods.user_update AS C) AS T
网络标签：

相关问题