柚子快報激活碼778899分享：數倉基礎知識
柚子快報激活碼778899分享：數倉基礎知識
拉鏈表
沒錯，就像衣服的拉鏈一樣重要，實用性非常強，使用頻率非常高。
拉鏈表核心思想，像個拉鏈，支持開鏈，支持閉鏈，支持退鏈，我們通常將最新的數據稱為開鏈數據，歷史數據稱為閉鏈數據，拉鏈表支持歷史數據查詢，且空間占用較小，但是數據加工處理較為繁瑣，屬于時間換空間的設計方式，拉鏈表一個時間維度中同一個用戶只保存一條用戶狀態(tài)。拉鏈表通常會增加三個技術字段“開始日期starttime、結束日期endtime、狀態(tài)標識mark”。通過主鍵（PK）與歷史數據進行對比，判斷當前數據與歷史數據是否發(fā)生變化，如果發(fā)生變化或者新增則進行相應的開鏈、閉鏈操作。
1.1 用戶基礎表拉鏈示例 以下使用用戶基本信息表對拉鏈表操作進行深入刨析 2021-01-01用戶基礎表原始數據如下：
userID為主鍵，可變化字段為登錄名(userName)、電話號碼(phoneNum)、賬號狀態(tài)(status)、最近登錄日期(lastLoginDate)。
setp1：首先設計拉鏈表的主鍵，根據原始數據表的表結構，選擇userID作為拉鏈表PK鍵；
setp2：選擇phoneNum、status作為notPK鍵，notPK作為對比字段，這里去除了lastLoginDate與dataTime這兩個經常變化的字段，如果需要跟蹤登陸日期lastLoginDate數據，請使用事件表，notPK鍵選擇原則需要同時滿足如下條件：
會發(fā)生變化的字段，且滿足緩慢變化維SCD；
不能選擇每天都發(fā)生變化的無對比意義字段（如dataTime字段，對于原始數據表這個字段是每天都變化的，所以沒有對比意義）；
setp3：確定數倉技術字段，本次加入數據加工日期etlTimestamp、開始時間startTime、結束時間endTime、標識位mark("i":新增，"u":修改，"d":刪除)四個技術字段；
數據采集方式為T+1方式（今天計算昨天的數據）。
2021-01-02用戶拉鏈表如下：
第一次加載因為拉鏈表歷史數據為空，所以所有數據都為新增數據，標識位標為新增狀態(tài)"i"，開始時間為數據日期2021/1/1，當前所有數據都為最新數據，多以結束時間設置為一個較大的時間2999/12/31作為開鏈時間標識日期。
2021-01-02用戶基礎表原始數據如下：
使用原始數據表主鍵userID關聯(lián)拉鏈表中開鏈數據(where endTime=2999/12/31)的userID，對比notPK字段是否相同，我們選擇的notPK字段為phoneNum和status，發(fā)生變化的數據： coolniu2021a0001用戶電話號碼發(fā)生變化； coolniu2021a0005用戶電話號碼發(fā)生變化；
coolniu2021a0007用戶狀態(tài)發(fā)生變化；
新增coolniu2021a0008、coolniu2021a0009、coolniu2021a0010三位用戶；
2021-01-03用戶拉鏈表如下：
coolniu2021a0001用戶拉鏈分析
coolniu2021a0001電話號碼發(fā)生變化，在拉鏈表中將結束日期修改為變化時間的數據日期2021/1/2
代表coolniu2021a0001用戶上一個狀態(tài)結束。 又新增了一行coolniu2021a0001的數據
針對這條數據新的狀態(tài)開始時間startTime為變化日期的數據日期2021/1/2，結束時間標識為2999/12/31（代表最新開鏈狀態(tài)），標識位"u"（代表這條數據是修改狀態(tài)）。 coolniu2021a0005用戶拉鏈分析 coolniu2021a0005與coolniu2021a0001用戶一樣，都是電話號碼發(fā)生了變化，拉鏈處理方式與coolniu2021a0001一致。
coolniu2021a0007用戶拉鏈分析
coolniu2021a0007用戶狀態(tài)由1變成了銷戶狀態(tài)"2"，拉鏈表處理：
coolniu2021a0007賬號狀態(tài)為1的數據，結束時間修改為數據發(fā)生變化的時間2021/1/2
新增一條coolniu2021a0007記錄，標識位mark標識為"d"，開始時間為數據變化時間2021/1/2，結束時間修改為2021/1/2，表示該條數據已經閉鏈。
新增用戶拉鏈分析 coolniu2021a0008、coolniu2021a0009、coolniu2021a0010三個用戶為2021-01-02日新增用戶，開始時間為2021/1/2，結束時間為2999/12/31（標識該數據為最新開鏈狀態(tài)），mark位標為新增標識"i"。
未變化用戶拉鏈分析 用戶coolniu2021a0002、coolniu2021a0003、coolniu2021a0004、coolniu2021a0006用戶notPK數據未變化，所以保持不變。
2021-01-03用戶基礎表原始數據如下：
1月3日新增了一條coolniu2021a0011數據。 2021-01-04用戶拉鏈表如下：
因為其他天沒有發(fā)生變化，所以在拉鏈表中保持不變，只新增一條coolniu2021a0011的記錄。
1.2 拉鏈表算法示例 1.2.1 建表 source_table
CREATE TABLE source_table( userid string, loginname string, regiondate string, phonenum string, birthday string, status string, lastlogindate string) PARTITIONED BY (datatime string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘|’ STORED AS TEXTFILE;
1.2.2 load數據
load data local INPATH ‘/tmp/xinniu/20210101’ into table source_table partition (datatime=‘20210101’); load data local INPATH ‘/tmp/xinniu/20210102’ into table source_table partition (datatime=‘20210102’); load data local INPATH ‘/tmp/xinniu/20210103’ into table source_table partition (datatime=‘20210103’);
1.2.3 創(chuàng)建拉鏈表
CREATE TABLE zip_table( userid string, loginname string, regiondate string, phonenum string, birthday string, status string, lastlogindate string, datatime string, etltimestamp string, starttime string, endtime string, mark string)
1.2.4 創(chuàng)建算法文件sqlfile
vim /tmp/xinniu/sqlfile
填寫如下內容
– 創(chuàng)建一張拉鏈表的備份表 備份拉鏈表歷史開鏈數據 CREATE TABLE IF NOT EXISTS xinniu.zip_table_bk stored AS orc tblproperties (“orc.compress” = “SNAPPY”) AS SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table WHERE 1 = 0 ;
– 將拉鏈表歷史開鏈數據插入到bk備份表中 – 卡拉鏈條件：startTime < to_date(from_unixtime(unix_timestamp(‘
h
i
v
e
c
o
n
f
:
b
a
t
c
h
d
a
t
e
′
,
′
y
y
y
y
M
M
d
d
′
)
)
)
A
N
D
e
n
d
T
i
m
e
>
=
t
o
d
a
t
e
(
f
r
o
m
u
n
i
x
t
i
m
e
(
u
n
i
x
t
i
m
e
s
t
a
m
p
(
′
{hiveconf:batch_date}' ,'yyyyMMdd'))) AND endTime >= to_date(from_unixtime(unix_timestamp('
hiveconf:batchd?ate′,′yyyyMMdd′)))ANDendTime>=tod?ate(fromu?nixtime(unixt?imestamp(′{hiveconf:batch_date}’ ,‘yyyyMMdd’))) – 備份表非空判斷：(select count(1) from xinniu.zip_table_bk limit 1) = 0 判斷備份表非空才插入 此處必須判空 不能使用drop或者truncate的方式清空備份表 會導致失敗重跑時丟數 INSERT INTO xinniu.zip_table_bk SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table join (select count(1) cnt from xinniu.zip_table_bk limit 1) b WHERE startTime < to_date(from_unixtime(unix_timestamp(‘
h
i
v
e
c
o
n
f
:
b
a
t
c
h
d
a
t
e
′
,
′
y
y
y
y
M
M
d
d
′
)
)
)
A
N
D
e
n
d
T
i
m
e
>
=
t
o
d
a
t
e
(
f
r
o
m
u
n
i
x
t
i
m
e
(
u
n
i
x
t
i
m
e
s
t
a
m
p
(
′
{hiveconf:batch_date}' ,'yyyyMMdd'))) AND endTime >= to_date(from_unixtime(unix_timestamp('
hiveconf:batchd?ate′,′yyyyMMdd′)))ANDendTime>=tod?ate(fromu?nixtime(unixt?imestamp(′{hiveconf:batch_date}’ ,‘yyyyMMdd’))) AND b.cnt = 0 ;
– 創(chuàng)建拉鏈表閉鏈數據備份表bf CREATE TABLE IF NOT EXISTS xinniu.zip_table_bf stored AS orc tblproperties (“orc.compress” = “SNAPPY”) AS SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table WHERE 1 = 0 ; – 備份拉鏈表中歷史閉鏈數據 卡拉鏈條件：endTime < to_date(to_timestamp(‘${hiveconf:batch_date}’ , ‘yyyyMMdd’))
– 備份表非空判斷：(select count(1) from xinniu.zip_table_bf limit 1) = 0 同上，不能使用drop或者truncate的方式清空備份表 INSERT INTO xinniu.zip_table_bf SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table join ( SELECT count(1) cnt FROM xinniu.zip_table_bf LIMIT 1) b WHERE endTime < to_date(from_unixtime(unix_timestamp(‘${hiveconf:batch_date}’ ,‘yyyyMMdd’))) AND b.cnt = 0 ;
– 中間加工表清空 DROP TABLE IF EXISTS xinniu.zip_table_nw; DROP TABLE IF EXISTS xinniu.zip_table_od;
– 創(chuàng)建中間表 新增變化修改中間表 CREATE TABLE IF NOT EXISTS xinniu.zip_table_nw stored AS orc tblproperties (“orc.compress” = “SNAPPY”) AS SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table WHERE 0 = 1;
– 創(chuàng)建中間表 未變化中間表 CREATE TABLE IF NOT EXISTS xinniu.zip_table_od stored AS orc tblproperties (“orc.compress” = “SNAPPY”) AS SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime, etlTimestamp , startTime , endTime , mark FROM xinniu.zip_table WHERE 0 = 1;
– 新增、修改、刪除變化數據插入變化中間表nw – 原始數據表與拉鏈表進行full join關聯(lián)，通過主鍵is_pk關聯(lián) 根據不同情況生成對應的startTime與endTime及mark三個技術字段 – 字段值選擇原始表與目標表的非空字段值 nvl(n.@{source_column_names}, o.@{xinniu.zip_table_column_names}) – 本邏輯中mark分為I、D兩種，新增與修改為I，刪除為D INSERT INTO TABLE xinniu.zip_table_nw SELECT nvl(n.userID,o.userID) , nvl(n.loginName,o.loginName) , nvl(n.regionDate,o.regionDate) , nvl(n.phoneNum,o.phoneNum) , nvl(n.birthday,o.birthday) , nvl(n.status,o.status) , nvl(n.lastLoginDate,o.lastLoginDate) , nvl(n.dataTime,o.dataTime) , current_date AS etlTimestamp , CASE WHEN n.dataTime IS NULL THEN o.startTime ELSE to_date(from_unixtime(unix_timestamp(‘
h
i
v
e
c
o
n
f
:
b
a
t
c
h
d
a
t
e
′
,
′
y
y
y
y
M
M
d
d
′
)
)
)
E
N
D
A
S
s
t
a
r
t
T
i
m
e
,
C
A
S
E
W
H
E
N
n
.
d
a
t
a
T
i
m
e
I
S
N
U
L
L
T
H
E
N
t
o
d
a
t
e
(
f
r
o
m
u
n
i
x
t
i
m
e
(
u
n
i
x
t
i
m
e
s
t
a
m
p
(
′
{hiveconf:batch_date}' ,'yyyyMMdd'))) END AS startTime , CASE WHEN n.dataTime IS NULL THEN to_date(from_unixtime(unix_timestamp('
hiveconf:batchd?ate′,′yyyyMMdd′)))ENDASstartTime,CASEWHENn.dataTimeISNULLTHENtod?ate(fromu?nixtime(unixt?imestamp(′{hiveconf:batch_date}’ ,‘yyyyMMdd’))) ELSE to_date(from_unixtime(unix_timestamp(‘29991231’ ,‘yyyyMMdd’))) END AS endTime , CASE WHEN ( n.userID is null ) THEN ‘D’ ELSE ‘I’ END AS mark FROM ( SELECT userID, loginName, regionDate, phoneNum, birthday, status, lastLoginDate, dataTime FROM xinniu.source_table WHERE dataTime = ‘${hiveconf:batch_date}’ ) n FULL JOIN xinniu.zip_table_bk o ON o.userID = n.userID WHERE ( o.userID IS NULL ) OR ( n.userID IS NULL ) OR ( nvl( CAST(o.phoneNum AS string) , ‘’ ) <> nvl( CAST(n.phoneNum AS string) , ‘’ ) OR nvl( CAST(o.status AS string) , ‘’ ) <> nvl( CAST(n.status AS string) , ‘’ ) ) ;
– 閉鏈發(fā)生變化的數據 endTime改為hiveconf:batch_date – 未變化數據保持原來狀態(tài) 新增與修改狀態(tài)統(tǒng)一"I" – 發(fā)生變化的endTime邏輯：when n.startTime is not null then to_date(to_timestamp(‘
h
i
v
e
c
o
n
f
:
b
a
t
c
h
d
a
t
e
′
,
′
y
y
y
y
M
M
d
d
′
)
)
?
?
沒發(fā)生變化的
e
n
d
T
i
m
e
邏輯：
w
h
e
n
o
.
e
n
d
T
i
m
e
>
=
t
o
d
a
t
e
(
t
o
t
i
m
e
s
t
a
m
p
(
′
{hiveconf:batch_date}' , 'yyyyMMdd')) -- 沒發(fā)生變化的endTime邏輯：when o.endTime >= to_date(to_timestamp('
hiveconf:batchd?ate′,′yyyyMMdd′))??沒發(fā)生變化的endTime邏輯：wheno.endTime>=tod?ate(tot?imestamp(′{hiveconf:batch_date}’ , ‘yyyyMMdd’)) then to_date(to_timestamp(‘29991231’,‘yyyyMMdd’)) INSERT INTO TABLE xinniu.zip_table_od SELECT o.userID, o.loginName, o.regionDate, o.phoneNum, o.birthday, o.status, o.lastLoginDate, o.dataTime, o.etlTimestamp , o.startTime , CASE WHEN n.startTime IS NOT NULL THEN to_date(from_unixtime(unix_timestamp(‘
h
i
v
e
c
o
n
f
:
b
a
t
c
h
d
a
t
e
′
,
′
y
y
y
y
M
M
d
d
′
)
)
)
W
H
E
N
o
.
e
n
d
T
i
m
e
>
=
t
o
d
a
t
e
(
f
r
o
m
u
n
i
x
t
i
m
e
(
u
n
i
x
t
i
m
e
s
t
a
m
p
(
′
{hiveconf:batch_date}' ,'yyyyMMdd'))) WHEN o.endTime >= to_date(from_unixtime(unix_timestamp('
hiveconf:batchd?ate′,′yyyyMMdd′)))WHENo.endTime>=tod?ate(fromu?nixtime(unixt?imestamp(′{hiveconf:batch_date}’ ,‘yyyyMMdd’))) THEN to_date(from_unixtime(unix_timestamp(‘29991231’ ,‘yyyyMMdd’))) ELSE o.endTime END AS endTime , ‘I’ AS mark FROM xinniu.zip_table_bk o LEFT JOIN xinniu.zip_table_nw n ON o.userID = n.userID WHERE nvl(n.endTime,to_date(from_unixtime(unix_timestamp(‘29991231’ ,‘yyyyMMdd’)))) <> to_date(from_unixtime(unix_timestamp(‘${hiveconf:batch_date}’ ,‘yyyyMMdd’))) ;
– 清空拉鏈表 TRUNCATE TABLE xinniu.zip_table;
– 插入數據到拉鏈表 INSERT INTO TABLE xinniu.zip_table SELECT * FROM xinniu.zip_table_nw UNION ALL SELECT * FROM xinniu.zip_table_od UNION ALL SELECT * FROM xinniu.zip_table_bf ;
– 清空臨時表 DROP TABLE xinniu.zip_table_bk;
DROP TABLE xinniu.zip_table_bf;
DROP TABLE xinniu.zip_table_nw;
DROP TABLE xinniu.zip_table_od;
1.2.5 執(zhí)行跑批任務
hive -hiveconf batch_date=20210101 -f /tmp/xinniu/sqlfile && hive -hiveconf batch_date=20210102 -f /tmp/xinniu/sqlfile && hive -hiveconf batch_date=20210103 -f /tmp/xinniu/sqlfile
1.3 效果驗證 1.3.1 全表驗證
select * from xinniu.zip_table;
1.3.2 卡拉鏈，查詢歷史某一節(jié)點數據
select * from xinniu.zip_table where startTime<=‘2021-01-01’ and endTime>‘2021-01-01’;
select * from xinniu.zip_table where startTime<=‘2021-01-02’ and endTime>‘2021-01-02’;
柚子快報激活碼778899分享：數倉基礎知識
好文鏈接
本文內容根據網絡資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點和立場。
轉載請注明，如有侵權，聯(lián)系刪除。
Netsea網購海洋
