為什么要做拉鏈表

拉鏈表適合于：數據會發生新增和變化，但是大部分是不變的，且是緩慢變化的（如電商中用戶信息表中的手機號不可能每天都變化），如果是快速變化的（如每天一變），則每天做全量更新（事務型事實表）。主要目的是節省存儲空間。

拉鏈表的使用場景

在數據倉庫的數據模型設計過程中，經常會遇到下面這種表的設計：

• 1 有一些表的數據量很大，比如一張用戶表，大約10億條記錄，50個字段，這種表，即使使用ORC壓縮，單張表的存儲也會超過100G，在HDFS使用雙備份或者三備份的話就更大一些。
• 2 表中的部分字段會被update更新操作，如用戶聯系方式，產品的描述信息，訂單的狀態等等。
• 3 需要查看某一個時間點或者時間段的歷史快照信息，比如，查看某一個訂單在歷史某一個時間點的狀態。
• 4 表中的記錄變化的比例和頻率不是很大，比如，總共有10億的用戶，每天新增和發生變化的有200萬左右，變化的比例占的很小

什么是拉鏈表

記錄每條信息的生命周期，一旦一條信息的生命周期結束，就重新開始一條新紀錄，并把當前日期放入生效日期。 如果當前日期至今有效，在結束日期放入一個最大值，例如（9999-99-99）

- 優勢：訂單1經歷了四個生命周期，如果每天增量同步數據，則一年有365條數據，而拉鏈表只會產生4條數據

制作拉鏈表

首先初始化拉鏈表

通常要在原表的基礎上加入start_date,end_date,并從原表導入數據到拉鏈表

drop table if exists dwd_order_info_his; create external table dwd_order_info_his(`id` string COMMENT '訂單編號',`order_status` string COMMENT '訂單狀態',`start_date` string COMMENT '有效開始日期',`end_date` string COMMENT '有效結束日期' ) COMMENT '訂單拉鏈表'

創建臨時表整個中間數據

臨時表和拉鏈表表結構一致，只是為了存儲中間過程。

drop table if exists dwd_order_info_his_tmp; create external table dwd_order_info_his_tmp(`id` string COMMENT '訂單編號',`order_status` string COMMENT '訂單狀態',`start_date` string COMMENT '有效開始日期',`end_date` string COMMENT '有效結束日期' ) COMMENT '訂單拉鏈臨時表'

向臨時表中插入數據

插入前，拉鏈表數據

訂單新增表數據

插入數據后

訂單1和訂單2，訂單狀態都發生了改變，產生了新數據

具體sql

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' ) oion oh.id=oi.id )his order by his.id, start_date;

分為兩個部分數據 第一部分把新增修改表直接插入，這部分數據都是新鮮的，end_date是9999-99-99

第二部分是原拉鏈表和新增變化表做左連接，原表存在id且終止時間為無限的數據會保留，說明這部分數據需要變更。（如果右表id為空，說明這部分數據沒有任何改變，end_date保持不變，如果右表數據存在，說明經過了改變，則，end_date需要減1），所以這部分完成了歷史數據的更改

將拉鏈表數據用臨時表覆蓋

因為hive修改字段值不方便，直接全變覆蓋

insert overwrite table dwd_order_info_his select * from dwd_order_info_his_tmp;

總結

以上是生活随笔為你收集整理的单张表超过30个字段_拉链表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。