拉链表
拉链表记录一个事物从开始,一直到当前状态的所有变化的信息。可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。
拉链表的使用场景
在数据仓库的数据模型设计过程中,经常会遇到下面这种表的设计:
- 有一些表的数据量很大,比如一张用户表,大约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层的use