电商离线数据仓库搭建总结

1、数据源

数据库数据不能直接作为数据仓库数据源的原因：

（1）数据库要求行式存储，数据仓库要求列式存储，直接对接会导致查询速度过慢，数据仓库性能有影响

（2）数据库中存储的数据不是海量数据，数据仓库要求数据是海量数据，否则分析效果不佳

（3）数据库不是为了数据仓库而服务的，若直接对接数据仓库会导致数据库性能受到影响

2、数据采集

3、数据分类

（1）业务数据

处理事务过程中产生的数据，存储在 MYSQL 等数据库中

（2）用户行为数据

用户在使用产品过程中，通过埋点收集和客户端产品交互过程中产生的数据，并发往日志服务器进行保存。储在日志文件中

4、数据仓库

5、技术选型

6、系统流程图

kafka：主要是为了均衡传输速率（偏移量），对实时数据更好处理，消峰

7、全量增量

全量：表的全部数据，每天都将业务数据库中的全部数据同步一份到数据仓库，这是保证两侧数据同步的最简单的方式。

增量：表的新增变化数据，每天只将业务数据中的新增及变化数据同步到数据仓库。采用每日增量同步的表，通常需要在首日先进行一次全量同步。

总结：数据量大增量，数据量小全量

此项目全量采用 datax，增量采用 maxwell

maxwell 开启 binlog 会增加性能损耗

8、flume 相关

(1) 不使用 Sink 的原因：

在 Source 和 Sink 之间添加 channel 的目的是解决读写速率不均衡的问题，但我们的目的就是写入 kafka，所以没有必要再使用 Sink

(2) 不使用 event 来封装数据

9、电商常识

（1）SKU：库存量基本单位，现在引申为产品统一编号的简称，每种商品对应有唯一的 SKU 号

（2）SPU：商品信息聚合的最小单位，是一组可以重复用，易于检索的标准化信息集合（更详细）

（3）平台属性：

（4）销售属性

9、业务数据采集

数据同步的方式：周期同步（天为单位）

全量同步（DataX）：表的全部数据，不用于实时数据分析

增量同步（MaxWell）：表的新增及变化的数据，用于实时数据分析，离线数据分析

采集目的：未来后期的统计分析做准备

10、maxwell 采集数据原理

（1）为了加快读写速率，一些常用的数据以及刚刚写入的数据会被放入缓存中进行快速读取

（2）为了防止断电后数据丢失，放入缓存的数据会预先写成文件，写成的文件是按照顺写的方式形成的

（3）Maxwell 会把自己伪装成 slave 来同步 master 的数据，进而用于读取，从而做到读写分离

11、日志数据采集的零点漂移问题

由于数据从提交到处理的这段时间可能存在延迟，因此一旦出现了跨天的情况就会导致数据的存储路径错误（时间错位）。

造成的原因主要是因为实际存储过程中使用的时间是处理时间而不是上传时间，因此需要使用拦截器将存储时间修改成上传时间

拦截器功能：

1、判断 JSON 格式是否有效

2、解决零点漂移问题

12、增量同步数据采集分析

（1）需要用拦截器往 event 里的 header 放入 tablename 从而构造写路径

（2）flume 底层操作以毫秒为单位，maxwell 中的时间戳是秒，所以需要乘以 1000 让单位相同

13、增量表数据操作

14、数据库 VS 数据仓库

（1）数据库只用来存，数据仓库还需要用来算

（2）数据库数据来源于企业业务数据，数据仓库数据来源于数据库中的数据

（3）数据库核心用于查找业务数据，数据仓库核心用于分析数据

（4）数据库负责保证全企业全业务的正常运行，数据仓库将数据的统计结果为企业的经营决策提供支持，需要通过可视化展现

15、数据仓库分层规划

（1）ODS：最初的数据源

（2）DWD：事实表，加工数据

（3）DIM：维度表

（4）DWS：汇总数据层，负责对加工后的数据进行统计。

用于存储中间计算结果，方便复用，尽管这可能会导致数据冗余

ADS：存放统计结果，用于分析

分层目的：让复杂的问题简单化，让数据结果能够重复利用

16、学习重点

（1）建表（建模）

（2）SQL

枯燥，重复性很强

（3）任务调度器

最重要的东西

17、数据仓库核心架构

18、ER 模型

（1）实体关系

一对一，一对多，多对多

（2）数据库规范化

使用一系列范式来设计数据库的过程，目的是减少数据冗余，增强数据一致性

（3）三范式

【1】属性不可分割

【2】不能存在部分函数依赖

部分函数依赖：

有些属性 (名字，系名、主任) 只依赖于一部分主键(学号)，叫做部分函数依赖

【3】不能存在传递函数依赖

（4）不适合作为数仓模型使用

ER 模型主要是为了减少数据冗余，保持数据一致，并不适合用于数据分析。而数仓是需要进行数据分析的，因此不适合使用

19、维度模型

维度模型主要通过事实和维度两个概念进行呈现。

事实：对应业务流程，类似于一个人做的一件事

维度：业务过程发生时所处的流程，相当于对这件事的描述 (何人, 何时, 何地)

业务：一个个不可拆分的行为事件

会造成数据冗余，但以空间换时间

20、数仓构建流程

（1）数据调研

【1】业务调研

目的是熟悉业务流程、业务数据，

熟悉业务流程需要把该业务所包含的每个业务过程一一列举出来；

熟悉业务数据需要将数据和业务过程对应起来，明确每个业务过程会对哪些表的数据产生影响，以及产生什么影响，例如是新增一条数据还是修改一条数据

【2】需求分析

分析需求时需要明确所需要的业务过程和维度

做完业务分析和需求分析后，要保证每个需求都能够找到与之对应的业务过程以及维度

（2）明确数据域

目的是便于数据的管理和应用

通常可以根据业务过程或者部门进行划分，一个业务过程只能属于一个数据域

（3）构建业务总线矩阵

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

（4）明确统计指标

深入分析需求，构建指标体系。构建指标体系的主要意义就是指标定义的标准化。所有指标的定义都必须尊循同一套标准，这样才能避免指标定义存在分歧重复。

【1】原子指标

基于某一个业务过程的度量值，是业务定义中不可再拆解的指标，原子指标的核心就是对指标的聚合逻辑进行了定义。

原子指标三要素：业务过程、度量值、聚合逻辑

【2】派生指标

基于原子指标

需求最终都可以拆解成一个个派生指标，派生指标通常会对应实际的统计需求

统计周期一般指数据时间范围（分区字段过滤：过滤文件夹）

业务限定一般指数据约束条件（数据字段过滤，过滤文件）

统计粒度一般指分组粒度

【3】衍生指标

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

【4】指标体系对于数仓建模的意义

统计需求足够多时，必然会出现部分统计需求对应的派生指标相同的情况。这种情况就可以考虑保存公共派生指标，进而减少重复计算，提高数据的复用性。

这些公共派生指标统一保存在 DWS 层

21、hive on spark 工作原理

（1）spark 会将程序上传到 namenode 运行

（2）spark 会将相关环境包一同上传到 namenode 用于运行

（3）通过中间件 HDFS 上传存储完毕后，会被拉取到 namenode 运行，运行结束后删除

（4）为了防止上传的 hadoop 包和 yarn 的 hadoop 包冲突，spark 使用纯净版本

（5）为了防止重复上传依赖 jar 包，可以在中间件 HDFS 中提前准备好 jar 包

22、ODS 层设计

（1）ODS 层的表结构设计依托于从业务系统同步过来的数据结构。

（2）ODS 层要保存全部历史数据，故其压缩格式应选择压缩比 较高的，此处选择 gzip。

（3）ODS 层表名的命名规范为：ods_表名_单分区增量全量标识（inc/full）。

（4）命名规范

（5）日志表

（6）创建成外部表的原因

外部表是为了方便其他人使用，这里创建外部表是因为我们在学习阶段需要频繁改动表，为了保持数据不变因此使用外部表

（7）分区表

目的就是为了加快查询速度（定位数据）

静态分区：分区字段为固定值

动态分区：分区字段取之于查询结果

【1】分区字段不能赋值

【2】查询字段应该在最后增加一个额外的字段用于分区操作，称之为分区字段

【3】默认情况 hive 没开启动态分区处理

（8）默认无法识别 JSON 文件数据，因此需要指定行数据解析格式

SerDe：序列化操作

（9）表中取数据流程：

面对不同格式的数据转换，主要是依赖于内存作为中转进行序列化和反序列化操作

（10）字段匹配关系

【1】不存在字段会设定为 null

【2】多于表的字段不做解析

【3】属性字段不区分大小写

（11）JSON 嵌套

会将最外层的 JSON 对象的属性作为 JSON 表的字段

（12）map 类型和 struct 类型的区别

【1】泛型，map 中字段类型会统一，struct 可变

【2】struct 中的属性名固定，map 可变

22、维度表

维度建模的基础和灵魂维度表围绕业务过程所处的环境进行设计，主要包含一个主键和各种维度字段，维度字段称为维度属性

（1）维度表设计步骤

【1】确定维度（表）

在设计事实表的时候就看已经设计好了每个事实表相关的维度，理论上每个相关的维度都需要对应一张维度表。

可能错在多个事实表和同一个维度表相关的情况，这个时候需要保证维度的唯一性，就是只创建一张维度表。

如果某些维度表的属性很少，则可不创建维度表，而是将该表的维度属性直接增加到与之相关的事实表中，这个操作叫做维度退化

【2】确定主维度表和相关维表

主维表和相关维表均指业务系统中和某维度相关的表。其中和业务操作直接相关的表称为主维表，其它表为相关维表。相关维表的粒度通常和主维表相同

【3】确定维度属性

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

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

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

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

（b）尽量不单独使用编码，而使用明确的文字说明，一般可以编码和文字共存（编码文字解释）

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

有些维度属性的获取需要进行比较复杂的逻辑处理，例如需要通过多个字段拼接得到。为了避免后续每次使用时重复处理，可以将这些维度属性沉淀到维度表中

（2）维度设计要点

【1】规范化与反规范化

规范化指使用一系列范式设计数据库的过程，目的是减少冗余，增强数据一致性，规范化后一张表会被拆分为多张表

反规范化指将多张表的数据冗余到一张表，目的是为了减少 join 操作，提高查询性能。

设计维度表时，如果进行规范化，得到的维度模型叫做雪花模型，如果进行反规范化，得到的模型称为星型模型

雪花模型复杂度高，查询性能差

星型模型方便且易用性能好

一般维度表是很不规范化的

（3）维度变化

维度属性不是静态的，而是变化的。保存维度数据历史状态的做法主要有两种，分别是全量快照表和拉链表

【1】全量快照表

离线数据仓库计算周期通常是每天一次，所以可以每天保存一份全量的维度数据。这就叫全量快照表

这种方式优点是使用简单且有效，维护开发成本低，方便理解使用

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

适合数据量比较少的时候使用

【2】拉链表

拉链表能够更加高效保存维度信息和历史状态，能够记录每条信息的生命周期

若当前信息至今有效，则会在结束 日期中填入一个极大值

拉链表适合于数据会发生变化，但变化比率不高的维度

使用起来稍微比较麻烦

（4）多值维度

若事实表中一条记录在某个维度表中有多条记录对应，则称为多值维度

方法一：降低粒度，例如将订单事实表的粒度由一个订单降低为一门课程

方法二：不能降低粒度，若记录个数固定则通过添加新的列来细分记录；若记录个数不固定则使用特殊类型（数组）进行存储

尽量使用第一种方法解决多值维度问题

（5）多值属性

维度表中某个属性有多个值时叫做 “多值属性”，解决方案有两个：

方法一：将多值属性存放到一个字段，字段内容为 key1：value1，key2：value2.... 例如一个可成可以属于编程技术、后端开发等多个类别

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

23、事实表

（1）概述

数仓维度建模的核心。包含与业务过程有关的维度引用（维度表外键）以及业务过程的度量（可累加的数字类型字段）

（2）特点

列较少，行较多，行增速快

（3）分类

事务事实表、周期快照事实表、累积快照事实表

（4）格式

存储格式：列式存储

压缩格式：snappy

（5）数据域：

23.1、事务事实表

用来记录各业务过程，用于保存各业务过程的原子操作事件，即最细粒度的操作事件

事务型事实表可用于分析与各业务相关的各项统计指标，由于保存了最细粒度的记录，可以提供最大限度的灵活性 (粒度越细，能够分析的角度就越多)

（1）设计流程

选择业务过程 -》声明粒度 -》确认粒度 -》确认事实

【1】选择业务过程，确定需要创建多少个事务型事实表

挑选用户感兴趣的业务过程，业务过程可以概括为一个个不可拆分的行为事件，例如下单、付款等。通常一个业务过程对应一张事务型事实表

【2】声明粒度

确定每张事务型事实表每行数据表示什么，应该尽可能选择最细的粒度来满足需求

例子

订单事实表中的一行数据表示的是一个订单中的一门课程

【3】确定维度

确定和每张事务型事实表相关的维度有哪些

维度越丰富，能够支持的指标也越丰富

【4】确定事实

确定每个业务过程的度量值（数字类型的值）

（2）不足

【1】在某些场景下使用事务型事实表的性能会比较低，例如在大表中操作存量型指标（单个任务多条数据）

【2】在某些场景下使用事务型事实表的性能会比较低，例如多事务关联指标（多个任务单条数据）

23.2、周期快照事实表——存量型指标需求

以具有规律性的、可预见性的时间间隔来记录事实，主要用于分析一些存量型（购物车存量、账户余额）或者状态型（空气温度、行驶速度）指标。

对于存量指标，业务系统中通常会计算并保存最新结果，定期同一份全量数据到数据仓库，构建周期型快照事实表，从而应对统计需求，而不再需要对历史记录进行聚合了。

（1）设计流程

【1】确定粒度

通过采样周期和维度来描述，确定采样周期和维度后就可以确定粒度

采样周期通常是每日

维度可以根据统计指标决定

【2】确认事实

确认度量值的类型，度量值分为三类，分别是可加事实，半可加事实和不可加事实

（a）可加事实

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

（b）半可加事实

只能按照与事实相关的一部分维度进行累加，例如周期型快照事实表中的事实。按照用户进行统计有分析意义，按照时间则没有意义

（c）不可加事实

完全不具备可加性，例如比率型事实，通常不可加事实需要转化成可加事实，例如比率转换成分子分母

23.3、累积快照事实表

基于一个业务流程中多个关键业务过程联合处理而构建的事实表。如交易流程中的试听、下单、支付等流程

这种表的特点是每个业务过程都有时间维度

主要用于分析各个业务过程之间的时间间隔等需求，使用累积快照事实表可以避免连个事务事实表的关联操作，从而变得高效

（1）设计流程

选择业务过程 -》声明粒度 -》确认维度 -》确认事实

【1】选择业务过程

选择一个业务流程中需要关联分析的多个关键业务流程，多个业务过程对应一张累积型快照事实表

【2】声明粒度

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

【3】确认维度

选择和各业务过程相关的维度，注意每个业务过程都需要一个日期维度

【4】确认事实

选择各业务过程的度量值

（2）时间字段选择

如果一个业务流程存在多个时间段，则一般选择最后的时间字段作为分区字段

最后一个时间字段可能是空，因此一般会采用时间极大值而不要 null 表示分区

24、分区策略

目的：查询数据要高效且数据有效

ODS 层：

一天采集到的数据就存储到表的一天分区

DIM 层：

（1）全量：每天一份全量数据，存放到一天的分区中

（2）拉链：采用结束时间作为分区字段，如果没有则采用最大值。好处是使用结束时间能知道开始和结束时间，但只使用开始则不知道何时结束

DWD 层：

（1）事务型事实表：一条的业务行为数据存放到一天的分区中

（2）周期型快照事实表：将每一天的全部状态数据保存到这一天的分区中

（3）累积型快照事实表：将业务流程中最后的时间字段作为分区字段。如果只取前面的时间字段，那么后面的时间字段还需要判断

25、小总结

ODS 层：作为整个数据仓库的数据源

DIM 层，DWD 层：去哦我统计分析的数据源

DIM 层：根据数据状态，对统计结果进行分析

DWD 层：根据行为产生的数据，进行统计

学习过程中需要提前准备好历史全量数据，但在首日会出现全量和增量数据无法同时获取的问题，因为首日的全量和增量数据生成不同步，这就导致数据同步时间也不同。

因此实际上首日的增量数据同步是在下一天进行的，拥有一条的延迟性，所以不会出现问题

26、ADS 层

Application Data Service

（1）Application （数据仓库）应用

（2）Data 用户需求的统计结果数据

（3）Service 对外服务

【1】ADS 层保存的数据是最终的统计结果，无需进一步计算

不需要列式存储，也不需要 snappy 压缩

【2】统计结果的目的是对外提供服务，所以表不会是最终数据的存储位置

需要将表中的数据同步到第三方存储，从而降低耦合性 (MySQL)

ADS 层的表最好是行式存储：tsv(DataX)

压缩格式采用 gzip

【3】统计结果的数据量不会很多，不用分区

【4】表的设计

ODS 层：表的结构依托于数据源的数据结构（ER 模型）

DIM 层：遵循维度模型的维度表的设计理念（维度越丰富越好）

DWD 层：遵循维度模型的事实表设计理念（粒度越细越好）

ADS：客户要啥给啥，不要额外添加

维度：分析数据的角度

粒度：分析数据的详细程度

统计周期：数据统计的时间范围，类似窗口

统计粒度：分析数据的角度，称之为统计粒度（站在哪一个角度统计数据，分析维度）

指标：客户想要的一个结果数值

27、SQL 语句构建注意

（1）分组聚合的场合下，可以出现在 seleect 子句中的参数

【1】常量

【2】聚合函数字段

【3】参与分组字段

（2）多个字段参与分组的时候，统计值的含义

【1】如果多个字段存在上下级，所属关系，那么统计结果和下级字段相关，上级字段参与分组纯粹是用于补全数据

【2】如果多个字段存在关联关系，那么统计结果和具有唯一性的字段相关。其他字段纯粹用于补全数据

【3】如果多个字段没有任何关系，那么统计结果就和所有的字段相关。

（3）统计粒度

【1】当统计 (ads) 的统计粒度减少，且就是对减少的粒度进行统计，则不用判重

【2】当统计 (ads) 的统计粒度减少，对其他字段进行进一步统计，则需要聚合数据

【3】当统计 (ads) 的统计粒度变化，此时必须判重

【4】当统计 (ads) 的统计粒度变化，此时必须聚合数据

【5】粒度没有变化的情况下，可以直接使用

28、DWS 层

（1）Data Warehouse ：数据仓库

（2）Summary：汇总

【1】用于将 DIM、DWD 的数据进行提前统计，将统计结果保存到当前的表中

【2】所以当前的表不是最终的统计结果表

【3】数据量可能有点多，表的设计应该添加分区，需要进一步的聚合处理

【4】DWS 层的数据存储格式为列 orc 列存储 + snappy 压缩

【5】DWS 层的表名的命名规范为 dws_数据域_统计粒度_业务过程_统计周期 (1d/nd/td）

【6】统计的字段如果可以跨越天，那么就不能在每天中统计

【7】若需要统计的字段不能提前聚合，则未来避免数据丢失，需要在表中再增加这个字段，而不是统计这个字段

【8】根据数据范围进行分类

1d：1 天的数据统计，数据来源 DIM,DWD

nd：N 天的数据统计，数据来源 1d

td：所有的数据统计，数据来源 1d 或 DIM,DWD

【9】可放在一块的指标的条件

业务过程相同：数据来源相同

统计周期相同：数据范围相同

统计粒度相同：数据含义相同

29、ADS 层

（1）统计结果数据量比较少，所以建表语句没有分区

（2）统计结果是最终结果，无需进行经一部统计分析，所以不需要列式存储或者使用 snappy 压缩

（3）因为存储的数据结果需要同步到 MySQL 给第三方可视化平台使用，所以采用 tsv 格式

（4）表的字段不用很多，满足客户需求即可

30、DolphinScheduler

（1）参数优先级

从低到高：

上游传递参数 < 全局参数 < 本地参数

（2）内置参数使用

【1】基础内置参数

脚本中依旧使用参数变量，在本地参数界面输入内置参数使用

【2】衍生内置参数