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

1、1、数据仓库基本概念1.1、主题（Subject ）主题就是指我们所要分析的具体方面。例如：某年某月某地区某机型某款App的安装情况。主题有两个元素：一是各个分析角度（维度），如时间位置；二是要分析的具体量度， 该量度一般通过数值体现，如 App安装量。1.2、维(Dimension )维是用于从不同角度描述事物特征的，一般维都会有多层（Level：级别），每个Level都会包含一些共有的或特有的属性（Attribute），可以用下图来展示下维的结构和组成：以时间维为例，时间维一般会包含年、季、月、日这几个Level,每个Level 一般都会有ID、NAME、DESCRIPTION这几个公共属

2、性，这几个公共属性不仅适用于时间维，也同样表 现在其它各种不同类型的维。Attributes1.3、分层（Hierarchy ）OLAP需要基于有层级的自上而下的钻取，或者自下而上地聚合。所以我们一般会在维 的基础上再次进行分层，维、分层、层级的关系如下图：每一级之间可能是附属关系（如市属于省、省属于国家），也可能是顺序关系（如天周年），如下图所示：nilCn.d.0.| lino isVictoriaUrbanaruiifi/ABri h.h C ；i| jnrhid jNew YorkOnurirToronto j OttawiiiNew Yorki-* iBunaldprovide or

3、 s tate1.4、量度量度就是我们要分析的具体的技术指标，诸如年销售额之类。它们一般为数值型数据。我们或者将该数据汇总， 或者将该数据取次数、 独立次数或取最大最小值等， 这样的数据称 为量度。1.5、粒度数据的细分层度，例如按天分按小时分。1.6、事实表和维表事实表是用来记录分析的内容的全量信息的，包含了每个事件的具体要素，以及具体发生的事情。事实表中存储数字型ID以及度量信息。维表则是对事实表中事件的要素的描述信息，就是你观察该事务的角度，是从哪个角度去观察这个内容的。事实表和维表通过ID相关联，如图所示：地城ID也址髙匣销書事实表PK.FK1PKJK2PK5K3 PK.FK4 PKr

4、FR5然區ID产晶ID 支忖IDPK产启M严品名 产骷属性PKiff占式PS用尸喪玺用户荷jaPKajrbfl*l1.7、星形/雪花形/事实星座这三者就是数据仓库多维数据模型建模的模式上图所示就是一个标准的星形模型。雪花形就是在维度下面又细分出维度， 这样切分是为了使表结构更加规范化。 雪花模式 可以减少冗余，但是减少的那点空间和事实表的容量相比实在是微不足道，而且多个表联结操作会降低性能，所以一般不用雪花模式设计数据仓库。事实星座模式就是星形模式的集合，包含星形模式，也就包含多个事实表。1.8 、企业级数据仓库 / 数据集市企业级数据仓库： 突出大而全， 不论是细致数据和聚合数据它全都有，

5、设计时使用事实 星座模式数据集市： 可以看做是企业级数据仓库的一个子集， 它是针对某一方面的数据设计的数 据仓库， 例如为公司的支付业务设计一个单独的数据集市。由于数据集市没有进行企业级的设计和规划，所以长期来看，它本身的集成将会极其复杂。其数据来源有两种，一种是直接 从原生数据源得到，另一种是从企业数据仓库得到。设计时使用星形模型2、数据仓库设计步骤2.1 、确定主题主题与业务密切相关， 所以设计数仓之前应当充分了解业务有哪些方面的需求， 据此确 定主题。2.2 、确定量度在确定了主题以后， 我们将考虑要分析的技术指标， 诸如年销售额之类。 量度是要统计 的指标，必须事先选择恰当，基于不同的

6、量度将直接产生不同的决策结果。2.3 、确定数据粒度考虑到量度的聚合程度不同，我们将采用“最小粒度原则” ，即将量度的粒度设置到最 小。例如如果知道某些数据细分到天就好了，那么设置其粒度到天；但是如果不确定的话， 就将粒度设置为最小，即毫秒级别的。2.4 、确定维度设计各个维度的主键、层次、层级，尽量减少冗余。2.5 、创建事实表事实表中将存在维度代理键和各量度， 而不应该存在描述性信息， 即符合“瘦高原则” ， 即要求事实表数据条数尽量多 （粒度最小 ），而描述性信息尽量少。3、数据仓库-全量表全量表：保存用户所有的数据（包括新增与历史数据）增量表：只保留当前新增的数据快照表：按日分区，记录

7、截止数据日期的全量数据切片表：切片表根据基础表，往往只反映某一个维度的相应数据。其表结构与基础表结构相同，但数据往往只有某一维度，或者某一个事实条件的数据3.1、更新插入算法更新插入（主表）算法适用于保留最新状态表的处理。案例：银行账户余额表，全表表大约8000万，非结息日每日变动 100万，结息日变动2000 万。非结息日：它是指根据主键（或指定字段）进行数据对比，如果增量表存在记录，则更新 原全量表，否则插入数据。ETL更新的优化？ Merge?结息日：新建空表，它是指根据主键（或指定字段）进行数据对比，首先插入原全量表与增量表无法匹配的非变更数据，再次插入可以匹配的增量表数据，最后补齐增

8、量表与全量表无法匹配的增量数据。3.2、直接追加算法直接追加算法是指增量数据直接追加到目标表中，此算法适合流水、交易、 事件、话单等增量且不修改的数据。由于历史信息表数据量过于庞大，往往在数据库设计中将引入分区表的逻辑来处理，具体实现逻辑自查。3.3、全量历史表算法拉链表。4、数据仓库-拉链表拉链表：数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。我们先看一个示例，这就是一张拉链表， 存储的是用户的最基本信息以及每条记录的生 命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。MtrniIU户祜孑用

9、1号田t start datet end da2017-01-01mill2017-01-019999-V-ll2017'01-01022222222017-01 012017-01 012U17-01-0100223333d2017-0L 029995-12*312017-01-01丽333332017-01-019999-U-ilX1V-01-010044444442017-01-012017 01-012017 01-010044324322017-01 022017 01-022»17 01-010G44324122017-01-039999-丄丄班005555555

10、2017-CJL-022017 01-022M.7 G1-02005115115ZG17-01-033933-12-?12017-01 030062017-014)39999 12-11在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：1、 有一些表的数据量很大， 比如一张用户表，大约10亿条记录，50个字段，这种表， 即使使用ORC压缩，单张表的存储也会超过 100G （在HDFS使用双备份或者三备份的话就 更大一些）。2、表中的部分字段会被 update更新操作，如用户联系方式，产品的描述信息，订单的 状态等等。3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订

11、单在历史 某一个时间点的状态。4、 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发 生变化的有200万左右，变化的比例占的很小。那么对于这种表我该如何设计呢？下面有几种方案可选：方案一：每天只留最新的一份（比如我们每天用 Sqoop抽取最新的一份全量数据到Hive中）。方案二：每天保留一份全量的切片数据。方案三：使用拉链表。4.1、为什么使用拉链表现在我们对前面提到的三种进行逐个的分析。这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。优点很明显，节省空间，一些普通的使用也很方便， 不用在选择表的时候加一个时间分 区什么的。缺点同样

12、明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。万案一每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。缺点就是存储空间占用量太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。拉链表在使用上基本兼顾了我们的需求。首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。所

13、以我们还是很有必要来使用拉链表的。4.2、拉链表的实现下面我们来举个栗子详细看一下拉链表。我们先看一下在 Mysql关系型数据库里的user表中信息变化。在2017-01-01这一天表中的数据是：逹册E1期屈户号芋机号出2017-01-010011111112017-01-010022222222017-01-010033333332017-01-01004在2017-01-02这一天表中的数据是，用户002和004资料进行了修改，005是新增用户:汪Bfina2017-001用户曲号001111U12017-01-010022OI7-01-0L0052017-01-OL0044J2432(f

14、B444444 J432432 '20174U-Q20055555S3(J017-01)在2017-01-03这一天表中的数据是，用户004和005资料进行了修改，006是新增用户:用户号手机号碍Mrl2017-01-01001linn2017-01-00Q2253333201701-01OOiM站站2017-01-010046S4321M1701-0?005115115(155555115115)2017 01 03006(2Oi7-oiai5Kig)如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天(即 2017-01-03)的数据：翊日朋手tjtart

15、dat*t *hd dal2017-01-01001mm2G17-01-0I999Q-12-512017-0V01»02222Z222017-01-012017 01-012017-01 OL00223333320I7-OP022017-01-019033333132017-01-019999-L2-312017-01-01004444444201701 012017-01-01.2017-01-0100443243Z2017-01-0?2017 01-022017-01-01004217-01 039999-12-312017-01-0290S555S&520L7-01-0

16、22017-01-022017 01-0ZOOS11SUS2017-01-039999-1212017-01-036666662017-01 039999-12-31说明t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。t_en d_date = -99931表示该条记录目前处于有效状态。如果查询当前所有有效的记录，则select * from user where t_end_date =-1929-3919'。如果查询 2017-01-02 的历史快照，则 select from user where t_start_dat

17、e<=-0210-0127'and t_end_date>=-20011-072 '。 （*此处要好好理解，是拉链表比较重要的一块。*）4.3 、拉链表在 Hive 中的实现在现在的大数据场景下，大部分的公司都会选择以 Hdfs 和 Hive 为主的数据仓库架构。 目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表智能进行删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。还是以上面的用户表为例， 我们要实现用户的拉链表。 在实现它之前，我们需要先确定 一下我们有哪些数据源可以用。我们需要一张ODS层的用户全量表。至少

18、需要用它来初始化。 每日的用户更新表。而且我们要确定拉链表的时间粒度， 比如说拉链表每天只取一个状态， 也就是说如果一 天有 3 个状态变更，我们只取最后一个状态， 这种天粒度的表其实已经能解决大部分的问题 了。ods 层的 user 表现在我们来看一下我们ods层的用户资料切片表的结构：CREATE EXTERNAL TABLE ods.user ( user_num STRING COMMENT 用' 户编号 ', mobile STRING COMMENT 手' 机号码 ', reg_date STRING COMMENT注册日期COMMENT '

19、用户资料表 ' PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LINES TERMINATED BY 'n' STORED AS ORCLOCATION '/ods/user')ods 层的 user_update 表 然后我们还需要一张用户每日更新表， 前面已经分析过该如果得到这张表， 现在我们假 设它已经存在。CREATE EXTERNAL TABLE ods.user_update ( user_num STRING COMMENT 用

20、' 户编号 ', mobile STRING COMMENT 手' 机号码 ', reg_date STRING COMMENT注册日期COMMENT '每日用户资料更新表 ' PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LINES TERMINATED BY 'n' STORED AS ORCLOCATION '/ods/user_update')拉链表 现在我们创建一张拉链表：CREATE EXT

21、ERNAL TABLE dws.user_his ( user_num STRING COMMENT 用' 户编号 ', mobile STRING COMMENT 手' 机号码 ', reg_date STRING COMMENT 用' 户编号 ', t_start_date , t_end_dateCOMMENT '用户资料拉链表 'ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' LINES TERMINATED BY 'n' STORED AS OR

22、CLOCATION '/dws/user_his') 实现 sql 语句 然后初始化的 sql 就不写了，其实就相当于是拿一天的ods 层用户表过来就行，我们写一下每日的更新语句。现在我们假设我们已经已经初始化了 2017-01-01 的日期，然后需要更新 2017-01-02 那 一天的数据，我们有了下面的Sql。然后把两个日期设置为变量就可以了。INSERT OVERWRITE TABLE dws.user_his SELECT * FROM(SELECT A.user_num, A.mobile, A.reg_date, A.t_start_time, CASEWHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-0101'ELSE A.t_end_timeEND AS t_end_timeFROM dws.user_his ALEFT JOIN ods.user_update BON A.user_num = B.user_numUNIONSELECT C.user_num, C.mobile, C.reg_date, '2017-01-02' AS t_star