1.数据仓库模型设计规范

数据仓库模型设计规范

1. 前言
   1. 背景及目的

数梦工场主要面向政府部门进行数据的清洗、整合以及应用。政府各部门拥有大量的、凌乱的、互不相通的数据。在当前政务大数据的火热背景之下，建立一个统一的数据仓库显得尤为重要，并且解决以下几个重点问题：

1. 统一的架构体系，保障其设计理念、处理方法、命名标准在不同团队中保持一致，在不同时间阶段保持连续性。
2. 统一的字段口径，保证不同部门之间数据逻辑的统一。
3. 实现数据互通，不同来源的数据统一整合，通过主键相互连接。
4. 构建一个可重复利用的基础数据层，提高数据处理效率。避免了烟囱式的面向应用的数据开发模式和组织方式造成了大量的数据冗余，减少重复开发。
5. 便于不同团队之间沟通与维护。
   1. 适用范围

本规范主要用于数据仓库建设的数据模型设计和维护，所有参与数据仓库建设的数据模型师、ETL开发工程师都需要重点熟悉本规范约定，并在模型设计、开发、维护过程中保障其落实到位。

1. 1. 专业术语解释
2. DW：数据仓库。
3. ODPS：大数据计算服务ODPS（Open Data Processing Service）是由阿里云自主研发，提供针对TB/PB级数据、实时性要求不高的分布式处理能力。
4. ODS：操作数据存储ODS（Operational Data Store），具备数据仓库的部分特征和OLTP系统的部分特征，它是“面向主题的、集成的、当前或接近当前的、不断变化的”数据。

1. 数据仓库数据模型架构
   1. 设计目标
      1. 业务目标

将基础数据作为一个基础公共服务，为数据应用和产品提供公共数据服务，帮助数据应用或产品提升获取数据的效率，降低数据加工的深度和复杂度；提升各个产品和应用间数据的一致性。主要包括以下几方面的内容：

1. 将业务系统数据快速同步进入到ODPS，建立统一、一致、唯一的ODS数据层。降低业务方获取数据成本，数据使用方只需要在单一平台（ODPS）上获取数据。
2. 实现公共数据业务逻辑的加工和转换。
3. 实现公共维度下的公共数据指标，为上层数据应用服务。
   1. 1. 技术目标

在满足业务目标的同时，在数据模型设计上，重点以下目标作为设计时的考量：

1. 成本：模型设计者必须平衡性能和成本要素对数据模型的影响，尤其在海量数据情况下，在保障业务和性能的前提下，应该使用合理的数据模型方案和存储策略，尽量消除过度的数据复制与冗余。
2. 性能：模型设计者需要兼顾模型刷新性能开销、产出时间和访问性能。
3. 数据一致性及数据互通：各个数据模型或者数据表之间保障数据输出的一致性，相同粒度的相同数据项（指标、维度）保持在命名、描述、内容、结构的相对一致性，不同算法的数据项应该显示的通过命名和描述显性化区分。
4. 数据质量：数据仓库模型需要尽可能屏蔽源头垃圾数据源，一方面要保障数据本身的高质量，减少数据缺失、错误、异常等情况的发生；另一方面需要保障其对应的业务元数据的高质量，数据有明确的业务含义，为数据使用者提供正确的指引。
5. 易用：在保障以上目标的前提下，数据用户能从业务角度出发快速找到所需数据；能较快的掌握模型的适用场景和使用方法；能相对便捷获取数据。但是，在目标出现冲突时，在数据仓库数据模型并不完全承载用户使用数据的易用性目标要求，数据消费服务产品和数据应用可以提升数据使用的易用性。
   1. 数据仓库的用户

1. ETL数据研发人员：ETL开发人员使用数据仓库数据实现数据应用和产品的数据需求。
2. BI数据分析人员：BI数据分析人员可以使用数据仓库数据完成自助取数和自助分析。
3. 公共数据消费产品与服务：基于数据仓库的数据将会以OPEN API，指标小库等数据产品或者服务提供给下游应用。
   1. 数据仓库架构

数据仓库的整体架构如下图所示，将数据划分为四个层级：

1. ODS层（接口层）：是生产系统数据源的直接拷贝，由ETL过程对数据源进行直接抽取。
2. DWD层（细节数据层）：是数据仓库的细节数据层，将数据按照主题的形式存放。
3. DWS层（轻度综合层）:是对DWD层的数据进行轻度综合和汇总统计，面向分析性应用进行细粒度的统计和沉淀。
4. ADS层（集市层）:将数据按照分析的专题组织成多为库表的形式存放，主要来源于ODS和MID层。

1. 1. 数仓设计原则
2. 高内聚和低耦合：一个逻辑和物理模型由哪些记录和字段组成，应该遵循最基本的软件设计方法论的高内聚和低耦合原则。主要从数据业务特性和访问特性两个角度来考虑：将业务相近或者相关的数据、粒度相同数据设计为一个逻辑或者物理模型；将高概率同时访问的数据放一起，将低概率同时访问的数据分开存储。
3. 核心模型与扩展模型分离：建立核心模型与扩展模型体系，核心模型包括的字段支持常用核心的业务，扩展模型包括的字段支持个性化或是少量应用的需要，必要时让核心模型与扩展模型做关联，不能让扩展字段过度侵入核心模型，破坏了核心模型的架构简洁性与可维护性。
4. 公共处理逻辑下沉及单一：越是底层公用的处理逻辑更应该在数据调度依赖的底层进行封装与实现，不要让公共的处理逻辑暴露给应用层实现，不要让公共逻辑在多处同时存在。
5. 成本与性能平衡：适当的数据冗余换取查询和刷新性能，不宜过度冗余与数据复制。
6. 数据可回滚：处理逻辑不变，在不同时间多次运行数据结果确定不变。
7. 一致性：相同的字段在不同表字段名相同。
8. 命名清晰可理解：表命名规范需清晰、一致，表名需易于下游理解和使用。
   1. 数据类型规范

Mysql数据类型	Odps数据类型
TINYINT/SMALLINT/ MEDIUMINT/ INTEGER / BIGINT	Bigint
FLOAT / DOUBLE / DECIMAL	Double
LONGTEXT /TEXT/VARCHAR/ CHAR	String
DATE/ DATETIME	String(格式：YYYY-MM-DD HI24:MM:SS)

Oracle数据类型	Odps数据类型
Number	ID转换为bigint，根据实际数据，如果是浮点数则使用double，默认使用bigint。
VARCHAR2/VARCHAR	String
DATE	String
CLOB	String

数据仓库如果是引用ODS层数据，默认使用ODS层字段数据类型；衍生加工数据字段按以下标准执行：

1. 金额类及其它小数点数据：double
2. 字符类数据：string
3. ID类：bigint
4. 时间类型数据：string(如果有特殊的格式强制要求，可以选择性使用Datetime)
5. 状态：string
   1. 数据仓库公共字段定义

1. 数据统计日期分区字段

• 按天分区：ds(YYYYMMDD)
• 按小时分区：hh( 00-23)
• 按分钟：mi (00-59)

1. dw_status：数据状态，I,D,U分别对应增加、删除和修改
2. is_{业务}：表示布尔型数据字段。”Y”,”N”表示，不允许出现空值域。
3. 原则上不需要冗余分区字段
   1. 数据冗余
      1. 相关数据冗余

一个表做宽表化处理时冗余一些相关维度属性，应该遵循以下几个建议准则：

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

从一个集合中冗余一部分记录作为另外一张表存在时，可以优先考虑子分区方式，但是多级子分区不超过（5级），只有以下情况才考虑冗余：

1. 子类型表有较多（大于10）个字段父类型表并不存在。
2. 子集合的过滤条件被多次（大于5次）应用。
   1. 数据拆分

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

1. 在物理上划分核心模型和扩展模型，将其字段进行垂直划分；
2. 将访问相关度较高的列在一个表存储，将访问相关度较低相关的字段分开存储；
3. 将经常用到的where条件按记录行进行水平切分或者冗余；
4. 将表中出现大量空值、过度离散表，做适当的水平和垂直切分；
   1. 空值处理原则

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

1. ODS模型设计规范
   1. 数据同步及处理规范
      1. 数据同步方式

1. 数据同步主要依靠BASE的数据同步节点，支持全量同步以及增量同步。
2. 一个系统的源系统表只允许同步一次到ODPS。
3. 支持MYSQL、ORACLE、HBASE、……（需补充）
4. 文本文件可通过shell节点载入
   1. 1. 数据加载和处理

1. 同步的数据直接进入ODS层表的当日分区。
2. 所有ODS层的表都以统计日期时间分区表方式存储，数据成本方由存储管理和策略部分控制和管理。
3. 自适应处理源系统字段变更，具体策略是：如果源系统的字段在ODPS目标表不存在，由同步中心自动添加字段；如果目标表的字段在源系统中不存在，同步中心填充NULL。
4. 去重增量数据表(T+1)需要获取的分区跨度是T-1的23:45到T+1的00:15(比如s_tc_biz_order_delta刷新ds=20140425这个分区，依赖的数据是源头表s_tt_tc_biz_order_tt4的ds=20140423/hh=23/mm=45到ds=20140425/hh=00/mm=15这些分区的所有数据)。
5. 去重增量数据表(小时级)需要获取的分区跨度是T-1的23:45开始，截止到当前的所有数据(比如s_tc_biz_order_delta_hh刷新ds=20140424/hh=04这个分区，依赖的数据是源头表s_tt_tc_biz_order_tt4的ds=20140423/hh=23/mm=45到ds=20140424/hh=05/mm=15这些分区的所有数据)。
   1. 1. 数据清洗

ODS层不做处理，按原始结构导入。

1. 1. 命名规范
      1. 表命名规范
2. 非去重增量数据：{project_name}.s_{源系统表名}_deltas
3. 增量数据：{project_name}.s_{源系统表名}_delta
4. 全量数据:  {project_name}.s_{源系统表名}
5. ODS ETL过程的临时表：{project_name}.t_{临时表所在过程的输出表}_{从0开始的序号}
6. 准实时数据命名：

按小时的增量表：{project_name}.s_{源系统表名}_{delta}_{h/m}

按小时的全量表：{project_name}.s_{源系统表名}_{h/m}

1. 当从不同源系统同步到一个project下表命名冲突时，后进来的表的命名加上源系统的dbname。
   1. 1. 字段命名规范

1. 字段沿用源系统字段名称
2. 字段名与ODPS关键字冲突时处理规则：加一个”_col”后缀，即：源字段名_col
3. ODS层可以添加以下DW特殊字段
   • ds：YYYYMMDD格式，数据统计日期分区字段
   • dw_status：数据状态，I,D,U分别对应增加、删除和修改（非必须，如果有记录需求才使用）
   • dw_quality_tag:数据仓库质量标记
   • dw_ins_date:数据插入时间
     1. 同步任务命名规范
4. 任务名：dc_{源系统表名}[_delta]，同一project下异库同名表的任务名：dc_{源系统表名}_{tddl的appname}[_delta]
5. 任务的output: 跟3.2.1的表命名保持一致
   1. 数据存储及生命周期管理规范

创建表之前必须根据需求确认表的生命周期并给表和字段加上完整的注释。

1. 1. 数据质量规范
2. 每个ODS全量表必须配置唯一性字段标识。
3. 每个ODS全量表必须做分区空数据监控。
4. 建议对重要表的重要枚举类型字段做枚举值变化、及枚举值分布监控。
5. 建议对ODS表设置数据量及数据记录数做上周同比无变化监控，用于监控源系统下线或者已迁移。
6. 只有有监控要求的表才创建数据质量管控层，视图层任务后挂一个DQC检测，防止错误的dirty过滤，在出现dirty过多时阻断报警。
7. 每个ODS层全表都必须要有注释，由数据质量团队推动前台业务系统执行此规范。

1. DWD模型设计规范

根据业务情况，对ODS层的数据进行清洗、过滤、记历史等操作，同时将各个专业数据进行集中，按主题的形式将数据存放。属于数据的公共层建设。

1. 1. 事实表建立

数据仓库中有三种基本类型的事实表：事务型事实表，周期快照事实表，累计快照事实表。事务事实表与周期快照事实表、累积快照事实表使用相同的一致性维度，但是它们在描述业务事实方面是有着非常大的差异的。

1. 1. 1. 事务型事实表

事务事实表记录的事务层面的事实，保存的是最原子的数据，也称“原子事实表”。事务事实表中的数据在事务事件发生后产生，数据的粒度通常是每个事务一条记录。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。

事务型事实表主要用于分析行为，追踪事件。事务事实表获取业务过程中的事件或者行为细节，通过事实与维度之间关联，可以非常方便统计各种事件相关的度量，比如交易支付金额，浏览UV，搜索次数等等。

1. 基于数据应用需求的分析，如果下游存在较大针对某个业务过程事件的分析指标需求，可以考虑基于某一个事件过程构建事务型事实表。
2. 事务型事实表一般选用事件发生日期或者时间作为分区字段，可以非常方便下游作业数据扫描执行分区裁剪。

1. 明细层事实表的冗余子集的原则：有利于降低上层数据访问的IO开销

明细层事实表维度退化到事实表原则：有利于明显减少上层数据访问的JOIN成本。

1. 1. 1. 周期快照事实表

周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，时间间隔如每天、每月、每年等等。周期快照事实表的粒度是每个时间段一条记录，通常比事务事实表的粒度要粗，是在事务事实表之上建立的聚集表。事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。

1. 1. 1. 累积快照事实表

累计型快照事实表主要用于分析事件之间的时间间隔与周期，比如交易的支付与发货间隔，分析发货速度，支付和退款环节，分析支付退款率等等；同时也可以用于少量的、且对刷新时间不是非常敏感的指标统计需要，比如交易的关闭和发货，在当前事务型事实表不支持，且只有少量的统计指标，可以基于累计快照事实表计算。

1. 1. 命名规范

{project_name}.dwd_{业务BU缩写}_{数据域缩写}_{业务过程缩写}[_{自定义表命名标签缩写}]_{统计时间周期范围缩写}，例如：

1. tbcdm.dwd_tb_td_ordcrt_trip_1d（淘宝航旅机票订单下单事实表，每日增量）
2. tbcdm.dwd_tb_td_order_td（截至淘宝交易全流程事实表，每日全量）
   1. 数据质量规范

1. 空值处理
2. 小数精度控制
3. 枚举值域处理
4. 参照完整性要求
5. 唯一性要求

1. DWS模型设计规范

DWS层是DWD层和ADS层之间的一个过渡层，是对DWD层的数据进行轻度的汇总和统计，是面向分析性应用进行细粒度的统计和沉淀。通常是星形或雪花结构的数据。从数据粒度来说，这层的数据是轻度汇总级的数据，已经不存在明细数据了。从数据的时间跨度来说，通常是DWD层的一部分，主要的目的是为了满足用户分析的需求，而从分析的角度来说，用户通常只需要分析近几年（如近三年的数据）的即可。从数据的广度来说，仍然覆盖了所有业务数据。

1. 1. 命名规范

{project_name}.dws_{业务BU缩写}_{数据粒度缩写}_{数据域缩写}[_{自定义表命名标签缩写}]_{统计时间周期范围缩写}。例如：

1. tbcdm.dws_tb_byr_trd_subpay_1d (淘宝买家粒度交易分阶段付款日汇总事实表)
2. tbcdm.dws_tb_byr_trd_cod_xd (淘宝买家粒度货到付款交易31日内汇总事实表)
3. tbcdm.dws_tb_slr_prd_td (淘宝卖家粒度商品截至当日存量汇总表)