數(shù)據(jù)倉(cāng)庫(kù)多維數(shù)據(jù)模型的設(shè)計(jì).doc

1、數(shù)據(jù)倉(cāng)庫(kù)基本概念1.1、主題（Subject）主題就是指我們所要分析的具體方面。例如：某年某月某地區(qū)某機(jī)型某款A(yù)pp的安裝情況。主題有兩個(gè)元素：一是各個(gè)分析角度（維度），如時(shí)間位置；二是要分析的具體量度，該量度一般通過(guò)數(shù)值體現(xiàn)，如App安裝量。1.2、維（Dimension）維是用于從不同角度描述事物特征的，一般維都會(huì)有多層（Level：級(jí)別），每個(gè)Level都會(huì)包含一些共有的或特有的屬性（Attribute），可以用下圖來(lái)展示下維的結(jié)構(gòu)和組成：以時(shí)間維為例，時(shí)間維一般會(huì)包含年、季、月、日這幾個(gè)Level，每個(gè)Level一般都會(huì)有ID、NAME、DESCRIPTION這幾個(gè)公共屬性，這幾個(gè)公共屬性不僅適用于時(shí)間維，也同樣表現(xiàn)在其它各種不同類型的維。1.3、分層（Hierarchy）OLAP需要基于有層級(jí)的自上而下的鉆取，或者自下而上地聚合。所以我們一般會(huì)在維的基礎(chǔ)上再次進(jìn)行分層，維、分層、層級(jí)的關(guān)系如下圖：每一級(jí)之間可能是附屬關(guān)系（如市屬于省、省屬于國(guó)家），也可能是順序關(guān)系（如天周年），如下圖所示：1.4、量度量度就是我們要分析的具體的技術(shù)指標(biāo)，諸如年銷售額之類。它們一般為數(shù)值型數(shù)據(jù)。我們或者將該數(shù)據(jù)匯總，或者將該數(shù)據(jù)取次數(shù)、獨(dú)立次數(shù)或取最大最小值等，這樣的數(shù)據(jù)稱為量度。1.5、粒度 數(shù)據(jù)的細(xì)分層度，例如按天分按小時(shí)分。1.6、事實(shí)表和維表事實(shí)表是用來(lái)記錄分析的內(nèi)容的全量信息的，包含了每個(gè)事件的具體要素，以及具體發(fā)生的事情。事實(shí)表中存儲(chǔ)數(shù)字型ID以及度量信息。維表則是對(duì)事實(shí)表中事件的要素的描述信息，就是你觀察該事務(wù)的角度，是從哪個(gè)角度去觀察這個(gè)內(nèi)容的。事實(shí)表和維表通過(guò)ID相關(guān)聯(lián)，如圖所示：1.7、星形/雪花形/事實(shí)星座這三者就是數(shù)據(jù)倉(cāng)庫(kù)多維數(shù)據(jù)模型建模的模式上圖所示就是一個(gè)標(biāo)準(zhǔn)的星形模型。雪花形就是在維度下面又細(xì)分出維度，這樣切分是為了使表結(jié)構(gòu)更加規(guī)范化。雪花模式可以減少冗余，但是減少的那點(diǎn)空間和事實(shí)表的容量相比實(shí)在是微不足道，而且多個(gè)表聯(lián)結(jié)操作會(huì)降低性能，所以一般不用雪花模式設(shè)計(jì)數(shù)據(jù)倉(cāng)庫(kù)。事實(shí)星座模式就是星形模式的集合，包含星形模式，也就包含多個(gè)事實(shí)表。1.8、企業(yè)級(jí)數(shù)據(jù)倉(cāng)庫(kù)/數(shù)據(jù)集市企業(yè)級(jí)數(shù)據(jù)倉(cāng)庫(kù)：突出大而全，不論是細(xì)致數(shù)據(jù)和聚合數(shù)據(jù)它全都有，設(shè)計(jì)時(shí)使用事實(shí)星座模式數(shù)據(jù)集市：可以看做是企業(yè)級(jí)數(shù)據(jù)倉(cāng)庫(kù)的一個(gè)子集，它是針對(duì)某一方面的數(shù)據(jù)設(shè)計(jì)的數(shù)據(jù)倉(cāng)庫(kù)，例如為公司的支付業(yè)務(wù)設(shè)計(jì)一個(gè)單獨(dú)的數(shù)據(jù)集市。由于數(shù)據(jù)集市沒(méi)有進(jìn)行企業(yè)級(jí)的設(shè)計(jì)和規(guī)劃，所以長(zhǎng)期來(lái)看，它本身的集成將會(huì)極其復(fù)雜。其數(shù)據(jù)來(lái)源有兩種，一種是直接從原生數(shù)據(jù)源得到，另一種是從企業(yè)數(shù)據(jù)倉(cāng)庫(kù)得到。設(shè)計(jì)時(shí)使用星形模型2、數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)步驟2.1、確定主題主題與業(yè)務(wù)密切相關(guān)，所以設(shè)計(jì)數(shù)倉(cāng)之前應(yīng)當(dāng)充分了解業(yè)務(wù)有哪些方面的需求，據(jù)此確定主題。2.2、確定量度在確定了主題以后，我們將考慮要分析的技術(shù)指標(biāo)，諸如年銷售額之類。量度是要統(tǒng)計(jì)的指標(biāo)，必須事先選擇恰當(dāng)，基于不同的量度將直接產(chǎn)生不同的決策結(jié)果。2.3、確定數(shù)據(jù)粒度考慮到量度的聚合程度不同，我們將采用“最小粒度原則”，即將量度的粒度設(shè)置到最小。例如如果知道某些數(shù)據(jù)細(xì)分到天就好了，那么設(shè)置其粒度到天；但是如果不確定的話，就將粒度設(shè)置為最小，即毫秒級(jí)別的。2.4、確定維度設(shè)計(jì)各個(gè)維度的主鍵、層次、層級(jí)，盡量減少冗余。2.5、創(chuàng)建事實(shí)表事實(shí)表中將存在維度代理鍵和各量度，而不應(yīng)該存在描述性信息，即符合“瘦高原則”，即要求事實(shí)表數(shù)據(jù)條數(shù)盡量多(粒度最小)，而描述性信息盡量少。3、數(shù)據(jù)倉(cāng)庫(kù)-全量表全量表：保存用戶所有的數(shù)據(jù)（包括新增與歷史數(shù)據(jù)）增量表：只保留當(dāng)前新增的數(shù)據(jù)快照表：按日分區(qū)，記錄截止數(shù)據(jù)日期的全量數(shù)據(jù)切片表：切片表根據(jù)基礎(chǔ)表，往往只反映某一個(gè)維度的相應(yīng)數(shù)據(jù)。其表結(jié)構(gòu)與基礎(chǔ)表結(jié)構(gòu)相同，但數(shù)據(jù)往往只有某一維度，或者某一個(gè)事實(shí)條件的數(shù)據(jù)3.1、更新插入算法更新插入(主表)算法適用于保留最新?tīng)顟B(tài)表的處理。案例：銀行賬戶余額表，全表表大約8000萬(wàn)，非結(jié)息日每日變動(dòng)100萬(wàn)，結(jié)息日變動(dòng)2000萬(wàn)。非結(jié)息日：它是指根據(jù)主鍵(或指定字段)進(jìn)行數(shù)據(jù)對(duì)比，如果增量表存在記錄，則更新原全量表，否則插入數(shù)據(jù)。ETL更新的優(yōu)化？Merge?結(jié)息日：新建空表，它是指根據(jù)主鍵(或指定字段)進(jìn)行數(shù)據(jù)對(duì)比，首先插入原全量表與增量表無(wú)法匹配的非變更數(shù)據(jù)，再次插入可以匹配的增量表數(shù)據(jù)，最后補(bǔ)齊增量表與全量表無(wú)法匹配的增量數(shù)據(jù)。3.2、直接追加算法直接追加算法是指增量數(shù)據(jù)直接追加到目標(biāo)表中，此算法適合流水、交易、事件、話單等增量且不修改的數(shù)據(jù)。由于歷史信息表數(shù)據(jù)量過(guò)于龐大，往往在數(shù)據(jù)庫(kù)設(shè)計(jì)中將引入分區(qū)表的邏輯來(lái)處理，具體實(shí)現(xiàn)邏輯自查。3.3、全量歷史表算法拉鏈表。4、數(shù)據(jù)倉(cāng)庫(kù)-拉鏈表拉鏈表：數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)中表存儲(chǔ)數(shù)據(jù)的方式而定義的，顧名思義，所謂拉鏈，就是記錄歷史。記錄一個(gè)事物從開(kāi)始，一直到當(dāng)前狀態(tài)的所有變化的信息。我們先看一個(gè)示例，這就是一張拉鏈表，存儲(chǔ)的是用戶的最基本信息以及每條記錄的生命周期。我們可以使用這張表拿到最新的當(dāng)天的最新數(shù)據(jù)以及之前的歷史數(shù)據(jù)。在數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)模型設(shè)計(jì)過(guò)程中，經(jīng)常會(huì)遇到下面這種表的設(shè)計(jì)：1、有一些表的數(shù)據(jù)量很大，比如一張用戶表，大約10億條記錄，50個(gè)字段，這種表，即使使用ORC壓縮，單張表的存儲(chǔ)也會(huì)超過(guò)100G（在HDFS使用雙備份或者三備份的話就更大一些）。2、表中的部分字段會(huì)被update更新操作，如用戶聯(lián)系方式，產(chǎn)品的描述信息，訂單的狀態(tài)等等。3、需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息，比如，查看某一個(gè)訂單在歷史某一個(gè)時(shí)間點(diǎn)的狀態(tài)。4、表中的記錄變化的比例和頻率不是很大，比如，總共有10億的用戶，每天新增和發(fā)生變化的有200萬(wàn)左右，變化的比例占的很小。那么對(duì)于這種表我該如何設(shè)計(jì)呢?下面有幾種方案可選：方案一：每天只留最新的一份（比如我們每天用Sqoop抽取最新的一份全量數(shù)據(jù)到Hive中）。方案二：每天保留一份全量的切片數(shù)據(jù)。方案三：使用拉鏈表。4.1、為什么使用拉鏈表現(xiàn)在我們對(duì)前面提到的三種進(jìn)行逐個(gè)的分析。方案一這種方案就不用多說(shuō)了，實(shí)現(xiàn)起來(lái)很簡(jiǎn)單，每天drop掉前一天的數(shù)據(jù)，重新抽一份最新的。優(yōu)點(diǎn)很明顯，節(jié)省空間，一些普通的使用也很方便，不用在選擇表的時(shí)候加一個(gè)時(shí)間分區(qū)什么的。缺點(diǎn)同樣明顯，沒(méi)有歷史數(shù)據(jù)，先翻翻舊賬只能通過(guò)其它方式，比如從流水表里面抽。方案二每天一份全量的切片是一種比較穩(wěn)妥的方案，而且歷史數(shù)據(jù)也在。缺點(diǎn)就是存儲(chǔ)空間占用量太大了，如果對(duì)這邊表每天都保留一份全量，那么每次全量中會(huì)保存很多不變的信息，對(duì)存儲(chǔ)是極大的浪費(fèi)。當(dāng)然我們也可以做一些取舍，比如只保留近一個(gè)月的數(shù)據(jù)?但是，需求是無(wú)恥的，數(shù)據(jù)的生命周期不是我們能完全左右的。拉鏈表在使用上基本兼顧了我們的需求。首先它在空間上做了一個(gè)取舍，雖說(shuō)不像方案一那樣占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是萬(wàn)分之一。其實(shí)它能滿足方案二所能滿足的需求，既能獲取最新的數(shù)據(jù)，也能添加篩選條件也獲取歷史的數(shù)據(jù)。所以我們還是很有必要來(lái)使用拉鏈表的。4.2、拉鏈表的實(shí)現(xiàn)下面我們來(lái)舉個(gè)栗子詳細(xì)看一下拉鏈表。我們先看一下在Mysql關(guān)系型數(shù)據(jù)庫(kù)里的user表中信息變化。在2017-01-01這一天表中的數(shù)據(jù)是：在2017-01-02這一天表中的數(shù)據(jù)是， 用戶002和004資料進(jìn)行了修改，005是新增用戶：在2017-01-03這一天表中的數(shù)據(jù)是， 用戶004和005資料進(jìn)行了修改，006是新增用戶：如果在數(shù)據(jù)倉(cāng)庫(kù)中設(shè)計(jì)成歷史拉鏈表保存該表，則會(huì)有下面這樣一張表，這是最新一天(即2017-01-03)的數(shù)據(jù)：說(shuō)明t_start_date表示該條記錄的生命周期開(kāi)始時(shí)間，t_end_date表示該條記錄的生命周期結(jié)束時(shí)間。t_end_date = 9999-12-31表示該條記錄目前處于有效狀態(tài)。如果查詢當(dāng)前所有有效的記錄，則select * from user where t_end_date = 9999-12-31。如果查詢2017-01-02的歷史快照，則select from user where t_start_date <= 2017-01-02 and t_end_date >= 2017-01-02。(*此處要好好理解，是拉鏈表比較重要的一塊。*)4.3、拉鏈表在Hive中的實(shí)現(xiàn)在現(xiàn)在的大數(shù)據(jù)場(chǎng)景下，大部分的公司都會(huì)選擇以Hdfs和Hive為主的數(shù)據(jù)倉(cāng)庫(kù)架構(gòu)。目前的Hdfs版本來(lái)講，其文件系統(tǒng)中的文件是不能做改變的，也就是說(shuō)Hive的表智能進(jìn)行刪除和添加操作，而不能進(jìn)行update?；谶@個(gè)前提，我們來(lái)實(shí)現(xiàn)拉鏈表。還是以上面的用戶表為例，我們要實(shí)現(xiàn)用戶的拉鏈表。在實(shí)現(xiàn)它之前，我們需要先確定一下我們有哪些數(shù)據(jù)源可以用。我們需要一張ODS層的用戶全量表。至少需要用它來(lái)初始化。每日的用戶更新表。而且我們要確定拉鏈表的時(shí)間粒度，比如說(shuō)拉鏈表每天只取一個(gè)狀態(tài)，也就是說(shuō)如果一天有3個(gè)狀態(tài)變更，我們只取最后一個(gè)狀態(tài)，這種天粒度的表其實(shí)已經(jīng)能解決大部分的問(wèn)題了。ods層的user表現(xiàn)在我們來(lái)看一下我們ods層的用戶資料切片表的結(jié)構(gòu)：CREATEEXTERNALTABLEods.user(user_numSTRINGCOMMENT用戶編號(hào),mobileSTRINGCOMMENT手機(jī)號(hào)碼,reg_dateSTRINGCOMMENT注冊(cè)日期COMMENT用戶資料表PARTITIONEDBY(dtstring)ROWFORMATDELIMITEDFIELDSTERMINATEDBYtLINESTERMINATEDBYnSTOREDASORCLOCATION/ods/user;)ods層的user_update表然后我們還需要一張用戶每日更新表，前面已經(jīng)分析過(guò)該如果得到這張表，現(xiàn)在我們假設(shè)它已經(jīng)存在。CREATEEXTERNALTABLEods.user_update(user_numSTRINGCOMMENT用戶編號(hào),mobileSTRINGCOMMENT手機(jī)號(hào)碼,reg_dateSTRINGCOMMENT注冊(cè)日期COMMENT每日用戶資料更新表PARTITIONEDBY(dtstring)ROWFORMATDELIMITEDFIELDSTERMINATEDBYtLINESTERMINATEDBYnSTOREDASORCLOCATION/ods/user_update;)拉鏈表現(xiàn)在我們創(chuàng)建一張拉鏈表：CREATEEXTERNALTABLEdws.user_his(user_numSTRINGCOMMENT用戶編號(hào),mobileSTRINGCOMMENT手機(jī)號(hào)碼,reg_dateSTRINGCOMMENT用戶編號(hào),t_start_date,t_end_dateCOMMENT用戶資料拉鏈表ROWFORMATDELIMITEDFIELDSTERMINATEDBYtLINESTERMINATEDBYnSTOREDASORCLOCATION/dws/user_his;)實(shí)現(xiàn)sql語(yǔ)句然后初始化的sql就不寫了，其實(shí)就相當(dāng)于是拿一天的ods層用戶表過(guò)來(lái)就行，我們寫一下每日的更新語(yǔ)句。現(xiàn)在我們假設(shè)我們已經(jīng)已經(jīng)初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的數(shù)據(jù)，我們有了下面的Sql。然后把兩個(gè)日期設(shè)置為變量就可以了。INSERTOVERWRITETABLEdws.user_hisSELECT*FROM(SELECTA.user_num,A.mobile,A.reg_date,A.t_start_time,CASEWHENA.t_end_time=9999-12-31ANDB.user_numISNOTNULLTHEN2017-01-01ELSEA.t_end_timeENDASt_end_timeFROMdws.user_his ALEFTJOINods.user_update BONA.user_num=B.user_numUNIONSELECTC.user_num,C.mobile,C.reg_date,2017-01-02ASt_start_time,9999-12-31ASt_end_timeFROMods.user_updateASC)AST好了，我們分析了拉鏈表的原理、設(shè)計(jì)思路、并且在Hive環(huán)境下實(shí)現(xiàn)了一份拉鏈表，下面對(duì)拉鏈表做一些小的補(bǔ)充。拉鏈表和流水表流水表存放的是一個(gè)用戶的變更記錄，比如在一張流水表中，一天的數(shù)據(jù)中，會(huì)存放一個(gè)用戶的每條修改記錄，但是在拉鏈表中只有一條記錄。這是拉鏈表設(shè)計(jì)時(shí)需要注意的一個(gè)粒度問(wèn)題。我們當(dāng)然也可以設(shè)置的粒度更小一些，一般按天就足夠。查詢性能拉鏈表當(dāng)然也會(huì)遇到查詢性能的問(wèn)題，比如說(shuō)我們存放了5年的拉鏈數(shù)據(jù)，那么這張表勢(shì)必會(huì)比較大，當(dāng)查詢的時(shí)候性能就比較低了，個(gè)人認(rèn)為兩個(gè)思路來(lái)解決：在一些查詢引擎中，我們對(duì)start_date和end_date做索引，這樣能提高不少性能。保留部分歷史數(shù)據(jù)，比如說(shuō)我們一張表里面存放全量的拉鏈表數(shù)據(jù)，然后再對(duì)外暴露一張只提供近3個(gè)月數(shù)據(jù)的拉鏈表。使用拉鏈表的時(shí)候可以不加t_end_date，即失效日期，但是加上之后，能優(yōu)化很多查詢?？梢约由袭?dāng)前行狀態(tài)標(biāo)識(shí)，能快速定位到當(dāng)前狀態(tài)。在拉鏈表的設(shè)計(jì)中可以加一些內(nèi)容，因?yàn)槲覀兠刻毂４嬉粋€(gè)狀態(tài)，如果我們?cè)谶@個(gè)狀態(tài)里面加一個(gè)字段，比如如當(dāng)天修改次數(shù)，那么拉鏈表的作用就會(huì)更大。5、對(duì)私數(shù)據(jù)倉(cāng)庫(kù)實(shí)戰(zhàn)數(shù)據(jù)倉(cāng)庫(kù)主題，客戶資產(chǎn)等級(jí)。何為客戶資產(chǎn)等級(jí)，根據(jù)客戶的純資產(chǎn)的月均總額、貸款余額的總額、信用卡近一年消費(fèi)額的總額，分別按照規(guī)則制定，計(jì)算出分別的等級(jí)，取三者的最高等級(jí)，用于定義客戶在我行的資產(chǎn)等級(jí)。分別為：私行、財(cái)富、理財(cái)、普通。源事實(shí)表：存款賬戶表、基金賬戶、理財(cái)賬戶、客戶信息表、匯率表、信用卡交易表、貸款余額表。