微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

数据仓库数据模型之:极限存储–历史拉链表

数据仓库/数据库 lxw1234@qq.com 30971℃ 6评论

在数据仓库的数据模型设计过程中,经常会遇到这样的需求:

  1. 数据量比较大;
    2. 表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;
    3. 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,
    比如,查看某一个用户在过去某一段时间内,更新过几次等等;
    4. 变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右;
    5. 如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费;拉链历史表,既能满足反应数据的历史状态,又可以最大程度的节省存储;举个简单例子,比如有一张订单表,6月20号有3条记录:
订单创建日期 订单编号 订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成

 

到6月21日,表中有5条记录:

 

订单创建日期 订单编号 订单状态
2012-06-20 001 支付完成(从创建到支付)
2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单

 

到6月22日,表中有6条记录:

 

订单创建日期 订单编号 订单状态
2012-06-20 001 支付完成(从创建到支付)
2012-06-20 002 创建订单
2012-06-20 003 已发货(从支付到发货)
2012-06-21 004 创建订单
2012-06-21 005 支付完成(从创建到支付)
2012-06-22 006 创建订单

 

 

数据仓库中对该表的保留方法:

 

  1. 只保留一份全量,则数据和6月22日的记录一样,如果需要查看6月21日订单001的状态,则无法满足;
  2. 每天都保留一份全量,则数据仓库中的该表共有14条记录,但好多记录都是重复保存,没有任务变化,如订单002,004,数据量大了,会造成很大的存储浪费;

 

如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表:

 

 

订单创建日期 订单编号 订单状态 dw_begin_date dw_end_date
2012-06-20 001 创建订单 2012-06-20 2012-06-20
2012-06-20 001 支付完成 2012-06-21 9999-12-31
2012-06-20 002 创建订单 2012-06-20 9999-12-31
2012-06-20 003 支付完成 2012-06-20 2012-06-21
2012-06-20 003 已发货 2012-06-22 9999-12-31
2012-06-21 004 创建订单 2012-06-21 9999-12-31
2012-06-21 005 创建订单 2012-06-21 2012-06-21
2012-06-21 005 支付完成 2012-06-22 9999-12-31
2012-06-22 006 创建订单 2012-06-22 9999-12-31

 

 

说明:

 

  1. dw_begin_date表示该条记录的生命周期开始时间,dw_end_date表示该条记录的生命周期结束时间;
  2. dw_end_date = ‘9999-12-31’表示该条记录目前处于有效状态;
  3. 如果查询当前所有有效的记录,则select * from order_his where dw_end_date = ‘9999-12-31′
  4. 如果查询2012-06-21的历史快照,则select * from order_his where dw_begin_date <= ‘2012-06-21′ and end_date >= ‘2012-06-21’,这条语句会查询到以下记录:

 

订单创建日期 订单编号 订单状态 dw_begin_date dw_end_date
2012-06-20 001 支付完成 2012-06-21 9999-12-31
2012-06-20 002 创建订单 2012-06-20 9999-12-31
2012-06-20 003 支付完成 2012-06-20 2012-06-21
2012-06-21 004 创建订单 2012-06-21 9999-12-31
2012-06-21 005 创建订单 2012-06-21 2012-06-21

 

 

和源表在6月21日的记录完全一致:

 

订单创建日期 订单编号 订单状态
2012-06-20 001 支付完成(从创建到支付)
2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单

 

 

可以看出,这样的历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源;

 

关于这种历史拉链表的etl刷新策略和方法,下次再谈吧。。。


 

历史拉链表的etl刷新策略和方法已更新,请参考阅读:

数据仓库中历史拉链表的更新方法

 

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » 数据仓库数据模型之:极限存储–历史拉链表

喜欢 (65)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(6)个小伙伴在吐槽
  1. 您好:我发现一点问题,不知道是您数据的瑕疵,还是我理解与问题,所以特来和您讨论一下,在拉链表中,订单标号001在2012-06-20是创建订单,2012-06-21支付完成,那么对于创建订单的001这条记录,dw_begin_date是2012-06-20 是没有问题的,但是dw_end_dat是不是应该是2012-06-21。21号订单状态发生的变化,前一状态的才会失效。这是我的理解,请你给讲解一下
    大宇哥2018-01-26 17:43 回复
    • 观点一致。最近我们在实施大数据项目中,做拉链时,dw_end_date用的就是状态变化那天的日期,而不是像博主文章中的用状态变化前一天。。比如20号的那条记录,在21号状态发生变化,那dw_end_dat就设置为21号做闭链
      狂风2018-05-10 18:41 回复
      • hdfs上的数据不支持更新操作呢
        andrew2018-05-15 08:15 回复
        • 可以做表overwrite
          白桦林2019-10-17 06:45 回复
  2. 你好,我在看《大数据之路》的时候,介绍极限存储是对历史拉链表的优化,包括进行透明化处理和分月做历史拉链表。在你文中的意思是极限存储=历史拉链表?
    hannah2019-08-21 10:00 回复
  3. 极限存储拉链表文章没有说重跑任务的方法,文章里只写了case when t2.id is null and t1.dw_end_date '9999-12-31' then '9999-12-31' 但是这样重跑会有问题。于是为了方便历史数据追溯,如果结束时间等于今天且没有今天的更新数据,就把结束时间调整为9999-99-99 说明是后续跑明天的数据才会更新这条记录,相当于时间往回拨到重跑的时刻的状态,根本不知道未来会发生什么事,比如同条订单号时序状态还会不会变化就当不知道。如果不加t1.dw_end_date '9999-12-31',会把订单号重跑时刻之后如果存在的订单记录也改变了。这样配置后,验证了几天的数据才没问题。当然想重跑那个时刻,那个时刻后面的日期分区都要跟着一起重跑。 --记录当前订单结束状态 ( -- 此判断是为了重跑历史任务,不会造成订单结束时间异常 -- 比如:现有订单记录 -- 当前日期 订单 订单创建时间 订单修改时间 订单状态 开始时间 结束时间 -- 03-06 1 03-01 03-01 已支付 03-01 03-02 -- 1 03-01 03-03 已发货 03-03 03-05 -- 1 03-01 03-06 已完成 03-06 9999 -- 重跑4号的任务,保证 订单状态 记录变更为 -- 当前日期 订单 订单创建时间 订单修改时间 订单状态 开始时间 结束时间 -- 03-04 1 03-01 03-01 已支付 03-01 03-02 -- 1 03-01 03-03 已发货 03-03 9999 case when t2.id is null and t1.dw_end_date >= '${etl_date}' and t1.dw_end_date '9999-12-31' then '9999-12-31' -- 此处判断,是当订单状态发生变化时,修改当前订单状态的结束时间 when t2.id is not null and t1.dw_end_date >'${etl_date}' then date_sub('${etl_date}',1) else dw_end_date end ) as dw_end_date
    ctc2020-04-18 22:57 回复