一、數(shù)據(jù)倉(cāng)庫(kù)
數(shù)據(jù)倉(cāng)庫(kù)是一個(gè)面向主題的、集成的、相對(duì)穩(wěn)定的、反應(yīng)歷史變化的數(shù)據(jù)集合，用于支持管理決策。
- 面向主題：傳統(tǒng)的數(shù)據(jù)庫(kù)是面向事務(wù)處理的，而數(shù)據(jù)倉(cāng)庫(kù)是面向某一領(lǐng)域而組織的數(shù)據(jù)集合，主題是指用戶關(guān)心的某一聯(lián)系緊密的集合。
- 集成：數(shù)據(jù)倉(cāng)庫(kù)中數(shù)據(jù)來(lái)源于各個(gè)離散的業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫(kù)、外部數(shù)據(jù)、非結(jié)構(gòu)化數(shù)據(jù)的集合，數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)是集成的。
- 相對(duì)穩(wěn)定：數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)不應(yīng)該支持dml操作，而是通過(guò)批處理方式進(jìn)行數(shù)據(jù)的處理。
- 反應(yīng)歷史：數(shù)據(jù)倉(cāng)庫(kù)保存了數(shù)據(jù)的歷史各個(gè)版本。
我們今天所介紹的就是數(shù)據(jù)倉(cāng)庫(kù)保留數(shù)據(jù)歷史版本的一種方法-拉鏈表。
這里我簡(jiǎn)單介紹一下我們數(shù)據(jù)倉(cāng)庫(kù)中掃采用的架構(gòu)，主要包括貼源層、明細(xì)層、匯總層、集市層、報(bào)表層、維度層，簡(jiǎn)單的介紹如下：
- 貼源層：采集的各個(gè)業(yè)務(wù)系統(tǒng)數(shù)據(jù)首先存儲(chǔ)在貼源層中，這里需要注意的是采集業(yè)務(wù)源數(shù)據(jù)的方法，增量采集還是全量采集，好的業(yè)務(wù)系統(tǒng)設(shè)計(jì)應(yīng)該支持增量采集（這里留一個(gè)問(wèn)題作為思考：增量采集數(shù)據(jù)應(yīng)該滿足哪些要求），這樣的好處減少了采集數(shù)據(jù)對(duì)倉(cāng)庫(kù)資源和業(yè)務(wù)系統(tǒng)資源的消耗。
- 明細(xì)層：該層采用規(guī)范化方式存儲(chǔ)數(shù)據(jù)，處理數(shù)據(jù)主要來(lái)自于貼源層，實(shí)現(xiàn)的目的主要包括面向主題設(shè)計(jì)存儲(chǔ)結(jié)構(gòu)、集成不同業(yè)務(wù)源數(shù)據(jù)、統(tǒng)一編碼規(guī)范、保留歷史數(shù)據(jù)（拉鏈表主要在這一層中進(jìn)行設(shè)計(jì)實(shí)現(xiàn)）等倉(cāng)庫(kù)基本要處理的
- 匯總層：對(duì)于明細(xì)層整合的數(shù)據(jù)，針對(duì)需要匯總的指標(biāo)按照業(yè)務(wù)口徑進(jìn)行計(jì)算并且初步反規(guī)范化設(shè)計(jì)實(shí)現(xiàn)連接明細(xì)層的規(guī)范化數(shù)據(jù)成小寬表，目的方便下一步處理使用。
- 集市層：面向不同需求方，按照維度建模方法，進(jìn)行星型模型設(shè)計(jì)， 這一層設(shè)計(jì)完成后的目的要達(dá)到可以方便出具報(bào)表和日常提數(shù)任務(wù)。這里有些倉(cāng)庫(kù)設(shè)計(jì)人員還會(huì)用另一個(gè)思路，即集市層不采用星型模型設(shè)計(jì)方法，而是設(shè)計(jì)大寬表，采用這種方式的設(shè)計(jì)人員主要理由是這種方式方便人們使用。
- 報(bào)表層：根據(jù)各個(gè)部門(mén)不同需求出具報(bào)表。
- 維度層：統(tǒng)一存儲(chǔ)數(shù)倉(cāng)維表相關(guān)數(shù)據(jù)。
目前數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)主要有兩個(gè)陣營(yíng)，kimball和inmon架構(gòu)，這里不會(huì)針對(duì)與這兩種放進(jìn)進(jìn)行詳細(xì)說(shuō)明。個(gè)人所接觸項(xiàng)目經(jīng)驗(yàn)，如果極端采用某一種架構(gòu)，最后數(shù)倉(cāng)項(xiàng)目成功概率都很低，因此個(gè)人建議結(jié)合兩種架構(gòu)的優(yōu)點(diǎn)進(jìn)行數(shù)倉(cāng)設(shè)計(jì)（即三范式簡(jiǎn)歷數(shù)倉(cāng)明細(xì)層，集市層采用星型模型設(shè)計(jì)方法），合理結(jié)合兩種思路優(yōu)點(diǎn)可以有效的避免業(yè)務(wù)驅(qū)動(dòng)方式帶來(lái)的煩雜工作以及需求驅(qū)動(dòng)所帶來(lái)的后期維護(hù)及擴(kuò)展性問(wèn)題。
二、拉鏈表原理
這里以一個(gè)虛擬的示例簡(jiǎn)單介紹拉鏈表實(shí)現(xiàn)原理：
1、比如在2017-01-01日，我們初始化了用戶數(shù)據(jù)到數(shù)據(jù)倉(cāng)庫(kù)，我們?yōu)槌跏蓟綌?shù)據(jù)倉(cāng)庫(kù)中的用戶表（customer）添加了一個(gè)start_date和end_date字段用來(lái)標(biāo)識(shí)該條數(shù)據(jù)的生命周期，具體如下：
cus_id job start_date end_date
----------------------------------------------------------------------
10001 oracle 2018-01-01 3000-12-21
10002 pgsql 2018-01-01 3000-12-21
10003 mysql 2018-01-01 3000-12-21
10004 java 2018-01-01 3000-12-21
10005 python 2018-01-01 3000-12-21
2、在2017-01-02這一天，10004用戶被刪除，同時(shí)增加了10006及10007用戶，10003用戶的job由mysql變成了mongodb，明細(xì)數(shù)據(jù)如下：
cus_id job start_date end_date
--------------------------------------------
10001 oracle 2018-01-01 3000-12-21
10002 pgsql 2018-01-01 3000-12-21
10003 mysql 2018-01-01 2018-01-02
10003 mongodb 2018-01-02 3000-12-21
10004 java 2018-01-01 2018-01-02
10005 python 2018-01-01 3000-12-21
10006 docker 2018-01-02 3000-12-21
10007 redis 2018-01-02 3000-12-21
3、在2017-01-03這一天，10007用戶被刪除，同時(shí)10006工作由docker變成了openstack，10003用戶工作由mongodb變成了hive，并且增加了10008用戶數(shù)據(jù)，明細(xì)數(shù)據(jù)如下：
cus_id job ???start_date end_date
---------------- ??----------------------------
10001 oracle ???2018-01-01 3000-12-21
10002 pgsql ???2018-01-01 3000-12-21
10003 mysql ???2018-01-01 2018-01-02
10003 mongodb ???2018-01-02 2018-01-03
10003 hive ? ???2018-01-03 3000-12-21
10004 java ??? ???2018-01-01 2018-01-02
10005 python ???2018-01-01 3000-12-21
10006 docker ???2018-01-02 2018-01-03
10006 openstack ??2018-01-03 3000-12-21
10007 redis ???????2018-01-02 2018-01-03
10008 hadoop ???2018-01-03 3000-12-21
拉鏈表原理分析：這里以10003用戶為例，通過(guò)記錄10003用戶數(shù)據(jù)變化時(shí)間線我們可以發(fā)現(xiàn)如下的規(guī)律：
2017-01-01 首次注冊(cè)，job為mysql；
2017-01-02 工作變更，job變?yōu)閙ongodb；
2017-01-03 工作變更，job變?yōu)閔ive。
在上圖中，10003用戶工作變更的時(shí)間線上，我們可以發(fā)現(xiàn)每一個(gè)時(shí)間點(diǎn)，10003用戶只有一個(gè)工作。在20170101~20170102期間內(nèi)10003的job為mysql，在20170102~20170103期間內(nèi)10003的job為mongodb，在20170103~30001231期間內(nèi)10003的job為hive。拉鏈表中每一個(gè)記錄都滿足上邊規(guī)律，下面讓我們想想怎么樣準(zhǔn)確的訪問(wèn)拉鏈表數(shù)據(jù)呢？
拉鏈表訪問(wèn)方法：
- 訪問(wèn)拉鏈表最新數(shù)據(jù)：
select * from customer t where t.end_date = '3000-12-31';
- 訪問(wèn)2017-01-01這天的歷史快照數(shù)據(jù)：
select * from customer t where t.start_date <= '2017-01-01' and t.end_date > '2017-01-01';
3、訪問(wèn)2017-01-02這天的歷史快照數(shù)據(jù)：
select * from customer t where t.start_date <= '2017-01-02' and t.end_date > '2017-01-02';
4、訪問(wèn)10003用戶所有歷史數(shù)據(jù)：
select * from customer t where t.cus_id = '10003';
三、拉鏈表實(shí)現(xiàn)步驟
1、準(zhǔn)備數(shù)據(jù)：
1）2017-01-01初始化數(shù)據(jù)：
| cus_id |
job |
start_date |
end_date |
dtype |
dw_status |
dw_ins_date |
| 10001 |
oracle |
2017-01-01 |
3000-12-31 |
C |
I |
2017-01-01 |
| 10002 |
pgsql |
2017-01-01 |
3000-12-31 |
C |
I |
2017-01-01 |
| 10003 |
mysql |
2017-01-01 |
3000-12-31 |
C |
I |
2017-01-01 |
| 10004 |
java |
2017-01-01 |
3000-12-31 |
C |
I |
2017-01-01 |
| 10005 |
python |
2017-01-01 |
3000-12-31 |
C |
I |
2017-01-01 |
2）2017-01-02增量數(shù)據(jù)：
| cus_id |
job |
dw_status |
dw_ins_date |
| 10003 |
mongodb |
U |
2017-01-02 |
| 10004 |
java |
D |
2017-01-02 |
| 10006 |
docker |
I |
2017-01-02 |
| 10007 |
redis |
I |
2017-01-02 |
3）2017-01-03增量數(shù)據(jù)：
| cus_id |
job |
dw_status |
dw_ins_date |
| 10003 |
hive |
U |
2017-01-03 |
| 10007 |
redis |
D |
2017-01-03 |
| 10006 |
openstack |
U |
2017-01-03 |
| 10008 |
hadoop |
I |
2017-01-03 |
2、數(shù)據(jù)加載過(guò)程：
- 初始化customer表：
drop table customer;
create table customer(
cus_id ????????int,
job ???????????varchar2(20),
start_date ????varchar2(10),
end_date ??????varchar2(10),
dtype ?????????varchar2(1),
dw_status ?????varchar2(1),
dw_ins_date ???varchar2(10)
)
partition by list(end_date)
(
partition cus_par20170101 values('2017-01-01') tablespace users,
partition cus_par20170102 values('2017-01-02') tablespace users,
partition cus_par20170103 values('2017-01-03') tablespace users,
partition cus_par30001231 values('3000-12-31') tablespace users
);
insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10001,'oracle','2017-01-01','3000-12-31','C','I','2017-01-01');
insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10002,'pgsql','2017-01-01','3000-12-31','C','I','2017-01-01');
insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10003,'mysql','2017-01-01','3000-12-31','C','I','2017-01-01');
insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10004,'java','2017-01-01','3000-12-31','C','I','2017-01-01');
insert into customer(cus_id,job,start_date,end_date,dtype,dw_status,dw_ins_date) values (10005,'python','2017-01-01','3000-12-31','C','I','2017-01-01');
- 初始化2017-01-02號(hào)增量表：
create table customer_inc(
cus_id ????????int,
job ???????????varchar2(20),
dw_status ?????varchar2(1),
dw_ins_date ???varchar2(10)
);
truncate table customer_inc;
insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10003,'mongodb','U','2017-01-02');
insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10004,'java','D','2017-01-02');
insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10006,'docker','I','2017-01-02');
insert into customer_inc(cus_id,job,dw_status,dw_ins_date)values(10007,'redis','I','2017-01-02');
- 創(chuàng)建中間表：
drop table customer_tmp0;
create table customer_tmp0(
cus_id ????????int,
job ???????????varchar2(20),
start_date ????varchar2(10),
end_date ??????varchar2(10),
dtype ?????????varchar2(1),
dw_status ?????varchar2(1),
dw_ins_date ???varchar2(10)
)
partition by list(dtype)
(
partition cus_dtype_H values('H') tablespace users,
partition cus_dtype_C values('C') tablespace users
);
3、刷新customer_inc表數(shù)據(jù)到customer表（2017-01-02）：
- customer表最新分區(qū)和customer_inc表中更新和刪除數(shù)據(jù)連接，處理customer最新分區(qū)中變化數(shù)據(jù)：
insert?into?customer_tmp0
select?
t1.cus_id,
t1.job,
t1.start_date,
case?when?t2.cus_id is?null?then?t1.end_date else?'2017-01-02'?end?as?end_date,
case?when?t2.cus_id is?null?then?'C'?else?'H'?end?dtype,
case?when?t2.cus_id is?null?then?t1.dw_status else?t2.dw_status end?dw_status,
case?when?t2.cus_id is?null?then?t1.dw_ins_date else?t2.dw_ins_date end?as?dw_ins_date
from?customer t1 left?join?customer_inc t2 on?t1.cus_id =?t2.cus_id and?t2.dw_status in?('D','U')
where?t1.end_date =?'3000-12-31'
order?by?cus_id asc
;
2）將customer表中更新和插入數(shù)據(jù)插入到customer_tmp0臨時(shí)表中：
insert?into?customer_tmp0
select?
t1.cus_id,
t1.job,
'2017-01-02'?as?start_date,
'3000-12-31'?as?end_date,
'C'?as?dtype,
t1.dw_status,
'2017-01-03'?as?dw_ins_date
from?customer_inc t1
where?t1.dw_status in?('I','U')
;
3）同步表到customer事實(shí)表，這一步可以使用交換分區(qū)操作：
alter?table?customer truncate?partition?cus_par30001231;
insert?into?customer
select?*?from?customer_tmp0;
4）查看結(jié)果：
SQL> select * from customer order by cus_id asc;
????CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
---------- -------------------- ---------- ---------- ----- --------- -----------
?????10001 oracle ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10002 pgsql ???????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10003 mysql ???????????????2017-01-01 2017-01-02 H ????U ????????2017-01-02
?????10003 mongodb ?????????????2017-01-02 3000-12-31 C ????U ????????2017-01-03
?????10004 java ????????????????2017-01-01 2017-01-02 H ????D ????????2017-01-02
?????10005 python ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10006 docker ??????????????2017-01-02 3000-12-31 C ????I ????????2017-01-03
?????10007 redis ???????????????2017-01-02 3000-12-31 C ????I ????????2017-01-03
8 rows selected
SQL>
4、刷新customer_inc表數(shù)據(jù)到customer表（2017-01-03）
1）初始化2017-01-02號(hào)增量表：
truncate?table?customer_inc;
insert?into?customer_inc(cus_id,job,dw_status,dw_ins_date)values(10003,'hive','U','2017-01-03');
insert?into?customer_inc(cus_id,job,dw_status,dw_ins_date)values(10008,'hadoop','I','2017-01-03');
insert?into?customer_inc(cus_id,job,dw_status,dw_ins_date)values(10006,'openstack','U','2017-01-03');
insert?into?customer_inc(cus_id,job,dw_status,dw_ins_date)values(10007,'redis','D','2017-01-03');
- customer表最新分區(qū)和customer_inc表中更新和刪除數(shù)據(jù)連接，處理customer最新分區(qū)中變化數(shù)據(jù)：
truncate?table?customer_tmp0;
insert?into?customer_tmp0
select?
t1.cus_id,
t1.job,
t1.start_date,
case?when?t2.cus_id is?null?then?t1.end_date else?'2017-01-03'?end?as?end_date,
case?when?t2.cus_id is?null?then?'C'?else?'H'?end?dtype,
case?when?t2.cus_id is?null?then?t1.dw_status else?t2.dw_status end?dw_status,
case?when?t2.cus_id is?null?then?t1.dw_ins_date else?t2.dw_ins_date end?as?dw_ins_date
from?customer t1 left?join?customer_inc t2 on?t1.cus_id =?t2.cus_id and?t2.dw_status in?('D','U')
where?t1.end_date =?'3000-12-31'
order?by?cus_id asc
;
3）將customer表中更新和插入數(shù)據(jù)插入到customer_tmp0臨時(shí)表中：
insert?into?customer_tmp0
select?
t1.cus_id,
t1.job,
'2017-01-03'?as?start_date,
'3000-12-31'?as?end_date,
'C'?as?dtype,
t1.dw_status,
'2017-01-04'?as?dw_ins_date
from?customer_inc t1
where?t1.dw_status in?('I','U')
;
- 表到customer事實(shí)表，這一步可以使用交換分區(qū)操作：
alter?table?customer truncate?partition?cus_par30001231;
insert?into?customer
select?*?from?customer_tmp0;
- 查看結(jié)果
SQL> select * from customer order by cus_id asc;
??????CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
----------- -------------------- ---------- ---------- ----- --------- -----------
??????10001 oracle ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
??????10002 pgsql ???????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
??????10003 mongodb ?????????????2017-01-02 2017-01-03 H ????U ????????2017-01-03
??????10003 hive ????????????????2017-01-03 3000-12-31 C ????U ????????2017-01-04
??????10003 mysql ???????????????2017-01-01 2017-01-02 H ????U ????????2017-01-02
??????10004 java ????????????????2017-01-01 2017-01-02 H ????D ????????2017-01-02
??????10005 python ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
??????10006 docker ??????????????2017-01-02 2017-01-03 H ????U ????????2017-01-03
??????10006 openstack ???????????2017-01-03 3000-12-31 C ????U ????????2017-01-04
??????10007 redis ???????????????2017-01-02 2017-01-03 H ????D ????????2017-01-03
??????10008 hadoop ??????????????2017-01-03 3000-12-31 C ????I ????????2017-01-04
11 rows selected
SQL>文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-839435.html
5、查詢拉鏈表：
- 查詢拉鏈表最新數(shù)據(jù)：
SQL> select * from customer where end_date = '3000-12-31' order by cus_id asc;
???CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
--------- -------------------- ---------- ---------- ----- --------- -----------
????10001 oracle ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
????10002 pgsql ???????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
????10003 hive ????????????????2017-01-03 3000-12-31 C ????U ????????2017-01-04
????10005 python ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
????10006 openstack ???????????2017-01-03 3000-12-31 C ????U ????????2017-01-04
????10008 hadoop ??????????????2017-01-03 3000-12-31 C ????I ????????2017-01-04
6 rows selected
SQL>
- 查詢2017-01-01歷史快照數(shù)據(jù)：
SQL> select * from customer where start_date <= '2017-01-01' and end_date > '2017-01-01' order by cus_id asc;
???CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
--------- -------------------- ---------- ---------- ----- --------- -----------
????10001 oracle ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
????10002 pgsql ???????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
????10003 mysql ???????????????2017-01-01 2017-01-02 H ????U ????????2017-01-02
????10004 java ????????????????2017-01-01 2017-01-02 H ????D ????????2017-01-02
????10005 python ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
SQL>
3）查詢2017-01-02歷史快照數(shù)據(jù)：
SQL> select * from customer where start_date <= '2017-01-02' and end_date > '2017-01-02' order by cus_id asc;
????CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
---------- -------------------- ---------- ---------- ----- --------- -----------
?????10001 oracle ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10002 pgsql ???????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10003 mongodb ?????????????2017-01-02 2017-01-03 H ????U ????????2017-01-03
?????10005 python ??????????????2017-01-01 3000-12-31 C ????I ????????2017-01-01
?????10006 docker ??????????????2017-01-02 2017-01-03 H ????U ????????2017-01-03
?????10007 redis ???????????????2017-01-02 2017-01-03 H ????D ????????2017-01-03
6?rows selected
SQL>
4）查看10003用戶的所有數(shù)據(jù)：
SQL> select * from customer where cus_id = '10003';
????CUS_ID JOB ?????????????????START_DATE END_DATE ??DTYPE DW_STATUS DW_INS_DATE
---------- -------------------- ---------- ---------- ----- --------- -----------
?????10003 mysql ???????????????2017-01-01 2017-01-02 H ????U ????????2017-01-02
?????10003 mongodb ?????????????2017-01-02 2017-01-03 H ????U ????????2017-01-03
?????10003 hive ????????????????2017-01-03 3000-12-31 C ????U ????????2017-01-04文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-839435.html
SQL>
到了這里，關(guān)于數(shù)據(jù)倉(cāng)庫(kù)保存歷史數(shù)據(jù)方法之拉鏈表的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
