HIVE数据仓库——拉链表

在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：

有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些，表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等
需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态
表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小
针对以上表设计，拉链表满足既能获取最新的数据，也能添加筛选条件并获取历史的数据的要求。

在Hive中实现拉链表
目前Hive的表只能进行删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。
在实现拉链表之前，需要先确定一下有哪些数据源可以用：
1、需要一张ODS层的用户全量表，需要用它来初始化
2、每日的用户更新表

而且还需要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。
另外，对于每日的用户更新表该怎么获取，有以下方式拿到或者间接拿到每日的用户增量：

可以监听Mysql数据的变化，比如说用Canal，最后合并每日的变化，获取到最后的一个状态；假设每天都会获得一份切片数据，可以通过取两天切片数据的不同来作为每日更新表，这种情况下可以对所有的字段先进行concat，再取md5流水表，有每日的变更流水表
通过etl工具对操作型数据库按照时间字段增量抽取到ods或者数据仓库(每天抽取前一天的数据)，形成每天的增量数据(实际中使用最多的情形)。

ods层的user表
ods层的用户资料切片表的结构：
CREATE EXTERNAL TABLE ods.user ( 
  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'; 
) 

ods层的user_update表
还需要一张用户每日更新表
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'; 
) 

每日的更新语句：假设已经已经初始化了2023-01-01的日期，然后需要更新2023-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 '2023-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, 
           '2023-01-02' AS t_start_time, 
           '9999-12-31' AS t_end_time 
    FROM ods.user_update AS C 
) AS T 

拉链表实现方式二：
操作型数据库的用户表结构：

CREATE EXTERNAL TABLE ods.user ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '注册日期' ,
  last_modify_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';

假设ods.user表的业务主键为user_num+mobile作为联合主键。

每天增量抽取的用户表结构和抽取条件：
1）表结构和上面的表结构保持一致，我们取表名为ods.user_update
2）增量抽取条件：select * from ods.user where last_modify_date = ‘$date’

创建一张拉链表：
CREATE EXTERNAL TABLE dws.user_his ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '用户编号',
  last_modify_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'; 
) 

实现sql
merge into dws.user_his tar 
using
(
　　select user_num,mobile from ods.user_update 
) sou on tar.user_num=sou.user_num and tar.mobile=sou.mobile and tar.t_start_date < '$date' and tar.t_end_date >  '$date' 
when matched then 
update set tar.t_end_date='9999-12-31'
按照主键筛选，在dws.user_his表中出现过的并且现在为有效数据的，全部更新为闭链数据。

总结：
1、使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询
2、可以加上当前行状态标识，能快速定位到当前状态
3、在拉链表的设计中可以加一些内容，因为每天保存一个状态，如果在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大