数据仓库多维数据模型的设计

1、第据仓库基本概念

1.1.主题(Subject)

主题就是指我们所要分析的具体方面。例如：某年某月某池区某机型某款App的安装

情况。主题有两个元素：一^＞分析角度(维度)，如时间位置；二是要分析的具体量度，

该量度普通通过数值体现，如App安装量。

1.2、维(Dimension)

维是用于从不同角度描述事物特征的，普通维都会有多层(Level:级别),每一个

Level都会包含一些共有的或者特有的属性(Attribute),可以用下图来展示下维的结构和

组成：

以时间维为例，时间维普通会包含年、季、月、日这几个Level,每一个Level普通都

会有ID、NAME、DESCRIPTION这几个公共属性，这几个公共属性不仅合用于时间维，

也同样表现在其它各种不同类型的维。

1.3、分层(Hierarchy)

OLAP需要基于有层级的自上而下的钻取，或者自下而上地聚合。所以我们普通会在维

的基础上再次进行分层，维、分层、层级的关系如下图：

Hierarchy」

Dimension

Hierarchy_2

每一级之间可能是附属关系（如市属于省、省属于国家）,也可能是顺序关系（如天周

年），如下图所示：

1.4.fijg

量度就是我们要分析的具体的技术指标，诸如年销售额之类。它们普通为数值型数据。

我们或者将该数据;匚总、，或者将该数据取次数、独立次数或者取最大最小值等，这样的数据

称为量度。

1.5、降

数据的细分层度，例如按天分按小时分。

1.6、事实表和维表

事实表是用来记录分析的内容的全量信息的，包含了每一个事件的具体要素，以及具体发

生的事情。事实表中存储数字型ID以及度量信息。

维表则是对事实表中事件的要素的描述信息，就是你观察该事务的角度，是从哪个角度

去观察这个内容的。

事实表和维表通过ID相关联，如图所示：

1.7.星形/雪花形神实星座

这三者就是数据仓库多维数据模型建模的模式

上图所示就是一个标准的星形模型。

雪花形就是在维度下面又细分出维度，这样切分是为了使表结构更加规x化。雪花模

式可以减少冗余，但是减少的那点空间和事实表的容量相匕匕实在是微不足道，而且多个表联

结操作会降彳灯生能，所以普通不用雪花模式设计数据仓库。

事实星座模式就是星形模式的集合，包含星形模式，也就包含多个事实表。

L&企业级数据仓库/雌集市

仓库：突出大而全，不管是细致辘和聚合缄它全都有,设计时使用事实

星座模式

数据集市：可以看做是企业级数据仓库的一个子集，它是针对某一方面的数据设计的数

据仓库，例如为公司的支付业务设计一个单独的数据集市。由于数据集市没有进彳亍企业级的

设计和规划，所以长期来看，它本身的集成将会极其复杂。其数据来源有两种，一种是直接

从原生数据源得到，另一种是从企业数据仓库得到。设计时使用星形模型

2、蝴仓库设计步骤

2.1、确定主题

主题与业务密切相关，所以设计数仓之前应当充分了解业务有哪些方面的需求，据此确

定主题。

2.2、确定量度

在确定了主题以后，我们将考虑要分析的技术指标，诸如年销售额之类。量度是要统计

的指标，必须事先选择恰当，基于不同的量度将直接产生不同的决策结果。

2.3、确定数据粒度

考虑到量度的聚合程度不同，我仰锦用“最4地度原则"，即将量度的粒度设螯曝小。

例如如果知道某些雌细分到天就好了，那末设置其粒度到天；但是如果不确定的话，就将

粒度设置为最小，即亳秒级^的。

2.4、确定维度

设计各个维度的主键、层次、层级，尽量减少冗余。

2.5、创建事实表

艳表利弗在维度代理键和各量度,而不应该存i璘述性信息，即符合"瘦高殿！r,

即要求事实表数据条数尽量多（粒度最小），而描述性信息尽量少。

3、雌仓库-全量表

全量表：保存用户所有的数据（包括新增与历史数据）

增量表：只保留当前新增的数据

快照表：按日分区，记录截止数据日期的全量数据

切片表：切片表根据基础表，往往只反映某一个维度的相应数据。其表结构与基础表结

构相同，但数据往往惟独某一维度，或者某一个事实条件的数据

3.1、更新插入算法

更新插入（主表）算法合用于保留最新状态表的处理。

案例：银行账户余额表，全表表大约8000万，非结息日每日变动100万，结息日变动

2000万。

非结息日：它是指根据主键（或者指定字段）进行数据对照，如果增量表存在记录，则更

新原全量表，否则插入数据。

ETL更新的优化？Merge?

结息日：新建空表，它是指根据主键（或者指定字段）进行数据对照，首先插入原全量表

与增量表无法匹配的非变更数据，再次插入可以匹配的增量本婶，最后补齐增量表与仝

量表无法匹配的增量数据。

3.2、直接追加算法

直接追加算;境^增量数宪直接追加到目标表中，此算法适合流水、交易、事件、话单

等增量且不修改的数据。

体实现逻辑自查。

3.3、全量历史表算法

拉链表。

4、数据仓库-拉链表

搔表：数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录

历史。记录一个事物从开始，向来到当前状态的所有变化的信息。

我们先看一个示例，这就是一X拉链表，存储的是用户的最基本信息以及每条记录的

生命周期。我们可以使用这X表拿到最新的当天的最新数据以及之前的历史数据。

注册日照用户18号判翎tstartdatet.enddate

2017-01-01001111U12017-01-019999-12-M

2017-01-010022017-01-012017-01-01

2017-01-010022333532017-01-029W12-31

2017-0101(XB3333332017-01019999-12-M

2017-01-0100444Tl442017-01-012017-01-01

201701-01(XM4324322017-01-02201701-02

201701-010044324”2017-01-039999^12-il

2017-01-02□055S5SSS2017-01-022017-01-02

2017-01-020051151152017-01-039999-12-31

2017-010B0%6666662017-01-03999^12-il

在数据仓库的数据模型设计过程中，时常会遇到下面这种表的设计：

1、量很大，匕似Q-X用户表，大约10亿条记录，50个字段，曲表

即使使用ORC压缩，单X表的存储也会超过100G（在HDFS使用双备份或者二备份的话就

更大一些）。

2、表中的部份字段会被update更新操作，如用户联系方式，产品的描述信息，定单

的状态等等。

3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个定单在历史

某一个时间点的状态。

4、表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和

发生变化的有200万摆布，变化的比例占的很小。

那末对于这种表我该如何设计呢?下面有几种方案可选：

方案一：每天只留最新的T分（比如我们每天用Sqoop抽取最新的T分全量数据到Hive

中）。

方案二：每天保留一份全量的切片数据。

方案三：使用拉链表。

4.1、为什么使用拉链表

现在我们对前面提到的三种进行逐个的分析。

方案一

这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最

新的。

优点很明显，节省空间，一联通的使用也很方便，不用在选择表的时候个时间分

区什么的。

缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较妥帖的方案，而且历史数据也在。

缺点就是存储空间占用量太大了，如果对这边表每天都保留T分全量，那末每次全量中

会保存不少不变的信息，对存储是极大的浪费。

固然我们也可以做一些取舍，比如只保留近一个月的数据?但是，需求是无耻的，数据

的生命周期不是我们能彻底摆布的。

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍，虽说不像方案一月除占用是那末小，但是它每日的增量

可能惟独方案二的千分之一甚至是万分之一。

其实它能龌方案二所能满足的需求，既育缴取最新的数据，也能添加筛选条件也获取

历史的数据。

所以我们还是很有必要来使用拉链表的。

4.2.拉链表的实现

下面我们来举个栗子详细看一下拉链表。

我们先看一下在Mysql关系型数据库里的user表XX息变化。

在2022-01-01这一天表中的数据是：

注期用户管号手机号码

2017-01-01001111111

2017-01-01002222222

2017-01-01003533333

201701-01004444444

在2022-01-02这少表中的数据是,用户002和004资料进行了修改，005是新增用

户：

用户验写手机号码

2017-01-01001mill

201701-01002233333(由22222及松33333

2017-01-01003333333

2017-01-010044323(由444444变成432432

2017-01-0200SSS5555(2O17-Ol-OZmW)

在2022-01-03这一天表中的数据是，用户004和005资料进行了修改，006是新增用

户：

，王加日胤用户管号手机号码备注

2017-01-01001umi

2017-01-01002233汨

2017-01-01（XB资3333

2017-01-01004654521（3452432^654321）

2017-01-02005U5U5（由S5555陵或115115）

2017-01-05OOG（2017-01-0溺电）

如果在数据仓库中设计成历史拉链表保存该表：,则会白卜面隹伴一X表，隹是最新一

天(即2022-01-03)的数据：

注册日用用户IR判由tstartdatetenddak

2017-01-01001M11112017-01-019999L2-31

2017-01-010022222222017-01-012017-01-01

201701-010022心332017-01-02999^12-31

2017-01-010033333132017-01-019999-12-31

2017-01-010044444442017-01-012017-01-01

2017-01-010044324522017^01022017«0102

2017-01-010042017-01-03999912-31

2017-01-02005S5SSSS2017-01-022017-01-02

2017-01-02005115U52017-01-039999L2-31

2017-01-05006uuuuoo2017-01-039999-12-31

说明

t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期

结束时间。

t_end_date=9999-12-31，表示该条记录目前处于有效状态。

如果查询当前所有有效的记录，贝!Jselect*fromuserwheret_end_date='9999-12-31'。

如果囱旬2022-01-02的历史快照,贝I」selectfromuserwheret_start_date<=

2022-01-02andt_end_date>=2022-01-02、(*此处要好好理解，是拉链表匕瞅重要的一

t丸巧

4.3、拉链表在Hive中的实现

在现在的大数据场景下，大部份的公司都会选择以Hdfs和Hive为主的数据仓库架构。

目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的,也就是说Hive的表智能进行

删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。

还是以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定

一下我们有哪些数据源可以用。

我们需要一XODS层的用户全量表。至少需要用它来初始化。

每日的用户更新表。

而且我们要确定拉链表的时间粒度,比如说拉链表每天只取一个状态，也就是说如果一

天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部份的问题

To

ods层的user表

现在我们来看一下我们ods层的用户资料切片表的结构：

CREATEEXTERNALTABLEcdsuser(

user_numSTRINGMEN「用户编号

mobileSTRINGMENTW,

reg_dateSTRINGMENT'注册日期’

MEN「用户资料表’

PARimONEDBY(dtstring)

ROWFORMATDELIMITEDHELDSTERMINATEDBY''UNESTERMINATEDBY''

STOREDASORC

LOCATION'/ods/user';

)

odsuser_update表

然后我们还需要一X用户每日更新表，前面已经分析过该如果得到这X表，现在我们

假设它已经存在。

CREATEEXTERNALTABLEods.user_update(

user_numSTRINGMEN「用户编号'，

mobileSTRINGMENT,手机,，

reg_dateSTRINGMENT'注册日期’

MENT'每日用户资料更新表’

PARTTHONEDBY(dtstring)

ROWFORMATDELIMITEDRELDSTERMINATEDBY''UNESTERMINATEDBY''

STOREDASORC

LOCATION7ods/user_update';

)

拉链表

现在我们创建一X拉链表：

CREATEEXTERNALTABLEdws.user_his(

user_numSTRINGMENT'用户编号'，

mobileSTRINGMENTW,

reg_dateSTRINGMENT'用户编号'，

t_start_date,

t_end_date

MENT'用户资*4拉链表’

ROWFORMATDELIMITEDHELDSTERMINATEDBY''LINESTERMINATEDBY''

STOREDASORC

LOCATION7dws/user_his';

)

实现sql语句

然后初始化的sql就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写

一下每日的更新语句。

现在我们假设我们已经已经初始化了2022-01-01的日期，然后需要更新2022-01-02

那一天的数据我们有了下面的

，Sql0

然后把两个日期设置为变量就可以了。

INSERTOVERWRITETABLEdws.user_his

SELECT*FROM

(

SELECTA.user_num,

A.mobile,

A.reg_date,

A.t_start_time,

CASE

WHENA.t_end_time='9999-12-31'ANDB.user_numISNOTNULLTHEN'2022

-01-01,

ELSEA.t_end_time

ENDASt_end_time

FROMdws.user_hisA

LEFTJOINods.user_updateB

ONA.user_num=B.user_num

UNION