数据仓库之拉链表

大家好，欢迎来到IT知识分享网。

数据仓库之拉链表

• 一、常用表介绍
  • 1. 全量表
  • 2. 增量表
  • 3. 拉链表
  • 4. 流水表
  • 5. 拉链表 VS 流水表
• 二. 拉链表适用情况及优点
  • 1. 适用情况
  • 2. 优点
  • 三. 拉链表举例（订单域）
  • 1. 订单表结构
  • 2. 为实现订单表存储，常用解决方案及存在问题
    • （1）快照表
    • （2）全量历史表
  • ==（3）历史拉链表==
  • 3. 拉链表更新方案
    • （1）假设
    • （2）表结构及hivesql语句
    • （3）更新步骤
      • 1）全量初始化
        • 第一步：抽取全量数据到ODS
        • 第二步：从ODS刷新到DW
      • 2) 增量抽取
      • 3) 增量刷新历史数据
        • 第一步：通过增量抽取，将2015-08-21的数据抽取到ODS – 通过增量抽取，将2015-08-21的数据抽取到ODS：
        • 第二步：通过DW历史数据(数据日期为2015-08-20)，和ODS增量数据(2015-08-21)，刷新历史表
        • 第三步：通过增量抽取，将2015-08-22的数据抽取到ODS – 通过增量抽取，将2015-08-22的数据抽取到ODS：

一、常用表介绍

1. 全量表

每天的所有的最新状态的数据

2. 增量表

每天的新增数据

3. 拉链表

维护历史状态，以及最新状态数据

4. 流水表

对于表中的每一个修改都会记录，可以用于反映实际记录的变更

5. 拉链表 VS 流水表

二. 拉链表适用情况及优点

1. 适用情况

• 查看某一个订单在历史某一个时间点的状态
• 某一个用户在过去某一段时间，下单次数

2. 优点

• 满足反应数据的历史状态
• 最大程度节省存储

三. 拉链表举例（订单域）

1. 订单表结构

• 【1】6月20日订单表所有记录

• 【2】6月21日订单表所有记录

• 【3】6月22日订单表所有记录

2. 为实现订单表存储，常用解决方案及存在问题

（1）快照表

• 只保留一份全量，此时快照表与6.22的记录一样
• 若要查看6.21订单001的状态，则无法满足

（2）全量历史表

• 每天都保留一份全量，则数据库中的该表共有14条记录
• 存在重复保存的记录（订单002 004）
• 随着数据量的递增，会存在很大的存储浪费

（3）历史拉链表

• 增加两个字段：
  start_time(表示该条记录的生命周期开始时间——周期快照时的状态)
  end_time(该条记录的生命周期结束时间)
  
  
• end_time= ‘9999-12-31’ 表示该条记录目前处于有效状态
• 查询当前所有有效的记录:

select * from order_his where end_time = '9999-12-31'; 

• 查询2012-06-21的历史快照:

select * from order_his where start_time <= '2012-06-21' and end_time >= '2012-06-21'; 

3. 拉链表更新方案

（1）假设

• 数仓中订单历史表刷新频率为一天，当天更新前一天的增量数据
• 若一个订单在一天内存在多个变化，只会记录最后一个状态的历史
• 订单状态：创建、支付、完成
• 创建时间、修改时间只取到天
• 若原系统无修改时间，需要有机制确保抽取到每天的增量数据，如binlog解析，或者sqoop同步有过修改的数据

（2）表结构及hivesql语句

• 订单表【源系统】

CREATE TABLE orders ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) row format delimited fields terminated by '\t' 

•  
• 订单增量表【数仓ODS层，按天分区，存放每天的增量数据】

CREATE TABLE ods_orders_inc ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) PARTITIONED BY (day STRING) row format delimited fields terminated by '\t' 

• 历史数据拉链表【数仓DW层，存放订单的历史状态数据】

CREATE TABLE dw_orders_his ( orderid INT, createtime STRING, modifiedtime STRING, status STRING, dw_start_date STRING, dw_end_date STRING ) row format delimited fields terminated by '\t' ; 

2015-08-21至2015-08-23，每天订单系统的数据如下，黄色表示当天发生变化的订单，即增量数据

• 2015-08-21订单表：

• 2015-08-22订单表：

• 2015-08-23订单表：

（3）更新步骤

1）全量初始化

第一步：抽取全量数据到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′; 

2) 增量抽取

• 每天，从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表
• 这里的增量需要通过订单表中的创建时间和修改时间来确定
• 注意：在ODS层按天分区的增量表，最好保留一段时间的数据，比如半年，为了防止某一天的数据有问题而回滚重做数据

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '${day}') SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime = '${day}' OR modifiedtime = '${day}'; 

3) 增量刷新历史数据

• 从2015-08-22开始，需要每天正常刷新前一天（2015-08-21）的增量数据到历史表

第一步：通过增量抽取，将2015-08-21的数据抽取到ODS – 通过增量抽取，将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 = '2012-08-21' OR modifiedtime = '2012-08-21'; 

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

• 先把数据放到一张临时表中,UNION ALL的两个结果集中，第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量，能关联上的，并且dw_end_date > yyy−MM−dd，说明状态有变化，则把原来的dw_end_date置为({yyy-MM-dd}，说明状态有变化，则把原来的 dw\_ end\_ date 置为(yyy−MM−dd，说明状态有变化，则把原来的dw_end_date置为({yyy-MM-dd} – 1)；关联不上的，说明状态无变化，dw_end_date无变化。
  第二个结果集是直接将增量数据插入历史表。
  

DROP TABLE IF EXISTS t_dw_orders_his_tmp; CREATE TABLE t_dw_orders_his_tmp AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM (SELECT a.orderid, a.createtime, a.modifiedtime, a.status, a.dw_start_date, CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2012-06-21' THEN '2012-06-20' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2012-06-21') b ON (a.orderid = b.orderid) UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc WHERE day = '2012-06-21' ) x ORDER BY orderid,dw_start_date; 

• 最后把临时表中数据插入历史表：

INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; 

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

第三步：通过增量抽取，将2015-08-22的数据抽取到ODS – 通过增量抽取，将2015-08-22的数据抽取到ODS：

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21') SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime = '2012-08-21' OR modifiedtime = '2012-08-22'; 

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

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22') SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22'; DROP TABLE IF EXISTS t_dw_orders_his_tmp; CREATE TABLE t_dw_orders_his_tmp AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM ( SELECT a.orderid, a.createtime, a.modifiedtime, a.status, a.dw_start_date, 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 FROM t_dw_orders_his a left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-22') b ON (a.orderid = b.orderid) UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc WHERE day = '2015-08-22' ) x ORDER BY orderid,dw_start_date; INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp;