推广 热搜: 公司  快速  中国  上海    未来  企业  政策  教师  系统 

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

   日期:2024-12-20     作者:caijiyuan    caijiyuan   评论:0    移动:http://fabua.ksxb.net/mobile/news/4577.html
核心提示:在数据仓库的数据模型设计过程中,经常会遇到这样的需求: 数据量比较大; 2. 表中的部分字段会被update,如用户

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

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

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

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

订单创建日期订单编号订单状态2012-06-20001支付完成(从创建到支付)2012-06-20002创建订单2012-06-20003支付完成2012-06-21004创建订单2012-06-21005创建订单

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

订单创建日期订单编号订单状态2012-06-20001支付完成(从创建到支付)2012-06-20002创建订单2012-06-20003已发货(从支付到发货)2012-06-21004创建订单2012-06-21005支付完成(从创建到支付)2012-06-22006创建订单

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

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

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

订单创建日期订单编号订单状态dw_begin_datedw_end_date2012-06-20001创建订单2012-06-202012-06-202012-06-20001支付完成2012-06-219999-12-312012-06-20002创建订单2012-06-209999-12-312012-06-20003支付完成2012-06-202012-06-212012-06-20003已发货2012-06-229999-12-312012-06-21004创建订单2012-06-219999-12-312012-06-21005创建订单2012-06-212012-06-212012-06-21005支付完成2012-06-229999-12-312012-06-22006创建订单2012-06-229999-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_datedw_end_date2012-06-20001支付完成2012-06-219999-12-312012-06-20002创建订单2012-06-209999-12-312012-06-20003支付完成2012-06-202012-06-212012-06-21004创建订单2012-06-219999-12-312012-06-21005创建订单2012-06-212012-06-21

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

订单创建日期订单编号订单状态2012-06-20001支付完成(从创建到支付)2012-06-20002创建订单2012-06-20003支付完成2012-06-21004创建订单2012-06-21005创建订单

 

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

 

关于这种历史拉链表的etl刷新策略和方法、

使用这种方式即可以记录历史,而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。

本文中假设

  1. 数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据
  2. 如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史
  3. 订单状态包括三个:创建、支付、完成
  4. 创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据
  5. 本文中的表和SQL都使用Hive的HQL语法
  6. 源系统中订单表结构为

CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) stored AS textfile;

7.在数据仓库的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据

CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIonED BY (day STRING)
stored AS textfile;

8. 在数据仓库的DW层,有一张订单的历史数据拉链表,存放订单的历史状态数据

CREATE TABLE t_dw_orders_his (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) stored AS textfile;

9. 暂未考虑Hive上表的查询性能问题,只实现功能

10. 2015-08-21至2015-08-23,每天原系统订单表的数据如下,红色标出的为当天发生变化的订单,即增量数据

在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前,需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODW,并刷新到DW。

以上面的数据为例,比如在2015-08-21这天做全量初始化,那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW

第一步,抽取全量数据到ODS
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′)
SELECt orderid,createtime,modifiedtime,status
FROM orders
WHERe createtime <= ‘2015-08-20′;

第二步,从ODS刷新到DW
INSERT overwrite TABLE t_dw_orders_his
SELECt orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
‘9999-12-31′ AS dw_end_date
FROM t_ods_orders_inc
WHERe day = ‘2015-08-20′;

完成后,DW订单历史表中数据如下

 
  1. spark-sql> select * from t_dw_orders_his;
  2. 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
  3. 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
  4. 3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31
  5. 4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31
  6. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  7. 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31
  8. 7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31
  9. Time taken: 2.296 seconds, Fetched 7 row(s)

 

每天,从源系统订单表中,将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘${day}‘)
SELECt orderid,createtime,modifiedtime,status
FROM orders
WHERe createtime = ‘${day}’ OR modifiedtime = ‘${day}';

注意:在ODS层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据。

从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表。

第一步,通过增量抽取,将2015-08-21的数据抽取到ODS
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-21′)
SELECt orderid,createtime,modifiedtime,status
FROM orders
WHERe createtime = ‘2015-08-21′ OR modifiedtime = ‘2015-08-21′;

ODS增量表中2015-08-21的数据如下

  1. spark-sql> select * from t_ods_orders_inc where day = '2015-08-21';
  2. 3 2015-08-19 2015-08-21 支付 2015-08-21
  3. 4 2015-08-19 2015-08-21 完成 2015-08-21
  4. 7 2015-08-20 2015-08-21 支付 2015-08-21
  5. 8 2015-08-21 2015-08-21 创建 2015-08-21
  6. Time taken: 0.437 seconds, Fetched 4 row(s)

第二步,通过DW历史数据(数据日期为2015-08-20),和ODS增量数据(2015-08-21),刷新历史表

先把数据放到一张临时表中

  1. DROp TABLE IF EXISTS t_dw_orders_his_tmp;
  2. CREATE TABLE t_dw_orders_his_tmp AS
  3. SELECT orderid,
  4. createtime,
  5. modifiedtime,
  6. status,
  7. dw_start_date,
  8. dw_end_date
  9. FROM (
  10. SELECt a.orderid,
  11. a.createtime,
  12. a.modifiedtime,
  13. a.status,
  14. a.dw_start_date,
  15. CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date
  16. FROM t_dw_orders_his a
  17. left outer join (SELECt * FROM t_ods_orders_inc WHERe day = '2015-08-21') b
  18. ON (a.orderid = b.orderid)
  19. UNIOn ALL
  20. SELECt orderid,
  21. createtime,
  22. modifiedtime,
  23. status,
  24. modifiedtime AS dw_start_date,
  25. '9999-12-31' AS dw_end_date
  26. FROM t_ods_orders_inc
  27. WHERe day = '2015-08-21'
  28. ) x
  29. ORDER BY orderid,dw_start_date;

其中
UNIOn ALL的两个结果集中,第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量,能关联上的,并且dw_end_date > ${yyy-MM-dd},说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。
第二个结果集是直接将增量数据插入历史表。

最后把临时表中数据插入历史表
INSERT overwrite TABLE t_dw_orders_his
SELECt * FROM t_dw_orders_his_tmp;

 

 

刷新完后,历史表中数据如下

 
  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
  3. 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
  4. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  5. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  6. 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
  7. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  8. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  9. 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31
  10. 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
  11. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  12. 8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31
  13. Time taken: 0.717 seconds, Fetched 11 row(s)
  14.  

由于在2015-08-21做了8月20日以前的数据全量初始化,而订单3、4、7在2015-08-21的增量数据中也存在,因此都有两条记录,但不影响后面的查询。

再看将2015-08-22的增量数据刷新到历史表

 
  1. INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22')
  2. SELECt orderid,createtime,modifiedtime,status
  3. FROM orders
  4. WHERe createtime = '2015-08-22' OR modifiedtime = '2015-08-22';
  5.  
  6. DROp TABLE IF EXISTS t_dw_orders_his_tmp;
  7. CREATE TABLE t_dw_orders_his_tmp AS
  8. SELECT orderid,
  9. createtime,
  10. modifiedtime,
  11. status,
  12. dw_start_date,
  13. dw_end_date
  14. FROM (
  15. SELECt a.orderid,
  16. a.createtime,
  17. a.modifiedtime,
  18. a.status,
  19. a.dw_start_date,
  20. CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-22' THEN '2015-08-21' ELSE a.dw_end_date END AS dw_end_date
  21. FROM t_dw_orders_his a
  22. left outer join (SELECt * FROM t_ods_orders_inc WHERe day = '2015-08-22') b
  23. ON (a.orderid = b.orderid)
  24. UNIOn ALL
  25. SELECt orderid,
  26. createtime,
  27. modifiedtime,
  28. status,
  29. modifiedtime AS dw_start_date,
  30. '9999-12-31' AS dw_end_date
  31. FROM t_ods_orders_inc
  32. WHERe day = '2015-08-22'
  33. ) x
  34. ORDER BY orderid,dw_start_date;
  35.  
  36.  
  37. INSERT overwrite TABLE t_dw_orders_his
  38. SELECt * FROM t_dw_orders_his_tmp;
  39.  

刷新完后历史表数据如下

 
  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  3. 1 2015-08-18 2015-08-22 支付 2015-08-22 9999-12-31
  4. 2 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  5. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  6. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  7. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  8. 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
  9. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  10. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  11. 6 2015-08-20 2015-08-20 创建 2015-08-20 2015-08-21
  12. 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31
  13. 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
  14. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  15. 8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21
  16. 8 2015-08-21 2015-08-22 支付 2015-08-22 9999-12-31
  17. 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31
  18. 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
  19. Time taken: 0.66 seconds, Fetched 17 row(s)
  20.  

 

查看2015-08-21的历史快照数据

 
  1. spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
  2. 1 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  3. 2 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  4. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  5. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  6. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  7. 6 2015-08-20 2015-08-20 创建 2015-08-20 2015-08-21
  8. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  9. 8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21

订单1在2015-08-21的时候还处于创建的状态,在2015-08-22的时候状态变为支付。

再刷新2015-08-23的增量数据

按照上面的方法刷新完后,历史表数据如下

 
  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  3. 1 2015-08-18 2015-08-22 支付 2015-08-22 2015-08-22
  4. 1 2015-08-18 2015-08-23 完成 2015-08-23 9999-12-31
  5. 2 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21
  6. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  7. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  8. 3 2015-08-19 2015-08-21 支付 2015-08-21 2015-08-22
  9. 3 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31
  10. 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
  11. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  12. 5 2015-08-19 2015-08-20 支付 2015-08-19 2015-08-22
  13. 5 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31
  14. 6 2015-08-20 2015-08-20 创建 2015-08-20 2015-08-21
  15. 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31
  16. 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
  17. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  18. 8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21
  19. 8 2015-08-21 2015-08-22 支付 2015-08-22 2015-08-22
  20. 8 2015-08-21 2015-08-23 完成 2015-08-23 9999-12-31
  21. 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31
  22. 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
  23. 11 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31
  24. 12 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31
  25. 13 2015-08-23 2015-08-23 支付 2015-08-23 9999-12-31

订单1从20号-23号,状态变化了三次,历史表中有三条记录。

 
  1. //查看2015-08-22当天的历史快照,可以看出,和上面图中2015-08-22时候订单表中的数据是一样的
  2. spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-22' and dw_end_date >= '2015-08-22';
  3. 1 2015-08-18 2015-08-22 支付 2015-08-22 2015-08-22
  4. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  5. 3 2015-08-19 2015-08-21 支付 2015-08-21 2015-08-22
  6. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  7. 5 2015-08-19 2015-08-20 支付 2015-08-19 2015-08-22
  8. 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31
  9. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  10. 8 2015-08-21 2015-08-22 支付 2015-08-22 2015-08-22
  11. 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31
  12. 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
  13. Time taken: 0.328 seconds, Fetched 10 row(s)
  14. //查看当前所有订单的最新状态
  15. spark-sql> select * from t_dw_orders_his where dw_end_date = '9999-12-31';
  16. 1 2015-08-18 2015-08-23 完成 2015-08-23 9999-12-31
  17. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  18. 3 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31
  19. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  20. 5 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31
  21. 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31
  22. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  23. 8 2015-08-21 2015-08-23 完成 2015-08-23 9999-12-31
  24. 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31
  25. 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
  26. 11 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31
  27. 12 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31
  28. 13 2015-08-23 2015-08-23 支付 2015-08-23 9999-12-31
  29. Time taken: 0.293 seconds, Fetched 13 row(s)
本文地址:http://fabua.ksxb.net/news/4577.html    海之东岸资讯 http://fabua.ksxb.net/ , 查看更多

特别提示:本信息由相关用户自行提供,真实性未证实,仅供参考。请谨慎采用,风险自负。

 
标签: 订单 支付
 
更多>同类最新资讯
0相关评论

文章列表
相关文章
最新动态
推荐图文
最新资讯
点击排行
网站首页  |  关于我们  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  排名推广  |  广告服务  |  积分换礼  |  网站留言  |  RSS订阅  |  违规举报  |  粤ICP备2023022329号