1.數據倉庫及數倉中的數據模型及操作描述

在大數據設計與開發過程中，數據倉庫是必不可少的一部分。但很多開發者將數倉理解為很多業務庫和業務表的匯總集合，這是不全面的。數倉存在很多設計，架構，業務建模等多個維度的問題。關于數倉的大體介紹可以參照之前的文章(http://www.rzrgm.cn/jiashengmei/p/12092691.html), 本篇只分享數據倉庫中的數據模型及操作。落地到實際中就是在某些業務場景下，數倉的表怎樣去設計。

有過大數據開發經驗的開發者都明白，在大數據環節，數據的操作不能像RDMS數據庫那樣。比如最常見的，在RDMS庫中，假設是mysql數據庫，對數據的單條或根據一定條件修改更新，單條或根據一定條件刪除等等一些操作是再正常不過的。可能會因為數據庫表記錄多存在一定的性能問題。而在大數據環節，很多開發者都知道，大數據的很多數據服務組件其實不支持單條刪除或者更新操作的，比如hive，clickhouse等等數據組件。雖然某些大數據組件在高版本也做到對數據進行特殊的刪除修改，但是也是不建議使用的。為什么會有這樣的問題或者設計？，很多大數據開發人員都清楚，在大數據開發中，往往是面對TB、PB甚至更多的數據，也會面對光一個表就可能是10億、100億甚至更多的條數的數據。在這種情況下，如果不按分而治之的思想去對數據進行操作，而是采用傳統的RDMS數據組件的方式去操作，有一半的操作是沒法進行的，同時會對數據質量，數據的一致性、完整性、時效性造成破壞，同時會對數據服務帶來問題。而分而治之的設計其實就是對數據進行分區，最常見的有按天分區(T-1)模式，按小時分區(H-1)模式。在這些前提下，數倉的數據模型，表結構，及表對數據的存儲和操作都需要區別于傳統的RDMS單獨設計。當然，如何設計一定是站在業務的基礎上，只是某些設計方案和方法具備共用性。下面就舉例介紹在以hive為數倉基礎上的幾個常用的數據模型表設計。

2.數倉之拉鏈表

以電商中的訂單為例，經常會遇到這樣的需求

a.表中的部分字段會被update,最常見的如訂單的狀態

b.需要查看某一個時間點或者時間段的歷史快照信息，比如查看某一個訂單在歷史某一個時間點的狀態,比如查看某一個用戶在過去某一段時間內，更新過幾次。

c.變化的比例和頻率不是很大，比如，總共有1000萬的會員，每天新增和發生變化的有10萬左右。

舉個簡單例子,比如有一張訂單表,6月20號有3條記錄:

到6月21日,表中有5條記錄:

可以看出從2020-06-20到2020-06-21,訂單編號為001的訂單狀態有修改操作，訂單編號為004、005是新增操作

到6月22日，表中有6條記錄：

如果按照上述方式數據存儲下來，會遇到以下幾個問題
a.只保留一份全量，如當前時間為6月22號，數據如6月22日的記錄一樣，如果需要查看6月21日訂單003的狀態，則無法滿足。
b.每天都保留一份全量，即將每天的狀態作全量的保留，則數據倉庫中的該表共有14條記錄，有記錄是重復的，沒有任務變化，如訂單002,004，數據量大了，會造成很大的存儲浪費

基于以上問題，采用拉鏈表來設計訂單表
從6月20號到6月22號拉鏈表的記錄如下::

拉鏈表字段描述
a. dw_begin_date表示該條記錄的生命周期開始時間,dw_end_date表示該條記錄的生命周期結束時間。
b. dw_end_date='9999-12-31'表示該條記錄目前處于有效狀態。

下面來幾個查詢：

a.查詢當前訂單的最新有效狀態

select * from order_his where dw_end_date='9999-12-31';

查詢結果

b.如果查詢2020-06-21的歷史快照

select * from order_his where dw_begin_date<='2020-06-21' and dw_end_date>='2020-06-21';

查詢結果

從以上結果可以看出,這樣的拉鏈表,既能滿足對歷史數據的需求,又能很大程度的節省存儲資源。

3.拉鏈表的更新操作

現在有如下需求：
a.數據倉庫中訂單歷史表的刷新頻率為一天，當天更新前一天的增量數據。
b.如果一個訂單在一天內有多次狀態變化，則只會記錄最后一個狀態的歷史。
c.訂單狀態包括三個：創建、支付、完成。
d.創建時間和修改時間只取到天，如果源訂單表中沒有狀態修改時間，那么抽取增量就比較麻煩，需要有個機制來確保能抽取到每天的增量數據。
基于以上需求，需要創建三張表
源系統中訂單表

CREATE TABLE orders(
order_id string,
create_time string,
modified_time string,
status string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

數倉的ODS層,有一張訂單的增量數據表,按天分區,存放每天的增量數據

CREATE TABLE t_ods_orders_inc(
order_id string,
create_time string,
modified_time string,
status string)
PARTITIONED BY (day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

數倉的DW層,有一張訂單的歷史數據拉鏈表,存放訂單的歷史狀態數據

CREATE TABLE t_dw_orders_his (
order_id string,
create_time string,
modified_time string,
status string,
dw_start_date string,
dw_end_date string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

orders表在2020-08-21的全量數據

在2020-08-22這天抽取orders全量數據到t_ods_orders_inc

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2020-08-21')
SELECT order_id,create_time,modified_time,status
FROM orders WHERE create_time<='2020-08-21';

在2020-08-22這天抽取t_ods_orders_inc全量數據到t_dw_orders_his

INSERT overwrite TABLE t_dw_orders_his
SELECT order_id,create_time,modified_time,status,
create_time AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc WHERE day = '2020-08-21';

t_dw_orders_his數據明細如下

因為這是第一次通過拉鏈表同步數據，可能看不出什么特殊的情況，現在來演示增量更新
orders表在2020-08-22的全量數據

在2020-08-23號只做增量同步抽取2020-08-22的數據到t_ods_orders_inc

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2020-08-22')
SELECT order_id,create_time,modified_time,status
FROM orders
WHERE create_time = '2020-08-22' OR modified_time = '2020-08-22';

查詢結果如下

通過DW歷史數據(數據日期為2015-08-21)，和ODS增量數據(2020-08-22)，刷新歷史表
先把數據放到一張臨時表中：

DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS 
SELECT order_id,
create_time,
modified_time,
status,
dw_start_date,
dw_end_date 
FROM (
    SELECT a.order_id,
    a.create_time,
    a.modified_time,
    a.status,
    a.dw_start_date,
    CASE WHEN b.order_id IS NOT NULL AND a.dw_end_date > '2020-08-22' THEN '2020-08-21' ELSE a.dw_end_date END AS dw_end_date 
    FROM t_dw_orders_his a 
    left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2020-08-22') b 
    ON (a.order_id = b.order_id) 
    UNION ALL 
    SELECT order_id,
    create_time,
    modified_time,
    status,
    modified_time AS dw_start_date,
    '9999-12-31' AS dw_end_date 
    FROM t_ods_orders_inc 
    WHERE day = '2020-08-22' 
) x 
ORDER BY order_id,dw_start_date;

最后把臨時表中數據插入歷史表

INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp;

最后t_dw_orders_his全表的數據如下：

現在來幾個查詢，看拉鏈表是否能夠滿足基本的業務需求
查詢訂單編號為001的整個狀態流程

查詢訂單編號為002的目前狀態

查詢2020-08-21的歷史快照

說明

a.在實際實踐中，只要數據到了DW層的拉鏈表,其他臨時相關的表就可以刪除了，以便減少數據的存儲
b.實際開發設計時，上述過程是完全自動化的

4.數倉模型中其他幾種類型的表

全量表：每天的所有的最新狀態的數據
增量表：每天的新增數據
流水表：對于表中的每一個修改都會記錄，可以用于反映實際記錄的變更