詳解數(shù)據(jù)倉庫開發(fā)規(guī)范要點(diǎn)
具體倉庫的分層情況需要結(jié)合業(yè)務(wù)場景、數(shù)據(jù)場景、系統(tǒng)場景進(jìn)行綜合考慮，下面我們看一下常見的分層：
ODS：Operational Data Store，操作數(shù)據(jù)層，在結(jié)構(gòu)上其與源系統(tǒng)的增量或者全量數(shù)據(jù)基本保持一致。它相當(dāng)于一個數(shù)據(jù)準(zhǔn)備區(qū)，同時又承擔(dān)著基礎(chǔ)數(shù)據(jù)的記錄以及歷史變化。其主要作用是把基礎(chǔ)數(shù)據(jù)引入到數(shù)倉。
CDM：Common Data Model，公共維度模型層，又細(xì)分為DWD和DWS。它的主要作用是完成數(shù)據(jù)加工與整合、建立一致性的維度、構(gòu)建可復(fù)用的面向分析和統(tǒng)計(jì)的明細(xì)事實(shí)表以及匯總公共粒度的指標(biāo)。
DWD：Data Warehouse Detail，明細(xì)數(shù)據(jù)層。 DWS：Data Warehouse Summary，匯總數(shù)據(jù)層。 ADS：Application Data Service，應(yīng)用數(shù)據(jù)層。
該數(shù)據(jù)分類架構(gòu)在ODS層分為三部分：數(shù)據(jù)準(zhǔn)備區(qū)、離線數(shù)據(jù)和準(zhǔn)實(shí)時數(shù)據(jù)區(qū)。在進(jìn)入到CDM層后，由以下幾部分組成：
公共維度層：基于維度建模理念思想，建立整個企業(yè)的一致性維度。
明細(xì)粒度事實(shí)層：以業(yè)務(wù)過程為建模驅(qū)動，基于每個具體業(yè)務(wù)過程的特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)層事實(shí)表。您可以結(jié)合企業(yè)的數(shù)據(jù)使用特點(diǎn)，將明細(xì)事實(shí)表的某些重要維度屬性字段做適當(dāng)?shù)娜哂啵磳挶砘幚怼?/span>
公共匯總粒度事實(shí)層：以分析的主題對象為建模驅(qū)動，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)事實(shí)表，以寬表化手段來物理化模型。
請根據(jù)業(yè)務(wù)劃分?jǐn)?shù)據(jù)并約定命名，建議針對業(yè)務(wù)名稱結(jié)合數(shù)據(jù)層次約定相關(guān)命名的英文縮寫，這樣可以給后續(xù)數(shù)據(jù)開發(fā)過程中，對項(xiàng)目空間、表、字段等命名做為重要參照。
（1）數(shù)據(jù)劃分
按業(yè)務(wù)劃分：命名時按主要的業(yè)務(wù)劃分，以指導(dǎo)物理模型的劃分原則、命名原則及使用的ODS project。
按數(shù)據(jù)域劃分：命名時按照CDM層的數(shù)據(jù)進(jìn)行數(shù)據(jù)域劃分，以便有效地對數(shù)據(jù)進(jìn)行管理，以及指導(dǎo)數(shù)據(jù)表的命名。
按業(yè)務(wù)過程劃分：當(dāng)一個數(shù)據(jù)域由多個業(yè)務(wù)過程組成時，命名時可以按業(yè)務(wù)流程劃分。業(yè)務(wù)過程是從數(shù)據(jù)分析角度看客觀存在的或者抽象的業(yè)務(wù)行為動作。
（2）命名約定
如果公司業(yè)務(wù)線比較多，我們可以按照項(xiàng)目的模式進(jìn)行劃分，如果不是直接按照層次劃分，project_ods、project_dwd
（3）ODS層命名規(guī)范
表命名規(guī)范 表命名規(guī)則：{層次}{源系統(tǒng)表名}{時間單位與增全量},i表示增量，f表示全量 ,d 表示天, h表示小時
增量數(shù)據(jù)：{project_name}.s{源系統(tǒng)表名}_di。
全量數(shù)據(jù)：{project_name}.s{源系統(tǒng)表名}_df。
ODS ETL過程的臨時表：{project_name}.tmp{臨時表所在過程的輸出表}{從0開始的序號}。
按小時同步的增量表：{project_name}.s{源系統(tǒng)表名}_hi。
按小時同步的全量表：{project_name}.s{源系統(tǒng)表名}_hf。
當(dāng)不同源系統(tǒng)同步到同一個Project下的表命名沖突時，您需要給同步較晚的表名加上源系統(tǒng)的dbname以解決沖突。
字段命名規(guī)范 字段默認(rèn)使用源系統(tǒng)的字段名。
字段名與關(guān)鍵字沖突時，在源字段名后加上_col，即源字段名_col。
同步任務(wù)命名規(guī)范 任務(wù)名：建議和表名保持一致。
（4）dim 層命名規(guī)范
命名規(guī)則：{project_name}.dim{業(yè)務(wù)/pub}{維度定義}[_{自定義命名標(biāo)簽}]，其中的pub與具體業(yè)務(wù)無關(guān)，各個業(yè)務(wù)部都可以共用，例如時間維度。
公共區(qū)域維表dim_pub_area
公司社群板塊的群成員全量表dim_group_member
（5）dwd 層命名規(guī)范
通常需要遵照的命名規(guī)范為：dwd_{業(yè)務(wù)板塊/pub}{數(shù)據(jù)域縮寫}{業(yè)務(wù)過程縮寫}[_{自定義表命名標(biāo)簽縮寫}] _{單分區(qū)增量全量標(biāo)識}，pub表示數(shù)據(jù)包括多個業(yè)務(wù)板塊的數(shù)據(jù)。
單分區(qū)增量全量標(biāo)識通常為：i表示增量，f表示全量。例如：dwd_group_create_inf_df（公司社群創(chuàng)建事實(shí)表，日刷新全量）及dwd_group_chat_di（公司社群發(fā)消息事實(shí)表，日刷新增量）。
（6）dws 層命名規(guī)范
公共匯總事實(shí)表命名規(guī)范：dws_{業(yè)務(wù)板塊縮寫/pub}{數(shù)據(jù)域縮寫}{數(shù)據(jù)粒度縮寫}[{自定義表命名標(biāo)簽縮寫}]{統(tǒng)計(jì)時間周期范圍縮寫}。
關(guān)于統(tǒng)計(jì)實(shí)際周期范圍縮寫，缺省情況下，離線計(jì)算應(yīng)該包括最近一天（_1d），最近N天（_nd）和歷史截至當(dāng)天（_td）三個表。
對于小時表（無論是天刷新還是小時刷新），都用_hh 來表示。
對于分鐘表（無論是天刷新還是小時刷新），都用_mm來表示。
舉例如下：
dws_group_patient_join_1d（公司社群患者加群一日匯總事實(shí)表）
dws_group_patient_exit_td（公司社群患者退群截至當(dāng)日匯總表）
應(yīng)用層應(yīng)優(yōu)先調(diào)用公共層數(shù)據(jù)，必須存在中間層數(shù)據(jù)，不允許應(yīng)用層跨過中間層從ODS層重復(fù)加工數(shù)據(jù)。一方面，中間層人員應(yīng)該積極了解應(yīng)用層數(shù)據(jù)的建設(shè)需求，將公用的數(shù)據(jù)沉淀到公共層，為其他人員提供數(shù)據(jù)服務(wù)。
另一方面，應(yīng)用層人員也應(yīng)積極配合中間層人員進(jìn)行持續(xù)的數(shù)據(jù)公共建設(shè)的改造。必須避免出現(xiàn)過度的引用ODS層、不合理的數(shù)據(jù)復(fù)制以及子集合冗余。
ODS層數(shù)據(jù)不能被應(yīng)用層任務(wù)引用，中間層不能有沉淀的ODS層數(shù)據(jù)，必須通過CDM層的視圖訪問。CDM層視圖必須使用調(diào)度程序進(jìn)行封裝，保持視圖的可維護(hù)性與可管理性。
CDM層任務(wù)的深度不宜過大（建議不超過10層）。
原則上一個計(jì)算刷新任務(wù)只允許一個輸出表。
如果多個任務(wù)刷新輸出一個表（不同任務(wù)插入不同的分區(qū)），DataWorks上需要建立一個依賴多個刷新任務(wù)的虛擬任務(wù)，通常下游應(yīng)該依賴此虛擬任務(wù)。
CDM匯總層應(yīng)優(yōu)先調(diào)用CDM明細(xì)層。在調(diào)用可累加類指標(biāo)計(jì)算時，CDM匯總層盡量優(yōu)先調(diào)用已經(jīng)產(chǎn)出的粗粒度匯總層，以避免大量匯總直接從海量的明細(xì)數(shù)據(jù)層計(jì)算。
CDM明細(xì)層累計(jì)快照事實(shí)表優(yōu)先調(diào)用CDM事務(wù)型事實(shí)表，以保持?jǐn)?shù)據(jù)的一致性產(chǎn)出。
避免應(yīng)用層過度引用和依賴CDM層明細(xì)數(shù)據(jù)，需要針對性地建設(shè)好CDM公共匯總層。
ODS層的數(shù)據(jù)類型應(yīng)基于源系統(tǒng)數(shù)據(jù)類型轉(zhuǎn)換。例如，源數(shù)據(jù)為MySQL時的轉(zhuǎn)換規(guī)則如下。
MySQL數(shù)據(jù)類型和Hive數(shù)據(jù)類型：
| MySQL數(shù)據(jù)類型 | Hive 數(shù)據(jù)類型 |
|---|---|
| TINYINT | TINYINT |
| SMALLINT/MEDIUMINT | SMALLINT |
| INTEGER | INT |
| BIGINT | BIGINT |
| FLOAT | FLOAT |
| DOUBLE | DOUBLE |
| DECIMAL | DECIMAL |
| CHAR/VARCHAR | VARCHAR |
| LONGTEXT/TEXT | STRING |
| DATE/TIMESTAMP/TIME/YEAR | STRING |
| DATETIME | DATETIME |
CDM數(shù)據(jù)公共層如果是引用ODS層數(shù)據(jù)，則默認(rèn)使用ODS層字段的數(shù)據(jù)類型。其衍生加工數(shù)據(jù)字段按以下標(biāo)準(zhǔn)執(zhí)行：
金額類及其它小數(shù)點(diǎn)數(shù)據(jù)使用DOUBLE類型。
字符類數(shù)據(jù)使用STRING類型。
ID類和整形數(shù)值使用BIGINT類型。
時間類型數(shù)據(jù)使用STRING類型（如果有特殊的格式要求，可以選擇性使用DATETIME類型）。
狀態(tài)使用STRING類型。
數(shù)據(jù)統(tǒng)計(jì)日期的分區(qū)字段按以下標(biāo)準(zhǔn)：
按天分區(qū)：ds(YYYYMMDD)。
按小時分區(qū)：hh(00-23)。
按分鐘：mi (00-59)。
is_{業(yè)務(wù)}：表示布爾型數(shù)據(jù)字段。以Y和N表示，不允許出現(xiàn)空值域。
原則上不需要冗余分區(qū)字段。
一個表做寬表冗余維度屬性時，應(yīng)該遵循以下建議準(zhǔn)則：
冗余字段與表中其它字段高頻率（大于3個下游應(yīng)用SQL）同時訪問。
冗余字段的引入不應(yīng)造成其本身的刷新完成時間產(chǎn)生過多后延。
公共層數(shù)據(jù)不允許字段重復(fù)率大于60%的相同粒度數(shù)據(jù)表冗余，可以選擇在原表基礎(chǔ)上拓寬或者在下游應(yīng)用中通過JOIN方式實(shí)現(xiàn)。
數(shù)據(jù)的水平和垂直拆分是按照訪問熱度分布和數(shù)據(jù)表非空數(shù)據(jù)值、零數(shù)據(jù)值在行列二維空間上分布情況進(jìn)行劃分的。
在物理上劃分核心模型和擴(kuò)展模型，將其字段進(jìn)行垂直劃分。
將訪問相關(guān)度較高的列在一個表存儲，將訪問相關(guān)度較低的字段分開存儲。
將經(jīng)常用到的Where條件按記錄行進(jìn)行水平切分或者冗余。水平切分可以考慮二級分區(qū)手段，以避免多余的數(shù)據(jù)復(fù)制與冗余。
將出現(xiàn)大量空值和零值的統(tǒng)計(jì)匯總表，依據(jù)其空值和零值分布狀況可以做適當(dāng)?shù)乃胶痛怪鼻蟹郑詼p少存儲和下游的掃描數(shù)據(jù)量。
匯總類指標(biāo)的空值：空值處理，填充為零。
維度屬性值為空：在匯總到對應(yīng)維度上時，對于無法對應(yīng)的統(tǒng)計(jì)事實(shí)，記錄行會填充為-99（未知），對應(yīng)維表會出現(xiàn)一條-99（未知）的記錄。
一致性維度規(guī)范
公共層的維度表中相同維度屬性在不同物理表中的字段名稱、數(shù)據(jù)類型、數(shù)據(jù)內(nèi)容必須保持一致。除了以下情況：
在不同的實(shí)際物理表中，如果由于維度角色的差異，需要使用其他的名稱，其他名稱也必須是規(guī)范的維度屬性的別名。例如，定義一個標(biāo)準(zhǔn)的會員ID時，如果在一個表中，分別要表示買家ID，賣家ID，那么設(shè)計(jì)規(guī)范階段就預(yù)先對會員ID分別定義買家ID和賣家ID。 如果由于歷史原因，在暫時不一致的情況下，必須在規(guī)范的維度定義一個標(biāo)準(zhǔn)維度屬性，不同的物理名也必須是來自標(biāo)準(zhǔn)維度屬性的別名。 維度的組合與拆分
對于維度屬性過多，涉及源較多的維度表（例如會員表），可以做適當(dāng)拆分。 數(shù)據(jù)記錄數(shù)較大的維度表（例如商品表），可以適當(dāng)冗余一些子集合，以減少下游掃描數(shù)據(jù)量。 拆分為核心表和擴(kuò)展表。核心表相對字段較少，刷新產(chǎn)出時間較早，優(yōu)先使用。擴(kuò)展表字段較多，且可以冗余核心表部分字段，刷新產(chǎn)出時間較晚，適合數(shù)據(jù)分析人員使用。 根據(jù)維度屬性的業(yè)務(wù)不相關(guān)性，將相關(guān)度不大的維度屬性拆分為多個物理表存儲。 可以根據(jù)當(dāng)天是否有行為，產(chǎn)出一個有活躍行為的相關(guān)維表，以減少應(yīng)用的數(shù)據(jù)掃描量。 可根據(jù)所屬業(yè)務(wù)掃描數(shù)據(jù)范圍大小的不同，進(jìn)行適當(dāng)子集合冗余。 將維度所描述業(yè)務(wù)相關(guān)性強(qiáng)的字段在一個物理維表實(shí)現(xiàn)。相關(guān)性強(qiáng)是指經(jīng)常需要一起查詢或進(jìn)行報表展現(xiàn)、兩個維度屬性間是否存在天然的關(guān)系等。例如，商品基本屬性和所屬品牌。 無相關(guān)性的維度可以適當(dāng)考慮雜項(xiàng)維度（例如交易），可以構(gòu)建一個交易雜項(xiàng)維度收集交易的特殊標(biāo)記屬性、業(yè)務(wù)分類等信息。也可以將雜項(xiàng)維度退化在事實(shí)表中處理，不過容易造成事實(shí)表相對龐大，加工處理較為復(fù)雜。 所謂的行為維度是經(jīng)過匯總計(jì)算的指標(biāo)，在下游的應(yīng)用使用時將其當(dāng)維度處理。如果有需要，度量指標(biāo)可以作為行為維度冗余到維度表中。 組合原則。 拆分與冗余。
CDM公共維度層的表的類型為維度表，存儲方式為按天分區(qū)。
模型設(shè)計(jì)者根據(jù)自身業(yè)務(wù)需求設(shè)置表的生命周期管理。您可依據(jù)3個月內(nèi)的最大需要訪問的跨度設(shè)置保留策略，具體計(jì)算方式如下：
當(dāng)3個月內(nèi)的最大訪問跨度小于或等于4天時，建議將保留天數(shù)設(shè)為7天。 當(dāng)3個月內(nèi)的最大訪問跨度小于或等于12天時，建議將保留天數(shù)設(shè)為15天。 當(dāng)3個月內(nèi)的最大訪問跨度小于或等于30天時， 建議將保留天數(shù)設(shè)為33天。 當(dāng)3個月內(nèi)的最大訪問跨度小于或等于90天時，建議將保留天數(shù)設(shè)為93天。 當(dāng)3個月內(nèi)的最大訪問跨度小于或等于180天時， 建議將保留天數(shù)設(shè)為183天。 當(dāng)3個月內(nèi)的最大訪問跨度小于或等于365天時，建議將保留天數(shù)設(shè)為368天。
其實(shí)規(guī)范這個東西很重要，但是有時候它的設(shè)計(jì)不那么可續(xù)，例如我們公司的天分區(qū)字段是ds而不是pt，但是這個東西只要大家認(rèn)可就行，但是不能因?yàn)椴徽J(rèn)可就不遵守。
規(guī)范其實(shí)就是約定，所以需要大家共同去維護(hù)和遵守。