数据仓库之拉链表设计
数据仓库之拉链表设计
目录
一、拉链表的使用场景
二、拉链表的设计和实现
1、数据需求
2、拉链表设计说明
三、在Hive中实现拉链表
1、创建ods层和dw层表
2、增量的sql实现
3、查询性能
四、拉链表的sparkSQL整合hive实现
0、拉链表的数据效果图
1、拉链表设计具体步骤
2、MySQL和ods层以及dw层数据说明
3、数据导入
一、拉链表的使用场景
在数据仓库的数据模型设计过程中,经常会遇到下面这种表的设计:
1)有一些表的数据量很大,比如一张用户表,大约10亿条记录,50个字段,这种表,即使使用ORC压缩,单张表的存储也会超过100G,在HDFS使用双备份或者三备份的话就更大一些。
2)表中的部分字段会被update更新操作,如用户联系方式,产品的描述信息,订单的状态等等。
3)需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态。
4)表中的记录变化的比例和频率不是很大,比如,总共有10亿的用户,每天新增和发生变化的有200万左右,变化的比例占的很小。
那么对于这种表我该如何设计呢?下面有几种方案可选:
方案一:每天只留最新的一份,比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
这种方案实现起来很简单,每天drop掉前一天的数据,重新抽一份最新的。优点很明显,节省空间,一些普通的使用也很方便,不用在选择表的时候加一个时间分区什么的。缺点同样明显,没有历史数据,先翻翻旧账只能通过其它方式,比如从流水表里面抽。
方案二:每天保留一份全量的切片数据。
每天一份全量的切片是一种比较稳妥的方案,而且历史数据也在。缺点就是存储空间占用量太大太大了,如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费。当然我们也可以做一些取舍,比如只保留近一个月的数据?但是,需求是无耻的,数据的生命周期不是我们能完全左右的。
方案三:使用拉链表。
拉链表在使用上基本兼顾了我们的需求。首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其实它能满足方案二所能满足的需求,既能获取最新的数据,也能添加筛选条件也获取历史的数据。所以我们还是很有必要来使用拉链表的。
二、拉链表的设计和实现
1、数据需求
我们先看一下在Mysql关系型数据库里的user表中信息变化。
在2017-01-01这一天表中的数据是:
注册日期 用户编号 手机号码2017-01-01 001 1111112017-01-01 002 2222222017-01-01 003 3333332017-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新增)
如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表,这是最新一天(即2017-01-03)的数据:
注册日期 用户编号 手机号码 t_start_date t_end_date2017-01-01 001 111111 2017-01-01 9999-12-312017-01-01 002 222222 2017-01-01 2017-01-012017-01-01 002 233333 2017-01-02 9999-12-312017-01-01 003 333333 2017-01-01 9999-12-312017-01-01 004 444444 2017-01-01 2017-01-012017-01-01 004 432432 2017-01-02 2017-01-022017-01-01 004 654321 2017-01-03 9999-12-312017-01-02 005 555555 2017-01-02 2017-01-022017-01-02 005 115115 2017-01-03 9999-12-312017-01-03 006 666666 2017-01-03 9999-12-31
2、拉链表设计说明
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的历史快照,判断生效时间小于等于2017-01-02,失效时间大于等于2017-01-02,则
select * from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’
三、在Hive中实现拉链表
1、创建ods层和dw层表
ods层的ods.user表
以日期作为分区字段,压缩格式为ORC
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层的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'; )
在dw层创建拉链表:dws.user_his
在这张表中没有分区,但压缩格式依旧是ORC
CREATE EXTERNAL TABLE dws.user_his ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '用户编号', t_start_date , t_end_dateCOMMENT '用户资料拉链表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORC LOCATION '/dws/user_his'; )
2、增量的sql实现
假设我们已经已经初始化了2017-01-01的日期,然后需要更新2017-01-02那一天的数据
实现思路:(对需要修改的数据进行update) UNION ALL (新增的数据)
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 ALL 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
3、查询性能
假如存放了5年的拉链数据,那么这张表势必会比较大,当查询的时候性能就比较低了,个人认为两个思路来解决:
在一些查询引擎中,我们对start_date和end_date做索引,这样能提高不少性能。
保留部分历史数据,比如说我们一张表里面存放全量的拉链表数据,然后再对外暴露一张只提供近3个月数据的拉链表。
四、拉链表的sparkSQL整合hive实现
现在介绍的这种设计方案与上面的方案大同小异,主要区别在于数据的存储格式,以及分区的设计,增量抽取数据时增加了临时表作为存储。
0、拉链表的数据效果图
1、拉链表设计具体步骤
1)第一次全量导入
所有的ODS层数据全量导入到拉链历史记录表中
2)增量导入(某天,例如:2021-07-21)
增量导入某天的数据到ODS分区
合并历史数据(通过连接查询的方式进行更新)
2、MySQL和ods层以及dw层数据说明
MySQL层数据和ods层数据保持一致
在ods层使用dt作为日期,对每日增量数据进行分区存储,即每天都会增加一个分区
ods层数据都是用parquet格式存储,snappy压缩格式,对于sparkSQL的支持是最好的
在dw层使用拉链表技术,不分区
dw层每张表都有一张对应的临时中间表
3、数据导入
使用sparkSQL进行数据操作全量导入(数据日期是20190909号之前的)
modifyTime,为数据的修改时间
dw_start_date,为数据的生效时间,只要当modifyTime不为空,就等于modifyTime的时间,否则就等于createTime创建时间的值
dw_end_date,为数据的失效时间,第一次全量导入时全部都初始化为"9999-12-31",寓意永久不失效
增量导入(将20190910号的数据导入到拉链表中)
1)使用kettle将20190910的数据抽取到ods
2)编写spark-sql更新历史数据(从ods层的20190910号分区获取数据)
dw_start_date,为数据的生效时间,此时不会做改变
dw_end_date,为数据的失效时间,如果dw_end_date依旧为"9999-12-31",并且ods层的20190910号分区中goodsId(即两张表中都用此商品Id时),把dw_end_date更新为昨天的日期"2019-09-09",否则日期不变
3)编写spark-sql获取当日数据
dw_start_date,为数据的生效时间,只要当modifyTime不为空,就等于modifyTime的时间,否则就等于createTime创建时间的值
dw_end_date,为数据的失效时间,此时不会做改变
4)先把数据合并到临时表中,第5步在把临时表中的数据插入到dw层的表中
5)将历史数据和当日数据导入到历史拉链表中及查询数据
作者:落花桂
出处:http://grenet.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
来源https://www.cnblogs.com/nthforsth/p/15088772.html