一、數(shù)倉建模的意義，為什么要對數(shù)據(jù)倉庫分層？

只有數(shù)據(jù)模型將數(shù)據(jù)有序的組織和存儲(chǔ)起來之后，大數(shù)據(jù)才能得到高性能、低成本、高效率、高質(zhì)量的使用。

1、分層意義

1）清晰數(shù)據(jù)結(jié)構(gòu)：每一個(gè)數(shù)據(jù)分層都有它的作用域，這樣我們在使用表的時(shí)候能更方便地定位和理解。

數(shù)據(jù)關(guān)系條理化：源系統(tǒng)間存在復(fù)雜的數(shù)據(jù)關(guān)系，比如客戶信息同時(shí)存在于核心系統(tǒng)、信貸系統(tǒng)、理財(cái)系統(tǒng)、資金系統(tǒng)，取數(shù)時(shí)該如何決策呢？數(shù)據(jù)倉庫會(huì)對相同主題的數(shù)據(jù)進(jìn)行統(tǒng)一建模，把復(fù)雜的數(shù)據(jù)關(guān)系梳理成條理清晰的數(shù)據(jù)模型，使用時(shí)就可避免上述問題了。

2）數(shù)據(jù)血緣追蹤：簡單來講可以這樣理解，我們最終給業(yè)務(wù)誠信的是一能直接使用的張業(yè)務(wù)表，但是它的來源有很多，如果有一張來源表出問題了，我們希望能夠快速準(zhǔn)確地定位到問題，并清楚它的危害范圍。

3）數(shù)據(jù)復(fù)用，減少重復(fù)開發(fā)：規(guī)范數(shù)據(jù)分層，開發(fā)一些通用的中間層數(shù)據(jù)，能夠減少極大的重復(fù)計(jì)算。數(shù)據(jù)的逐層加工原則，下層包含了上層數(shù)據(jù)加工所需要的全量數(shù)據(jù)，這樣的加工方式避免了每個(gè)數(shù)據(jù)開發(fā)人員都重新從源系統(tǒng)抽取數(shù)據(jù)進(jìn)行加工。通過匯總層的引人，避免了下游用戶邏輯的重復(fù)計(jì)算， 節(jié)省了用戶的開發(fā)時(shí)間和精力，同時(shí)也節(jié)省了計(jì)算和存儲(chǔ)。極大地減少不必要的數(shù)據(jù)冗余，也能實(shí)現(xiàn)計(jì)算結(jié)果復(fù)用，極大地降低存儲(chǔ)和計(jì)算成本。

4）把復(fù)雜問題簡單化。講一個(gè)復(fù)雜的任務(wù)分解成多個(gè)步驟來完成，每一層只處理單一的步驟，比較簡單和容易理解。而且便于維護(hù)數(shù)據(jù)的準(zhǔn)確性，當(dāng)數(shù)據(jù)出現(xiàn)問題之后，可以不用修復(fù)所有的數(shù)據(jù)，只需要從有問題的步驟開始修復(fù)。

5）屏蔽原始數(shù)據(jù)的(影響) ，屏蔽業(yè)務(wù)的影響。業(yè)務(wù)或系統(tǒng)發(fā)生變化時(shí)，不必改一次業(yè)務(wù)就需要重新接入數(shù)據(jù)。提高數(shù)據(jù)穩(wěn)定性和連續(xù)性。

屏蔽源頭業(yè)務(wù)系統(tǒng)的復(fù)雜性：源頭系統(tǒng)可能極為繁雜，而且表命名、字段命名 、字段含義等可能五花八門，通過 DW 層來規(guī)范和屏蔽所有這些復(fù)雜性，保證下游數(shù)據(jù)用戶使用數(shù)據(jù)的便捷和規(guī)范。如果源頭系統(tǒng)業(yè)務(wù)發(fā)生變更，相關(guān)的變更由 DW 層來處理，對下游用戶透明，無須改動(dòng)下游用戶的代碼和邏輯。

數(shù)據(jù)倉庫的可維護(hù)性：分層的設(shè)計(jì)使得某一層的問題只在該層得到解決，無須更改下一層的代碼和邏輯。

大數(shù)據(jù)系統(tǒng)需要數(shù)據(jù)模型方法來幫助更好地組織和存儲(chǔ)數(shù)據(jù)，以便在性能、成本、效率和質(zhì)量之間取得最佳平衡！

2、數(shù)據(jù)倉庫（ETL）的四個(gè)操作

ETL(extractiontransformation loading)負(fù)責(zé)將分散的、異構(gòu)數(shù)據(jù)源中的數(shù)據(jù)抽取到臨時(shí)中間層后進(jìn)行清洗、轉(zhuǎn)換、集成，最后加載到數(shù)據(jù)倉庫或數(shù)據(jù)集市中。ETL 是實(shí)施數(shù)據(jù)倉庫的核心和靈魂，ETL規(guī)則的設(shè)計(jì)和實(shí)施約占整個(gè)數(shù)據(jù)倉庫搭建工作量的 60%～80%。

1）數(shù)據(jù)抽取(extraction)包括初始化數(shù)據(jù)裝載和數(shù)據(jù)刷新：初始化數(shù)據(jù)裝載主要關(guān)注的是如何建立維表、事實(shí)表，并把相應(yīng)的數(shù)據(jù)放到這些數(shù)據(jù)表中；而數(shù)據(jù)刷新關(guān)注的是當(dāng)源數(shù)據(jù)發(fā)生變化時(shí)如何對數(shù)據(jù)倉庫中的相應(yīng)數(shù)據(jù)進(jìn)行追加和更新等維護(hù)(比如可以創(chuàng)建定時(shí)任務(wù)，或者觸發(fā)器的形式進(jìn)行數(shù)據(jù)的定時(shí)刷新)。

2）數(shù)據(jù)清洗主要是針對源數(shù)據(jù)庫中出現(xiàn)的二義性、重復(fù)、不完整、違反業(yè)務(wù)或邏輯規(guī)則等問題的數(shù)據(jù)進(jìn)行統(tǒng)一的處理。即清洗掉不符合業(yè)務(wù)或者沒用的的數(shù)據(jù)。比如通過編寫hive或者M(jìn)R清洗字段中長度不符合要求的數(shù)據(jù)。

3）數(shù)據(jù)轉(zhuǎn)換(transformation)主要是為了將數(shù)據(jù)清洗后的數(shù)據(jù)轉(zhuǎn)換成數(shù)據(jù)倉庫所需要的數(shù)據(jù)：來源于不同源系統(tǒng)的同一數(shù)據(jù)字段的數(shù)據(jù)字典或者數(shù)據(jù)格式可能不一樣(比如A表中叫id,B表中叫ids)，在數(shù)據(jù)倉庫中需要給它們提供統(tǒng)一的數(shù)據(jù)字典和格式，對數(shù)據(jù)內(nèi)容進(jìn)行歸一化；另一方面，數(shù)據(jù)倉庫所需要的某些字段的內(nèi)容可能是源系統(tǒng)所不具備的，而是需要根據(jù)源系統(tǒng)中多個(gè)字段的內(nèi)容共同確定。

4）數(shù)據(jù)加載（loading）是將最后上面處理完的數(shù)據(jù)導(dǎo)入到對應(yīng)的存儲(chǔ)空間里（hbase，mysql等）以方便給數(shù)據(jù)集市提供，進(jìn)而可視化。

一般大公司為了數(shù)據(jù)安全和操作方便，都是自己封裝的數(shù)據(jù)平臺(tái)和任務(wù)調(diào)度平臺(tái)，底層封裝了大數(shù)據(jù)集群比如hadoop集群，spark集群，sqoop,hive,zookeepr,hbase等只提供web界面，并且對于不同員工加以不同權(quán)限，然后對集群進(jìn)行不同的操作和調(diào)用。以數(shù)據(jù)倉庫為例，將數(shù)據(jù)倉庫分為邏輯上的幾個(gè)層次。這樣對于不同層次的數(shù)據(jù)操作，創(chuàng)建不同層次的任務(wù)，可以放到不同層次的任務(wù)流中進(jìn)行執(zhí)行（大公司一個(gè)集群通常每天的定時(shí)任務(wù)有幾千個(gè)等待執(zhí)行，甚至上萬個(gè)，所以劃分不同層次的任務(wù)流，不同層次的任務(wù)放到對應(yīng)的任務(wù)流中進(jìn)行執(zhí)行，會(huì)更加方便管理和維護(hù)）。

3、分層的誤區(qū)

數(shù)倉層內(nèi)部的劃分不是為了分層而分層，分層是為了解決 ETL 任務(wù)及工作流的組織、數(shù)據(jù)的流向、讀寫權(quán)限的控制、不同需求的滿足等各類問題。

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

所以數(shù)據(jù)分層這塊一般來說三層是最基礎(chǔ)的，至于DW層如何進(jìn)行切分，是根據(jù)具體的業(yè)務(wù)需求和公司場景自己去定義。

二、技術(shù)架構(gòu)

數(shù)據(jù)中臺(tái)包含的內(nèi)容很多，對應(yīng)到具體工作中的話，它可以包含下面的這些內(nèi)容：

• 系統(tǒng)架構(gòu)：以Hadoop、Spark等組件為中心的架構(gòu)體系

• 數(shù)據(jù)架構(gòu)：頂層設(shè)計(jì)，主題域劃分，分層設(shè)計(jì)，ODS-DW-ADS

• 數(shù)據(jù)建模：維度建模，業(yè)務(wù)過程-確定粒度-維度-事實(shí)表

• 數(shù)據(jù)管理：資產(chǎn)管理，元數(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ù)門戶、機(jī)器學(xué)習(xí)數(shù)據(jù)挖掘、數(shù)據(jù)查詢、分析、報(bào)表系統(tǒng)、可視化系統(tǒng)、數(shù)據(jù)交換分享下載

三、數(shù)倉分層架構(gòu)

數(shù)據(jù)倉庫標(biāo)準(zhǔn)上可以分為四層。但是注意這種劃分和命名不是唯一的，一般數(shù)倉都是四層，但是不同公司可能叫法不同。但是核心的理念都是從四層數(shù)據(jù)模型而來。

1、貼源層（ODS, Operational Data Store）

數(shù)據(jù)引入層（ODS，Operational Data Store，又稱數(shù)據(jù)基礎(chǔ)層）：將原始數(shù)據(jù)幾乎無處理地存放在數(shù)據(jù)倉庫系統(tǒng)中，結(jié)構(gòu)上與源系統(tǒng)基本保持一致，是數(shù)據(jù)倉庫的數(shù)據(jù)準(zhǔn)備區(qū)。這一層的主要職責(zé)是將基礎(chǔ)數(shù)據(jù)同步、存儲(chǔ)。

一般來說 ODS 層的數(shù)據(jù)和源系統(tǒng)的數(shù)據(jù)是同構(gòu)的，主要目的是簡化后續(xù)數(shù)據(jù)加工處理的工作。從數(shù)據(jù)粒度上來說 ODS 層的數(shù)據(jù)粒度是細(xì)的。ODS 層的表通常包括兩類，一個(gè)用于存儲(chǔ)當(dāng)前需要加載的數(shù)據(jù)，一個(gè)用于存儲(chǔ)處理完后的歷史數(shù)據(jù)。歷史數(shù)據(jù)一般保存 3-6 個(gè)月后需要清除，以節(jié)省空間。但不同的項(xiàng)目要區(qū)別對待，如果源系統(tǒng)的數(shù)據(jù)量不大，可以保留更長的時(shí)間，甚至全量保存。

注意：在這層，理應(yīng)不是簡單的數(shù)據(jù)接入，而是要考慮一定的數(shù)據(jù)清洗，比如異常字段的處理、字段命名規(guī)范化、時(shí)間字段的統(tǒng)一等，一般這些很容易會(huì)被忽略，但是卻至關(guān)重要。特別是后期我們做各種特征自動(dòng)生成的時(shí)候，會(huì)十分有用。

注意：有的公司ODS層不會(huì)做太多數(shù)據(jù)過濾處理,會(huì)放到DWD層來處理。有的公司會(huì)在一開始時(shí)就在ODS層做數(shù)據(jù)相對精細(xì)化的過濾.這個(gè)并沒有明確規(guī)定,看每個(gè)公司自己的想法和技術(shù)規(guī)范。

一般企業(yè)開發(fā)時(shí),都會(huì)對原始數(shù)據(jù)存入到ODS時(shí),做一些最基本的處理。

數(shù)據(jù)來源區(qū)分

數(shù)據(jù)按照時(shí)間分區(qū)存儲(chǔ),一般是按照天,也有公司使用年、月、日三級分區(qū)做存儲(chǔ)的。

進(jìn)行最基本的數(shù)據(jù)處理,如格式錯(cuò)誤的丟棄,關(guān)鍵信息丟失的過濾掉等等。

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

• 離線方面：每日定時(shí)任務(wù)型：跑批任務(wù)，業(yè)務(wù)庫，比如我們典型的日計(jì)算任務(wù)，這里經(jīng)常會(huì)使用 Sqoop 來抽取，比如我們每天定時(shí)抽取一次。每天凌晨算前一天的數(shù)據(jù)，早上起來看報(bào)表。這種任務(wù)經(jīng)常使用 Hive、Spark 來計(jì)算，最終結(jié)果寫入 Hive、Hbase、Mysql、Es 或者 Redis 中。

• 實(shí)時(shí)數(shù)據(jù)：日志埋點(diǎn)數(shù)據(jù)或者業(yè)務(wù)庫，這部分主要是各種實(shí)時(shí)的系統(tǒng)使用，比如我們的實(shí)時(shí)推薦、實(shí)時(shí)用戶畫像，一般我們會(huì)用 Spark Streaming、Flink 來計(jì)算，最后會(huì)落入 Es、Hbase 或者 Redis 中。數(shù)據(jù)源是業(yè)務(wù)數(shù)據(jù)庫，可以考慮用 Canal 監(jiān)聽 Mysql 的 Binlog，實(shí)時(shí)接入即可，然后也是收集到消息隊(duì)列中，最終再由 Camus 拉取到 HDFS。

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

• 數(shù)據(jù)源是業(yè)務(wù)數(shù)據(jù)庫，公司所有的系統(tǒng)產(chǎn)生的數(shù)據(jù)

• 是通過在客戶端埋點(diǎn)上報(bào)，收集用戶的行為日志，以及一些后端日志的日志類型數(shù)據(jù)源。對于埋點(diǎn)行為日志來說，一般會(huì)經(jīng)過一個(gè)這樣的流程，首先數(shù)據(jù)會(huì)上報(bào)到 Nginx 然后經(jīng)過 Flume 收集，然后存儲(chǔ)到 Kafka 這樣的消息隊(duì)列，然后再由實(shí)時(shí)或者離線的一些拉取的任務(wù)，拉取到我們的離線數(shù)據(jù)倉庫 HDFS

• 外部數(shù)據(jù)（包括合作數(shù)據(jù)以及爬蟲獲得的數(shù)據(jù)），將所采集的數(shù)據(jù)匯總到一起

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

實(shí)際應(yīng)用中，可以選擇采用增量、全量存儲(chǔ)或拉鏈存儲(chǔ)的方式。

• 增量存儲(chǔ)

為了滿足歷史數(shù)據(jù)分析需求，您可以在ODS層表中添加時(shí)間維度作為分區(qū)字段。以天為單位的增量存儲(chǔ)，以業(yè)務(wù)日期作為分區(qū)，每個(gè)分區(qū)存放日增量的業(yè)務(wù)數(shù)據(jù)。

舉例如下：

1月1日，用戶A訪問了A公司電商店鋪B，A公司電商日志產(chǎn)生一條記錄t1。1月2日，用戶A又訪問了A公司電商店鋪C，A公司電商日志產(chǎn)生一條記錄t2。

采用增量存儲(chǔ)方式，t1將存儲(chǔ)在1月1日這個(gè)分區(qū)中，t2將存儲(chǔ)在1月2日這個(gè)分區(qū)中。

1月1日，用戶A在A公司電商網(wǎng)購買了B商品，交易日志將生成一條記錄t1。1月2日，用戶A又將B商品退貨了，交易日志將更新t1記錄。

采用增量存儲(chǔ)方式，初始購買的t1記錄將存儲(chǔ)在1月1日這個(gè)分區(qū)中，更新后的t1將存儲(chǔ)在1月2日這個(gè)分區(qū)中。

交易、日志等事務(wù)性較強(qiáng)的ODS表適合增量存儲(chǔ)方式。這類表數(shù)據(jù)量較大，采用全量存儲(chǔ)的方式存儲(chǔ)成本壓力大。此外，這類表的下游應(yīng)用對于歷史全量數(shù)據(jù)訪問的需求較?。ù祟愋枨罂赏ㄟ^數(shù)據(jù)倉庫后續(xù)匯總后得到）。例如，日志類ODS表沒有數(shù)據(jù)更新的業(yè)務(wù)過程，因此所有增量分區(qū)UNION在一起就是一份全量數(shù)據(jù)。

• 全量存儲(chǔ)

以天為單位的全量存儲(chǔ)，以業(yè)務(wù)日期作為分區(qū)，每個(gè)分區(qū)存放截止到業(yè)務(wù)日期為止的全量業(yè)務(wù)數(shù)據(jù)。

例如，1月1日，賣家A在A公司電商網(wǎng)發(fā)布了B、C兩個(gè)商品，前端商品表將生成兩條記錄t1、t2。1月2日，賣家A將B商品下架了，同時(shí)又發(fā)布了商品D，前端商品表將更新記錄t1，同時(shí)新生成記錄t3。采用全量存儲(chǔ)方式， 在1月1日這個(gè)分區(qū)中存儲(chǔ)t1和t2兩條記錄，在1月2日這個(gè)分區(qū)中存儲(chǔ)更新后的t1以及t2、t3記錄。

對于小數(shù)據(jù)量的緩慢變化維度數(shù)據(jù)，例如商品類目，可直接使用全量存儲(chǔ)。

• 拉鏈存儲(chǔ)

拉鏈存儲(chǔ)通過新增兩個(gè)時(shí)間戳字段（start_dt和end_dt），將所有以天為粒度的變更數(shù)據(jù)都記錄下來，通常分區(qū)字段也是這兩個(gè)時(shí)間戳字段。

方案

概念：又稱為接口層(stage)，用于存儲(chǔ)每天的增量數(shù)據(jù)和變更數(shù)據(jù)

數(shù)據(jù)生成方式：直接從kafka接收源數(shù)據(jù)，需要業(yè)務(wù)表每天生成update,delete,inseret數(shù)據(jù)，只生成insert數(shù)據(jù)的業(yè)務(wù)表，數(shù)據(jù)直接入明細(xì)層。

討論方案：只把canal日志直接入緩沖層，如果其它有拉鏈數(shù)據(jù)的業(yè)務(wù)，也入緩沖層。

日志存儲(chǔ)方式：使用impala外表，parquet文件格式，方便需要MR處理的數(shù)據(jù)讀取。

日志刪除方式：長久存儲(chǔ)，可只存儲(chǔ)最近幾天的數(shù)據(jù)。討論方案：直接長久存儲(chǔ)。

表schema：一般按天創(chuàng)建分區(qū)，partitioned by 一般都是按照天進(jìn)行存放。

庫與表命名。庫名：ods,表名：初步考慮格式為ods日期業(yè)務(wù)表名,待定。

hive的外部表,對應(yīng)的是業(yè)務(wù)表。

hive外部表,存放數(shù)據(jù)的文件可以不是在hive的hdfs默認(rèn)的位置,并且hive對應(yīng)的表刪除時(shí),相應(yīng)的數(shù)據(jù)文件并不會(huì)被刪除.這樣對于企業(yè)開發(fā)來說,可以防止因?yàn)閯h除表的操作而把寶貴的數(shù)據(jù)刪除掉hive的業(yè)務(wù)表,則相反.數(shù)據(jù)文件存放在hive對應(yīng)的默認(rèn)位置,表刪除時(shí),對應(yīng)文件也會(huì)被刪除掉。

2、數(shù)倉層（DW,data warehouse）

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

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

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

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

明細(xì)數(shù)據(jù)層（DWD，Data Warehouse Detail）：以業(yè)務(wù)過程作為建模驅(qū)動(dòng)，基于每個(gè)具體的業(yè)務(wù)過程特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)事實(shí)表?？蓪⒛承┲匾獙傩宰侄巫鲞m當(dāng)冗余，也即寬表化處理。

匯總數(shù)據(jù)層（DWS，Data Warehouse Summary）：以分析的主題對象作為建模驅(qū)動(dòng)，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)表。以寬表化手段物理化模型，構(gòu)建命名規(guī)范、口徑一致的統(tǒng)計(jì)指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表、明細(xì)事實(shí)表。

主題域：面向業(yè)務(wù)過程，將業(yè)務(wù)活動(dòng)事件進(jìn)行抽象的集合，如下單、支付、退款都是業(yè)務(wù)過程。針對公共明細(xì)層（DWD）進(jìn)行主題劃分。

數(shù)據(jù)域：面向業(yè)務(wù)分析，將業(yè)務(wù)過程或者維度進(jìn)行抽象的集合。針對公共匯總層（DWS） 進(jìn)行數(shù)據(jù)域劃分。

DWD 層是以業(yè)務(wù)過程為驅(qū)動(dòng)。

DWS 層、DWT 層和 ADS 層都是以需求為驅(qū)動(dòng)。

DWD：data warehouse details 數(shù)據(jù)明細(xì)層。主要對ODS數(shù)據(jù)層做一些數(shù)據(jù)清洗和規(guī)范化的操作。

數(shù)據(jù)清洗：去除空值、臟數(shù)據(jù)、枚舉值轉(zhuǎn)換，超過極限范圍的。

DWB：data warehouse base 數(shù)據(jù)基礎(chǔ)層，存儲(chǔ)的是客觀數(shù)據(jù)，一般用作中間層，可以認(rèn)為是大量指標(biāo)的數(shù)據(jù)層。

DWS：data warehouse service 數(shù)據(jù)服務(wù)層，基于DWB上的基礎(chǔ)數(shù)據(jù)，整合匯總成分析某一個(gè)主題域的服務(wù)數(shù)據(jù)層，一般是寬表。用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。

用戶行為，輕度聚合

主要對ODS/DWD層數(shù)據(jù)做一些輕度的匯總。

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

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

基于維度建模理念思想，建立整個(gè)企業(yè)的一致性維度。降低數(shù)據(jù)計(jì)算口徑和算法不統(tǒng)一風(fēng)險(xiǎn)。

公共維度匯總層（DIM）主要由維度表（維表）構(gòu)成。維度是邏輯概念，是衡量和觀察業(yè)務(wù)的角度。維表是根據(jù)維度及其屬性將數(shù)據(jù)平臺(tái)上構(gòu)建的表物理化的表，采用寬表設(shè)計(jì)的原則。因此，構(gòu)建公共維度匯總層（DIM）首先需要定義維度。

高基數(shù)維度數(shù)據(jù)：一般是用戶資料表、商品資料表類似的資料表。數(shù)據(jù)量可能是千萬級或者上億級別。

低基數(shù)維度數(shù)據(jù)：一般是配置表，比如枚舉值對應(yīng)的中文含義，或者日期維表。數(shù)據(jù)量可能是個(gè)位數(shù)或者幾千幾萬。

設(shè)計(jì)維表：

完成維度定義后，您就可以對維度進(jìn)行補(bǔ)充，進(jìn)而生成維表了。維表的設(shè)計(jì)需要注意：

建議維表單表信息不超過1000萬條。

維表與其他表進(jìn)行Join時(shí)，建議您使用Map Join。

避免過于頻繁的更新維表的數(shù)據(jù)。緩慢變化維：拉鏈表

公共維度匯總層（DIM）維表規(guī)范

公共維度匯總層（DIM）維表命名規(guī)范：dim_{業(yè)務(wù)板塊名稱/pub}_{維度定義}[_{自定義命名標(biāo)簽}]，所謂pub是與具體業(yè)務(wù)板塊無關(guān)或各個(gè)業(yè)務(wù)板塊都可公用的維度，如時(shí)間維度。

例如：公共區(qū)域維表dim_pub_area 商品維表dim_asale_itm

事實(shí)表中一條記錄所表達(dá)的業(yè)務(wù)細(xì)節(jié)程度被稱為粒度。通常粒度可以通過兩種方式來表述：一種是維度屬性組合所表示的細(xì)節(jié)程度，一種是所表示的具體業(yè)務(wù)含義。通透！數(shù)據(jù)倉庫領(lǐng)域常見建模方法及實(shí)例演示。

建模方式及原則

需要構(gòu)建維度模型，一般采用星型模型，呈現(xiàn)的狀態(tài)一般為星座模型（由多個(gè)事實(shí)表組合，維表是公共的，可被多個(gè)事實(shí)表共享）；

為支持?jǐn)?shù)據(jù)重跑可額外增加數(shù)據(jù)業(yè)務(wù)日期字段，可按日進(jìn)行分表，用增量ODS層數(shù)據(jù)和前一天DWD相關(guān)表進(jìn)行merge處理？

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

維度建模步驟

選擇業(yè)務(wù)過程：在業(yè)務(wù)系統(tǒng)中，挑選感興趣的業(yè)務(wù)線，比如下單業(yè)務(wù)，支付業(yè)務(wù)，退款業(yè)務(wù)，物流業(yè)務(wù)，一條業(yè)務(wù)線對應(yīng)一張事實(shí)表。如果是中小公司，盡量把所有業(yè)務(wù)過程都選擇。DWD如果是大公司（1000多張表），選擇和需求相關(guān)的業(yè)務(wù)線。

聲明粒度：數(shù)據(jù)粒度指數(shù)據(jù)倉庫的數(shù)據(jù)中保存數(shù)據(jù)的細(xì)化程度或綜合程度的級別。聲明粒度意味著精確定義事實(shí)表中的一行數(shù)據(jù)表示什么，應(yīng)該盡可能選擇最小粒度，以此來應(yīng)各種各樣的需求。典型的粒度聲明如下：訂單當(dāng)中的每個(gè)商品項(xiàng)作為下單事實(shí)表中的一行，粒度為每次。每周的訂單次數(shù)作為一行，粒度為每周。每月的訂單次數(shù)作為一行，粒度為每月。如果在DWD層粒度就是每周或者每月，那么后續(xù)就沒有辦法統(tǒng)計(jì)細(xì)粒度的指標(biāo)了。所以建議采用最小粒度。

確定維度：維度的主要作用是描述業(yè)務(wù)是事實(shí)，主要表示的是“誰，何處，何時(shí)”等信息。確定維度的原則是：后續(xù)需求中是否要分析相關(guān)維度的指標(biāo)。例如，需要統(tǒng)計(jì)，什么時(shí)間下的訂單多，哪個(gè)地區(qū)下的訂單多，哪個(gè)用戶下的訂單多。需要確定的維度就包括：時(shí)間維度、地區(qū)維度、用戶維度。維度表：需要根據(jù)維度建模中的星型模型原則進(jìn)行維度退化。

確定事實(shí)：此處的“事實(shí)”一詞，指的是業(yè)務(wù)中的度量值（次數(shù)、個(gè)數(shù)、件數(shù)、金額，可以進(jìn)行累加），例如訂單金額、下單次數(shù)等。在DWD層，以業(yè)務(wù)過程為建模驅(qū)動(dòng)，基于每個(gè)具體業(yè)務(wù)過程的特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)層事實(shí)表。事實(shí)表可做適當(dāng)?shù)膶挶砘幚怼?/p>

注意：DWD層是以業(yè)務(wù)過程為驅(qū)動(dòng)。DWS層、DWT層和ADS層都是以需求為驅(qū)動(dòng)，和維度建模已經(jīng)沒有關(guān)系了。DWS和DWT都是建寬表，按照主題去建表。主題相當(dāng)于觀察問題的角度。對應(yīng)著維度表。

關(guān)于主題：

數(shù)據(jù)倉庫中的數(shù)據(jù)是面向主題組織的，主題是在較高層次上將企業(yè)信息系統(tǒng)中的數(shù)據(jù)進(jìn)行綜合、歸類和分析利用的一個(gè)抽象概念，每一個(gè)主題基本對應(yīng)一個(gè)宏觀的分析領(lǐng)域。如財(cái)務(wù)分析就是一個(gè)分析領(lǐng)域，因此這個(gè)數(shù)據(jù)倉庫應(yīng)用的主題就為“財(cái)務(wù)分析”。

關(guān)于主題域：

主題域通常是聯(lián)系較為緊密的數(shù)據(jù)主題的集合?？梢愿鶕?jù)業(yè)務(wù)的關(guān)注點(diǎn)，將這些數(shù)據(jù)主題劃分到不同的主題域(也說是對某個(gè)主題進(jìn)行分析后確定的主題的邊界)

關(guān)于主題域的劃分：

主題域的確定必須由最終用戶（業(yè)務(wù)）和數(shù)據(jù)倉庫的設(shè)計(jì)人員共同完成的， 而在劃分主題域時(shí)，大家的切入點(diǎn)不同可能會(huì)造成一些爭論、重構(gòu)等的現(xiàn)象，考慮的點(diǎn)可能會(huì)是下方的某些方面：

• 按照業(yè)務(wù)或業(yè)務(wù)過程劃分：比如一個(gè)靠銷售廣告位置的門戶網(wǎng)站主題域可能會(huì)有廣告域，客戶域等，而廣告域可能就會(huì)有廣告的庫存，銷售分析、內(nèi)部投放分析等主題；

• 根據(jù)需求方劃分：比如需求方為財(cái)務(wù)部，就可以設(shè)定對應(yīng)的財(cái)務(wù)主題域，而財(cái)務(wù)主題域里面可能就會(huì)有員工工資分析，投資回報(bào)比分析等主題；

• 按照功能或應(yīng)用劃分：比如微信中的朋友圈數(shù)據(jù)域、群聊數(shù)據(jù)域等，而朋友圈數(shù)據(jù)域可能就會(huì)有用戶動(dòng)態(tài)信息主題、廣告主題等；

• 按照部門劃分：比如可能會(huì)有運(yùn)營域、技術(shù)域等，運(yùn)營域中可能會(huì)有工資支出分析、活動(dòng)宣傳效果分析等主題；

總而言之，切入的出發(fā)點(diǎn)邏輯不一樣，就可以存在不同的劃分邏輯。在建設(shè)過程中可采用迭代方式，不糾結(jié)于一次完成所有主題的抽象，可先從明確定義的主題開始，后續(xù)逐步歸納總結(jié)成自身行業(yè)的標(biāo)準(zhǔn)模型。

主題：當(dāng)事人、營銷、財(cái)務(wù)、合同協(xié)議、機(jī)構(gòu)、地址、渠道、 產(chǎn)品、

金融業(yè)務(wù)主題有哪些 ：可分為四個(gè)主題：

• 用戶主題（用戶年齡、性別、收貨地址、電話、省份等）

• 交易主題（訂單數(shù)據(jù)、賬單數(shù)據(jù)等）

• 風(fēng)控主題（用戶的風(fēng)控等級，第三方征信數(shù)據(jù)）

• 營銷主題（營銷活動(dòng)名單，活動(dòng)配置信息等）

2）DWD（data warehouse detail）數(shù)據(jù)明細(xì)層，明細(xì)粒度事實(shí)層

DWD是業(yè)務(wù)層與數(shù)據(jù)倉庫的隔離層， 這一層主要解決一些數(shù)據(jù)質(zhì)量問題和數(shù)據(jù)的完整度問題。

明細(xì)表用于存儲(chǔ)ODS層原始表轉(zhuǎn)換過來的明細(xì)數(shù)據(jù)，DWD 層的數(shù)據(jù)應(yīng)該是一致的、準(zhǔn)確的、干凈的數(shù)據(jù)，即對源系統(tǒng)數(shù)據(jù)ODS層數(shù)據(jù)進(jìn)行清洗（去除空值，臟數(shù)據(jù)，超過極限范圍的數(shù)據(jù)，行式存儲(chǔ)改為列存儲(chǔ)，改壓縮格式）、規(guī)范化、維度退化、脫敏等操作。比如用戶的資料信息來自于很多不同表，而且經(jīng)常出現(xiàn)延遲丟數(shù)據(jù)等問題，為了方便各個(gè)使用方更好的使用數(shù)據(jù)，我們可以在這一層做一個(gè)屏蔽。這一層也包含統(tǒng)一的維度數(shù)據(jù)。

明細(xì)粒度事實(shí)層（DWD）：以業(yè)務(wù)過程作為建模驅(qū)動(dòng)，基于每個(gè)具體的業(yè)務(wù)過程特點(diǎn)，構(gòu)建最細(xì)粒度的明細(xì)層事實(shí)表?？梢越Y(jié)合企業(yè)的數(shù)據(jù)使用特點(diǎn)，將明細(xì)事實(shí)表的某些重要維度屬性字段做適當(dāng)冗余，即寬表化處理。明細(xì)粒度事實(shí)層的表通常也被稱為邏輯事實(shí)表。

負(fù)責(zé)數(shù)據(jù)的最細(xì)粒度的數(shù)據(jù)，在DWD層基礎(chǔ)上，進(jìn)行輕度匯總，結(jié)合常用維度（時(shí)間，地點(diǎn)，組織層級，用戶，商品等）

該層一般保持和ODS層一樣的數(shù)據(jù)粒度，并且提供一定的數(shù)據(jù)質(zhì)量保證，在ODS的基礎(chǔ)上對數(shù)據(jù)進(jìn)行加工處理，提供更干凈的數(shù)據(jù)。同時(shí)，為了提高數(shù)據(jù)明細(xì)層的易用性，該層會(huì)采用一些維度退化手法，當(dāng)一個(gè)維度沒有數(shù)據(jù)倉庫需要的任何數(shù)據(jù)時(shí)，就可以退化維度，將維度退化至事實(shí)表中，減少事實(shí)表和維表的關(guān)聯(lián)。

例如：

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

這一層的數(shù)據(jù)一般是遵循數(shù)據(jù)庫第三范式或者維度建模，其數(shù)據(jù)粒度通常和 ODS 的粒度相同。在 PDW 層會(huì)保存 BI 系統(tǒng)中所有的歷史數(shù)據(jù)，例如保存10年的數(shù)據(jù)。

數(shù)據(jù)在裝入本層前需要做以下工作：去噪、去重、提臟、業(yè)務(wù)提取、單位統(tǒng)一、砍字段、業(yè)務(wù)判別。

清洗的數(shù)據(jù)種類：

• 不完整數(shù)據(jù)

• 錯(cuò)誤數(shù)據(jù)

• 重復(fù)的數(shù)據(jù)

數(shù)據(jù)清洗的任務(wù)是過濾那些不符合要求的數(shù)據(jù)，將過濾的結(jié)果交給業(yè)務(wù)主管部門，確認(rèn)是否過濾掉還是由業(yè)務(wù)單位修正之后再進(jìn)行抽取。

DWD層做了哪些事？

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

去除廢棄字段,去除格式錯(cuò)誤的信息

去除丟失了關(guān)鍵字段的信息

過濾核心字段無意義的數(shù)據(jù)，比如訂單表中訂單id為null，支付表中支付id為空

對手機(jī)號、身份證號等敏感數(shù)據(jù)脫敏

去除不含時(shí)間信息的數(shù)據(jù)(這個(gè)看公司具體業(yè)務(wù),但一般數(shù)據(jù)中都會(huì)帶上時(shí)間戳,這樣方便后續(xù)處理時(shí),進(jìn)行時(shí)間維度上信息分析處理和提取)

有些公司還會(huì)在這一層將數(shù)據(jù)打平,不過這具體要看業(yè)務(wù)需求.這是因?yàn)閗ylin適合處理展平后數(shù)據(jù),不適合處理嵌套的表數(shù)據(jù)信息

有些公司還會(huì)將數(shù)據(jù)session做切割,這個(gè)一般是app的日志數(shù)據(jù),其他業(yè)務(wù)場景不一定適合.這是因?yàn)閍pp有進(jìn)入后臺(tái)模式,例如用戶上午打開app用了10分鐘,然后app切入后臺(tái),晚上再打開,這時(shí)候session還是一個(gè),實(shí)際上應(yīng)該做切割才對.(也有公司會(huì)記錄app進(jìn)入后臺(tái),再度進(jìn)入前臺(tái)的記錄,這樣來做session切割)

②數(shù)據(jù)映射,轉(zhuǎn)換

將GPS經(jīng)緯度轉(zhuǎn)換為省市區(qū)詳細(xì)地址。業(yè)界常見GPS快速查詢一般將地理位置知識(shí)庫使用geohash映射,然后將需要比對的GPS轉(zhuǎn)換為geohash后跟知識(shí)庫中g(shù)eohash比對,查找出地理位置信息當(dāng)然,也有公司使用open api,如高德地圖,百度地圖的api進(jìn)行GPS和地理位置信息映射,但這個(gè)達(dá)到一定次數(shù)需要花錢,所以大家都懂的

會(huì)將IP地址也轉(zhuǎn)換為省市區(qū)詳細(xì)地址。這個(gè)有很多快速查找?guī)?不過基本原理都是二分查找,因?yàn)閕p地址可以轉(zhuǎn)換為長整數(shù).典型的如ip2region庫

將時(shí)間轉(zhuǎn)換為年,月,日甚至周,季度維度信息

數(shù)據(jù)規(guī)范化,因?yàn)榇髷?shù)據(jù)處理的數(shù)據(jù)可能來資源公司不同部門,不同項(xiàng)目,不同客戶端,這時(shí)候可能相同業(yè)務(wù)數(shù)據(jù)字段,數(shù)據(jù)類型,空值等都不一樣,這時(shí)候需要在DWD層做抹平.否則后續(xù)處理使用時(shí),會(huì)造成很大的困擾

如boolean,有使用0 1標(biāo)識(shí),也有使用true false標(biāo)識(shí)的

如字符串空值,有使用'',也有使用null,的,統(tǒng)一為null即可

如日期格式,這種就差異性更大,需要根據(jù)實(shí)際業(yè)務(wù)數(shù)據(jù)決定,不過一般都是格式化為YYYY-MM-dd HH:mm:ss 這類標(biāo)準(zhǔn)格式

維度退化：對業(yè)務(wù)數(shù)據(jù)傳過來的表進(jìn)行維度退化和降維。（商品一級二級三級、省市縣、年月日）訂單id冗余在事實(shí)表

清洗掉多少數(shù)據(jù)算合理：1萬條數(shù)據(jù)清洗掉1條。

合理表數(shù)：一萬張表變?yōu)槿埍?，三千張表變?yōu)橐磺埍?/p>

明細(xì)粒度事實(shí)表設(shè)計(jì)原則：

• 一個(gè)明細(xì)粒度事實(shí)表僅和一個(gè)維度關(guān)聯(lián)。

• 盡可能包含所有與業(yè)務(wù)過程相關(guān)的事實(shí) 。

• 只選擇與業(yè)務(wù)過程相關(guān)的事實(shí)。

• 分解不可加性事實(shí)為可加的組件。

• 在選擇維度和事實(shí)之前必須先聲明粒度。

• 在同一個(gè)事實(shí)表中不能有多種不同粒度的事實(shí)。

• 事實(shí)的單位要保持一致。粒度

• 謹(jǐn)慎處理Null值。

• 使用退化維度提高事實(shí)表的易用性。

方案

討論方案：數(shù)據(jù)的合成方式為：

全量：每天把明細(xì)層的前天全量數(shù)據(jù)和昨天新數(shù)據(jù)合成一個(gè)新的數(shù)據(jù)表，覆蓋舊表。同時(shí)使用歷史鏡像，按周/按月/按年存儲(chǔ)一個(gè)歷史鏡像到新表。

日志存儲(chǔ)方式：直接數(shù)據(jù)使用impala外表，parquet文件格式， 建議使用內(nèi)表，下面幾層都是從impala生成的數(shù)據(jù)，建議都用內(nèi)表+靜態(tài)/動(dòng)態(tài)分區(qū)。

表schema：一般按天創(chuàng)建分區(qū)，沒有時(shí)間概念的按具體業(yè)務(wù)選擇分區(qū)字段。partitioned by 一般都是按照天進(jìn)行存放。

庫與表命名。庫名：dwd,表名：初步考慮格式為dwd日期業(yè)務(wù)表名,待定。

舊數(shù)據(jù)更新方式：直接覆蓋

明細(xì)粒度事實(shí)層（DWD）規(guī)范

命名規(guī)范為：dwd_{業(yè)務(wù)板塊/pub}_{數(shù)據(jù)域縮寫}_{業(yè)務(wù)過程縮寫}[_{自定義表命名標(biāo)簽縮寫}] _{單分區(qū)增量全量標(biāo)識(shí)}，pub表示數(shù)據(jù)包括多個(gè)業(yè)務(wù)板塊的數(shù)據(jù)。單分區(qū)增量全量標(biāo)識(shí)通常為：i表示增量，f表示全量。

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

本教程中，DWD層主要由三個(gè)表構(gòu)成：

• 交易商品信息事實(shí)表：dwd_asale_trd_itm_di。

• 交易會(huì)員信息事實(shí)表：ods_asale_trd_mbr_di。

• 交易訂單信息事實(shí)表：dwd_asale_trd_ord_di。

3）DWS（ data warehouse service）數(shù)據(jù)服務(wù)層，匯總層寬表

基于 DWD 明細(xì)數(shù)據(jù)層，我們會(huì)按照一些分析場景、分析實(shí)體等去組織我們的數(shù)據(jù)，組織成一些分主題的匯總數(shù)據(jù)層 DWS。

明細(xì)粒度 ==> 匯總粒度

DWS層（數(shù)據(jù)匯總層）寬表，面向主題的匯總，維度相對來說比較少，DWS是根據(jù)DWD層基礎(chǔ)數(shù)據(jù)按各個(gè)維度ID進(jìn)行粗粒度匯總聚合，如按交易來源，交易類型進(jìn)行匯合。整合匯總成分析某一個(gè)主題域的服務(wù)數(shù)據(jù)，一般是寬表。

以DWD為基礎(chǔ)，按天進(jìn)行輕度匯總。統(tǒng)計(jì)各個(gè)主題對象的當(dāng)天行為，（例如，購買行為，統(tǒng)計(jì)商品復(fù)購率）。

該層數(shù)據(jù)表會(huì)相對比較少，大多都是寬表(一張表會(huì)涵蓋比較多的業(yè)務(wù)內(nèi)容，表中的字段較多)。按照主題劃分，如訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。

融合多個(gè)中間層數(shù)據(jù)，基于主題形成事實(shí)表，比如用戶事實(shí)表、渠道事實(shí)表、終端事實(shí)表、資產(chǎn)事實(shí)表等等，事實(shí)表一般是寬表，在本層上實(shí)現(xiàn)企業(yè)級數(shù)據(jù)的一致性。

首先劃分業(yè)務(wù)主題，將主題劃分為銷售域、庫存域、客戶域、采購域 等，其次就是 確定每個(gè)主題域的事實(shí)表和維度表。通常根據(jù)業(yè)務(wù)需求，劃分成流量、訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。

最近一天某個(gè)類目（例如：廚具）商品在各省的銷售總額、該類目Top10銷售額商品名稱、各省用戶購買力分布。因此，我們可以以最終交易成功的商品、類目、買家等角度對最近一天的數(shù)據(jù)進(jìn)行匯總。

比如用戶每個(gè)時(shí)間段在不同登錄ip購買的商品數(shù)等。這里做一層輕度的匯總會(huì)讓計(jì)算更加的高效，在此基礎(chǔ)上如果計(jì)算僅7天、30天、90天的行為的話會(huì)快很多。我們希望80%的業(yè)務(wù)都能通過我們的DWS層計(jì)算，而不是ODS。

DWS層做了哪些事？

dws將dwd層的數(shù)據(jù)按主題進(jìn)行匯總，按照主題放到一個(gè)表中，

比如用戶主題下會(huì)將用戶注冊信息、用戶收貨地址、用戶的征信數(shù)據(jù)放到同一張表中，而這些在dwd層是對應(yīng)多張表的,按照業(yè)務(wù)劃分，如流量、訂單、用戶等，生成字段比較多的寬表

主題建模,圍繞某一個(gè)業(yè)務(wù)主題進(jìn)行數(shù)據(jù)建模,將相關(guān)數(shù)據(jù)抽離提取出來.

如：

• 將流量會(huì)話按照天,月進(jìn)行聚合

• 將每日新用戶進(jìn)行聚合

• 將每日活躍用戶進(jìn)行聚合

• 維度建模,其實(shí)也差不多,不過是根據(jù)業(yè)務(wù)需要,提前將后續(xù)數(shù)據(jù)查詢處理需要的維度數(shù)據(jù)抽離處理出來,方便后續(xù)查詢使用.

• 如將運(yùn)營位維度數(shù)據(jù)聚合

• 將渠道拉新維度數(shù)據(jù)聚合

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

具體寬表名稱：用戶行為寬表，用戶購買商品明細(xì)行為寬表，商品寬表， 物流寬表、 售后等。

②哪個(gè)寬表最寬？大概有多少個(gè)字段？

最寬的是用戶行為寬表。大概有60-200個(gè)字段

③具體用戶行為寬表字段名稱

評論、打賞、收藏、關(guān)注--商品、關(guān)注--人、點(diǎn)贊、分享、好價(jià)爆料、文章發(fā)布、活躍、簽到、補(bǔ)簽卡、幸運(yùn)屋、禮品、金幣、電商點(diǎn)擊、gmv

④分析過的指標(biāo)

日活、月活、周活、留存、留存率、新增（日、周、年）、轉(zhuǎn)化率、流失、回流、七天內(nèi)連續(xù) 3 天登錄（點(diǎn)贊、收藏、評價(jià)、購買、加購、下單、活動(dòng)）、連續(xù) 3 周（月）登錄、GMV（成交金額，下單）、復(fù)購率、復(fù)購率排行、點(diǎn)贊、評論、收藏、領(lǐng)優(yōu)惠價(jià)人數(shù)、使用優(yōu)惠價(jià)、沉默、值不值得買、退款人數(shù)、退款率 topn 熱門商品

• 活躍

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

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

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

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

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

10%-20% 100 萬-200 萬

• 復(fù)購率

某日常商品復(fù)購；（手紙、面膜、牙膏）10%-20%

電腦、顯示器、手表 1%

• 轉(zhuǎn)化率

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

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

• 留存率

1/2/3、周留存、月留存

搞活動(dòng)：10-20%

方案：

概念：又稱數(shù)據(jù)集市或?qū)挶?。按照業(yè)務(wù)劃分，如流量、訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。

數(shù)據(jù)生成方式：由輕度匯總層和明細(xì)層數(shù)據(jù)計(jì)算生成。

日志存儲(chǔ)方式：使用impala內(nèi)表，parquet文件格式。

表schema：一般按天創(chuàng)建分區(qū)，沒有時(shí)間概念的按具體業(yè)務(wù)選擇分區(qū)字段。

庫與表命名。庫名：dws, 表名：初步考慮格式為：dws日期業(yè)務(wù)表名,待定。

舊數(shù)據(jù)更新方式：直接覆蓋

公共匯總事實(shí)表規(guī)范

公共匯總事實(shí)表命名規(guī)范：dws_{業(yè)務(wù)板塊縮寫/pub}_{數(shù)據(jù)域縮寫}_{數(shù)據(jù)粒度縮寫}[_{自定義表命名標(biāo)簽縮寫}]_{統(tǒng)計(jì)時(shí)間周期范圍縮寫}。關(guān)于統(tǒng)計(jì)實(shí)際周期范圍縮寫，缺省情況下，離線計(jì)算應(yīng)該包括最近一天（_1d），最近N天（_nd）和歷史截至當(dāng)天（_td）三個(gè)表。如果出現(xiàn)_nd的表字段過多需要拆分時(shí)，只允許以一個(gè)統(tǒng)計(jì)周期單元作為原子拆分。即一個(gè)統(tǒng)計(jì)周期拆分一個(gè)表，例如最近7天（_1w）拆分一個(gè)表。不允許拆分出來的一個(gè)表存儲(chǔ)多個(gè)統(tǒng)計(jì)周期。

對于小時(shí)表（無論是天刷新還是小時(shí)刷新），都用_hh來表示。對于分鐘表（無論是天刷新還是小時(shí)刷新），都用_mm來表示。

舉例如下：

dws_asale_trd_byr_subpay_1d（買家粒度交易分階段付款一日匯總事實(shí)表）

dws_asale_trd_byr_subpay_td（買家粒度分階段付款截至當(dāng)日匯總表）

dws_asale_trd_byr_cod_nd（買家粒度貨到付款交易匯總事實(shí)表）

dws_asale_itm_slr_td（賣家粒度商品截至當(dāng)日存量匯總表）

dws_asale_itm_slr_hh（賣家粒度商品小時(shí)匯總表）---維度為小時(shí)

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 '地址',--下單數(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<struct<sku_id:string,sku_num:bigint,order_count:bigint,order_amount:decimal(20,2)>> comment '下單明細(xì)統(tǒng)計(jì)') comment '每日購買行為'partitioned by(`dt`string)stored as parquetlocation '/warehouse/gmall/dws/dws_sale_detail_daycount/'tblproperties('parquet.compression' = 'lzo');

• 商品維度：商品主題

問：數(shù)據(jù)集市層是不是沒地方放了，各個(gè)業(yè)務(wù)的數(shù)據(jù)集市表是應(yīng)該在 dws 還是在 app?

答：這個(gè)問題不太好回答，我感覺主要就是明確一下數(shù)據(jù)集市層是干什么的，如果你的數(shù)據(jù)集市層放的就是一些可以供業(yè)務(wù)方使用的寬表，放在 app 層就行。如果你說的數(shù)據(jù)集市層是一個(gè)比較泛一點(diǎn)的概念，那么其實(shí) dws、dwd、app 這些合起來都算是數(shù)據(jù)集市的內(nèi)容。

3、應(yīng)用層（ADS）applicationData Service應(yīng)用數(shù)據(jù)服務(wù)

數(shù)據(jù)應(yīng)用層（ADS，Application Data Store）：存放數(shù)據(jù)產(chǎn)品個(gè)性化的統(tǒng)計(jì)指標(biāo)數(shù)據(jù)，報(bào)表數(shù)據(jù)。主要是提供給數(shù)據(jù)產(chǎn)品和數(shù)據(jù)分析使用的數(shù)據(jù)，通常根據(jù)業(yè)務(wù)需求，劃分成流量、訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。從數(shù)據(jù)粒度來說，這層的數(shù)據(jù)是匯總級的數(shù)據(jù)，也包括部分明細(xì)數(shù)據(jù)。從數(shù)據(jù)的時(shí)間跨度來說，通常是DW層的一部分，主要的目的是為了滿足用戶分析的需求，而從分析的角度來說，用戶通常只需要分析近幾年的即可。從數(shù)據(jù)的廣度來說，仍然覆蓋了所有業(yè)務(wù)數(shù)據(jù)。

在 DWS 之上，我們會(huì)面向應(yīng)用場景去做一些更貼近應(yīng)用的 APP 應(yīng)用數(shù)據(jù)層，這些數(shù)據(jù)應(yīng)該是高度匯總的，并且能夠直接導(dǎo)入到我們的應(yīng)用服務(wù)去使用。

應(yīng)用層(ADS)：應(yīng)用層主要是各個(gè)業(yè)務(wù)方或者部門基于DWD和DWS建立的數(shù)據(jù)集市(Data Market, DM)，一般來說應(yīng)用層的數(shù)據(jù)來源于DW層，而且相對于DW層，應(yīng)用層只包含部門或者業(yè)務(wù)方面自己關(guān)心的明細(xì)層和匯總層的數(shù)據(jù)。

該層主要是提供數(shù)據(jù)產(chǎn)品和數(shù)據(jù)分析使用的數(shù)據(jù)。一般就直接對接OLAP分析,或者業(yè)務(wù)層數(shù)據(jù)調(diào)用接口了

數(shù)據(jù)應(yīng)用層APP：面向業(yè)務(wù)定制的應(yīng)用數(shù)據(jù)主要提供給數(shù)據(jù)鏟平和數(shù)據(jù)分析使用的數(shù)據(jù)，一般會(huì)放在ES，MYSQL，Oracle，Redis等系統(tǒng)供線上系統(tǒng)使用，也可以放在Hive 或者 Druid 中供數(shù)據(jù)分析和數(shù)據(jù)挖掘使用。

APP 層：為應(yīng)用層，這層數(shù)據(jù)是完全為了滿足具體的分析需求而構(gòu)建的數(shù)據(jù)，也是星形或雪花結(jié)構(gòu)的數(shù)據(jù)。如我們經(jīng)常說的報(bào)表數(shù)據(jù)，或者說那種大寬表，一般就放在這里。包括前端報(bào)表、分析圖表、KPI、儀表盤、OLAP、專題等分析，面向最終結(jié)果用戶；

概念：應(yīng)用層是根據(jù)業(yè)務(wù)需要，由前面三層數(shù)據(jù)統(tǒng)計(jì)而出的結(jié)果，可以直接提供查詢展現(xiàn)，或?qū)胫罬ysql中使用。

數(shù)據(jù)生成方式：由明細(xì)層、輕度匯總層，數(shù)據(jù)集市層生成，一般要求數(shù)據(jù)主要來源于集市層。

日志存儲(chǔ)方式：使用impala內(nèi)表，parquet文件格式。

表schema：一般按天創(chuàng)建分區(qū)，沒有時(shí)間概念的按具體業(yè)務(wù)選擇分區(qū)字段。

庫與表命名。庫名：暫定ads，另外根據(jù)業(yè)務(wù)不同，不限定一定要一個(gè)庫。

舊數(shù)據(jù)更新方式：直接覆蓋。

ADS 層復(fù)購率統(tǒng)計(jì)

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 '點(diǎn)不值次數(shù)',zhi_cnt bigint COMMENT '點(diǎn)值次數(shù)',zan_cnt bigint COMMENT '點(diǎn)贊次數(shù)',share_cnts bigint COMMENT '分享次數(shù)',bl_cnt bigint COMMENT '爆料數(shù)',fb_cnt bigint COMMENT '好價(jià)發(fā)布數(shù)',online_cnt bigint COMMENT '活躍次數(shù)',checkin_cnt bigint COMMENT '簽到次數(shù)',fix_checkin bigint COMMENT '補(bǔ)簽次數(shù)',house_point bigint COMMENT '幸運(yùn)屋金幣抽獎(jiǎng)次數(shù)',house_gold bigint COMMENT '幸運(yùn)屋積分抽獎(jiǎng)次數(shù)',pack_cnt bigint COMMENT '禮品兌換次數(shù)',gold_add bigint COMMENT '獲取金幣',gold_cancel bigint COMMENT '支出金幣',surplus_gold bigint COMMENT '剩余金幣',event bigint COMMENT '電商點(diǎn)擊次數(shù)',gmv_amount bigint COMMENT 'gmv',gmv_sales bigint COMMENT '訂單數(shù)')PARTITIONED BY( dt string)--stat_dtdate COMMENT '互動(dòng)日期',

①如何分析用戶活躍？

在啟動(dòng)日志中統(tǒng)計(jì)不同設(shè)備 id 出現(xiàn)次數(shù)。

②如何分析用戶新增?

用活躍用戶表 left join 用戶新增表，用戶新增表中 mid 為空的即為用戶新增。

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

留存用戶=前一天新增 join 今天活躍

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

④如何分析沉默用戶？

(登錄時(shí)間為 7 天前,且只出現(xiàn)過一次)

按照設(shè)備 id 對日活表分組，登錄次數(shù)為 1，且是在一周前登錄。

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

本周活躍 left join 本周新增 left join 上周活躍，且本周新增 id 和上周活躍 id 都為 null。

⑥如何分析流失用戶？

(登錄時(shí)間為 7 天前)

按照設(shè)備 id 對日活表分組，且七天內(nèi)沒有登錄過。

⑦如何分析最近連續(xù) 3 周活躍用戶數(shù)？

按照設(shè)備 id 對周活進(jìn)行分組，統(tǒng)計(jì)次數(shù)大于 3 次。

⑧如何分析最近七天內(nèi)連續(xù)三天活躍用戶數(shù)？

• 查詢出最近 7 天的活躍用戶，并對用戶活躍日期進(jìn)行排名

• 計(jì)算用戶活躍日期及排名之間的差值

• 對同用戶及差值分組，統(tǒng)計(jì)差值個(gè)數(shù)

• 將差值相同個(gè)數(shù)大于等于 3 的數(shù)據(jù)取出，然后去重，即為連續(xù) 3 天及以上活躍的用戶

7 天連續(xù)收藏、點(diǎn)贊、購買、加購、付款、瀏覽、商品點(diǎn)擊、退貨

1 個(gè)月連續(xù) 7 天

連續(xù)兩周

TMP：每一層的計(jì)算都會(huì)有很多臨時(shí)表，專設(shè)一個(gè)DW TMP層來存儲(chǔ)我們數(shù)據(jù)倉庫的臨時(shí)表。

4、層次調(diào)用規(guī)范

• 禁止反向調(diào)用

• ODS 只能被 DWD 調(diào)用。

• DWD 可以被 DWS 和 ADS 調(diào)用。

• DWS 只能被 ADS 調(diào)用。

• 數(shù)據(jù)應(yīng)用可以調(diào)用 DWD、DWS、ADS，但建議優(yōu)先考慮使用匯總度高的數(shù)據(jù)。

• ODS->DWD->DWS>ADS

• ODS->DWD->ADS