大数据之数据仓库实战_数仓项目实战

三范式

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

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

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

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

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

 数据仓库建模基本理论

数据仓库建模目标

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

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

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

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

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

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

ER 实体模型

1. 基本理论

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

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

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

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

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

“金额”的属性产品。

1. 实体之间的对照关系

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

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

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

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

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

1. ER 建模的图形表示

在日常建模过程中：

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

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

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

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

ER 实体建模实例

1. 场景

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

1. 实现步骤

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

图 3-4    ER 关系图

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

​​​​​​​维度模型

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

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

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

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

 事实表

图 3-5    维度模型

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

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

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

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

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

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

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

                                              图 3-6  订单事实表

2.  维度表

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

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

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

图 3-7 商品维度表

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

图 3-8 维度建模实例

​​​​​​​维度建模实例

1. 背景

某电商平台，经常需要对订单进行分析，以某宝的购物订单为例，以维度建模的方式设计该模型。

1. 事实表与维度表的划分

事实表为订单表、子订单表，维度包括商品维度、用户维度、商家维度、区域维度、时间维度。

1. 事实表分析

订单中包含的度量：商品件数、总金额、总减免金额； 描述性属性：下单时间、付款时间、订单状态等；

子订单包含度量：商品 ID、单价、减免金额；

描述性属性：加入购物车时间、下单时间、付款时间、状态；

1. 维度表分析

商品维度：商品 ID、商品名称、商品品类、单价、颜色、尺寸、生产商等； 用户维度：用户 ID、姓名、性别、生日、职业、信用值、收货地址等；

时间维度：日期 ID、日期、周几、是否周末、是否假期等；

区域维度：区域 ID，县/区、县/区 ID、市、市 ID、省、省 ID；

图 3-9  维度建模实例

订单表和子订单表是两张事实表，我们往往会避免事实表之间产生关系，因此会考虑 让子订单表中有一定的数据冗余，比如订单表和订单明细表都各自记录着用户 ID，区域 ID， 时间 ID 等，这样在查询子订单信息时，就不用通过关联订单表来获取上述数据了，但是子订单表中仍会保存订单 ID 字段。

​​​​​​​维度建模实例

1. 背景

某电商平台，经常需要对订单进行分析，以某宝的购物订单为例，以维度建模的方式设计该模型。

1. 事实表与维度表的划分

事实表为订单表、子订单表，维度包括商品维度、用户维度、商家维度、区域维度、时间维度。

1. 事实表分析

订单中包含的度量：商品件数、总金额、总减免金额； 描述性属性：下单时间、付款时间、订单状态等；

子订单包含度量：商品 ID、单价、减免金额；

描述性属性：加入购物车时间、下单时间、付款时间、状态；

1. 维度表分析

商品维度：商品 ID、商品名称、商品品类、单价、颜色、尺寸、生产商等； 用户维度：用户 ID、姓名、性别、生日、职业、信用值、收货地址等；

时间维度：日期 ID、日期、周几、是否周末、是否假期等； 区域维度：区域 ID，县/区、县/区 ID、市、市 ID、省、省 ID；

图 3-9  维度建模实例

订单表和子订单表是两张事实表，我们往往会避免事实表之间产生关系，因此会考虑 让子订单表中有一定的数据冗余，比如订单表和订单明细表都各自记录着用户 ID，区域 ID， 时间 ID 等，这样在查询子订单信息时，就不用通过关联订单表来获取上述数据了，但是子订单表中仍会保存订单 ID 字段。

​​​​​​​建模方法总结

ER 模型以及维度模型是当前主流的建模方法。

ER 模型常用于 OLTP 数据库建模，应用到构建数仓时更偏重数据整合，站在企业整体考虑，将各个系统的数据按相似性、一致性合并处理，为数据分析、决策服务，但并不便于直接用来支持分析。

ER 模型的特点如下：

1. 需要全面梳理企业所有的业务和数据流；
2. 实施周期长；
3. 对建模人员要求高；

维度建模是面向分析场景而生，针对分析场景构建数仓模型；重点关注快速、灵活的解决分析需求，同时能够提供大规模数据的快速响应性能。针对性强，主要应用于数据仓库构建和 OLAP 引擎低层数据模型。

1. 不需要完整的梳理企业业务流程和数据；
2. 实施周期根据主题边界而定，容易快速实现 demo  ；

星型模式、雪花模式与星座模式

​​​​​​​星型模式

图 3-1  星型模型

星型模式是维度模型中最简单的形式，也是数据仓库以及数据集市开发中使用最广泛的

形式。星型模式由事实表和维度表组成，一个星型模式中可以有一个或多个事实表，每个

事实表引用任意数量的维度表。星型模式的物理模型像一颗星星的形状，中心是一个事实表， 围绕在事实表周围的维度表表示星星的放射状分支，这就是星型模式这个名字的由来。

星型模式将业务流程分为事实和维度。事实包含业务的度量，是定量的数据，如销售价格、销售数量、距离、速度、重量等是事实。维度是对事实数据属性的描述，如日期、产品、客户、地理位置等是维度。一个含有很多维度表的星型模式有时被称为蜈蚣模式，显然这个名字也是因其形状而得来的。蜈蚣模式的维度往往只有很少的几个属性，这样可以简化对维度表的维护，但查询数据时会有更多的表连接，严重时会使模型难于使用，因此在设计中应该尽量避免蜈蚣模式。

​​​​​​​雪花模式

图 3-2  雪花模型

雪花模式是一种多维模型中表的逻辑布局，其实体关系图有类似于雪花的形状，因此得名。

与星型模式相同，雪花模式也是由事实表和维度表所组成。所谓的“雪花化”就是将行星模型中的维度表进行规范化处理。当所有的维度表完成规范化后，就形成了以事实表为

中心的雪花型结构，即雪花模式。将维度表进行规范化的具体做法是，把低基数的属性从维度表中移除并形成单独的表。基数指的是一个字段中不同值的个数，如主键列具有唯一值， 所以有最高的基数，而像性别这样的列基数就很低。

在雪花模式中，一个维度被规范化成多个关联的表，而在星型模式中，每个维度由一个单一的维度表所表示。一个规范化的维度对应一组具有层次关系的维度表，而事实表作为雪花模式里的字表，存在具有层次关系的多个父表。

​​​​​​​星座模式

图 3-3  星座模式

数据仓库由多个主题构成，包含多个事实表，而维表是公共的，可以共享，这种模式可以看做星型模式的汇集，因而称作星系模式或者事实星座模式。

​​​​​​​模型的选择

在数据仓库建模时，会涉及到模式的选择，我们要根据不同模式的特点选择适合具体业务的模式：

冗余：雪花模型符合业务逻辑设计，采用 3NF 设计，有效降低数据冗余；星型模型的维度表设计不符合 3NF（如果是雪花模型改造成了星型模型，那么肯定不符合 3NF，因为一定发生了表的整合，即降维，一定有传递依赖，但是，并不是所有的星型模型都不符合

3NF，很多星型模型的表是符合 3NF 的），反规范化，维度表之间不会直接相关，牺牲部分存储空间。（雪花模型的维度之间是有关联的）

性能：雪花模型由于存在维度间的关联，采用 3NF 降低冗余，通常在使用过程中，需要连接更多的维度表，导致性能偏低；星型模型反三范式，采用降维的操作将维度整合，以存储空间为代价有效降低维度表连接数，性能较雪花模型高。（ 星型表的数据冗余大，是用存储空间换取效率 ）（ BI 的一些工具对于星型模型的支持更规范化 ）

ETL：雪花模型符合业务 ER 模型设计原则，在 ETL 过程中相对简单，但是由于附属模型的限制，ETL 任务并行化较低（由于雪花模型中有很多的维度依赖，在 ETL 的时候， 需要在保持 3NF 的前提下对数据进行清洗，即对数据一致性/规范化的处理，例如数据来自于多个业务系统，各个系统对于用户的定义不一致，此时要对每个业务定义的用户数据进行

​​​​​​​代理键

维度表中必须有一个能够唯一标识一行记录的列（最好是原子性的列，不要是组合键）， 通过该列维护维度表与事实表之间的关系，一般在维度表中业务主键符合条件可以当作维度主键。

但是，数据仓库是整个公司数据的整合，这会涉及到多个数据源有相同维度，那么就会出现以下两个问题：

1. 当整合多个数据源的维度时，不同数据源的业务主键重复怎么办？
2. 涉及维度拉链表时，同一主体多条记录，业务键重复怎么办？

表 5.1 财务部门维度表

表 5.2 研发部门维度表

如上图所示，业务键重复，我们可以引入代理键，如下表所示：

表 5.3 引入代理键

把多个系统的数据复合在一起，同时再维护一个代理键，而且代理键在这个维度表里是唯一标识一条记录的，类似于业务系统的业务键。

代理键是由数据仓库处理过程中产生的、与业务本身无关的、唯一标识维度表中一条记录并充当维度表主键的列，也是描述维度表与事实表关系的纽带。

在设计有代理键的维度表中，事实表中的关联键是代理键而不是原有的业务主键，即

业务关系是靠代理键维护，这样有效避免源系统变化对数仓数据对影响。

在实际业务中，代理键通常是数值型、自增的值。

​​​​​​​稳定维度

部分维度表的维度是在维度表产生后，属性是稳定的、无变化的。比如时间维度、区 域维度等，针对这种维度，设计维度表的时候，仅需要完整的数据，不需要天的快照数据， 因为当前数据状态就是历史数据状态。

图 5-1  稳定维度

​​​​​​​缓慢渐变维

维度数据会随着时间发生变化，变化速度比较缓慢，这种维度数据通常称作缓慢渐变维，例如电商平台的用户维度表，用户可能会随着时间推移改变收件地址，因此用户维度表

中的收件地址就是一个缓慢变化维。由于数据仓库需要追溯历史变化，尤其是一些重要的数据，所以历史状态也需要采取一定的措施进行保存，保存历史状态的方式有以下三种：

1. 每天保存当前数据的全量快照数据（每天一个新增分区），该方案适合数据量较小

（根据公司具体的配置而定）的维度，使用简单的方式保存历史状态。

1. 在维表中添加关键属性值的历史字段，仅保留上一个的状态值。可能同时有多个属性都非常重要，而且只能追溯上一个数据，不是所有的历史数据，这种范式应用场景较少。
2. 拉链表：当维度数据发生变化时，将旧数据置为失效，将更改后的数据当作新的记录插入到维度表中，并开始生效，这样能够记录数据在某种粒度上的变化历史。

拉链表详解 

将数据的变更当做流水记录下来 ，旧的设为失效，新的设为生效，如果粒度为天，那么就可以得到一天的最终状态作为最终状态。

表 5-4 拉链表

表 5-4 中每条记录都有一个 End_date，当有新的数据产生时，在旧数据的 End_date 字段中插入日期，然后新插入一条数据，新数据的 End_date 字段中是一个永久有效的值，如果再发生更新，上一次更新数据的 End_date 字段设置为当前日期，然后再次插入新数据， 新数据的 End_date 字段中设置一个永久有效的值。

如果想知道某个员工在 5 月 22 号时在哪个部门，那么可以通过如下 SQL：

Select * from user where start_date<= 2018-05-22 and end_date>= 2018-05-22

根据拉链表的结构，如果对维度表做拉链，那么一个维度实体必然存在多条记录，也就是一个主键 ID 对应多条数据，此时维度表的原子性主键也就没有意义了。

维度表做拉链后会失去原子性主键，那么拉链维度表如何和事实表进行关联呢？ 此时就要用到代理键，也就是在事实表和维度表中同时添加代理键，如下图所示：

表 5-5 用户详情拉链表

表 5-6 订单表

完成代理键的添加后，在之后的统计中，按照代理键进行聚合即可。

事实表来源于业务事务表，代理键和业务本身没有关系，那么怎么在新增数据时在事实表中装载代理键？

当事实表中有新增数据时，新增数据中记录了维度表中原有的原子性主键，可以根据原有的主键匹配维度表中的数据，然后根据新增数据的时间范围找到匹配的代理键，然后在事实表的新增数据中加入代理键。

数据仓库规范

1，​​​​​​​命名规范

1. 根据业务过程，抽象基本术语单元；
2. 对相应的术语单元做语义翻译，可以采用拼音、英文、含义数字，避免英文、拼音混用；
3. 拼音、英文尽量在不失原意的情况下采用缩写形式；
4. 避免数字开头；

表 6-1   命名规范示例

 表命名规范

数据仓库中表格的命名规范如下表所示：

表 6-2 表命名规范

在表格的命名中，我们要求能够合理的区分出表所描述的数据域、数据周期等。命名格式：层次_数据域_修饰/描述_范围/周期

根据表的命名规范，我们对以下的数据表进行命名：

订单相关数据表：

DWD 层：d_ord_info_d

DWS 层：s_ord_st_d

维度表：

用户维度：dim_user_d

商品缓慢渐变维表：dim_product_l

ODS 层表：

（对于 ODS 层表，最好能够区分数据来源，包括来自什么系统、数据源名称） 业务系统编码：buss

业务系统订单表：loan_order

Ods 层表命名：o_buss_loan_order_d

​​​​​​​字段命名规范

设计模型的时候，按照业务含义、业务术语规范命名字段汉字名称。

1. 避免数字开头
2. 同一业务含义统一命名，避免不同表达方式
3. 统一书写格式，比如用户 id:user_id，用户姓名：userName 非统一格式
4. 统一大小写，建议统一小写
5. 避免与关键字、自定义 udf 重名

根据字段命名对字段进行命名，如下表所示：

表 6-3 字段命名规范

​​​​​​​脚本命名规范

数据仓库中会使用到很多自动化脚本，脚本的命名规则如下：

1. ETL 脚本名称尽可能和所产出的表同名
2. 数据采集、数据推送脚本尽可能标识数据去向
3. ETL 脚本若产生多个表，采用对应的数据域和语义描述命名
4. Jar 包命名以实际的业务处理逻辑语义描述为主，调度任务命名同样尽量以产出表名命名

根据脚本命名规范，我们对如下的脚本进行命名：

1. 数据采集过程

采集数据到 ODS 层的表 o_buss_loan_order_d，我们按照输出表 d_ord_ino_d 对脚本和任务进行命名：

数据采集脚本命名：imp_o_buss_loan_order_d.sh、imp_o_buss_loan_order_d.py

1. 订单 ETL 过程
2. 2020/12/9/order/ODS
3. 2020/12/9/USER/ODS

从 ODS 层的表 o_buss_loan_order_d 整理数据并且装载到 DWD 层表 d_ord_ino_d 中， 我们按照输出表 d_ord_ino_d 对脚本和任务进行命名：

ETL 脚本命名： d_ord_info_d.sh、d_ord_info_d.py、d_ord_info_d.hql、d_ord_info_d.jar

ETL 任务命名：d_ord_info_d

1. 一个 ETL 脚本产出多个表，比如从商品表中分离出商品维度、厂家维度：

ETL 脚本命名：dim_product_mfrs_d.sh、dim_product_mfrs_d.py、dim_product_mfrs_d.hql、

dim_product_mfrs_d.jar

ETL 任务名称：dim_product_mfrs_d

​​​​​​​开发规范

1.   数仓中 MR 程序尽可能统一输入参数、输出参数，单个 jar 程序的功能模块清晰， 避免多种处理逻辑写入一个 jar 包；
   1. ETL 脚本格式、备注清晰，避免大范围、格式杂乱的脚本，合理利用临时表
   2. 每个 ETL 脚本尽可能产出一张数仓表，方便任务排查，同时也减少数仓表的耦合性；
2. 字段列对齐；
3. 关键字列对齐；
4. ​​​​​​​禁止使用 Tab，全部使用 4 个空格代替； 标准的 ETL 脚本格式如下图所示：
5. 

图 6-1 ETL 脚本格式