從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 tableif 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 parquetlocation '/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 層復購率統計

①如何分析使用者活躍?

在啟動日誌中統計不同裝置 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