在資料倉庫的資料模型設計過程中，經常會遇到這樣的需求：
1.資料量比較大;
2.表中的部分字段會被update,如使用者的位址，産品的描述資訊，訂單的狀态等等;
3.需要檢視某一個時間點或者時間段的曆史快照資訊，比如，檢視某一個訂單在曆史某一個時間點的狀态， 比如，檢視某一個使用者在過去某一段時間内，更新過幾次等等;
4.變化的比例和頻率不是很大，比如，總共有1000萬的會員，每天新增和發生變化的有10萬左右;
5.如果對這邊表每天都保留一份全量，那麼每次全量中會儲存很多不變的資訊，對存儲是極大的浪費;遇到些問題在沒解決嗎？
拉鍊曆史表，既能滿足反應資料的曆史狀态，又可以最大程度的節省存儲。
拉連結清單案例：
1、有一張訂單表，2019-06-20 這天裡面有三條訂單資料：
到了2019-06-21這天，表中有5條記錄：
到了2019-06-22日，表中有6條記錄：
這種存儲方式有什麼問題呢？
1、隻保留一份全量，則資料和6月22日的記錄一樣，如果需要檢視6月21日訂單001的狀态，則無法滿足；
2、每天都保留一份全量，則資料倉庫中的該表共有14條記錄，但好多記錄都是重複儲存，沒有任務變化，如訂單002,004，資料量大了，會造成很大的存儲浪費；
如果設計成曆史拉連結清單，如下：
拉連結清單解釋：
說明：
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 dw_end_date >= '2012-06-21'，這條語句會查詢到以下記錄：
可以看出，這樣的曆史拉連結清單，既能滿足對曆史資料的需求，又能很大程度的節省存儲資源；
拉連結清單實戰：
源系統中訂單表結構為：
CREATE TABLE orders_20190821 (orderid INT,createtime STRING,modifiedtime STRING,status STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','stored AS textfile;CREATE TABLE orders_20190822 (orderid INT,createtime STRING,modifiedtime STRING,status STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','stored AS textfile;CREATE TABLE orders_20190823 (orderid INT,createtime STRING,modifiedtime STRING,status STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','stored AS textfile;
資料：
2019-08-21訂單資料表(order)1,2019-08-18,2019-08-18,建立2,2019-08-18,2019-08-18,建立3,2019-08-19,2019-08-21,支付4,2019-08-19,2019-08-21,完成5,2019-08-19,2019-08-20,支付6,2019-08-20,2019-08-20,建立7,2019-08-20,2019-08-21,支付8,2019-08-21,2019-08-21,建立2019-08-22訂單資料表(order)*1,2019-08-18,2019-08-22,支付*2,2019-08-18,2019-08-22,完成3,2019-08-19,2019-08-21,支付4,2019-08-19,2019-08-21,完成5,2019-08-19,2019-08-20,支付*6,2019-08-20,2019-08-22,支付7,2019-08-20,2019-08-21,支付*8,2019-08-21,2019-08-22,支付*9,2019-08-22,2019-08-22,建立*10,2019-08-22,2019-08-22,支付2019-08-23訂單資料表1,2019-08-18,2019-08-23,完成2,2019-08-18,2019-08-22,完成3,2019-08-19,2019-08-23,完成4,2019-08-19,2019-08-21,完成5,2019-08-19,2019-08-23,完成6,2019-08-20,2019-08-22,支付7,2019-08-20,2019-08-21,支付8,2019-08-21,2019-08-23,完成9,2019-08-22,2019-08-22,建立10,2019-08-22,2019-08-22,支付11,2019-08-23,2019-08-23,建立12,2019-08-23,2019-08-23,建立13,2019-08-23,2019-08-23,支付
load data inpath '/user/root/csii/orders_20190821' into table orders_20190821;load data inpath '/user/root/csii/orders_20190822' into table orders_20190822;load data inpath '/user/root/csii/orders_20190823' into table orders_20190823;
ODS層設計：
在資料倉庫的ODS層，有一張訂單的增量資料表，按天分區，存放每天的增量資料：
CREATE TABLE ods_orders_inc (orderid INT,createtime STRING,modifiedtime STRING,status STRING)PARTITIONED BY (day STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','stored AS textfile;
DW層設計：
在資料倉庫的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 ','stored AS textfile;
第一步，抽取全量資料到ODS：
INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-20')SELECT orderid,createtime,modifiedtime,statusFROM orders_20190821WHERE createtime <= '2019-08-20';
第二步，從ODS重新整理到DW：
INSERT overwrite TABLE dw_orders_hisSELECT orderid,createtime,modifiedtime,status,createtime AS dw_start_date,'9999-12-31' AS dw_end_dateFROM ods_orders_incWHERE day = '2019-08-20';
完成後，DW訂單曆史表中資料:
hive> select * from dw_orders_his;1 2019-08-18 2019-08-18 建立 2019-08-18 9999-12-31 2 2019-08-18 2019-08-18 建立 2019-08-18 9999-12-313 2019-08-19 2019-08-21 支付 2019-08-19 9999-12-314 2019-08-19 2019-08-21 完成 2019-08-19 9999-12-315 2019-08-19 2019-08-20 支付 2019-08-19 9999-12-316 2019-08-20 2019-08-20 建立 2019-08-20 9999-12-317 2019-08-20 2019-08-21 支付 2019-08-20 9999-12-31
增量抽取
每天，從源系統訂單表中，将前一天的增量資料抽取到ODS層的增量資料表。 這裡的增量需要通過訂單表中的建立時間和修改時間來确定：
INSERT overwrite TABLE ods_orders_inc PARTITION (day = '${day}')SELECT orderid,createtime,modifiedtime,statusFROM ordersWHERE createtime = '${day}' OR modifiedtime = '${day}';
增量重新整理曆史資料
從2019-08-22開始，需要每天正常重新整理前一天(2019-08-21)的增量資料到曆史表。
第一步，通過增量抽取，将2019-08-21的資料抽取到ODS：
INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-21')SELECT orderid,createtime,modifiedtime,statusFROM orders_20190821WHERE modifiedtime = '2019-08-21' or createtime = '2019-08-21';
ODS增量表中2019-08-21的資料如下：
hive> select * from ods_orders_inc where day = '2019-08-21';3 2019-08-19 2019-08-21 支付 2019-08-214 2019-08-19 2019-08-21 完成 2019-08-217 2019-08-20 2019-08-21 支付 2019-08-218 2019-08-21 2019-08-21 建立 2019-08-21
第二步，通過DW曆史資料(資料日期為2019-08-20)，和ODS增量資料(2019-08-21)，重新整理曆史表：**
先把資料放到一張臨時表中：
DROP TABLE IF EXISTS dw_orders_his_tmp;CREATE TABLE 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 > '2019-08-21' THEN '2019-08-20' ELSE a.dw_end_date END AS dw_end_date FROM dw_orders_his a left outer join (SELECT * FROM ods_orders_inc WHERE day = '2019-08-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 ods_orders_inc WHERE day = '2019-08-21' ) x ORDER BY orderid,dw_start_date;
最後把臨時表中資料插入曆史表：
INSERT overwrite TABLE dw_orders_hisSELECT * FROM dw_orders_his_tmp;
檢視拉連結清單資料：
将2019-08-22的增量資料重新整理到曆史表:
第一步，通過增量抽取，将2019-08-22的資料抽取到ODS：INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-22') SELECT orderid,createtime,modifiedtime,status FROM orders_20190822WHERE createtime = '2019-08-22' OR modifiedtime = '2019-08-22';第二步，通過DW曆史資料(資料日期為2019-08-21)，和ODS增量資料(2019-08-22)DROP TABLE IF EXISTS dw_orders_his_tmp;CREATE TABLE 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 > '2019-08-22' THEN '2019-08-21' ELSE a.dw_end_date END AS dw_end_date FROM dw_orders_his a left outer join (SELECT * FROM ods_orders_inc WHERE day = '2019-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 ods_orders_inc WHERE day = '2019-08-22' ) x ORDER BY orderid,dw_start_date; 第三步：最後把臨時表中資料插入曆史表：INSERT overwrite TABLE dw_orders_his SELECT * FROM dw_orders_his_tmp;
重新整理完後曆史表資料如下：
檢視2019-08-21的曆史快照資料：
select * from dw_orders_his where dw_start_date <= '2019-08-21' and dw_end_date >= '2019-08-21';
将2019-08-23的增量資料重新整理到曆史表:
第一步，通過增量抽取，将2019-08-22的資料抽取到ODS：INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-23') SELECT orderid,createtime,modifiedtime,status FROM orders_20190823WHERE createtime = '2019-08-23' OR modifiedtime = '2019-08-23';第二步，通過DW曆史資料(資料日期為2019-08-22)，和ODS增量資料(2019-08-23)DROP TABLE IF EXISTS dw_orders_his_tmp;CREATE TABLE 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 > '2019-08-23' THEN '2019-08-22' ELSE a.dw_end_date END AS dw_end_date FROM dw_orders_his a left outer join (SELECT * FROM ods_orders_inc WHERE day = '2019-08-23') 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 ods_orders_inc WHERE day = '2019-08-23' ) x ORDER BY orderid,dw_start_date;第三步：最後把臨時表中資料插入曆史表：INSERT overwrite TABLE dw_orders_his SELECT * FROM dw_orders_his_tmp;
hive> select * from dw_orders_his order by orderid,dw_start_date;1 2019-08-18 2019-08-18 建立 2019-08-18 2019-08-211 2019-08-18 2019-08-22 支付 2019-08-22 2019-08-221 2019-08-18 2019-08-23 完成 2019-08-23 9999-12-312 2019-08-18 2019-08-18 建立 2019-08-18 2019-08-212 2019-08-18 2019-08-22 完成 2019-08-22 9999-12-313 2019-08-19 2019-08-21 支付 2019-08-19 2019-08-203 2019-08-19 2019-08-21 支付 2019-08-21 2019-08-223 2019-08-19 2019-08-23 完成 2019-08-23 9999-12-314 2019-08-19 2019-08-21 完成 2019-08-19 2019-08-204 2019-08-19 2019-08-21 完成 2019-08-21 9999-12-315 2019-08-19 2019-08-20 支付 2019-08-19 2019-08-225 2019-08-19 2019-08-23 完成 2019-08-23 9999-12-316 2019-08-20 2019-08-20 建立 2019-08-20 2019-08-216 2019-08-20 2019-08-22 支付 2019-08-22 9999-12-317 2019-08-20 2019-08-21 支付 2019-08-20 2019-08-207 2019-08-20 2019-08-21 支付 2019-08-21 9999-12-318 2019-08-21 2019-08-21 建立 2019-08-21 2019-08-218 2019-08-21 2019-08-22 支付 2019-08-22 2019-08-228 2019-08-21 2019-08-23 完成 2019-08-23 9999-12-319 2019-08-22 2019-08-22 建立 2019-08-22 9999-12-3110 2019-08-22 2019-08-22 支付 2019-08-22 9999-12-3111 2019-08-23 2019-08-23 建立 2019-08-23 9999-12-3112 2019-08-23 2019-08-23 建立 2019-08-23 9999-12-3113 2019-08-23 2019-08-23 支付 2019-08-23 9999-12-31
檢視最新的狀态：
hive> select * from dw_orders_his where dw_end_date = '9999-12-31';