转载自：https://www.cnblogs.com/traditional/p/9205300.html

数仓分层都分哪些层？

一般来说，数据仓库我们可以分为如下5层：

关于数仓分层，不同的公司分的层数是不一样的，并且数仓的每一层的命名也没有一个统一的标准，比如这一层就叫这个名字，但是整体思想是一样的。

ODS层：

用于存放原始数据，数据不做任何修改，所以这相当于起到一个备份作用。因为在数仓建设当中，数据是最重要的，宁可多费一些磁盘空间，也要保证数据的完整性。所以即使ODS层下面所有层的表数据都没了，也是没有关系的，因为只要有ODS层在，其余所有层的数据都可以重新生成，因为它们归根结底都是建立在ODS层之上进行相关操作得到的。

DWD层：

负责对ODS层数据进行清洗，因为原始数据可能会包含大量的脏数据，是不能直接用的，再加上DWD下面的层是用来分析指标的，因此在这一层要确保所有数据都是正常可用的。而这一步业界也有一个专们的岗位叫ETL，而用于ETL的工具一般有Hive SQL、MapReduce、Spark SQL、Python、kettle、Informatica等等。但是注意: 在清洗的过程中，表的结构和粒度与ODS层是一致的，比如ODS层的表是以"星期"为粒度来统计用户的下单次数，那么DWD层也应该以"星期"为粒度。

关于粒度，可以从细到粗、但无法从粗到细。比如当前粒度是"星期"，可以通过汇总的方式将粒度从"星期"变成"月"，但是显然我们无法将"星期"分解成"天"。

DWS层：

对DWD层数据进行轻度汇总，基本上是按天汇总。比如用户每下单一次，DWD层就会有一条记录；每评价一次、每点赞一次、每收藏一次，在DWD层都会各自对应一记录。所以DWS层一般会按照天来进行汇总，求出每个用户在每一天内总共下单多少次、点赞多少次、收藏多少次等等。所以本质上，就是将一张高表通过聚合、以及行转列的方式变成宽表。

DWT层：

对DWS层按照主题进行汇总，比如获取某个用户从创建以来直到现在，总共下单多少次。以我个人的京东账户为例：

显然这里的"总共下单"、"总共消费"、"累计节省"就相当于在DWT层按照主题进行的汇总。

ADS层：

这一层就是负责提供报表数据了，比如：日活跃用户、日交易额等等我们可以从DWS层来统计相关指标；比如：某个用户总共下单多少次、总共花了多少钱等等就可以从DWT层来统计相关指标，或者还可以按照下单总次数、花费总金额进行倒序排序，给top N的用户送几张优惠券等等。所以这一层主要是统计相关指标，用来进行报表展示的。

因此我们看到ADS层的数据可以来自于DWS、也可以来自于DWT，但是很少有来自于DWD层，因为这不符合数仓的建设规范。数仓的每一层都有它自己的规范，数据该从哪一层获取也有相应的规范，我们应该严格遵循。可能碰巧从ODS层来的数据比较干净、加上结构比较简单，我们从DWD层进行统计也能得到ADS层需要的数据，但即便如此也不要这么做，还是那句话，这不符合数仓的建设规范。

数仓为什么要分层

事实上在早期，数据过来的时候都是"一条龙服务"直接"一步到胃"，分层的概念是后面才提出的，但为什么要分层呢？原因如下：

1. 把复杂的问题简单化：通过将复杂的任务分解成多层来完成，每一层只负责一部分，这样出问题了可以快速定位。比如在ADS层时候，发现了一条脏数据，很明显这是在DWD层出现了问题，这时候直接去检查DWD层的清洗逻辑即可，看看是不是在清洗的时候遗漏了什么。

2. 减少重复开发：规范数据分层，通过中间层的数据，能够极大地减少重复计算，增加一次计算结果的重用性。比如：清洗工作，实际上只需要做一遍即可。但如果不分层，那么每统计一个指标都要从头到尾清洗一次，可想而知会有多少无意义的工作量。

3. 隔离原始数据：主要是针对ODS层，不管下面层级的数据如何，只要你发现结果不是想要的，都可以重新计算，甚至推到重来也没关系。原因就在于ODS层保证了原始数据的完整性，只要ODS层在，那么就可以使真实数据和统计数据之间完全解耦。因此ODS层的数据是无论如何都不可以丢的，它是后面所有的层的依赖，否则无论后面的层级设计的有多么优秀，也只是巧妇难为无米之炊。

数据集市和数据仓库的区别

数据集市(Data Market)，不同的书籍和公司对此有不同的看法，但主流的看法是：数据集市是一种微型的数据仓库，它通常有更少的数据、更少的主题区域、以及更少的历史数据，因此是部门级的，一般只能为某个局部范围内的管理人员提供服务。而数据仓库是企业级的，能为整个企业各个部门的运行提供决策支持手段。

可以认为：多个数据集市组成了一个数据仓库。

数仓命名规范

数据仓库的每一层都有不同的作用，所以它们的命名也要遵循一定的设计规则。

ODS层的表命名为：ods_表名

DWD层的表命名为：dwd_dim_表名、或者dwd_fact_表名，因为DWD层会有维表和事实表。另外由于公司不同表名也是不统一的，也有的公司将维表和事实表分别命名为：dim_表名和dwd_表名。

DWS层的表命名为：dws_表名

DWT层的表命名为：dwt_表名

ADS层的表命名为：ads_表名

临时表命名为：xxx_tmp或者tmp_xxx

用户行为表命名为：表名以log为后缀

范式理论

范式可以理解为设计一张符合标准级别的数据表结构时，所需要遵循的规范和要求。

而在关系型数据库设计时遵照一定的规范要求，可以带来很多好处。比如：降低数据的冗余性：

1. 因为数据冗余度高的话, 会增大磁盘开销

2. 在不使用分布式系统的情况下, 数据冗余度高的话, 可能需要增加磁盘的数量, 而单机的磁盘个数是有限的

3. 数据冗余度高的话, 会导致一次修改需要修改多个表, 很难保证数据的一致性

举个栗子：

假设有两张表，一张表存储用户的信息和购买过的商品、数量、以及金额；另一张表存放的是用户信息和它评价了哪些商品、搜藏了哪些商品。但是我们看到这样做会造成数据冗余，因为用户信息被存储了两次，所以这个时候可以将用户的信息单独抽出来作为一张用户表，并设置一个用户id。然后别的表只需要存储用户id即可，这样可以避免不必要的数据存储。

而且当用户修改信息之后，只需要更新用户表的信息即可，不会影响其它的表。但如果不把用户信息单独作为一张表的话，那么当用户修改信息时每张表都需要改一遍。

所以在如果设计上不遵循规范的话，那么就会造成许多问题，再举个栗子：

这种表设计相当于将员工信息、所在部门以及职位信息都存储到一个表中了，显然会存在如下问题：

数据冗余: 同一个部门的信息存储了多份, 需要占用更多的磁盘空间; 数据冗余有时候也可能是在不同的表中存储了重复的数据, 比如最开始的那个关于购买商品的栗子;

插入异常: 如果想要成立一个新的部门, 由于还没有增加新的员工, 因此无法录入这个部门的信息;

删除异常: 如果某个部门的所有员工都被删除, 该部门的信息也将不复存在;

更新异常: 如果需要修改部门信息, 那么会需要更新多行数据, 效率低下; 不小心忽略了某些记录的话，将会导致数据不一致;

为了解决这些问题，数据库引入了规范化过程。而规范化便使用我们上面说的"范式"来定义和衡量。关系模式的创始人 Edgar Codd 最早提出了第一范式（1NF）、第二范式（2NF）以及第三范式（3NF）。随后又出现了更高级别的范式，包括 BC 范式（BCNF）、第四范式（4NF）等。每个范式都基于前面的范式，例如第二范式需要先满足第一范式。它们之间的关系如下图所示：

下面我们对范式进行具体的分析，对于大多数的数据库系统而言，到达第三范式就已经足够了。

第一范式：

第一范式要求满足以下条件：

表中的字段都是不可再分的单一属性;

表需要定义主键(PRIMARY KEY);

简单来说，首先就是每个属性要有单独的字段。在上面的不规范设计中，员工的居住地址和手机号都存储在一个字段中，破坏了原子性。另外，还需要为表定义一个主键，用于唯一识别表中的每一行数据；假设每个部门中的员工不会同名，可以使用部门名称加员工姓名作为联合主键。

将上面的示例修改成以下结构就可以满足第一范式：

这里我们将原来的"居住信息"拆分成了两个字段，因为员工的手机号和住址是相互独立的；此外我们将"部门名称"和"姓名"作为了联合主键(假设不重复)，当然更常见的做法是使用数据库的自增主键。

第二范式：

第二范式要求满足以下条件：

满足第一范式;

非主键字段必须完全依赖于主键, 不能只依赖于主键的一部分(不能存在部分函数依赖);

A和B能够确定C，但是单独的A或B不能确定C，此时C完全依赖于(A, B)；A和B能够确定C，但是单独的A或B也能确定C，那么C部分依赖于(A, B)。而在第二范式中，非主键字段必须要完全依赖于主键。

示例中的"部门地址"只取决于"部门名称"、也就是主键的一部分，它和"姓名无关"，因此出现了部分函数依赖。显然，此时部门信息存在冗余，可能带来各种操作异常。

此时，可以将部门信息和职位信息存储到别的表中，并通过外键让它们和员工表之间建立一对多的关系。我们可以继续将表的结构修改如下：

第三范式：

第三范式要求满足以下条件：

满足第二范式;

属性不依赖于其它的非主键属性(不能存在传递函数依赖);

怎么理解呢？如果通过A能够确定B，通过B能够确定C，但是通过C不能确定A，那么C的传递依赖于A。

对于前三个范式而言，只需要将不同的实体/对象单独存储到一张表中，并且通过外键建立它们之间的联系即可满足。

此时，我们再来看看非规范化设计时的几个问题，会发现都得到了解决：

部门、员工以及职位信息分别存储一份，通过外键保持它们之间的联系。因此，不存在数据冗余的问题

如果想要成立一个新的部门，直接录入部门信息即可，解决了插入异常的问题

如果某个部门的所有员工都被删除，该部门的信息不会受到影响，不存在删除异常

如果需要修改部门信息，直接更新部门表即可，不会导致数据不一致

很多不搞大数据的公司可能要求在设计表的时候，必须严格符合第三范式，但是搞大数据的话，其实是不需要遵循第三范式的。因为搞大数据重点是在数仓的建设，分好层才是最重要的，另外如果在数仓建设中严格遵循第三范式的话，那么需要很多的外键，而为了维护外键需要额外的性能。但如果不用外键的话，在查询的时候就又会出现大量的join。

关系建模和维度建模

当今的数据处理可以分为两大类：联机事务处理(OLTP, on-line transaction processing)和联机分析处理(OLAP, on-line analytical processing)。OLTP是传统的关系型数据库的主要应用，主要进行基本的、日常的事务处理，比如银行交易；OLAP是数仓系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

把OLTP想象成PostgreSQL，OLAP想象成Hive即可。一般搭建数据仓库，所面临的场景都是数据量达到关系型数据库不好存储了(当然即便数据量不大，也是可以使用数仓的，把关系型数据库本身当成数仓也行)，而且对于数仓来说，它的重点在于大批量数据查询分析，不在于数据的导入。所以OLAP不可能像OLTP那样，来一条数据就插入一条，而是要批量导入的。另外Hive的底层存储使用的HDFS，从HDFS的设计原理来讲，我们也能看出它不适合少量数据的频繁导入，而是一次性导入大批量数据。

关系建模：

关系模型是要遵循第三范式的，通过拆分成多个物理表，来降低数据的冗余度。由于数据分布在众多的表中，这些数据可以更加灵活的背应用，功能性更强。关系模型主要应用在OLTP系统中，为了保证数据的一致性以及避免数据冗余，所以大部分业务系统的表都是遵循第三范式的。

维度建模：

维度模型不需要遵循第三范式，它主要应用于OLAP系统中，通常是以某一个事实表为中心进行表的组织，主要面向业务。特征是存在数据冗余，但是能最方便地得到数据。

关系模型虽然数据冗余度低，但是在大规模数据的跨表分析和统计查询的过程中，会造成多表关联，会导致执行效率的大幅度降低。而数仓主要在于查询，所以我们会采用维度建模，把相关的表分为两种：维度表和事实表。

而在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型。

星型模型：

事实表周围便是维度表(关于两者的区别后面会说)，星型模型要求"事实表"周围的"维度表"只能有一层。

雪花模型：

雪花模型的话，事实表周围的维度表可以有多层。可以看到雪花模型比较靠近第三范式，但是无法完全遵守。因为对于数仓建设来说，完全遵守第三范式的成本太高，原因就在于join。

星座模型：

星座模型和前两个模型的区别就在于事实表的数量，星座模型基于多个事实表，显然这才是数仓的常态。因为数仓肯定不只有一个事实表，并且维度表是可以被多个事实表共享的。比如上图：图中的事实表可以认为是员工参与辩论赛的一张表，有比赛等级(省级、市级、县级等等)，论文标题，参与身份(一个团队参赛、该员工扮演的角色)。而它周围的维表显然是员工的一些个人信息，这些个人信息显然还会被其它的表所需要，因此很好理解。

模型选择：

模型有以上三种，那么实际建设中要选择哪一种呢？

首先星座模型只跟数据和需求有关系，跟设计无关，这个不用选择。如果维度表被多个事实表需要，那么就是星座模型；否则就不是星座模型，但是绝大部分都是星座模型，因为不可能就只有一张事实表。所以我们看到星座模型和剩余两种模型之间是不冲突的，要么是星座模型+星型模型，要么是星座模型+雪花模型。

因此我们的重心是在星型模型和雪花模型的选择上，至于这两种模型选择哪种，则取决于性能优先，还是灵活优先。

目前实际企业开发中，不会绝对选择一种，而是根据情况灵活组合，甚至并存。但是从整体来看，更倾向维度更少的星型模型。尤其是Hadoop体系，减少join就是减少shuffle，而shuffle是一个性能非常低下的操作。而很多公司用的也正是Hadoop体系，比如Hive，本质上是对MapReduce进行了封装。

另外，OLAP大部分都是基于Hive、Spark，但是实际上还有很多其它更加优秀的框架，比如：kylin、presto等等，它们的表现更加优秀，只不过Hive、Spark出现的要早一些。但是，大人食大便啦。

Apache Kylin是一个开源的、分布式的分析型数据仓库，提供Hadoop/Spark 之上的 SQL 查询接口及多维分析（OLAP）能力以支持超大规模数据，最初由 eBay 开发并贡献至开源社区。它能在亚秒内查询巨大的表。

而Presto是由 Facebook 推出的一个基于Java(就很烦)开发的开源分布式SQL查询引擎,适用于交互式分析查询，数据量支持GB到PB字节。

我们有机会慢慢介绍。

维度表和事实表

维度表

维度表：一般是对事实的描述信息，每一张维度表对应现实世界中的一个对象或者概念。例如：用户、商品名、日期、地区等等，所以维度表描述是静态信息。

维度表的特征：

维表是一张宽表(具有多个属性、列比较多)

跟事实表相比，行数相对较小，通常小于10万条。比如公司的人员信息表就是一张维度表，显然公司很少会超过10万人。

内容相对固定，不是很容易变，因为是静态信息。比如人员表，存储人员的姓名、年龄、工号、手机号等等；比如商品表，存放商品的名称、价格、描述、功能等等。这些信息基本上是固定的，当然不是一直不变，而是变化频率很低。再比如时间表，我们为了避免计算，可能把某个日期是星期几、一年当中的第几天、一年当中的第几周、哪个季度、是否是周六日等等也会存储起来，而这种表一旦导入就不会再更改了。

所以，维度表存储的是那些会被其它表所引用的、不会常变的静态信息。

事实表

事实表：里面的一条数据都代表一个业务事件(下单、支付、退款、评价等等)，"事实"这个术语表示的是业务事件的"度量值"(可统计次数、个数、金额等等)，所以订单表便是一张事实表。

每一个事实表的行包括：具有度量值、与维表相关联的字段(比如: 通过user_id和用户表关联)。

事实表的特征：

非常的大

内容相对的窄(列数较少)

经常会发生变化，每天会新增加很多。

而事实表又分为以下三种：

事务型事实表：以每个事务或者事件为单位，例如一个销售订单记录、一个支付记录，便是事实表中的一行数据；一旦事务被提交，数据进入到事实表中，那么事实表中的数据就不能再修改了，其更新方式为增量更新。

周期型快照事实表：周期型快照事实表不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额、账户余额等等。

累计型快照事实表：累计型快照事实表用于跟踪业务事实的变化。例如：订单查询，我们在购买商品的时候，商品邮寄到达了什么地方都会动态更新。显然当这个业务进行时，事实表的记录也要不断更新或者插入。再比如工作中执行任务，当任务下发的时候，任务状态为"任务下发"；当员工接收任务时，任务状态为"任务处理中"；当员工完成任务之后上报时，任务状态为"任务已完成"。所以累计型快照事实表它负责跟踪一个业务的整个生命周期，每当状态发生更改，就会修改表记录、或者新增一条记录。

数据仓库建模

ODS层

1. 保持数据原貌, 不做任何修改, 只起到备份的作用;

2. 数据采用压缩, 减少磁盘使用;

3. 创建分区表, 每一天的数据是一个单独的文件夹, 防止后续使用where进行全表扫描;

DWD层

DWD层算是数仓中最重要的一层了，需要构建维度模型。一般采用星型模型，呈现的状态一般为星座模型。

而维度建模一般采用以下四个步骤：选择业务过程、声明粒度、确认维度、确认事实。

1. 选择业务过程

在业务系统中，挑选我们后续需要关注的业务线，比如：下单业务、支付业务、退款业务、物流业务等等，一条业务线对应一张事实表。

2. 声明粒度

数据粒度指数据仓库中保存的数据的细化程度或者综合程度的级别。

声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，依次来满足各种各样的需求。

比如：

订单事实表中每个商品项作为一行数据, 那么粒度就是"每次下单";

每周的订单次数作为一行, 粒度就是"每周下单";

每月的订单次数作为一行, 粒度就是"每月下单";

3. 确定维度

维度的主要作用是描述业务，主要表示"谁、何处、何时"等信息。

4. 确定事实

此处的"事实"一词，指的是业务中的度量值，例如订单金额、下单次数等等。

在DWD层，以"业务驱动"为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表，必要情况下可适当宽表化。

还是以电商为例：

横坐标是维度，纵坐标是事实表，不同的事实表会依赖同一张维度表。

至此数仓的建模已经完毕，DWS、DWT、ADS和维度建模已经没有关系了。

而DWS、DWT都是建宽表，而建宽表则按照主题去建，主题相当于观察问题的角度，对应着维度表。比如：图中有八张事实表，我想查看哪些商品被加入到购物车里面了、哪些商品被评价了、哪些商品被退款了等等，这里的商品便是主题。

DWS层

统计各个主题对象的当天行为，服务于DWT层的主题宽表，以及一些业务明细数据，应对特殊需求(例如：购买行为，统计商品复购率)。

DWT层

以分析的主题对象为建模驱动，基于上层应用和产品的指标需求，构建主题对象的全量宽表。

ADS层

对各大主题指标分别进行分析。

什么是拉链表?

一张表中的数据每日既有可能新增，也有可能修改，但是频率并不高，属于缓慢变化的维度，因此可以使用拉链表存储维度数据。

那么什么是拉链表呢？

拉链表：记录每条信息的生命周期，所以拉链表中都会有一个起始时间、一个结束时间、以及一个业务主键。当插入一条记录的时候，会将起始时间设置为插入时间、或者采集时间等等，把结束时间设置为NULL或者一个极大值(9999-09-09)。一旦来了一条新的记录，业务主键和上一条记录相同，那么就意味着上一条记录的生命周期结束了，所以会将设置上一条记录的结束时间。然后把这条新记录插入到表中，然后起始时间设置为插入时间或者采集时间，结束时间设置为NULL或者极大值。

光看文字不好理解的话，我们举几个栗子。

1. 以公司分配任务为例：

首先每个任务都有一个唯一的id，用于定位具体的任务，然后任务有三种状态：已接收、进展50%、办结。

操作人在2018-01-01的时候接收了id为000135任务，所以数据库中会产生这么一条记录，但是结束时间为极大值。

操作人在2018-01-16的时候，将id为000135任务完成了50%，所以上面的记录就成为了拉链(历史数据)。然后将这条新的数据插入进去，设置起始时间，然后结束时间为极大值，但是不要忘记设置已经变成拉链的历史数据的结束时间。这里我们将历史数据的结束时间设置成了新记录的起始时间的前一天，但是具体怎么设置可以由业务决定。

操作人在2018-02-12的时候，将id为000135任务完成了，此时任务的状态为办结。所以id为000135、任务状态为"进展50%"的记录也成为了拉链，所以我们筛选id为000135、结束时间为极大值的记录，设置它的结束时间。然后将新的记录插入进去，再将结束时间设置为极大值。

可以看到这便是拉链表，它记录了一个事物的整个生命周期，一旦它的结束时间不是极大值或者NULL，那么这条记录就变成了拉链。

2. 以人员表为例：

我们看到图中的也是一张拉链表，记录员工使用的手机信息，员工在某一个阶段使用的什么手机都进行了记录。

总结一下：拉链表就是记录历史，一个事物从创建到结束的整个生命周期，在不同的时间处于不同的状态，都会进行记录。一旦有了新的状态，那么过去的状态就变成了历史，将每一个过程都记录下来就是拉链表。

拉链表适合于：数据会发生变化，但是大部分是不变的(缓慢变化)。

如果数据不变，那么采用增量事实表，因为事实表的数据一旦导入就不会发生变化了。

那么我们如何使用拉链表呢？

通过 起始时间 <= 指定日期 < 结束时间，那么能够得到某个时间点的数据的全量切片。比如：我们想查看2018-05-05的时候员工使用的手机品牌，直接就可以进行定位，当然我们这里数据比较少。

关于粒度，可以从细到粗、但无法从粗到细。比如当前粒度是"星期"，可以通过汇总的方式将粒度从"星期"变成"月"，但是显然我们无法将"星期"分解成"天"。

拉链表的形成过程

以我所在的公司为例，从接口获取过来的数据有的是全量数据，就是所有数据全部在里面。所以我们可以在此基础之上增加两个字段：起始时间、结束时间，而且对方都会有一个时间字段，我们称之为业务时间，而起到标识的字段我们称之为业务主键(不是数据库的主键, 而是业务主键, 比如: 工号、任务id等等)。然后我们按天来采集对方的数据，假设从2019-01-01开始采集，导入到数据库；然后在采集2019-01-02的数据时，会和库中已有的2019-01-01的数据按照业务主键进行对比。如果业务主键相同，那么2019-01-01的数据就成为了拉链，然后将新数据导入进去。然后一直持续这个过程，直到将对方的数据获取完毕。

所以拉链表一定要有一个起始时间和结束时间，记录一个事物在不同阶段所处的状态。

我们举个栗子：

当我们采集完2019-02-02的数据时，准备导入到库中，这个时候要和2019-01-01的数据进行对比，然后将业务主键相同的数据变成拉链。显然这里的业务主键就是工号，因为工号可以精确定位到某个具体的员工。

对比完的结果就是这个样子。

对比完的结果就是这个样子。不过我所在公司从对方接口获取完数据之后，并不是直接就进行对比。针对不同的业务，我们都会有两张表，一张di表和一张df表，df表是拉链表，di表是拉链临时表。会通过任务调度工具airflow自动执行Python脚本从接口获取的数据，然后会先将数据原封不动的导入到di表，然后再通过di表和df表进行对比、设置拉链，后续都会基于df表进行操作，所以相当于说多了一步中转站(di)。所以整个流程如下：

所以拉链表其实很简单，一句话总结就是"记录历史"，重点也是难点在于和业务的结合。本人所在公司的拉链表远比当前图中说的复杂，而且是两层拉链表。从ODS层到DWD层还要走一次拉链表，因为还要考虑员工是否离职等很多因素。因此在写完拉链表的代码之后，我感觉自己快要升天了。

除了拉链表之外，在我们公司内部还有一个流水表，流水表比较简单，直接按天导入即可，没有所谓的对比更新逻辑。