拉链表 流水表

拉链表介绍

部分转载自36大数据(36dsj.com): 36大数据» 漫谈数据仓库之拉链表(原理、设计以及在Hive中的实现)

什么是拉链表:可自定义的部分历史记录表,属于数据仓库特有的定义范围

什么是流水表:同样是历史记录表

两者的不同:拉链表自定义变更范围,拉链表字段比流水表少。比如一个大表有100个字段,每次所有字段的变更都记录到流水表中,这是我只想记录 其中3个字段的每日最终一次变更,就用到了拉链表。

拉链表关键问题:如何特定字段的将新增与变更存入拉链表

为什么用到拉链表:
前置信息:8000万线上用户信息表,100个字段,部分信息变更,但是变更的不频繁。
需求:需要查看 用户地址变更 的历史记录,且如果一天修改n次,只保留最后一次变更

or

前置信息:订单表 100g数据,每日新增1000万数据,修改100万数据,
需求:只分析订单金额、订单状态的变更历史

在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新增)

如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表,这是最新一天(即2017-01-03)的数据:

备注:使用拉链表的时候可以不加t_end_date,即失效日期,但是加上之后,能优化很多查询。

注册日期 用户编号 手机号码 t_start_date t_end_date
2017-01-01 001 111111 2017-01-01 9999-12-31
2017-01-01 002 222222 2017-01-01 2017-01-01
2017-01-01 002 233333 2017-01-02 9999-12-31
2017-01-01 003 333333 2017-01-01 9999-12-31
2017-01-01 004 444444 2017-01-01 2017-01-01
2017-01-01 004 432432 2017-01-02 2017-01-02
2017-01-01 004 654321 2017-01-03 9999-12-31
2017-01-02 005 555555 2017-01-02 2017-01-02
2017-01-02 005 115115 2017-01-03 9999-12-31
2017-01-03 006 666666 2017-01-03 9999-12-31
说明

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’ and t_end_date >= ‘2017-01-02’。(此处要好好理解,是拉链表比较重要的一块。*

如果原始表中存在updatetime字段

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

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

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

注册日期 用户编号 手机号码 备注 变更时间
2017-01-01 001 111111 20170101
2017-01-01 002 233333 (由222222变成233333) 20170102
2017-01-01 003 333333
2017-01-01 004 432432 (由444444变成432432) 20170102
2017-01-02 005 555555 (2017-01-02新增) 20170102

select 用户编号、注册时间、手机号 from xx where updatetime=20170102,再cancat+md5 ,若不同,则存储拉链表即可,一般的由于变更的数据量不大,没有必要再进行cancat+md5比较,直接将所有update存入拉链表再去重即可。

获取原始表变更的几种方式

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

创建拉链表:
现在我们创建一张拉链表:

CREATEEXTERNALTABLEdws.user_his (
user_numSTRINGCOMMENT’用户编号’,
mobileSTRINGCOMMENT’手机号码’,
reg_dateSTRINGCOMMENT’用户编号’,
t_start_date ,
t_end_dateCOMMENT’用户资料拉链表’ROWFORMATDELIMITEDFIELDSTERMINATEDBY’\t’LINESTERMINATEDBY’\n’STOREDASORC
LOCATION’/dws/user_his’;
)
实现sql语句

然后初始化的sql就不写了,其实就相当于是拿一天的ods层用户表过来就行,我们写一下每日的更新语句。

现在我们假设我们已经已经初始化了2017-01-01的日期,然后需要更新2017-01-02那一天的数据,我们有了下面的Sql。

然后把两个日期设置为变量就可以了。

INSERTOVERWRITETABLEdws.user_hisSELECT*FROM(SELECTA.user_num,
A.mobile,
A.reg_date,
A.t_start_time,CASEWHENA.t_end_time =’9999-12-31’ANDB.user_numISNOTNULLTHEN’2017-01-01’ELSEA.t_end_timeENDASt_end_timeFROMdws.user_hisASALEFTJOINods.user_updateASBONA.user_num = B.user_numUNIONSELECTC.user_num,
C.mobile,
C.reg_date,’2017-01-02’ASt_start_time,’9999-12-31’ASt_end_timeFROMods.user_updateASC
)AST

补充
好了,我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表,下面对拉链表做一些小的补充。

拉链表和流水表
流水表存放的是一个用户的变更记录,比如在一张流水表中,一天的数据中,会存放一个用户的每条修改记录,但是在拉链表中只有一条记录。

这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些,一般按天就足够。

查询性能
拉链表当然也会遇到查询性能的问题,比如说我们存放了5年的拉链数据,那么这张表势必会比较大,当查询的时候性能就比较低了,个人认为两个思路来解决:

在一些查询引擎中,我们对start_date和end_date做索引,这样能提高不少性能。
保留部分历史数据,比如说我们一张表里面存放全量的拉链表数据,然后再对外暴露一张只提供近3个月数据的拉链表。
0xFF 总结
我们在这篇文章里面详细地分享了一下和拉链表相关的知识点,但是仍然会有一会遗漏。欢迎交流。

在后面的使用中又有了一些心得,补充进来:

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

  • 1
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值