柚子快報激活碼778899分享：大數(shù)據(jù) 數(shù)據(jù)倉庫【分層設(shè)計】

http://yzkb.51969.com/

一、為什么要進行數(shù)倉分層

清晰的數(shù)據(jù)結(jié)構(gòu)：每一個分層都有它的作用域與職責(zé)，這樣能夠在我們使用的時候更方便的理解和定位；統(tǒng)一輸出口徑：數(shù)倉分層加工數(shù)據(jù)共享層、指標(biāo)層，統(tǒng)一了數(shù)據(jù)的輸出口徑；數(shù)據(jù)血緣追蹤：方便我們更準(zhǔn)確快速的定位業(yè)務(wù)數(shù)據(jù)問題，并清除它對應(yīng)的影響范圍，及時反饋給業(yè)務(wù)，減少損失；數(shù)據(jù)復(fù)用，減少重復(fù)開發(fā)：規(guī)范的數(shù)倉分層，開發(fā)一些通用的數(shù)倉中間層數(shù)據(jù)，能夠極大的減少重復(fù)計算，實現(xiàn)結(jié)果的復(fù)用，減少煙囪式的開發(fā)模式，極大的降低存儲和計算成本；把復(fù)雜的問題簡單化：將一個復(fù)雜的業(yè)務(wù)問題進行分步驟拆解，每一層只處理單一的步驟，比較簡單也容易理解。如果數(shù)據(jù)出現(xiàn)問題，我們只需要從有問題的步驟開始維護，便于后期數(shù)據(jù)維護；空間換取時間：規(guī)范的數(shù)倉分層，將數(shù)據(jù)進行了充分的數(shù)據(jù)處理，能夠滿足不同粒度的數(shù)據(jù)查詢需求，節(jié)省業(yè)務(wù)查詢數(shù)據(jù)的流程和時間；屏蔽原始數(shù)據(jù)的影響，屏蔽業(yè)務(wù)的影響：業(yè)務(wù)系統(tǒng)發(fā)生變化時，不必每次都重新接入數(shù)據(jù)，通過DW層規(guī)范和屏蔽所有的業(yè)務(wù)復(fù)雜性，保證下游數(shù)據(jù)用戶使用數(shù)據(jù)的便捷和規(guī)范，分層設(shè)計使得某一層的問題只在該層就得到解決，無需更改下一層的代碼和邏輯，提高了數(shù)據(jù)的穩(wěn)定性和連續(xù)性。

數(shù)據(jù)倉庫，通過數(shù)倉建模更好的組織管理和存儲數(shù)據(jù)，以便在性能、成本、效率和質(zhì)量之間取得最好的平衡。?

?二、數(shù)據(jù)倉庫（ETL）的四個操作

ETL（extraction Transformation Loading）負(fù)責(zé)將分散的、異構(gòu)數(shù)據(jù)源中的數(shù)據(jù)抽取到臨時中間層后進行清洗、轉(zhuǎn)換、集成，最后加載到數(shù)據(jù)倉庫或者數(shù)據(jù)集市中。ETL是實施數(shù)據(jù)倉庫的核心和靈魂。

數(shù)據(jù)抽?。‥xtraction）：包括初始化數(shù)據(jù)裝在和數(shù)據(jù)刷新。初始化數(shù)據(jù)裝在主要是如何建立維表、事實表，并把響應(yīng)的數(shù)據(jù)放到這些表中；以及如何做到從業(yè)務(wù)數(shù)據(jù)庫到數(shù)倉的定期新增或者更新數(shù)據(jù)。數(shù)據(jù)清洗：主要是針對源數(shù)據(jù)庫中出現(xiàn)的二義性、重復(fù)的、不完整、違反業(yè)務(wù)或者邏輯規(guī)則等問題的數(shù)據(jù)進行統(tǒng)一的處理。清洗掉不符合業(yè)務(wù)或者沒用的數(shù)據(jù)。數(shù)據(jù)轉(zhuǎn)換（Transformation）：主要是為了將清洗后的數(shù)據(jù)轉(zhuǎn)換成數(shù)據(jù)倉庫所需要的數(shù)據(jù)。來源于不同源系統(tǒng)的同一個字段的數(shù)據(jù)字典或者數(shù)據(jù)格式可能不一樣，在數(shù)據(jù)倉庫中需要給他們提供統(tǒng)一的數(shù)據(jù)字典和格式，對數(shù)據(jù)內(nèi)容進行歸一化。數(shù)據(jù)加載（Loading）：是將最后上面處理完成的數(shù)據(jù)導(dǎo)入到對應(yīng)的存儲空間里，以便給數(shù)據(jù)集市提供，進而可視化。

三、數(shù)據(jù)倉庫的架構(gòu)體系

系統(tǒng)架構(gòu)：基于Hadoop、Spark、Flink等組件為中心的架構(gòu)體系?數(shù)據(jù)架構(gòu)：頂層設(shè)計，主題域劃分，分層設(shè)計，ODS-DWD-DWS-ADS數(shù)據(jù)建模：維度建模，選擇業(yè)務(wù)過程-聲明粒度-確定維度-確定事實數(shù)據(jù)管理：數(shù)據(jù)資產(chǎn)管理，元數(shù)據(jù)管理、數(shù)據(jù)質(zhì)量管理、主數(shù)據(jù)管理、數(shù)據(jù)標(biāo)準(zhǔn)制定、數(shù)據(jù)安全管理輔助系統(tǒng)：調(diào)度系統(tǒng)、ETL系統(tǒng)、監(jiān)控系統(tǒng)數(shù)據(jù)服務(wù)：數(shù)據(jù)門戶、機器學(xué)習(xí)數(shù)據(jù)挖掘、數(shù)據(jù)查詢、分析、報表系統(tǒng)、可視化系統(tǒng)

四、數(shù)倉分層

ODS層：Operation Data Store，元數(shù)據(jù)據(jù)層，存放原始數(shù)據(jù)，直接加載原始日志、數(shù)據(jù)，數(shù)據(jù)保持原貌不做處理；DWD層：Datawarehouse Detail，基于維度建模、維度退化，復(fù)用關(guān)聯(lián)計算，減少數(shù)據(jù)shuffle。對ODS層數(shù)據(jù)進行清洗（去除空值、臟數(shù)據(jù)、超過極限范圍的數(shù)據(jù)）、脫敏等，保存業(yè)務(wù)事實明細(xì)，一行信息代表一次業(yè)務(wù)行為，例如一次下單。DWS層：datawarehouse service，以DWD為基礎(chǔ)，按天進行輕度匯總。構(gòu)建命名規(guī)范、口徑一致的統(tǒng)計指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表。一行信息代表一個主題對象一天的匯總行為，例如一個用戶一天下單的次數(shù)。DM層：以DWS層為基礎(chǔ)，對數(shù)據(jù)進行累計匯總。一行信息代表一個主題對象的累計行為，例如用戶從注冊那天開始一共下來多少次單。ADS層：Application data Store，以DWS為基礎(chǔ)，按主題進行匯總。為各種統(tǒng)計報表提供數(shù)據(jù)、寬表集市、趨勢指標(biāo)。DIM層：維度層，保存維度數(shù)據(jù)，主要對業(yè)務(wù)事實的描述信息，例如何時、何地、何人、渠道等。?

?五、數(shù)倉模型設(shè)計的基本原則（衡量數(shù)倉好壞）

高內(nèi)聚低耦合：

將業(yè)務(wù)相近或者相關(guān)的數(shù)據(jù)、粒度相同的數(shù)據(jù)設(shè)計為一個邏輯或者物理模型；將高概率同時訪問的數(shù)據(jù)放一起，將低概率同時訪問的數(shù)據(jù)分開存儲；核心模型與擴展模型分離：建立核心模型與擴展模型，核心模型包括的字段支持常用的核心的業(yè)務(wù)，擴展模型包括的字段支持個性化或是少量應(yīng)用的需要，不能讓擴展字段過度侵入核心模型，破壞了核心模型的架構(gòu)間接性和可維護性。公共處理邏輯下沉：越是底層共用的處理邏輯更應(yīng)該在數(shù)據(jù)調(diào)度依賴的底層進行封裝與實現(xiàn)，不要讓公共的處理邏輯暴露給應(yīng)用層實現(xiàn)，不要讓公共邏輯在多出同時存在。成本與性能平衡：適當(dāng)?shù)臄?shù)據(jù)冗余換取查詢和刷新的性能，不宜過度冗余與數(shù)據(jù)復(fù)制。數(shù)據(jù)可回滾：處理邏輯不變，在不同多次運行數(shù)據(jù)結(jié)果確定不變。指標(biāo)一致性：相同的字段含義在不同的表中字段命名必須相同，必須使用規(guī)范定義中的名稱。命名清洗可理解：表命名需清晰、一致，表名需易于消費者理解和使用。層次依賴合理：

dwd層應(yīng)該嚴(yán)格遵守層次依賴，理論上只可引用ODS、DIM和部分的DWD數(shù)據(jù)，不可引用處于下游層次的ADS等數(shù)據(jù)，以避免出現(xiàn)“反向引用”的情況；dws應(yīng)該嚴(yán)格遵守層次依賴，理論上只可引用DIM、DWD的數(shù)據(jù)，不可引用處于下游層次的ADS等數(shù)據(jù)，以避免出現(xiàn)“反向引用”的情況。ODS層查詢率：ODS層查詢次數(shù)越多、占比越高，說明數(shù)據(jù)倉模型設(shè)計的越不完善、不好用DWD層數(shù)據(jù)的復(fù)用率： 引用系數(shù)越高，說明數(shù)倉的復(fù)用性越好。模型引用系數(shù)：一個模型被讀取，直接產(chǎn)出下游模型的平均數(shù)量。

六、每層如何設(shè)計

?6.1 ODS設(shè)計

ODS層的處理比較簡單，基本上是將業(yè)務(wù)系統(tǒng)數(shù)據(jù)原封不動的同步即可，一般采用增量、或者全量的方式。

數(shù)據(jù)引入層：將原始數(shù)據(jù)幾乎無處理的存放在數(shù)據(jù)倉庫系統(tǒng)中，結(jié)構(gòu)上與源系統(tǒng)基本保持一致，是數(shù)據(jù)倉庫的數(shù)據(jù)準(zhǔn)備區(qū)。這一層的主要職責(zé)是將基礎(chǔ)數(shù)據(jù)同步、存儲。一般來說ODS層的數(shù)據(jù)和源系統(tǒng)的數(shù)據(jù)是同構(gòu)的，主要目的是簡化后續(xù)數(shù)據(jù)加工處理的工作。從數(shù)據(jù)粒度上來說ODS層的數(shù)據(jù)粒度是細(xì)的。ODS層的表通常包括兩類，一個用于存儲當(dāng)前需要加載的數(shù)據(jù)，一個用于存儲處理完后的歷史數(shù)據(jù)。歷史數(shù)據(jù)一般保存3-6個月后需要清楚，以節(jié)省空間。但是不同的項目要區(qū)別對待，如果源系統(tǒng)的數(shù)據(jù)量不大，可以保留更長的時間，甚至全量保存。

數(shù)據(jù)實時、離線

離線方面：每日定時任務(wù)型，跑批任務(wù)，業(yè)務(wù)庫，比如我們典型的日計算任務(wù)，這里經(jīng)常會使用Sqoop、DataX來抽取，比如我們每天定時抽取一次。每天凌晨計算T+1天的數(shù)據(jù)，早上起來看報表。這種任務(wù)經(jīng)常使用Hive、Spark來計算，最終結(jié)果寫入Hive、Hbase、Mysql、ES或者redis中。實時數(shù)據(jù)：日志埋點數(shù)據(jù)后者業(yè)務(wù)數(shù)據(jù)庫，這一部分主要是各種實時的系統(tǒng)使用，比如我們的實時推薦、實時用戶畫像，一般我們會用Spark Streaming、Flink來計算，最后會落入ES、Hbase或者Redis中。數(shù)據(jù)源是業(yè)務(wù)數(shù)據(jù)庫，可以考慮用Canal監(jiān)聽Mysql的Binlog，實時接入即可，然后是收集到消息隊列中，最終再由Camus拉取到HDFS。

數(shù)據(jù)主要來源：

數(shù)據(jù)源是業(yè)務(wù)數(shù)據(jù)庫，公司所有系統(tǒng)產(chǎn)生的數(shù)據(jù) 是通過在客戶端埋點上報，收集用戶的行為日志，以及一些后端日志的日志類型數(shù)據(jù)源。對于埋點行為日志來說，一般會經(jīng)過一個這樣的流程，首先數(shù)據(jù)會上報到Nginx然后經(jīng)過Flume收集，然后存儲到Kafka這樣的消息隊列，然后再由實時或者離線的一些拉取任務(wù)，拉取到我們的離線數(shù)據(jù)倉庫HDFS。 外部數(shù)據(jù)：包括合作數(shù)據(jù)以及爬蟲獲得的數(shù)據(jù)，將所采集的數(shù)據(jù)匯總到一起。?

數(shù)據(jù)存儲策略（增量、全量）

增量存儲：為了滿足歷史數(shù)據(jù)分析需求，可以在ODS層表中添加時間維度作為分區(qū)字段，以天為單位的增量存儲，以業(yè)務(wù)日期作為分區(qū)，每個分區(qū)存放日增量的業(yè)務(wù)數(shù)據(jù)。交易、日志等事務(wù)性較強的ODS表適合增量存儲方式，這類表數(shù)據(jù)量較大，采用全量存儲的方式存儲成本壓力大，此外，這里表的下游應(yīng)用對于歷史全量數(shù)據(jù)訪問的需求較小。 全量存儲：以天為單位的全量存儲，以業(yè)務(wù)日期作為分區(qū)，每個分區(qū)存放截止到當(dāng)前業(yè)務(wù)日期位置的全量業(yè)務(wù)數(shù)據(jù)。對于小數(shù)據(jù)量的緩慢變化維度數(shù)據(jù)，例如商品類目，可直接使用全量存儲。 拉鏈存儲：拉鏈存儲通過新增兩個時間戳字段（start_dt和end_dt），將所有以天為粒度的變更數(shù)據(jù)都記錄下來，通過分區(qū)字段也是這兩個時間戳字段?

6.2公共層設(shè)計（DWD\DWS）

公共層主要通過抽象、復(fù)用、沉淀物理或邏輯的模型，以提供整體架構(gòu)的數(shù)據(jù)效率并確?？趶揭恢滦浴Ｕ麄€公共層設(shè)計要點在于需求上識別公共性邏輯，設(shè)計上抽象復(fù)用模型，現(xiàn)實上平衡易用性和穩(wěn)定性。一般要遵循以下幾點原則：

是否具有共性：是公共層要考慮的核心問題

DWD的原則：一般情況下，DWD的模型相對好設(shè)計一些，核心是基于維度建模，冗余維度屬性，降低頻率關(guān)聯(lián)，提升基礎(chǔ)數(shù)據(jù)模型的易用性；DWS的原則：DWS的核心訴求是通過空間換取時間，在解決成本、提升效率的同時，實現(xiàn)數(shù)據(jù)口徑的一致性。既然是這樣，那就不能為了加工DWS而加工DWS數(shù)據(jù)，要基于是否是業(yè)務(wù)核心指標(biāo)判斷是否要沉淀公共層，另外，如果是事后沉淀公共層，那要看下需要沉淀的指標(biāo)的應(yīng)用場景有多少，假如只有一個地方使用，那就沒有必要沉淀DWS的必要了。復(fù)用性、易用性、穩(wěn)定性：公共層模型不是為了某一應(yīng)用場景單獨設(shè)計的，而是面向大部分的應(yīng)用場景進行設(shè)計，因此需要進行一定的抽象以提升通用性，從而盡可能覆蓋更多的應(yīng)用場景；

復(fù)用性：指標(biāo)復(fù)用性抽象，轉(zhuǎn)變不可累加的指標(biāo)為可累加的指標(biāo)，如比例、比率建議保留分子分母；粒度復(fù)用性抽象，以最大公約數(shù)的邏輯抽象復(fù)用，比如上游表是子公司的粒度，表二是一級類目粒度，那就設(shè)計出sku粒度的dws表。易用性：在不影響模型產(chǎn)出時效性的情況下，需盡量考慮模型易用性，提升應(yīng)用研發(fā)的使用效率。易用性的設(shè)計主要指的是寬表的設(shè)計和水平切分，用于降低下游理解和多表關(guān)聯(lián)。DWS模型易用性上，通過冗余維度屬性、采用大寬表的方式構(gòu)建，以提升下游易用性；DWS冗余相對不易變的維度屬性，減少下游頻繁關(guān)聯(lián)；DWD模型易用性上，采用星型模型、維度冗余和信息完善度進行設(shè)計，以提升下游易用性，模型設(shè)計應(yīng)以星型模型為主。穩(wěn)定性：通過打款表的建設(shè)方式，公共層極大提升了模型的易用性，但因應(yīng)用場景差異化，時效性也對應(yīng)有不同的要求。公共層需進行必要的穩(wěn)定性設(shè)計，滿足下游重要應(yīng)用高時效性產(chǎn)出的要求成本和效率要有一個權(quán)衡

一般情況下，對于數(shù)據(jù)量比較小的場景，可以優(yōu)先構(gòu)建DWD，后構(gòu)建DWS，在構(gòu)建DWS的過程中，可以優(yōu)先構(gòu)建細(xì)粒度的DWS表（為了擴展性），最后沉淀粗粒度的DWS表對于數(shù)據(jù)體量比較大的情況，可以優(yōu)先構(gòu)建粗粒度的DWS，對于DWD的構(gòu)建，可以采用水平拆解的方式，比如不在冗余半結(jié)構(gòu)化的字段，從而提升產(chǎn)出的時效，日升下游的使用效率。迭代升級

數(shù)據(jù)域的劃分是建設(shè)公共層的基礎(chǔ)，但是數(shù)據(jù)域不是一成不變的，由于業(yè)務(wù)不同，對應(yīng)的數(shù)據(jù)域劃分也自然各不相同，有時候需要靈活處理，并且要根據(jù)業(yè)務(wù)的發(fā)展而調(diào)整相關(guān)數(shù)據(jù)域的劃分；數(shù)據(jù)域目的是為了給數(shù)據(jù)分類，所以盡量可以更好的以業(yè)務(wù)分析視角去組織公共數(shù)據(jù)，從而更好的保持?jǐn)?shù)據(jù)的獨立性，切不可大一統(tǒng)的簡單劃分粗略的一個領(lǐng)域。

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

數(shù)據(jù)倉庫層（DW）：數(shù)據(jù)倉庫層是我們在做數(shù)據(jù)倉庫時要核心設(shè)計的一層，本層將從ODS層中獲得的數(shù)據(jù)按照主題建立各種數(shù)據(jù)模型，每一個主題對應(yīng)一個宏觀的分析領(lǐng)域，數(shù)據(jù)倉庫層排除對決策無用的數(shù)據(jù)，提供特定主題的簡明視圖。DW層會保存BI系統(tǒng)中所有的歷史數(shù)據(jù)。

DW存放明細(xì)事實數(shù)據(jù)、維度數(shù)據(jù)以及公共指標(biāo)匯總數(shù)據(jù)。其中，明細(xì)事實數(shù)據(jù)、維表數(shù)據(jù)一般根據(jù)ODS層數(shù)據(jù)加工生成。公共指標(biāo)匯總數(shù)據(jù)一般根據(jù)維表數(shù)據(jù)和明細(xì)事實數(shù)據(jù)加工生成。

DW層有細(xì)分為維度層（DIM）、明細(xì)數(shù)據(jù)層（DWD）和匯總數(shù)據(jù)層（DWS），采用維度模型方法作為理論基礎(chǔ)，可以定義維度模型主鍵與事實模型中外鍵關(guān)系，減少數(shù)據(jù)冗余，也提高明細(xì)數(shù)據(jù)表的易用性。在匯總數(shù)據(jù)層同樣可以關(guān)聯(lián)復(fù)用統(tǒng)計粒度中的維度，采取更多的寬表化手段構(gòu)建公共指標(biāo)數(shù)據(jù)層，提升公共指標(biāo)的復(fù)用性，減少重復(fù)加工。

維度層（DIM，Dimension）：以維度作為建模驅(qū)動，基于每個維度的業(yè)務(wù)含義，通過添加維度屬性、關(guān)聯(lián)維度等定義計算邏輯，完成屬性定義的過程并建立一致的數(shù)據(jù)分析維表。為了避免在維度模型中冗余關(guān)聯(lián)維度的屬性，基于雪花模型構(gòu)建維度表。

高基數(shù)維度數(shù)據(jù)：一般是用戶資料表、商品資料表類似的資料表。數(shù)據(jù)量可能是千萬級或者上億級別；低基數(shù)維度數(shù)據(jù)：一般是配置表，比如枚舉值對應(yīng)的中文含義，或者日期維表，數(shù)據(jù)量可能是個位或者幾千幾萬。命名：dim_{業(yè)務(wù)板塊名稱/pub}_{維度定義}[自定義命名標(biāo)簽]。pub是與具體業(yè)務(wù)板塊無關(guān)或者各個業(yè)務(wù)板塊都可共用的維度，如時間維度。舉例：dim_pub_area；商品維表：dim_asale_itm明細(xì)數(shù)據(jù)層（DWD，Data Warehouse Detail）：以業(yè)務(wù)過程作為建模驅(qū)動，基于每個具體的業(yè)務(wù)過程特點，構(gòu)建最細(xì)粒度的明細(xì)事實表?？梢詫⒛承┲匾獙傩宰侄巫鲞m當(dāng)?shù)娜哂?，也即寬表化處理。匯總數(shù)據(jù)層（DWS，Data Warehouse Summary）：以分析的駐日對象作為建模驅(qū)動，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)，以寬表化手段物理化模型，構(gòu)建命名統(tǒng)一、口徑一致的統(tǒng)計指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表、明細(xì)事實表。?

數(shù)據(jù)域和主題域

主題域：面向業(yè)務(wù)過程，將業(yè)務(wù)活動時間進行抽象的集合，如下單、支付、退款等都是業(yè)務(wù)過程，針對公共明細(xì)層（DWD）進行主題劃分。主題域通常是聯(lián)系較為緊密的數(shù)據(jù)主題的集合，可以根據(jù)業(yè)務(wù)的關(guān)注點，將這些數(shù)據(jù)主題劃分到不同的主題域（也就是對某個主題進行分析后確定的主題的邊界） 數(shù)據(jù)域：面向業(yè)務(wù)分析，將業(yè)務(wù)過程或者維度進行抽象的集合，針對公共匯總層進行數(shù)據(jù)域劃分。?

維度建模的步驟

選擇業(yè)務(wù)過程：在業(yè)務(wù)系統(tǒng)中，挑選感興趣的業(yè)務(wù)線，比如下單業(yè)務(wù)、支付業(yè)務(wù)、退款業(yè)務(wù)、物流業(yè)務(wù)，一條業(yè)務(wù)線對應(yīng)一張事實表，如果是小公司，盡量把所有的業(yè)務(wù)過程都選擇。聲明粒度：數(shù)據(jù)粒度指數(shù)據(jù)倉庫的數(shù)據(jù)中保存數(shù)據(jù)的細(xì)化程度或綜合程度的級別。聲明粒度意味著精確定義事實表中的一行數(shù)據(jù)表示什么，應(yīng)該盡量選擇最小粒度，一次來應(yīng)對各種各樣的需求。典型的粒度聲明如下：訂單當(dāng)中的每個商品作為下單事實表中的一行，粒度為每次。每周的訂單次數(shù)作為一行，粒度為每周。每月的訂單為一行，粒度為每月。確定維度：維度的主要作用是描述業(yè)務(wù)時事實，主要表示的是“誰、何時、何處”等信息。確定維度的原則是：后續(xù)需求中是否要分析 相關(guān)維度的指標(biāo)。例如，需要統(tǒng)計，什么時間下單的訂單多，那個地區(qū)的訂單多，那個用戶下的訂單多。需要確定的維度就包括：時間維度、地區(qū)維度、用戶維度。維度表：需要根據(jù)維度建模中的星型模型原則進行維度退化。確定事實：?此處的事實一詞，指的是業(yè)務(wù)中的度量值（次數(shù)、個數(shù)、件數(shù)、金額，可以進行累加），例如訂單金額、下單次數(shù)等，dwd層，以業(yè)務(wù)過程為建模驅(qū)動，基于每個具體業(yè)務(wù)過程的特點，構(gòu)建最細(xì)粒度的明細(xì)層事實表，事實表可以做適當(dāng)?shù)膶挶砘幚怼?/p>

DWD層做了哪些事

命名：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_asale_trd_itm_di;交易會員信息事實表：ods_asale_trd_mbr_di；交易訂單信息事實表：dwd_asale_trd_ord_di。

CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di

(

item_id BIGINT COMMENT '商品ID',

item_title STRING COMMENT '商品名稱',

item_price DOUBLE COMMENT '商品價格',

item_stuff_status BIGINT COMMENT '商品新舊程度_0全新1閑置2二手',

item_prov STRING COMMENT '商品省份',

item_city STRING COMMENT '商品城市',

cate_id BIGINT COMMENT '商品類目ID',

cate_name STRING COMMENT '商品類目名稱',

commodity_id BIGINT COMMENT '品類ID',

commodity_name STRING COMMENT '品類名稱',

buyer_id BIGINT COMMENT '買家ID',

)

COMMENT '交易商品信息事實表'

PARTITIONED BY (ds STRING COMMENT '日期')

LIFECYCLE 400;

數(shù)據(jù)清洗過濾

去除廢棄字段、去除格式錯誤的信息 去除丟失關(guān)鍵字段的信息 過濾核心字段無意義的數(shù)據(jù)，比如訂單id為null的，支付表支付id為空的 對手機號、身份證號等敏感信息數(shù)據(jù)脫敏 去除不含時間信息的數(shù)據(jù) 數(shù)據(jù)映射、轉(zhuǎn)換

將GPS經(jīng)緯度轉(zhuǎn)換成省市縣詳細(xì)地址。業(yè)界常用gps快速查詢一般將地理位置知識庫使用genhash映射，然后將需要比對的gps轉(zhuǎn)換為geohash后跟知識庫中的geohash對比，查找出地理位置信息； 將IP地址也轉(zhuǎn)換為省市縣詳細(xì)地址，這個有很多快速查找?guī)?，不過基本原理都是二分查找，因為IP地址可以轉(zhuǎn)換為長整數(shù)，典型的如ip2region庫 將時間轉(zhuǎn)換成年、月、日甚至周、季度維度信息 數(shù)據(jù)規(guī)范化：因為大數(shù)據(jù)處理的數(shù)據(jù)可能來自不同的部門，不同的項目、不同的客戶端，這個時候可能相同的業(yè)務(wù)數(shù)據(jù)字段、數(shù)據(jù)類型、空值等都不一樣，需要在DWD層做抹平，否則后續(xù)處理使用的時候，會造成很大的困擾。

如boolean，有使用0、1標(biāo)識的，也有使用true、false標(biāo)識的 如字符串空值，有使用’‘的，也有使用null的，統(tǒng)一為null即可 如日期格式，這種差異性更大，需要根據(jù)實際業(yè)務(wù)數(shù)據(jù)決定，不過一般都格式化為YYYY-MM-dd HH:mm:ss這類標(biāo)準(zhǔn)格式 維度退化：對業(yè)務(wù)數(shù)據(jù)傳過來的表進行維度退化和降維。訂單id冗余在事實表。?

DWS數(shù)據(jù)服務(wù)層，匯總層寬表

DWS層（數(shù)據(jù)匯總）寬表，面向主題的匯總，維度相對來說比較少，DWS是根據(jù)DWD層基礎(chǔ)數(shù)據(jù)各個維度ID進行粗粒度匯總聚合，如按交易來源，交易類型進行匯總。整個匯總成分析某一個主題域的服務(wù)數(shù)據(jù)，一般是寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析、數(shù)據(jù)分發(fā)等。

主題建模：圍繞某一個業(yè)務(wù)主題進行數(shù)據(jù)建模，將相關(guān)數(shù)據(jù)抽離提取出來，如流量會話按照天、月進行聚合，每日用戶進行聚合。維度建模： 根據(jù)業(yè)務(wù)需要，提前將后續(xù)數(shù)據(jù)查詢處理需要的維度數(shù)據(jù)抽離處理出來，方便后續(xù)查詢使用；

drop table

if exists dws_sale_detail_daycount;

create external table dws_sale_detail_daycount(

user_id string comment '用戶 id',

--用戶信息

user_gender string comment '用戶性別',

user_age string comment '用戶年齡',

user_level string comment '用戶等級',

buyer_nick string comment '買家昵稱',

mord_prov string comment '地址',

--下單數(shù)、 商品數(shù)量， 金額匯總

login_count bigint comment '當(dāng)日登錄次數(shù)',

cart_count bigint comment '加入購物車次數(shù)',

order_count bigint comment '當(dāng)日下單次數(shù)',

order_amount decimal(16,2) comment '當(dāng)日下單金額',

payment_count bigint comment '當(dāng)日支付次數(shù)',

payment_amount decimal(16,2) comment '當(dāng)日支付金額',

confirm_paid_amt_sum_1d double comment '最近一天訂單已經(jīng)確認(rèn)收貨的金額總和'

order_detail_stats array> comment '下單明細(xì)統(tǒng)計'

) comment '每日購買行為'

partitioned by(`dt`

string)

stored as parquet

location '/warehouse/gmall/dws/dws_sale_detail_daycount/'

tblproperties("parquet.compression" = "lzo");

用戶粒度匯總表樣例

首次、末次登錄時間 ?首次、末次下單時間 首次、末次支付時間 最近30天下單的次數(shù)、金額 最近30天支付次數(shù)、金額 累計登錄天數(shù)、最近30天的登錄天數(shù) 累計下單次數(shù)、金額 累計支付次數(shù)、金額

6.3如何設(shè)計ADS層

應(yīng)用層的定位是根據(jù)特定業(yè)務(wù)需求，按照業(yè)務(wù)角度組織數(shù)據(jù)以快速滿足業(yè)務(wù)需求。應(yīng)用層研發(fā)核心關(guān)注研發(fā)效率、口徑一致性，以及核心應(yīng)用的穩(wěn)定性。設(shè)計原則以下：

需求驅(qū)動：需求驅(qū)動構(gòu)建集市，按照最小原則設(shè)計，除非有明確的業(yè)務(wù)延續(xù)，否則不做過度的擴展設(shè)計。應(yīng)用層的設(shè)計需要考慮業(yè)務(wù)定制的需求，提供面向業(yè)務(wù)低估值的應(yīng)用數(shù)據(jù)，比如報表數(shù)據(jù)、大寬表等，供線上系統(tǒng)使用。劃分集市域、公共抽象下沉

與公共層類似，以高內(nèi)聚低耦合的原則對集市進行劃分，讓單集市數(shù)據(jù)研發(fā)聚焦在某一個領(lǐng)域需求實現(xiàn)；集市間應(yīng)該避免互相依賴，避免復(fù)雜度的提升；ADS也可以抽象公共部分，通過依賴ADS數(shù)據(jù)，提升開發(fā)效率和產(chǎn)出效率減少ods的依賴?：減少直接引用ODS表，降低源系統(tǒng)變更帶來的改造成本，架構(gòu)合理上考慮，公共層針對復(fù)用性的場景進行模型沉淀，當(dāng)源系統(tǒng)變更時，通過公共層適應(yīng)性改造屏蔽下游變更。

CREATE TABLE app_usr_interact( user_id string COMMENT '用戶id',

nickname string COMMENT '用戶昵稱',

register_date string COMMENT '注冊日期',

register_from string COMMENT '注冊來源',

remark string COMMENT '細(xì)分渠道',

province string COMMENT '注冊省份',

pl_cnt bigint COMMENT '評論次數(shù)',

ds_cnt bigint COMMENT '打賞次數(shù)',

sc_add bigint COMMENT '添加收藏',

sc_cancel bigint COMMENT '取消收藏',

gzg_add bigint COMMENT '關(guān)注商品',

gzg_cancel bigint COMMENT '取消關(guān)注商品',

gzp_add bigint COMMENT '關(guān)注人',

gzp_cancel bigint COMMENT '取消關(guān)注人',

buzhi_cnt bigint COMMENT '點不值次數(shù)',

zhi_cnt bigint COMMENT '點值次數(shù)',

zan_cnt bigint COMMENT '點贊次數(shù)',

share_cnts bigint COMMENT '分享次數(shù)',

bl_cnt bigint COMMENT '爆料數(shù)',

fb_cnt bigint COMMENT '好價發(fā)布數(shù)',

online_cnt bigint COMMENT '活躍次數(shù)',

checkin_cnt bigint COMMENT '簽到次數(shù)',

fix_checkin bigint COMMENT '補簽次數(shù)',

house_point bigint COMMENT '幸運屋金幣抽獎次數(shù)',

house_gold bigint COMMENT '幸運屋積分抽獎次數(shù)',

pack_cnt bigint COMMENT '禮品兌換次數(shù)',

gold_add bigint COMMENT '獲取金幣',

gold_cancel bigint COMMENT '支出金幣',

surplus_gold bigint COMMENT '剩余金幣',

event bigint COMMENT '電商點擊次數(shù)',

gmv_amount bigint COMMENT 'gmv',

gmv_sales bigint COMMENT '訂單數(shù)'

)

PARTITIONED BY( dt string)

--stat_dt

date COMMENT '互動日期',

?分析指標(biāo)舉例：

日活、月活、周活、留存、留存率、新增（日、月、年）、轉(zhuǎn)化率、流失、回流、7天內(nèi)連續(xù)3天登錄（點贊、收藏、評價、購買、下單、活動）、連續(xù)3周（月）登錄、GMV、復(fù)購率、退款人數(shù)

柚子快報激活碼778899分享：大數(shù)據(jù) 數(shù)據(jù)倉庫【分層設(shè)計】

http://yzkb.51969.com/

文章鏈接