資料倉庫ETL算法之拉鍊算法

目錄

拉鍊定義

拉連結清單資料存儲方式

拉鍊的意義

拉鍊算法詳解

拉鍊定義

• 現實社會：拉鍊是依靠排列的鍊牙，使物品并合或分離的連接配接件
• 資料倉庫：記錄資料在某一時間内的狀态以及資料在某一時點上的變化的資料存儲方式，也是應需求産生的技術解決方案

曆史存儲資料的倆種方式：

• 快照存儲
• 拉鍊存儲

下面用一組業務資料來解釋倆者差別：

業務系統2014年1月1日的資料

賬戶ID	戶名	餘額
001	張三	2000

業務系統2014年1月15日的資料

賬戶ID	戶名	餘額
001	張三	2000

業務系統2014年2月1日的資料

賬戶ID	戶名	餘額
001	張三	4000

 以上是賬戶ID001戶名為張三的業務資料，分别用倆種曆史資料存儲方式來存儲。

快照存儲方式如下：

快照存儲方式

賬戶ID	戶名	餘額	資料日期
001	張三	2000	2014-01-01
001	張三	2000	2014-01-15
001	張三	4000	2014-02-01

拉鍊存儲方式如下：

拉鍊存儲方式

賬戶ID	戶名	餘額	開始日期	結束日期
001	張三	2000	2014-01-01	2014-02-01（閉鍊）
001	張三	4000	2014-02-01（開鍊）	2999-12-31

資料存儲方式--新增

主鍵（賬戶ID）

新增資料：即主鍵新增，直接開鍊

拉連結清單資料存儲方式

資料存儲方式--删除

主鍵（賬戶ID）

删除資料：即資料删除，直接關鍊

資料存儲方式--修改

主鍵（賬戶ID）

修改資料：即屬性字段的更新，先關鍊，再開鍊

拉鍊的意義

• 節省存儲空間
• 記錄資料變化

拉鍊算法詳解

1.建立臨時表VT_NEW用于存放轉換、處理後的資料

create table VT_NEW as select * from TAG where 1=0;
insert into VT_NEW(ID,NAME,BAL,START_DT,END_DT)
select ID,NAME,BAL,'2014-01-04','2999-12-31'
from SRC;
           

2.建立臨時表VT_INC用于存放比對出的增量資料

create table VT_INC as select * from TAG where 1=0;
insert into VT_INC(ID,NAME,BAL,START_DT,END_DT)
select ID,NAME,BAL,START_DT,END_DT
from VT_NEW 
where (ID,NAME,BAL) not in(
select ID,NAME,BAL
from TAG
where END_DT = '2999-12-31');
           

3.修改目标表，進行關聯更新操作

update TAG
set END_DT = '2014-01-04'
where END_DT = '2999-12-31' and ID in(select ID from VT_INC)
           

4.修改目标表，進行開鍊插入操作

insert into TAG(ID,NAME,BAL,START_DT,END_DT)
select ID,NAME,BAL,START_DT,END_DT
from VT_INC where END_DT <>'1990-01-02';