一碘耳、拉鏈表定義
記錄每條信息的生命周期胁黑，一旦一條記錄的生命周期結(jié)束，就重新開始一條新的記錄貌踏，并把當(dāng)前日期放入生效開始日期瓮增。如果當(dāng)前信息至今有效，在生效結(jié)束日其中填入一個極大值（如：9999-99-99）
生效日期 <= 某個日期 且 >=某個日期
二哩俭、為什么要做拉鏈表
拉鏈表適合于：數(shù)據(jù)會發(fā)生變化，但是大多數(shù)的數(shù)據(jù)是不變的拳恋，即緩慢變化維
比如: 訂單信息從未支付凡资、已支付、未發(fā)貨、已完成等狀態(tài)經(jīng)歷了一周隙赁，大部分時間是不變化的垦藏，如果數(shù)據(jù)量規(guī)模較大，按照每日全量的方式保存效率很低伞访，如：1億用戶 * 365天掂骏，每天一份用戶信息（每日全量效率低）。
三厚掷、拉鏈表形成過程
通過生效日期 <= 某個日期 且 >=某個日期弟灼，得到某個時間點的全量切片。
1）拉鏈表數(shù)據(jù)
2)例如：獲取2019-01-01的歷史切片：select * from order_info where start_date<='2019-01-01' and end_date>='2019-01-01';
3)例如：獲取2019-01-02的歷史切片：select * from order_info where start_date<='2019-01-02' and end_date>='2019-01-02';
四冒黑、拉鏈表制作流程圖
將首日全部數(shù)據(jù)作為初始拉鏈表dwd_order_info_his田绑；第二日，創(chuàng)建臨時拉鏈表和第二日變化數(shù)據(jù)（新增和變化）制作成一個新的拉鏈表抡爹，以此類推掩驱。
五、拉鏈表制作實戰(zhàn)
5.1初始化拉鏈表
1）建立拉鏈表
drop table if exists dwd_order_info_his;
create external table dwd_order_info_his(
`id` string COMMENT '訂單編號',
`total_amount` decimal(10,2) COMMENT '訂單金額',
`order_status` string COMMENT '訂單狀態(tài)',
`user_id` string COMMENT '用戶id' ,
`payment_way` string COMMENT '支付方式',
`out_trade_no` string COMMENT '支付流水號',
`create_time` string COMMENT '創(chuàng)建時間',
`operate_time` string COMMENT '操作時間',
`start_date` string COMMENT '有效開始日期',
`end_date` string COMMENT '有效結(jié)束日期'
) COMMENT '訂單拉鏈表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his/'
tblproperties ("parquet.compression"="snappy");
初始日期以2019-02-13為例冬竟。
2）初始化拉鏈表
從ods層直接將ods_order_info 所有數(shù)據(jù)拿過來
insert overwrite table dwd_order_info_his
select
id,
total_amount,
order_status,
user_id,
payment_way,
out_trade_no,
create_time,
operate_time,
'2019-02-13',
'9999-99-99'
from ods_order_info oi
where oi.dt='2019-02-13';
5.2 制作當(dāng)日變動數(shù)據(jù)（新增和修改）欧穴，每日執(zhí)行
（1）如何獲取每日變動表
- 表內(nèi)有創(chuàng)建時間和變動時間，可直接獲取
- 表內(nèi)沒有創(chuàng)建時間和變動時間泵殴，利用第三方工具監(jiān)控比如canal涮帘，監(jiān)控MySQL的實時變化進(jìn)行記錄
- 業(yè)務(wù)數(shù)據(jù)庫提供變動流水
我們dwd_order_info在我們的dwd層是根據(jù)每日partition的，所以我們根據(jù)日期倒過來的數(shù)據(jù)就是新增變動明細(xì)表袋狞。
5.3先合并變動信息焚辅，再追加新增信息，插入到臨時拉鏈表中
1）建立臨時表
drop table if exists dwd_order_info_his_tmp;
create table dwd_order_info_his_tmp(
`id` string COMMENT '訂單編號',
`total_amount` decimal(10,2) COMMENT '訂單金額',
`order_status` string COMMENT '訂單狀態(tài)',
`user_id` string COMMENT '用戶id' ,
`payment_way` string COMMENT '支付方式',
`out_trade_no` string COMMENT '支付流水號',
`create_time` string COMMENT '創(chuàng)建時間',
`operate_time` string COMMENT '操作時間',
`start_date` string COMMENT '有效開始日期',
`end_date` string COMMENT '有效結(jié)束日期'
) COMMENT '訂單拉鏈臨時表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his_tmp/'
tblproperties ("parquet.compression"="snappy");
2)導(dǎo)入腳本
insert overwrite table dwd_order_info_his_tmp
select * from
(
select
id,
total_amount,
order_status,
user_id,
payment_way,
out_trade_no,
create_time,
operate_time,
'2019-02-14' start_date,
'9999-99-99' end_date
from dwd_order_info where dt='2019-02-14'
union all
select oh.id,
oh.total_amount,
oh.order_status,
oh.user_id,
oh.payment_way,
oh.out_trade_no,
oh.create_time,
oh.operate_time,
oh.start_date,
if(oi.id is null, oh.end_date, date_add(oi.dt,-1)) end_date
from dwd_order_info_his oh left join
(
select
*
from dwd_order_info
where dt='2019-02-14'
) oi
on oh.id=oi.id and oh.end_date='9999-99-99'
)his
order by his.id, start_date;
解析：
訂單變化表 union all (初始拉鏈表dwd_order_info_his left join 訂單變化表dwd_order_info)
為什么要加
if(oi.id is null, oh.end_date, date_add(oi.dt,-1)) end_date
oi 訂單變化表苟鸯， oh 初始拉鏈表
因為訂單變化表中id在初始拉鏈表中不存在同蜻，為null，即為新增數(shù)據(jù)早处，則這條數(shù)據(jù)的結(jié)束日期就是該條數(shù)據(jù)的結(jié)束日期湾蔓，不需要改變。
訂單變化表中id在初始拉鏈表中存在砌梆，不為null默责，即為需要變化數(shù)據(jù)，則這條數(shù)據(jù)的添加到拉鏈表中時咸包，要將id相同的上一條數(shù)據(jù)的日期的9999-99-99改成date_add(oi.dt,-1)桃序，即新的這條數(shù)據(jù)的前一天的日期。
為什么要找出這條9999-99-99的數(shù)據(jù)烂瘫？
因為 相同id的數(shù)據(jù)有多條的時候媒熊，會將所有該id的數(shù)據(jù)的結(jié)束日期修改date_add(oi.dt,-1)，例如：
id status start_date end_date
1 1 2019-01-10 2019-02-13
1 2 2019-02-13 9999-99-99
如果此時來了一條數(shù)據(jù):" 1 3 2019-01-10 2019-02013",我們只能修改“1 2 2019-02-13 9999-99-99”不能修改“1 1 2019-01-10 2019-02-13”
3）把臨時表覆蓋給拉鏈表
insert overwrite table dwd_order_info_his
select * from dwd_order_info_his_tmp;
