大數(shù)據(jù)開發(fā)中數(shù)倉拉鏈表怎么迭代或回滾

這篇文章主要介紹“大數(shù)據(jù)開發(fā)中數(shù)倉拉鏈表怎么迭代或回滾”，在日常操作中，相信很多人在大數(shù)據(jù)開發(fā)中數(shù)倉拉鏈表怎么迭代或回滾問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”大數(shù)據(jù)開發(fā)中數(shù)倉拉鏈表怎么迭代或回滾”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

1.背景

拉鏈表是什么，在數(shù)倉建立時(shí)候，一種重要的表數(shù)據(jù)處理方式，可以將數(shù)據(jù)結(jié)構(gòu)于算法，類比于拉鏈表于數(shù)倉，旨在解決數(shù)倉建立里面的SCD需求，那么什么是SCD，就是緩慢變化維，隨著時(shí)間流逝，數(shù)據(jù)相對事實(shí)表發(fā)生緩慢變化。

SCD的常用處理方式有以下幾種：

• 保留原值

• 直接覆蓋

• 增加新屬性列

• 快照表

• 拉鏈表

本文主要講解拉鏈表來處理SCD的問題，其特點(diǎn)歸納以下，有以下幾種場景時(shí)候，可以使用拉鏈表。

1.表數(shù)據(jù)量較大，用全量表會(huì)占用很多存儲(chǔ)

2.表數(shù)據(jù)會(huì)有修改，用增量表，難以處理重復(fù)且修改數(shù)據(jù)

3.有回溯的需求，需要知道歷史某個(gè)時(shí)間點(diǎn)的全量數(shù)據(jù)

4.數(shù)據(jù)有修改，但是頻率和量不是很大比如只有百萬分之一有修改

2.拉鏈表處理理論

首先拉鏈表是一個(gè)全量表且不是分區(qū)表，為了達(dá)到前面描述的各種效果，必然需要一個(gè)中間表來做中間跳板，這個(gè)中間跳板表是一個(gè)分區(qū)表，數(shù)據(jù)是增量數(shù)據(jù)，增量內(nèi)容包括修改和增加，即常常是create_time or update_time 落在當(dāng)前天，對于拉鏈表需要增加兩個(gè)與原始數(shù)據(jù)沒有關(guān)系的兩個(gè)字段來標(biāo)識(shí)數(shù)據(jù)開始時(shí)間和有效截至?xí)r間，在示例中，這兩個(gè)日期分別為start_date 和 end_date ，拉鏈表其處理方式主要有以下三種：初始化，每天更新數(shù)據(jù)，回滾數(shù)據(jù)。

2.1 初始化和新增數(shù)據(jù)

其每天的滾動(dòng)方式如下圖：

初始化部分，是拉鏈全量表的開始時(shí)間，也奠定了回滾時(shí)候能夠回滾的最早時(shí)間，每天更新邏輯如上圖，新增數(shù)據(jù)會(huì)分為兩部分，一部分是每天新增的數(shù)據(jù)，對于當(dāng)天分區(qū)里面有相同變化或者未變化的數(shù)據(jù)時(shí)候，分別修改對應(yīng)的start_date 和 end_date 即可達(dá)到更新數(shù)據(jù)。

2.1 數(shù)據(jù)回滾

對于上面的更新邏輯，我們來考慮如何回滾數(shù)據(jù)，即回到歷史的某個(gè)時(shí)間點(diǎn)，對于拉鏈表來說是全量表，所以只有一個(gè)回滾即可。回滾策略可以根據(jù)回滾時(shí)間點(diǎn)和數(shù)據(jù)生成的start_date 和 end_date，具體怎么回滾，我們來看下面的示意圖：

在end_date < rollback_date 的數(shù)據(jù)要保留，對于處理end_date ≥ rollback_date ≥ start_date 設(shè)置end_date 為9999-12-31 ，對于回滾的結(jié)果，一般為了保持?jǐn)?shù)據(jù)的完整性，可以將回滾的數(shù)據(jù)放在一個(gè)新的拉鏈臨時(shí)表中。

3.拉鏈表處理案例

對于數(shù)倉的常用分層DIM即維度層是拉鏈表的常用場景，下面有個(gè)例子拉看看拉鏈表怎么做新增和回滾。

用拉鏈表實(shí)現(xiàn)核心交易分析中DIM層商家維表，并實(shí)現(xiàn)該拉鏈表的回滾。

3.1 創(chuàng)建表并導(dǎo)入數(shù)據(jù)

其中商家維表結(jié)構(gòu)如下：

--創(chuàng)建商家信息表(增量表 分區(qū)表)
drop table if exists ods.ods_trade_shops;
create table ods.ods_trade_shops(
  `shopid` int COMMENT '商鋪ID',
  `userid` int COMMENT '商鋪負(fù)責(zé)人', 
  `areaid` int COMMENT '區(qū)域ID',
  `shopname` string COMMENT '商鋪名稱',
  `shoplevel` int COMMENT '商鋪等級(jí)',
  `status` int COMMENT '商鋪狀態(tài)',
  `createtime` string COMMENT '創(chuàng)建日期',
  `modifytime` string COMMENT  '修改日期'
) COMMENT '商家信息表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

-- 創(chuàng)建商家信息維表
drop table if exists dim.dim_trade_shops;
create table dim.dim_trade_shops(
  `shopid` int COMMENT '商鋪ID',
  `userid` int COMMENT '商鋪負(fù)責(zé)人', 
  `areaid` int COMMENT '區(qū)域ID',
  `shopname` string COMMENT '商鋪名稱',
  `shoplevel` int COMMENT '商鋪等級(jí)',
  `status` int COMMENT '商鋪狀態(tài)',
  `createtime` string COMMENT '創(chuàng)建日期',
  `modifytime` string COMMENT  '修改日期',
  `startdate` string  COMMENT '生效起始日期',
  `enddate` string  COMMENT '失效結(jié)束日期'
) COMMENT '商家信息表';

導(dǎo)入以下測試數(shù)據(jù)：

/root/data/shop-2020-11-20.dat
100050,1,100225,WSxxx營超市,1,1,2020-06-28,2020-11-20 13:22:22
100052,2,100236,新鮮xxx旗艦店,1,1,2020-06-28,2020-11-20 13:22:22
100053,3,100011,華為xxx旗艦店,1,1,2020-06-28,2020-11-20 13:22:22
100054,4,100159,小米xxx旗艦店,1,1,2020-06-28,2020-11-20 13:22:22
100055,5,100211,蘋果xxx旗艦店,1,1,2020-06-28,2020-11-20 13:22:22
 
 
/root/data/shop-2020-11-21.dat
100057,7,100311,三只xxx鼠零食,1,1,2020-06-28,2020-11-21 13:22:22
100058,8,100329,良子xxx鋪美食,1,1,2020-06-28,2020-11-21 13:22:22
100054,4,100159,小米xxx旗艦店,2,1,2020-06-28,2020-11-21 13:22:22
100055,5,100211,蘋果xxx旗艦店,2,1,2020-06-28,2020-11-21 13:22:22
 
 
/root/data/shop-2020-11-22.dat
100059,9,100225,樂居xxx日用品,1,1,2020-06-28,2020-11-22 13:22:22
100060,10,100211,同仁xxx大健康,1,1,2020-06-28,2020-11-22 13:22:22
100052,2,100236,新鮮xxx旗艦店,1,2,2020-06-28,2020-11-22 13:22:22

load data local inpath '/root/data/shop-2020-11-20.dat' overwrite into table ods.ods_trade_shops partition(dt='2020-11-20');
load data local inpath '/root/data/shop-2020-11-21.dat' overwrite  into table ods.ods_trade_shops partition(dt='2020-11-21');
load data local inpath '/root/data/shop-2020-11-22.dat' overwrite  into table ods.ods_trade_shops partition(dt='2020-11-22');

3.2 拉鏈表初始化

假設(shè)將第一天數(shù)據(jù)作為歷史的所有數(shù)據(jù)

INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       CASE
           WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10)
           ELSE substr(createtime, 0, 10)
       END AS startdate,
       '9999-12-31' AS enddate
FROM ods.ods_trade_shops
WHERE dt ='2020-11-20';

3.3 更新拉鏈表

對于增量表，一般的邏輯是，create_time或者modifytime的截取作為當(dāng)天分區(qū)dt，modifytime 大于等于create_time ,這里取前兩個(gè)

INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       CASE
           WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10)
           ELSE substr(createtime, 0, 10)
       END AS startdate,
       '9999-12-31' AS enddate
FROM ods.ods_trade_shops
WHERE dt = '2020-11-21'
UNION ALL
SELECT b.shopid,
       b.userid,
       b.areaid,
       b.shopname,
       b.shoplevel,
       b.status,
       b.createtime,
       b.modifytime,
       b.startdate,
       CASE
           WHEN a.shopid IS NOT NULL
                AND b.enddate ='9999-12-31' THEN date_add('2020-11-21', -1)
           ELSE b.enddate
       END AS enddate
FROM
  (SELECT *
   FROM ods.ods_trade_shops
   WHERE dt='2020-11-21') a
RIGHT JOIN dim.dim_trade_shops b ON a.shopid = b.shopid;

加載拉鏈表的腳本如下：

dim_load_shops.sh

#!/bin/bash
 
source /etc/profile
if [ -n "$1" ]
then
  do_date=$1
else
  do_date=`date -d "-1 day" +%F`
fi
 
sql="
INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       CASE
           WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10)
           ELSE substr(createtime, 0, 10)
       END AS startdate,
       '9999-12-31' AS enddate
FROM ods.ods_trade_shops
WHERE dt = '$do_date'
UNION ALL
SELECT b.shopid,
       b.userid,
       b.areaid,
       b.shopname,
       b.shoplevel,
       b.status,
       b.createtime,
       b.modifytime,
       b.startdate,
       CASE
           WHEN a.shopid IS NOT NULL
                AND b.enddate ='9999-12-31' THEN date_add('$do_date', -1)
           ELSE b.enddate
       END AS enddate
FROM
  (SELECT *
   FROM ods.ods_trade_shops
   WHERE dt='$do_date') a
RIGHT JOIN dim.dim_trade_shops b ON a.shopid = b.shopid;
"
 
hive -e "$sql"

可以執(zhí)行此腳本來加載2020-12-22 的數(shù)據(jù)，sh dim_load_shops.sh 2020-12-22

3.4 回滾拉鏈表到某一時(shí)間點(diǎn)

先創(chuàng)建一個(gè)臨時(shí)表，tmp.shops_tmp 用來放回滾的數(shù)據(jù)

DROP TABLE IF EXISTS tmp.shops_tmp;
CREATE TABLE IF NOT EXISTS tmp.tmp_shops AS
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       startdate,
       enddate
FROM dim.dim_trade_shops
WHERE enddate < '2020-11-21'
UNION ALL
SELECT shopid,
       userid,
       areaid,
       shopname,
       shoplevel,
       status,
       createtime,
       modifytime,
       startdate,
       '9999-12-31' AS enddate
FROM dim.dim_trade_shops
WHERE startdate <= '2020-11-21'
  AND enddate >= '2020-11-21';


INSERT OVERWRITE TABLE dim.dim_trade_shops
SELECT *
FROM tmp.tmp_shops;

回滾腳本和更新腳本類似，只要更新其中的sql即可，這里不再重復(fù)。

到此，關(guān)于“大數(shù)據(jù)開發(fā)中數(shù)倉拉鏈表怎么迭代或回滾”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！