數(shù)據(jù)倉庫前置知識

數(shù)據(jù)倉庫分層

使用數(shù)據(jù)分層目的，減少重復開發(fā)，隔離原始數(shù)據(jù)，按照業(yè)務需求設計層次。較為常見的為早期的四層架構(gòu)（貼源層ods、明細層dwd、匯總層dws、集市層ads），如果是復雜數(shù)倉使用傳統(tǒng)的四層架構(gòu)不能滿足需求，多采用五層架構(gòu)（技術緩沖層ITL、貼源模型層IOL、主題模型層IML、共性加工層ICL、應用集市層IDL），針對每層功能在下面章節(jié)詳細介紹。

數(shù)據(jù)存儲策略

存儲策略分類

數(shù)據(jù)存儲方式分為增量與全量兩大類。

增量存儲，即每天新增的，這類數(shù)據(jù)存儲方式為每天只抽取增量部分，抽取新增的數(shù)據(jù)進行存儲，一般增量存儲都采用分片的方式進行存儲，即使用分區(qū)的方式進行存儲，每天一個分區(qū)。

全量存儲，即每天存儲最新一天全量數(shù)據(jù)，每天在源系統(tǒng)全量提取最新數(shù)據(jù)，進行存儲，全量存儲又分為保存歷史與不保存歷史，保存歷史的通常叫做全量快照表，每天全量保存到一個分區(qū)中，另外一種不保存歷史的方式則為在數(shù)據(jù)倉庫中只保存一份最新的表，沒有分區(qū)，不能查詢歷史某個時間節(jié)點的數(shù)據(jù)。

file

如上，每天只從源系統(tǒng)抽取跑批日期當天的數(shù)據(jù)，插入到數(shù)倉中跑批日期當天的分區(qū)中。

file

如上，每天從源系統(tǒng)全量抽取，沒有where條件，然后插入到數(shù)倉的快照表的指定跑批日期的分區(qū)中，每天保存一份。

file

如上，每天從源系統(tǒng)全量抽取數(shù)據(jù)，覆蓋到數(shù)倉中的全量表里，但是在覆蓋前一定要做數(shù)據(jù)備份，避免數(shù)據(jù)丟失，可以在數(shù)據(jù)插入完成之后，將備份表再刪除。

拉鏈表

拉鏈表概述

拉鏈表核心思想，像個拉鏈，有開鏈與閉鏈，我們通常將最新的數(shù)據(jù)稱為開鏈數(shù)據(jù)，歷史數(shù)據(jù)稱為閉鏈數(shù)據(jù)，拉鏈表支持歷史數(shù)據(jù)查詢，且空間占用較小，但是數(shù)據(jù)加工處理較為繁瑣，屬于時間換空間的設計方式，拉鏈表一個時間維度中同一個用戶只保存一條用戶狀態(tài)。拉鏈表通常會增加三個技術字段“開始日期startdate、結(jié)束日期enddate、狀態(tài)標識mark”。

通過主鍵（PK）與歷史數(shù)據(jù)進行對比，判斷拉鏈表中最新的數(shù)據(jù)與歷史數(shù)據(jù)是否一致，如果發(fā)生變化或者新增則進行相應的開鏈、閉鏈操作。

開鏈：是一個開放的區(qū)間，一條數(shù)據(jù)從一個開始時間(startdate)開始一直持續(xù)到了現(xiàn)在仍然沒有變化，我們也不知道他在未來哪一天才會變化，所以我們通常將enddate設置成一個較大的時間29991231或者30000101。

閉鏈：是一個閉合的區(qū)間，指數(shù)據(jù)在一個時間區(qū)間內(nèi)的狀態(tài)，有開始時間與結(jié)束時間。

拉鏈表示例

file

file

拉鏈表查詢某個時間節(jié)點（2021年01月06日）最新狀態(tài)：

Select * from zip_table where startdate<=20210106 and enddate>20210106;

此時在拉鏈表中命中了一條數(shù)據(jù)，即:{xxxname:xxx,gfname:002,startdate:20210105,enddate:20210201}

如上查詢方式通常叫做"卡拉鏈"，startdate<=querydate and enddate>querydate

拉鏈表形態(tài)示例：

如上示例拉鏈表供記錄了三個id的歷史變化情況，支持任何時間段歷史數(shù)據(jù)的查詢。

拉鏈表適用場景

有歷史某個時間節(jié)點查詢需求、數(shù)據(jù)量較大、變化比較緩慢的數(shù)據(jù)。

如用戶基本信息表，數(shù)據(jù)量非常龐大，手機號這類基本字段不經(jīng)常變化，但是有變化的可能性，為了滿足支持歷史某個時間節(jié)點的狀態(tài)查詢，有兩種實現(xiàn)方式，可以使用全量快照的方式進行存儲，但是由于數(shù)據(jù)量比較大，并且變化的數(shù)據(jù)較少，每天重復的保存了未變化的數(shù)據(jù)，造成磁盤空間的嚴重浪費，第二種方式則為拉鏈表存儲，在數(shù)倉中只保存一份數(shù)據(jù)，某個時間段內(nèi)只保存一份數(shù)據(jù)，不重復存儲，并且支持歷史查詢。

file

類似應用場景還有一些字典表、碼表、規(guī)則表等。

拉鏈表算法設計

設計思想，每天從上游抽取全量數(shù)據(jù)，與拉鏈表中最新的數(shù)據(jù)進行對比，針對不同情況處理方式如下：

*修改類：通過對比發(fā)現(xiàn)上游系統(tǒng)最新數(shù)據(jù)與拉鏈表中的數(shù)據(jù)不一致，在拉鏈表中閉鏈該條數(shù)據(jù)，同時開啟一條新的開鏈數(shù)據(jù)，狀態(tài)標識為"I"；

* 新增類：通過對比發(fā)現(xiàn)上游系統(tǒng)有，但是拉鏈表中沒有，在拉鏈表中新增一條新的開鏈數(shù)據(jù)，狀態(tài)標識為"I"；

* 刪除類：通過對比發(fā)現(xiàn)上游系統(tǒng)沒有，但是拉鏈表中有，在拉鏈表中閉鏈該條數(shù)據(jù)，狀態(tài)標識為"D"；

* 未變化：通過對比發(fā)現(xiàn)上游系統(tǒng)數(shù)據(jù)與拉鏈表中的數(shù)據(jù)一致，則保持拉鏈表中現(xiàn)有狀態(tài)不變；

file

file

拉鏈表SQL分析

數(shù)據(jù)準備

-- 拉鏈表建表語句
CREATE TABLE xinniu.zip_table(
   pk string,
   col1 string, 
   col2 string,
   starttime string, 
   endtime string, 
   mark string)
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
   STORED AS TEXTFILE
   ;

-- 拉鏈表初始化數(shù)據(jù)加載
load data local inpath '/tmp/xinniu/zipinitdata' into table zip_table;

-- 上游系統(tǒng)建表語句
CREATE TABLE xinniu.source_table(
   pk string,
   col1 string, 
   col2 string)
PARTITIONED BY (datatime string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE;

-- 上游系統(tǒng)數(shù)據(jù)加載
load data local inpath '/tmp/xinniu/sourcedata-20210107' into table xinniu.source_table partition (datatime='20210107');

• step1:拆分開鏈數(shù)據(jù)與閉鏈數(shù)據(jù)

1. 創(chuàng)建開鏈表zip_table_bk、閉鏈表zip_table_bf；
2. 查詢拉鏈表中開鏈數(shù)據(jù)與閉鏈數(shù)據(jù)分別插入開鏈表與閉鏈表中，在插入數(shù)據(jù)前需要判斷備份表是否為空，如果不為空則不進行插入，避免數(shù)據(jù)異常;

-- 創(chuàng)建開鏈表
CREATE TABLE IF NOT EXISTS xinniu.zip_table_bk stored AS orc tblproperties ("orc.compress" = "SNAPPY") AS
SELECT
pk,
col1,
col2,
startTime ,
endTime ,
mark
FROM
    xinniu.zip_table
WHERE
    1 = 0 ;

-- 抽取開鏈數(shù)據(jù)到開鏈表
 INSERT
    INTO
    xinniu.zip_table_bk
SELECT
    pk,
    col1,
    col2,
    startTime ,
    endTime ,
    mark
FROM
    xinniu.zip_table 
    join (select count(1) cnt from xinniu.zip_table_bk limit 1) b
WHERE
    startTime < '${hiveconf:batch_date}'
    AND endTime >= '${hiveconf:batch_date}'
    AND b.cnt = 0
    ;

-- 創(chuàng)建閉鏈表
CREATE TABLE IF NOT EXISTS xinniu.zip_table_bf stored AS orc tblproperties ("orc.compress" = "SNAPPY") AS
SELECT
    pk,
    col1,
    col2,
    startTime ,
    endTime ,
    mark
FROM
    xinniu.zip_table
WHERE
    1 = 0 ;
    
--抽取閉鏈數(shù)據(jù)到閉鏈表
INSERT
    INTO
    xinniu.zip_table_bf
SELECT
    pk,
    col1,
    col2,
    startTime ,
    endTime ,
    mark
FROM
    xinniu.zip_table 
    join 
    (
    SELECT
        count(1) cnt
    FROM
        xinniu.zip_table_bf
    LIMIT 1) b
WHERE
    endTime < '${hiveconf:batch_date}'
    AND b.cnt = 0 ;

• step2:開鏈表與上游系統(tǒng)新數(shù)據(jù)關聯(lián)，提取出發(fā)生變化(新增、修改、刪除)的數(shù)據(jù)插入到zip_table_nw變化表中。

-- 中間加工表清空
DROP TABLE IF EXISTS xinniu.zip_table_nw;

-- 創(chuàng)建中間表 新增變化修改中間表
CREATE TABLE IF NOT EXISTS xinniu.zip_table_nw stored AS orc tblproperties ("orc.compress" = "SNAPPY") AS
SELECT
    pk,
    col1,
    col2,
    startTime ,
    endTime ,
    mark
FROM
    xinniu.zip_table
WHERE
    0 = 1;

-- 與上游數(shù)據(jù)對比，將變化數(shù)據(jù)插入到變化表中
INSERT
    INTO
    TABLE xinniu.zip_table_nw
SELECT
    nvl(n.pk,o.pk),
    nvl(n.col1,o.col1) ,
    nvl(n.col2,o.col2) ,
    CASE
        WHEN n.pk IS NULL THEN o.startTime
        ELSE '${hiveconf:batch_date}'
    END AS startTime ,
    CASE
        WHEN n.pk IS NULL THEN '${hiveconf:batch_date}'
        ELSE '29991231'
    END AS endTime ,
    CASE
        WHEN ( n.pk is null ) THEN 'D'
        ELSE 'I'
    END AS mark
FROM
    (
    SELECT
      pk,  
      col1,
      col2
    FROM
        xinniu.source_table
    WHERE
        dataTime = '${hiveconf:batch_date}' ) n
FULL JOIN xinniu.zip_table_bk o ON o.pk = n.pk
WHERE
    (
        o.pk IS NULL )
    OR (
        n.pk IS NULL )
    OR (
        nvl( CAST(o.col1 AS string) , '' ) <> nvl( CAST(n.col1 AS string) , '' )
        OR nvl( CAST(o.col2 AS string) , '' ) <> nvl( CAST(n.col2 AS string) , '' )
       ) 
    ;

• step3:開鏈表與變化表關聯(lián)對比，生成未變化數(shù)據(jù)，已變化數(shù)據(jù)閉鏈,插入到zip_table_od表。

-- 清空中間表
DROP TABLE IF EXISTS xinniu.zip_table_od;

-- 創(chuàng)建中間表 未變化中間表
CREATE TABLE IF NOT EXISTS xinniu.zip_table_od stored AS orc tblproperties ("orc.compress" = "SNAPPY") AS
SELECT
    pk,    
    col1,    
    col2,
    startTime ,
    endTime ,
    mark
FROM
    xinniu.zip_table
WHERE
    0 = 1;

-- 對比開鏈表與變化表，閉環(huán)已變化數(shù)據(jù)，生成未變化數(shù)據(jù)，插入到zip_table_od表
INSERT
    INTO
    TABLE xinniu.zip_table_od
SELECT
    o.pk,
    o.col1,
    o.col2,
    o.startTime ,
    CASE
        WHEN n.startTime IS NOT NULL THEN '${hiveconf:batch_date}'
        WHEN o.endTime >= '${hiveconf:batch_date}' THEN '29991231' ELSE o.endTime
    END AS endTime ,
    'I' AS mark
FROM
    xinniu.zip_table_bk o
LEFT JOIN xinniu.zip_table_nw n ON o.pk = n.pk
WHERE
    nvl(n.endTime,'29991231') <> '${hiveconf:batch_date}'
    ;

step4:清空拉鏈表，合并變化表nw、閉鏈表bf、未變化與已變化閉鏈表od。

-- 清空拉鏈表
TRUNCATE TABLE xinniu.zip_table;

-- 插入數(shù)據(jù)到拉鏈表
 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;

可以將上面的所有步驟分裝到一個文件中，使用hive -f的方式調(diào)用sql文件，使用-hiveconf的方式傳入跑批日期，實現(xiàn)調(diào)度系統(tǒng)自動調(diào)用。

數(shù)據(jù)架構(gòu)設計

本次課程采用五層的架構(gòu)模式，即技術緩沖層ITL、貼源模型層IOL、主題模型層IML、共性加工層ICL、應用集市層IDL，如下圖

file

通俗理解數(shù)倉

file

技術緩沖層

技術緩沖層概述

技術緩沖層(ITL)，貼源抽取，只增加需要的技術字段（ETL date），其他字段保持與源系統(tǒng)一致，該層數(shù)據(jù)用于給貼源模型層供數(shù)，增加緩沖層的目的是為了實現(xiàn)貼源模型層數(shù)據(jù)處理緩沖，避免計算過程積壓在ETL過程。

file

如上圖，如果沒有增加緩沖層，貼源模型層如果做拉鏈表，那么就只能在ETL階段實現(xiàn)，這樣對ETL步驟壓力較大，因為ETL服務器性能遠不如大數(shù)據(jù)集群，甚至有些ETL服務器還是單點部署。如果采用了技術緩沖層，那么ETL階段只需要直抽上游數(shù)據(jù)到大數(shù)據(jù)平臺即可，不需要在ETL階段做復雜計算，向貼源模型層的裝數(shù)動作在大數(shù)據(jù)平臺完成。

同時技術緩沖層可以避免數(shù)據(jù)加載錯誤，在數(shù)據(jù)抽取有誤時，在技術緩沖層及時檢核發(fā)現(xiàn)，進行數(shù)據(jù)重新抽取，避免數(shù)據(jù)直接接入貼源層。

本層特點

數(shù)據(jù)完全貼源抽取，中間不做邏輯計算，只增加技術字段；

在本層需要嚴格把控數(shù)據(jù)檢核；

數(shù)據(jù)保存周期一般為一周，采用分區(qū)方式保存；

貼源模型層

貼源模型層概述

貼源模型層(IOL)，采用貼源建模的方式加載，數(shù)據(jù)加工場景通常適合于數(shù)據(jù)整合度低、時效性要求較高且需要進行加工但又無法直接通過文件加工獲得的應用，可以以開放數(shù)據(jù)庫直連的方式對外提供服務，也可以導出數(shù)據(jù)文件給下游系統(tǒng)的方式對外供數(shù)。就應用而言，貼源模型層主要是滿足數(shù)據(jù)的快速接入，建設時效性要求比較高、跨系統(tǒng)的數(shù)據(jù)分析，從而減輕業(yè)務系統(tǒng)的壓力，同時支持數(shù)據(jù)檢查和監(jiān)管類的應用。數(shù)據(jù)保存周期一般為永久保留，提供多部門任意歷史數(shù)據(jù)查詢。

file

針對多種源系統(tǒng)數(shù)據(jù)，通過技術緩沖層將數(shù)據(jù)進行表級關聯(lián)或歷史拉鏈等算法，將數(shù)據(jù)存入貼源模型層中，本層中主題設計不會設計特別多，只是針對下游系統(tǒng)對時效性要求較高的場景進行建設，當下游系統(tǒng)有簡單模型劃分時在本層劃分主題，否則交由主題模型層進行主題詳細劃分。

本層特點

整合度低且不能通過文件加工獲得，對建設時效性要求較高,則通過簡單處理及模型拆分的方式對下供數(shù)；

跨系統(tǒng)數(shù)據(jù)分析需求，則關聯(lián)后，使用寬表落地，否則數(shù)據(jù)保留在技術緩沖層；

數(shù)據(jù)存儲周期一般為永久存儲；

主題模型層

主題模型層概述

主題模型層（IML），作為數(shù)據(jù)倉庫中最重要的基礎存儲區(qū)，此數(shù)據(jù)區(qū)將按照分析型業(yè)務的特點對所有進入大數(shù)據(jù)平臺的源數(shù)據(jù)按照主題進行分類存儲，主題的劃分是對分析型應用系統(tǒng)對數(shù)據(jù)需求的分類歸納，如金融行業(yè)較為權威的九大主題模型，主題模型層數(shù)據(jù)來源于技術緩沖層和貼源模型層，主題模型層采用弱三范式進行數(shù)據(jù)組織。

主題模型層會對歷史數(shù)據(jù)進行累積和保存，與技術緩沖層和貼源模型層相比，主題模型層的數(shù)據(jù)涉及范圍更廣、存儲周期更長。

主題模型層表類型分為以下幾類：

1. 第一類、帶有時間戳且不會更新的事件表或者幾乎每天都會發(fā)生變化的流水表；

2. 第二類、會增刪改的狀態(tài)表，數(shù)據(jù)體量大但是變化頻率低，即緩慢變化維SCD（Slowly Changing Dimensions）；

對于第一類表，需要從技術緩沖層獲取增量信息，并根據(jù)數(shù)據(jù)映射加載到主題模型層的目標表中，而對于第二類表，在從技術緩沖層獲取到數(shù)據(jù)后，需要使用時間拉鏈算法進行數(shù)據(jù)加載和轉(zhuǎn)換，在目標表中以開始日期和結(jié)束日期以及標識狀態(tài)來保留歷史數(shù)據(jù)。

file

本層特點

偏重于歷史和整合；

建設周期較長；

適合實現(xiàn)較長歷史周期的數(shù)據(jù)需求；

適合實現(xiàn)一些站在全局角度的高端分析應用及隨機查詢需求；

實現(xiàn)多種業(yè)務整合的應用需求；

金融主題模型示例

file

共性加工層

共性加工層概述

共性加工層（ICL），是從業(yè)務視角出發(fā)，提煉出對數(shù)據(jù)平臺具有共性的數(shù)據(jù)訪問和統(tǒng)計需求，從而構(gòu)建一個面向支持應用的、提供共享的數(shù)據(jù)訪問服務的公共數(shù)據(jù)。

共性加工層的數(shù)據(jù)來源于貼源模型層或主題模型層，數(shù)據(jù)組織形式采用逆三范式的方式，所以該層的表都是一些較大的寬表，涉及維度較多，這樣才能從應用的角度出發(fā)設計，基于這種設計初衷，針對多種應用系統(tǒng)進行業(yè)務調(diào)研，設計出共性部分以及匯總部分，構(gòu)建共性加工層。

共性加工層的構(gòu)建主要完成對基礎數(shù)據(jù)的預連接、預計算、預匯總，主要實現(xiàn)目標如下：

• 同時服務于多個不同應用，實現(xiàn)數(shù)據(jù)加工結(jié)果的共享，減少系統(tǒng)重復加工的開銷；

• 提高用數(shù)查詢效率；

• 降低應用開發(fā)和數(shù)據(jù)查詢的復雜程度；

• 實現(xiàn)對常用業(yè)務統(tǒng)計口徑的定義和維護，避免不同的應用系統(tǒng)加工出來不同口徑的結(jié)果，如碼表、計算規(guī)則、營銷策略等；

file

由于不同業(yè)務各有特點，所以不同業(yè)務的維度和指標將不盡相同，所以共性加工層也需要按照業(yè)務適當?shù)膭澐殖刹煌黝}域，如金融領域可劃分為：合約級、客戶級、產(chǎn)品級、機構(gòu)級，列舉主題域如下：

• 合約級：合約級是銀行賬戶、合同、借據(jù)、行用卡等數(shù)據(jù)的分類匯總，銀行賬戶包括存款賬戶、貸款賬戶、內(nèi)部賬戶、信用卡賬戶等，合同包括個人貸款合同、對公貸款合同等，借據(jù)包括貸款借據(jù)、貼現(xiàn)借據(jù)等。

• 客戶級：客戶級數(shù)據(jù)匯總主要是針對客戶賬務數(shù)據(jù)和基本信息匯總 ，以及客戶整合、客戶貢獻的方案等，一般客戶級可以劃分為個人客戶匯總、個人客戶存款匯總、個人客戶貸款匯總、個人客戶額度匯總、個人客戶理財匯總、個人客戶其他資產(chǎn)匯總、對公客戶匯總、對公客戶存款匯總、對公客戶貸款匯總、對公客戶貼現(xiàn)匯總。

• 產(chǎn)品級：產(chǎn)品級數(shù)據(jù)匯總主要是按照不同業(yè)務產(chǎn)品口徑進行的匯總，如貸款業(yè)務條線余額統(tǒng)計、中間業(yè)務產(chǎn)品收益統(tǒng)計。

• 機構(gòu)級：機構(gòu)級主要是對存貸款賬戶、信用卡、客戶主題數(shù)據(jù)按機構(gòu)屬性的匯總，機構(gòu)級數(shù)據(jù)匯總可以由合約級匯總得到，按照機構(gòu)維度劃分，如內(nèi)部機構(gòu)信息表、機構(gòu)對公貸款匯總、機構(gòu)個人存款匯總、機構(gòu)對公存款匯總、機構(gòu)拆借匯總、機構(gòu)內(nèi)部賬戶匯總、機構(gòu)授信匯總、機構(gòu)貼現(xiàn)匯總。

應用集市層

應用集市層（IDL），用于支持各種分析型的應用，除了高級分析人員可以對大數(shù)據(jù)平臺進行靈活查詢以外，大數(shù)據(jù)大數(shù)據(jù)平臺上的應用均以數(shù)據(jù)集市的方式對外提供數(shù)據(jù)服務，應用集市層不同于共性加工層，其數(shù)據(jù)僅面向于特定的應用，在應用間不做數(shù)據(jù)共享，如果應用間使用存在數(shù)據(jù)共享需求，那么在共性加工層實現(xiàn)共性加工，應用集市層可以構(gòu)建在大數(shù)據(jù)平臺內(nèi)，也可以構(gòu)建在大數(shù)據(jù)平臺以外（應用子系統(tǒng)平臺內(nèi)），其數(shù)據(jù)組織形式?jīng)]有固定限制，按照實際應用子系統(tǒng)決定數(shù)據(jù)組織形式，其數(shù)據(jù)來源于上層基礎數(shù)據(jù)區(qū)與共性加工層。

各種應用集市的建設都依賴于業(yè)務實現(xiàn)的需求，所以設計側(cè)重點和設計方法都不同。

轉(zhuǎn)載自海汼部落，原文鏈接：http://hainiubl.com/topics/75543