數(shù)據(jù)倉庫多維數(shù)據(jù)模型的設(shè)計(jì)說明

1、數(shù)據(jù)倉庫基本概念1.1、主題（Subject）主題就是指我們所要分析的具體方面。例如：某年某月某地區(qū)某機(jī)型某款A(yù)pp的安裝狀況。主題有兩個(gè)元素：一是各個(gè)分析角度（維度），如時(shí)間位置；二是要分析的具體量度，該量度普通通過數(shù)值體現(xiàn)，如App安裝量。1.2、維（Dimension）維是用于從不同角度描述事物特性的，普通維都會(huì)有多層（Level：級(jí)別），每個(gè)Level都會(huì)包含某些共有的或特有的屬性（Attribute），能夠用下圖來展示下維的構(gòu)造和構(gòu)成：以時(shí)間維為例，時(shí)間維普通會(huì)包含年、季、月、日這幾個(gè)Level，每個(gè)Level普通都會(huì)有ID、NAME、DESCRIPTION這幾個(gè)公共屬性，這幾個(gè)公共屬性不僅合用于時(shí)間維，也同樣體現(xiàn)在其它多個(gè)不同類型的維。1.3、分層（Hierarchy）OLAP需要基于有層級(jí)的自上而下的鉆取，或者自下而上地聚合。因此我們普通會(huì)在維的基礎(chǔ)上再次進(jìn)行分層，維、分層、層級(jí)的關(guān)系以下圖：每一級(jí)之間可能是附屬關(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í)表是用來統(tǒng)計(jì)分析的內(nèi)容的全量信息的，包含了每個(gè)事件的具體要素，以及具體發(fā)生的事情。事實(shí)表中存儲(chǔ)數(shù)字型ID以及度量信息。維表則是對(duì)事實(shí)表中事件的要素的描述信息，就是你觀察該事務(wù)的角度，是從哪個(gè)角度去觀察這個(gè)內(nèi)容的。事實(shí)表和維表通過ID有關(guān)聯(lián)，如圖所示：1.7、星形/雪花形/事實(shí)星座這三者就是數(shù)據(jù)倉庫多維數(shù)據(jù)模型建模的模式上圖所示就是一種原則的星形模型。雪花形就是在維度下面又細(xì)分出維度，這樣切分是為了使表構(gòu)造更加規(guī)范化。雪花模式能夠減少冗余，但是減少的那點(diǎn)空間和事實(shí)表的容量相比實(shí)在是微局限性道，并且多個(gè)表聯(lián)結(jié)操作會(huì)減少性能，因此普通不用雪花模式設(shè)計(jì)數(shù)據(jù)倉庫。事實(shí)星座模式就是星形模式的集合，包含星形模式，也就包含多個(gè)事實(shí)表。1.8、公司級(jí)數(shù)據(jù)倉庫/數(shù)據(jù)集市公司級(jí)數(shù)據(jù)倉庫：突出大而全，不管是細(xì)致數(shù)據(jù)和聚合數(shù)據(jù)它全都有，設(shè)計(jì)時(shí)使用事實(shí)星座模式數(shù)據(jù)集市：能夠看做是公司級(jí)數(shù)據(jù)倉庫的一種子集，它是針對(duì)某首先的數(shù)據(jù)設(shè)計(jì)的數(shù)據(jù)倉庫，例如為公司的支付業(yè)務(wù)設(shè)計(jì)一種單獨(dú)的數(shù)據(jù)集市。由于數(shù)據(jù)集市沒有進(jìn)行公司級(jí)的設(shè)計(jì)和規(guī)劃，因此長久來看，它本身的集成將會(huì)極其復(fù)雜。其數(shù)據(jù)來源有兩種，一種是直接從原生數(shù)據(jù)源得到，另一種是從公司數(shù)據(jù)倉庫得到。設(shè)計(jì)時(shí)使用星形模型

2、數(shù)據(jù)倉庫設(shè)計(jì)環(huán)節(jié)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)生不同的決策成果。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)當(dāng)存在描述性信息，即符合“瘦高原則”，即規(guī)定事實(shí)表數(shù)據(jù)條數(shù)盡量多(粒度最小)，而描述性信息盡量少。

3、數(shù)據(jù)倉庫-全量表全量表：保存顧客全部的數(shù)據(jù)（涉及新增與歷史數(shù)據(jù)）增量表：只保存現(xiàn)在新增的數(shù)據(jù)快照表：按日分區(qū)，統(tǒng)計(jì)截止數(shù)據(jù)日期的全量數(shù)據(jù)切片表：切片表根據(jù)基礎(chǔ)表，往往只反映某一種維度的對(duì)應(yīng)數(shù)據(jù)。其表構(gòu)造與基礎(chǔ)表構(gòu)造相似，但數(shù)據(jù)往往只有某一維度，或者某一種事實(shí)條件的數(shù)據(jù)3.1、更新插入算法更新插入(主表)算法合用于保存最新狀態(tài)表的解決。案例：銀行賬戶余額表，全表表大概8000萬，非結(jié)息日每日變動(dòng)100萬，結(jié)息日變動(dòng)萬。非結(jié)息日：它是指根據(jù)主鍵(或指定字段)進(jìn)行數(shù)據(jù)對(duì)比，如果增量表存在統(tǒng)計(jì)，則更新原全量表，否則插入數(shù)據(jù)。ETL更新的優(yōu)化？Merge?結(jié)息日：新建空表，它是指根據(jù)主鍵(或指定字段)進(jìn)行數(shù)據(jù)對(duì)比，首先插入原全量表與增量表無法匹配的非變更數(shù)據(jù)，再次插入能夠匹配的增量表數(shù)據(jù)，最后補(bǔ)齊增量表與全量表無法匹配的增量數(shù)據(jù)。3.2、直接追加算法直接追加算法是指增量數(shù)據(jù)直接追加到目的表中，此算法適合流水、交易、事件、話單等增量且不修改的數(shù)據(jù)。由于歷史信息表數(shù)據(jù)量過于龐大，往往在數(shù)據(jù)庫設(shè)計(jì)中將引入分區(qū)表的邏輯來解決，具體實(shí)現(xiàn)邏輯自查。3.3、全量歷史表算法 拉鏈表。

4、數(shù)據(jù)倉庫-拉鏈表拉鏈表：數(shù)據(jù)倉庫設(shè)計(jì)中表存儲(chǔ)數(shù)據(jù)的方式而定義的，顧名思義，所謂拉鏈，就是統(tǒng)計(jì)歷史。統(tǒng)計(jì)一種事物從開始，始終到現(xiàn)在狀態(tài)的全部變化的信息。我們先看一種示例，這就是一張拉鏈表，存儲(chǔ)的是顧客的最基本信息以及每條統(tǒng)計(jì)的生命周期。我們能夠使用這張表拿到最新的當(dāng)天的最新數(shù)據(jù)以及之前的歷史數(shù)據(jù)。在數(shù)據(jù)倉庫的數(shù)據(jù)模型設(shè)計(jì)過程中，經(jīng)常會(huì)碰到下面這種表的設(shè)計(jì)：1、有某些表的數(shù)據(jù)量很大，例如一張顧客表，大概10億條統(tǒng)計(jì)，50個(gè)字段，這種表，即使使用ORC壓縮，單張表的存儲(chǔ)也會(huì)超出100G（在HDFS使用雙備份或者三備份的話就更大某些）。2、表中的部分字段會(huì)被update更新操作，如顧客聯(lián)系方式，產(chǎn)品的描述信息，訂單的狀態(tài)等等。3、需要查看某一種時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息，例如，查看某一種訂單在歷史某一種時(shí)間點(diǎn)的狀態(tài)。4、表中的統(tǒng)計(jì)變化的比例和頻率不是很大，例如，總共有10億的顧客，每天新增和發(fā)生變化的有200萬左右，變化的比例占的很小。那么對(duì)于這種表我該如何設(shè)計(jì)呢?下面有幾個(gè)方案可選：方案一：每天只留最新的一份（例如我們每天用Sqoop抽取最新的一份全量數(shù)據(jù)到Hive中）。方案二：每天保存一份全量的切片數(shù)據(jù)。方案三：使用拉鏈表。4.1、為什么使用拉鏈表現(xiàn)在我們對(duì)前面提到的三種進(jìn)行逐個(gè)的分析。方案一這種方案就不用多說了，實(shí)現(xiàn)起來很簡樸，每天drop掉前一天的數(shù)據(jù)，重新抽一份最新的。優(yōu)點(diǎn)很明顯，節(jié)省空間，某些普通的使用也很方便，不用在選擇表的時(shí)候加一種時(shí)間分區(qū)什么的。缺點(diǎn)同樣明顯，沒有歷史數(shù)據(jù)，先翻翻舊賬只能通過其它方式，例如從流水表里面抽。方案二每天一份全量的切片是一種比較穩(wěn)妥的方案，并且歷史數(shù)據(jù)也在。缺點(diǎn)就是存儲(chǔ)空間占用量太大了，如果對(duì)這邊表每天都保存一份全量，那么每次全量中會(huì)保存諸多不變的信息，對(duì)存儲(chǔ)是極大的浪費(fèi)。固然我們也能夠做某些取舍，例如只保存近一種月的數(shù)據(jù)?但是，需求是無恥的，數(shù)據(jù)的生命周期不是我們能完全左右的。拉鏈表在使用上基本兼顧了我們的需求。首先它在空間上做了一種取舍，雖說不像方案一那樣占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是萬分之一。其實(shí)它能滿足方案二所能滿足的需求，既能獲取最新的數(shù)據(jù)，也能添加篩選條件也獲取歷史的數(shù)據(jù)。因此我們還是很有必要來使用拉鏈表的。4.2、拉鏈表的實(shí)現(xiàn)下面我們來舉個(gè)栗子具體看一下拉鏈表。我們先看一下在Mysql關(guān)系型數(shù)據(jù)庫里的user表中信息變化。在-01-01這一天表中的數(shù)據(jù)是：在-01-02這一天表中的數(shù)據(jù)是，顧客002和004資料進(jìn)行了修改，005是新增顧客：在-01-03這一天表中的數(shù)據(jù)是，顧客004和005資料進(jìn)行了修改，006是新增顧客：如果在數(shù)據(jù)倉庫中設(shè)計(jì)成歷史拉鏈表保存該表，則會(huì)有下面這樣一張表，這是最新一天(即-01-03)的數(shù)據(jù)：闡明t_start_date表達(dá)該條統(tǒng)計(jì)的生命周期開始時(shí)間，t_end_date表達(dá)該條統(tǒng)計(jì)的生命周期結(jié)束時(shí)間。t_end_date=‘9999-12-31’表達(dá)該條統(tǒng)計(jì)現(xiàn)在處在有效狀態(tài)。如果查詢現(xiàn)在全部有效的統(tǒng)計(jì)，則select*fromuserwheret_end_date=‘9999-12-31’。如果查詢-01-02的歷史快照，則selectfromuserwheret_start_date<=‘-01-02’andt_end_date>=‘-01-02’。(*此處要好好理解，是拉鏈表比較重要的一塊。**)4.3、拉鏈表在Hive中的實(shí)現(xiàn)在現(xiàn)在的大數(shù)據(jù)場景下，大部分的公司都會(huì)選擇以Hdfs和Hive為主的數(shù)據(jù)倉庫架構(gòu)。現(xiàn)在的Hdfs版原來講，其文獻(xiàn)系統(tǒng)中的文獻(xiàn)是不能做變化的，也就是說Hive的表智能進(jìn)行刪除和添加操作，而不能進(jìn)行update。基于這個(gè)前提，我們來實(shí)現(xiàn)拉鏈表。還是以上面的顧客表為例，我們要實(shí)現(xiàn)顧客的拉鏈表。在實(shí)現(xiàn)它之前，我們需要先擬定一下我們有哪些數(shù)據(jù)源能夠用。我們需要一張ODS層的顧客全量表。最少需要用它來初始化。每日的顧客更新表。并且我們要擬定拉鏈表的時(shí)間粒度，例如說拉鏈表每天只取一種狀態(tài)，也就是說如果一天有3個(gè)狀態(tài)變更，我們只取最后一種狀態(tài)，這種天粒度的表其實(shí)已經(jīng)能解決大部分的問題了。ods層的user表現(xiàn)在我們來看一下我們ods層的顧客資料切片表的構(gòu)造：CREATE

EXTERNAL

TABLE

ods.user

(

user_num

STRING

COMMENT

'顧客編號(hào)',

mobile

STRING

COMMENT

'手機(jī)號(hào)碼',

reg_date

STRING

COMMENT

'注冊(cè)日期'

COMMENT

'顧客資料表'

PARTITIONED

BY

(dt

string)

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

'\t'

LINES

TERMINATED

BY

'\n'

STORED

AS

ORC

LOCATION

'/ods/user';

)

ods層的user_update表然后我們還需要一張顧客每日更新表，前面已經(jīng)分析過該如果得到這張表，現(xiàn)在我們假設(shè)它已經(jīng)存在。CREATE

EXTERNAL

TABLE

ods.user_update

(

user_num

STRING

COMMENT

'顧客編號(hào)',

mobile

STRING

COMMENT

'手機(jī)號(hào)碼',

reg_date

STRING

COMMENT

'注冊(cè)日期'

COMMENT

'每日顧客資料更新表'

PARTITIONED

BY

(dt

string)

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

'\t'

LINES

TERMINATED

BY

'\n'

STORED

AS

ORC

LOCATION

'/ods/user_update';

)

拉鏈表現(xiàn)在我們創(chuàng)立一張拉鏈表：CREATE

EXTERNAL

TABLE

dws.user_his

(

user_num

STRING

COMMENT

'顧客編號(hào)',

mobile

STRING

COMMENT

'手機(jī)號(hào)碼',

reg_date

STRING

COMMENT

'顧客編號(hào)',

t_start_date

,

t_end_date

COMMENT

'顧客資料拉鏈表'

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

'\t'

LINES

TERMINATED

BY

'\n'

STORED

AS

ORC

LOCATION

'/dws/user_his';

)

實(shí)現(xiàn)sql語句然后初始化的sql就不寫了，其實(shí)就相稱于是拿一天的ods層顧客表過來就行，我們寫一下每日的更新語句?，F(xiàn)在我們假設(shè)我們已經(jīng)已經(jīng)初始化了-01-01的日期，然后需要更新-01-02那一天的數(shù)據(jù)，我們有了下面的Sql。然后把兩個(gè)日期設(shè)立為變量就能夠了。INSERT

OVERWRITE

TABLE

dws.user_his

SELECT

*

FROM

(

SELECT

A.user_num,

A.mobile,

A.reg_date,

A.t_start_time,

CASE

WHEN

A.t_end_time

=

'9999-12-31'

AND

B.user_num

IS

NOT

NULL

THEN

'-01-01'

ELSE

A.t_end_time

END

AS

t_end_time

FROM

dws.user_his

A

LEFT

JOIN

ods.user_update

B

ON

A.user_num

=

B.user_num

UNION

SELECT

C.user_num,

C.mobile,

C.reg_date,

'-01-02'

AS

t_start_time,

'9999-12-31'

AS

t_end_time

FROM

ods.user_update

AS

C

)

AS

T

好了，我們分析了拉鏈表的原理、設(shè)計(jì)思路、并且在Hive環(huán)境下實(shí)現(xiàn)了一份拉鏈表，下面