1.2 推荐阅读
本文涉及的数据规范，整体参考了部分成熟的文档资料并结合实际的项目实施经验进行整合归纳，理论指导实践，想要形成自己的知识体系，推荐阅读：
- 《数据仓库工具箱：维度建模权威指南(第3版)》
- 《大数据之路：阿里巴巴大数据实践》
- 部分参考文章：
2、开发流程管理
2.1 数仓开发流程
数仓的常用开发流程：
- 需求分析调研（数据调研、需求调研、业务调研）：明确业务及数据需求，评估开发排期
- 模型设计：根据业务需求做模型设计（确认来源系统、表及表间关系、表数据量、同步频率、模型加工逻辑、模型建表等）
- ETL开发：STG->ODS->DWD->DWS->DM
- 数据验证：结合数据测试标准，做验证测试
- 任务调度：规范化调度参数配置
- 上线管理：上线迁移、初始化&验证
- 运维管理：运维支持
3、数据模型原则
3.1 数仓分层原则
优秀可靠的数仓体系，往往需要清晰的数据分层结构，既要保证数据层的稳定又要屏蔽对下游的影响，并且避免链路过长。一个好的分层架构，需要有以下好处（可结合数据流向参考图理解）：
- 清晰的数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。
- 数据血缘追踪：简单理解，我们最终给业务呈现的是一张能直接使用的张业务表，但是它的来源有很多，如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。
- 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算
- 复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复
- 屏蔽原始数据的影响：简单理解，模型逻辑和字段不变的情况下，原始数据字段的新增、修改和变更只影响到STG/ODS的字段mapping对应关系，不影响后续DW模型层处理逻辑。
数仓分层一般要结合项目实际业务场景进行，项目上一般采用的数仓分层设计如下：
各层级典型数据流向参考：
3.1.1 数据贴源层
贴源层一般会细分为：STG(数据缓冲层) 和 ODS(原始数据层)
-
- 数据缓冲层：STG：
针对数据源的大数据量表，一般会采用增量同步的策略，增量同步的数据一般会放在数据缓冲层，只存增量数据(即和ODS相比，STG只有部分增量数据)，然后再将数据更新或覆盖到ODS(原始数据层)表中。
该层一般只为ODS层服务。
-
- 原始数据层：ODS：Operation Data Store
ODS层是最接近数据源中数据的一层，为了考虑后续可能需要追溯数据问题，因此对于这一层不建议做过多的数据清洗工作，原封不动的接入原始数据即可，数据的去重、去噪、异常值处理等可以放到后面的dwd层来做。
ODS的表结构和数据需要与业务系统基本保持一致，仅做简单整合、非结构化数据结构化处理或者增加标识数据日期描述信息，不做深度清洗加工。
- 字段名：与业务系统字段名保持一致，字段类型也尽可能保持一致
- 对于数据量比较大的业务表，采用增量同步的方式，则要同时建立增量表(STG)和全量表(ODS)。
- 对于日志、文件等半结构数据，不仅要存储原始数据，还要存储结构化之后的数据。
3.1.2 数据公共层
数据公共层是我们在做数据仓库时要核心设计的一层，在这里，从ODS层获得的数据按照主题建立各种数据模型。数据公共层又细分为DWD（明细数据层）、DWS（轻度汇总层）、DIM（维度表）、DWM（中间层，未在分层图中展示）
-
- 数据明细层：DWD(数据清洗/DWI) Data Warehouse Detail
对ODS层数据进行数据清洗、整合、规范化，脏数据、垃圾数据、规范不一致的、状态定义不一致的、 命名不规范的数据都会被处理，生成DWD层数据。
同时，为了提高数据明细层的易用性， 该层会采用一些维度退化手法， 将维度退化至事实表中， 减少事实表和维表的关联。基于维表建模，明细宽表，复用关联计算，减少数据扫描。
-
- 轻度汇总层：DWS(宽表-用户行为，轻度聚合) Data Warehouse Service
DWS 层为公共汇总层， 会进行轻度汇总， 粒度比明细数据稍粗， 基于 DWD 层上的基础数据， 整合汇总成分析某一个主题域的服务数据， 一般是宽表。
-
- 数据中间层：DWM (中间表数据) Data Warehouse Middle
在实际计算中， 如果直接从 DWD 或者 ODS 计算出宽表的统计指标， 会存在计算量太大并且维度太少的问题， 因此一般的做法是， 在 DWM 层先计算出多个小的中间表， 然后再拼接成一张 DWS 的宽表。可以理解为DWM为中间表层(dwd->dwm->dws)，复杂的计算需要拆表时可以定义DWM中间表用于存放中间结果数据。
-
- 维表层：DIM (Dimension)
维度表一般是主数据比如用户数据、配置数据等，如果主数据/维表比较多，可以针对维表单独设计一层，维表层主要包含两部分数据：
- 高基数维度数据：一般是用户资料表、 商品资料表类似的资料表。 数据量可能是千万级或者上亿级别
- 低基数维度数据：一般是配置表， 比如枚举值对应的中文含义， 或者日期维表。数据量可能是个位数或者几千几万
维度表存主数据，DWD/DWS层存维度表主键，最后的DM层如果是宽表需要冗余所有的字段，可在数据落DM表时，通过DW表的维度表主键关联维度表取数据， 设计的好处：维度主数据如果有变更（比如名称、说明等变化），只需要更新dim层表数据即可，中间DW层无需刷新历史数据。
维度表又细分为三种
- 单级维（星形模型）
- 多级维（雪花模型）
- 缓慢变化维
缓慢变化维：维度建模的数据仓库中，有一个概念叫Slowly Changing Dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。
处理缓慢变化维的方法通常分为三种方式：
第一种方式是直接覆盖原值。这样处理，最容易实现，但是没有保留历史数据，无法分析历史变化信息。第一种方式通常简称为“TYPE 1”。
第二种方式是添加维度行。这样处理，需要代理键的支持。实现方式是当有维度属性发生变化时，生成一条新的维度记录，主键是新分配的代理键，通过自然键可以和原维度记录保持关联。第二种方式通常简称为“TYPE 2”。
第三种方式是添加属性列。这种处理的实现方式是对于需要分析历史信息的属性添加一列，来记录该属性变化前的值，而本属性字段使用TYPE 1来直接覆盖。这种方式的优点是可以同时分析当前及前一次变化的属性值，缺点是只保留了最后一次变化信息。第三种方式通常简称为“TYPE 3”。
在实际建模中，我们可以联合使用三种方式，也可以对一个维度表中的不同属性使用不同的方式，这些，都需要根据实际情况来决定，但目的都是一样的，就是能够支持方便的分析历史变化情况。
3.1.3 数据应用层
-
- DM（数据集市层）：DataMart
在这里， 主要是提供给数据产品和数据分析使用的数据，针对特定业务或者报表需求的结果表，偏定制化，一般是宽表。 一般数据会按需存放在 PostgreSql、Doris、Greenplum、Clcikhouse、Mysql 等系统中供线上系统使用， 也可能会存在 Hive 中供数据分析和数据挖掘使用。 比如我们经常说的报表数据， 一般就放在这里。
数据集市层 根据主题域的不同可以在物理上进行划分——它表现为多个相互独立的库，各个数据集市之间不允许做数据依赖。每个数据集市可以由该主题域的使用方在数据仓库规范下自行开发和建设。
-
- ADS(应用层 APP/DAL/DF/DM)- 出报表结果 Application Data Store
基于应用的数据组装：宽表集市、趋势指标，个性化指标加工：定制化、复杂性指标（大部分复合指标），做分析处理同步到RDS数据库里边。
作用等价于DM数据集市层，一般项目上使用其中之一就好了，常用DM层；
3.1.4 数仓存储架构
各层级存储原则参考：
3.2 主题域划分原则
主题域的考虑可以从数据仓库层或者数据集市层出发，考虑将全域的数据分散到若干个主题中存放。就像图书管理员需要将书籍分门别类一样，主题域的划分是为了将相关的数据组织在一起，从而使其更容易被寻找和使用。
主题域的划分强依赖于对业务层面的理解。在一个不熟悉全局业务的情况下，划分主题域是非常困难的。
3.2.1 按照业务或者业务过程划分
业务容易理解，就是指的功能模块/业务线，比如EBS中的采购模块(PO)，库存模块(INV)，总账模块(GL)等；
业务过程，指企业的业务活动事件，以电商业务为例，如下单、支付、退款都是业务过程。需要注意的是，一个业务过程是一个不可拆分的行为事件，通俗的讲，业务过程就是企业活动中的事件。
3.2.2 按照数据域划分
数据域是指面向业务分析， 将业务过程或者维度进行抽象的集合。 其中， 业务过程可以概括为一个个不可拆分的行为事件， 在业务过程下， 可以定义指标， 维度是指度量的环境， 如买家下单事件， 买家是维度。
为保障整个体系的生命力， 数据域是需要抽象提炼， 并且长期维护和更新的， 但不轻易变动。 在划分数据域时，既能涵盖当前所有的业务需求， 又能在新业务进入时无影响地被包含进已有的数据域中和扩展新的数据域。
此种划分方式适用于对企业业务和数据流有深入了解的场景。
3.3 数据模型设计原则
3.3.1 高内聚、 低耦合
即主题内部高内聚、 不同主题间低耦合。 明细层(dwd)按照业务/业务过程划分主题， 汇总层(dws)按照“ 实体+ 活动” 划分不同分析主题， 应用层(dm)根据应用需求划分不同应用主题。（Ps：各层级主题也可按照统一的业务/业务过程进行简单划分，按项目情况调整即可）
将业务相近或者相关、粒度相同的数据设计为一个逻辑或者物理模型：将高概率同时访问的数据放一起 ，将低概率同时访问的数据分开存储。
3.3.2 核心模型和扩展模型要分离
建立核心模型与扩展模型体系， 核心模型包括的字段支持常用的核心业务（即相对通用的dw表）， 扩展模型包括的字段支持个性化或少量应用的需要（定制化扩展表，适用于一个dw模型表需要适配N多复杂场景共用的情况，个性化需求添加扩展表的模式）， 不能让扩展模型的字段过度侵入核心模型， 以免破坏核心模型的架构简洁性与可维护性（过度侵入后，导致每次增加新需求，都需要调整dw公共表结构，过于频繁的修改dw公共模型表会导致其他稳定的流程出问题bug）。
- 小tips：
-
- 扩展字段：为适配新增的需求，可在设计dw表时增加扩展字段(att1-20)，少部分新增字段需求可存储在扩展字段中，或者新增扩展表处理。
- 维度字段处理：维度表除主键外的其他易变更的字段(比如说明、备注等)，在dm层落表时再关联获取，dw层只存储维度表id及编码等不可变字段(如果中间dw层会用到这些不可变字段的话)
- 维度模型VS宽表：dw层更建议使用维度建模，dm层走宽表
3.3.3 公共处理逻辑下沉及单一
越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现， 不要让公用的处理逻辑暴露给应用实现， 不要让公共逻辑多处同时存在。
3.3.4 成本与性能平衡
适当的数据冗余可换取查询和刷新性能， 不宜过度冗余与数据复制。（这个度，需要项目上结合实际情况把控）
3.3.5 数据可回滚
处理逻辑不变， 在不同时间多次运行数据结果确定不变。（对代码实现的要求，可重运行，结果不变，比如STG到ODS的数据插入，要考虑数据更新/清空机制，避免运行N次数据重复N倍）
3.3.6 一致性
相同含义的字段命名及定义，在不同表中保持一致
3.3.7 命名清晰、可理解
表命名需清晰、一致，表名需易于使用方理解
4、数仓建模基础
常用概念、部分名词解释，面向初级数据开发人员。
4.1 数据建模
4.1.1 什么是数据建模
数据建模简单来说就是基于对业务目标的理解，将各种数据进行整合和关联，并最终使得这些数据可用性，可读性增强，让使用方能快速的获取到自己关心的有价值的信息并且及时的作出响应，为公司带来效益。
翻译版：
数据建模简单来说就是基于对业务目标的理解（自顶向下-以业务需求为导向，或自底向上-整体分析构建全域数据中心，看业务想要什么样的数据），将各种数据（包括跨源、跨系统的数据表）进行整合和关联（各源原始数据通过ETL同步到数仓中做关联分析），并最终使得这些数据可用性，可读性增强（原始数据杂乱无章，按业务需求对数据做加工处理、分析计算得到业务需要的结果数据），让使用方能快速的获取到自己关心的有价值的信息（业务需要的数据都经过处理放到dw/dm宽表里了，直接查表）并且及时的作出响应(dm结果数据放OLAP数据集市里，查询秒级响应)，为公司带来效益(用数据驱动业务，根据展示的结果数据，对生成经营过程做指导，比如数据显示某类商品卖的好-增产，某类商品卖不动-减产或停产)。
白话版：
淘金，指淘金者们打捞起河里或湖里的淤泥后，在淘盘将淤泥洗涤，以便找出淤泥里的天然金沙，然后售卖。
数据建模可类比淘金，需要先将淤泥（高价值和低/无价值数据混合在一起）打捞起来（数据可能分散在各个异构系统或者线下，需要将数据做归集，打捞的过程对应ETL中的数据抽取），在淘盘（数仓底座，比如hadoop、doris等,提供存储和算力）将淤泥洗涤（建立数据模型，对数据做加工处理的逻辑，常使用SQL技术语言去对数据做过滤、关联、分析处理），以便找出淤泥里的天然金沙（产出有价值的结果数据，前提是需要的结果数据能从原始数据中加工出来，对原始数据质量有一定要求，避免比如想看淘宝的购物记录但是打开的却是拼多多里面没有淘宝购物记录），然后售卖（提供数据服务或者报表给用户看），日复一日的打捞淘金（使用数据开发平台如HDSP去管理整套加工流程，定时调度并做运维监控）。
4.1.2 建模工具
专业的建模工具（使用方式自行查阅网上文章）：
- PowerDesigner、ezdml、erwin
项目常用的建模工具：
- Excel ：Excel对业务顾问建模简单一些，交付给技术去看时也比较方便
4.1.3 Kimball和Inmon架构
数据仓库，简称数仓，主要有四种架构，Kimball的DW/BI架构、独立数据集市架构、辐射状企业信息工厂Inmon架构、混合Inmon与Kimball架构。不过不管是那种架构，基本上都会使用到维度建模。
- Kimball 模式从流程上看是是自底向上的，即从数据集市到数据仓库再到数据源(先有数据集市再有数据仓库)的一种敏捷开发方法。对于Kimball模式，数据源每每是给定的若干个数据库表，数据较为稳定可是数据之间的关联关系比较复杂，须要从这些OLTP中产生的事务型数据结构抽取出分析型数据结构，再放入数据集市中方便下一步的BI与决策支持。所以KimBall是根据需求来确定需要开发ETL哪些数据。以需求为导向，一般使用星型模型；
- Inmon 模式从流程上看是自顶向下的，即从数据源到数据仓库再到数据集市的（先有数据仓库再有数据市场）一种瀑布流开发方法。在Inmon模式中，并不强调事实表和维度表的概念，由于数据源变化的可能性较大，须要更增强调数据的清洗工做，从中抽取实体-关系。immon是将整个数据仓库规划好，统一按照范式建模进行开发。以数据源为导向，模型偏向于3NF。
两种架构的简单对比：
| 特性 | Kimball | Inmon |
| 时间 | 快速交付 | 路漫漫其修远兮 |
| 开发难度 | 小 | 大 |
| 维护难度 | 大 | 小 |
| 技能要求 | 入门级 | 专家级 |
| 数据要求 | 特定业务 | 企业级 |
类比实际项目场景（各自适应不同的应用场景）：
- Kimball，做BI数仓，以BI需求为导向，分析要同步的数据源表、数据加工处理逻辑。
- Inmon，做企业全域数仓，盘点有哪些数据源、哪些业务数据，然后看能基于这些数据去出哪些数据集市和分析结果。
4.2 模型建设方法
4.2.1 ER模型
ER模型常见于三范式，适用于OLTP业务系统开发。
在关系数据模型设计中，一般需要满足第三范式的要求，如果一个表有良好的主外键设计，就应该是满足3NF的表。通过规范化设计的好处是通过减少数据冗余提高更新数据的效率，同时保证数据完整性。规范化程度越高，划分的表就越多，在查询数据时越有可能使用表连接操作。而如果连接的表过多，会影响查询的性能。
所以也要避免过度规范化的问题。关键是要依据业务需求，仔细权衡数据查询和数据更新的关系，制定最适合的规范化程度。
4.2.2 维度建模
维度建模是一种将大量数据结构化的逻辑设计手段，包含维度和指标，它不像ER模型目的是消除冗余数据，维度建模是OLAP面向分析，最终目的是提高查询性能，所以会增加数据冗余，并且违反三范式。
维度建模也是重点关注让用户快速完成需求分析且对于复杂查询及时响应，根据事实表和维度表的关系，维度建模一般可以分为三种：
- 星型模型
- 雪花模型
- 星座模型
其中最常用的是星型模型。
4.2.2.1 事实表和维度表
4.2.2.1.1 维度表
维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联；相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）、地点表（包含国家、省／州、城市等属性）等。维度是维度建模的基础和灵魂。
优点：
- 缩小了事实表的大小。
- 便于维度的管理和维护，增加、删除和修改维度的属性，不必对事实表的大量记录进行改动。
- 维度表可以为多个事实表重用，以减少重复工作。
类比：淘宝购物，你选择的商品-对应商品维表，维护的收获地址-地址维表，你登录的用户-用户维表等；
4.2.2.1.2 事实表
指存储有事实记录的表，如系统日志、销售记录等；事实表的记录在不断地动态增长，所以它的体积通常远大于其他表。
事实表作为数据仓库建模的核心，需要根据业务过程来设计，包含了引用的维度和业务过程有关的度量。
类比：淘宝购物，你购买商品生成的订单- 订单交易事实表（里面包含了商品维、收获地址维等），你浏览商品的历史记录-浏览历史记录事实表；
4.2.2.2 星型模型 ***
星型模型中只有一张事实表，以及0张或多张维表，事实表与维表通过主键外键相关联，维表之间不存在关联关系，当所有维表都关联到事实表时，整个图形非常像一种星星的结构，所以称之为“星型模型”。
星型模型是最简单最常用的模型。星型模型本质是一张大表，相比于其他数据模型更合适于大数据处理。其他模型可以通过一定的转换，变为星型模型。
星型模型的缺点是存在一定程度的数据冗余。因为其维表只有一个层级，有些信息被存储了多次。比如一张包含国家、省份、地市三列的维表，国家列会有很多重复的信息。
4.2.2.3 雪花模型
当一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的"层次"区域，这些被分解的表都连接到主维表而不是事实表。
其优点是通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能，避免了数据冗余。其缺点是增加了主键-外键关联的几率，导致查询效率低于星型模型，并且不利于开发。（Ps：维度表设计有3NF范式设计的味道了）
4.2.2.4 星座模型
星座模型也是星型模型的扩展。区别是星座模型中存在多张事实表，不同事实表之间共享维表信息，常用于数据关系更复杂的场景。其经常被称为星系模型。
4.2.2.5 3种数据模型对比
| 属性 | 星型模型（星座模型） | 雪花模型 |
| 事实表 | 1张或多张 | 1张或多张 |
| 维表 | 一级维表 | 多层级维表 |
| 存储空间 | 多 | 少 |
| 数据冗余度 | 高 | 低 |
| 可读性 | 高 | 低 |
| 表个数 | 少 | 多 |
| 表宽度 | 宽 | 窄 |
| 查询逻辑 | 简单 | 复杂 |
| 查询性能 | 高 | 低 |
| 扩展性 | 差 | 好 |
通过上面的对比分析，可以发现数据仓库大多数时候比较适合使用星型模型来构建底层数据 hive 表，通过大量的数据冗余来减少表查询的次数以提高查询效率。星型模型对OLAP的分析引擎比较友好，而雪花模型在关系型数据库中（MySQL/Oracle）中非常常见 即3NF范式设计，尤其是在电商的数据库表。在具体规划设计时，应结合具体场景及两者的优缺点来进行设计，找到一个平衡点。
4.3 名词解释
4.3.1 宽表
宽表从字面意义上讲就是字段比较多的表。通常是指业务主题相关的指标、维度、属性关联在一起的表。
通常做法是把很多的维度关联到事实表中，形成一张既包含了大量维度又包含了相关事实的表。
4.3.1.1 宽表的误区
不是所有层级都适合做宽表即冗余维表数据（比如dwd/dws）。
宽表的使用，有其一定的便利性。使用方不需要再去考虑跟维度表的关联，也不需要了解维度表和事实表是什么东西。
但是随着业务的增长，始终无法预见性地设计和定义宽表究竟该冗余多少维度，也无法清晰地定义出宽表冗余维度的底线在哪里。
一个可能存在的情况是，为了满足使用上的需求，要不断地将维表中已经存在的列增加到宽表中。这会导致宽表的表结构频繁发生变动。
4.3.1.2 模型的选择：维度模型 VS 宽表
维度模型和宽表的对比：
| 比较点 | 维度建模 | 宽表 |
| 扩展性 | 维度表变更，事实表可能不影响 | 维度变更可能导致很多宽表都要调整 |
| 耦合度 | 事实表和维度表解藕，某些粒度上不会因为维度表失败而影响聚合表的产出 | 一个非重要任务失败会导致整个宽表无法产出 |
| 组织方式 | 任务及工作流易组织 | 因高耦合导致任务之间盘根错节，不利于组织任务和工作流 |
| 数据一致性 | 企业级数据仓库总线架构的基石 | 底层如果没有维度建模支撑，容易陷入混乱 |
| 易用性 | 维表需要多几个维表关联 | 宽表一时爽 |
| 适用层级 | 数据公共层(dwd/dws/dwm) | 数据应用层(dm/ads) |
维度模型和宽表可结合使用，在数仓层(dw)建议选择采用标准的维度建模的方式—星型模型。而宽表则可以存在于更靠后的数据集市层(dm)。
- 扩展性：想要让dw层表设计更有扩展性，即少量新字段需求不用每次都调整表结构，可以提前预留10个或多个attribute扩展字段，少量新字段需求存扩展字段中，按需使用。
-
- 表新增字段后，SQL语句好调整，但是涉及表结构调整比如新增字段，一些不规范写法(比如使用select * ：insert into target_table as select * from xxxx）会因xxxx表结构变更后导致原本正常的SQL语句直接报错。
-
-
- 报错：SemanticException [Error 10044]：Table insclause-0 has 3 columns, but query has 4 columns ， 查询的结果和写入的表列数对不上
-
-
- 所以：insert插入语句中使用select * 其实有风险，尤其是需求开发频繁调整的模块，更容易因为表结构的变更导致SQL语句出错，写明字段会使程序更健壮。
4.3.2 粒度
粒度问题是设计数据仓库的一个最重要方面。粒度是指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级就越小；相反，细化程度越低，粒度级就越大。
笼统的说，粒度就是维度的组合。一般常说的按统计粒度，就是指按哪些维度条件做分析(求和、求平均等)计算。
4.3.3 维度退化
冗余，将一些常用的维度属性直接写到事实表中的维度操作称为维度退化，常用于dm层的宽表冗余维度属性；
4.3.4 规范化
按照三范式形成设计是事实和纬度表的方式管理数据称为规范化，规范化常用于OLTP系统的设计
4.3.5 反规范化
将维度的属性信息合并到事实表中的操作称为反规范化(不按照3NF的设计故意做数据冗余)。反规范化会产生包含全部信息的宽表，形成数据冗余；实现用空间换取简明性和查询性能的效果，常用于OLAP系统的设计
4.3.6 OLTP 和 OLAP
4.3.6.1 OLTP
OLTP（on-line transaction processing），联机事务处理，主要是对数据的增删改，是数据库的应用。
OLTP主要用来记录某类业务事件的发生，如购买行为，当行为产生后，系统会记录是谁在何时何地做了何事，这样的一行（或多行）数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功，像公司常见的业务系统如ERP，CRM，OA等系统都属于OLTP。（mysql、oracle、sqlserver等支持事务的数据库）
4.3.6.2 OLAP
OLAP（On-Line Analytical Processing），联机分析处理，主要是对数据的查询，是数据仓库的应用。
当OLTP的数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就是在做OLAP了。
因为OLTP所产生的业务数据分散在不同的业务系统中，而OLAP往往需要将不同的业务数据集中到一起进行统一综合的分析，这时候就需要根据业务分析需求做对应的数据清洗后存储在数据仓库中，然后由数据仓库来统一提供OLAP分析。
4.3.6.3 两者对比
| 比较点 | OLTP | OLAP |
| 业务目的 | 处理业务，如订单、合同等 | 数据分析，业务支持决策 |
| 面向对象 | 业务处理人员 | 分析决策人员 |
| 主要工作负载 | 增、删、改 | 查询 |
| 主要衡量指标 | 事务吞吐量（TPS） | 查询响应速度（QPS） |
| 数据库设计 | 3NF 或 BCNF | 星型/雪花模型 |
OLAP和OLTP之间的关系可以认为OLAP是依赖于OLTP的，因为OLAP分析的数据都是由OLTP所产生的，也可以看作OLAP是OLTP的一种延展，一个让OLTP产生的数据发现价值的过程。
4.3.7 下钻
从汇总到明细，比如淘宝订单，购买10件商品订单总金额100， 想要查看对应10件商品的金额明细，查看明细表示下钻。
数据明细，粗粒度到细粒度的过程，会细化某些维度
下钻是商业用户分析数据的最基本的方法。下钻仅需要在查询上增加一个维度属性，附加在SQL的GROUP BY语句中。属性可以来自任何与查询使用的事实表关联的维度。下钻不需要存在层次的定义或是下钻路径。
4.3.8 上卷
基于明细做汇总，比如淘宝订单，购买了10件商品每个商品单价10元，我要看订单总金额是多少钱，就需要对订单金额做汇总得到100元的总价，汇总金额表示上卷。
数据的汇总聚合，细粒度到粗粒度的过程,会无视某些维度
4.3.9 增量表
增量数据是上次导出后的新数据。
- 记录每次增加的量，而不是总量
- 只汇报变化量，无变化不汇报
- 每天一个分区
4.3.10 全量表
所有的数据。
- 全量表，有无变化都要报。
- 每次上报的数据都是所有的数据
- 只有一个分区
4.3.11 快照表
按日分区，记录截止数据日期的全量数据。
- 快照表，有无变化，都要报
- 每次上报的数据都是所有的数据
- 一天一个分区
4.3.12 拉链表
记录截止日期的全量数据
- 记录一个事物从开始，一直到当前状态的所有变化的信息
- 拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总 量
- 当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）
- 只有一个分区
阿里的onedata理论；
5、数据模型规范
5.1 分层规范
5.1.1 项目数仓分层
数据仓库一般分为三层，自下而上分别为数据贴源层（ODS，Operation Data Store）、数据公共层（CDM，Common Data Model）和数据应用层（ADS，Application Data Service），每一层会细分并创建独立的schema/database，此处以Hive数仓为例，每一层会创建独立的database用于存放对应层级的表和数据。
其中：数据贴源层可细分为stg和ods，数据公共层可细分为dwd/dws/dim，数据应用层可细分为dm/ads.
- stg：缓存层，存增量/临时数据，一般只为ods层服务；
- ods：原始数据层，与业务侧db实体保持同构
- dwd：明细层，对ods数据进行规范化（编码转换、清洗、统一格式、脱敏等,维表走外键形式）
- dws：汇总层，对dwd各信息做聚合分析，输出聚合分析结果
- dim：维度表，公共的维表主数据
- dm：集市层，面向业务需求定制开发，冗余宽表
实际数仓层级架构可结合项目业务场景做调整，比如：
5.1.2 层次调用规范
标准的数据流向，即：ODS -> DWD -> DWS -> DM；
原则上ODS的表只能被DWD引用（一般场景是ODS数据质量不高，需要经过清洗加工得到标准数据到dwd，即dwd是高质量数据），DWS和DM等禁止直接使用ODS的表。实际项目过程中如果ODS质量有保障，也会出现ODS->DWS->DM的跨层引用流向图，按实际情况处理。
但是：禁止出现反向依赖，例如DWD的结果表反向依赖DWS/DM的表数据。
各层级数据流调用图参考：
5.1.3 命名规范
5.1.3.1 基本命名规范
- 命名使用具有意义的英文词汇，词汇中间以下划线分隔（反例：appName或AppName，正例：app_name）,禁止使用驼峰式命名。
- 命名只能使用英文字母，数字，下划线。
- 避免使用SQL、Mysql、Hadoop等数据库的保留字和关键字，比如desc、关键字index。
- 命名使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。
- 命名中若使用特殊约定或缩写，则要注释说明。
- 除非必要,所有对象名称统一使用小写字母书写。
5.1.3.2 表命名规范
国际通行的命名方式有两种：驼峰命名法 (camelCase) 和蛇形命名法 (snake_case)。推荐采用蛇形命名法——全小写，单词之间用下划线分隔。
下面以常用的hive数仓为例，其他数仓类型可参考思路设计规范。
| 数据层级 | 分层 | Schema | 表命名 | 存储格式 | 说明 |
| 数据贴源层 | stg | stg | stg_<原系统简称>_<原系统表名> | TEXTFILE | 增量缓存/临时数据，字段类型统一为String（使用HDSP建表则需要检查默认生成的类型），存储格式用textfile，用来原封不动对接原始数据。【stg_erp_po_headers】. |
| ods | ods | ods_<原系统简称>_<原系统表名> | ORCFILE | 原始数据，镜像表贴源数据，字段类型：id类使用bigint，金额计算类decimal(38,8)，其他类型都是string；数据量原则上和原系统表保持一致。【ods_erp_po_headers】 | |
| 数据公共层 | dwd | dwd | dwd_<主题简称>_<内容描述>_<分表规则> | ORCFILE | 明细数据做加工，比如维度值退化、过滤无效数据或字段转换等；【dwd_po_headers_di】 |
| dws | dws | dws_<主题简称>_<内容描述>_<分表规则> | ORCFILE | 轻度汇总数据，基于维度做一些汇总计算 【dws_po_headers_amount_di】 | |
| dim | dim | dim_<主题简称>_<维度含义> dim_<维度含义> | ORCFILE | 维度数据 【dim_hr_employees】 | |
| 数据应用层 | dm | dm | dm_<主题简称>_<内容描述>_<分表规则> | ORCFILE | 业务分析数据 【dm_po_orders_report_di】 |
| 特殊表 | 适用于所有分层 | bak_<目标表名>_<日期> | ORCFILE | 归档备份表，bak_后可直接跟原表名+日期后缀，记录备份结果。【bak_dwd_po_headers_di_20210101】 | |
| mid_<目标表名>_[0~9] dwm_<目标表名>_[0~9] | ORCFILE | 中间表，存储中间计算结果数据的表，一般用于较复杂程序计算。目标表名是指任务中的目标表名字，通常一个任务只会有一个目标表，加上表名是防止自由发挥时表名冲突。（如果大量使用中间表，也可单开一个schema用于存储中间表计算结果，看实际项目需求） 【mid_dwd_po_headers_di_1,mid_dwd_po_headers_di_2】 | |||
| tmp_<自定义表名> test_<自定义表名> | ORCFILE | 临时表，临时适用一次的测试表，暂时保存下数据看看，后续一般不再使用，可以随时删除。只用于测试验证。 【tmp_yyc_demo01，test_yyc_demo01】 | |||
| v_<目标表名> | view | 视图，用于SQL逻辑的封装 【v_dwd_po_heaers_di】 | |||
| dq_<模块质检编码>_<目标表> | ORCFILE | 质检结果表，按质检编码区分质检项，用于做数据质量检查的结果表，以dq开头为标识，一般会给每个质检项分配一个质检编码。 【dq_inv001_dwd_month_inventory_di】 | |||
数据公共层和应用层(dwd/dws/dm)如果想要体现表内的数据周期，比如是按天、按周、按小时更新，可额外添加<分表规则>后缀，类似：dwd_<主题简称>_<数据内容>_<分表规则>，dwd_po_headers_di 增量每天同步；
常用分表规则：（非必须）
另外表命名后缀建议带上分表规则，如xxx_di,xxx_df
Hive的Textfile和ORC格式，压缩率的对比：
5.1.3.3 字段命名规范
- 表达其实际含义的英文单词或简写，命名不允许使用中文或者特殊字符，单词间用下划线分隔。
- 各表之间相同意义的字段应同名。
- 字段不要使用关键字如:”DATA”和保留字,如:” GROUP”.
- 字段名的长度不超过30个字符.
5.1.3.4 作业命名规范
略，详见HDSP开发手册中的作业命名规范。
5.2 对象设计使用规范
5.2.1 表设计规约
*业务，代表该规则适用于OLTP业务表，数仓表是面向OLAP查询的该规则不适用
- 【强制】所有表、字段均应用comment 列属性来描述此表、字段所代码的真正含义，如枚举值则建议将该字段中使用的内容都定义出来
- 【强制】禁止在数据库中存储明文密码；
- 【强制】需要join的字段，数据类型保持绝对一致。
- 【强制】创建数据表（DDL）的时候,必须要指定schema。不能创建在默认schema中。
- 【强制】小数类型为 decimal，禁止使用 float 和 double
- 【强制】Hive建表时，一般stg层使用textfile格式，ods及之上的层级表使用orc存储格式，无特殊说明的情况下一般是创建内部表，外部表按需创建。
- 【强制*业务】建表的时候，尽量不用规定主键和外键和检查约束，入库的数据应在入库前整理好。
- 【强制*业务】如无说明，表必须包含五个字段：object_version_number，creation_date，created_by，last_update_date，last_updated_by (含义：版本号-用来处理锁，记录的创建时间，创建人，最后更新时间，最后更新人) 。
- 【推荐】适当冗余，减少关联查询，反范式化（冗余字段不是频繁修改的字段）；
- 【推荐】禁止使用复杂数据类型（数组，自定义等）。
- 【推荐】表的数据周期要有文档明确规定，并说明在某处进行处理。
- 【推荐】按照日期进行查询，或者需要定期按照日期删除的表，建议使用分区表。
- 【推荐】对于数据量超过1TB的大表,需从应用设计方面，考虑对大表进行优化，例如是否可划分为历史数据表和当前数据表，并分开存放；是否合理分区；是否应定期清理数据，是否合理分段等等。
- 【推荐*业务】如无说明，表中的第一个id字段一定是主键且为自动增长，禁止在非事务内作为上下文作为条件进行数据传递。
- 【推荐*业务】如无备注，标记字段，比如是否启用 ENABLE_FLAG ，都建议设置一个默认值；
- 【参考】Mysql存储引擎，如无说明，建表时一律采用 InnoDB引擎，不同场景选用不同的存储引擎 ； （myisam与innodb的区别）
- 【参考】用尽量少的存储空间来存储一个字段的数据；
- 【参考】按需设计冗余字段，比如attribute1-20，String类型，用于数据扩展。
5.2.2 索引设计规约
- 【强制*业务】重要的SQL必须被索引，核心SQL优先考虑覆盖索索引
-
- UPDATE、DELETE语句的WHERE条件列
- ORDER BY、GROUP BY、DISTINCT的字段
- 多表JOIN的字段
- 【强制*业务】主键原则：
-
- 表必须有主键
- 不使用更新频繁的列
- 尽量不选择字符串列
- 默认使用非空的唯一键
- 建议选择自增（Mysql）或 序列（Oracle）
- 【强制*业务】区分度/筛选性最大的字段放在前面
-
- 选择筛选性更优的字段放在最前面，比如单号、userid等，type，status等筛选性一般的不建议放在最前面。
-
-
- 筛选性更优表示作为限制条件能返回尽可能少的数据量； RATE = DISTINCT / COUNT(*) ，值越小，表示筛选性越好；
-
-
- 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
- 【强制*业务】索引禁忌，拒绝索引抑制
-
- 使用不等于运算符
- 使用IS NULL 或 IS NOT NULL
- 使用LIKE '%xxx'
- 使用数学运算和函数运算，如：TRUNC(索引列)， 索引列+1 > 10
- 关联条件存在隐式转换（不匹配的数据类型）
- 【强制*业务】Mysql 中索引的限制
-
- MYISAM 存储引擎索引长度的总和不能超过 1000 字节
- BLOB 和 TEXT 类型的列只能创建前缀索引（因为该类型数据太大，Mysql直接创建索引会报错，索引也是占空间的）
- MYSQL 目前不支持函数索引（Oracle支持函数索引，比如非空判断 nvl(disable_flag,'N') 可作为索引列，比较常用）
- 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。（不建议此语法，是否等价改写，不用死记硬背，是否走索引可查看执行计划）
- 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。（产生索引抑制）
- join语句中join条件字段类型不一致的时候MYSQL无法使用索引。（隐式的类型转换）
- 使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
- 使用非等值查询的时候, MYSQL 无法使用 Hash 索引。
- 【推荐】尽量不使用外键 （我们项目没有使用外键，阿里巴巴的SQL规约中也写明：不得使用外键与级联，一切外键概念必须在应用层解决）
-
- 外键用来保护参照完整性，可在业务端实现
- 对父表和子表的操作会相互影响，降低可用性
- INNODB本身对online DDL的限制
- 【推荐】索引的基本命名规则
-
- 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名（项目上有的用 表名_N1）
- 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名（项目上有的用 表名_U1）
- 主键约束：pk_表名称。 （项目上有的是表名_PK）
- 唯一约束：uk_表名称_字段名。（应用中需要同时有唯一性检查逻辑。）(项目上一般用唯一索引)
- 【参考】索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个。
5.2.3 数据类型规范
统一规定不同的数据的数据类型， 严格按照规定的数据类型执行（以Hive数仓为例）：
- 【强制】金额： 使用 decimal(38,8) 控制精度
-
- 有金额类数据计算（加减乘除等）时，如果金额字段是String类型，需要转成decimal(38,8)再做计算，否则字符串间做加减会有精度丢失（比如1+1=1.99999），转换函数： cast (column as decimal(38,8))
- decimal(38,8)表示最多38位数字，后8位是小数，小数点前最多30位。
- Hive的decimal最大精度是38
- 【强制】字符串： string。
- 【强制】时间： string。
- 【强制】状态： string。
- 【推荐】id 类： bigint 或者string。
即：主键id类使用bigint或string，金额计算类的decimal(38,8)，其他类型都是string。
- 类型越少开发越简单：id类型直接用string接也都ok（有时建表不想区分太细很废时间），但是金额类强制decima(38,8)，因为涉及金额计算时String类型计算有精度丢失每次都要做cast(column as decimal(38,8))的转换也麻烦。
-
- ID类字段做比较时，比如取id的最大最小值，如果字段存的String类型注意转换为BIGINT后再比较值。
- 针对日期使用string类型：比如日期格式 YYYY-MM-DD ，同样的格式和长度前提下，string类型的字符串可直接做比对，字符串的比较是按从左到右逐个字符按ASCII码做大小比较；只要长度和格式相同，正常比较就行，无需转成data日期类型再比较。
- 可从dw建模开始：在不清楚原始表字段含义的时候，数据类型可先统一赋值给string是不会有问题的（ods是抽数不涉及计算无影响），到dw数仓公共层做模型设计时，需要清楚相关字段的含义和类型，此时需要遵循如上强制的类型规则。即stg/ods字段类型可赋值为string，dw数仓模型层建议遵循数据类型规范（主要是金额类的字段）；
Hive支持字符、数值类型，很多情况下项目建表都是默认给的string类型，如果源系统表字段是数值类型，需要做比较（比如取最大最小值），需要做类型转换，字符串的比较规则和数值是不一样的；
类型转换语句:
- cast(source_line_id as BIGINT) -> 转整型， BIGINT长整型，占用8个字节，存储范围-2^63到2^63-1
- cast(source_line_id as DECIMAL(38,6)) -> 带浮点数，带精度
不做类型转换：（根据主键id做order by 取最大最小值会有问题，因为是字符串是从左往右逐个字符比较，有结果就退出比较）
类型转换后的结果：
类型转换后得到想要的数值比较结果；
做金额计算时(加减乘除) 需要转成 decimal(38,8)类型后在做计算，否则计算结果可能有差异；
5.2.4 表的生命周期管理
通过对历史数据的等级划分与对表类型的划分生成相应的生命周期管理矩阵（参考阿里巴巴大数据实践，有生命周期管理的思路即可）。
1) 历史数据等级划分
主要将历史数据划分P0、Pl、P2、P3 四个等级，其具体定义如下：
- P0 ：非常重要的主题域数据和非常重要的应用数据，具有不可恢复性，如交易、日志、集团 KPI 数据、 IPO 关联表。
- Pl ：重要的业务数据和重要的应用数据，具有不可恢复性，如重要的业务产品数据。
- P2 ：重要的业务数据和重要的应用数据，具有可恢复性，如交易线 ETL 产生的中间过程数据。
- P3 ：不重要的业务数据和不重要的应用数据，具有可恢复性，如某些 SNS 产品报表。
2) 表类型划分
下面的几种表类型偏电商业务，一般的项目没有这么多复杂表类型和特殊名词，只需了解即可。借鉴思路，触类旁通。
- 事件型流水表（增量表）
事件型流水表（增量表）指数据无重复或者无主键数据，如日志。
- 事件型镜像表（增量表）
事件型镜像表（增量表）指业务过程性数据，有主键，但是对于同样主键的属性会发生缓慢变化，如交易、订单状态与时间会根据业务发生变更。
- 维表
维表包括维度与维度属性数据，如用户表、商品表。
- Merge 全量表
Merge 全量表包括业务过程性数据或者维表数据。由于数据本身有新增的或者发生状态变更，对于同样主键的数据可能会保留多份，因此可以对这些数据根据主键进行 Merge 操作，主键对应的属性只会保留最新状态，历史状态保留在前一天分区 中。例如，用户表、交易表等都可以进行 Merge 操作。
- ETL 临时表
ETL 临时表是指 ETL 处理过程中产生的临时表数据，一般不建议保留，最多7天。
- TT 临时数据
TT 拉取的数据和 DbSync 产生的临时数据最终会流转到 DS 层，ODS 层数据作为原始数据保留下来，从而使得 TT&DbSync 上游数据成为临时数据。这类数据不建议保留很长时间，生命周期默认设置为 93天，可以根据实际情况适当减少保留天数。
- 普通全量表
很多小业务数据或者产品数据，BI一般是直接全量拉取，这种方式效率快，对存储压力也不是很大，而且表保留很长时间，可以根据历史数据等级确定保留策略。
3) 生命周期管理矩阵
通过上述历史数据等级划分与表类型划分，生成相应的生命周期管理矩阵，如下表所示：
4) 实际项目简化版矩阵
项目上不用完全照搬上图中的模式，非电商类项目的数据场景有所不同，只需借鉴思路即可，简单举例：
| 数据层级 | 分层 | Schema | 表命名 | 存储周期 |
| 数据贴源层 | stg | stg | stg_<原系统简称>_<原系统表名> | 30天（1个月） |
| ods | ods | ods_<原系统简称>_<原系统表名> | 永久保留，定期合并小文件 | |
| 数据公共层 | dwd | dwd | dwd_<主题简称>_<数据内容> | 永久保留，定期合并小文件 |
| dws | dws | dws_<主题简称>_<数据内容> | 永久保留，定期合并小文件 | |
| dim | dim | dim_<主题>_<维度含义> | 永久保留，定期合并小文件 | |
| 数据应用层 | dm | dm | dm_<主题>_<应用说明> | 永久保留，定期合并小文件 |
| 特殊表 | 适用于所有分层 | bak_<目标表名>_<日期> | 按需 | |
| mid_<目标表名>_[0~9] dwm_<目标表名>_[0~9] | 30天（1个月） | |||
| tmp_<自定义表名> test_<自定义表名> | 30天（1个月） | |||
| v_<目标表名> | 视图不存数据，无需清理 | |||
| dq_<模块质检编码>_<目标表> | 30天（1个月） | |||
5.2.5 表分区
说明：数仓大表分区可以加速删除数据和查询
- 在性能可以满足的情况下，尽量不使用数据分区；
- 表的数据在单个实例的数据量在100万级别以下，不需要分区；
- 大表走分区，分区字段一般是使用业务日期字段，按天/月/年等分区；
- 谨慎选择分区表达式，建议最好不要超过100 个分区，一些按天分区的表分区数会超100，注意小文件问题；
- 报表层尽量使用所有的纬度组合作为分区键（分区键当条件可扫描更少的数据行数，提高效率）；
- 报表层数据量比较大（记录数>3kw），尽量使用所有的纬度组合+数据比较分散的字段。
- 日表在日增量超过300W条记录时即可建为分区表，每日记录低于2000W时按照2000W记录为一个分区原则建立分区，否则以一日为一个分区，以方便数据的周期性管理。月表每月记录超过300W即可以1为单位建分区。
- 禁止擅自将分区表变成非分区表，需要通知专职人员修改相关配置表。
4678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
