kimball维度建模(5)-拉链表原理、设计以及在Hive中的实现

原帖地址：

漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）

什么是拉链表

　　拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史，存储的是事物最基本信息以及每条记录的生命周期。记录一个事物从开始，一直到当前状态的所有变化的信息。

　　等价于缓慢变化维的类型2：增加新行。即维度行中考虑增加三个属性：

　　1）行生效时间

　　2）行失效时间

　　3）当前行标识

拉链表使用场景

　　就是缓慢变化维类型2的使用场景：基础数据量比较大，其中少部分数据信息会发生更新，且后续使用需要访问信息历史

　　这时候有三种处理方式：

　　1）每天全量刷新，保存当前快照。优点是节省空间，缺点是没法访问历史信息

　　2）每天一份全量快照保存当天分区，对历史分区做生命周期管理。优点是保留了历史处理方便，缺点是占用存储过多，生命周期管理不好控制

　　3）使用拉链表。兼顾1）、2）的优点，既考虑了存储、又保留了历史

拉链表的设计与实现

设计上主要是要满足缓慢变化维类型2中新增的行生效、行失效及当前行标志三个属性，这里可以采用：

　　1）新增字段t_start_date表示该条记录的生命周期开始时间

　　2）新增字段t_end_date表示该条记录的生命周期结束时间。

　　3）其中t_end_date = '9999-12-31’表示该条记录目前处于有效状态。

在使用时：

　　1）如果查询当前所有有效的记录，则select * from user where t_end_date = ‘9999-12-31’。

　　2）如果查询2017-01-02的历史快照，则select * from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’。

hive拉链表实操

数据准备：

　　1）一张ODS层的用户全量表。至少需要用它来初始化，这里假设已经初始化好了。

　　2）每日的用户更新表。根据每天的更新数据去做拉链。

每日用户更新表结构：

CREATE EXTERNAL TABLE ods.user_update (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '注册日期'
COMMENT '每日用户资料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';
)

拉链表表结构：

CREATE EXTERNAL TABLE dws.user_his (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '用户编号',
  t_start_date ,
  t_end_date
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';
)

每天更新拉链表处理，假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据，我们有了下面的Sql：

INSERT OVERWRITE TABLE dws.user_his
SELECT * 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_num
UNION                                                --插入今天更新的数据
    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