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