一、為什么要對數(shù)據(jù)倉庫分層
只有數(shù)據(jù)模型將數(shù)據(jù)有序的組織和存儲起來之后，大數(shù)據(jù)才能得到高性能、低成本、高效率、高質(zhì)量的使用。
01 分層意義
1）清晰數(shù)據(jù)結(jié)構(gòu)：每一個數(shù)據(jù)分層都有它的作用域，這樣我們在使用表的時候能更方便地定位和理解。
數(shù)據(jù)關(guān)系條理化：源系統(tǒng)間存在復(fù)雜的數(shù)據(jù)關(guān)系，比如客戶信息同時存在于核心系統(tǒng)、信貸系統(tǒng)、理財系統(tǒng)、資金系統(tǒng)，取數(shù)時該如何決策呢？數(shù)據(jù)倉庫會對相同主題的數(shù)據(jù)進行統(tǒng)一建模，把復(fù)雜的數(shù)據(jù)關(guān)系梳理成條理清晰的數(shù)據(jù)模型，使用時就可避免上述問題了。
2）數(shù)據(jù)血緣追蹤：簡單來講可以這樣理解，我們最終給業(yè)務(wù)誠信的是一能直接使用的張業(yè)務(wù)表，但是它的來源有很多，如果有一張來源表出問題了，我們希望能夠快速準(zhǔn)確地定位到問題，并清楚它的危害范圍。
3）數(shù)據(jù)復(fù)用，減少重復(fù)開發(fā)：規(guī)范數(shù)據(jù)分層，開發(fā)一些通用的中間層數(shù)據(jù)，能夠減少極大的重復(fù)計算。數(shù)據(jù)的逐層加工原則，下層包含了上層數(shù)據(jù)加工所需要的全量數(shù)據(jù)，這樣的加工方式避免了每個數(shù)據(jù)開發(fā)人員都重新從源系統(tǒng)抽取數(shù)據(jù)進行加工。通過匯總層的引人，避免了下游用戶邏輯的重復(fù)計算， 節(jié)省了用戶的開發(fā)時間和精力，同時也節(jié)省了計算和存儲。極大地減少不必要的數(shù)據(jù)冗余，也能實現(xiàn)計算結(jié)果復(fù)用，極大地降低存儲和計算成本。
4）把復(fù)雜問題簡單化。講一個復(fù)雜的任務(wù)分解成多個步驟來完成，每一層只處理單一的步驟，比較簡單和容易理解。而且便于維護數(shù)據(jù)的準(zhǔn)確性，當(dāng)數(shù)據(jù)出現(xiàn)問題之后，可以不用修復(fù)所有的數(shù)據(jù)，只需要從有問題的步驟開始修復(fù)。
5）屏蔽原始數(shù)據(jù)的(影響) ，屏蔽業(yè)務(wù)的影響。業(yè)務(wù)或系統(tǒng)發(fā)生變化時，不必改一次業(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 層來處理，對下游用戶透明，無須改動下游用戶的代碼和邏輯。
數(shù)據(jù)倉庫的可維護性：分層的設(shè)計使得某一層的問題只在該層得到解決，無須更改下一層的代碼和邏輯。
大數(shù)據(jù)系統(tǒng)需要數(shù)據(jù)模型方法來幫助更好地組織和存儲數(shù)據(jù)，以便在性能、成本、效率和質(zhì)量之間取得最佳平衡！
02 數(shù)據(jù)倉庫（ETL）的四個操作
ETL(extractiontransformation loading)負(fù)責(zé)將分散的、異構(gòu)數(shù)據(jù)源中的數(shù)據(jù)抽取到臨時中間層后進行清洗、轉(zhuǎn)換、集成，最后加載到數(shù)據(jù)倉庫或數(shù)據(jù)集市中。ETL 是實施數(shù)據(jù)倉庫的核心和靈魂，ETL規(guī)則的設(shè)計和實施約占整個數(shù)據(jù)倉庫搭建工作量的 60%～80%。
1）數(shù)據(jù)抽取(extraction)包括初始化數(shù)據(jù)裝載和數(shù)據(jù)刷新：初始化數(shù)據(jù)裝載主要關(guān)注的是如何建立維表、事實表，并把相應(yīng)的數(shù)據(jù)放到這些數(shù)據(jù)表中；而數(shù)據(jù)刷新關(guān)注的是當(dāng)源數(shù)據(jù)發(fā)生變化時如何對數(shù)據(jù)倉庫中的相應(yīng)數(shù)據(jù)進行追加和更新等維護(比如可以創(chuàng)建定時任務(wù)，或者觸發(fā)器的形式進行數(shù)據(jù)的定時刷新)。
2）數(shù)據(jù)清洗主要是針對源數(shù)據(jù)庫中出現(xiàn)的二義性、重復(fù)、不完整、違反業(yè)務(wù)或邏輯規(guī)則等問題的數(shù)據(jù)進行統(tǒng)一的處理。即清洗掉不符合業(yè)務(wù)或者沒用的的數(shù)據(jù)。比如通過編寫hive或者MR清洗字段中長度不符合要求的數(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)容進行歸一化；另一方面，數(shù)據(jù)倉庫所需要的某些字段的內(nèi)容可能是源系統(tǒng)所不具備的，而是需要根據(jù)源系統(tǒng)中多個字段的內(nèi)容共同確定。
4）數(shù)據(jù)加載（loading）是將最后上面處理完的數(shù)據(jù)導(dǎo)入到對應(yīng)的存儲空間里（hbase，MySQL等）以方便給數(shù)據(jù)集市提供，進而可視化。
一般大公司為了數(shù)據(jù)安全和操作方便，都是自己封裝的數(shù)據(jù)平臺和任務(wù)調(diào)度平臺，底層封裝了大數(shù)據(jù)集群比如hadoop集群，spark集群，sqoop,hive,zookeepr,hbase等只提供web界面，并且對于不同員工加以不同權(quán)限，然后對集群進行不同的操作和調(diào)用。以數(shù)據(jù)倉庫為例，將數(shù)據(jù)倉庫分為邏輯上的幾個層次。這樣對于不同層次的數(shù)據(jù)操作，創(chuàng)建不同層次的任務(wù)，可以放到不同層次的任務(wù)流中進行執(zhí)行（大公司一個集群通常每天的定時任務(wù)有幾千個等待執(zhí)行，甚至上萬個，所以劃分不同層次的任務(wù)流，不同層次的任務(wù)放到對應(yīng)的任務(wù)流中進行執(zhí)行，會更加方便管理和維護）。
03 分層的誤區(qū)
數(shù)倉層內(nèi)部的劃分不是為了分層而分層，分層是為了解決 ETL 任務(wù)及工作流的組織、數(shù)據(jù)的流向、讀寫權(quán)限的控制、不同需求的滿足等各類問題。
業(yè)界較為通行的做法將整個數(shù)倉層又劃分成了 DWD、DWT、DWS、DIM、DM等很多層。然而我們卻始終說不清楚這幾層之間清晰的界限是什么，或者說我們能說清楚它們之間的界限，復(fù)雜的業(yè)務(wù)場景卻令我們無法真正落地執(zhí)行。
所以數(shù)據(jù)分層這塊一般來說三層是最基礎(chǔ)的，至于DW層如何進行切分，是根據(jù)具體的業(yè)務(wù)需求和公司場景自己去定義。
二、數(shù)據(jù)倉庫的技術(shù)架構(gòu)
數(shù)據(jù)中臺包含的內(nèi)容很多，對應(yīng)到具體工作中的話，它可以包含下面的這些內(nèi)容：
- 系統(tǒng)架構(gòu)：以Hadoop、Spark等組件為中心的架構(gòu)體系
- 數(shù)據(jù)架構(gòu)：頂層設(shè)計，主題域劃分，分層設(shè)計，ODS-DW-ADS
- 數(shù)據(jù)建模：維度建模，業(yè)務(wù)過程-確定粒度-維度-事實表
- 數(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ù)門戶、機器學(xué)習(xí)數(shù)據(jù)挖掘、數(shù)據(jù)查詢、分析、報表系統(tǒng)、可視化系統(tǒng)、數(shù)據(jù)交換分享下載
三、數(shù)倉分層架構(gòu)
數(shù)據(jù)倉庫標(biāo)準(zhǔn)上可以分為四層。但是注意這種劃分和命名不是唯一的，一般數(shù)倉都是四層，但是不同公司可能叫法不同。但是核心的理念都是從四層數(shù)據(jù)模型而來。
01 貼源層（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ù)同步、存儲。
一般來說 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ù)量不大，可以保留更長的時間，甚至全量保存。
注意：在這層，理應(yīng)不是簡單的數(shù)據(jù)接入，而是要考慮一定的數(shù)據(jù)清洗，比如異常字段的處理、字段命名規(guī)范化、時間字段的統(tǒng)一等，一般這些很容易會被忽略，但是卻至關(guān)重要。特別是后期我們做各種特征自動生成的時候，會十分有用。
注意：有的公司ODS層不會做太多數(shù)據(jù)過濾處理,會放到DWD層來處理。有的公司會在一開始時就在ODS層做數(shù)據(jù)相對精細(xì)化的過濾.這個并沒有明確規(guī)定,看每個公司自己的想法和技術(shù)規(guī)范。
一般企業(yè)開發(fā)時,都會對原始數(shù)據(jù)存入到ODS時,做一些最基本的處理。
數(shù)據(jù)來源區(qū)分
數(shù)據(jù)按照時間分區(qū)存儲,一般是按照天,也有公司使用年、月、日三級分區(qū)做存儲的。
進行最基本的數(shù)據(jù)處理,如格式錯誤的丟棄,關(guān)鍵信息丟失的過濾掉等等。
數(shù)據(jù)實時離線
- 離線方面：每日定時任務(wù)型：跑批任務(wù)，業(yè)務(wù)庫，比如我們典型的日計算任務(wù)，這里經(jīng)常會使用 Sqoop 來抽取，比如我們每天定時抽取一次。每天凌晨算前一天的數(shù)據(jù)，早上起來看報表。這種任務(wù)經(jīng)常使用 Hive、Spark 來計算，最終結(jié)果寫入 Hive、Hbase、Mysql、Es 或者 redis 中。
- 實時數(shù)據(jù)：日志埋點數(shù)據(jù)或者業(yè)務(wù)庫，這部分主要是各種實時的系統(tǒng)使用，比如我們的實時推薦、實時用戶畫像，一般我們會用 Spark Streaming、Flink 來計算，最后會落入 Es、Hbase 或者 Redis 中。數(shù)據(jù)源是業(yè)務(wù)數(shù)據(jù)庫，可以考慮用 Canal 監(jiān)聽 Mysql 的 Binlog，實時接入即可，然后也是收集到消息隊列中，最終再由 Camus 拉取到 HDFS。
1）數(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ù)匯總到一起
2）數(shù)據(jù)存儲策略（增量、全量）
實際應(yīng)用中，可以選擇采用增量、全量存儲或拉鏈存儲的方式。
- 增量存儲
為了滿足歷史數(shù)據(jù)分析需求，您可以在ODS層表中添加時間維度作為分區(qū)字段。以天為單位的增量存儲，以業(yè)務(wù)日期作為分區(qū)，每個分區(qū)存放日增量的業(yè)務(wù)數(shù)據(jù)。
舉例如下：
1月1日，用戶A訪問了A公司電商店鋪B，A公司電商日志產(chǎn)生一條記錄t1。1月2日，用戶A又訪問了A公司電商店鋪C，A公司電商日志產(chǎn)生一條記錄t2。
采用增量存儲方式，t1將存儲在1月1日這個分區(qū)中，t2將存儲在1月2日這個分區(qū)中。
1月1日，用戶A在A公司電商網(wǎng)購買了B商品，交易日志將生成一條記錄t1。1月2日，用戶A又將B商品退貨了，交易日志將更新t1記錄。
采用增量存儲方式，初始購買的t1記錄將存儲在1月1日這個分區(qū)中，更新后的t1將存儲在1月2日這個分區(qū)中。
交易、日志等事務(wù)性較強的ODS表適合增量存儲方式。這類表數(shù)據(jù)量較大，采用全量存儲的方式存儲成本壓力大。此外，這類表的下游應(yīng)用對于歷史全量數(shù)據(jù)訪問的需求較?。ù祟愋枨罂赏ㄟ^數(shù)據(jù)倉庫后續(xù)匯總后得到）。例如，日志類ODS表沒有數(shù)據(jù)更新的業(yè)務(wù)過程，因此所有增量分區(qū)UNION在一起就是一份全量數(shù)據(jù)。
- 全量存儲
以天為單位的全量存儲，以業(yè)務(wù)日期作為分區(qū)，每個分區(qū)存放截止到業(yè)務(wù)日期為止的全量業(yè)務(wù)數(shù)據(jù)。
例如，1月1日，賣家A在A公司電商網(wǎng)發(fā)布了B、C兩個商品，前端商品表將生成兩條記錄t1、t2。1月2日，賣家A將B商品下架了，同時又發(fā)布了商品D，前端商品表將更新記錄t1，同時新生成記錄t3。采用全量存儲方式， 在1月1日這個分區(qū)中存儲t1和t2兩條記錄，在1月2日這個分區(qū)中存儲更新后的t1以及t2、t3記錄。
對于小數(shù)據(jù)量的緩慢變化維度數(shù)據(jù)，例如商品類目，可直接使用全量存儲。
- 拉鏈存儲
拉鏈存儲通過新增兩個時間戳字段（start_dt和end_dt），將所有以天為粒度的變更數(shù)據(jù)都記錄下來，通常分區(qū)字段也是這兩個時間戳字段。
方案
概念：又稱為接口層(stage)，用于存儲每天的增量數(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ù)，也入緩沖層。
日志存儲方式：使用impala外表，parquet文件格式，方便需要MR處理的數(shù)據(jù)讀取。
日志刪除方式：長久存儲，可只存儲最近幾天的數(shù)據(jù)。討論方案：直接長久存儲。
表schema：一般按天創(chuàng)建分區(qū)，partitioned by 一般都是按照天進行存放。
庫與表命名。庫名：ods,表名：初步考慮格式為ods日期業(yè)務(wù)表名,待定。
hive的外部表,對應(yīng)的是業(yè)務(wù)表。
hive外部表,存放數(shù)據(jù)的文件可以不是在hive的hdfs默認(rèn)的位置,并且hive對應(yīng)的表刪除時,相應(yīng)的數(shù)據(jù)文件并不會被刪除.這樣對于企業(yè)開發(fā)來說,可以防止因為刪除表的操作而把寶貴的數(shù)據(jù)刪除掉hive的業(yè)務(wù)表,則相反.數(shù)據(jù)文件存放在hive對應(yīng)的默認(rèn)位置,表刪除時,對應(yīng)文件也會被刪除掉。
02 數(shù)倉層（DW,data warehouse）
數(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ù)，例如保存10年的數(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)建維度表。
明細(xì)數(shù)據(jù)層（DWD，Data Warehouse Detail）：以業(yè)務(wù)過程作為建模驅(qū)動，基于每個具體的業(yè)務(wù)過程特點，構(gòu)建最細(xì)粒度的明細(xì)事實表?？蓪⒛承┲匾獙傩宰侄巫鲞m當(dāng)冗余，也即寬表化處理。
匯總數(shù)據(jù)層（DWS，Data Warehouse Summary）：以分析的主題對象作為建模驅(qū)動，基于上層的應(yīng)用和產(chǎn)品的指標(biāo)需求，構(gòu)建公共粒度的匯總指標(biāo)表。以寬表化手段物理化模型，構(gòu)建命名規(guī)范、口徑一致的統(tǒng)計指標(biāo)，為上層提供公共指標(biāo)，建立匯總寬表、明細(xì)事實表。
主題域：面向業(yè)務(wù)過程，將業(yè)務(wù)活動事件進行抽象的集合，如下單、支付、退款都是業(yè)務(wù)過程。針對公共明細(xì)層（DWD）進行主題劃分。
數(shù)據(jù)域：面向業(yè)務(wù)分析，將業(yè)務(wù)過程或者維度進行抽象的集合。針對公共匯總層（DWS） 進行數(shù)據(jù)域劃分。
DWD 層是以業(yè)務(wù)過程為驅(qū)動。
DWS 層、DWT 層和 ADS 層都是以需求為驅(qū)動。
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ǔ)層，存儲的是客觀數(shù)據(jù)，一般用作中間層，可以認(rèn)為是大量指標(biāo)的數(shù)據(jù)層。
DWS：data warehouse service 數(shù)據(jù)服務(wù)層，基于DWB上的基礎(chǔ)數(shù)據(jù)，整合匯總成分析某一個主題域的服務(wù)數(shù)據(jù)層，一般是寬表。用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。
用戶行為，輕度聚合
主要對ODS/DWD層數(shù)據(jù)做一些輕度的匯總。
1）公共維度層（DIM，Dimension）
DIM：這一層比較單純，舉個例子就明白，比如國家代碼和國家名、地理位置、中文名、國旗圖片等信息就存在DIM層中。
基于維度建模理念思想，建立整個企業(yè)的一致性維度。降低數(shù)據(jù)計算口徑和算法不統(tǒng)一風(fēng)險。
公共維度匯總層（DIM）主要由維度表（維表）構(gòu)成。維度是邏輯概念，是衡量和觀察業(yè)務(wù)的角度。維表是根據(jù)維度及其屬性將數(shù)據(jù)平臺上構(gòu)建的表物理化的表，采用寬表設(shè)計的原則。因此，構(gòu)建公共維度匯總層（DIM）首先需要定義維度。
高基數(shù)維度數(shù)據(jù)：一般是用戶資料表、商品資料表類似的資料表。數(shù)據(jù)量可能是千萬級或者上億級別。
低基數(shù)維度數(shù)據(jù)：一般是配置表，比如枚舉值對應(yīng)的中文含義，或者日期維表。數(shù)據(jù)量可能是個位數(shù)或者幾千幾萬。
設(shè)計維表：
完成維度定義后，您就可以對維度進行補充，進而生成維表了。維表的設(shè)計需要注意：
建議維表單表信息不超過1000萬條。
維表與其他表進行Join時，建議您使用Map Join
避免過于頻繁的更新維表的數(shù)據(jù)。緩慢變化維：拉鏈表
公共維度匯總層（DIM）維表規(guī)范
公共維度匯總層（DIM）維表命名規(guī)范：dim_{業(yè)務(wù)板塊名稱/pub}_{維度定義}[_{自定義命名標(biāo)簽}]，所謂pub是與具體業(yè)務(wù)板塊無關(guān)或各個業(yè)務(wù)板塊都可公用的維度，如時間維度。
例如：公共區(qū)域維表dim_pub_area 商品維表dim_asale_itm
事實表中一條記錄所表達的業(yè)務(wù)細(xì)節(jié)程度被稱為粒度。通常粒度可以通過兩種方式來表述：一種是維度屬性組合所表示的細(xì)節(jié)程度，一種是所表示的具體業(yè)務(wù)含義。通透！數(shù)據(jù)倉庫領(lǐng)域常見建模方法及實例演示。
建模方式及原則
需要構(gòu)建維度模型，一般采用星型模型，呈現(xiàn)的狀態(tài)一般為星座模型（由多個事實表組合，維表是公共的，可被多個事實表共享）；
為支持?jǐn)?shù)據(jù)重跑可額外增加數(shù)據(jù)業(yè)務(wù)日期字段，可按日進行分表，用增量ODS層數(shù)據(jù)和前一天DWD相關(guān)表進行merge處理？
粒度是一行信息代表一次行為，例如一次下單。
維度建模步驟
選擇業(yè)務(wù)過程：在業(yè)務(wù)系統(tǒng)中，挑選感興趣的業(yè)務(wù)線，比如下單業(yè)務(wù)，支付業(yè)務(wù)，退款業(yè)務(wù)，物流業(yè)務(wù)，一條業(yè)務(wù)線對應(yīng)一張事實表。如果是中小公司，盡量把所有業(yè)務(wù)過程都選擇。DWD如果是大公司（1000多張表），選擇和需求相關(guān)的業(yè)務(wù)線。
聲明粒度：數(shù)據(jù)粒度指數(shù)據(jù)倉庫的數(shù)據(jù)中保存數(shù)據(jù)的細(xì)化程度或綜合程度的級別。聲明粒度意味著精確定義事實表中的一行數(shù)據(jù)表示什么，應(yīng)該盡可能選擇最小粒度，以此來應(yīng)各種各樣的需求。典型的粒度聲明如下：訂單當(dāng)中的每個商品項作為下單事實表中的一行，粒度為每次。每周的訂單次數(shù)作為一行，粒度為每周。每月的訂單次數(shù)作為一行，粒度為每月。如果在DWD層粒度就是每周或者每月，那么后續(xù)就沒有辦法統(tǒng)計細(xì)粒度的指標(biāo)了。所以建議采用最小粒度。
確定維度：維度的主要作用是描述業(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層是以業(yè)務(wù)過程為驅(qū)動。DWS層、DWT層和ADS層都是以需求為驅(qū)動，和維度建模已經(jīng)沒有關(guān)系了。DWS和DWT都是建寬表，按照主題去建表。主題相當(dāng)于觀察問題的角度。對應(yīng)著維度表。
關(guān)于主題：
數(shù)據(jù)倉庫中的數(shù)據(jù)是面向主題組織的，主題是在較高層次上將企業(yè)信息系統(tǒng)中的數(shù)據(jù)進行綜合、歸類和分析利用的一個抽象概念，每一個主題基本對應(yīng)一個宏觀的分析領(lǐng)域。如財務(wù)分析就是一個分析領(lǐng)域，因此這個數(shù)據(jù)倉庫應(yīng)用的主題就為“財務(wù)分析”。
關(guān)于主題域：
主題域通常是聯(lián)系較為緊密的數(shù)據(jù)主題的集合?？梢愿鶕?jù)業(yè)務(wù)的關(guān)注點，將這些數(shù)據(jù)主題劃分到不同的主題域(也說是對某個主題進行分析后確定的主題的邊界)
關(guān)于主題域的劃分：
主題域的確定必須由最終用戶（業(yè)務(wù)）和數(shù)據(jù)倉庫的設(shè)計人員共同完成的， 而在劃分主題域時，大家的切入點不同可能會造成一些爭論、重構(gòu)等的現(xiàn)象，考慮的點可能會是下方的某些方面：
- 按照業(yè)務(wù)或業(yè)務(wù)過程劃分：比如一個靠銷售廣告位置的門戶網(wǎng)站主題域可能會有廣告域，客戶域等，而廣告域可能就會有廣告的庫存，銷售分析、內(nèi)部投放分析等主題；
- 根據(jù)需求方劃分：比如需求方為財務(wù)部，就可以設(shè)定對應(yīng)的財務(wù)主題域，而財務(wù)主題域里面可能就會有員工工資分析，投資回報比分析等主題；
- 按照功能或應(yīng)用劃分：比如微信中的朋友圈數(shù)據(jù)域、群聊數(shù)據(jù)域等，而朋友圈數(shù)據(jù)域可能就會有用戶動態(tài)信息主題、廣告主題等；
- 按照部門劃分：比如可能會有運營域、技術(shù)域等，運營域中可能會有工資支出分析、活動宣傳效果分析等主題；
總而言之，切入的出發(fā)點邏輯不一樣，就可以存在不同的劃分邏輯。在建設(shè)過程中可采用迭代方式，不糾結(jié)于一次完成所有主題的抽象，可先從明確定義的主題開始，后續(xù)逐步歸納總結(jié)成自身行業(yè)的標(biāo)準(zhǔn)模型。
主題：當(dāng)事人、營銷、財務(wù)、合同協(xié)議、機構(gòu)、地址、渠道、 產(chǎn)品、
金融業(yè)務(wù)主題有哪些 ：可分為四個主題：
- 用戶主題（用戶年齡、性別、收貨地址、電話、省份等）
- 交易主題（訂單數(shù)據(jù)、賬單數(shù)據(jù)等）
- 風(fēng)控主題（用戶的風(fēng)控等級，第三方征信數(shù)據(jù)）
- 營銷主題（營銷活動名單，活動配置信息等）
2）DWD（data warehouse detail）數(shù)據(jù)明細(xì)層，明細(xì)粒度事實層
DWD是業(yè)務(wù)層與數(shù)據(jù)倉庫的隔離層， 這一層主要解決一些數(shù)據(jù)質(zhì)量問題和數(shù)據(jù)的完整度問題。
明細(xì)表用于存儲ODS層原始表轉(zhuǎn)換過來的明細(xì)數(shù)據(jù)，DWD 層的數(shù)據(jù)應(yīng)該是一致的、準(zhǔn)確的、干凈的數(shù)據(jù)，即對源系統(tǒng)數(shù)據(jù)ODS層數(shù)據(jù)進行清洗（去除空值，臟數(shù)據(jù)，超過極限范圍的數(shù)據(jù)，行式存儲改為列存儲，改壓縮格式）、規(guī)范化、維度退化、脫敏等操作。比如用戶的資料信息來自于很多不同表，而且經(jīng)常出現(xiàn)延遲丟數(shù)據(jù)等問題，為了方便各個使用方更好的使用數(shù)據(jù)，我們可以在這一層做一個屏蔽。這一層也包含統(tǒng)一的維度數(shù)據(jù)。
明細(xì)粒度事實層（DWD）：以業(yè)務(wù)過程作為建模驅(qū)動，基于每個具體的業(yè)務(wù)過程特點，構(gòu)建最細(xì)粒度的明細(xì)層事實表?？梢越Y(jié)合企業(yè)的數(shù)據(jù)使用特點，將明細(xì)事實表的某些重要維度屬性字段做適當(dāng)冗余，即寬表化處理。明細(xì)粒度事實層的表通常也被稱為邏輯事實表。
負(fù)責(zé)數(shù)據(jù)的最細(xì)粒度的數(shù)據(jù)，在DWD層基礎(chǔ)上，進行輕度匯總，結(jié)合常用維度（時間，地點，組織層級，用戶，商品等）
該層一般保持和ODS層一樣的數(shù)據(jù)粒度，并且提供一定的數(shù)據(jù)質(zhì)量保證，在ODS的基礎(chǔ)上對數(shù)據(jù)進行加工處理，提供更干凈的數(shù)據(jù)。同時，為了提高數(shù)據(jù)明細(xì)層的易用性，該層會采用一些維度退化手法，當(dāng)一個維度沒有數(shù)據(jù)倉庫需要的任何數(shù)據(jù)時，就可以退化維度，將維度退化至事實表中，減少事實表和維表的關(guān)聯(lián)。
例如：
訂單id,這種量級很大的維度，沒必要用一張維度表來進行存儲，而我們一般在進行數(shù)據(jù)分析時訂單id又非常重要，所以我們將訂單id冗余在事實表中，這種維度就是退化維度。
這一層的數(shù)據(jù)一般是遵循數(shù)據(jù)庫第三范式或者維度建模，其數(shù)據(jù)粒度通常和 ODS 的粒度相同。在 PDW 層會保存 BI 系統(tǒng)中所有的歷史數(shù)據(jù)，例如保存10年的數(shù)據(jù)。
數(shù)據(jù)在裝入本層前需要做以下工作：去噪、去重、提臟、業(yè)務(wù)提取、單位統(tǒng)一、砍字段、業(yè)務(wù)判別。
清洗的數(shù)據(jù)種類：
- 不完整數(shù)據(jù)
- 錯誤數(shù)據(jù)
- 重復(fù)的數(shù)據(jù)
數(shù)據(jù)清洗的任務(wù)是過濾那些不符合要求的數(shù)據(jù)，將過濾的結(jié)果交給業(yè)務(wù)主管部門，確認(rèn)是否過濾掉還是由業(yè)務(wù)單位修正之后再進行抽取。
DWD層做了哪些事？
①數(shù)據(jù)清洗過濾
去除廢棄字段,去除格式錯誤的信息
去除丟失了關(guān)鍵字段的信息
過濾核心字段無意義的數(shù)據(jù)，比如訂單表中訂單id為null，支付表中支付id為空
對手機號、身份證號等敏感數(shù)據(jù)脫敏
去除不含時間信息的數(shù)據(jù)(這個看公司具體業(yè)務(wù),但一般數(shù)據(jù)中都會帶上時間戳,這樣方便后續(xù)處理時,進行時間維度上信息分析處理和提取)
有些公司還會在這一層將數(shù)據(jù)打平,不過這具體要看業(yè)務(wù)需求.這是因為kylin適合處理展平后數(shù)據(jù),不適合處理嵌套的表數(shù)據(jù)信息
有些公司還會將數(shù)據(jù)session做切割,這個一般是App的日志數(shù)據(jù),其他業(yè)務(wù)場景不一定適合.這是因為app有進入后臺模式,例如用戶上午打開app用了10分鐘,然后app切入后臺,晚上再打開,這時候session還是一個,實際上應(yīng)該做切割才對.(也有公司會記錄app進入后臺,再度進入前臺的記錄,這樣來做session切割)
②數(shù)據(jù)映射,轉(zhuǎn)換
將GPS經(jīng)緯度轉(zhuǎn)換為省市區(qū)詳細(xì)地址。業(yè)界常見GPS快速查詢一般將地理位置知識庫使用geohash映射,然后將需要比對的GPS轉(zhuǎn)換為geohash后跟知識庫中g(shù)eohash比對,查找出地理位置信息當(dāng)然,也有公司使用open api,如高德地圖,百度地圖的api進行GPS和地理位置信息映射,但這個達到一定次數(shù)需要花錢,所以大家都懂的
會將IP地址也轉(zhuǎn)換為省市區(qū)詳細(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冗余在事實表
清洗掉多少數(shù)據(jù)算合理：1萬條數(shù)據(jù)清洗掉1條。
合理表數(shù)：一萬張表變?yōu)槿埍?，三千張表變?yōu)橐磺埍?/p>
明細(xì)粒度事實表設(shè)計原則：
- 一個明細(xì)粒度事實表僅和一個維度關(guān)聯(lián)。
- 盡可能包含所有與業(yè)務(wù)過程相關(guān)的事實 。
- 只選擇與業(yè)務(wù)過程相關(guān)的事實。
- 分解不可加性事實為可加的組件。
- 在選擇維度和事實之前必須先聲明粒度。
- 在同一個事實表中不能有多種不同粒度的事實。
- 事實的單位要保持一致。粒度
- 謹(jǐn)慎處理Null值。
- 使用退化維度提高事實表的易用性。
方案
討論方案：數(shù)據(jù)的合成方式為：
全量：每天把明細(xì)層的前天全量數(shù)據(jù)和昨天新數(shù)據(jù)合成一個新的數(shù)據(jù)表，覆蓋舊表。同時使用歷史鏡像，按周/按月/按年存儲一個歷史鏡像到新表。
日志存儲方式：直接數(shù)據(jù)使用impala外表，parquet文件格式， 建議使用內(nèi)表，下面幾層都是從impala生成的數(shù)據(jù)，建議都用內(nèi)表+靜態(tài)/動態(tài)分區(qū)。
表schema：一般按天創(chuàng)建分區(qū)，沒有時間概念的按具體業(yè)務(wù)選擇分區(qū)字段。partitioned by 一般都是按照天進行存放。
庫與表命名。庫名：dwd,表名：初步考慮格式為dwd日期業(yè)務(wù)表名,待定。
舊數(shù)據(jù)更新方式：直接覆蓋
明細(xì)粒度事實層（DWD）規(guī)范
命名規(guī)范為：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_ordcrt_trip_di（A電商公司航旅機票訂單下單事實表，日刷新增量） dwd_asale_itm_item_df（A電商商品快照事實表，日刷新全量）。
本教程中，DWD層主要由三個表構(gòu)成：
- 交易商品信息事實表：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）數(shù)據(jù)服務(wù)層，匯總層寬表
基于 DWD 明細(xì)數(shù)據(jù)層，我們會按照一些分析場景、分析實體等去組織我們的數(shù)據(jù)，組織成一些分主題的匯總數(shù)據(jù)層 DWS。
明細(xì)粒度 ==> 匯總粒度
DWS層（數(shù)據(jù)匯總層）寬表，面向主題的匯總，維度相對來說比較少，DWS是根據(jù)DWD層基礎(chǔ)數(shù)據(jù)按各個維度ID進行粗粒度匯總聚合，如按交易來源，交易類型進行匯合。整合匯總成分析某一個主題域的服務(wù)數(shù)據(jù)，一般是寬表。
以DWD為基礎(chǔ)，按天進行輕度匯總。統(tǒng)計各個主題對象的當(dāng)天行為，（例如，購買行為，統(tǒng)計商品復(fù)購率）。
該層數(shù)據(jù)表會相對比較少，大多都是寬表(一張表會涵蓋比較多的業(yè)務(wù)內(nèi)容，表中的字段較多)。按照主題劃分，如訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。
融合多個中間層數(shù)據(jù)，基于主題形成事實表，比如用戶事實表、渠道事實表、終端事實表、資產(chǎn)事實表等等，事實表一般是寬表，在本層上實現(xiàn)企業(yè)級數(shù)據(jù)的一致性。
首先劃分業(yè)務(wù)主題，將主題劃分為銷售域、庫存域、客戶域、采購域 等，其次就是 確定每個主題域的事實表和維度表。通常根據(jù)業(yè)務(wù)需求，劃分成流量、訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。
最近一天某個類目（例如：廚具）商品在各省的銷售總額、該類目Top10銷售額商品名稱、各省用戶購買力分布。因此，我們可以以最終交易成功的商品、類目、買家等角度對最近一天的數(shù)據(jù)進行匯總。
比如用戶每個時間段在不同登錄ip購買的商品數(shù)等。這里做一層輕度的匯總會讓計算更加的高效，在此基礎(chǔ)上如果計算僅7天、30天、90天的行為的話會快很多。我們希望80%的業(yè)務(wù)都能通過我們的DWS層計算，而不是ODS。
DWS層做了哪些事？
dws將dwd層的數(shù)據(jù)按主題進行匯總，按照主題放到一個表中，
比如用戶主題下會將用戶注冊信息、用戶收貨地址、用戶的征信數(shù)據(jù)放到同一張表中，而這些在dwd層是對應(yīng)多張表的,按照業(yè)務(wù)劃分，如流量、訂單、用戶等，生成字段比較多的寬表
主題建模,圍繞某一個業(yè)務(wù)主題進行數(shù)據(jù)建模,將相關(guān)數(shù)據(jù)抽離提取出來.
如：
- 將流量會話按照天,月進行聚合
- 將每日新用戶進行聚合
- 將每日活躍用戶進行聚合
- 維度建模,其實也差不多,不過是根據(jù)業(yè)務(wù)需要,提前將后續(xù)數(shù)據(jù)查詢處理需要的維度數(shù)據(jù)抽離處理出來,方便后續(xù)查詢使用.
- 如將運營位維度數(shù)據(jù)聚合
- 將渠道拉新維度數(shù)據(jù)聚合
①DWS層每個主題1-3張寬表（處理100-200個指標(biāo) 70%以上的需求）
具體寬表名稱：用戶行為寬表，用戶購買商品明細(xì)行為寬表，商品寬表， 物流寬表、 售后等。
②哪個寬表最寬？大概有多少個字段？
最寬的是用戶行為寬表。大概有60-200個字段
③具體用戶行為寬表字段名稱
評論、打賞、收藏、關(guān)注--商品、關(guān)注--人、點贊、分享、好價爆料、文章發(fā)布、活躍、簽到、補簽卡、幸運屋、禮品、金幣、電商點擊、gmv
④分析過的指標(biāo)
日活、月活、周活、留存、留存率、新增（日、周、年）、轉(zhuǎn)化率、流失、回流、七天內(nèi)連續(xù) 3 天登錄（點贊、收藏、評價、購買、加購、下單、活動）、連續(xù) 3 周（月）登錄、GMV（成交金額，下單）、復(fù)購率、復(fù)購率排行、點贊、評論、收藏、領(lǐng)優(yōu)惠價人數(shù)、使用優(yōu)惠價、沉默、值不值得買、退款人數(shù)、退款率 topn 熱門商品
- 活躍
日活：100 萬 ；月活：是日活的 2-3 倍 300 萬
總注冊的用戶多少？1000 萬-3000 萬之間
- GMV，哪個商品賣的最好？每天下單量多少？
GMV：每天 10 萬訂單 （50 – 100 元） 500 萬-1000 萬
100萬的日活每天大概有10萬人購買，平均每人消費100元，一天的GMV在1000萬
10%-20% 100 萬-200 萬
- 復(fù)購率
某日常商品復(fù)購；（手紙、面膜、牙膏）10%-20%
電腦、顯示器、手表 1%
- 轉(zhuǎn)化率
商品詳情 =》 加購物車 =》下單 =》 支付
5%-10% 60-70% 90%-95%
- 留存率
1/2/3、周留存、月留存
搞活動：10-20%
方案：
概念：又稱數(shù)據(jù)集市或?qū)挶?。按照業(yè)務(wù)劃分，如流量、訂單、用戶等，生成字段比較多的寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。
數(shù)據(jù)生成方式：由輕度匯總層和明細(xì)層數(shù)據(jù)計算生成。
日志存儲方式：使用impala內(nèi)表，parquet文件格式。
表schema：一般按天創(chuàng)建分區(qū)，沒有時間概念的按具體業(yè)務(wù)選擇分區(qū)字段。
庫與表命名。庫名：dws, 表名：初步考慮格式為：dws日期業(yè)務(wù)表名,待定。
舊數(shù)據(jù)更新方式：直接覆蓋
公共匯總事實表規(guī)范
公共匯總事實表命名規(guī)范：dws_{業(yè)務(wù)板塊縮寫/pub}_{數(shù)據(jù)域縮寫}_{數(shù)據(jù)粒度縮寫}[_{自定義表命名標(biāo)簽縮寫}]_{統(tǒng)計時間周期范圍縮寫}。關(guān)于統(tǒng)計實際周期范圍縮寫，缺省情況下，離線計算應(yīng)該包括最近一天（_1d），最近N天（_nd）和歷史截至當(dāng)天（_td）三個表。如果出現(xiàn)_nd的表字段過多需要拆分時，只允許以一個統(tǒng)計周期單元作為原子拆分。即一個統(tǒng)計周期拆分一個表，例如最近7天（_1w）拆分一個表。不允許拆分出來的一個表存儲多個統(tǒng)計周期。
對于小時表（無論是天刷新還是小時刷新），都用_hh來表示。對于分鐘表（無論是天刷新還是小時刷新），都用_mm來表示。
舉例如下：
dws_asale_trd_byr_subpay_1d（買家粒度交易分階段付款一日匯總事實表）
dws_asale_trd_byr_subpay_td（買家粒度分階段付款截至當(dāng)日匯總表）
dws_asale_trd_byr_cod_nd（買家粒度貨到付款交易匯總事實表）
dws_asale_itm_slr_td（賣家粒度商品截至當(dāng)日存量匯總表）
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 '地址',
--下單數(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)計'
) 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',
--商品信息，產(chǎn)品信息
item_title STRING COMMENT '商品名稱',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
--mord_prov STRING COMMENT '收貨人省份',
--商品售出金額匯總
confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天訂單已經(jīng)確認(rèn)收貨的金額總和'
)
COMMENT '商品粒度交易最近一天匯總事實表'
PARTITIONED BY (ds STRING COMMENT '分區(qū)字段YYYYMMDD')
LIFECYCLE 36000;
問：數(shù)據(jù)集市層是不是沒地方放了，各個業(yè)務(wù)的數(shù)據(jù)集市表是應(yīng)該在 dws 還是在 app?
答：這個問題不太好回答，我感覺主要就是明確一下數(shù)據(jù)集市層是干什么的，如果你的數(shù)據(jù)集市層放的就是一些可以供業(yè)務(wù)方使用的寬表，放在 app 層就行。如果你說的數(shù)據(jù)集市層是一個比較泛一點的概念，那么其實 dws、dwd、app 這些合起來都算是數(shù)據(jù)集市的內(nèi)容。
03 應(yīng)用層（ADS）applicationData Service應(yīng)用數(shù)據(jù)服務(wù)
數(shù)據(jù)應(yīng)用層（ADS，Application Data Store）：存放數(shù)據(jù)產(chǎn)品個性化的統(tǒng)計指標(biāo)數(shù)據(jù)，報表數(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ù)的時間跨度來說，通常是DW層的一部分，主要的目的是為了滿足用戶分析的需求，而從分析的角度來說，用戶通常只需要分析近幾年的即可。從數(shù)據(jù)的廣度來說，仍然覆蓋了所有業(yè)務(wù)數(shù)據(jù)。
在 DWS 之上，我們會面向應(yīng)用場景去做一些更貼近應(yīng)用的 APP 應(yīng)用數(shù)據(jù)層，這些數(shù)據(jù)應(yīng)該是高度匯總的，并且能夠直接導(dǎo)入到我們的應(yīng)用服務(wù)去使用。
應(yīng)用層(ADS)：應(yīng)用層主要是各個業(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ù)，一般會放在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)常說的報表數(shù)據(jù)，或者說那種大寬表，一般就放在這里。包括前端報表、分析圖表、KPI、儀表盤、OLAP、專題等分析，面向最終結(jié)果用戶；
概念：應(yīng)用層是根據(jù)業(yè)務(wù)需要，由前面三層數(shù)據(jù)統(tǒng)計而出的結(jié)果，可以直接提供查詢展現(xiàn)，或?qū)胫罬ysql中使用。
數(shù)據(jù)生成方式：由明細(xì)層、輕度匯總層，數(shù)據(jù)集市層生成，一般要求數(shù)據(jù)主要來源于集市層。
日志存儲方式：使用impala內(nèi)表，parquet文件格式。
表schema：一般按天創(chuàng)建分區(qū)，沒有時間概念的按具體業(yè)務(wù)選擇分區(qū)字段。
庫與表命名。庫名：暫定ads，另外根據(jù)業(yè)務(wù)不同，不限定一定要一個庫。
舊數(shù)據(jù)更新方式：直接覆蓋。
ADS 層復(fù)購率統(tǒ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 '互動日期',
①如何分析用戶活躍？
在啟動日志中統(tǒng)計不同設(shè)備 id 出現(xiàn)次數(shù)。
②如何分析用戶新增?
用活躍用戶表 left join 用戶新增表，用戶新增表中 mid 為空的即為用戶新增。
③如何分析用戶 1 天留存？
留存用戶=前一天新增 join 今天活躍
用戶留存率=留存用戶/前一天新增
④如何分析沉默用戶？
(登錄時間為 7 天前,且只出現(xiàn)過一次)
按照設(shè)備 id 對日活表分組，登錄次數(shù)為 1，且是在一周前登錄。
⑤如何分析本周回流用戶？
本周活躍 left join 本周新增 left join 上周活躍，且本周新增 id 和上周活躍 id 都為 null。
⑥如何分析流失用戶？
(登錄時間為 7 天前)
按照設(shè)備 id 對日活表分組，且七天內(nèi)沒有登錄過。
⑦如何分析最近連續(xù) 3 周活躍用戶數(shù)？
按照設(shè)備 id 對周活進行分組，統(tǒng)計次數(shù)大于 3 次。
⑧如何分析最近七天內(nèi)連續(xù)三天活躍用戶數(shù)？
- 查詢出最近 7 天的活躍用戶，并對用戶活躍日期進行排名
- 計算用戶活躍日期及排名之間的差值
- 對同用戶及差值分組，統(tǒng)計差值個數(shù)
- 將差值相同個數(shù)大于等于 3 的數(shù)據(jù)取出，然后去重，即為連續(xù) 3 天及以上活躍的用戶
7 天連續(xù)收藏、點贊、購買、加購、付款、瀏覽、商品點擊、退貨
1 個月連續(xù) 7 天
連續(xù)兩周
TMP：每一層的計算都會有很多臨時表，專設(shè)一個DW TMP層來存儲我們數(shù)據(jù)倉庫的臨時表。
04 層次調(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
