從ODS到ADS，詳解數倉分層！

一、為什麼要分層資料倉儲

只有資料模型將資料有序的組織和儲存起來之後，大資料才能得到高效能、低成本、高效率、高品質的使用。

01 分層意義

1）清晰資料結構：每一個資料分層都有它的作用域，這樣我們在使用表的時候才能更方便地定位和理解。

資料關係條理化：來源系統間存在複雜的資料關係，例如客戶資訊同時存在於核心系統、信貸系統、理財系統、資金系統，取數時該如何決策？資料倉儲會對相同主題的資料進行統一建模，把複雜的資料關係梳理成條理清晰的資料模型，使用時就可避免上述問題了。

2）資料血緣追蹤：簡單來講可以這樣理解，我們最終給業務誠信的是一能直接使用的張業務表，但是它的來源有很多，如果有一張來源表出問題了，我們希望能夠快速準確地定位到問題，並清楚它的危害範圍。

3）資料重複使用，減少重複開發：規範資料分層，開發一些通用的中間層數據，能夠減少極大的重複計算。資料的逐層加工原則，下層包含了上層資料加工所需的全量數據，這樣的加工方式避免了每個資料開發人員都重新從來源系統抽取資料進行加工。透過總結層的引人，避免了下游使用者邏輯的重複運算， 節省了使用者的開發時間和精力，同時也節省了運算和儲存。大幅減少不必要的資料冗餘，也能實現運算結果多用，大幅降低儲存和運算成本。

4）把複雜問題簡單化。 講一個複雜的任務分解成多個步驟來完成，每一層只處理單一的步驟，比較簡單和容易理解。而且便於維護數據的準確性，當數據出現問題後，可以不用修復所有的數據，只需要從有問題的步驟開始修復。

5）屏蔽原始資料的(影響) ，屏蔽業務的影響。 業務或系統發生變更時，不必改一次業務就需要重新存取資料。提高數據穩定性和連續性。

屏蔽源頭業務系統的複雜性：源頭系統可能極為繁雜，而且表命名、欄位命名、欄位意義等可能五花八門，透過DW 層來規範和封鎖所有這些複雜性，確保下游資料使用者使用資料的便利性和規格。如果來源系統業務發生變更，相關的變更由 DW 層來處理，對下游使用者透明，無須改變下游使用者的程式碼和邏輯。

資料倉儲的可維護性：分層的設計使得某一層的問題只在該層解決，無須更改下一層的程式碼和邏輯。

大數據系統需要數據模型方法來幫助更好地組織和儲存數據，以便在效能、成本、效率和品質之間取得最佳平衡！

02  資料倉儲（ETL）的四個動作

ETL(extractiontransformation loading)負責將分散的、異質資料來源中的資料抽取到臨時中間層後進行清洗、轉換、集成，最後載入到資料倉儲或資料集市。 ETL 是實施資料倉儲的核心與靈魂，ETL規則的設計與實作約佔整個資料倉儲搭建工作量的 60%～80%。

#

1）資料抽取(extraction)包括初始化資料裝載和資料刷新：初始化資料裝載主要關注的是如何建立維表、事實表，並將相應的資料放到這些資料表中；而資料刷新關注的是當來源資料變更時如何對資料倉儲中的對應資料進行追加和更新等維護(例如可以建立定時任務，或是觸發器的形式進行資料的定時刷新)。

2）資料清洗#主要是針對來源資料庫中出現的二義性、重複、不完整、違反業務或邏輯規則等問題的資料進行統一的處理。即清洗掉不符合業務或沒用的資料。例如透過編寫hive或MR清洗欄位中長度不符合要求的資料。

3）資料轉換(transformation)主要是為了將資料清洗後的資料轉換成資料倉庫所需的資料：來自不同來源系統的相同資料欄位的資料字典或資料格式可能不一樣(例如A表中叫id,B表中叫ids)，在資料倉儲中需要給它們提供統一的資料字典和格式，對資料內容進行歸一化；另一方面，資料倉儲所需的某些欄位的內容可能是來源系統所不具備的，而是需要根據來源系統中多個欄位的內容共同決定。

4）資料載入（loading）是將最後上面處理完的資料匯入到對應的存儲空間裡（hbase，mysql等）以方便給資料集市提供，進而視覺化。

一般大公司為了資料安全和操作方便，都是自己封裝的資料平台和任務調度平台，底層封裝了大資料集群例如hadoop集群， spark集群，sqoop,hive,zookeepr,hbase等只提供web介面，並且對於不同員工加以不同權限，然後對集群進行不同的操作和調用。以資料倉儲為例，將資料倉儲分為邏輯上的幾個層次。這樣對於不同層次的資料操作，創建不同層次的任務，可以放到不同層次的任務流程中進行執行（大公司一個集群通常每天的定時任務有幾千個等待執行，甚至上萬個，所以劃分不同層次的任務流，不同層次的任務放到對應的任務流中執行，會更方便管理與維護）。

03 分層的誤區

數倉層內部的分割不是為了分層而分層，分層是為了解決ETL 任務及工作流程的組織、資料的流向、讀寫權限的控制、不同需求的滿足等各類問題。

業界較為通行的做法將整個數倉層又劃分成了 DWD、DWT、DWS、DIM、DM等很多層。然而我們卻始終說不清楚這幾層之間清晰的界限是什麼，或者說我們能說清楚它們之間的界限，複雜的業務場景卻令我們無法真正落地執行。

所以資料分層這塊一般來說三層是最基礎的，至於DW層如何進行切分，是根據具體的業務需求和公司場景自己定義。

二、資料倉儲的技術架構

資料中台包含的內容很多，對應到具體工作中的話，它可以包含下面的這些內容：##

• 系統架構：以Hadoop、Spark等元件為中心的架構體系
• 資料架構：頂層設計，主題域劃分，分層設計，ODS-DW-ADS
• ##資料建模： 維度建模，業務流程-確定粒度-維度-事實表
• #資料管理：資產管理，元資料管理、品質管理、主資料管理、資料標準、資料安全管理
• #輔助系統：調度系統、ETL系統、監控系統
• 資料服務：資料入口網站、機器學習資料探勘、資料查詢、分析、報表系統、視覺化系統、資料交換分享下載

三、數倉分層架構

#資料倉儲標準上可分為四層。但要注意這種劃分和命名不是唯一的，一般數倉都是四層，但是不同公司可能叫法不同。但核心的理念都是從四層資料模型而來。

#01 貼波源層（ODS, Operational Data Store）

資料引入層（ODS，Operational Data Store，又稱為資料基礎層）：將原始資料幾乎無法處理地存放在資料倉儲系統中，結構上與來源系統基本保持一致，是資料倉儲的資料準備區。這一層的主要職責是將基礎資料同步、儲存。

一般來說 ODS 層的資料和來源系統的資料是同構的，主要目的是簡化後續資料加工處理的工作。從資料粒度上來說 ODS 層的資料粒度是細的。 ODS 層的表通常包括兩類，一個用於儲存目前需要載入的數據，一個用於儲存處理完後的歷史資料。歷史資料一般保存 3-6 個月後需清除，以節省空間。但不同的項目要區別對待，如果來源系統的資料量不大，可以保留更長的時間，甚至全量保存。

注意：在這層，理應不是簡單的資料接入，而是要考慮一定的資料清洗，例如異常欄位的處理、欄位命名規範化、時間字段的統一等，一般這些很容易被忽略，但是卻至關重要。特別是後期我們做各種特徵自動生成的時候，會十分有用。

注意：有的公司ODS層不會做太多資料過濾處理,會放到DWD層來處理。有的公司會在一開始時就在ODS層做數據相對精細化的過濾.這個並沒有明確規定,看每個公司自己的想法和技術規範。

一般企業開發時,都會對原始資料存入ODS時,做一些最基本的處理。

資料來源區分

#資料依照時間分區儲存,一般是依照天,也有公司使用年、月、日三級分區做儲存的。

進行最基本的資料處理,如格式錯誤的丟棄,關鍵資訊遺失的過濾掉等等。

資料即時離線#

• 離線方面：每日定時任務型：跑批任務，業務庫，例如我們典型的每日計算任務，這裡經常會使用Sqoop 來抽取，例如我們每天定時抽取一次。每天凌晨算前一天的數據，早上起來看報表。這個任務經常使用 Hive、Spark 來計算，最終結果寫入 Hive、Hbase、Mysql、Es 或 Redis 中。
• 即時資料：日誌埋點資料或業務庫，這部分主要是各種即時的系統使用，例如我們的即時推薦、即時使用者畫像，一般我們會用Spark Streaming、Flink 來計算，最後會落入Es、Hbase 或Redis 中。資料來源是業務資料庫，可以考慮用 Canal 監聽 Mysql 的 Binlog，即時存取即可，然後也是收集到訊息佇列中，最後再由 Camus 拉取到 HDFS。

1）資料主要來源：

• 資料來源是業務資料庫，公司所有的系統產生的資料
• 是透過在客戶端埋點上報，收集使用者的行為日誌，以及一些後端日誌的日誌類型資料來源。對於埋點行為日誌來說，一般會經過一個這樣的流程，首先數據會上報到Nginx 然後經過Flume 收集，然後存儲到Kafka 這樣的消息隊列，然後再由實時或者離線的一些拉取的任務，拉取到我們的離線資料倉儲HDFS
• 外部資料（包括合作資料以及爬蟲所獲得的資料），將所收集的資料匯總到一起

2）資料儲存策略（增量、全量）

實際應用中，可以選擇採用增量、全量儲存或拉鍊儲存的方式。

• 增量儲存

#為了滿足歷史資料分析需求，您可以在ODS層表中新增時間維度作為分區欄位。以天為單位的增量存儲，以業務日期作為分區，每個分區存放日增量的業務資料。

舉例如下：

#1月1日，用戶A訪問了A公司電商店舖B，A公司電商日誌產生一筆記錄t1。1月2日，用戶A又造訪了A公司電商店鋪C，A公司電商日誌產生一筆記錄t2。

採用增量儲存方式，t1將儲存在1月1日這個分割區中，t2將儲存在1月2日這個分割區中。

1月1日，用戶A在A公司電商網購買了B商品，交易日誌將產生一筆記錄t1。1月2日，用戶A又將B商品退貨了，交易日誌會更新t1記錄。

採用增量儲存方式，初始購買的t1記錄將會儲存在1月1日這個分割區中，更新後的t1將會儲存在1月2日這個分區中。

交易、日誌等交易性較強的ODS表適合用來增量儲存方式。這類表資料量較大，以全量儲存的方式儲存成本壓力大。此外，這類表的下游應用對於歷史全量資料存取的需求較小（此類需求可透過資料倉儲後續匯總後得到）。例如，日誌類別ODS表沒有資料更新的業務流程，因此所有增量分區UNION在一起就是一份全量資料。

• 全量儲存

#以天為單位的全量存儲，以業務日期作為分區，每個分區存放截止到業務日期為止的全量業務資料。

例如，1月1日，賣家A在A公司電商網發布了B、C兩個商品，前端商品表將產生兩筆記錄t1、t2。1月2日，賣家A將B商品下架了，同時又發布了商品D，前端商品表將更新記錄t1，同時新生成記錄t3。採用全量儲存方式， 在1月1日這個分區中儲存t1和t2兩筆記錄，在1月2日這個分區中儲存更新後的t1以及t2、t3記錄。

#

對於小數據量的緩慢變化維度數據，例如商品類目，可直接使用全量儲存。

• 拉鍊儲存

#拉鍊儲存透過新增兩個時間戳欄位（start_dt和end_dt），將所有以天為粒度的變更資料都記錄下來，通常分區欄位也是這兩個時間戳欄位。

方案

#概念：又稱為介面層(stage)，用於儲存每天的增量資料和變更資料

資料產生方式：直接從kafka接收來源數據，需要業務表每天產生update,delete,inseret數據，只產生insert資料的業務表，資料直接入明細層。

討論方案：只把canal日誌直接入緩衝層，如果其它有拉鍊資料的業務，也入緩衝層。

日誌儲存方式：使用impala外表，parquet檔案格式，方便需要MR處理的資料讀取。

日誌刪除方式：長久存儲，可只儲存最近幾天的資料。討論方案：直接長久儲存。

表schema：一般會按天建立分割區，partitioned by 一般都是依照天進行存放。

庫與表命名。庫名：ods,表名：初步考慮格式為ods日期業務表名,待定。

hive的外部表,對應的是業務表。

hive外部表,存放資料的檔案可以不是在hive的hdfs預設的位置,並且hive對應的表刪除時,對應的資料檔並不會被刪除.這樣對於企業開發來說,可以防止因為刪除表的操作而把寶貴的數據刪除掉hive的業務表,則相反.數據文件存放在hive對應的默認位置,表刪除時,對應文件也會被刪除掉。

02 數倉層（DW,data warehouse）

資料倉儲層(DW)層：資料倉儲層是我們在做資料倉儲時要核心設計的一層，本層將從ODS 層中獲得的資料依照主題建立各種資料模型，每一個主題對應一個宏觀的分析領域，資料倉儲層排除對決策無用的數據，提供特定主題的簡明視圖。在DW層會保存BI系統中所有的歷史數據，例如保存10年的數據。

DW存放明細事實資料、維表資料及公共指標總表資料。其中，明細事實資料、維表資料一般依據ODS層資料加工產生。公共指標匯總資料一般根據維表資料和明細事實資料加工產生。

DW層又細分為維度層（DIM）、明細資料層（DWD）和匯總資料層（DWS），並採用維度模型方法作為理論基礎， 可定義維度模型主鍵與事實模型中外鍵關係，減少資料冗餘，也提高明細資料表的易用性。在匯總資料層同樣可以關聯復用統計粒度中的維度，採取更多的寬表化手段建構公共指標資料層，提升公共指標的複用性，減少重複加工。

維度層（DIM，Dimension）：以維度作為建模驅動，基於每個維度的業務意義，透過新增維度屬性、關聯維度等定義計算邏輯，完成屬性定義的過程並建立一致的資料分析維度表。為了避免在維度模型中冗餘關聯維度的屬性，基於雪花模型建立維度表。

明細資料層（DWD，Data Warehouse Detail）：以業務流程作為建模驅動，基於每個具體的業務流程特點，建構最細粒度的明細事實表。可將某些重要屬性欄位做適當冗餘，也即寬表化處理。

#

總結資料層（DWS，Data Warehouse Summary）：以分析的主題物件作為建模驅動，基於上層的應用和產品的指標需求，建立公共粒度的匯總指標表。以寬表化手段物理化模型，建構命名規範、口徑一致的統計指標，為上層提供公共指標，建立總結寬表、明細事實表。

主題域：面向業務流程，將業務活動事件進行抽象的集合，如下單、支付、退款都是業務過程。針對公共明細層（DWD）進行主題劃分。

資料域：面向業務分析，將業務流程或維度進行抽象的集合。針對公共匯總層（DWS） 進行資料域劃分。

DWD 層是以業務流程為驅動。

DWS 層、DWT 層和 ADS 層都是以需求為驅動。

DWD：data warehouse details 資料明細層。主要對ODS資料層做一些資料清洗和規範化的操作。

資料清洗：移除空值、髒資料、枚舉值轉換，超過極限範圍的。

DWB：data warehouse base 資料基礎層，儲存的是客觀數據，一般用作中間層，可以認為是大量指標的資料層。

DWS：data warehouse service 資料服務層，基於DWB上的基礎數據，整合匯總成分析某一個主題域的服務數據層，一般是寬表。用於提供後續的業務查詢，OLAP分析，資料分發等。

使用者行為，輕度聚合

主要對ODS/DWD層資料做一些輕度的總和。

1）公共維度層（DIM，Dimension）

DIM：這一層比較單純，舉個例子就明白，例如國家代碼和國家名、地理位置、中文名、國旗圖片等資訊就存在DIM層中。

基於維度建模理念思想，建立整個企業的一致性維度。降低資料計算口徑和演算法不統一風險。

公共維度匯總層（DIM）主要由維度表（維度表）構成。維度是邏輯概念，是衡量和觀察業務的角度。維表是根據維度及其屬性將資料平台上建構的表物理化的表，採用寬表設計的原則。因此，建構公共維度匯總層（DIM）首先需要定義維度。

高基數維度資料：一般是使用者資料表、商品資料表類似的資料表。數據量可能是千萬級或上億級。

低基數維度資料：一般是配置表，例如枚舉值對應的中文意義，或是日期維表。資料量可能是個位數或幾千幾萬。

設計維表：

#完成維度定義後，您就可以對維度進行補充，進而產生維表了。維表的設計需要注意：

建議維表單表資訊不超過1000萬個。

維表與其他表格進行Join時，建議您使用Map Join

避免過於頻繁的更新維表的資料。緩慢變更維：拉鍊表

公有維度匯總層（DIM）維表規格

公共維度匯總層（DIM）維度表命名規格：dim_{業務板塊名稱/pub}_{維度定義}[_{自訂命名標籤}]，所謂pub是與特定業務板塊無關或各業務板塊都可公用的維度，如時間維度。

#

例如：公共區域維表dim_pub_area 商品維表dim_asale_itm

事實表中一筆記錄所表達的業務細節程度被稱為粒度。通常粒度可以透過兩種方式來表達：一種是維度屬性組合所表示的細節程度，另一種是所表示的特定業務意義。通透！資料倉儲領域常見建模方法及實例演示。

建模方式及原則

#需要建立維度模型，一般採用星型模型，呈現的狀態一般為星座模型（由多個事實表組合，維表是公共的，可被多個事實表共享）；

#為支援資料重跑可額外增加資料業務日期字段，可按日進行分錶，以增量ODS層資料和前一天DWD相關表進行merge處理？

粒度是一行訊息代表一次行為，例如一次下單。

維度建模步驟

#選擇業務流程：在業務系統中，挑選有興趣的業務線，例如下單業務，支付業務，退款業務，物流業務，一條業務線對應一張事實表。如果是中小公司，盡量把所有業務流程都選擇。 DWD如果是大公司（1000多張表），選擇和需求相關的業務線。

宣告粒度：資料粒度指資料倉儲的資料中保存資料的細化程度或綜合程度的等級。聲明粒度意味著精確定義事實表中的一行資料表示什麼，應該盡可能選擇最小粒度，以此來應各種各樣的需求。典型的粒度聲明如下：訂單當中的每個商品項目作為下單事實表中的一行，粒度為每次。每週的訂單次數作為一行，粒度為每週。每月的訂單次數作為一行，粒度為每月。如果在DWD層粒度就是每週或每月，那麼後續就沒有辦法統計細粒度的指標了。所以建議採用最小粒度。

確定維度：維度的主要作用是描述業務是事實，主要表示的是「誰，何處，何時」等資訊。確定維度的原則是：後續需求中是否要分析相關維度的指標。例如，需要統計，什麼時間的訂單多，哪個地區下的訂單多，哪個使用者下的訂單多。需要確定的維度就包括：時間維度、地區維度、使用者維度。維度表：需要根據維度建模中的星型模型原則進行維度退化。

確定事實：此處的「事實」一詞，指的是業務中的度量值（次數、個數、件數、金額，可以進行累加），例如訂單金額、下單次數等。在DWD層，以業務流程為建模驅動，基於每個特定業務流程的特點，建立最細粒度的明細層事實表。事實表可做適當的寬表化處理。

注意：DWD層是以業務流程為驅動。 DWS層、DWT層和ADS層都是以需求為驅動，和維度建模已經沒有關係了。 DWS和DWT都是建寬表，依照主題去建表。主題相當於觀察問題的角度。對應著維度表。

關於主題：

#資料倉儲中的資料是面向主題組織的，主題是在較高層次上將企業資訊系統中的資料進行綜合、歸類和分析利用的一個抽象概念，每一個主題基本上對應一個宏觀的分析領域。如財務分析就是一個分析領域，因此這個資料倉儲應用的主題就為「財務分析」。

###關於主題域：##################主題域通常是個比較緊密的資料主題的集合。可以根據業務的關注點，將這些資料主題劃分到不同的主題域(也說是對某個主題進行分析後確定的主題的邊界)################ ##關於主題域的分割：#########

主題域的確定必須由最終用戶（業務）和資料倉儲的設計人員共同完成的， 而在劃分主題域時，大家的切入點不同可能會造成一些爭論、重構等的現象，考慮的點可能會是下方的某些方面：

• 按業務或業務流程劃分：例如一個靠銷售廣告位置的入口網站主題域可能會有廣告域，客戶域等，而廣告域可能會有廣告的庫存，銷售分析、內部投放分析等主題；
• 依照需求方劃分：例如需求方為財務部，就可以設定對應的財務主題域，而財務主題域裡面可能就會有員工薪資分析，投資回報比分析等主題；
• #依照功能或應用程式劃分：例如微信中的朋友圈資料域、群組聊天資料域等，而朋友圈資料域可能會有使用者動態資訊主題、廣告主題等等；
• 依照部門劃分：例如可能會有營運領域、科技領域等，營運領域可能會有薪資支出分析、活動宣傳效果分析等主題；

總而言之，切入的出發點邏輯不一樣，就可以存在不同的劃分邏輯。在建造過程中可採用迭代方式，不糾結於一次完成所有主題的抽象，可先從明確定義的主題開始，後續逐步歸納總結成自身產業的標準模型。

主題：當事人、行銷、財務、合約協議、機構、地址、管道、 產品、

金融業務主題有哪些：可分為四個主題：

• #使用者主題（使用者年齡、性別、收貨地址、電話、省等）
• 交易主題（訂單資料、帳單資料等）
• 風控主題（使用者的風控等級，第三方徵信資料）
• 行銷主題（行銷活動名單，活動設定資訊等）

2）DWD（data warehouse detail）資料明細層，明細粒度事實圖層

DWD是業務層與資料倉儲的隔離層， 這一層主要解決一些資料品質問題和資料的完整度問題。

明細表用於儲存ODS層原始表轉換過來的明細數據，DWD 層的數據應該是一致的、準確的、乾淨的數據，即對來源系統資料ODS層資料進行清洗（去除空值，髒數據，超過極限範圍的數據，行式存儲改為列存儲，改壓縮格式）、規範化、維度退化、脫敏等操作。例如用戶的資料資訊來自於許多不同表，而且經常出現延遲丟失數據等問題，為了方便各個使用方更好的使用數據，我們可以在這一層做一個屏蔽。這一層也包含統一的維度資料。

明細粒度事實層（DWD）：以業務流程作為建模驅動，基於每個特定的業務流程特點，建立最細粒度的明細層事實表。可以結合企業的資料使用特點，將明細事實表的某些重要維度屬性欄位做適當冗餘，即寬表化處理。明細粒度事實圖層的表通常也稱為邏輯事實表。

負責數據的最細粒度的數據，在DWD層基礎上，進行輕度匯總，結合常用維度（時間，地點，組織層級，用戶，商品等）

該層一般保持和ODS層一樣的資料粒度，並且提供一定的資料品質保證，在ODS的基礎上對資料進行加工處理，提供更乾淨的數據。同時，為了提高資料明細層的易用性，該層會採用一些維度退化手法，當一個維度沒有資料倉儲所需的任何資料時，就可以退化維度，將維度退化至事實表中，減少事實表和維表的關聯。

#

例如：

訂單id,這種量級很大的維度，沒必要用一張維度表來進行存儲，而我們一般在進行數據分析時訂單id又非常重要，所以我們將訂單id冗餘在事實表中，這種維度就是退化維度。

這一層的資料一般是遵循資料庫第三範式或維度建模，其資料粒度通常和 ODS 的粒度相同。在 PDW 層會保存 BI 系統中所有的歷史數據，例如保存10年的數據。

資料在裝入本層前需要做以下工作：去噪、去重、提髒、業務提取、單位統一、砍字段、業務判別。

清洗過的資料種類：

• 不完整資料
• 錯誤資料
• #重複的資料

資料清洗的任務是過濾那些不符合要求的數據，將過濾的結果交給業務主管部門，確認是否過濾掉還是由業務單位修正之後再進行抽取。

DWD層做了哪些事？

①資料清洗過濾器

#移除廢棄欄位,去除格式錯誤的資訊

移除遺失了關鍵欄位的資訊

#過濾核心欄位無意義的數據，例如訂單表中訂單id為null，支付表中支付id為空

#對手機號碼、身分證字號等敏感資料脫敏

去除不含時間資訊的資料(這個看公司具體業務,但一般資料中都會帶上時間戳,這樣方便後續處理時,進行時間維度上信息分析處理與提取)

有些公司還會在這一層將資料打平,不過這具體要看業務需求.這是因為kylin適合處理展平後資料,不適合處理巢狀的表格資料資訊

#有些公司也會將資料session切割,這個一般是app的日誌資料,其他業務場景不一定適合.這是因為app有進入後台模式,例如用戶上午打開app用了10分鐘,然後app切入後台,晚上再打開,這時候session還是一個,實際上應該做切割才對.(也有公司會記錄app進入後台,再度進入前台的記錄,這樣來做session切割)

#②資料映射,轉換

將GPS經緯度轉換為省市區詳細地址。業界常見GPS快速查詢一般將地理位置知識庫使用geohash映射,然後將需要比對的GPS轉換為geohash後跟知識庫中geohash比對,查找出地理位置資訊當然,也有公司使用open api,如高德地圖,百度地圖的api進行GPS和地理位置資訊映射,但這個達到一定次數需要花錢,所以大家都懂的

會將IP位址也轉換為省市區詳細地址。這個有很多快速查找庫,不過基本原理都是二分查找,因為ip地址可以轉換為長整數.典型的如ip2region庫

將時間轉換為年,月,日甚至週,季度維度資訊

資料標準化,因為大數據處理的資料可能來資源公司不同部門,不同專案,不同客戶端,這時候可能相同業務資料欄位,資料型別,空值等都不一樣,這時候需要在DWD層做抹平.否則後續處理使用時,會造成很大的困擾

#如boolean,有使用0 1標識,也有使用true false標識的

如字串空值,有使用"",也有使用null,的,統一為null即可

如日期格式,這種就差異性更大,需要根據實際業務資料決定,不過一般都是格式化為YYYY-MM-dd HH:mm:ss 這類標準格式

維度退化：將業務資料傳過來的資料表進行維度退化與降維。 （商品一級二級三級、省市縣、年月日）訂單id冗餘在事實表

#清洗掉多少資料算合理：1萬條資料清洗掉1條。

合理表數：一萬張表變成三千張表，三千張表變成一千張表

#明細粒度事實表設計原則：

• #一個明細粒度事實表僅和一個維度關聯。
• 盡可能包含所有與業務流程相關的事實 。
• 只選擇與業務流程相關的事實。
• 分解不可加性事實為可加的元件。
• 在選擇維度和事實之前必須先宣告粒度。
• 在同一個事實表中不能有多種不同粒度的事實。
• 事實的單位要一致。粒度
• 謹慎處理Null值。
• 使用退化維度來提高事實表的易用性。

方案

#討論方案：資料的合成方式為：

全量：每天把明細層的前天全量資料和昨天新資料合成一個新的資料表，覆寫舊表。同時使用歷史鏡像，按週/按月/按年儲存一個歷史鏡像到新表。

日誌儲存方式：直接資料使用impala外表，parquet檔案格式，建議使用內表，下面幾層都是從impala產生的數據，建議都用內表靜態/動態分區。

表schema：一般會按天建立分割區，沒有時間概念的依特定業務選擇分割區欄位。 partitioned by 一般都是按照天進行存放。

庫與表命名。庫名：dwd,表名：初步考慮格式為dwd日期業務表名,待定。

舊資料更新方式：直接覆寫

#明細粒度事實圖層（DWD）規格

命名規格為：dwd_{業務板塊/pub}_{資料域縮寫}_{業務流程縮寫}[_{自訂表格命名標籤縮寫}] _{單分區增量全量標識}，pub表示資料包含多個業務板塊的資料。單分區增量全量標識通常為：i表示增量，f表示全量。

例如：dwd_asale_trd_ordcrt_trip_di（A電商公司航空機票訂單下單事實表，日刷新增量） dwd_asale_itm_item_df（A電商商品快照事實表，日刷新全量）。

本教學中，DWD層主要由三個表格構成：

• 交易商品資訊事實表：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;

#

3）DWS（data warehouse service）資料服務層，匯總層寬表

基於DWD 明細資料層，我們會依照一些分析場景、分析實體等去組織我們的數據，組織成一些分主題的匯總資料層DWS。

明細粒度==> 匯總粒度

DWS層（資料匯總層）寬表，主題導向的匯總，維度相對來說比較少，DWS是根據DWD層基礎資料按各個維度ID進行粗粒度匯總聚​​合，如按交易來源，交易類型進行匯合。整合匯總成分析某一個主題域的服務數據，通常是寬表。

以DWD為基礎，以天為單位進行輕度匯總。統計各主題物件的當天行為，（例如，購買行為，統計商品重購率）。

該層資料表會相對比較少，大多都是寬表(一張表會涵蓋比較多的業務內容，表中的欄位較多)。依照主題劃分，如訂單、使用者等，產生欄位比較多的寬表，用於提供後續的業務查詢，OLAP分析，資料分發等。

融合多個中間層數據，基於主題形成事實表，例如使用者事實表、管道事實表、終端事實表、資產事實表等等，事實表一般是寬表，在本層上實現企業級資料的一致性。

先分割業務主題，將主題分割成銷售網域、庫存網域、客戶網域、採購網域等，其次就是決定每個主題網域的事實表和維度表。通常根據業務需求，劃分成流量、訂單、使用者等，產生欄位比較多的寬表，用於提供後續的業務查詢，OLAP分析，資料分發等。

最近一天某個類目（例如：廚具）商品在各省的銷售總額、該類目Top10銷售額商品名稱、各省用戶購買力分佈。因此，我們可以以最終交易成功的商品、類目、買家等角度對最近一天的資料進行匯總。

例如使用者每個時段在不同登入ip購買的商品數等。這裡做一層輕度的總結會讓計算更加的高效，在此基礎上如果計算僅7天、30天、90天的行為的話會快很多。我們希望80%的業務都能透過我們的DWS層計算，而不是ODS。

DWS層做了哪些事？

dws將dwd層的資料依主題進行匯總，並依照主題放到一個表中，

例如用戶主題下會將用戶註冊資訊、用戶收貨地址、用戶的徵信資料放到同一張表中，而這些在dwd層是對應多張表的,按照業務劃分，如流量、訂單、使用者等，生成欄位比較多的寬表

主題建模,圍繞某一個業務主題進行資料建模,將相關資料抽離提取出來.

如：

• #將流量會話依照天,月進行聚合
• 將每日新用戶進行聚合
• 將每日活躍用戶進行聚合
• 維度建模,其實也差不多,不過是根據業務需要,提前將後續資料查詢處理需要的維度資料抽離處理出來,方便後續查詢使用.
• 如將營運位元維度資料聚合
• 將頻道拉新維度資料聚合

①DWS層每個主題1-3張寬表（處理100-200個指標70%以上的需求）

具體寬表名稱：使用者行為寬表，使用者購買商品明細行為寬表，商品寬表， 物流寬表、 售後等。

②哪個寬表最寬？大概有多少個字段？

最寬的是使用者行為寬表。大概有60-200個欄位

③具體使用者行為寬表欄位名稱

# #評論、打賞、收藏、追蹤--商品、追蹤--人、按讚、分享、好價爆料、文章發布、活躍、簽到、補簽卡、幸運屋、禮品、金幣、電商點擊、gmv

④分析的指標

#日活、月活、週活、留存、留任率、新增（日、週、年）、轉換率、流失、回流、七天內連續3 天登入（按讚、收藏、評估、購買、加購、下單、活動）、連續3 週（月）登入、GMV（成交金額，下單）、重購率、重購率排行、按讚、留言、收藏、領優惠價人數、使用優惠價、沉默、值不值得買、退款人數、退款率topn 熱門商品

• 活躍

日活： 100 萬；月活：是日活的2-3 倍300 萬

總註冊的用戶多少？ 1000 萬-3000 萬之間

• GMV，哪個商品賣的最好？每天下單量多少？

GMV：每天10 萬訂單（50 – 100 元） 500 萬-1000 萬

#100萬的日活每天大概有10萬人購買，平均每人消費100元，一天的GMV在1000萬

##10 %-20% 100 萬-200 萬

• #複購率

某日常商品再購;（手紙、面膜、牙膏）10%-20%

#電腦、顯示器、手錶1%

• 轉換率

#商品詳情=》 加購物車=》下單=》支付

5%-10% 60-70% 90%-95%

留存率

1/2/3、週留存、月留會

##搞活動：10-20%

#################### #####概念：又稱資料集市或寬表。依業務劃分，如流量、訂單、使用者等，產生欄位比較多的寬表，用於提供後續的業務查詢，OLAP分析，資料分發等。 ##################資料產生方式：由輕度總計層和明細層資料計算產生。 ##################日誌儲存方式：使用impala內表，parquet檔案格式。 ##################表schema：一般會按天建立分割區，沒有時間概念的依特定業務選擇分割區欄位。 ##################庫與表命名。庫名：dws, 表名：初步考慮格式為：dws日期業務表名,待定。 ##################舊資料更新方式：直接覆寫################## 公共匯總事實表規格## #######

公共汇总事实表命名规范：dws_{业务板块缩写/pub}_{数据域缩写}_{数据粒度缩写}[_{自定义表命名标签缩写}]_{统计时间周期范围缩写}。关于统计实际周期范围缩写，缺省情况下，离线计算应该包括最近一天（_1d），最近N天（_nd）和历史截至当天（_td）三个表。如果出现_nd的表字段过多需要拆分时，只允许以一个统计周期单元作为原子拆分。即一个统计周期拆分一个表，例如最近7天（_1w）拆分一个表。不允许拆分出来的一个表存储多个统计周期。

对于小时表（无论是天刷新还是小时刷新），都用_hh来表示。对于分钟表（无论是天刷新还是小时刷新），都用_mm来表示。

举例如下：

dws_asale_trd_byr_subpay_1d（买家粒度交易分阶段付款一日汇总事实表）

dws_asale_trd_byr_subpay_td（买家粒度分阶段付款截至当日汇总表）

dws_asale_trd_byr_cod_nd（买家粒度货到付款交易汇总事实表）

dws_asale_itm_slr_td（卖家粒度商品截至当日存量汇总表）

dws_asale_itm_slr_hh（卖家粒度商品小时汇总表）---维度为小时

dws_asale_itm_slr_mm（卖家粒度商品分钟汇总表）---维度为分钟

• 用户维度：用户主题

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 '地址',
--下单数、 商品数量， 金额汇总
login_count bigint comment '当日登录次数',
cart_count bigint comment '加入购物车次数',
order_count bigint comment '当日下单次数',
order_amount decimal(16,2) comment '当日下单金额',
payment_count bigint comment '当日支付次数',
payment_amount decimal(16,2) comment '当日支付金额',
confirm_paid_amt_sum_1d double comment '最近一天订单已经确认收货的金额总和'
order_detail_stats array<struct<sku_id:string,sku_num:bigint,order_count:bigint,order_amount:decimal(20,2)>> comment '下单明细统计'




) comment '每日购买行为'
partitioned by(`dt`
string)
stored as parquet
location '/warehouse/gmall/dws/dws_sale_detail_daycount/'
tblproperties("parquet.compression" = "lzo");

• 商品维度：商品主题

CREATE TABLE IF NOT EXISTS dws_asale_trd_itm_ord_1d
(
item_id BIGINT COMMENT '商品ID',
--商品信息，产品信息
item_title STRING COMMENT '商品名称',
cate_id BIGINT COMMENT '商品类目ID',
cate_name STRING COMMENT '商品类目名称',
--mord_prov STRING COMMENT '收货人省份',
--商品售出金额汇总
confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '商品粒度交易最近一天汇总事实表'
PARTITIONED BY (ds STRING COMMENT '分区字段YYYYMMDD')
LIFECYCLE 36000;

问：数据集市层是不是没地方放了，各个业务的数据集市表是应该在 dws 还是在 app?

答：这个问题不太好回答，我感觉主要就是明确一下数据集市层是干什么的，如果你的数据集市层放的就是一些可以供业务方使用的宽表，放在 app 层就行。如果你说的数据集市层是一个比较泛一点的概念，那么其实 dws、dwd、app 这些合起来都算是数据集市的内容。

03 应用层（ADS）applicationData Service应用数据服务

数据应用层（ADS，Application Data Store）：存放数据产品个性化的统计指标数据，报表数据。主要是提供给数据产品和数据分析使用的数据，通常根据业务需求，划分成流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。从数据粒度来说，这层的数据是汇总级的数据，也包括部分明细数据。从数据的时间跨度来说，通常是DW层的一部分，主要的目的是为了满足用户分析的需求，而从分析的角度来说，用户通常只需要分析近几年的即可。从数据的广度来说，仍然覆盖了所有业务数据。

在 DWS 之上，我们会面向应用场景去做一些更贴近应用的 APP 应用数据层，这些数据应该是高度汇总的，并且能够直接导入到我们的应用服务去使用。

应用层(ADS)：应用层主要是各个业务方或者部门基于DWD和DWS建立的数据集市(Data Market, DM)，一般来说应用层的数据来源于DW层，而且相对于DW层，应用层只包含部门或者业务方面自己关心的明细层和汇总层的数据。

该层主要是提供数据产品和数据分析使用的数据。一般就直接对接OLAP分析,或者业务层数据调用接口了

数据应用层APP：面向业务定制的应用数据主要提供给数据铲平和数据分析使用的数据，一般会放在ES，MYSQL，Oracle，Redis等系统供线上系统使用，也可以放在Hive 或者 Druid 中供数据分析和数据挖掘使用。

APP 层：为应用层，这层数据是完全为了满足具体的分析需求而构建的数据，也是星形或雪花结构的数据。如我们经常说的报表数据，或者说那种大宽表，一般就放在这里。包括前端报表、分析图表、KPI、仪表盘、OLAP、专题等分析，面向最终结果用户；

概念：应用层是根据业务需要，由前面三层数据统计而出的结果，可以直接提供查询展现，或导入至Mysql中使用。

数据生成方式：由明细层、轻度汇总层，数据集市层生成，一般要求数据主要来源于集市层。

日志存储方式：使用impala内表，parquet文件格式。

表schema：一般按天创建分区，没有时间概念的按具体业务选择分区字段。

库与表命名。库名：暂定ads，另外根据业务不同，不限定一定要一个库。

旧数据更新方式：直接覆盖。

ADS 层复购率统计

CREATE TABLE app_usr_interact( user_id string COMMENT '用户id',
nickname string COMMENT '用户昵称',
register_date string COMMENT '注册日期',
register_from string COMMENT '注册来源',
remark string COMMENT '细分渠道',
province string COMMENT '注册省份',
pl_cnt bigint COMMENT '评论次数',
ds_cnt bigint COMMENT '打赏次数',
sc_add bigint COMMENT '添加收藏',
sc_cancel bigint COMMENT '取消收藏',
gzg_add bigint COMMENT '关注商品',
gzg_cancel bigint COMMENT '取消关注商品',
gzp_add bigint COMMENT '关注人',
gzp_cancel bigint COMMENT '取消关注人',
buzhi_cnt bigint COMMENT '点不值次数',
zhi_cnt bigint COMMENT '点值次数',
zan_cnt bigint COMMENT '点赞次数',
share_cnts bigint COMMENT '分享次数',
bl_cnt bigint COMMENT '爆料数',
fb_cnt bigint COMMENT '好价发布数',
online_cnt bigint COMMENT '活跃次数',
checkin_cnt bigint COMMENT '签到次数',
fix_checkin bigint COMMENT '补签次数',
house_point bigint COMMENT '幸运屋金币抽奖次数',
house_gold bigint COMMENT '幸运屋积分抽奖次数',
pack_cnt bigint COMMENT '礼品兑换次数',
gold_add bigint COMMENT '获取金币',
gold_cancel bigint COMMENT '支出金币',
surplus_gold bigint COMMENT '剩余金币',
event bigint COMMENT '电商点击次数',
gmv_amount bigint COMMENT 'gmv',
gmv_sales bigint COMMENT '订单数'
)
PARTITIONED BY( dt string)
--stat_dt
date COMMENT '互动日期',

①如何分析用户活跃？

在启动日志中统计不同设备 id 出现次数。

②如何分析用户新增?

用活跃用户表 left join 用户新增表，用户新增表中 mid 为空的即为用户新增。

③如何分析用户 1 天留存？

留存用户=前一天新增 join 今天活跃

用户留存率=留存用户/前一天新增

④如何分析沉默用户？

(登录时间为 7 天前,且只出现过一次)

按照设备 id 对日活表分组，登录次数为 1，且是在一周前登录。

⑤如何分析本周回流用户？

本周活跃 left join 本周新增 left join 上周活跃，且本周新增 id 和上周活跃 id 都为 null。

⑥如何分析流失用户？

(登录时间为 7 天前)

按照设备 id 对日活表分组，且七天内没有登录过。

⑦如何分析最近连续 3 周活跃用户数？

按照设备 id 对周活进行分组，统计次数大于 3 次。

⑧如何分析最近七天內連續三天活躍用戶數？

• 查詢出最近7 天的活躍用戶，並對用戶活躍日期進行排名
• 計算使用者活躍日期與排名之間的差值
• #對同使用者及差值分組，統計差值個數
• 將差值相同個數大於等於3 的資料取出，然後去重，即為連續3 天及以上活躍的使用者

7 天連續收藏、按讚、購買、加購、付款、瀏覽、商品點擊、退貨

1 個月連續7 天

連續兩週

TMP：每一層的計算都會有很多臨時表，專屬一個DW TMP層來儲存我們資料倉儲的臨時表。

04 層次呼叫規格

• #禁止反向呼叫
• #ODS 只能被DWD 呼叫。
• DWD 可以被 DWS 和 ADS 呼叫。
• DWS 只能被 ADS 呼叫。
• 資料應用程式可以呼叫 DWD、DWS、ADS，但建議優先使用總計度高的資料。
• ODS->DWD->DWS>ADS
• ODS->DWD- >ADS

#

以上是從ODS到ADS，詳解數倉分層！的詳細內容。更多資訊請關注PHP中文網其他相關文章！