【数仓】建模与分层

本文涉及拉链表的理论知识Slowly Changed Dimension(缓慢渐变维)

数仓Data WareHouse

是么是数仓？

数据仓库是面向分析的集成化数据平台，分析的结果给企业提供决策支持。

数仓的应用场景是什么？

满足企业中所有数据的统一化存储，通过规范化的数据处理来实现企业的数据分析应用。

简而言之就是分析数据的。

数仓的4大核心特点

1,面向主题性

主题(Subject) 是在较高层次上将企业信息系统中某一分析对象（重点是分析的对象）的数据进行整合、归类并分析的一种范围，属于一个抽象概念。

2,集成性

• 数据仓库不产生数据也不使用数据

• 只会实现存储和加工

3,非易失性

• 数仓上面的数据几乎没有修改操作，都是查询分析的操作。

• 数仓是分析数据规律的平台 不是创造数据规律的平台。

注意：改指的数据之间的规律不能修改。

4,时变性

• 数仓是一个持续维护建设的东西。

• 站在时间的角度，数仓的数据成批次变化更新。如一天一分析（T+1） 一周一分析（T+7）

OLTP和OLAP区别

OLAP(联机事务处理系统)

核心：事务支持

代表：RDBMS关系型数据库管理系统，比如MySQL、ORACLE。

特点：

1. 数据安全

2. 数据完整

3. 操作响应效率、时间

4. 并发支持

5. CRUD操作

OLTP(联机分析处理系统)

核心：分析支持

代表：数据仓库、数据集市、面向分析的数据库系统

特点：

1. 数据量大

2. 事务性要求不高

3. 支撑满足不同程度分析需求

4. 查询操作

数仓架构及核心流程

由下往上是：源数据 => ETL => 数据仓库 => 数据集市 => 决策分析,报表,查询 => 用户层

图片这里就省略了。。因为上次网站图床崩掉现在图片都不敢放在免费图床了。

核心流程

ELT，数仓分层，数仓建模

ELT

现在用的最多的可以说是ELT而非ETL，因为ETL先转换再加载MySQL中的数据到数仓中，中间可能会转换掉有用的数据，而ELT先加载再转换不会过滤掉有用的数据。

1.1 抽取Extract

数据抽取是从各个业务系统、外部系统等源数据处采集源数据。

1.2 转换Transfer

采集过来的源数据如果要存储到数据仓库需要按照一定的数据格式对源数据进行转换。 常见的转换方式有数据类型转换、格式转换、缺失值补充、数据综合等。

1.3 装载Load

转换后的数据就可以存储到数据仓库中，这个过程叫装载。 数据装载通常是按一定的频率进行的，比如每天装载前一天的订单数据、每星期装载客户信息等。

数仓建模

目的: 决定了数据存储的方式，表的设计。 例如：有哪些表，表中有哪些字段？表之间有什么关系等等。

范式建模(Normal Form)

数据仓库之父Bill Inmon提出的数据仓库建模方法是自上而下的，从全企业的高度设计一个符合3NF的数据模型，用实体关系（Entity Relationship,ER）模型描述企业的业务。

不过由于按照这种方式建模数据表的设计比较多，且复杂。很多企业基本不用

优点：符合数据库的设计规范，没有冗余数据，保证性能，业务的需求把握的比较全面。

缺点：设计时候非常复杂，必须找到所有实体和关系，才能构建。

这里bb一句三范式：

第一范式：字段不可再分

第二范式：每个表只能描述一个具体信息

第三范式：不存在主键的传递依赖

ER模型三要素:

实体(entity)：在ER图中用矩形表示

属性(attribute): 在ER图中用椭圆表示

关系(relationship):在ER图中用菱形表示

ER模型图

详解E-R图及技巧※

这是夯哥总结出来的，感谢respect，解决了我大学4年的疑惑。

求交集：有交集留交集，没有交集写n

比如分析用户实体

商品实体之间的关系，

一个用户可以购买多个商品，则 用户——> 商品 1:n

一个商品可以被多个用户购买，则 用户<—— 商品n:1

求交集，用户的一方是1和n没有交集，写n。同理商品的一方是1和n，没有交集，同样写n。

ER模型

ER模型的构建流程

1. 找到所有 实体，以及每个 实体 的属性

2. 找到所有实体之间的关系

3. 建表，每个实体与每个关系都是一张表

维度建模与ER建模的区别

实体-关系建模：面向应用，遵循第三范式，以消除数据冗余为目标的设计技术。 维度建模：面向分析，为了提高查询性能可以增加数据冗余，反规范化 的设计技术。

维度建模(Dimension)

维度建模的核心：

1，明确事实表(指标 + 维度外键)

2，明确维度表(维度id + 具体的信息)

Important解释：指标=数值，维度是看待事物的角度

维度建模的三种模型：星型模型，雪花模型，星座模型

事实表

事实表的概念

举个栗子，订单事实表就是指标(个数num，订单价格order_price)+维度外键(除了num，order_price)的其他字段

-- orderid   userid   shopid   productid   num  order_price  time
-- 订单编号    用户编号  店铺编号  商品编号     个数   订单价格      时间
-- 000001     10086   sp_123   pd_0012     2      12.34     20211010
-- 000002     10010   sp_211   hd_jhas     21     25.25     20211011
-- 000002     10010   sp_211   hd_jhas     21     25.25     20211011

事实表的特点(文言文)

• 事实表包含了与各维度表相关联的外键，可与维度表关联；

• 表里没有存放实际的内容，是一堆主键的集合，这些主键ID分别能对应到维度表中的一条记录；

• 事实表的度量（指标）通常是数值类型；

• 事实表记录数会不断增加，表数据规模迅速增长。

事实表度量（指标）分类

1、可加数值类型

2、半可加数值类型

3、不可加数值类型

事实表的分类

• 事务事实表(Transaction fact table)

• 周期快照事实表(Periodic snapshot fact table)

• 累积快照事实表(Accumulating snapshot fact table)

• 无事实事实表

维度表

维度表：举个栗子，就是跟MySQL练习题中的category表一样嘛，一列数字，一列商品名称。

id          type        num         hanyi（含义）
0001        week         1           周一
0002        week         2           周二
0003        week         3           周三

维度表的分类

• 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。

• 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表、地理维表等。数据量可能是个位数或者几千条几万条。

这里有个不成文的规定：10w条数据以上是高基维

指标和维度

指标=数值，维度是看待事物的角度

星型模型

• 一张事实表，多张维度表

• 所有维度表都直接关联事实表，且维度表之间无关联

适用于项目前期

雪花模型(Snowflake)

• 1张事实表，多张维度表

• 维度表与维度表之间可以直接相互关联，像雪花一样

适用于项目中后期

弊端：

1层层join，效率低

2对企业来讲，维护成本高

3对技术人员来讲，开发难度相对较大

星座模型(Constellation)

• 多张事实表，多张维度表

• 可以实现维度表共享，但是维度表与维度表之间不能直接关联

渐变维（Showly Changed Dimension）

假设有个：顾客信息表，2012年1月1日起，BIWORK居住在北京

2012年3月12日以后搬去了三亚居住，因此该条数据进行了更新.

SCD1（缓慢渐变类型1）

通过更新维度记录 直接覆盖已存在的值。不维护记录的历史。一般用于修改错误的数据。

直接覆盖这种方式不是很人性化，不使用。

SCD2（缓慢渐变类型2）-拉链表

拉链表实现方式：（在表中添加两个字段，表示该条数据状态的起始时间、结束时间。）

| 用户id | 所在的地区 | 时间标记：startTime | 时间标记：endTime |
| ------ | ---------- | --------------- | ------------- |
| 1001   | beijing    | 2018-01-01      | 2020-01-01    |
| 1001   | sanya      | 2020-01-01      | 2021-01-01    |
| 1001   | meiguo     | 2021-01-01      | 2022-01-01    |
| 1001   | yilake     | 2022-01-01      | 9999-12-31    |
​
--工作中的需求是可以指定日期查询对应的状态
 where startTime >= 2019 and endTime < 2020
--默认应该处理最新的状态：通过9999-12-31，来标记这是最新的状态
 where endTime = 9999-12-31

SCD3（缓慢渐变类型3）

SCD3希望只维护更少的历史记录。

比如说把要维护的历史字段新增一列，然后每次只更新Current Column和Previous Column。
这样，只保存了最近两次的历史记录。
但是如果要维护的字段比较多，就比较麻烦，因为要更多的Current和Previous字段。
所以SCD3用的还是没有SCD1和SCD2那么普遍。
它只适用于数据的存储空间不足并且用户接受有限维度历史的情况。

数仓分层

目的: 将各种数据的处理流程进行规范化。

分层的实现: 当使用Hive作为数据仓库工具的时候，分层是在Hive中逻辑划分实现的。

常见的做法是：不同的分层创建不同的database

分层的好处

1. 清晰数据结构 每一个数据分层都有它的作用域，在使用表的时候能更方便地定位和理解。

2. 数据血缘追踪 简单来说，我们最终给业务呈现的是一个能直接使用业务表，但是它的来源有很多，如果有一张来源表出问题了， 我们希望能够快速准确地定位到问题，并清楚它的危害范围。

3. 减少重复开发 规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。

4. 把复杂问题简单化 将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性， 当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

5. 屏蔽原始数据的异常 屏蔽业务的影响，不必改一次业务就需要重新接入数据

数仓经典三层结构

ODS：Operational Data Store

源数据层、贴源层、数据暂存层

保持和源数据一致，临时存储，一般不做操作，给下一层做铺垫

DW: Data WareHouse

数仓层

具体做数据分析的

DA:(RPT)-Report

报表应用层，数据报表层

通过BI报表展示分析的数据的

DW层细分

1．明细层DWD（Data Warehouse Detail）

存储明细数据，此数据是最细粒度的事实数据。该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。同时， 为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。

2．中间层DWM（Data WareHouse Middle）

存储中间数据，为数据统计需要创建的中间表数据，此数据一般是对多个维度的聚合数据，此层数据通常来源于DWD层的数据。

3．业务层DWS（Data WareHouse Service）

存储宽表数据，此层数据是针对某个业务领域的聚合数据，应用层的数据通常来源与此层，为什么叫宽表，主要是为了应用层的需要在 这一层将业务相关的所有数据统一汇集起来进行存储，方便业务层获取。此层数据通常来源与DWD和DWM层的数据。

数据集市层DM

DM（数据集市层 Data Mart） 功能职责:进行细粒度统计操作, 基于DWS层, 进行上卷维度统计操作, 形成大的主题统计宽表 (一般来说是一个主题对应一个统计宽表)

Important Things

Report,报表层。

作用:通过BI报表，展示分析后的结果数据。

Data Market, 数据集市层.

作用:基于DWS层的数据，上卷(Roll up)出年月周的统计。

Data warehouse Service, 数据服务层.

作用:基于DWB层的宽表，按照主题进行分析，获取各主题的日统计宽表。用户主题日统计宽表

Data warehouse Base,数据基础层，也叫数据中间层(Middle).

作用:降维(维度退化)，形成宽表， 类似于从3NF => 2NF

ata warehouse Detail, 数据明细层，

作用:清洗转换，区分维度表和事实表，事实表: fact_ 表名 维度表: dim_表名

ODS层：也叫贴源层，源数据层，这一层的数据结构基本和源数据保持一致.

用于临时存储要分析的数据的，建议采用: orc + zlib方式存储