01 技術(shù)架構(gòu)選型

教程本身是以阿里云MaxCompute為例，實(shí)際上，流程和方法論是通用的。

在數(shù)據(jù)模型設(shè)計(jì)之前，需要首先完成技術(shù)架構(gòu)的選型。本教程中使用阿里云大數(shù)據(jù)產(chǎn)品MaxCompute配合DataWorks，完成整體的數(shù)據(jù)建模和研發(fā)流程。

完整的技術(shù)架構(gòu)圖如下圖所示：

其中，DataWorks的數(shù)據(jù)集成負(fù)責(zé)完成數(shù)據(jù)的采集和基本的ETL【可以基于開(kāi)源的相關(guān)技術(shù)組件構(gòu)建數(shù)據(jù)采集和ETL基礎(chǔ)平臺(tái)】。MaxCompute作為整個(gè)大數(shù)據(jù)開(kāi)發(fā)過(guò)程中的離線計(jì)算引擎。DataWorks則包括數(shù)據(jù)開(kāi)發(fā)、數(shù)據(jù)質(zhì)量、數(shù)據(jù)安全、數(shù)據(jù)管理等在內(nèi)的一系列功能。

02 數(shù)倉(cāng)分層

在阿里巴巴的數(shù)據(jù)體系中，我們建議將數(shù)據(jù)倉(cāng)庫(kù)分為三層，自下而上為：數(shù)據(jù)引入層（ODS，Operation Data Store）、數(shù)據(jù)公共層（CDM，Common Data Model）和數(shù)據(jù)應(yīng)用層（ADS，Application Data Service）。

數(shù)據(jù)倉(cāng)庫(kù)的分層和各層級(jí)用途如下圖所示：

1. 數(shù)據(jù)引入層ODS（Operation Data Store）：存放未經(jīng)過(guò)處理的原始數(shù)據(jù)至數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)，結(jié)構(gòu)上與源系統(tǒng)保持一致，是數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)準(zhǔn)備區(qū)。主要完成基礎(chǔ)數(shù)據(jù)引入到MaxCompute的職責(zé)，同時(shí)記錄基礎(chǔ)數(shù)據(jù)的歷史變化。
2. 數(shù)據(jù)公共層CDM（Common Data Model，又稱(chēng)通用數(shù)據(jù)模型層），包括DIM維度表、DWD和DWS，由ODS層數(shù)據(jù)加工而成。主要完成數(shù)據(jù)加工與整合，建立一致性的維度，構(gòu)建可復(fù)用的面向分析和統(tǒng)計(jì)的明細(xì)事實(shí)表，以及匯總公共粒度的指標(biāo)。

• 公共維度層（DIM）：基于維度建模理念思想，建立整個(gè)企業(yè)的一致性維度。降低數(shù)據(jù)計(jì)算口徑和算法不統(tǒng)一風(fēng)險(xiǎn)。公共維度層的表通常也被稱(chēng)為邏輯維度表，維度和維度邏輯表通常一一對(duì)應(yīng)。

• 公共匯總粒度事實(shí)層（DWS）：以分析的主題對(duì)象作為建模驅(qū)動(dòng)，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)事實(shí)表，以寬表化手段物理化模型。構(gòu)建命名規(guī)范、口徑一致的統(tǒng)計(jì)指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表、明細(xì)事實(shí)表。
  公共匯總粒度事實(shí)層的表通常也被稱(chēng)為匯總邏輯表，用于存放派生指標(biāo)數(shù)據(jù)。

• 明細(xì)粒度事實(shí)層（DWD）：以業(yè)務(wù)過(guò)程作為建模驅(qū)動(dòng)，基于每個(gè)具體的業(yè)務(wù)過(guò)程特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)層事實(shí)表。可以結(jié)合企業(yè)的數(shù)據(jù)使用特點(diǎn)，將明細(xì)事實(shí)表的某些重要維度屬性字段做適當(dāng)冗余，即寬表化處理。明細(xì)粒度事實(shí)層的表通常也被稱(chēng)為邏輯事實(shí)表。

3. 數(shù)據(jù)應(yīng)用層ADS（Application Data Service）：存放數(shù)據(jù)產(chǎn)品個(gè)性化的統(tǒng)計(jì)指標(biāo)數(shù)據(jù)。根據(jù)CDM與ODS層加工生成。

該數(shù)據(jù)分類(lèi)架構(gòu)在ODS層分為三部分：數(shù)據(jù)準(zhǔn)備區(qū)、離線數(shù)據(jù)和準(zhǔn)實(shí)時(shí)數(shù)據(jù)區(qū)。整體數(shù)據(jù)分類(lèi)架構(gòu)如下圖所示：

在本教程中，從交易數(shù)據(jù)系統(tǒng)的數(shù)據(jù)經(jīng)過(guò)DataWorks數(shù)據(jù)集成，同步到數(shù)據(jù)倉(cāng)庫(kù)的ODS層。經(jīng)過(guò)數(shù)據(jù)開(kāi)發(fā)形成事實(shí)寬表后，再以商品、地域等為維度進(jìn)行公共匯總。

整體的數(shù)據(jù)流向如下圖所示：

其中，ODS層到DIM層的ETL（萃?。‥xtract）、轉(zhuǎn)置（Transform）及加載（Load））處理是在MaxCompute中進(jìn)行的，處理完成后會(huì)同步到所有存儲(chǔ)系統(tǒng)。ODS層和DWD層會(huì)放在數(shù)據(jù)中間件中，供下游訂閱使用。而DWS層和ADS層的數(shù)據(jù)通常會(huì)落地到在線存儲(chǔ)系統(tǒng)中，下游通過(guò)接口調(diào)用的形式使用。

03 數(shù)據(jù)模型

1. 數(shù)據(jù)引入層（ODS）

ODS層存放從業(yè)務(wù)系統(tǒng)獲取的最原始的數(shù)據(jù)，是其他上層數(shù)據(jù)的源數(shù)據(jù)。業(yè)務(wù)數(shù)據(jù)系統(tǒng)中的數(shù)據(jù)通常為非常細(xì)節(jié)的數(shù)據(jù)，經(jīng)過(guò)長(zhǎng)時(shí)間累積，且訪問(wèn)頻率很高，是面向應(yīng)用的數(shù)據(jù)。

數(shù)據(jù)引入層表設(shè)計(jì)

本教程中，在ODS層主要包括的數(shù)據(jù)有：交易系統(tǒng)訂單詳情、用戶(hù)信息詳情、商品詳情等。這些數(shù)據(jù)未經(jīng)處理，是最原始的數(shù)據(jù)。邏輯上，這些數(shù)據(jù)都是以二維表的形式存儲(chǔ)。雖然嚴(yán)格的說(shuō)ODS層不屬于數(shù)倉(cāng)建模的范疇，但是合理的規(guī)劃ODS層并做好數(shù)據(jù)同步也非常重要。
本教程中，使用了6張ODS表：
記錄用于拍賣(mài)的商品信息：s_auction。
記錄用于正常售賣(mài)的商品信息：s_sale。
記錄用戶(hù)詳細(xì)信息：s_users_extra。
記錄新增的商品成交訂單信息：s_biz_order_delta。
記錄新增的物流訂單信息：s_logistics_order_delta。
記錄新增的支付訂單信息：s_pay_order_delta。

說(shuō)明：
通過(guò)_delta來(lái)標(biāo)識(shí)該表為增量表。
表中某些字段的名稱(chēng)剛好和關(guān)鍵字重名了，可以通過(guò)添加_col1后綴解決。

建表示例（s_auction）

CREATE TABLE IF NOT EXISTS s_auction
(
    id                             STRING COMMENT '商品ID',
    title                          STRING COMMENT '商品名',
    gmt_modified                   STRING COMMENT '商品最后修改日期',
    price                          DOUBLE COMMENT '商品成交價(jià)格，單位元',
    starts                         STRING COMMENT '商品上架時(shí)間',
    minimum_bid                    DOUBLE COMMENT '拍賣(mài)商品起拍價(jià)，單位元',
    duration                       STRING COMMENT '有效期，銷(xiāo)售周期，單位天',
    incrementnum                   DOUBLE COMMENT '拍賣(mài)價(jià)格的增價(jià)幅度',
    city                           STRING COMMENT '商品所在城市',
    prov                           STRING COMMENT '商品所在省份',
    ends                           STRING COMMENT '銷(xiāo)售結(jié)束時(shí)間',
    quantity                       BIGINT COMMENT '數(shù)量',
    stuff_status                   BIGINT COMMENT '商品新舊程度 0 全新 1 閑置 2 二手',
    auction_status                 BIGINT COMMENT '商品狀態(tài) 0 正常 1 用戶(hù)刪除 2 下架 3 從未上架',
    cate_id                         BIGINT COMMENT '商品類(lèi)目ID',
    cate_name                        STRING COMMENT '商品類(lèi)目名稱(chēng)',
    commodity_id                     BIGINT COMMENT '品類(lèi)ID',
    commodity_name                    STRING COMMENT '品類(lèi)名稱(chēng)',
    umid                              STRING COMMENT '買(mǎi)家umid'
)
COMMENT '商品拍賣(mài)ODS'
PARTITIONED BY (ds         STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;

數(shù)據(jù)引入層存儲(chǔ)

為了滿(mǎn)足歷史數(shù)據(jù)分析需求，可以在ODS層表中添加時(shí)間維度作為分區(qū)字段。實(shí)際應(yīng)用中，可以選擇采用增量、全量存儲(chǔ)或拉鏈存儲(chǔ)的方式。

• 增量存儲(chǔ)
  以天為單位的增量存儲(chǔ)，以業(yè)務(wù)日期作為分區(qū)，每個(gè)分區(qū)存放日增量的業(yè)務(wù)數(shù)據(jù)。舉例如下：
  1月1日，用戶(hù)A訪問(wèn)了A公司電商店鋪B，A公司電商日志產(chǎn)生一條記錄t1。1月2日，用戶(hù)A又訪問(wèn)了A公司電商店鋪C，A公司電商日志產(chǎn)生一條記錄t2。采用增量存儲(chǔ)方式，t1將存儲(chǔ)在1月1日這個(gè)分區(qū)中，t2將存儲(chǔ)在1月2日這個(gè)分區(qū)中。
  1月1日，用戶(hù)A在A公司電商網(wǎng)購(gòu)買(mǎi)了B商品，交易日志將生成一條記錄t1。1月2日，用戶(hù)A又將B商品退貨了，交易日志將更新t1記錄。采用增量存儲(chǔ)方式，初始購(gòu)買(mǎi)的t1記錄將存儲(chǔ)在1月1日這個(gè)分區(qū)中，更新后的t1將存儲(chǔ)在1月2日這個(gè)分區(qū)中。

交易、日志等事務(wù)性較強(qiáng)的ODS表適合增量存儲(chǔ)方式。這類(lèi)表數(shù)據(jù)量較大，采用全量存儲(chǔ)的方式存儲(chǔ)成本壓力大。此外，這類(lèi)表的下游應(yīng)用對(duì)于歷史全量數(shù)據(jù)訪問(wèn)的需求較小（此類(lèi)需求可通過(guò)數(shù)據(jù)倉(cāng)庫(kù)后續(xù)匯總后得到）。例如，日志類(lèi)ODS表沒(méi)有數(shù)據(jù)更新的業(yè)務(wù)過(guò)程，因此所有增量分區(qū)UNION在一起就是一份全量數(shù)據(jù)。

• 全量存儲(chǔ)
  以天為單位的全量存儲(chǔ)，以業(yè)務(wù)日期作為分區(qū)，每個(gè)分區(qū)存放截止到業(yè)務(wù)日期為止的全量業(yè)務(wù)數(shù)據(jù)。
  例如，1月1日，賣(mài)家A在A公司電商網(wǎng)發(fā)布了B、C兩個(gè)商品，前端商品表將生成兩條記錄t1、t2。1月2日，賣(mài)家A將B商品下架了，同時(shí)又發(fā)布了商品D，前端商品表將更新記錄t1，同時(shí)新生成記錄t3。采用全量存儲(chǔ)方式， 在1月1日這個(gè)分區(qū)中存儲(chǔ)t1和t2兩條記錄，在1月2日這個(gè)分區(qū)中存儲(chǔ)更新后的t1以及t2、t3記錄。

對(duì)于小數(shù)據(jù)量的緩慢變化維度數(shù)據(jù)，例如商品類(lèi)目，可直接使用全量存儲(chǔ)。

• 拉鏈存儲(chǔ) 拉鏈存儲(chǔ)通過(guò)新增兩個(gè)時(shí)間戳字段（start_dt和end_dt），將所有以天為粒度的變更數(shù)據(jù)都記錄下來(lái)，通常分區(qū)字段也是這兩個(gè)時(shí)間戳字段。

拉鏈存儲(chǔ)舉例如下。

這樣，下游應(yīng)用可以通過(guò)限制時(shí)間戳字段來(lái)獲取歷史數(shù)據(jù)。例如，用戶(hù)訪問(wèn)1月1日數(shù)據(jù)，只需限制start_dt<=20160101并且 end_dt>20160101。

緩慢變化維度

MaxCompute不推薦使用代理鍵，推薦使用自然鍵作為維度主鍵，主要原因有兩點(diǎn)：

• MaxCompute是分布式計(jì)算引擎，生成全局唯一的代理鍵工作量非常大。當(dāng)遇到大數(shù)據(jù)量情況下，這項(xiàng)工作就會(huì)更加復(fù)雜，且沒(méi)有必要。
• 使用代理鍵會(huì)增加ETL的復(fù)雜性，從而增加ETL任務(wù)的開(kāi)發(fā)和維護(hù)成本。

在不使用代理鍵的情況下，緩慢變化維度可以通過(guò)快照方式處理。

快照方式下數(shù)據(jù)的計(jì)算周期通常為每天一次?；谠撝芷冢幚砭S度變化的方式為每天一份全量快照。

例如商品維度，每天保留一份全量商品快照數(shù)據(jù)。任意一天的事實(shí)表均可以取到當(dāng)天的商品信息，也可以取到最新的商品信息，通過(guò)限定日期，采用自然鍵進(jìn)行關(guān)聯(lián)即可。該方式的優(yōu)勢(shì)主要有以下兩點(diǎn)：

• 處理緩慢變化維度的方式簡(jiǎn)單有效，開(kāi)發(fā)和維護(hù)成本低。
• 使用方便，易于理解。數(shù)據(jù)使用方只需要限定日期即可取到當(dāng)天的快照數(shù)據(jù)。任意一天的事實(shí)快照與任意一天的維度快照通過(guò)維度的自然鍵進(jìn)行關(guān)聯(lián)即可。

該方法的弊端主要是存儲(chǔ)空間的極大浪費(fèi)。例如某維度每天的變化量占總體數(shù)據(jù)量比例很低，極端情況下，每天無(wú)變化，這種情況下存儲(chǔ)浪費(fèi)嚴(yán)重。該方法主要實(shí)現(xiàn)了通過(guò)犧牲存儲(chǔ)獲取ETL效率的優(yōu)化和邏輯上的簡(jiǎn)化。請(qǐng)避免過(guò)度使用該方法，且必須要有對(duì)應(yīng)的數(shù)據(jù)生命周期制度，清除無(wú)用的歷史數(shù)據(jù)。

數(shù)據(jù)同步加載與處理

ODS的數(shù)據(jù)需要由各數(shù)據(jù)源系統(tǒng)同步到MaxCompute，才能用于進(jìn)一步的數(shù)據(jù)開(kāi)發(fā)。本教程建議使用DataWorks數(shù)據(jù)集成功能完成數(shù)據(jù)同步。在使用數(shù)據(jù)集成的過(guò)程中，建議遵循以下規(guī)范：

• 一個(gè)系統(tǒng)的源表只允許同步到MaxCompute一次，保持表結(jié)構(gòu)的一致性。
• 數(shù)據(jù)集成僅用于離線全量數(shù)據(jù)同步，實(shí)時(shí)增量數(shù)據(jù)同步需要使用數(shù)據(jù)傳輸服務(wù)DTS實(shí)現(xiàn)，詳情請(qǐng)參見(jiàn)數(shù)據(jù)傳輸服務(wù)DTS。
• 數(shù)據(jù)集成全量同步的數(shù)據(jù)直接進(jìn)入全量表的當(dāng)日分區(qū)。
• ODS層的表建議以統(tǒng)計(jì)日期及時(shí)間分區(qū)表的方式存儲(chǔ)，便于管理數(shù)據(jù)的存儲(chǔ)成本和策略控制。
• 數(shù)據(jù)集成可以自適應(yīng)處理源系統(tǒng)字段的變更：
• 如果源系統(tǒng)字段的目標(biāo)表在MaxCompute上不存在，可以由數(shù)據(jù)集成自動(dòng)添加不存在的表字段。
• 如果目標(biāo)表的字段在源系統(tǒng)不存在，數(shù)據(jù)集成填充N(xiāo)ULL。

2. 公共維度匯總層（DIM）

公共維度匯總層（DIM）基于維度建模理念，建立整個(gè)企業(yè)的一致性維度。

公共維度匯總層（DIM）主要由維度表（維表）構(gòu)成。維度是邏輯概念，是衡量和觀察業(yè)務(wù)的角度。維表是根據(jù)維度及其屬性將數(shù)據(jù)平臺(tái)上構(gòu)建的表物理化的表，采用寬表設(shè)計(jì)的原則。因此，構(gòu)建公共維度匯總層（DIM）首先需要定義維度。

定義維度

在劃分數(shù)據(jù)域、構(gòu)建總線矩陣時(shí)，需要結(jié)合對(duì)業(yè)務(wù)過(guò)程的分析定義維度。以本教程中A電商公司的營(yíng)銷(xiāo)業(yè)務(wù)板塊為例，在交易數(shù)據(jù)域中，我們重點(diǎn)考察確認(rèn)收貨（交易成功）的業(yè)務(wù)過(guò)程。

在確認(rèn)收貨的業(yè)務(wù)過(guò)程中，主要有商品和收貨地點(diǎn)（本教程中，假設(shè)收貨和購(gòu)買(mǎi)是同一個(gè)地點(diǎn)）兩個(gè)維度所依賴(lài)的業(yè)務(wù)角度。

從商品角度可以定義出以下維度：
商品ID
商品名稱(chēng)
商品價(jià)格
商品新舊程度：0-全新、1-閑置、 2-二手
商品類(lèi)目ID
商品類(lèi)目名稱(chēng)
品類(lèi)ID
品類(lèi)名稱(chēng)
買(mǎi)家ID
商品狀態(tài)：0-正常、1-用戶(hù)刪除、2-下架、3-從未上架
商品所在城市
商品所在省份

從地域角度，可以定義出以下維度：
買(mǎi)家ID
城市code
城市名稱(chēng)
省份code
省份名稱(chēng)

作為維度建模的核心，在企業(yè)級(jí)數(shù)據(jù)倉(cāng)庫(kù)中必須保證維度的唯一性。以A公司的商品維度為例，有且只允許有一種維度定義。例如，省份code這個(gè)維度，對(duì)于任何業(yè)務(wù)過(guò)程所傳達(dá)的信息都是一致的。

設(shè)計(jì)維表

完成維度定義后，就可以對(duì)維度進(jìn)行補(bǔ)充，進(jìn)而生成維表了。
維表的設(shè)計(jì)需要注意：

• 建議維表單表信息不超過(guò)1000萬(wàn)條。
• 維表與其他表進(jìn)行Join時(shí)，建議使用Map Join。
• 避免過(guò)于頻繁的更新維表的數(shù)據(jù)。

在設(shè)計(jì)維表時(shí)，需要從下列方面進(jìn)行考慮：

• 維表中數(shù)據(jù)的穩(wěn)定性。例如A公司電商會(huì)員通常不會(huì)出現(xiàn)消亡，但會(huì)員數(shù)據(jù)可能在任何時(shí)候更新，此時(shí)要考慮創(chuàng)建單個(gè)分區(qū)存儲(chǔ)全量數(shù)據(jù)。如果存在不會(huì)更新的記錄，可能需要分別創(chuàng)建歷史表與日常表。日常表用于存放當(dāng)前有效的記錄，保持表的數(shù)據(jù)量不會(huì)膨脹；歷史表根據(jù)消亡時(shí)間插入對(duì)應(yīng)分區(qū)，使用單個(gè)分區(qū)存放分區(qū)對(duì)應(yīng)時(shí)間的消亡記錄。
• 是否需要垂直拆分。如果一個(gè)維表存在大量屬性不被使用，或由于承載過(guò)多屬性字段導(dǎo)致查詢(xún)變慢，則需考慮對(duì)字段進(jìn)行拆分，創(chuàng)建多個(gè)維表。
• 是否需要水平拆分。如果記錄之間有明顯的界限，可以考慮拆成多個(gè)表或設(shè)計(jì)成多級(jí)分區(qū)。
• 核心的維表產(chǎn)出時(shí)間通常有嚴(yán)格的要求。

設(shè)計(jì)維表的主要步驟如下：

• 完成維度的初步定義，并保證維度的一致性。
• 確定主維表（中心事實(shí)表，本教程中采用星型模型）。此處的主維表通常是數(shù)據(jù)引入層（ODS）表，直接與業(yè)務(wù)系統(tǒng)同步。例如，s_auction是與前臺(tái)商品中心系統(tǒng)同步的商品表，此表即是主維表。
• 確定相關(guān)維表。數(shù)據(jù)倉(cāng)庫(kù)是業(yè)務(wù)源系統(tǒng)的數(shù)據(jù)整合，不同業(yè)務(wù)系統(tǒng)或者同一業(yè)務(wù)系統(tǒng)中的表之間存在關(guān)聯(lián)性。根據(jù)對(duì)業(yè)務(wù)的梳理，確定哪些表和主維表存在關(guān)聯(lián)關(guān)系，并選擇其中的某些表用于生成維度屬性。以商品維度為例，根據(jù)對(duì)業(yè)務(wù)邏輯的梳理，可以得到商品與類(lèi)目、賣(mài)家、店鋪等維度存在關(guān)聯(lián)關(guān)系。
• 確定維度屬性，主要包括兩個(gè)階段。第一個(gè)階段是從主維表中選擇維度屬性或生成新的維度屬性；第二個(gè)階段是從相關(guān)維表中選擇維度屬性或生成新的維度屬性。以商品維度為例，從主維表（s_auction）和類(lèi)目 、賣(mài)家、店鋪等相關(guān)維表中選擇維度屬性或生成新的維度屬性。
  盡可能生成豐富的維度屬性。
  盡可能多地給出富有意義的文字性描述。
  區(qū)分?jǐn)?shù)值型屬性和事實(shí)。
  盡量沉淀出通用的維度屬性。

公共維度匯總層（DIM）維表規(guī)范
公共維度匯總層（DIM）維表命名規(guī)范：dim_{業(yè)務(wù)板塊名稱(chēng)/pub}_{維度定義}[_{自定義命名標(biāo)簽}]，所謂pub是與具體業(yè)務(wù)板塊無(wú)關(guān)或各個(gè)業(yè)務(wù)板塊都可公用的維度，如時(shí)間維度。
舉例如下：
公共區(qū)域維表dim_pub_area A公司電商板塊的商品全量表dim_asale_itm

建表示例

CREATE TABLE IF NOT EXISTS dim_asale_itm
(
    item_id                            BIGINT COMMENT '商品ID',
    item_title                      STRING COMMENT '商品名稱(chēng)',
    item_price                     DOUBLE COMMENT '商品成交價(jià)格_元',
    item_stuff_status              BIGINT COMMENT '商品新舊程度_0全新1閑置2二手',
    cate_id                          BIGINT COMMENT '商品類(lèi)目ID',
    cate_name                        STRING COMMENT '商品類(lèi)目名稱(chēng)',
    commodity_id                      BIGINT COMMENT '品類(lèi)ID',
    commodity_name                  STRING COMMENT '品類(lèi)名稱(chēng)',
    umid                           STRING COMMENT '買(mǎi)家ID',
    item_status                    BIGINT COMMENT '商品狀態(tài)_0正常1用戶(hù)刪除2下架3未上架',
    city                           STRING COMMENT '商品所在城市',
    prov                           STRING COMMENT '商品所在省份'
)
COMMENT '商品全量表'
PARTITIONED BY (ds        STRING COMMENT '日期,yyyymmdd');

CREATE TABLE IF NOT EXISTS dim_pub_area
(
    buyer_id       STRING COMMENT '買(mǎi)家ID',
    city_code      STRING COMMENT '城市code',
    city_name      STRING COMMENT '城市名稱(chēng)',
    prov_code      STRING COMMENT '省份code',
    prov_name      STRING COMMENT '省份名稱(chēng)'
)
COMMENT '公共區(qū)域維表'
PARTITIONED BY (ds             STRING COMMENT '日期分區(qū)，格式y(tǒng)yyymmdd')
LIFECYCLE 3600;

3. 明細(xì)粒度事實(shí)層（DWD）

明細(xì)粒度事實(shí)層以業(yè)務(wù)過(guò)程驅(qū)動(dòng)建模，基于每個(gè)具體的業(yè)務(wù)過(guò)程特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)層事實(shí)表?？梢越Y(jié)合企業(yè)的數(shù)據(jù)使用特點(diǎn)，將明細(xì)事實(shí)表的某些重要維度屬性字段做適當(dāng)冗余，即寬表化處理。

公共匯總粒度事實(shí)層（DWS）和明細(xì)粒度事實(shí)層（DWD）的事實(shí)表作為數(shù)據(jù)倉(cāng)庫(kù)維度建模的核心，需緊繞業(yè)務(wù)過(guò)程來(lái)設(shè)計(jì)。通過(guò)獲取描述業(yè)務(wù)過(guò)程的度量來(lái)描述業(yè)務(wù)過(guò)程，包括引用的維度和與業(yè)務(wù)過(guò)程有關(guān)的度量。度量通常為數(shù)值型數(shù)據(jù)，作為事實(shí)邏輯表的依據(jù)。事實(shí)邏輯表的描述信息是事實(shí)屬性，事實(shí)屬性中的外鍵字段通過(guò)對(duì)應(yīng)維度進(jìn)行關(guān)聯(lián)。

事實(shí)表中一條記錄所表達(dá)的業(yè)務(wù)細(xì)節(jié)程度被稱(chēng)為粒度。通常粒度可以通過(guò)兩種方式來(lái)表述：一種是維度屬性組合所表示的細(xì)節(jié)程度，一種是所表示的具體業(yè)務(wù)含義。

作為度量業(yè)務(wù)過(guò)程的事實(shí)，通常為整型或浮點(diǎn)型的十進(jìn)制數(shù)值，有可加性、半可加性和不可加性三種類(lèi)型：

• 可加性事實(shí)是指可以按照與事實(shí)表關(guān)聯(lián)的任意維度進(jìn)行匯總。
• 半可加性事實(shí)只能按照特定維度匯總，不能對(duì)所有維度匯總。例如庫(kù)存可以按照地點(diǎn)和商品進(jìn)行匯總，而按時(shí)間維度把一年中每個(gè)月的庫(kù)存累加則毫無(wú)意義。
• 完全不可加性，例如比率型事實(shí)。對(duì)于不可加性的事實(shí)，可分解為可加的組件來(lái)實(shí)現(xiàn)聚集。

事實(shí)表相對(duì)維表通常更加細(xì)長(zhǎng)，行增加速度也更快。維度屬性可以存儲(chǔ)到事實(shí)表中，這種存儲(chǔ)到事實(shí)表中的維度列稱(chēng)為維度退化，可加快查詢(xún)速度。與其他存儲(chǔ)在維表中的維度一樣，維度退化可以用來(lái)進(jìn)行事實(shí)表的過(guò)濾查詢(xún)、實(shí)現(xiàn)聚合操作等。

明細(xì)粒度事實(shí)層（DWD）通常分為三種：事務(wù)事實(shí)表、周期快照事實(shí)表和累積快照事實(shí)表，詳情請(qǐng)參見(jiàn)數(shù)倉(cāng)建設(shè)指南。

• 事務(wù)事實(shí)表用來(lái)描述業(yè)務(wù)過(guò)程，跟蹤空間或時(shí)間上某點(diǎn)的度量事件，保存的是最原子的數(shù)據(jù)，也稱(chēng)為原子事實(shí)表。
• 周期快照事實(shí)表以具有規(guī)律性的、可預(yù)見(jiàn)的時(shí)間間隔記錄事實(shí)。
• 累積快照事實(shí)表用來(lái)表述過(guò)程開(kāi)始和結(jié)束之間的關(guān)鍵步驟事件，覆蓋過(guò)程的整個(gè)生命周期，通常具有多個(gè)日期字段來(lái)記錄關(guān)鍵時(shí)間點(diǎn)。當(dāng)累積快照事實(shí)表隨著生命周期不斷變化時(shí)，記錄也會(huì)隨著過(guò)程的變化而被修改。

明細(xì)粒度事實(shí)表設(shè)計(jì)原則

明細(xì)粒度事實(shí)表設(shè)計(jì)原則如下所示：

• 通常，一個(gè)明細(xì)粒度事實(shí)表僅和一個(gè)維度關(guān)聯(lián)。
• 盡可能包含所有與業(yè)務(wù)過(guò)程相關(guān)的事實(shí) 。
• 只選擇與業(yè)務(wù)過(guò)程相關(guān)的事實(shí)。
• 分解不可加性事實(shí)為可加的組件。
• 在選擇維度和事實(shí)之前必須先聲明粒度。
• 在同一個(gè)事實(shí)表中不能有多種不同粒度的事實(shí)。
• 事實(shí)的單位要保持一致。
• 謹(jǐn)慎處理Null值。
• 使用退化維度提高事實(shí)表的易用性。
• 明細(xì)粒度事實(shí)表整體設(shè)計(jì)流程如下圖所示。

明細(xì)粒度事實(shí)層（DWD）規(guī)范

通常需要遵照的命名規(guī)范為：dwd_{業(yè)務(wù)板塊/pub}_{數(shù)據(jù)域縮寫(xiě)}_{業(yè)務(wù)過(guò)程縮寫(xiě)}[_{自定義表命名標(biāo)簽縮寫(xiě)}] _{單分區(qū)增量全量標(biāo)識(shí)}，pub表示數(shù)據(jù)包括多個(gè)業(yè)務(wù)板塊的數(shù)據(jù)。單分區(qū)增量全量標(biāo)識(shí)通常為：i表示增量，f表示全量。例如：dwd_asale_trd_ordcrt_trip_di（A電商公司航旅機(jī)票訂單下單事實(shí)表，日刷新增量）及dwd_asale_itm_item_df（A電商商品快照事實(shí)表，日刷新全量）。

本教程中，DWD層主要由三個(gè)表構(gòu)成：
交易商品信息事實(shí)表：dwd_asale_trd_itm_di。
交易會(huì)員信息事實(shí)表：ods_asale_trd_mbr_di。
交易訂單信息事實(shí)表：dwd_asale_trd_ord_di。

建表示例（dwd_asale_trd_itm_di）

CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di
(
    item_id              BIGINT COMMENT '商品ID',
    item_title           STRING COMMENT '商品名稱(chēng)',
    item_price           DOUBLE COMMENT '商品價(jià)格',
    item_stuff_status    BIGINT COMMENT '商品新舊程度_0全新1閑置2二手',
    item_prov            STRING COMMENT '商品省份',
    item_city            STRING COMMENT '商品城市',
    cate_id              BIGINT COMMENT '商品類(lèi)目ID',
    cate_name            STRING COMMENT '商品類(lèi)目名稱(chēng)',
    commodity_id         BIGINT COMMENT '品類(lèi)ID',
    commodity_name       STRING COMMENT '品類(lèi)名稱(chēng)',
    buyer_id             BIGINT COMMENT '買(mǎi)家ID',
)
COMMENT '交易商品信息事實(shí)表'
PARTITIONED BY (ds     STRING COMMENT '日期')
LIFECYCLE 400;

4. 公共匯總粒度事實(shí)層（DWS）

明細(xì)粒度 ==> 匯總粒度

公共匯總粒度事實(shí)層以分析的主題對(duì)象作為建模驅(qū)動(dòng)，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求構(gòu)建公共粒度的匯總指標(biāo)事實(shí)表。公共匯總層的一個(gè)表通常會(huì)對(duì)應(yīng)一個(gè)派生指標(biāo)。

公共匯總事實(shí)表設(shè)計(jì)原則

聚集是指針對(duì)原始明細(xì)粒度的數(shù)據(jù)進(jìn)行匯總。DWS公共匯總層是面向分析對(duì)象的主題聚集建模。在本教程中，最終的分析目標(biāo)為：最近一天某個(gè)類(lèi)目（例如：廚具）商品在各省的銷(xiāo)售總額、該類(lèi)目Top10銷(xiāo)售額商品名稱(chēng)、各省用戶(hù)購(gòu)買(mǎi)力分布。因此，我們可以以最終交易成功的商品、類(lèi)目、買(mǎi)家等角度對(duì)最近一天的數(shù)據(jù)進(jìn)行匯總。

• 聚集是不跨越事實(shí)的。聚集是針對(duì)原始星形模型進(jìn)行的匯總。為獲取和查詢(xún)與原始模型一致的結(jié)果，聚集的維度和度量必須與原始模型保持一致，因此聚集是不跨越事實(shí)的。
• 聚集會(huì)帶來(lái)查詢(xún)性能的提升，但聚集也會(huì)增加ETL維護(hù)的難度。當(dāng)子類(lèi)目對(duì)應(yīng)的一級(jí)類(lèi)目發(fā)生變更時(shí)，先前存在的、已經(jīng)被匯總到聚集表中的數(shù)據(jù)需要被重新調(diào)整。

此外，進(jìn)行DWS層設(shè)計(jì)時(shí)還需遵循以下原則：

• 數(shù)據(jù)公用性： 需考慮匯總的聚集是否可以提供給第三方使用?？梢耘袛?，基于某個(gè)維度的聚集是否經(jīng)常用于數(shù)據(jù)分析中。如果答案是肯定的，就有必要把明細(xì)數(shù)據(jù)經(jīng)過(guò)匯總沉淀到聚集表中。
• 不跨數(shù)據(jù)域： 數(shù)據(jù)域是在較高層次上對(duì)數(shù)據(jù)進(jìn)行分類(lèi)聚集的抽象。數(shù)據(jù)域通常以業(yè)務(wù)過(guò)程進(jìn)行分類(lèi)，例如交易統(tǒng)一劃到交易域下，商品的新增、修改放到商品域下。
• 區(qū)分統(tǒng)計(jì)周期： 在表的命名上要能說(shuō)明數(shù)據(jù)的統(tǒng)計(jì)周期，例如_1d表示最近1天，td表示截至當(dāng)天，nd表示最近N天。

公共匯總事實(shí)表規(guī)范

公共匯總事實(shí)表命名規(guī)范：dws_{業(yè)務(wù)板塊縮寫(xiě)/pub}_{數(shù)據(jù)域縮寫(xiě)}_{數(shù)據(jù)粒度縮寫(xiě)}[_{自定義表命名標(biāo)簽縮寫(xiě)}]_{統(tǒng)計(jì)時(shí)間周期范圍縮寫(xiě)}。關(guān)于統(tǒng)計(jì)實(shí)際周期范圍縮寫(xiě)，缺省情況下，離線計(jì)算應(yīng)該包括最近一天（_1d），最近N天（_nd）和歷史截至當(dāng)天（_td）三個(gè)表。如果出現(xiàn)_nd的表字段過(guò)多需要拆分時(shí)，只允許以一個(gè)統(tǒng)計(jì)周期單元作為原子拆分。即一個(gè)統(tǒng)計(jì)周期拆分一個(gè)表，例如最近7天（_1w）拆分一個(gè)表。不允許拆分出來(lái)的一個(gè)表存儲(chǔ)多個(gè)統(tǒng)計(jì)周期。
對(duì)于小時(shí)表（無(wú)論是天刷新還是小時(shí)刷新），都用_hh來(lái)表示。對(duì)于分鐘表（無(wú)論是天刷新還是小時(shí)刷新），都用_mm來(lái)表示。
舉例如下：
dws_asale_trd_byr_subpay_1d（A電商公司買(mǎi)家粒度交易分階段付款一日匯總事實(shí)表）
dws_asale_trd_byr_subpay_td（A電商公司買(mǎi)家粒度分階段付款截至當(dāng)日匯總表）
dws_asale_trd_byr_cod_nd（A電商公司買(mǎi)家粒度貨到付款交易匯總事實(shí)表）
dws_asale_itm_slr_td（A電商公司賣(mài)家粒度商品截至當(dāng)日存量匯總表）
dws_asale_itm_slr_hh（A電商公司賣(mài)家粒度商品小時(shí)匯總表）---維度為小時(shí)
dws_asale_itm_slr_mm（A電商公司賣(mài)家粒度商品分鐘匯總表）---維度為分鐘
建表示例

滿(mǎn)足業(yè)務(wù)需求的DWS層建表語(yǔ)句如下

CREATE TABLE IF NOT EXISTS dws_asale_trd_byr_ord_1d
(
    buyer_id                BIGINT COMMENT '買(mǎi)家ID',
    buyer_nick              STRING COMMENT '買(mǎi)家昵稱(chēng)',
    mord_prov               STRING COMMENT '收貨人省份',
    cate_id                 BIGINT COMMENT '商品類(lèi)目ID',
    cate_name               STRING COMMENT '商品類(lèi)目名稱(chēng)',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天訂單已經(jīng)確認(rèn)收貨的金額總和'
)
COMMENT '買(mǎi)家粒度所有交易最近一天匯總事實(shí)表'
PARTITIONED BY (ds         STRING COMMENT '分區(qū)字段YYYYMMDD')
LIFECYCLE 36000;

CREATE TABLE IF NOT EXISTS dws_asale_trd_itm_ord_1d
(
    item_id                 BIGINT COMMENT '商品ID',
    item_title               STRING COMMENT '商品名稱(chēng)',
    cate_id                 BIGINT COMMENT '商品類(lèi)目ID',
    cate_name               STRING COMMENT '商品類(lèi)目名稱(chēng)',
    mord_prov               STRING COMMENT '收貨人省份',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天訂單已經(jīng)確認(rèn)收貨的金額總和'
)
COMMENT '商品粒度交易最近一天匯總事實(shí)表'
PARTITIONED BY (ds         STRING COMMENT '分區(qū)字段YYYYMMDD')
LIFECYCLE 36000;

04 層次調(diào)用規(guī)范

在完成數(shù)據(jù)倉(cāng)庫(kù)的分層后，需要對(duì)各層次的數(shù)據(jù)之間的調(diào)用關(guān)系作出約定。

ADS應(yīng)用層優(yōu)先調(diào)用數(shù)據(jù)倉(cāng)庫(kù)公共層數(shù)據(jù)。如果已經(jīng)存在CDM層數(shù)據(jù)，不允許ADS應(yīng)用層跨過(guò)CDM中間層從ODS層重復(fù)加工數(shù)據(jù)。CDM中間層應(yīng)該積極了解應(yīng)用層數(shù)據(jù)的建設(shè)需求，將公用的數(shù)據(jù)沉淀到公共層，為其他數(shù)據(jù)層次提供數(shù)據(jù)服務(wù)。同時(shí)，ADS應(yīng)用層也需積極配合CDM中間層進(jìn)行持續(xù)的數(shù)據(jù)公共建設(shè)的改造。避免出現(xiàn)過(guò)度的ODS層引用、不合理的數(shù)據(jù)復(fù)制和子集合冗余?？傮w遵循的層次調(diào)用原則如下：

• ODS層數(shù)據(jù)不能直接被應(yīng)用層任務(wù)引用。如果中間層沒(méi)有沉淀的ODS層數(shù)據(jù)，則通過(guò)CDM層的視圖訪問(wèn)。CDM層視圖必須使用調(diào)度程序進(jìn)行封裝，保持視圖的可維護(hù)性與可管理性。
• CDM層任務(wù)的深度不宜過(guò)大（建議不超過(guò)10層）。
• 一個(gè)計(jì)算刷新任務(wù)只允許一個(gè)輸出表，特殊情況除外。
• 如果多個(gè)任務(wù)刷新輸出一個(gè)表（不同任務(wù)插入不同的分區(qū)），DataWorks上需要建立一個(gè)虛擬任務(wù)，依賴(lài)多個(gè)任務(wù)的刷新和輸出。通常，下游應(yīng)該依賴(lài)此虛擬任務(wù)。
• CDM匯總層優(yōu)先調(diào)用CDM明細(xì)層，可累加指標(biāo)計(jì)算。CDM匯總層盡量?jī)?yōu)先調(diào)用已經(jīng)產(chǎn)出的粗粒度匯總層，避免大量匯總層數(shù)據(jù)直接從海量的明細(xì)數(shù)據(jù)層中計(jì)算得出。
• CDM明細(xì)層累計(jì)快照事實(shí)表優(yōu)先調(diào)用CDM事務(wù)型事實(shí)表，保持?jǐn)?shù)據(jù)的一致性產(chǎn)出。
• 有針對(duì)性地建設(shè)CDM公共匯總層，避免應(yīng)用層過(guò)度引用和依賴(lài)CDM層明細(xì)數(shù)據(jù)。