ODS數(shù)據(jù)加載算法

ODS.jpg

更多信息卿城,請關注個人博客不圓的珠子
儲),是企業(yè)數(shù)據(jù)的抽取與交換平臺。通常ODS的數(shù)據(jù)不做任何轉化示弓,只保留源系統(tǒng)數(shù)據(jù)损拢。并將數(shù)據(jù)分發(fā)給數(shù)據(jù)倉庫,數(shù)據(jù)集市等下游系統(tǒng)被碗,并在ODS數(shù)據(jù)基礎上開發(fā)各類報表某宪。目前接觸到的項目中锐朴,將數(shù)據(jù)倉庫與ODS集成到一個項目中兴喂,共用同一個數(shù)據(jù)庫衣迷。減少了數(shù)據(jù)傳輸,提高了資源的利用率酱酬。在ODS層數(shù)據(jù)中,我們將源系統(tǒng)數(shù)據(jù)變化的過程保留下來岳悟,由于源系統(tǒng)和ODS不在同一服務器上,源系統(tǒng)通常通過文件交換的方式傳輸數(shù)據(jù)贵少,為了減少文件傳輸,數(shù)據(jù)文件可分為增量文件和全量文件滔灶。根據(jù)保留數(shù)據(jù)類型的不同普碎,又分為流水數(shù)據(jù)與歷史數(shù)據(jù)录平。通常我們在開發(fā)ODS時麻车,涉及到以下幾種算法缀皱,全量歷史加載算法,增量歷史加載算法动猬,流水全量加載算法啤斗,流水增量加載算法,全量覆蓋加載算法赁咙。

2.相關變量說明

變量名 變量描述
${DB} 數(shù)據(jù)庫實例名
${TBNAME} 表名
$COLS 表字段钮莲,以逗號分隔,col1,col2..
$DATA_DT 數(shù)據(jù)加載日期
${SRC_DB} 源數(shù)據(jù)庫實例名
${SRC_TBNAME} 源表名
ND 臨時表彼水,用于加載當日數(shù)據(jù)
OD 臨時表崔拥,用于加載上日數(shù)據(jù)
${HT_S_DT} 拉鏈開始日期
${HT_E_DT} 拉鏈結束日期
${TX_DATE} 交易日期,作業(yè)運行日期
WT_U 臨時表凤覆,用于保存需要關鏈數(shù)據(jù)
WT_I 臨時表链瓦,用于保存需要插入的數(shù)據(jù)

3.全量覆蓋加載算法

全量覆蓋加載是所有加載中最簡單的一種數(shù)據(jù)加載方式。它是指直接將目標表中數(shù)據(jù)刪除并將源系統(tǒng)提供的數(shù)據(jù)文件直接加載到目標表中的過程盯桦。偽代碼如下:

//清除表數(shù)據(jù)
TRUNCATE TABLE  ${DB}.${TBNAME} ;
//將源表數(shù)據(jù)插入到目標表中
INSERT INTO ${DB}.${TBNAME}
(    $COLS
    ,DATA_DT)
SELECT
     $COLS
    ,TO_DATE('$DATA_DT', 'YYYYMMDD')
FROM ${SRC_DB}.${SRC_TBNAME};

4.全量歷史加載算法

  • 將當日數(shù)據(jù)加載到ND表中
INSERT INTO ${ND}( $COLS )
SELECT $COLS
FROM ${SRC_DB}.${SRC_TBNAME}
;
  • 將上日正常數(shù)據(jù)加載到臨時表OD中
INSERT INTO OD
SELECT * FROM ${DB}.${TBNAME}
WHERE ${HT_E_DT} > ${TX_DATE}   
AND   ${HT_S_DT} <= ${TX_DATE}
;
  • 產生插入結果數(shù)據(jù)(新增數(shù)據(jù)慈俯,當日數(shù)據(jù)不在昨日數(shù)據(jù)中),并將數(shù)據(jù)加載到臨時表WT_I中
INSERT INTO WT_I(
    $COLS
   ,${HT_S_DT}
   ,${HT_E_DT}
   ,DATA_DT )
SELECT
    $COLS
   ,$TX_DATE
   ,$MAX_DT
   ,$TX_DATE
FROM ${ND}
WHERE ($COLS_NOT_NULL)
  NOT IN (SELECT $COLS_NOT_NULL
          FROM OD)
;
  • 產生關鏈數(shù)據(jù)(昨日數(shù)據(jù)不在當日數(shù)據(jù)中,已經被源系統(tǒng)刪除的數(shù)據(jù))拥峦,并將數(shù)據(jù)加在到臨時表WT_U中
INSERT INTO WT_U(
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT )
SELECT
     $COLS
    ,${HT_S_DT}
    ,${TX_DATE}
    ,DATA_DT
FROM OD
WHERE ( $COLS_NOT_NULL )
  NOT IN (SELECT $COLS_NOT_NULL
          FROM ${ND})
;
  • 將目標表中已經失效的數(shù)據(jù)關鏈
UPDATE  ${DB}.${TBNAME}
SET     ${HT_E_DT} = ${TX_DATE}
WHERE   ($COLS_NOT_NULL) IN (SELECT $COLS_NOT_NULL FROM WT_U)
AND   ${HT_E_DT} > ${TX_DATE}   
AND   ${HT_S_DT} <= ${TX_DATE}
;
  • 將新增數(shù)據(jù)插入插入到目標表中
INSERT INTO ${DB}.${TBNAME}(
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT)
SELECT
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT
FROM WT_I;

5.增量歷史加載算法

1.將當日數(shù)據(jù)加載到臨時表ND表中

INSERT INTO ${ND}( $COLS )
SELECT $COLS
FROM ${DB}.${SRC_TBNAME}
;
  • 將上日正常數(shù)據(jù)加載到臨時表OD中(根據(jù)表主鍵查找當日數(shù)據(jù))
INSERT INTO OD
SELECT * FROM ${DB}.${TBNAME}
WHERE ($PRI_KEY_NAME) IN (SELECT $PRI_KEY_NAME FROM ${ND})
AND ${HT_E_DT} > ${TX_DATE}   
AND ${HT_S_DT} <= ${TX_DATE}
  • 產生插入結果數(shù)據(jù)(當日數(shù)據(jù)不在昨日數(shù)據(jù)中)肥卡,并將數(shù)據(jù)加載到臨時表WT_I中
INSERT INTO WT_I(
    $COLS
   ,${HT_S_DT}
   ,${HT_E_DT}
   ,DATA_DT )
SELECT
    $COLS
   ,$TX_DATE
   ,$MAX_DT
   ,$TX_DATE
FROM ${ND}
WHERE ($COLS_NOT_NULL)
  NOT IN (SELECT $COLS_NOT_NULL
          FROM OD)
;
  • 產生關鏈數(shù)據(jù)(昨日數(shù)據(jù)不在當日數(shù)據(jù)中,源系統(tǒng)已經失效數(shù)據(jù)),并將數(shù)據(jù)加在到臨時表WT_U中
INSERT INTO WT_U(
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT )
SELECT
     $COLS
    ,${HT_S_DT}
    ,${TX_DATE}
    ,DATA_DT
FROM OD
WHERE ( $COLS_NOT_NULL )
  NOT IN (SELECT $COLS_NOT_NULL
          FROM ${ND})
;
  • 將目標表中失效的數(shù)據(jù)關鏈
UPDATE  ${DB}.${TBNAME}
SET     ${HT_E_DT} =${TX_DATE}
WHERE   ($COLS_NOT_NULL) IN (SELECT $COLS_NOT_NULL FROM WT_U)
AND ($PRI_KEY_NAME) IN (SELECT $PRI_KEY_NAME FROM ${ND})
AND ${HT_E_DT} > ${TX_DATE}   
AND ${HT_S_DT} <= ${TX_DATE}
;
  • 插入當日新增數(shù)據(jù)
INSERT INTO ${DB}.${TBNAME}(
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT)
SELECT
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT
FROM WT_I;

6.流水增量加載算法

  • 清除當日數(shù)據(jù)
DELETE FROM ${DB}.${TBNAME}
WHERE DATA_DT = '$DATA_DT'
;
  • 插入數(shù)據(jù)
INSERT INTO ${DB}.${TBNAME}
(    $COLS
    ,DATA_DT)
SELECT
     $COLS
    ,'$DATA_DT'
FROM ${DB}.${SRC_TBNAME}
MINUS ALL
SELECT
     $COLS
    ,'$DATA_DT'
FROM ${DB}.${TBNAME}
WHERE DATA_DT <= ${TX_DATE}
;

7.流水全量加載算法

  • 清除當日數(shù)據(jù)
DELETE FROM ${DB}.${TBNAME}
WHERE DATA_DT = $DATA_DT
  • 插入數(shù)據(jù)
INSERT INTO ${DB}.${TBNAME}
(    $COLS
    ,DATA_DT
    ,HT_S_DT
    ,HT_E_DT
    )
SELECT
     $COLS
    ,'$DATA_DT'
    ,'$DATA_DT'
    ,'$MAX_DT'
FROM ${DB}.${SRC_TBNAME};
最后編輯于
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 序言:七十年代末事镣,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子揪胃,更是在濱河造成了極大的恐慌璃哟,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,729評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件喊递,死亡現(xiàn)場離奇詭異随闪,居然都是意外死亡,警方通過查閱死者的電腦和手機骚勘,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,226評論 3 399
  • 文/潘曉璐 我一進店門铐伴,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人俏讹,你說我怎么就攤上這事当宴。” “怎么了泽疆?”我有些...
    開封第一講書人閱讀 169,461評論 0 362
  • 文/不壞的土叔 我叫張陵户矢,是天一觀的道長。 經常有香客問我殉疼,道長梯浪,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,135評論 1 300
  • 正文 為了忘掉前任礼预,我火速辦了婚禮虏劲,結果婚禮上,老公的妹妹穿的比我還像新娘获高。我一直安慰自己吻育,他們只是感情好,可當我...
    茶點故事閱讀 69,130評論 6 398
  • 文/花漫 我一把揭開白布摊趾。 她就那樣靜靜地躺著游两,像睡著了一般。 火紅的嫁衣襯著肌膚如雪肛炮。 梳的紋絲不亂的頭發(fā)上宝踪,一...
    開封第一講書人閱讀 52,736評論 1 312
  • 那天,我揣著相機與錄音秕重,去河邊找鬼厉膀。 笑死,一個胖子當著我的面吹牛凳兵,可吹牛的內容都是我干的菱魔。 我是一名探鬼主播,決...
    沈念sama閱讀 41,179評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼聚蝶,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了碘勉?” 一聲冷哼從身側響起,我...
    開封第一講書人閱讀 40,124評論 0 277
  • 序言:老撾萬榮一對情侶失蹤倍宾,失蹤者是張志新(化名)和其女友劉穎高职,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體怔锌,經...
    沈念sama閱讀 46,657評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡埃元,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 38,723評論 3 342
  • 正文 我和宋清朗相戀三年岛杀,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片类嗤。...
    茶點故事閱讀 40,872評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡土浸,死狀恐怖,靈堂內的尸體忽然破棺而出,到底是詐尸還是另有隱情泪酱,我是刑警寧澤,帶...
    沈念sama閱讀 36,533評論 5 351
  • 正文 年R本政府宣布毡惜,位于F島的核電站经伙,受9級特大地震影響,放射性物質發(fā)生泄漏帕膜。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 42,213評論 3 336
  • 文/蒙蒙 一达吞、第九天 我趴在偏房一處隱蔽的房頂上張望荒典。 院中可真熱鬧,春花似錦覆糟、人聲如沸遮咖。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,700評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽魄藕。三九已至,卻和暖如春背率,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背寝姿。 一陣腳步聲響...
    開封第一講書人閱讀 33,819評論 1 274
  • 我被黑心中介騙來泰國打工饵筑, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人根资。 一個月前我還...
    沈念sama閱讀 49,304評論 3 379
  • 正文 我出身青樓玄帕,卻偏偏與公主長得像,于是被迫代替她去往敵國和親裤纹。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 45,876評論 2 361

推薦閱讀更多精彩內容