离线数仓（五）【数据仓库建模】

前言

        今天开始正式数据仓库的内容了, 前面我们把生产数据 , 数据上传到 HDFS , Kafka 的通道都已经搭建完毕了, 数据也就正式进入数据仓库了, 解下来的数仓建模是重中之重 , 是将来吃饭的家伙 ! 以及 Hive SQL 必须熟练到像喝水一样 !

第1章 数据仓库概述

1.1 数据仓库概念

        数据仓库 (dataware,简称 DW) 是一个为数据分析而设计的企业级数据管理系统。数据仓库可集中、整合多个信息源的大量数据，借助数据仓库的分析能力，企业可从数据中获得宝贵的信息进而改进决策。同时，随着时间的推移，数据仓库中积累的大量历史数据对于数据科学家和业务分析师也是十分宝贵的。

        数据仓库必须具备存储 , 管理 , 分析和计算的能力 !

        数据仓库并不是数据的最终目的地 , 而是提供给数据仓库下游的应用 !

1.2 数据仓库核心架构

        Hive 一般都是用来作为我们数据仓库的主体 , 因为它具备数仓必备的存储 (底层是 HDFS , 所以可以存储海量数据) , 管理 (Hive 可以将我们 HDFS 中的数据映射成一张张的二维表)  以及分析计算 (Hive 支持通过 Hive SQL 来对二维表进行分析查询, 它的引擎可以是 MR / Tez / Spark)

        业务系统: 就是企业当中支撑公司核心业务的系统 , 比如电商公司的核心就是电商业务系统 , 那么它产生的大量的业务数据(比如订单数据,用户信息数据等)和用户行为日志数据(比如点击某个按钮,收藏) 对我们数据的分析都是非常有意义的,都需要采集到数仓当中 .

        DataX 是一个基于查询的全量采集工具 ( select * ) , 而 Maxwell 是基于 binlog 的一个增量数据采集工具 .它们都是业务数据采集工具 (从 MySQL 这种关系型数据库采集到 HDFS) , 而用户行为数据我们用的一般是 Flume .

        数据采集到数仓之后就需要把这些 HDFS 文件映射成一张张二维表了 (通过 load 语句),  之后我们就可以开始进行数仓建模了 . 所谓的数据建模就是对数据进行更加合理高效的存储整理 , 最终我们的数据就被分为多层 , 每层存储的都是一张张二维表 , 而且每一层都有自己的处理逻辑 , 每一层都是从上一层计算的结果 .

        整个数仓的重点就是 Hive 了 , 我们的主要工作其实就是数仓的建模和写 SQL  . 关于建模我们要知道每一层的每一张表有哪些字段 , 每一行每一列分别代表什么含义 . SQL 是对数据进行处理 , 以便发送给下一层 .

        Hive 数仓中不同层之间需要执行不同的 SQL , 而且必须等待上一层执行完才能执行 , 这就需要一个调度框架来协调任务的执行了 ( linux 的 crontab 命令并不能满足这个需求 , 因为 crontab 并不能确定上一个任务是否执行完毕 , 估算可能会出现误差 ) 这里我们用的是 Dolphin Scheduler 这是一个国产的工作流程定时调度器 (工作流程是由一个个的工作单元组成的 , 就比如我们这里每一层的 SQL )

        这里强调最重要的就是数仓建模和 SQL , 菜就多练 !

第2章 数据仓库建模概述

2.1 数据仓库建模的意义

        如果把数据看作图书馆里的书，我们希望看到它们在书架上分门别类地放置；如果把数据看作城市的建筑，我们希望城市规划布局合理；如果把数据看作电脑文件和文件夹，我们希望按照自己的习惯有很好的文件夹组织方式，而不是糟糕混乱的桌面，经常为找一个文件而不知所措。

        数据模型就是数据组织和存储方法，它强调从业务、数据存取和使用角度合理存储数据。只有将数据有序的组织和存储起来之后，数据才能得到高性能、低成本、高效率、高质量的使用。

• 高性能：良好的数据模型能够帮助我们快速查询所需要的数据。(比如说使用宽表来减少作业中多表 join 的计算开销)
• 低成本：良好的数据模型能减少重复计算，实现计算结果的复用，降低计算成本。
• 高效率：良好的数据模型能极大的改善用户(数仓的用户: 比如下游的应用)使用数据的体验，提高使用数据的效率。
• 高质量：良好的数据模型能改善数据统计口径 (防止歧义) 的混乱，减少计算错误的可能性。

2.2 数据仓库建模方法论

目前数仓用的更多的是下面的维度模型，至于 ER 模型更多的是在关系型数据库中用的比较多 。

2.2.1 ER模型 (了解)

        数仓库之父Bill Inmon ( 比尔沂蒙 )提出的建模方法是从全企业的高度，用实体关系（Entity Relationship，ER）模型来描述企业业务，并用规范化 ( 数据库规范化 , 范式等级一般为第三范式 ) 的方式表示出来，在范式理论上符合3NF。       

        比如说学生管理系统 , 对于学生和班级这两个实体的关系,一个学生一般对应一个班级,但是一个班级对应多个学生,所以我们就可以在学生表中使用外键来关联班级信息.

        同样 , 比如学生表和课程表 , 一个学生可能有多门课程 , 一们课程也有多个学生 , 这种关系用外键是实现不了的 , 我们可以在两张表中间加入一张选课表 , 表中主要就俩字段 学生id和课程id 就足以说明了两者之间的关系了 。

1）实体关系模型

        实体关系模型将复杂的数据抽象为两个概念——实体和关系。实体表示一个对象，例如学生、班级，关系是指两个实体之间的关系，例如学生和班级之间的从属关系。

2）数据库规范化

        数据库规范化是使用一系列范式 (normal form) 设计数据库（通常是关系型数据库）的过程，其目的是减少数据冗余 (数据重复 , 比如有多张表都存储了同一个字段,比如一张表的一个字段值有重复 ; 因为数据冗余会浪费存储空间) ，增强数据的一致性 (正因为存在数据冗余 , 同一个字段被多次存储在多张表中 , 如果一张表进行了修改但是别的表没有修改 , 这就造成了数据的不一致性问题)。

        这一系列范式就是指在设计关系型数据库时，需要遵从的不同的规范。关系型数据库的范式一共有六种，分别是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF）。遵循的范式级别越高，数据冗余性就越低。

3）三范式

        范式必须按顺序去遵守 , 比如遵循了第一范式 , 才能遵循第二范式 , 遵循了第二范式才能遵循第三范式 ... 但是遵循范式并不是越多越好 , 范式级别越高 , 我们的表就被拆分的越细 , 我们的关系模型就变得复杂 , 而且我们稍微复杂一点的查询可能就需要 join 了 . 一般我们都会做一个折中的取舍 , 选择第三范式 , 甚至是第二范式 .

（1）函数依赖

完全函数依赖:

        比如 f(x,y) = z ; 我们必须知道三个未知数 x,y,z 中的两个值才能知道另一个的值 . 对应到表中就比如我们必须通过 (学号,课程名) 才能得到该学生的分数 , 缺一不可 .

部分函数依赖:

        比如我们可以通过 (学号 , 课程名) 来得到学生的姓名 , 但是要知道学生的姓名其实并不需要得到课程名 , 这里的 姓名就是部分依赖于  (学号 , 课程名) 的 .

传递函数依赖:

        比如 f(x) = y , g(y) = z , 那么我们就可以通过知道 x 的值来得到 z .对应到表中就是我们可以通过学生的学号知道这个学生是哪个系的 , 然后通过系名就可以知道这个系的主任是谁 , 这里就是系主任传递函数依赖于学号.

（2）第一范式 (1NF)

核心原则: 属性不可切分 , 对应到表中就是字段不可切分

ID	订单	商家id	用户id
001	联想小新Pro15 * 5	xxx旗舰店	00001

这里上面的属性 "订单" 就不符合第一范式 , 因为它可以再拆分为: 

第一范式非常简单 , 它并没有函数依赖 , 但是非常重要 , 因为它是所有范式的基础 .

（3）第二范式 (2NF)

核心原则: 不能存在 "部分函数依赖" , 对应到表中就是 "不能存在非主键字段部分函数依赖于主键字段"

        这里的主键是由 "学号" 和 "课名" 组成的联合主键 , 可以看到 , 这张表中冗余的部分: 姓名 , 系名和系主任都是部分依赖函数于联合主键字段中的学号的 , 而分数是完全依赖于这个联合主键的 .

        消除函数依赖实现第二范式的方式就是把这张表中完全依赖的部分单独拆出来:

        这样就既满足了第二范式 , 也解决了数据的冗余 . 但是我们还会发现 , 在第二张表中依然存在系名和系主任数据冗余的问题 , 这就需要我们来了解一下第三范式了 :

（4）第三范式 (3NF)

核心: 不能存在传递函数依赖 , 对应到我们的关系型数据库表中就是 , 不能存在非主键字段传递函数依赖于主键字段

         这里的学号可以推出系名 ,  然后系名可以推出系主任 , 所以这里存在系主任传递函数于学号( 主键 ) , 我们需要继续拆表: 

下面我们看一个采用 Bill Inmon 倡导的建模方法(ER 模型) 构建的模型 :

        我们可以看到一张订单明细表现在被拆分成了十几张表 , 这种建模方法的出发点是整合数据，其目的是将整个企业的数据进行组合和合并，并进行规范处理，减少数据冗余性，保证数据的一致性。这种模型并不适合直接用于分析统计。 

        假如我们要写一个查询实现统计出每个国家 2024 年的订单金额的总额 , 那么我们就需要对上面接近 10 张的表进行 join 操作 !

2.2.2 维度模型 (重点)

        数据仓库领域的另一位大师——Ralph Kimball倡导的建模方法为维度建模。维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程（行为），而维度通常对应业务过程发生时所处的环境（人时地）。

注：业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单，付款，退单等，都是业务过程。

        下图为一个典型的维度模型，其中位于中心的SalesOrder为事实表，其中保存的是下单这个业务过程的所有记录。位于周围每张表都是维度表，包括Date（日期），Customer（顾客），Product（产品），Location（地区）等，这些维度表就组成了每个订单发生时所处的环境，即何人、何时、在何地下单了何种产品。从图中可以看出，模型相对清晰、简洁。

        维度建模以数据分析作为出发点，为数据分析服务，因此它关注的重点是用户如何更快的完成需求分析以及如何实现较好的大规模复杂查询的响应性能。

        但是维度模型的缺点也显而易见，那就是数据冗余（主要是维度表），数据冗余的问题主要就是存储浪费和数据一致性问题，对我们的 Hive 数仓来说根本不是问题，至于数据一致性，由于我们数仓的数据写进来后是不怎么会去修改的，所以数据一致性也不是问题。

第3章 维度建模理论之事实表

牢记：所有的事实表它的字段都是由 维度外键 和 度量指标 组成的！

3.1 事实表概述

        事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程（下单、退款、付款）来设计。其包含与该业务过程有关的维度引用（维度表外键）以及该业务过程的度量（通常是可累加的数字类型字段，用来量化业务过程的字段，比如用下单总金额或者下单的商品数量来量化下单这个业务过程）。

3.1.1 事实表特点

        事实表通常比较“细长”，即列较少，但行较多，且行的增速快。

3.1.2 事实表分类

        事实表有三种类型：分别是事务事实表、周期快照事实表和累积快照事实表，其中，事务事实表占据主要地位，也就是说事务事实表是常见的，但是周期快照事实表和累积快照事实表是可以不存在的。每种事实表都具有不同的特点和适用场景，下面逐个介绍。

3.2 事务型事实表（重点）

3.2.1 概述

        事务型事实表用来记录各业务过程，它保存的是各业务过程的原子操作事件，即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度。比如订单信息表和订单明细表，订单信息表中每一行存储的是每个订单（包含多个商品）的数据，而订单明细表中每一行存储的是每个订单中的每个商品的信息。

        事务型事实表可用于分析与各业务过程相关的各项统计指标，由于其保存了最细粒度的记录，可以提供最大限度的灵活性，可以支持无法预期的各种细节层次的统计需求。

3.2.2 设计流程

        设计事务事实表时一般可遵循以下四个步骤：选择业务过程→声明粒度→确认维度→确认事实

1）选择业务过程

        在业务系统中，挑选我们感兴趣的业务过程（也就是我们的需求），业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单，付款，退单等，都是业务过程。通常情况下，一个业务过程对应一张事务型事实表。

2）声明粒度（确定每行代表什么）

        业务过程确定后，需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示什么，应该尽可能选择最细粒度，以此来应各种细节程度的需求。

典型的粒度声明如下：

        订单事实表中一行数据表示的是一个订单中的一个商品项。

3）确定维度（确定维度外键）

        确定维度具体是指，确定与每张事务型事实表相关的维度有哪些。

        确定维度时应尽量多的选择与业务过程相关的环境信息。因为维度的丰富程度就决定了维度模型能够支持的指标丰富程度。

4）确定事实（确定度量指标）

        此处的“事实”一词，指的是每个业务过程的度量值（通常是可累加的数字类型的值，例如：次数、个数、件数、金额等）。

        经过上述四个步骤，事务型事实表就基本设计完成了。第一步选择业务过程可以确定有哪些事务型事实表，第二步可以确定每张事务型事实表的每行数据是什么，第三步可以确定每张事务型事实表的维度外键，第四步可以确定每张事务型事实表的度量值字段。

3.2.3 不足

        事务型事实表可以保存所有业务过程的最细粒度的操作事件，故理论上其可以支撑与各业务过程相关的各种统计粒度的需求。但对于某些特定类型的需求，其逻辑可能会比较复杂，或者效率会比较低下。例如：

1）存量型指标

        例如商品库存，账户余额等。此处以电商中的虚拟货币（淘金币、京豆）为例，虚拟货币业务包含的业务过程主要包括获取货币和使用货币，两个业务过程各自对应一张事务型事实表，一张存储所有的获取货币的原子操作事件，另一张存储所有使用货币的原子操作事件。

        假定现有一个需求，要求统计截至当日的各用户虚拟货币余额。由于获取货币和使用货币均会影响到余额，故需要对两张事务型事实表进行聚合，且需要区分两者对余额的影响（加或减），另外需要对两张表的全表数据聚合才能得到统计结果。

        可以看到，不论是从逻辑上还是效率上考虑（事务事实表非常大，做聚合操作比较耗时），这都不是一个好的方案。

2）多事务关联统计

        例如，现需要统计最近30天，用户下单到支付的时间间隔的平均值。统计思路应该是找到下单事务事实表和支付事务事实表，以订单id作为关联条件join得到下单时间和支付时间，过滤出最近30天的记录，然后按照订单id对两张事实表进行关联，之后用支付时间减去下单时间，然后再求平均值。

        逻辑上虽然并不复杂，但是其效率较低，应为下单事务事实表和支付事务事实表均为大表，大表 join 大表的操作应尽量避免。

        可以看到，在上述两种场景下事务型事实表的表现并不理想。下面要介绍的另外两种类型的事实表就是为了弥补事务型事实表的不足的。其中，周期型快照事实表就是用来解决存量型指标的，而累积型快照事实表就是用来解决多事务关联统计的。

3.3 周期型快照事实表

3.3.1 概述

        周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，主要用于分析一些存量型（例如商品库存，账户余额）或者状态型（空气温度，行驶速度）指标。

        对于商品库存、账户余额这些存量型指标，业务系统中（MySQL）通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求，而无需再对事务型事实表中大量的历史记录进行聚合了。

        周期快照表通常都是分区表，根据日期分区。

        核心思想就是直接利用业务系统重现有的结果，而不是费时地去对事务事实表（大表）进行聚合。

        对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作（原子事务操作：比如下单买了几件商品花了多少钱），所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表（比如每隔 1 小时记录一下空气温度的变化值）。

3.3.2 设计流程

1）确定粒度（确定每行是什么，以及部分列代表什么）

        周期型快照事实表的粒度可由采样周期和维度描述，故确定采样周期和维度后即可确定粒度。

        采样周期通常选择每日。

        维度可根据统计指标决定，例如指标为统计每个仓库中每种商品的库存，则可确定维度为仓库和商品。

        确定完采样周期和维度后，即可确定该表粒度为每日-仓库-商品（所以这个周期快照表的一行代表每天每个仓库每个商品的某个指标值，我们也就确定了除了度量指标的三个列：日期 + 仓库id + 商品id ）。

2）确认事实

        事实也可根据统计指标决定，例如指标为统计每个仓库中每种商品的库存，则事实为商品库存。到这里我们也就确定了最后一个字段（度量指标）：库存数量。

3.3.3 事实类型

        此处的事实类型是指度量值的类型，而非事实表的类型。事实（度量值）共分为三类，分别是可加事实，半可加事实和不可加事实。

1）可加事实

        可加事实是指可以按照与事实表相关的所有维度进行累加，例如事务型事实表中的事实。

2）半可加事实

        半可加事实是指只能按照与事实表相关的一部分维度进行累加，例如周期型快照事实表中的事实。以上述各仓库中各商品的库存每天快照事实表为例，这张表中的库存事实可以按照仓库或者商品维度进行累加，但是不能按照时间维度进行累加，因为将每天的库存累加起来是没有任何意义的。

总结：事务型事实表中的事实都是可加事实，周期型快照事实表中的事实都是半可加事实！

3）不可加事实

        不可加事实是指完全不具备可加性，例如比率型事实（比如退货率=退货数/下单数，我们假如有一张表有两个字段：商品id，退货率；显然不管根据商品id这个维度还是退货率这个度量指标都是无法累加的）。不可加事实应尽量避免，所以通常需要转化为可加事实，例如比率可转化为分子和分母。

3.4 累积型快照事实表

3.4.1 概述

        累计快照事实表是基于一个业务流程（区别于业务过程，业务过程指的是一个业务的原子操作，而业务流程是由多个有关联的业务过程组成的）中的多个关键业务过程联合处理而构建的事实表，如交易流程中的下单、支付、发货、确认收货业务过程。

        累积型快照事实表通常具有多个日期字段，每个日期对应业务流程中的一个关键业务过程（里程碑）比如下面的 下单日期  -> 支付日期  -> 发货日期 -> 收货日期。

订单id	用户id	下单日期	支付日期	发货日期	确认收货日 期	订单金额	支付金额
1001	1234	2020-06-14	2020-06-15	2020-06-16	2020-06-17	1000	1000

维度外键（多个业务过程对应的维度外键）：

订单id

用户id

下单日期

支付日期

发货日期

确认收货日 期

度量值（多个业务过程的度量值）：

订单金额

支付金额

        累积型快照事实表主要用于分析业务过程（里程碑）之间的时间间隔等需求。例如前文提到的用户下单到支付的平均时间间隔，使用累积型快照事实表进行统计，就能避免两个事务事实表的关联操作，从而变得十分简单高效。

        这里的累积指的是这张表不是一次创建好的，比如用户下单，我们就可以从订单事务事实表中拿到下单日期和下单金额放到我们这张表中；过了一天用户支付，我们又可以从支付事务事实表中拿到支付日期和支付金额到这张表 ... 直到用户收货，我们就把这张表补充完整了。从而省去了多表 join 的过程。

3.4.2 设计流程

        累积型快照事实表的设计流程同事务型事实表类似，也可采用以下四个步骤，下面重点描述与事务型事实表的不同之处。

选择业务过程→声明粒度→确认维度→确认事实。

1）选择业务过程

        选择一个业务流程中需要关联分析的多个关键业务过程，多个业务过程对应一张累积型快照事实表（对比我们之前事务型事实表选择业务的过程：选择感兴趣的业务过程，一个业务过程对应一张事务事实表）。

2）声明粒度

        精确定义每行数据表示的是什么，尽量选择最小粒度。

3）确认维度

        选择与每个业务过程相关的维度，需要注意的是，每各业务过程均需要一个日期维度。

4）确认事实

        选择各业务过程的度量值。

第4章 维度建模理论之维度表

4.1 维度表概述

        维度表是维度建模的基础和灵魂。前文提到，事实表紧紧围绕业务过程进行设计，而维度表则围绕业务过程所处的环境（何人何时何地）进行设计。维度表主要包含一个主键和各种维度字段，维度字段称为维度属性。

4.2 维度表设计步骤

1）确定维度（表）

        在设计事实表时，已经确定了与每个事实表相关的维度，理论上每个相关维度均需对应一张维度表。需要注意到，可能存在多个事实表与同一个维度都相关的情况（比如下单表和支付表这两个事务事实表都存在用户id这个维度外键），这种情况需保证维度的唯一性，即只创建一张维度表。另外，如果某些维度表的维度属性很少（比如支付方式表没有必要去单独创建一个维度表，因为它就一个支付方式字段），则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中，这个操作称为维度退化。

pay_id	支付方式
1	微信
2	支付宝
3	银联

        注意：前面我们说事实表只有两种字段：维度外键和度量指标，这里我们引入了第三种字段：维度退化字段。

2）确定主维表和相关维表

        此处的主维表和相关维表均指业务系统中与某维度相关的表。例如业务系统中与商品相关的表有sku_info，spu_info，base_trademark，base_category3，base_category2，base_category1等，其中sku_info就称为商品维度的主维表（通常情况下粒度最细的是主维表），其余表称为商品维度的相关维表。维度表的粒度通常与主维表相同。

3）确定维度属性

        确定维度属性即确定维度表字段。维度属性主要来自于业务系统中与该维度对应的主维表和相关维表。维度属性可直接从主维表或相关维表中选择，也可通过进一步加工得到。

确定维度属性时，需要遵循以下要求：

（1）尽可能生成丰富的维度属性

        维度属性是后续做分析统计时的查询约束条件、分组字段的基本来源，是数据易用性的关键。维度属性的丰富程度直接影响到数据模型能够支持的指标的丰富程度。

（2）尽量不使用编码，而使用明确的文字说明，一般可以编码和文字共存。

        比如支付方式，如果有这样一张维度表用 1 代表微信、2 代表支付宝、3代表银联，而不是使用文字，那么维度表那么多如果很多都包含这样的编码，之后用起来还得专门去字典表（一般对于这种编码系统会专门建字典表dic）里去查。所以我们最好使用文字作为维度属性或者蚊子和编码都作为维度属性。

（3）尽量沉淀出通用的维度属性

        有些维度属性的获取需要进行比较复杂的逻辑处理，例如需要通过多个字段拼接得到（加工）。为避免后续每次使用时的重复处理，可将这些维度属性沉淀到维度表中。比如活动表中：满多少金额减多少，满多少件打几折这种复杂的逻辑处理涉及到多个维度字段，我们需要进行沉淀。

4.3 维度设计要点

4.3.1 规范化与反规范化

        规范化是指使用一系列范式设计数据库的过程，其目的是减少数据冗余，增强数据的一致性。通常情况下，规范化之后，一张表的字段会拆分到多张表。

        反规范化是指将多张表的数据冗余到一张表，其目的是减少join操作（空间换时间），提高查询性能。

        在设计维度表时，如果对其进行规范化，得到的维度模型称为雪花模型，如果对其进行反规范化，得到的模型称为星型模型（星型模型更加适合数据分析）。

        数据仓库系统的主要目的是用于数据分析和统计，所以是否方便用户进行统计分析决定了模型的优劣。采用雪花模型，用户在统计分析的过程中需要大量的关联操作，使用复杂度高，同时查询性能很差，而采用星型模型，则方便、易用且性能好。所以出于易用性和性能的考虑，维度表一般是很不规范化的。

4.3.2 维度变化

        维度属性通常不是静态的，而是会随时间变化的（比如用户表的手机号，比如省份表），数据仓库的一个重要特点就是反映历史的变化，所以如何保存维度的历史状态是维度设计的重要工作之一。保存维度数据的历史状态，通常有以下两种做法，分别是全量快照表和拉链表。

1）全量快照表

        离线数据仓库的计算周期通常为每天一次，所以可以每天保存一份全量的维度数据到一个分区。这种方式的优点和缺点都很明显。

        优点是简单而有效，开发和维护成本低，且方便理解和使用。

        缺点是浪费存储空间，尤其是当数据的变化比例比较低时。

        比如上面这张用户表，从 2019-01-01 到 2019-05-11并没有数据变化，但是它还是每天全量的进行保存。 

2）拉链表（重点）

        拉链表的意义就在于能够更加高效的保存维度信息的历史状态。

（1）什么是拉链表

        拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就直接重新开始一条记录，并把当前日期放入生效开始信息。

        如果当前信息至今有效，就在生效结束日期中填入一个极大值（如 9999-12-31）

（2）为什么要做拉链表

        拉链表适合于：数据会发生变化，但是变化频率并不高的维度（即：缓慢变化维）。英文拉链 zip 也可以翻译为 压缩，所以拉链表也可以理解为压缩表。

        比如：用户信息的变化就不并不高，所以如果按照每日全量的方式保存效率很低。

（3）如何使用拉链表

        拉链表同样是维度表，所以我们依然是用事实表和它去做关联，关联的时候的规则同样是哪一天发生的事实去 join 哪天的维度状态。现在的问题是我们如何通过拉链表获得那一天的全量状态。

        对于最新数据，我们可以直接查询满足状态结束日期为 9999-12-31 的数据。对于某天的数据，比如 2023-12-1 我们只要要求 状态开始日期 <= 2023-12-1 <= 状态结束日期 即可。

4.3.3 多值维度

        多值维度是一种现象，是在我们设计事实表时可能存在的一种现象。如果事实表中一条记录在某个维度表中有多条记录与之对应，称为多值维度。例如，下单事实表中的一条记录为一个订单（这种事实表在创建时没有考虑周全），一个订单可能包含多个商品，所会商品维度表中就可能有多条数据与之对应。

        针对这种情况，通常采用以下两种方案解决。

        第一种：降低事实表的粒度，例如将订单事实表的粒度由一个订单降低为一个订单中的一个商品项。

        第二种：在事实表中采用多字段保存多个维度值，每个字段保存一个维度id。这种方案只适用于多值维度个数固定的情况。如果多维度个数不固定可以使用 Hive 的复杂数据类型（比如数组）。

        建议尽量采用第一种方案解决多值维度问题。

4.3.3 多值属性

        多值属性同样是一种现象，是在我们设计维度表时可能存在的一种现象。多值维表中的某个属性同时有多个值，称之为“多值属性”，例如商品维度的平台属性（比如品牌、CPU，是否支持快充）和销售属性（比如规格、颜色、尺寸），每个商品均有多个属性值。

        针对这种情况，通常有可以采用以下两种方案。

        第一种：将多值属性放到一个字段，该字段内容为key1:value1，key2:value2的形式，例如一个手机商品的平台属性值为“品牌:华为，系统:鸿蒙，CPU:麒麟990”。

        第二种：将多值属性放到多个字段，每个字段对应一个属性。这种方案只适用于多值属性个数固定的情况。

4.4 维度模型对同步策略的影响

        这是我们上一篇同步策略中划分的对于不同业务表对应不同的同步策略，现在我们可以看到这样的现象：

• 对于事务事实表将来需要用到的表通常采用增量同步
• 对于周期快照事实表将来需要用到的表通常采用全量同步
• 对于累积事实表将来需要用到的表通常采用增量同步
• 对于每日全量快照维度表将来需要用到的表通常采用全量同步
• 对于拉链表将来需要用到的表通常采用增量同步

比如对上面的 cart_info（购物车信息） 这个表，我们既做一个事务事实表，也要做周期快照事实表。上面的 user_info 虽然更像是维度表，但是因为它将来要做为拉链表，所以采用增量同步。

第5章 数据仓库设计

5.1 数据仓库分层规划

        优秀可靠的数仓体系，需要良好的数据分层结构。合理的分层，能够使数据体系更加清晰，使复杂问题得以简化。以下是该项目的分层规划。

        分层的目的就是为了提高开发效率，就比如我之前开发一个桌面软件，因为比较复杂加上自己没有相关经验，导致开发过程有大量的代码冗余，这不要紧，主要是屎山难以维护，我的好多工作需要不断返工 ，对之前设计不合理的地方不断修改，但是屎山牵一发动全身，导致我最后狼狈无比，落荒而逃，只能择日重构整个项目。

• ODS 层：只做数据准备（把数据原封不动从 HDFS 映射到 Hive 表中）不做数据处理
• DWD 层：存放维度模型中的事实表
• DIM 层：存放维度模型中的维度表
• DWS 层：存放后面计算需要的公用中间计算结果，减少重复计算。（DWS 层的数据大多来自 DWD 层）
• ADS 层：存放各项统计指标的结果

5.2 数据仓库构建流程

• 数据调研：业务调研（调研的是业务系统中的数据，要熟悉业务逻辑）和需求分析（数仓后续的应用的需求）
• 明确数据域：对数据进行分类，为的是从业务系统中快速的找到我们希望得到的数据
• 构建业务总线矩阵：其实就是一个二维表格，行代表业务过程，列代表维度，如果业务过程和某个维度有关联就打对钩，最终总线矩阵构建好之后，其实我们的维度模型也基本构建完成了
• 维度模型设计：构建 DWD 层和 DIM 层。维度模型设计由业务驱动是因为我们的事实表取决于业务系统中业务过程，我们的维度表取决于业务系统中的环境，它们和我们后面的指标并没有太大关系。
• 明确统计指标：要做汇总模型就必须明确统计指标，为的是找到统计指标需要的中间计算结果。这个过程会对报表需求进行分析，整理出指标体系，我们可以根据指标体系找到需要存储在 DWS 层的中间计算结果。
• 汇总模型设计：完全依赖于统计指标需求，因为只要知道了需求才能知道要存储哪部分中间结果，所以汇总模型设计是需求驱动的。

5.2.1 数据调研

        数据调研重点要做两项工作，分别是业务调研和需求分析。这两项工作做的是否充分，直接影响着数据仓库的质量。

1）业务调研

        业务调研的主要目标是熟悉业务流程、熟悉业务数据。

        熟悉业务流程要求做到，明确每个业务的具体流程，需要将该业务所包含的每个业务过程一一列举出来。

        熟悉业务数据要求做到，将数据（包括埋点日志和业务数据表）与业务过程对应起来，明确每个业务过程会对哪些表的数据产生影响，以及产生什么影响。产生的影响，需要具体到，是新增一条数据，还是修改一条数据，并且需要明确新增的内容或者是修改的逻辑。

        下面业务电商中的交易为例进行演示，交易业务涉及到的业务过程有买家下单、买家支付、卖家发货，买家收货，具体流程如下图。

        比如我们要建一张加购表（事务事实表），那么我们就需要知道这个业务过程（加购操作）会对哪些表产生影响。首先我们要从业务数据中获取加购操作的信息加载到事实表，就需要有 cart_info 的 binlog 变更日志，Maxwell 的输出是 json 格式的，对于加购表来说，我们需要知道 type=insert 的数据一定是加购操作，至于 type=update 的语句我们需要判断它修改的是哪个字段，如果修改的是 sku_num （商品数量）并且数值是变大的，那这也是加购操作。

2）需求分析

        典型的需求指标如，最近一天各省份手机品类订单总额。

        分析需求时，需要明确需求所需的业务过程及维度，例如该需求所需的业务过程就是下单这个行为，所需的维度有日期，省份，商品品类。

3）总结

        做完业务分析和需求分析之后，要保证每个需求都能找到与之对应的业务过程及维度。若现有数据无法满足需求，则需要和业务方进行沟通，例如某个页面需要新增某个行为的埋点。

5.2.2 明确数据域

        数据仓库模型设计除横向的分层外，通常也需要根据业务情况进行纵向划分数据域。划分数据域的意义是便于数据的管理和应用。其实就是方便开发时分工以及之后取数据更快一些。

        通常可以根据业务过程或者部门进行划分，本项目根据业务过程进行划分，需要注意的是一个业务过程只能属于一个数据域。因为划分数据域按照业务过程分，所以也就相当于在 DWD 层准备事实表，以及 DWD 上层的 DWS 层的汇总表也会进行划分数据域，它和 DWD 层是一一对应的。但是 DIM 层就不需要划分数据域，因为一张维度表可能被多个事实表关联，所以无法确定它是哪个数据域。

        所以，只有在 DWD 层和 DWS 层会进行数据域的划分，DIM 层不会进行数据域的划分。

下面是本数仓项目所需的所有业务过程及数据域划分详情。

数据域	业务过程
交易域	加购、下单、取消订单、支付成功、退单、退款成功
流量域	页面浏览、启动应用、动作、曝光、错误
用户域	注册、登录
互动域	收藏、评价
工具域	优惠券领取、优惠券使用（下单）、优惠券使用（支付）

        这里也有一些业务过程我们并没有选择，比如交易域中还可以有：减购、确认收货等，但是我们在学习事务型事实表的设计流程中说过，选择自己感兴趣的业务流程，也就是我们需求指标需要用到的业务过程，所以这里没有选择。但是如果前瞻性的创建也不是不行。

        流量域相关的业务过程我们并不能直接从业务系统中直接拿到，而是得从用户行为日志中去获取。

5.2.3 构建业务总线矩阵

        业务总线矩阵中包含维度模型所需的所有事实（业务过程）以及维度，以及各业务过程与各维度的关系。矩阵的行是一个个业务过程，矩阵的列是一个个的维度，行列的交点表示业务过程与维度存在关联关系。

        一个业务过程对应维度模型中一张事务型事实表，一个维度则对应维度模型中的一张维度表。所以构建业务总线矩阵的过程就是设计维度模型的过程。但是需要注意的是，总线矩阵中通常只包含事务型事实表，另外两种类型的事实表（周期快照、累积快照）需要单独设计。

        按照事务型事实表的设计流程我们就可以得到业务总线矩阵：选择业务过程 -> 声明粒度 -> 确认维度 -> 确认事实 。

数据域

业务过程

粒度

维度

度量

时间

用户

商品

地区

活动(具体规则)

优惠券

支付方式

退单类型

退单原因类型

渠道

设备

交易域

加购物车

一次加购物车的操作

√

√

√

商品件数

下单

一个订单中一个商品项

√

√

√

√

√

√

下单件数/下单原始金额/下单最终金额/活动优惠金额/优惠券优惠金额

取消订单

一次取消订单操作

√

√

√

√

√

√

下单件数/下单原始金额/下单最终金额/活动优惠金额/优惠券优惠金额

支付成功

一个订单中的一个商品项的支付成功操作

√

√

√

√

√

√

√

支付件数/支付原始金额/支付最终金额/活动优惠金额/优惠券优惠金额

退单

一次退单操作

√

√

√

√

√

√

退单件数/退单金额

退款成功

一次退款成功操作

√

√

√

√

√

退款件数/退款金额

流量域

页面浏览

一次页面浏览记录

√

√

√

√

√

浏览时长

动作

一次动作记录

√

√

√

√

√

√

√

无事实(次数1)

曝光

一次曝光记录

√

√

√

√

√

√

√

无事实(次数1)

启动应用

一次启动记录

√

√

√

√

√

无事实(次数1)

错误

一次错误记录

√

√

√

√

无事实(次数1)

用户域

注册

一次注册操作

√

√

无事实(次数1)

登录

一次登录操作

√

√

√

√

√

无事实(次数1)

工具域

领取优惠券

一次优惠券领取操作

√

√

√

无事实(次数1)

使用优惠券(下单)

一次优惠券使用(下单)操作

√

√

√

无事实(次数1)

使用优惠券(支付)

一次优惠券使用(支付)操作

√

√

√

无事实(次数1)

互动域

收藏商品

一次收藏商品操作

√

√

√

无事实(次数1)

评价

一次取消收藏商品操作

√

√

√

无事实(次数1)

        后续的DWD层以及DIM层的搭建需参考业务总线矩阵。

5.2.4 明确统计指标

        明确统计指标具体的工作是，深入分析需求（深入了解每个业务过程每个指标的运算逻辑），构建指标体系。构建指标体系的主要意义就是指标定义标准化。所有指标的定义，都必须遵循同一套标准，这样能有效的避免指标定义存在歧义，指标定义重复等问题。

1）指标体系相关概念

（1）原子指标

        原子指标基于某一业务过程的度量值，是业务定义中不可再拆解的指标，原子指标的核心功能就是对指标的聚合逻辑进行了定义。我们可以得出结论，原子指标包含三要素，分别是业务过程、度量值和聚合逻辑。

        例如订单总额就是一个典型的原子指标（它只是完整统计指标的一部分），其中的业务过程为用户下单、度量值为订单金额，聚合逻辑为 sum() 求和。需要注意的是原子指标只是用来辅助定义指标一个概念，通常不会对应有实际统计需求与之对应。

（2）派生指标

        派生指标基于原子指标。

        与原子指标不同，派生指标通常会对应实际的统计需求。派生指标通过公式来使指标定义标准化。

        一般一个派生指标都可以通过一张事实表进行分组聚合计算得到计算结果。

（3）衍生指标

        衍生指标是在一个或多个派生指标的基础上，通过各种逻辑运算复合而成的。例如比率、比例等类型的指标。衍生指标也会对应实际的统计需求。

         所有的衍生指标都可以通过一个或多个派生指标计算得到。这里的退货率就需要两个派生指标（下单次数和退单次数）来进行计算得到。

2）指标体系对于数仓建模的意义（主要是对DWS层的意义）

        通过上述两个具体的案例可以看出，绝大多数的统计需求，都可以使用原子指标、派生指标以及衍生指标这套标准去定义。同时能够发现这些统计需求都直接的或间接的对应一个或者是多个派生指标。

        当统计需求足够多时，必然会出现部分统计需求对应的派生指标相同的情况。这种情况下，我们就可以考虑将这些公共的派生指标保存下来，这样做的主要目的就是减少重复计算，提高数据的复用性。

        这些公共的派生指标统一保存在数据仓库的DWS层。因此DWS层设计，就可以参考我们根据现有的统计需求整理出的派生指标：

我们把上面所有的派生指标拿出来分析： 

原子指标			统计周期	业务限定	统计粒度
业务过程	度量值	聚合逻辑
页面浏览	*	*	最近1/7/30日		会话
页面浏览	during_time	sum()	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话
页面浏览	*	*	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		访客-页面
页面浏览	1	count()	最近1/7/31日		访客-页面
用户登录	date_id	max()	历史至今		用户
用户登录	date_id	max()	历史至今		用户
用户登录	date_id	max()	历史至今		用户
用户登录	date_id	max()	历史至今		用户
加购	1	count()	最近1/7/30日		用户
下单	订单金额	sum()	最近1/7/30日		用户
下单	order_id	count(distinct())	最近1/7/30日		用户
下单	order_id	count(distinct)	最近1/7/30日		用户
下单	order_id	count(distinct)	最近1/7/30日		用户
下单	date_id	min()	历史至今		用户
下单	date_id	max()	历史至今		用户
下单	1	count()	最近30日		用户-商品
下单	1	count()	最近1/7/30日		用户-商品
下单	1	count()	最近1/7/30日		用户-商品
下单	1	count()	最近1/7/30日		用户-商品
下单	1	count()	最近1/7/30日		用户-商品
下单	order_id	count(distinct)	最近1/7/30日		省份
下单	订单金额	sum()	最近1/7/30日		省份
下单	订单原始金额	sum()	最近30日	订单使用优惠券(coupon_id不为null)且优惠券发布日期在最近30日内	优惠券
下单	优惠券优惠金额	sum()	最近30日	订单使用优惠券(coupon_id不为null)且优惠券发布日期在最近30日内	优惠券
下单	订单原始金额	sum()	最近30日	订单参与活动(activity_id不为null)且活动的发布日期在最近30日内	活动
下单	活动优惠金额	sum()	最近30日	订单参与活动(activity_id不为null)且活动的发布日期在最近30日内	活动
退单	1	count()	最近1/7/30日		用户-商品
退单	1	count()	最近1/7/30日		用户-商品
退单	1	count()	最近1/7/30日		用户-商品
退单	1	count()	最近1/7/30日		用户-商品
退单	1	count()	最近1/7/30日		用户
退单	1	count()	最近1/7/30日		用户
支付	date_id	min()	历史至今		用户
支付	order_id	count(distinct())	最近1/7/30日		用户

        这样我们就可以清楚的看到表中存在很多相同的派生指标，那我们就可以依据这些公共的派生指标去给 DWS 层建表，但是我们并不是把业务过程相同、统计周期相同、统计粒度相同、度量值、聚合逻辑都相同的数据放到同一张汇总表，具体设计看下面的汇总表模型设计。

5.2.4 维度模型设计

        维度模型的设计参照上述得到的业务总线矩阵即可。事实表存储在DWD层，维度表存储在DIM层。

5.2.5 汇总模型设计

        汇总模型的设计参考上述整理出的指标体系（主要是派生指标）即可。汇总表与派生指标的对应关系是，一张汇总表通常包含业务过程相同、统计周期相同、统计粒度相同的多个派生指标。而不是非得度量值、聚合逻辑也都相同才放到一张汇总表中（不通需求的度量值和聚合逻辑总是不一样的）。

        比如上面的表格中，我们可以发现：

原子指标			统计周期	业务限定	统计粒度
业务过程	度量值	聚合逻辑
页面浏览	*	*	最近1/7/30日		会话
页面浏览	during_time	sum()	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话
页面浏览	*	*	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		访客-页面
页面浏览	1	count()	最近1/7/31日		访客-页面

 其中存在完全相同结构的派生指标：

页面浏览

1

count()

最近1/7/30日

会话

页面浏览

1

count()

最近1/7/30日

访客-页面

         但是我们并不会直接把这种完全相同派生指标的计算结果存储一份到 DWS 层，而是把具有业务过程相同、统计周期相同、统计粒度相同的多个派生指标计算结果分别存到汇总表中：

原子指标			统计周期	业务限定	统计粒度
业务过程	度量值	聚合逻辑
页面浏览	*	*	最近1/7/30日		会话
页面浏览	during_time	sum()	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话
页面浏览	*	*	最近1/7/30日		会话
页面浏览	1	count()	最近1/7/30日		会话

页面浏览	1	count()	最近1/7/30日		访客-页面
页面浏览	1	count()	最近1/7/31日		访客-页面

        业务过程相同，说明我们会用到同一张事实表；统计周期相同，说明会用到同一张事实表的同一天的数据；统计粒度相同，说明我们的每一行代表的含义是相同的。

汇总表与事实表的对应关系是？

        一个汇总表只对应一个事实表（因为汇总表必须有相同的业务过程，而一个业务过程又对应一个事实表），但是一个事实表对应多个汇总表，因为我们的需求（派生指标）统计粒度和统计周期可能不同。

总结

        现在是 2024-3-9 16:25 ，数仓建模的知识终于学完了，用了近一周。理论的学习还是非常有必要的，这是我现在越发体会到的。就比如最近背的面试题，你要说写代码用的上吗？那一般指定用不上，但是对你不论是理解代码还是二次开发都是必须熟悉的。就像 MapReduce 程序工作会让我们去写吗，那肯定不会，都是用 Hive SQL ，但是不了解行吗？就像 Flink 的水位线不去了解它怎么做到精确一次背后的原理，只会跟着视频敲代码那也绝对屁用没有。所以很多人说 Java 网上 SSM 十几个小时速成，我看完多练练就精通了，但是 SSM 背后的反射机制、注解、代理模式、单例模式、工厂模式这些东西自己不懂那只能说你的上限也就到这了。