数据仓库之各种表

数据仓库之各种表

在数仓项目中最大的感觉就是各种表各种分类，有丢丢搞坨坨不清，本文目的就是梳理一下数据仓库的各种“表”。

在此之前需要弄清楚OLTP和OLAP的恩恩怨怨,以及为什么要从OLTP到OLAP呢？

OLTP（On-Line Transaction Processing），操作型处理，也叫联机事务处理，也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。

OLAP（On-Line Analytical Processing），分析型处理，叫联机分析处理，一般针对某些主题的历史数据进行分析，支持管理决策。简单来说数据库和数据仓库的区别某种程度和意义上就是等同于OLTP和OLAP的区别。

也就是说如果OLTP它储存的是最新的数据，而数据仓库或者说OLAP它是基于历史数据进行分析的，因为不要忘了我们是分析过去的数据呀~~

那么为什么就是说要从OLTP到OLAP呢？

首先对于前期数据需求较少，不需要你去提供各种指标给各个业务部门，当时通常的做法就是说写脚本，从命令行输入参数，连接数据库，将需要的原始数据提取出来，在内存中计算数据，然后将结果写入一个专门存放统计结果的DB，最后再写一个PHP页面作为报表提供给需求方，那么后面就会出问题的说，你前面偷的懒后面是要还的！所以说随着需求的增加和精细化，问题变得棘手，而且还严重影响的开发效率，基于美团数据仓库搭建过程中的各种困难场景举例说明：比如说在这过程中有很多重复劳动和代码，比如连接数据库的代码，每个人都要写，各种写法不同，分布在很多地方，如果哪个DB的连接方法变更了，需要更改很多地方。中间数据缺失，中间计算结果不能共享。不同的人写报表，每人都可能要重算一次。很难管理和维护，程序语言五花八门，同一指标可以写多种统计方法，各种语言各种执行方式，缺少文档，其他人很难接手维护。数据的清洗和转换没有统一方法，比如，哪天是每月第一天或每周第几天这种需求，靠手工调用各种时间函数来计算，非常容易出错。不同数据源的数据很难综合使用， 比如一个数据需要使用主站的数据和合同系统的数据， 要把这些数据组织在一起就很麻烦。所以我们就需要OLAP。

那就是基于OLAP，才引出了后面一大堆表！

实体表

实体表，一般是指一个现实存在的业务对象，比如用户，商品，商家，销售员等等。大家可以自行想象假如你是老板，你会怎么登记你顾客的信息，那么这个顾客的信息，就是对应实体表里面的一条数据。。。

维度表

维度表，一般是指对应一些业务状态，编号的解释表。也是对事实的描述信息，也就是这件事到底是啥样。也可以称之为码表。每一张维表对应现实世界中的一个对象或者概念。

比如地区表，订单状态，支付方式，审批状态，商品分类等等。

维度表的特征：

维表的范围很宽（具有多个属性、列比较多），因为你想就光一只猫我们可以分为胖猫瘦猫，黑猫白猫，高猫矮猫，那更不用提那么复杂的世界了。。。所以它跟事实表相比，行数相对较小：通常< 10万条，因为维度表的行记录的就是我们对这个属性的描述，那通常是肯定可以进行枚举的，并且呢内容相对固定，就比如说编码表，来来去去也就那些编码，那可以看到以时间维度表举例：

日期ID	day of week	day of year	季度	节假日
2020-01-01	2	1	1	元旦
2020-01-02	3	2	1	无
2020-01-03	4	3	1	无

我们主要关注下退化维度和缓慢变化维。这种维度指的是直接把一些简单的维度放在事实表中。退化维度是维度建模领域中的一个非常重要的概念，它对理解维度建模有着非常重要的作用，退化维度一般在分析中可以用来做分组使用。退化维度：在维度类型中，有一种重要的维度称为退化维度，这种维度指的是直接吧一些简单的维度放在事实表中，那么在此基础上，就是引申出了一个概念就是缓慢变化维，

因为维度的属性并不是始终不变的，它会随着时间的流逝发生缓慢的变化，这种随时间发生变化的维度我们一般称之为缓慢变化维（SCD）。

SCD常用的三种处理方式：

①直接覆盖原值

②增加维度行

​ 在为维度成员增加新行时，需为其分配新的主代理键。并且，至少需要在维度行再增加三列：有效日期、截止日期、行标识。这个地方可联想拉链表设计。

③ 增加属性列

④ 混合方式

可根据实际业务场景，混合或选择使用以上三种方式，以快速方便而又准确的分析历史变化情况。

事实表

事实表中的每行数据代表一个业务事件（比如说你在淘宝上下单、支付、退款、评价等各种行为都算作一个事件）。那么“事实”这个术语表示的是业务事件的度量值（我们可以统计次数、个数、金额等），例如，今年的双十一，你在哪家店铺买了多少钱买了多少商品，那么这个对应我们可以将这一个动作记录在维度表中的：时间，用户，商品，店铺，登记在事实表里面的度量值可以有购买金额和购买数量。

每一个事实表的行包括：具有可加性的数值型的度量值【因为最能体现出效果的就是数字啦】、与维度表相连接的外键【我的理解就是不然光秃秃你也不知道咋讲啥】、通常具有两个和两个以上的外键、外键之间表示维表之间多对多的关系。

那么事实表的特征：首先 数据量非常的大(行数)，因为就光双十一这一天产生多少订单，然后我们的一切行为数据就会被这么记录了，那么可以想象那行能不多嘛， 内容相对的窄：列数较少（主要是外键id和度量值），这个是因为事实表的设计初衷就是这样，想要窄窄的记录重要的信息，不负责属性的各种描述，并且它还会经常发生变化，每天会新增加很多，还是拿电商举例就是说，每天的订单记录会不停在增加。

在此基础上于是就产生了三大事实表！

1）事务型事实表

事务事实表的粒度是每一行数据对应一个事务，或者一行对应事务中的一个条目。事务可以理解为业务过程中的各个事件节点，比如网购交易过程中的创建订单，买家付款，物流过程中的揽活，发货，签收，退款过程中的申请退款，确认退款等，都可以理解为事务。事务事实表针对这些过程构建，作为数仓的原子明细数据，可以跟踪定义业务过程的事件，提供丰富的分析能力。

事务事实表只有事务发生才会新增对应的一条数据，一般不会做更新操作。

简单来说，事实表就是以每个事务或事件为单位，比如说一个销售订单记录，一笔支付记录等，我们都可以作为事实表里的一行数据。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为每日增量更新。

2）周期型快照事实表

周期快照事实表在了解有规律的、可预见时间间隔的业务累计性能方面很有必要。与针对每个出现的事件都要加载一行数据的事务事实表不同，利用周期快照可以在每天，每周，每月结束时，为当时的行为进行拍照，然后在下一周期拍另一张照片，比如商品库存，账户余额，每日交易额，每月累计交易额等。事务事实表需要聚集长期的事务历史才能得出这些结果，使用效率比较差，可以用周期快照作为事务事实表的补充。

周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额，或每月的账户余额等。还有就是例如购物车，有加减商品，随时都有可能变化，但是我们更关心每天结束时这里面有多少商品，方便我们后期统计分析。因此周期型快照事实表的更新方式为每日全量更新，即每日对数据做一个快照。

3）累积型快照事实表

累积快照表完全涵盖一个事务或者离散产品（或者用户）的生命期的不确定跨度。累积快照表具有多个日期字段，用于对应可预见的主要事件或者环节的发生时间。通常还有一个用于指示快照行最后一次更新的附加日期列。

累积快照事实表需要对数据进行重新访问，当某一事件发生后，需要对应时间字段和相关度量进行更新操作，而不是新增一行。累积快照可以很好的满足某些特殊的需求，例如:统计下单到支付的时长，支付到发货的时长，下单到确认收货的时长等。如果用事务事实表的话逻辑复杂且性能较差。

**换句话说累积快照事实表用于跟踪业务事实的变化。**例如，数据仓库中可能需要累积或者存储订单从下订单开始，到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单生命周期的进展情况。当这个业务过程进行时，事实表的记录也要不断更新。

订单id	用户id	下单时间	打包时间	发货时间	签收时间	订单金额
		3-8	3-8	3-9	3-10

维度模型设计采用四步设计方法：

选择业务流程-》声明粒度=》确定维度=》确定粒度

• 选择业务过程

​ 设计过程的第一步是确定要建模的业务过程和度量，这些业务过程是在业务需求收集过程中明确下来的。业务系统中，如果业务表过多，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流业务等等，一条业务线对应一张事实表

• 声明粒度

  数据粒度是数据仓库的数据中保存数据的细化程度或者是综合程度的级别。一旦业务过程被确定下来，就必须声明事实表的粒度，清楚定义事实表的行到底代表什么业务含义。一般的事实表粒度的选择应尽可能是最小的原子单元。在最小粒度的数据上设计出来的事实表是最健壮的设计。和那些粒度较大的数据相比，原子数据在响应突如其来的新查询和未预料到的数据加工方面具有更好的表现。

• 识别维度

​ 一旦事实表的粒度已经确定下来，就意味着确定了主键。对应的维度组合和相关的维度字段就能确定了，应该尽可能选择最小粒度，以此来应对各种各样的需求。

• 识别事实

​ 最后一步是仔细选择适用于业务过程的事实和指标，且事实的粒度要与所声明的事实表的粒度一致。

表的同步策略

这个说的是数据同步的时候我们根据它同步的方式不同去进行的划分。

数据同步策略的类型包括：全量表、增量表、新增及变化表、特殊表。通常数仓中DWD层是根据你的ODS层的表格的同步策略和是否按照分区进行设计。

全量表：存储完整的数据。

增量表：存储新增加的数据，数据随到随倒，小溪汇大海

新增及变化表：存储新增加的数据和变化的数据，比上面强点的是还能同步变化过的，那么这样就会有每次整过容一丢丢的数据了。。。。

特殊表：只需要存储一次。

拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据，然后如果当前信息至今有效的话，在生效结束日期中填入一个极大值（例如说9999-99-99）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XuJpe27c-1603555003668)(C:\Users\SWQ\AppData\Roaming\Typora\typora-user-images\1603554594534.png)]

在现在的大数据场景下，大部分的公司都会选择以Hdfs和Hive为主的数据仓库架构。目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表智能进行删除和添加操作，而不能进行update。基于这个前提，我们就可以实现拉链表。还是以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定一下我们有哪些数据源可以用。首先我们需要一张ODS层的用户全量表。至少需要用它来初始化。第二个就是每日的用户更新表。而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

另外补充在知乎上看到的每日的用户更新表该怎么获取的3种方式，通过它们可以拿到或者间接拿到每日的用户增量：

第一种就是我们可以监听Mysql数据的变化，从数据源头上监测，比如说用Canal，最后呢我们可以合并每日的变化，只需要获取到最后的一个状态就可以了。

第二种就是假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这种情况下我们可以对所有的字段先进行concat，再取md5，这样就ok了。

第三种就是制作流水表，然后有每日的变更流水表。

流水表

流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。

这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

我们要是想要知道这些数据，我们就可以创建流水表，也就相当于一个状态变更表这样的我们可以进行记录，然后在创建一张事务型事实表，这样就可以获取到这些变更数据。

拉链表当然也会遇到查询性能的问题，比如说我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，在网上看到两个思路就是说：

首先可以在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。那么还可以保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

那么对于表的记录就到这了~~

的时候性能就比较低了，在网上看到两个思路就是说：

首先可以在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。那么还可以保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

那么对于表的记录就到这了~~