数据仓库开发

• 数据开发流程规范及数据监控

1 数仓链路优化

• 第七章 数据仓库的链路优化_随缘清风殇的博客-CSDN博客_数据链路优化

1.1 CDM概述

• CDM：公共数据层，由DWD+DWS+ADS+DIM共同构成

（1）DWD核心：公共处理逻辑收敛和下沉

（2）DWS+ADS的核心：统一公共指标和公共维度，减少数据的不一致性

（3）DIM核心：建立整个业务范围内的一致性维度，并确保使用；

1.2 公用数据沉淀

• CDM层需不断根据上游的数据需求，将公用数据沉淀到CDM层，为其他的数据需求提供服务，减少重复计算并统一口径，开发过程是不断迭代的

1.3 链路长度度量

（1）长度限制：CDM层链路不宜过长，如果过长，请考虑复用&重构；

（2）深度限制：CDM层链路深度不宜过大，单个任务连路控制在一小时以内，否则数据重跑时间成本过大；

1.4 查询优先级别

（1）ADS层和临时查询：优先使用CDM层数据

（2）若ODS层数据没有沉淀到CDM，则通过新建CDM层的视图访问

2 数仓开发详细剖析

• #yyds干货盘点#数仓开发详细剖析_五分钟学大数据的技术博客_51CTO博客

2.1 数据各层作用

2.1.1 ODS（原始数据层）

日志数据和业务进入数仓后，首先放入该层，建立分区表，防止后续的全表扫描，使用 ORC 列式存储，同时对数据进行压缩，压缩格式采用 LZO，以减少存储空间。

日志：商品列表、商品点击、商品详情;广告;故障;后台活跃、通知;启动表;点赞、评论、收藏等。

业务数据：订单表、用户表、支付流水表、订单详情表、商品表、三级、二级、一级，物流信息(根据产品的来源，有两种，香港特快直送，闪电保税仓。一个从香港发货，一个从内地的保税仓发货)等。

2.1.2 DWD（明细数据层）

对 ODS 层数据清洗（去除空值，脏数据，超过极限范围的数据）。

1. 用户行为数据：自定义 UDF（extends UDF 实现 evaluate 方法），解析公共字段；自定义 UDTF(extends Genertic UDTF->实现三个方法 init(指定返回值的名称和类型)、process(处理字段一进多出)、close 方法)，自定义方法的好处在于更加灵活以及方便调试 bug。在自定义函数解析字段时，我们一般建立中间表，存放解析后的表，最后通过 get_json_object 获的我们所需要的字段，建立最终所需表。

2. 业务数据：维度退化+数据清洗（where group by）

3. 脱敏：利用 spark 对手机号、身份证号、银行账号等敏感信息进行脱敏处理。

4. ETL：通过 HQL、Kettle 对数据进行清洗。清洗标准是核心字段满足业务逻辑要求，去除重复、空值、超过时限等数据。一般清洗率为万分之一，如果大于这个数，需要和前端、javaEE 人员进行沟通。

5. 维度退化：商品表+三级分类、二级分类、一级分类=>商品表，省份+地区表=>省份表，其中我们用到的维度建模理论是星型模型，事实表周围 1 级维度。

6. LZO 压缩：减少存储空间

7. 列式存储：ORC，增加压缩比

8. 分区表：防止后续的全表扫描

重点重点：DWD 层我们使用的是标准的数仓建模理论

2.1.3 数仓建模怎么建？

按照数仓工具箱中的维度建模四步走来建的：

• 选择业务过程：由于我们公司当时数据量较小，我把 javeEE 涉及的业务表全部导入了，这些表包括实体表，维度表，事务型快照事实表，周期性快照事实表、累积型事实表。过来之后，将这些表作为矩阵的一个列。
• 声明粒度：粒度一般有：一行信息代表一次、按天、按周、按月等，参考了很多架构之后，我们考虑到后期想要分析更多的指标，只能选择最小的粒度，一行信息代表一次消费。
• 确认维度：采用标准数仓建模的思维，争取事实表周围都是 1 级维度。我们关系的就是什么时间、什么地点、什么人、具体什么活动、优惠券等主题的维度，同时将跟用户、商品相关的表进行维度退化，尽量把他们降成一级维度。
• 确认事实：这里我们确定的不是事实表，而是事实表的度量值，我们用到的度量值有订单的个数、订单的金额、下单次数等可以累加的字段。

2.1.4 DWS、DWT（每天的用户行为宽表）

每天的用户行为宽表、商品宽表，相当于一个周期型快照事实表。每天记录用户做了那些事情，商品被下单了多少。

1. DWS 宽表的字段我们是站在维度的角度来取的，比如站在用户的维度去看待周围的对应事实表，取事实表对应的度量值，取出订单的次数、订单的金额、支付的次数、支付的金额、加入购物车的次数、加入购物车的金额、评论的次数、点赞的次数、收藏的次数等等，将他们组合成为 DWS 层每天发生的事情。
2. 后期我们为了统计的指标，加了一个 DWT 层，DWT 层还是站在维度的角度去看待对应事实表，但是它和 DWS 有略微的区别，现在关注的是这个用户什么时间开始创建的，最后一次登录是什么时候，累计登录多少次，最近 30 天登录多少次等信息。

2.1.5 DWS、DWT 统称为服务层

1. 都是为后面的 ADS 层提供服务的，如果统计的是累积性指标，从 DWT 层拿取数据；如果统计的当天的指标，直接从 DWS 层取对应的数据。DWS 层最大的行为宽表是用户行为宽表，其字段有互动日期、用户 id、用户昵称、注册日期、注册来源、细分渠道、注册省份、评论次数、打赏次数、添加收藏、取消收藏、关注商品、取消关注的商品、关注人、取消关注的人、点不值次数、点值次数、点赞次数、分享次数、爆料数、加购物车数、取消购物车次数等待。DWT 也是用户行为宽表，其字段有互动日期、用户 id、用户昵称、注册日期、注册来源、细分渠道、注册省份、最后一次登录日期、累计登录日期、最近 30 天登录日期等等。
2. ADS 层：分析了 100 多个指标：包括 日活、月活、周活、留存、留存率、新增（日、周、年）、转化率、流失、回流、七天内连续 3 天登录（点赞、收藏、评价、购买、加购、下单、活动）、连续 3 周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠价人数、使用优惠价、沉默、值不值得买、退款人数、退款率 topN 热门商品、留转 G 复活等。

2.2 数仓业务详解

我们数据仓库是基于维度建模，主要使用星型模型。

2.2.1 维度表

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

维表的特征：

• 维表的范围很宽（具有多个属性、列比较多）
• 跟事实表相比，行数相对较小：通常< 10 万条
• 内容相对固定：编码表

2.2.2 事实表

分为事务型事实表（每个事务或事件为单位，一旦产生就固定）和周期型事实表（不会保留所有数据，只保留固定时间间隔的数据，比如每天、每月销售额）以及累积性事实表（累积型快照事实表用于跟踪业务事实的变化，比如订单的状态变化情况）。如果需要后面状态还会改变的就是周期型事实表，一旦确定了，就是事务性事实表。

事实表中的每行数据代表一个业务事件（下单、支付、退款、评价等） 。“事实”这个术语表示的是业务事件的度量值（可统计次数、个数、金额等） ，例如，订单事件中的下单金额。

每一个事实表的行包括：具有可加性的数值型的度量值、与维表相连接的外键、通常具有两个和两个以上的外键、外键之间表示维表之间多对多的关系。

事实表的特征：

• 非常的大
• 内容相对的窄：列数较少
• 经常发生变化，每天会新增加很多。

对于不同的表我们使用不同的同步策略：同步策略包括全量表，增量表，新增及变化，拉链表

2.2.3 日志表

商品点击，商品详情，商品详情页表，广告表，错误日志表，消息通知表等）

1. 商品点击：用户的基本信息字段，动作，商品 id，种类等。
2. 商品详情页：入口，上一页面来源，商品 id，加载时间，种类。
3. 广告表：入口，内容，行为，展示风格等。
4. 错误日志：错误详情
5. 消息通知表：通知类型，展示时间，通知内容等

这些记录性质的，都使用每日增量。

2.2.4 业务表

购物车，评分，评论，订单表，订单详情表，退货表，用户表，商家表，商品分类表（一级，二级，三级），支付流水，物流信息等）

1. 购物车详情：用户 id ，商品 id，商品价格，商家 id ，商品型号，商品分类等 同步策略：这属于周期型事实表，因为它可能会随时改变，所以得用每日新增及变化。

2. 评分表：评分时间，评分用户，评分商品 ，分数等。

   同步策略：这是事务性事实表，一般可以用每日增量就可以了，因为评论只能增加，不能修改。

3. 评论表：评论时间，评论用户，评论商品，评论内容。

   同步策略：这个跟评分差不多，用每日新增。

4. 订单表：订单状态，订单编号，订单金额，支付方式，支付流水，创建时间等

   同步策略：因为订单的状态会随时发生改变，比如下单，支付，商家发货，用户收到货，确认收货，等这一系列的状态会比较长，然后订单也比较多。所以，要做历史快照信息的话，最好使用拉链表。

5. 订单详情表：订单编号，订单号，用户 id，商品名称，商品价格，商品数量，创建时间等。

6. 用户表：用户 id，性别，等级，vip，注册时间等等。

   同步策略：因为表不是很大，每次做全量表。

7. 商家表：商家 id，商家地址，商家规模等级，商家注册时间，商家分类信息。

   同步策略：每次做每日全量。

2.3 总结

1. 实体表，不大，就可以做每日全量。
2. 对于维度表，比如说商品分类，这种不是很大，也可以做每日全量，有一些不太会发生改变的维度，就可以固定保存一份值，比如说：地区，种族等。
3. 像事务型事实表，比如说交易流水，操作日志，出库信息，这种每日比较大，且需要历史数据的，就根据时间做每日新增，可以利用分区表，每日做分区存储。
4. 像周期型事实表的同步策略，比如订单表，有周期性变化，需要反应不同时间点的状态的，就需要做拉链表。记录每条信息的生命周期，一旦一条记录的生命周期结束，就开始下一条新的记录。并把当前的日期放生效开始日期。

3 ODS、DWD、DWM理论实战

• 数仓建设 | ODS、DWD、DWM等理论实战（好文收藏）_五分钟学大数据的技术博客_51CTO博客

3.1 数据流向

3.2 应用示例

3.3 何为数仓DW

Data warehouse（可简写为DW或者DWH）数据仓库，是在数据库已经大量存在的情况下，它是一整套包括了etl、调度、建模在内的完整的理论体系。 数据仓库的方案建设的目的，是为前端查询和分析作为基础，主要应用于OLAP（on-line Analytical Processing），支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。目前行业比较流行的有：AWS Redshift，Greenplum，Hive等。 数据仓库并不是数据的最终目的地，而是为数据最终的目的地做好准备，这些准备包含：清洗、转义、分类、重组、合并、拆分、统计等

3.3.1 主要特点

• 面向主题
  • 操作型数据库组织面向事务处理任务，而数据仓库中的数据是按照一定的主题域进行组织。
  • 主题是指用户使用数据仓库进行决策时所关心的重点方面，一个主题通过与多个操作型信息系统相关。
• 集成
  • 需要对源数据进行加工与融合，统一与综合
  • 在加工的过程中必须消除源数据的不一致性，以保证数据仓库内的信息时关于整个企业的一致的全局信息。（关联关系）
• 不可修改
  • DW中的数据并不是最新的，而是来源于其他数据源
  • 数据仓库主要是为决策分析提供数据，涉及的操作主要是数据的查询
• 与时间相关
  • 处于决策的需要数据仓库中的数据都需要标明时间属性

与数据库的对比

• DW：专门为数据分析设计的，涉及读取大量数据以了解数据之间的关系和趋势
• 数据库：用于捕获和存储数据

3.4 为何要分层

数据仓库中涉及到的问题：

1. 为什么要做数据仓库？
2. 为什么要做数据质量管理？
3. 为什么要做元数据管理？
4. 数仓分层中每个层的作用是什么？

在实际的工作中，我们都希望自己的数据能够有顺序地流转，设计者和使用者能够清晰地知道数据的整个声明周期，比如下面左图。 但是，实际情况下，我们所面临的数据状况很有可能是复杂性高、且层级混乱的，我们可能会做出一套表依赖结构混乱，且出现循环依赖的数据体系，比如下面的右图。

为了解决我们可能面临的问题，需要一套行之有效的数据组织、管理和处理方法，来让我们的数据体系更加有序，这就是数据分层。数据分层的好处：

• 清晰数据结构：让每个数据层都有自己的作用和职责，在使用和维护的时候能够更方便和理解
• 复杂问题简化：将一个复杂的任务拆解成多个步骤来分步骤完成，每个层只解决特定的问题
• 统一数据口径：通过数据分层，提供统一的数据出口，统一输出口径
• 减少重复开发：规范数据分层，开发通用的中间层，可以极大地减少重复计算的工作

3.5 数据分层

每个公司的业务都可以根据自己的业务需求分层不同的层次；目前比较成熟的数据分层：数据运营层ODS、数据仓库层DW、数据服务层ADS(APP)。

3.5.1 数据运营层ODS

数据运营层：Operation Data Store 数据准备区，也称为贴源层。数据源中的数据，经过抽取、洗净、传输，也就是ETL过程之后进入本层。该层的主要功能：

• ODS是后面数据仓库层的准备区
• 为DWD层提供原始数据
• 减少对业务系统的影响

在源数据装入这一层时，要进行诸如去噪（例如有一条数据中人的年龄是 300 岁，这种属于异常数据，就需要提前做一些处理）、去重(例如在个人资料表中，同一 ID 却有两条重复数据，在接入的时候需要做一步去重)、字段命名规范等一系列操作。 但是为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据也可以，根据业务具体分层的需求来做。这层的数据是后续数据仓库加工数据的来源。数据来源的方式：

• 业务库
  • 经常会使用sqoop来抽取，例如每天定时抽取一次。
  • 实时方面，可以考虑用canal监听mysql的binlog，实时接入即可。
• 埋点日志
  • 日志一般以文件的形式保存，可以选择用flume定时同步
  • 可以用spark streaming或者Flink来实时接入
  • kafka也OK
• 消息队列：即来自ActiveMQ、Kafka的数据等。

3.5.2 数据仓库层DW

数据仓库层从上到下，又可以分为3个层：数据细节层DWD、数据中间层DWM、数据服务层DWS。

3.5.2.1 数据细节层DWD

数据细节层：data warehouse details，DWD(数据清洗/DWI) 该层是业务层和数据仓库的隔离层，保持和ODS层一样的数据颗粒度；主要是对ODS数据层做一些数据的清洗和规范化的操作，比如去除空数据、脏数据、离群值等。为了提高数据明细层的易用性，该层通常会才采用一些维度退化方法，将维度退化至事实表中，减少事实表和维表的关联。

3.5.2.2 数据中间层DWM

数据中间层：Data Warehouse Middle，DWM该层是在DWD层的数据基础上，对数据做一些轻微的聚合操作，生成一些列的中间结果表，提升公共指标的复用性，减少重复加工的工作。

简答来说，对通用的核心维度进行聚合操作，算出相应的统计指标

3.5.2.3 数据服务层DWS

数据服务层：Data Warehouse Service，DWS(宽表-用户行为，轻度聚合)该层是基于DWM上的基础数据，整合汇总成分析某一个主题域的数据服务层，一般是宽表，用于提供后续的业务查询，OLAP分析，数据分发等。一般来说，该层的数据表会相对较少；一张表会涵盖比较多的业务内容，由于其字段较多，因此一般也会称该层的表为宽表。

• 用户行为，轻度聚合对DWD
• 主要对ODS/DWD层数据做一些轻度的汇总。

3.5.3 数据应用层ADS

数据应用层：Application Data Service，ADS(APP/DAL/DF)-出报表结果。该层主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、Redis、PostgreSql等系统中供线上系统使用；也可能存放在hive或者Druid中，供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里的。

3.5.4 事实表 Fact Table

事实表是指存储有事实记录的表，比如系统日志、销售记录等。事实表的记录在不断地增长，比如电商的商品订单表，就是类似的情况，所以事实表的体积通常是远大于其他表。

3.5.5 维表层Dimension（DIM）

维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联，相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。维度表主要是包含两个部分：

• 高基数维度数据：一般是用户资料表、商品资料表类似的资料表，数据量可能是千万级或者上亿级别
• 低基数维度数据：一般是配置表，比如枚举字段对应的中文含义，或者日期维表等；数据量可能就是个位数或者几千几万。

3.5.6 临时表TMP

每一层的计算都会有很多临时表，专设一个DWTMP层来存储我们数据仓库的临时表

3.6 数据集市

狭义ADS层；广义上指hadoop从DWD DWS ADS 同步到RDS的数据数据集市（Data Mart），也叫数据市场，数据集市就是满足特定的部门或者用户的需求，按照多维的方式进行存储，包括定义维度、需要计算的指标、维度的层次等，生成面向决策分析需求的数据立方体。从范围上来说，数据是从企业范围的数据库、数据仓库，或者是更加专业的数据仓库中抽取出来的。数据中心的重点就在于它迎合了专业用户群体的特殊需求，在分析、内容、表现，以及易用方面。数据中心的用户希望数据是由他们熟悉的术语表现的。带有数据集市的数据仓储结构

3.6.1 区别数据仓库

数据集市就是企业级数据仓库的一个子集，它主要面向部门级业务，并且只面向某个特定的主题。为了解决灵活性与性能之间的矛盾，数据集市就是数据仓库体系结构中增加的一种小型的部门或工作组级别的数据仓库。数据集市存储为特定用户预先计算好的数据，从而满足用户对性能的需求。数据集市可以在一定程度上缓解访问数据仓库的瓶颈。 理论上讲，应该有一个总的数据仓库的概念，然后才有数据集市。实际建设数据集市的时候，国内很少这么做。国内一般会先从数据集市入手，就某一个特定的主题（比如企业的客户信息）先做数据集市，再建设数据仓库。数据仓库和数据集市建立的先后次序之分，是和设计方法紧密相关的。而数据仓库作为工程学科，并没有对错之分。 在数据结构上，数据仓库是面向主题的、集成的数据的集合。而数据集市通常被定义为星型结构或者雪花型数据结构，数据集市一般是由一张事实表和几张维表组成的。

3.7 问题总结

3.7.1 ODS与DWD区别？

问：还是不太明白 ods 和 dwd 层的区别，有了 ods 层后感觉 dwd 没有什么用了。

答：站在一个理想的角度来讲，如果 ods 层的数据就非常规整，基本能满足我们绝大部分的需求，这当然是好的，这时候 dwd 层其实也没太大必要。但是现实中接触的情况是 ods 层的数据很难保证质量，毕竟数据的来源多种多样，推送方也会有自己的推送逻辑，在这种情况下，我们就需要通过额外的一层 dwd 来屏蔽一些底层的差异。

问：我大概明白了，是不是说 dwd 主要是对 ods 层做一些数据清洗和规范化的操作，dws 主要是对 ods 层数据做一些轻度的汇总?

答：对的，可以大致这样理解。

3.7.2 APP层干什么的？

问：感觉DWS层是不是没地方放了，各个业务的DWS表是应该在 DWD还是在 app?

答：这个问题不太好回答，我感觉主要就是明确一下DWS层是干什么的，如果你的DWS层放的就是一些可以供业务方使用的宽表表，放在 app 层就行。如果你说的数据集市是一个比较泛一点的概念，那么其实 dws、dwd、app 这些合起来都算是数据集市的内容。

问：那存到 Redis、ES 中的数据算是 app层吗?

答：算是的，我个人的理解，app 层主要存放一些相对成熟的表，能供业务侧使用的。这些表可以在 Hive 中，也可以是从 Hive 导入 Redis 或者 ES 这种查询性能比较好的系统中。

4 数据仓库数据处理方式

• 16-数据仓库之数据建模、数据建模表的分类、数据建模步骤、数据分层的原因和优点_大数据下的画像人的博客-CSDN博客_数据仓库和数据建模

4.1 OLTP

1. 定义理解
   • OLTP的全称是On-line Transaction Processing，中文名称是联机事务处理
2. 特点
   • 主要用于管理事物，用来处理高并发且数据量级不大的查询
   • 此类系统专注于short on-line-tansactions，如insert、update、delete、query操作
   • 通常存在此系统中的数据都是以实体对象模型来存储的，并且满足3NF
3. 应用场景
   • 由于OLTP主要是为了操作数据而设计（操作系统），用于处理已知的任务和负载
   • 常见的优化在于主码索引和散列，检索特定的记录。去优化某一些特定的查询语句。

4.2 OLAP

1. 定义理解
   • OLAP的全称是 On-line Analytical Processing,中文名称是联机分析处理
2. 特点
   • 查询频率比OLTP系统低，但通常会涉及到非常复杂的聚合计算
   • OLAP系统以维度模型来存储历史数据，其主要存储描述性的数据并且在结构上都是同质的
3. 应用场景
   • OLAP则是为了分析数据而设计（数据仓库）的，其查询的方式往往是复杂且未知的，通常会涉及大量数据在汇总后的计算
   • 这种需要基于多维视图的数据操作在OLTP上执行的时候性能将是非常差的，并且也是极其危险的

4.3 OLTP和OLAP的区别总结

1. OLTP 和 OLAP：这两个术语看起来很相似，但指的是不同类型的数据处理系统
   • 联机事物处理（OLTP）可以实时捕获、存储和处理来自事物的数据。
   • 联机分析处理（OLAP）使用复杂查询来分析来自OLTP系统的聚合历史数据
2. 对于这两种系统的使用
   • 问题不在于选择哪一种，而在于如何针对您的情况充分利用这两种处理类型
   • 在线分析处理（OLAP）和在线事务处理（OLTP）。主要区别在于，一个使用数据来获得有价值的见解，而另一个则纯粹是操作性的。但是，有一些有意义的方法可以使用这两个系统来解决数据问题

	OLTP	OLAP
特性	处理大量小额交易	通过复杂的查询处理大量数据
查询类型	简单的标准化查询	复杂查询
操作	基于插入、更新、删除命令	基于 SELECT 命令聚合数据以进行报告
响应时间	毫秒	秒、分钟或小时，具体取决于要处理的数据量
设计	特定于行业，例如零售、制造或银行业	特定于主题，例如销售、库存或市场营销
源	交易	来自交易的聚合数据
目的	实时控制和运行基本业务运营	规划、解决问题、支持决策、发现隐藏的见解
数据更新	由用户发起的简短、快速更新	使用计划的、长时间运行的批处理作业定期刷新数据
空间要求	如果存档了历史数据，则通常很小	由于聚合大型数据集，通常很大
备份和恢复	需要定期备份以确保业务连续性并满足法律和公司治理要求	可以根据需要从 OLTP 数据库重新加载丢失的数据，以代替常规备份
生产力	提高最终用户的工作效率	提高业务经理、数据分析师和高管的工作效率
数据视图	列出日常业务交易	企业数据的多维视图
用户示例	面向客户的人员、文员、在线购物者	知识工作者，如数据分析师、业务分析师和高管
数据库设计	规范化数据库以提高效率	用于分析的非规范化数据库

5 数据建模

1. 数据建模的理解

   • 数据建模是指对现实世界各类数据的抽象组织，确定数据库需要管辖的范围、数据的组织形式等直至转化为现实的数据库

2. 数据建模的目标（将经过系统分析后抽象出来的概念模型转化为物理模型需要注意的方面）

   • 访问性能：良好的模型能帮我们快速查询需要的数据，减少数据的IO吞吐
   • 数据成本：减少数据冗余、计算结果复用、从而降低存储和计算成本
   • 使用效率：改善用户使用数据的体验，提高使用数据的效率
   • 数据质量：改善统计口径的不一致性，减少数据计算错误的可能性，提供高质量的、一致的数据访问平台

   综上，大数据的数仓建模需要通过建模的方法更好地组织、存储数据，以便在性能、成本、效率和数据质量之间找到最佳平衡点

5.1 ER建模

1. 定义理解
   • 把数据抽象成二维表，将事物抽象为“实体”、“属性”、“关系”来表示数据关联和事物描述
   • ER模型是数据库设计的理论基础，当前几乎所有的OLTP系统设计都采用ER模型建模的方式，且该建模方法需要满足3NF
   • 从全企业的高度设计一个3NF模型的方法，用实体加关系描述的数据模型描述企业业务架构，站在企业高度面向主题的抽象，而不是针对某个具体业务流程的实体对象关系抽象
     • 简而言之，第一范式就是无重复的列。
     • 简而言之，第二范式就是非主属性完全依赖于主关键字。
     • 简而言之，第三范式就是属性不依赖于其它非主属性。
2. 优点
   • 规范性好、冗余小、数据集成和数据一致性方面得到重视
3. 缺点
   • 需要全面了解企业业务、数据和关系；实施周期非常长、成本昂贵；对建模人员的能力要求也非常高，容易烂尾

5.2 维度建模

1. 维度建模出现的原因
   • 逐渐随着企业数据的高增长，复杂化，数仓全部使用ER模型建模显得越来越不合时宜。为什么呢，因为其按部就班的步骤，三范式等，不适合现代化复杂，多变的业务组织
2. 定义理解
   • 维度建模以分析决策的需求出发来构建模型，构建的数据模型为分析需求服务，因此他的重点在于解决用户如何快速完成分析需求，同时还有较好的大规模复杂查询的响应性能，更直接面向业务
3. 维度建模中的两种类型的表
   • 事实表
     • 发生在显示世界中的操作型事件，都是发生在实体之间的，伴随着这种操作所产生的的可度量的值，而这个过程就产生了一个事实表，存储了每一个可度量的事件。从最低的粒度级别来看，事实表对应一个度量事件，反之亦然
   • 维度表
     • 每个维度表都包含单一的主键列。维度表的主键可以作为与之关联的任何事实表的外键，当然，维度表行的描述环境与事实表行完全对应
     • 维度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性
   • 度量值
     • 度量值是对一次行为的度量
4. 优点
   • 技术要求不高，快速上手，敏捷迭代，快速交付；
   • 更快的完成分析需求，较好的大规模复杂查询的响应性能
5. 缺点
   • 维度表的冗余会较多，视野狭窄

6 维度建模表分类

6.1 维度模型思维导图

在维度建模中，将度量称为“事实”，将环境描述为“维度”

例：今天张三买了一瓶两块的矿泉水
在这里：”今天“、“张三”、“买”、”矿泉水“是维度，“一瓶”，“两块”是事实

6.2 维度表

维度表中存放了具有独立属性和层次结构的数据，一般由维度编码和对应的维度说明组成

6.2.1 维度表的概念

1. 维度

   • 维度是维度建模的基础和灵魂，对观察数据的角度

   • 在维度建模中，将度量称为“事实”，将环境描述为“维度”，维度是用于分析事实所需要的多样环境

   • 表示时间：日期-年-月-日-季-周（是不是有点像日期表）
     表示地点：国-省/州-市-区县-镇-村
     品类：用途-品牌-包装
     …………

     类似上面这些具有独立属性或层次结构的信息，我们将其称之为数据的维度

2. 维度属性

   • 维度所包含的表示维度的列，称为维度属性
   • 维度属性是查询约束条件、分组和报表标签生成的基本来源，是数据易用性的关键

3. 维度表的特征

   • 维度表的范围很宽（具有多个属性、列比较多）
   • 跟事实表相比，行数较少，（通常小于10万条）
   • 内容相对固定

6.2.2 维度表的设计原则

1. 维度属性尽量丰富，为数据使用打下基础
   • 比如淘宝商品维度有近百个维度属性，为下游的数据统计、分析、探查提供了良好的基础。
2. 给出详实的、富有意义的文字描述
   • 属性不应该是编码，而应该是真正的文字

6.3 事实表

事实表示对业务数据的度量，通常是数字类型的，可以进行聚合和计算

事实表中存储里能体现实际数据或者详细数值，一般由维度编码和事实数据组成

7 数据仓库分层

7.1 数仓分层原因

1. 用空间换时间
   • 通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量的冗余数据；
2. 增强扩展性
   • 不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大
3. 分层管理
   • 简化数据清洗过程
     • 通过数据分层管理可以把原来的一步工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，所以简化了数据清洗的过程
   • 简化处理逻辑
     • 同时数据分层使每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误时，我们只需要调整某个步骤即可

7.2 数仓分层优点

1. 清晰数据结构
   • 每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便的定位和理解
2. 方便数据血缘追踪
   • 简单来说，我们最终给业务呈现的是一个能直接使用的业务表，但是它的来源有很多，如果有一张来源表出问题了，我们能够能够快速准确地定位到问题，并清楚它的危害范围
3. 减少重复开发
   • 规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。
4. 把复杂问题简单化
   • 将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。
5. 屏蔽原始数据的异常
   • 屏蔽业务的影响，不必改一次业务就需要重新接入数据

7.3 数仓分层明细

数据分层每个企业根据自己的业务需求可以分成不同的层次，但是最基础的分层思想，理论上数据分为三个层：1 ODS层; 2 DW层; 3 ADS层

7.3.1 ODS原始数据层

1. 定义理解
   • ODS（Operate data store），操作数据存储层
   • 该层最接近数据源中数据的一层，或者只对原始数据进行了少量的操作
   • 数据源中的数据，经过抽取、洗净、传输后，也就是经过ETL之后，转入ODS层
2. 存储的数据种类
   • 业务数据
     • 表结构数据直接从原来的表结构拉取，保持了ER模型
   • 行为数据
     • 日志采集到什么就存储什么，有可能只会稍微做一点点适配于Hive表的修改
   • 本层的数据，总体上多是按照源头业务系统的分类方式而分类的
     • 例如：MySQL里面的一张表可以通过sqoop之间抽取到ODS层

7.3.2 DW数据仓库层

1. 定义理解
   • Data warehouse（数据仓库）
   • 使用维度建模按照主题重新整理ODS层的数据
   • 例如以研究人的旅游消费为主题的数据集中，可以结合航空公司的登机出行信息和银联系统的刷卡记录进行结合分析，产生数据集。
2. 四个概念
   • 维度（dimension）
     • 维度表是事实表不可或缺的组成部分。维度表包含业务过程度量事件有关的文本环境。他用来描述与"谁、什么、哪里、何时、如何、为什么"有关的事件。
   • 事实（Fact）
     • 事实涉及来自业务过程的度量，基本都以数量值表示。一个事实表行与粒度存在一对一关系。
   • 指标（Index）
     • 指标是业务流程节点上的一个数值。比如销量、价格、成本等
   • 粒度（Granularity）
     • 粒度就是业务流程中对度量的单位，比如商品是按件记录度量，还是按批记录度量。

7.3.3 ADS数据服务层

1. 定义理解
   • Application Data Service(应用数据服务)
   • 该层主要是提供数据产品和数据分析使用的数据，一般会存放在ES、MySQL等系统中供线上系统使用，也可能会存在Hive或者Druid中供数据分析和数据挖掘使用
     • 该层数据都是直接展示给老板看的数据–报表数据–BI数据
   • 绝对不要在ADS层进行第二次的数据计算，对应指标的结果应该在进入到ADS层之前就已经算好了
   • 例如：我们经常说的报表数据，一般就放在这里

7.3.4 数仓分层明细图解

8 数据仓库建设命名规范

• 数据仓库建设命名规范

数据模型是数据管理的分析工具和交流的有力手段；同时，还能够很好地保证数据的一致性，是实现商务智能（Business Intelligence）的重要基础。因此建立、管理一个企业级的数据中心，应该遵循标准的命名和设计规范。

8.1 命名规范

8.1.1 表属性规范

• 表名ODS层表名：　　
  前缀为ODS_应用系统名（缩写）数据表名 。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“”分割，例如：ODS_FUN_CUSTOMERINFO。表名称不能用双引号包含，表名长度不超过30个字符。如果ODS设计采用贴源设计，数据表名应与源系统一致。
• DW事实表表名　
  前缀为DW_主题名（缩写）功能描述 。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“”分割，例如：DW_ORD_DETAIL。表名称不能用双引号包含，表名长度不超过30个字符。
• DWS应用层表名

前缀为APP_主题名（缩写）功能描述 。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“”分割，例如： APP_RPT_ DEALER_GOODS。表名称不能用双引号包含，表名长度不超过30个字符。

• DW维度表表名

前缀为D_ 。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“_”分割，例如：D_ACCOUNT、D_PUB_DATE。表名称不能用双引号包含，表名长度不超过30个字符。

• 元数据表名

前缀为M_应用名（缩写）功能描述 。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“”分割，例如：M_ETL_TASK。表名称不能用双引号包含，表名长度不超过30个字符。

• 表分区名

前缀为p 分区名必须有特定含义的单词或字串。

例如 ：ods_pstn_detail 的分区p2004100101表示该分区存储 2004100101时段数数据。

• 字段名

字段名称必须用字母开头，采用有特征含义的单词或缩写，不能用双引号包含。尽量将类同的字段排列在一起，，例如开户日期，销户日期等主外键以PK_和FK_等开头

8.1.2 索引

普通索引前缀为IDX_。索引名称应是 前缀+表名+构成的字段名。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。表名可以去掉前缀。

• 主键索引

前缀为IDX_PK_。索引名称应是 前缀+表名+构成的主键字段名，在创建表时候用using index指定主键索引属性。

• 唯一索引

前缀为IDX_UK_。索引名称应是 前缀+表名+构成的字段名。

• 外键索引

前缀为IDX_FK_。索引名称应是 前缀+表名+构成的外键字段名。

• 函数索引

前缀为IDX_func_。索引名称应是 前缀+表名+构成的特征表达字符。

• 簇索引

前缀为IDX_clu_。索引名称应是 前缀+表名+构成的簇字段。

8.1.3 其他数据库对象

• 视图

前缀为V_。按业务操作命名视图。

• 物化视图

前缀为MV_。按业务操作命名实体化视图。

• 存储过程

前缀为SP_ 。按业务操作命名存储过程。

• 触发器

前缀为Trig_ 。触发器名应是 前缀 + 表名 + 触发器名。

• 函数

前缀为Func_ 。按业务操作命名函数。

• 数据包

前缀为Pkg_ 。按业务操作集合命名数据包。

• 序列

前缀为Seq_ 。按业务属性命名。

• 普通变量

前缀为Var_ 。 存放字符、数字、日期型变量。

• 游标变量

前缀为Cur_ 。存放游标记录集。

• 记录型变量

前缀为Rec_ 。 存放记录型数据。

• 表类型变量

前缀为Tab_ 。 存放表类型数据。

• 数据库链接

前缀为dbl_ 。 表示分布式数据库外部链接关系

8.2 质量规范

8.2.1 数据质量规范

每个ODS全量表必须配置唯一性字段标识。

每个ODS全量表必须有注释。

每个ODS全量表必须监控分区空数据。

仅有监控要求的ODS表才需要创建数据质量监控规则。未来可以通过数据资产管理系统配置数据质量监控规则。

建议对重要表的重要枚举类型字段进行枚举值变化及枚举值分布监控。

建议对ODS表的数据量及数据记录数设置周同环比监控，如果周同环比无变化，表示源系统已迁移或下线

8.2.2 公共字段定义规范

数据统计日期的分区字段按以下标准：

1. 按天分区：ds(YYYYMMDD)。
2. 按小时分区：hh(00~23)。
3. 按分钟：mi(00~59)。
4. is_{业务}：表示布尔型数据字段。以Y和N表示，不允许出现空值域。
5. 原则上不需要冗余分区字段。

8.2.3 数据冗余

一个表做宽表冗余维度属性时，应该遵循以下建议准则：

1. 冗余字段与表中其它字段高频率（大于3个下游应用SQL）同时访问。
2. 冗余字段的引入不应造成其本身的刷新完成时间产生过多后延。
3. DW层数据不允许字段重复率大于60%的相同粒度数据表冗余，可以选择在原表基础上拓宽或者在下游应用中通过JOIN方式实现。

8.2.3 数据拆分

数据的水平和垂直拆分是按照访问热度分布和数据表非空数据值、零数据值在行列二维空间上分布情况进行划分的。

1. 在物理上划分核心模型和扩展模型，将其字段进行垂直划分。
2. 将访问相关度较高的列在一个表存储，将访问相关度较低的字段分开存储。
3. 将经常用到的Where条件按记录行进行水平切分或者冗余。水平切分可以考虑二级分区手段，以避免多余的数据复制与冗余。
4. 将出现大量空值和零值的统计汇总表，依据其空值和零值分布状况可以做适当的水平和垂直切分，以减少存储和下游的扫描数据量。

8.2.4 空值处理原则

1. 汇总类指标的空值：空值处理，填充为零，当前hdp基于列存储的压缩技术不会由于填充大量空值导致存储成本上升。
2. 维度属性值为空：在汇总到对应维度上时，对于无法对应的统计事实，记录行会填充为-99（未知），对应维表会出现一条-99（未知）的记录。