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

《数据仓库多维数据模型的设计.docx》由会员分享，可在线阅读，更多相关《数据仓库多维数据模型的设计.docx（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、几个公共属性，这几个公共属性不仅合用于时间维，也 同样表现在其它各种不同类型的维。1.3、 分层（Hierarchy）OLAP需要基于有层级的自上而下的钻取，或者自下而上地聚合。所以我们普通会在维 的根抵上再次发展分层，维、分层、层级的关系如下列图：每一级之间可能是附属关系（如市属于省、省属于国家），也可能是顺序关系如天周 年），如下列图所示：L4、量度量度就是我们要分析的具体的技术指标，诸如年销售额之类。它们普通为数值型数据。 我们或者将该数据汇总，或者将该数据取次数、独立次数或者取最大最小值等，这样的数据称 为量度。15、粒度数据的细分层度，例如按天分按小时分。1.6、 事实表和维表事实表

3、是用来记录分析的容的全量信息的，包含了每一个事件的具体要素，以及具体发 生的事情。事实表中存储数字型ID以及度量信息。维表则是对事实表中事件的要素的描述信息，就是你观察该事务的角度，是从哪个角度 去观察这个容的。事实表和维表通过ID相关联，如下图：1.7、 星形/雪花形/事实星座这三者就是数据仓库多维数据模型建模的模式上图所示就是一个标准的星形模型。雪花形就是在维度下面又细分出维度，这样切分是为了使表构造更加规化。雪花模式可 以减少冗余，但是减少的那点空间和事实表的容量相比实在是微缺乏道，而且多个表联结操 作会降低性能，所以普通不用雪花模式设计数据仓库。事实星座模式就是星形模式的集合，包含星形

4、模式，也就包含多个事实表。1.8、 企业级数据仓库/数据集市企业级数据仓库：突出大而全，不管是细致数据和聚合数据它全都有，设计时使用事实 星座模式数据集市：可以看做是企业级数据仓库的一个子集，它是针对*一方面的数据设计的数 据仓库，例如为公司的支付业务设计一个单独的数据集市。由于数据集市没有发展企业级的 设计和规划，所以长期来看，它本身的集成将会极其复杂。其数据来源有两种，一种是直接 从原生数据源得到，另一种是从企业数据仓库得到。设计时使用星形模型2、数据仓库设计步骤2.1、 确定主题主题与业务密切相关，所以设计数仓之前应当充分了解业务有哪些方面的需求，据此确 定主题。2.2、 确定量度在确定

5、了主题以后，我们将考虑要分析的技术指标，诸如年销售额之类。量度是要统计 的指标，必须事先选择恰当，基于不同的量度将直接产生不同的决策结果。2.3、 确定数据粒度考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置到最小。 例如如果知道*些数据细分到天就好了，则设置其粒度到天；但是如果不确定的话，就将 粒度设置为最小，即亳秒级别的。2.4、 确定维度设计各个维度的主键、层次、层级，尽量减少冗余。2.5、 创立事实表事实表中将存在维度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原则”， 即要求事实表数据条数尽量多（粒度最小），而描述性信息尽量少。3、数据仓库-全量表全量

6、表：保存用户所有的数据（包括新增与历史数据）增量表：只保存当前新增的数据快照表：按日分区，记录截止数据日期的全量数据切片表：切片表根据根抵表，往往只反映*一个维度的相应数据其表构造与根抵表构 造一样，但数据往往惟独*一维度，或者*一个事实条件的数据3.1、 更新插入算法更新插入（主表）算法合用于保存最新状态表的处理。案例：银行账户余额表，全表表大约8000万，非结息日每日变动10（）万，结息日变动 2000 万。非结息口:它是指根据主键（或者指定字段）发展数据比照，如果增量表存在记录，则更 新原全量表，否则插入数据。ETL更新的优化？ Merge结息日：新建空表，它是指根据主健（或者指定字段）

7、发展数据比照，首先插入原全量表 与增量表无法匹配的非变更数据，再次插入可以匹配的增量表数据，最后补齐增量表与全量 表无法匹配的增量数据。3.2、 直接追加算法直接追加算法是指增量数据直接追加到H标表中，此算法适合流水、交易、事件、话单 爸增量且不修改的数据。由于历史信息表数据量过于庞大，往往在数据库设计中将引入分区表的逻辑来处理，具 体实现逻辑自查。3.3、 全量历史表算法拉链表。4、数据仓库.拉链表拉链表：数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录 历史。记录一个事物从开场，向来到当前状态的所有变化的信息。我们先看一个例如，这就是一拉链表，存储的是用户的最根本信息以

8、及每条记录的生命 周期。我们可以使用这表拿到最新的当天的最新数据以及之前的历史数据。在数据仓库的数据模型设计过程中，时常会遇到下面这种表的设计：1、有一些表的数据量很大，比方一用户表，大约10亿条记录，50个字段，这种表， 即使使用ORC压缩，单表的存储也会超过100G （在HDFS使用双备份或者三备份的话就更 大一些）。2、表中的局部字段会被update更新操作，如用户联系方式，产品的描述信息，定单的 状态等等。3、需要查看*一个时间点或者时间段的历史快照信息，比方，查看*一个定单在历史*一 个时间点的状态。4、表中的记录变化的比例和频率不是很大，比方，总共有10亿的用户，每天新增和发 生变

9、化的有200万摆布，变化的比例占的很小。则对于这种表我该如何设计呢”下面有几种方案可选：方案一：每天只留最新的一份（比方我们每天用Sqoop抽取最新的一份全量数据到Hive 中）。方案二：每天保存一份全量的切片数据。方案三：使用拉链表。4.1、 为什么使用拉链表现在我们对前面提到的三种发展逐个的分析。方案一这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最 新的.优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分 区什么的。缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比方从流水表里面抽。方案二每天一份全量的切片是一种比拟妥帖的方

10、案，而且历史数据也在。缺点就是存储空间占用量太大了，如果对这边表每天都保存一份全量，则每次全量中会 保存不少不变的信息，对存储是极大的浪费。固然我们也可以做一些取舍，比方只保存近一个月的数据”但是，需无耻的，数据的生 命周期不是我们能彻底摆布的。拉链表在使用上根本兼顾了我们的需求。首先它在空间上做了一个取舍，虽说不像方案一那样占用量则小，但是它每日的增量可 能惟独方案二的千分之一甚至是万分之一。其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取 历史的数据。所以我们还是很有必要来使用拉链表的。4.2、 拉链表的实现下面我们来举个栗子详细看一下拉链表。我们先看一下在My

11、sql关系型数据库里的user表息变化。2022-01-01这一天表中的数据是：在2022092这一天表中的数据是，用户002和期资料发展了修改，005是新增用户：在2022)M)3这一天表中的数据是，用户0M和(X)5资料发展了修改，(X)6是新增用户：如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一表，这是最新一天(即 2022。Q3)的数据：说明t_start_date表示该条记录的生命周期开场时间，t_end_date表示该条记录的生命周期完毕时 间。t_end_date = k9999-12-31，表示该条记录H前处于有效状态。如果查询当前所有有效的记录，则 select

12、* finm user wiiere t_end_date = 49999-12-31 如果查询 2022-01-02 的历史快照，则 select from user when3 t_start_datc = 42022-01-02 (*此处要好好理解，是拉链表比拟重要的一块。*)4.3、 拉链表在Hive中的实现在现在的大数据场景下，大局部的公司都会选择以Hdfs和Hive为主的数据仓库架构。 目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表智能发展删 除和添加操作，而不能发展update。基于这个前提，我们来实现拉链表。还是以上面的用户表为例，我们要实现用户

13、的拉链表。在实现它之前，我们需要先确定 一下我们有哪些数据源可以用。我们需要一 ODS层的用户全量表。至少需要用它来初始化。每日的用户更新表。而且我们要确定拉链表的时间粒度，比方说拉链表每天只取一个状态，也就是说如果一 天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大局部的问题 了。ods层的user表现在我们来看一下我们ods层的用户资料切片表的构造：CREATEE* TERNALTABLEods. user(user_numSTRINGMENT,用户编号，mobileSTRINGMENT手机，reg_daleSTRINGMENT注册日期，MENT，用户资料表PARTI

14、TIONEDBY( dtstring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY l LINESTERMINATEDBY， nSTOREDASORCLOCATION/ods/user;)ods 层的 usejupdale 表然后我们还需要一用户每Fl更新表，前面已经分析过该如果得到这表，现在我们假设它 已经存在。CREATEE* TERNALTABLEods. user_ update( usejnumSTRINGMENT用户编号， mobileSTRINGMENT手机， reg_dateSTRINGMENT注册日期 MENT，每日用户资料更新表 PARTITIO

15、NEDBY( dtslring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY C LINESTERMINATEDBY ， n STOREDASORCLOCATION7ods/user_update; ) 拉链表 现在我们创立一拉链表： CREATEE* TERNALTABLEdws. user. his( usejnumSTRINGMENT用户编号， mobileSTRINGMENT 手机， reg_dateSTRINGMENT用户编号， t_start_date. t_end_date MENT用户资料拉链表， ROWFORMATDELIMITEDFIELDSTE

16、RMINATEDBY t LINESTERMINATED BY n STOREDASORC LOCATION7dws/user_his; ) 见sql语句然后初始化的刘就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写一 下每日的更新语句。现在我们假设我们已经已经初始化了2022-0 J)l的日期，然后需要更新2022-01)2那一天 的数据，我们有了下面的Sql。然后把两个日期设置为变量就可以了。INSERTOVERWRITETABLEdws. user_ his SELECT* FROM ( SELECTA. uscr_num, A.mobile, Arcgjdatc、 A.t

17、_start_timc. CASEWHENA.t_end_time=9999-12-3 rANDB.user_numISNOTNULLTHEN2022-01-01, ELSEA.t_end_time ENDASt_ end_ time FROMdws.user_his A LEFTJOINods.user_update B ONA.user num=B.user numUNIONSELECTC.user_num,C.mobile,C.reg_date.*2O22-Ol-O2ASt_start_time.9999-12-3 rASt_end_timeFROMods. user_updatcASC

18、)AST好了，我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表， 下面对拉链表做一些小的补充。拉链表和流水表流水表存放的是一个用户的变更记录，比方在一流水表中，一天的数据中，会存放一个 用户的每条修改记录，但是在拉链表中惟独一条记录。这是拉链表设计时需要注意的一个粒度问题。我们固然也可以设置的粒度更小一些，一 股按天就足够。查询性能拉链表固然也会遇到查询性能的问题，比方说我们存放了5年的拉链数据，则这表势必 会比拟大，当行询的时候性能就比拟低了，个人认为两个思路来解决：在一些查询引擎中，我们对starjdate和end_date做索引，这样能提高不少性能。保存局部历史数据

19、，比方说我们一表里面存放全量的拉链表数据，然后再对外暴露一只 提供近3个月数据的拉链表。使用拉链表的时候可以不加t_end_date,即失效日期，但是加之之后，能优化不少查询。可以加之当前行状态标识，能快速定位到当前状态。在拉链表的设计中可以加一些容，因为我们每天保存一个状态，如果我们在这个状态里 面加一个字段，比方如当天修改次数，则拉链表的作用就会更大。5、对私数据仓库实战数据仓库主题，客户资产等级。何为客户资产等级，根据客户的纯资产的月均总额、贷 款余额的总额、信用k近一年消费额的总额，分别按照规则制定，计算出分别的等级，取三者 的最高等级，用于定义客户在我行的资产等级。分别为：私行、财富、理财、普通。源事实表：存款账户表、基金账户、理财账户、客户信息表、汇率表、信用卡交易表、 贷款余额表。