一、為什么要對數據倉庫分層
只有數據模型將數據有序的組織和存儲起來之后，大數據才能得到高性能、低成本、高效率、高質量的使用。
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
