网站开发账务处理,慧聪网是干什么的,网页网站建设的ppt模板,北京朝阳区优化前言 今天开始正式数据仓库的内容了, 前面我们把生产数据 , 数据上传到 HDFS , Kafka 的通道都已经搭建完毕了, 数据也就正式进入数据仓库了, 解下来的数仓建模是重中之重 , 是将来吃饭的家伙 ! 以及 Hive SQL 必须熟练到像喝水一样 !
第1章 数据仓库概述
1.1 数据仓库概念 数…前言 今天开始正式数据仓库的内容了, 前面我们把生产数据 , 数据上传到 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 RelationshipER模型来描述企业业务并用规范化 ( 数据库规范化 , 范式等级一般为第三范式 ) 的方式表示出来在范式理论上符合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用户id001联想小新Pro15 * 5xxx旗舰店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_infospu_infobase_trademarkbase_category3base_category2base_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:value1key2: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 格式的对于加购表来说我们需要知道 typeinsert 的数据一定是加购操作至于 typeupdate 的语句我们需要判断它修改的是哪个字段如果修改的是 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_timesum()最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话页面浏览**最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话页面浏览1count()最近1/7/30日 访客-页面页面浏览1count()最近1/7/31日 访客-页面用户登录date_idmax()历史至今 用户用户登录date_idmax()历史至今 用户用户登录date_idmax()历史至今 用户用户登录date_idmax()历史至今 用户加购1count()最近1/7/30日 用户下单订单金额sum()最近1/7/30日 用户下单order_idcount(distinct())最近1/7/30日 用户下单order_idcount(distinct)最近1/7/30日 用户下单order_idcount(distinct)最近1/7/30日 用户下单date_idmin()历史至今 用户下单date_idmax()历史至今 用户下单1count()最近30日 用户-商品下单1count()最近1/7/30日 用户-商品下单1count()最近1/7/30日 用户-商品下单1count()最近1/7/30日 用户-商品下单1count()最近1/7/30日 用户-商品下单order_idcount(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日内活动退单1count()最近1/7/30日 用户-商品退单1count()最近1/7/30日 用户-商品退单1count()最近1/7/30日 用户-商品退单1count()最近1/7/30日 用户-商品退单1count()最近1/7/30日 用户退单1count()最近1/7/30日 用户支付date_idmin()历史至今 用户支付order_idcount(distinct())最近1/7/30日 用户 这样我们就可以清楚的看到表中存在很多相同的派生指标那我们就可以依据这些公共的派生指标去给 DWS 层建表但是我们并不是把业务过程相同、统计周期相同、统计粒度相同、度量值、聚合逻辑都相同的数据放到同一张汇总表具体设计看下面的汇总表模型设计。
5.2.4 维度模型设计 维度模型的设计参照上述得到的业务总线矩阵即可。事实表存储在DWD层维度表存储在DIM层。
5.2.5 汇总模型设计 汇总模型的设计参考上述整理出的指标体系主要是派生指标即可。汇总表与派生指标的对应关系是一张汇总表通常包含业务过程相同、统计周期相同、统计粒度相同的多个派生指标。而不是非得度量值、聚合逻辑也都相同才放到一张汇总表中不通需求的度量值和聚合逻辑总是不一样的。 比如上面的表格中我们可以发现
原子指标统计周期业务限定统计粒度业务过程度量值聚合逻辑页面浏览**最近1/7/30日 会话页面浏览during_timesum()最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话页面浏览**最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话页面浏览1count()最近1/7/30日 访客-页面页面浏览1count()最近1/7/31日 访客-页面 其中存在完全相同结构的派生指标
页面浏览1count()最近1/7/30日 会话
页面浏览1count()最近1/7/30日 访客-页面 但是我们并不会直接把这种完全相同派生指标的计算结果存储一份到 DWS 层而是把具有业务过程相同、统计周期相同、统计粒度相同的多个派生指标计算结果分别存到汇总表中
原子指标统计周期业务限定统计粒度业务过程度量值聚合逻辑页面浏览**最近1/7/30日 会话页面浏览during_timesum()最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话页面浏览**最近1/7/30日 会话页面浏览1count()最近1/7/30日 会话
页面浏览1count()最近1/7/30日 访客-页面页面浏览1count()最近1/7/31日 访客-页面 业务过程相同说明我们会用到同一张事实表统计周期相同说明会用到同一张事实表的同一天的数据统计粒度相同说明我们的每一行代表的含义是相同的。
汇总表与事实表的对应关系是 一个汇总表只对应一个事实表因为汇总表必须有相同的业务过程而一个业务过程又对应一个事实表但是一个事实表对应多个汇总表因为我们的需求派生指标统计粒度和统计周期可能不同。
总结 现在是 2024-3-9 16:25 数仓建模的知识终于学完了用了近一周。理论的学习还是非常有必要的这是我现在越发体会到的。就比如最近背的面试题你要说写代码用的上吗那一般指定用不上但是对你不论是理解代码还是二次开发都是必须熟悉的。就像 MapReduce 程序工作会让我们去写吗那肯定不会都是用 Hive SQL 但是不了解行吗就像 Flink 的水位线不去了解它怎么做到精确一次背后的原理只会跟着视频敲代码那也绝对屁用没有。所以很多人说 Java 网上 SSM 十几个小时速成我看完多练练就精通了但是 SSM 背后的反射机制、注解、代理模式、单例模式、工厂模式这些东西自己不懂那只能说你的上限也就到这了。
