大数据项目之企业级数据仓库实战

目录

第一章 数据仓库概述

1.1数据仓库简介

1.1.1 什么是数据库？

1.1.2 什么是数据仓库？

1.1.3 OLTP与OLAP

1.2数据仓库技术架构

第二章 Hive 技术框架

2.2.1  数据存储

2.2.2数据压缩

3.1关系模式范式

3.1.2范式基本概念

 3.1.3一范式

3.1.4二范式

3.1.5三范式

3.2数据仓库建模基本理论

3.2.1数据仓库建模目标

3.2.2ER 实体模型

3.3.3ER 实体建模实例

3.3.4维度模型

---

第一章 数据仓库概述

1.1数据仓库简介

1.1.1 什么是数据库？

    数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

数据库是长期储存在计算机内、有组织的、可共享的数据集合。

     常用的数据库有 MySQL、ORACLE、SQL Server 等。

1.1.2 什么是数据仓库？

        数据仓库是决策支持系统（dss）和联机分析应用数据源的结构化数据环境。数据仓库研究和解决从数据库中获取信息的问题。

        数据仓库的特征在于面向主题、集成性、稳定性和时变性，用于支持管理决策。

        数据仓库存在的意义在于对企业的所有数据进行汇总，为企业各个部门提供统一的， 规范的数据出口。

面向主题：数据仓库中的数据是按照一定的主题域进行组织的，每一个主题对应一个宏观的分析领域。数据仓库排除对于决策无用的数据，提供特定主题的简明视图。

集成的：企业内不同业务部门数据的完整集成。

对于企业内所有数据的集成要注意一致性（假设财务系统中对于性别使用 F/M，而 OA 系统对性别使用 A/B，这就是数据不一致，如果想搭建企业级的数据仓库，需要数据具有一致性）。

稳定的：数仓里不存在数据的更新和删除操作。

变化的：数仓里会完整的记录某个对象在一段时期内的变化情况。

数据仓库的目标是实现集成、稳定、反映历史变化有组织有结构的存储数据的集合。

    图 1-1  数据仓库基本架构

库基本架

如上图所示，一个公司可能有多个业务系统，而数据仓库就是将所有的业务系统按照某种组织架构整合起来，形成一个仓储平台，也就是数仓。

   ODS----脱敏、清洗 ----DWD---汇总----DWS--汇总/宽表---DW

    三范式（关系型数据库）——目的：让字段拆分开，尽可能实现数据库没有冗余

  而数仓会利用冗余换取查询的便利——宽表

1.1.3 OLTP与OLAP

   操作型处理，叫联机事务处理OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。

  分析型处理，叫联机分析处理OLAP（On-Line Analytical Processing）一般针对某些主题的历史数据进行分析，支持管理决策。

OLTP 与 OLAP 的异同如表 1.1 所示：

表 1-1 OLTP与OLAP的异同

操作型处理	分析型处理
细节的	综合的或提炼的
实体-关系（ER）模型	星型或雪花模型
存取瞬间数据	存储历史数据，不包含最近的数据

可更新的	只读，只追加
一次操作一个单元	一次操作一个集合
性能要求高，响应时间短	性能要求宽松
面向事务	面向分析
一次操作数据量小	一次操作数据量大
支持日常操作	支持决策需求
数据量小	数据量大
客户订单、库存水平和银行账户等	客户收益分析、市场细分等

1.2数据仓库技术架构

图 1-2  数据仓库技术架构

数据仓库是一门综合性学科，理论与技术并重，不仅有高深的理论概念，而且还涉及大量的大数据相关技术框架，从数据采集到数据通道，从离线处理到实时处理，从集群监控到任务调度，数据仓库无所不含，因此，为了更好地掌握数据仓库技术，我们需要掌握相关的理论概念及技术框架。

第二章 Hive 技术框架

2.2.1  数据存储

1.行存储

基于 Hadoop 系统行存储结构的优点在于快速数据加载和动态负载的高适应能力，但是， 行存储不支持快速查询，当查询仅仅针对多列表中的少数几列时，它不能跳过不必要的列读取。同时，由于行存储混合着不同数据值的列，行存储不容易获得一个极高的压缩比，即空间利用率不易大幅提高，尽管通过熵编码和利用列相关性能够获得一个较好的压缩比，但是复杂数据存储实现会导致解压开销增大。

2.列存储

列存储在查询时列存储能够避免读不必要的列，并且压缩一个列中的相似数据能够达到较高的压缩比。

3.存储方式的选择

在数据仓库的搭建中，我们选择列存储方式。

对于列式存储范式，有三种存储格式可以选择：rcfile，Orc，Parquet。

若论对 Hive（以 MapReduce 为执行引擎）的支持 Orc 是最好的，但是若论对 Spark 等 Hadoop 生态圈中更多的技术框架，Parquet 的支持是最好的，而 Spark 作为 Hive 的执行引擎时性能非常好，因以我们这里毫无疑问地选择了 Parquet。

2.2.2数据压缩

Hive 中可用的压缩格式如下表所示：

表 2-5 Hive压缩格式

压缩格式	工具	算法	文件拓展名	是否可切分
DEFAULT	无	DEFAULT	.delate	否
gzip	gzip	DEFAULT	.gz	否
bzip2	bzip2	bzip2	.bz2	是
LZO	lzop	LZO	.lzo	是
Snappy	无	Snappy	.nappy	否

由于我们的存储格选用了 Parquet，在压缩格式方面 Parquet 支持 Snappy 和 Gzip，Gzip

的压缩比更高但解压缩的速度较慢，我们选择 Snappy。

第三章  数据仓库基本理论

3.1关系模式范式

  核心思想： 你在学习mysql的时候的建表思想：

  三大范式

  3.1.1范式理论概述

关系型数据库设计时，遵照一定的规范要求，目的在于降低数据的冗余性和数据的一致性，目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式

(BCNF)、第四范式(4NF)、第五范式(5NF)。

范式的标准定义是：符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。通俗地讲，范式可以理解为一张数据表的表结构所符合的某种设计标准的级别。

使用范式的根本目的是：减少数据冗余，尽量让每个数据只出现一次，获取数据时通过 join 拼接出最后的数据。

3.1.2范式基本概念

表 3-1 学生成绩表

学号	姓名	系名	系主任	课名	分数
20170901176	王小强	计算机系	马小腾	C 语言	95
20170901176	王小强	计算机系	马小腾	计算机基础	99
20170901176	王小强	计算机系	马小腾	高等数学	80
20170901179	李阳	经管系	王小石	经济学	95
20170901179	李阳	经管系	王小石	管理学	92
20170901186	张小俊	数学系	钱小森	高等数学	89
20170901186	张小俊	数学系	钱小森	线性代数	96

1. 函数依赖

若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值， 那么就可以说 Y 函数依赖于 X，写作  X →  Y。

也就是说，在数据表中，如果符合函数依赖，那么不存在任意两条记录，它们在 X 属性（或属性组）上的值相同，而在 Y 属性上的值不同。这也就是“函数依赖”名字的由来，类

似于函数关系 y = f(x)，在 x 的值确定的情况下，y 的值一定是确定的。

例如，对于表 3 中的数据，找不到任何一条记录，它们的学号相同而对应的姓名不同。所以我们可以说姓名函数依赖于学号，写作 学号 → 姓名。但是反过来，因为可能出现同名的学生，所以有可能不同的两条学生记录，它们在姓名上的值相同，但对应的学号不同， 所以我们不能说学号函数依赖于姓名。

表中其他的函数依赖关系还有如： 系名 → 系主任

学号 → 系主任

（学号，课名） → 分数

以下函数依赖关系则不成立： 学号 → 课名

学号 → 分数课名 → 系主任

（学号，课名） → 姓名

2.完全函数依赖

在一张表中，若 X → Y，且对于  X  的任何一个真子集（假如属性组  X  包含超过一个属性的话），X '   →  Y 不成立，那么我们称  Y 对于  X  完全函数依赖，记做：

 

例如：

学号 F→ 姓名

（学号，课名） F→ 分数  （注：因为同一个的学号对应的分数不确定，同一个课名 对应的分数也不确定）

3.部分函数依赖

假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于  X，那么我们就称  Y 部分函数依赖于  X，记做：

 

简单来说，（学号，课名）→ 系名，学号 → 系名，那么（学号，课名）p→ 系名。

4.传递函数依赖

假如  Z  函数依赖于  Y，且  Y  函数依赖于  X  ，且 Y 不包含于  X，X  不函数依赖于  Y，那么我们就称  Z  传递函数依赖于  X，记做：

简单来说，系名 → 系主任，学号 → 系名，那么学号 T→ 系主任。

 3.1.3一范式

一范式（1NF）：域应该是原子性的，即数据库表的每一列都是不可分割的原子数据项。
域：域就是列的取值范围，比如性别的域就是（男，女）

数据表中的所有列保持其原子性，不可再分。

表 3-2 不符合一范式的表格设计

ID	商品	商家	用户ID	产地
001	5 台电脑	XXX旗舰店	00001	河北省石家庄市裕华区

很明显如图 3-1 所示的表格设计是不符合第一范式的，商品列中的数据不是原子数据项，是可以进行分割的，因此对表格进行修改，让表格符合第一范式的要求，修改结果如图3-2 所示：

表 3-3 符合一范式的表格设计

ID	商品	数量	商家ID	用户ID	省	市	区
001	电脑	5	00254	00001	河北	石家庄	裕华区

实际上， 1NF   是所有关系型数据库的最基本要求， 你在关系型数据库管理系统

（RDBMS），例如 SQL Server，Oracle，MySQL 中创建数据表的时候，如果数据表的设计

不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在 RDBMS 中已经存在的数据表，一定是符合 1NF 的。

3.1.4二范式

二范式（2NF）：在 1NF 的基础上，实体的属性完全函数依赖于主关键字（混合主键）， 不能存在部分函数依赖于主关键字（混合主键）。

如果存在某些属性只依赖混合主键中的部分属性，那么不符合二范式。

数据表中的所有列，必须依赖于唯一的一个主键。

表 3-4 不符合二范式的表格设计

学生 ID	姓名	所属系	系主任	所修课程	分数
20170901176	王小强	计算机系	马小腾	000001	95
20170901176	王小强	计算机系	马小腾	000002	99

上述表格中是混合主键（学生 ID + 所修课程），但是所属系和系主任这两个属性只依赖于混合主键中的学生 ID 这一个属性，因此，不符合第二范式。

如果有一天学生的所属系要调整，那么所属系和系主任这两列都需要修改，如果这个学生修了多门课程，那么表中的多行数据都要修改，这是非常麻烦的，不符合第二范式。

为了消除这种部分依赖，只有一个办法，就是将大数据表拆分成两个或者更多个更小

的数据表。

表 3-5 符合二范式的表格设计（1）

学生 ID	所修课程	分数
20170901176	000001	95
20170901176	000002	99

系 ID	所属系	系主任
000001	计算机系	马小腾
000002	计算机系	马小腾

学生 ID	姓名
20170901176	王小强
20170901176	王小强

通过上述的修改，当一个学生的所属系需要调整时，不管学生修了多少门课程，都只需要改变表 3-5 中的一行数据即可。

3.1.5三范式

3NF 在 2NF 的基础之上，消除了非主属性对于主键（复合主键）的传递依赖。

表 3-6 不符合三范式的表格设计

订单ID	商品ID	商品颜色	商品尺寸	商家ID	用户ID
001	0001	深空灰	300*270*40	XXX旗舰店	00001

很明显，表 3-7 中，商品颜色依赖于商品 ID，商品 ID 依赖于订单 ID，那么非主属性商品颜色就传递依赖于订单 ID，因此不符合三范式，解决方案是将大数据表拆分成两个或者更多个更小的数据表。

数据中只能有唯一的一个主键。外键不能传递依赖于主键。

表 3-7 符合三范式的表格设计（1）

订单ID	商品ID	商家ID	用户ID
001	0001	XXX旗舰店	00001

商品ID	商品颜色	商品尺寸
0001	深空灰	300*270*40

3.2数据仓库建模基本理论

3.2.1数据仓库建模目标

数据仓库建模的目标是通过建模的方法更好的组织、存储数据，以便在性能、成本、效率和数据质量之间找到最佳平衡点。

访问性能：能够快速查询所需的数据，减少数据 I/O；DM集市（hbase ES）mysql

数据成本：减少不必要的数据冗余，实现计算结果数据复用，降低大数据系统中的数据成本和计算成本；

使用效率：改善用户应用体验，提高使用数据的效率；

数据质量：整合所有数据源的数据，改善数据统计口径的不一致性，减少数据计算错误的可能性，提供高质量的、一致的数据访问平台。

上述的四点之间是存在冲突的，为了提高访问性能，可能会提高数据冗余（减少 Join）， 这样会降低计算成本，但是会导致数据存储成本很高，并且由于数据的冗余，会提高数据统计口径不一致的风险，我们的目的是通过合理的设计在性能、成本、效率和数据质量之间找到平衡点。

3.2.2ER 实体模型

1. 基本理论

ER 模型是数据库设计的理论基础，当前几乎所有的 OLTP 系统设计都采用 ER 模型建模的方式。

在信息系统中，将事物抽象为“实体”、“属性”、“关系”来表示数据关联和事物描述；其中，实体：Entity，关系：Relationship，这种对数据的抽象建模通常被称为 ER 实体关系模型。

实体：通常为参与到过程中的主体，客观存在的，比如商品、仓库、货位、汽车，此实体非数据库的实体表；

属性：对主体的描述、修饰即为属性，比如商品的属性有商品名称、颜色、尺寸、重量、产地等；

关系：现实的物理事件是依附于实体的，比如商品入库事件，依附实体商品、货位， 就会有“库存”的属性产生；用户购买商品，依附实体用户、商品，就会有“购买数量”、

“金额”的属性产品。

2.实体之间的对照关系

实体之间建立关系时，存在对照关系：

1:1，即 1 对 1 的关系，比如实体人、身份证，一个人有且仅有一个身份证号；（A->B： 相互完全依赖，知道 A 一定确定 B，知道 B 一定确定 A）。

（动静分离：在数据库设计时，会将动态属性（年龄、地址、偏好、...）和静态属性（姓名、性别、身份证号、...）进行分离，剥离为两张表，一张父表，一张子表，从而提高性能）。

1:n，即 1 对多的关系，比如实体学生、班级，对于某 1 个学生，仅属于 1 个班级，而在 1 个班级中，可以有多个学生；（一张学生表，一张班级表，通过班级 ID 这个外键进行关联）。

n:m，即多对多的关系，比如实体学生、课程，每个学生可以选修多门课程，同样每个课程也可以被多门学生选修；（一张学生表，一张课程表，一张选课表）。

3.ER 建模的图形表示

在日常建模过程中：

“实体”：使用矩形表示；

“关系”：使用菱形表示；

“属性”：使用椭圆形表示；

所以 ER 实体关系模型也称作 E-R 关系图。

3.3.3ER 实体建模实例

1. 场景

学生选课系统，该系统主要用来管理学生和选修课程，其中包括课程选修、学生管理功能，现需要完成数据库逻辑模型设计。

1. 实现步骤

1. 抽象出主体 —— 学生，课程；
2. 梳理主体之间的关系 —— 选修；（学生与选修课程是一个多对多的关系）
3. 梳理主体的属性；
4. 画出 E-R 关系图；

 

图 3-4    ER 关系图

使用 ER 模型构建数据仓库的成功率是比较低的，因为涉及到“抽象出实体”这个过程， 这就涉及到将企业所有业务系统中的所有实体都抽象出来，这需要先梳理出所有的业务系 统实体，再梳理实体之间的关系，这是一个非常复杂的过程，可能你把公司所有的实体梳 理清楚了，可能业务又要调整了。

​​​​​​​3.3.4维度模型

维度建模的理论由 Ralph Kimball 提出，他提出将数据仓库中的表划分为事实表和维度表两种类型。

维度建模源自数据集市，主要面向分析场景。

“事实表”，用来存储事实的度量（measure）及指向各个维的外键值。“维度表”， 用来保存该维的元数据，即维的描述信息，包括维的层次及成员类别等。

简单的说，维度表就是你观察该事物的角度（维度)，事实表就是你要关注的内容。例如用户使用滴滴打车，那么打车这件事就可以转化为一个事实表，即打车订单事实表，然后用户对应一张用户维度表，司机对应一张司机维度表。

图 3-5    维度模型

1.事实表

在现实世界中，每一个操作型事件，基本都是发生在实体之间的，伴随着这种操作事件的发生，会产生可度量的值，而这个过程就产生了一个事实表，存储了每一个可度量的事件。

发生在现实世界中的操作性事件所产生的可度量数值，存储在事实表中。从最低的粒

度级别来看，事实表行对应一个度量事件，反之亦然。因此，事实表的设计完全依赖于物理活动，不受可能产生的最终报表的影响。除数字度量外，事实表总是包含外键，用于关联与之相关的维度，也包含可选的退化维度键和日期/时间戳。查询请求的主要目标是基于事

实表展开计算和聚集操作。

事实表往往包含三个重要元素：

1. 维度表外键
2. 度量数据
3. 事件描述信息

例如在电商场景中的一次购买事件，涉及主体包括客户、商品、商家，产生的可度量值包括商品数量、金额、件数等。

图 3-6  订单事实表

2.维度表

每个维度表都包含单一的主键列。维度表的主键可以作为与之关联的任何事实表的外键，当然，维度表行的描述环境应与事实表行完全对应。维度表通常比较宽，是扁平型非

规范表，包含大量的低粒度的文本属性。

比如商品，单一主键为商品 ID，属性包括产地、颜色、材质、尺寸、单价等，但并非属性一定是文本，比如单价、尺寸，均为数值型描述性的，日常主要的维度抽象包括：时间维度表、地理区域维度表等。

图 3-7 商品维度表

综上所述，如果针对用户的下单行为（单一商品）进行维度建模，我们可以得到如下模型：

 

图 3-8 维度建模实例