1、1、數據倉庫基本概念 1.1、主題（Subject ） 主題就是指我們所要分析的具體方面。例如：某年某月某地區某機型某款App的安裝 情況。主題有兩個元素：一是各個分析角度（維度），如時間位置；二是要分析的具體量度， 該量度一般通過數值體現，如 App安裝量。 1.2、維(Dimension ) 維是用于從不同角度描述事物特征的，一般維都會有多層（Level：級別），每個Level 都會包含一些共有的或特有的屬性（Attribute），可以用下圖來展示下維的結構和組成： 以時間維為例，時間維一般會包含年、季、月、日這幾個Level,每個Level 一般都會有 ID、NAME、DESCRIPTI

2、ON這幾個公共屬性，這幾個公共屬性不僅適用于時間維，也同樣表 現在其它各種不同類型的維。 Attributes 1.3、分層（Hierarchy ） OLAP需要基于有層級的自上而下的鉆取，或者自下而上地聚合。所以我們一般會在維 的基礎上再次進行分層，維、分層、層級的關系如下圖： 每一級之間可能是附屬關系（如市屬于省、省屬于國家），也可能是順序關系（如天周 年），如下圖所示： 1.4、量度 量度就是我們要分析的具體的技術指標，諸如年銷售額之類。它們一般為數值型數據。 我們或者將該數據匯總， 或者將該數據取次數、 獨立次數或取最大最小值等， 這樣的數據稱 為量度。 1.5、粒度 數據的細分層度，

3、例如按天分按小時分。 1.6、事實表和維表 事實表是用來記錄分析的內容的全量信息的，包含了每個事件的具體要素，以及具體發 生的事情。事實表中存儲數字型ID以及度量信息。 維表則是對事實表中事件的要素的描述信息，就是你觀察該事務的角度，是從哪個角度 去觀察這個內容的。 事實表和維表通過ID相關聯，如圖所示： 地域4 石 m 他址 商品銷售事實表 PK,FK1 PKrFK2 PKJK3 PK.FK4 時即r 地關D 用口ID 產鈕D 卻D 駒軸* 賣忖金熨 產曲傑 PK 產黑他 嚴話屬性 I用戶維 N U 用廠表空 用戶名 玄村5 左時方式1 1.7、星形/雪花形/事實星座 這三者就是數據倉庫多維

4、數據模型建模的模式 上圖所示就是一個標準的星形模型。 雪花形就是在維度下面又細分出維度， 這樣切分是為了使表結構更加規范化。 雪花模式 可以減少冗余，但是減少的那點空間和事實表的容量相比實在是微不足道，而且多個表聯結 操作會降低性能，所以一般不用雪花模式設計數據倉庫。 事實星座模式就是星形模式的集合，包含星形模式，也就包含多個事實表。 1.8 、企業級數據倉庫 / 數據集市 企業級數據倉庫： 突出大而全， 不論是細致數據和聚合數據它全都有， 設計時使用事實 星座模式 數據集市： 可以看做是企業級數據倉庫的一個子集， 它是針對某一方面的數據設計的數 據倉庫， 例如為公司的支付業務設計一個單獨的數

5、據集市。由于數據集市沒有進行企業級的 設計和規劃，所以長期來看，它本身的集成將會極其復雜。其數據來源有兩種，一種是直接 從原生數據源得到，另一種是從企業數據倉庫得到。設計時使用星形模型 2、數據倉庫設計步驟 2.1 、確定主題 主題與業務密切相關， 所以設計數倉之前應當充分了解業務有哪些方面的需求， 據此確 定主題。 2.2 、確定量度 在確定了主題以后， 我們將考慮要分析的技術指標， 諸如年銷售額之類。 量度是要統計 的指標，必須事先選擇恰當，基于不同的量度將直接產生不同的決策結果。 2.3 、確定數據粒度 考慮到量度的聚合程度不同，我們將采用“最小粒度原則” ，即將量度的粒度設置到最 小。

6、例如如果知道某些數據細分到天就好了，那么設置其粒度到天；但是如果不確定的話， 就將粒度設置為最小，即毫秒級別的。 2.4 、確定維度 設計各個維度的主鍵、層次、層級，盡量減少冗余。 2.5 、創建事實表 事實表中將存在維度代理鍵和各量度， 而不應該存在描述性信息， 即符合“瘦高原則” ， 即要求事實表數據條數盡量多 （粒度最小 ），而描述性信息盡量少。 3、數據倉庫-全量表 全量表：保存用戶所有的數據（包括新增與歷史數據） 增量表：只保留當前新增的數據 快照表：按日分區，記錄截止數據日期的全量數據 切片表：切片表根據基礎表，往往只反映某一個維度的相應數據。其表結構與基礎表結 構相同，但數據往往

7、只有某一維度，或者某一個事實條件的數據 3.1、更新插入算法 更新插入（主表）算法適用于保留最新狀態表的處理。 案例：銀行賬戶余額表，全表表大約8000萬，非結息日每日變動 100萬，結息日變動 2000 萬。 非結息日：它是指根據主鍵（或指定字段）進行數據對比，如果增量表存在記錄，則更新 原全量表，否則插入數據。 ETL更新的優化？ Merge? 結息日：新建空表，它是指根據主鍵（或指定字段）進行數據對比，首先插入原全量表與 增量表無法匹配的非變更數據，再次插入可以匹配的增量表數據，最后補齊增量表與全量表 無法匹配的增量數據。 3.2、直接追加算法 直接追加算法是指增量數據直接追加到目標表中

8、，此算法適合流水、交易、 事件、話單 等增量且不修改的數據。 由于歷史信息表數據量過于龐大，往往在數據庫設計中將引入分區表的邏輯來處理，具 體實現邏輯自查。 3.3、全量歷史表算法 拉鏈表。 4、數據倉庫-拉鏈表 拉鏈表：數據倉庫設計中表存儲數據的方式而定義的，顧名思義，所謂拉鏈，就是記錄 歷史。記錄一個事物從開始，一直到當前狀態的所有變化的信息。 我們先看一個示例，這就是一張拉鏈表， 存儲的是用戶的最基本信息以及每條記錄的生 命周期。我們可以使用這張表拿到最新的當天的最新數據以及之前的歷史數據。 用戶錨弓 手機號田 t start date t end da It 2017-01-01 00

9、1 1HU1 2017-01-01 9999-12-il 2017-01-01 002 222222 ?07-01-01 2017-01-01 2017 01-OL 002 2珈玷 2017-01 02 9999-12-31 2017-01-OL 00i 333333 ?017-01 01 9995-_L2-il 20X7-01-DL 004 444444 2017-01-01 2017 0101 2017-01-01 (XM 2017-01 02 2017-01-02 2017 01-OL 004 4524i2 201701 03 9999-12 SI 2017-01-02 005 5555

10、55 Z017-01- 02 2017-01-02 2017-01-02 005 115115 ?017-D1-03 12-1 2017-01-03 6GG6 ) ods 層的 user_update 表 然后我們還需要一張用戶每日更新表， 前面已經分析過該如果得到這張表， 現在我們假 設它已經存在。 CREATEEXTERNALTABLEods.user_update ( user_num STRINGCOMMENT用戶編號: mobile STRINGCOMMENT手機號碼: reg_date STRINGCOMMENT注冊日期 COMMENT每日用戶資料更新表 PARTITIONEDBY

11、(dt string) ROWFORMATDELIMITEDFIELDSTERMINATEDBYt LINESTERMINATEDBYn STOREDASORC LOCATION/ods/user_update; ) 拉鏈表 現在我們創建一張拉鏈表： CREATEEXTERNALTABLEdws.user_his ( user_num STRINGCOMMENT 用戶編號 , mobile STRINGCOMMENT 手機號碼 , reg_date STRINGCOMMENT 用戶編號 , t_start_date , t_end_date COMMENT 用戶資料拉鏈表 ROW FORMAT

12、DELIMITEDFIELDSTERMINATEDBYt LINESTERMINATEDBYn STOREDASORC LOCATION/dws/user_his; ) 實現 sql 語句 然后初始化的 sql 就不寫了，其實就相當于是拿一天的 ods 層用戶表過來就行，我們寫 一下每日的更新語句。 現在我們假設我們已經已經初始化了 2017-01-01 的日期，然后需要更新 2017-01-02 那 一天的數據，我們有了下面的 Sql。 然后把兩個日期設置為變量就可以了。 INSERTOVERWRITETABLEdws.user_his SELECT* FROM ( SELECTA.user

13、_num, A.mobile, A.reg_date, A.t_start_time, CASE WHEN A.t_end_time = 9999-12-31 AND B.user_num ISNOTNULLTHEN2017-01- 01 ELSEA.t_end_time END ASt_end_time FROM dws.user_his A LEFTJOINods.user_update B ON A.user_num = B.user_num UNION SELECTC.user_num, C.mobile, C.reg_date, 2017 -01 -02 ASt_start_time

14、, 9999 -12 -31 ASt_end_time FROM ods.user_update ASC ) AS T 好了，我們分析了拉鏈表的原理、設計思路、并且在 Hive 環境下實現了一份拉鏈表， 下面對拉鏈表做一些小的補充。 拉鏈表和流水表 流水表存放的是一個用戶的變更記錄， 比如在一張流水表中， 一天的數據中， 會存放一 個用戶的每條修改記錄，但是在拉鏈表中只有一條記錄。 這是拉鏈表設計時需要注意的一個粒度問題。 我們當然也可以設置的粒度更小一些， 一 般按天就足夠。 查詢性能 拉鏈表當然也會遇到查詢性能的問題， 比如說我們存放了 5 年的拉鏈數據， 那么這張表 勢必會比較大，當查詢的時候性能就比較低了，個人認為兩個思路來解決： 在一些查詢引擎中，我們對 start_date 和 end_date 做索引，這樣能提高不少性能。 保留部分歷史數據， 比如說我們一張表里面存放全量的拉鏈表數據， 然后再對外暴露一 張只提供近 3 個月數據的拉鏈表。 使用拉鏈表的時候可以不加 t_end_date ，即失效日期，但是加上之后， 能優化很多查詢。 可以加上當前行狀態標識，能快速定位到當前狀態。 在拉鏈表的設計中可以加一些內容， 因為我們