详解数据仓库开发规范要点

01数据层次的划分

具体仓库的分层情况需要结合业务场景、数据场景、系统场景进行综合考虑，下面我们看一下常见的分层：

ODS：Operational Data Store，操作数据层，在结构上其与源系统的增量或者全量数据基本保持一致。它相当于一个数据准备区，同时又承担着基础数据的记录以及历史变化。其主要作用是把基础数据引入到数仓。

CDM：Common Data Model，公共维度模型层，又细分为DWD和DWS。它的主要作用是完成数据加工与整合、建立一致性的维度、构建可复用的面向分析和统计的明细事实表以及汇总公共粒度的指标。

DWD：Data Warehouse Detail，明细数据层。 DWS：Data Warehouse Summary，汇总数据层。

ADS：Application Data Service，应用数据层。

02数据分类架构

该数据分类架构在ODS层分为三部分：数据准备区、离线数据和准实时数据区。在进入到CDM层后，由以下几部分组成：

公共维度层：基于维度建模理念思想，建立整个企业的一致性维度。

明细粒度事实层：以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。您可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当的冗余，即宽表化处理。

公共汇总粒度事实层：以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段来物理化模型。

03数据划分及命名约定

请根据业务划分数据并约定命名，建议针对业务名称结合数据层次约定相关命名的英文缩写，这样可以给后续数据开发过程中，对项目空间、表、字段等命名做为重要参照。

（1）数据划分

按业务划分：命名时按主要的业务划分，以指导物理模型的划分原则、命名原则及使用的ODS project。

按数据域划分：命名时按照CDM层的数据进行数据域划分，以便有效地对数据进行管理，以及指导数据表的命名。

按业务过程划分：当一个数据域由多个业务过程组成时，命名时可以按业务流程划分。业务过程是从数据分析角度看客观存在的或者抽象的业务行为动作。

（2）命名约定

如果公司业务线比较多，我们可以按照项目的模式进行划分，如果不是直接按照层次划分，project_ods、project_dwd

（3）ODS层命名规范

表命名规范 表命名规则：{层次}{源系统表名}{时间单位与增全量},i表示增量，f表示全量 ,d 表示天, h表示小时

增量数据：{project_name}.s{源系统表名}_di。

全量数据：{project_name}.s{源系统表名}_df。

ODS ETL过程的临时表：{project_name}.tmp{临时表所在过程的输出表}{从0开始的序号}。

按小时同步的增量表：{project_name}.s{源系统表名}_hi。

按小时同步的全量表：{project_name}.s{源系统表名}_hf。

当不同源系统同步到同一个Project下的表命名冲突时，您需要给同步较晚的表名加上源系统的dbname以解决冲突。

字段命名规范  字段默认使用源系统的字段名。

字段名与关键字冲突时，在源字段名后加上_col，即源字段名_col。

同步任务命名规范  任务名：建议和表名保持一致。

（4）dim 层命名规范

命名规则：{project_name}.dim{业务/pub}{维度定义}[_{自定义命名标签}]，其中的pub与具体业务无关，各个业务部都可以共用，例如时间维度。

公共区域维表dim_pub_area

公司社群板块的群成员全量表dim_group_member

（5）dwd  层命名规范

通常需要遵照的命名规范为：dwd_{业务板块/pub}{数据域缩写}{业务过程缩写}[_{自定义表命名标签缩写}] _{单分区增量全量标识}，pub表示数据包括多个业务板块的数据。

单分区增量全量标识通常为：i表示增量，f表示全量。例如：dwd_group_create_inf_df（公司社群创建事实表，日刷新全量）及dwd_group_chat_di（公司社群发消息事实表，日刷新增量）。

（6）dws 层命名规范

公共汇总事实表命名规范：dws_{业务板块缩写/pub}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}。

关于统计实际周期范围缩写，缺省情况下，离线计算应该包括最近一天（_1d），最近N天（_nd）和历史截至当天（_td）三个表。

对于小时表（无论是天刷新还是小时刷新），都用_hh 来表示。

对于分钟表（无论是天刷新还是小时刷新），都用_mm来表示。

举例如下：

dws_group_patient_join_1d（公司社群患者加群一日汇总事实表）

dws_group_patient_exit_td（公司社群患者退群截至当日汇总表）

04层次调用约定

应用层应优先调用公共层数据，必须存在中间层数据，不允许应用层跨过中间层从ODS层重复加工数据。一方面，中间层人员应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他人员提供数据服务。

另一方面，应用层人员也应积极配合中间层人员进行持续的数据公共建设的改造。必须避免出现过度的引用ODS层、不合理的数据复制以及子集合冗余。

ODS层数据不能被应用层任务引用，中间层不能有沉淀的ODS层数据，必须通过CDM层的视图访问。CDM层视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。

CDM层任务的深度不宜过大（建议不超过10层）。

原则上一个计算刷新任务只允许一个输出表。

如果多个任务刷新输出一个表（不同任务插入不同的分区），DataWorks上需要建立一个依赖多个刷新任务的虚拟任务，通常下游应该依赖此虚拟任务。

CDM汇总层应优先调用CDM明细层。在调用可累加类指标计算时，CDM汇总层尽量优先调用已经产出的粗粒度汇总层，以避免大量汇总直接从海量的明细数据层计算。

CDM明细层累计快照事实表优先调用CDM事务型事实表，以保持数据的一致性产出。

避免应用层过度引用和依赖CDM层明细数据，需要针对性地建设好CDM公共汇总层。

05数据类型规范

ODS层的数据类型应基于源系统数据类型转换。例如，源数据为MySQL时的转换规则如下。

MySQL数据类型和Hive数据类型：

MySQL数据类型	Hive 数据类型
TINYINT	TINYINT
SMALLINT/MEDIUMINT	SMALLINT
INTEGER	INT
BIGINT	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL
CHAR/VARCHAR	VARCHAR
LONGTEXT/TEXT	STRING
DATE/TIMESTAMP/TIME/YEAR	STRING
DATETIME	DATETIME

CDM数据公共层如果是引用ODS层数据，则默认使用ODS层字段的数据类型。其衍生加工数据字段按以下标准执行：

金额类及其它小数点数据使用DOUBLE类型。

字符类数据使用STRING类型。

ID类和整形数值使用BIGINT类型。

时间类型数据使用STRING类型（如果有特殊的格式要求，可以选择性使用DATETIME类型）。

状态使用STRING类型。

06公共字段定义规范

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

按天分区：ds(YYYYMMDD)。

按小时分区：hh(00-23)。

按分钟：mi (00-59)。

is_{业务}：表示布尔型数据字段。以Y和N表示，不允许出现空值域。

原则上不需要冗余分区字段。

07数据冗余

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

冗余字段与表中其它字段高频率（大于3个下游应用SQL）同时访问。

冗余字段的引入不应造成其本身的刷新完成时间产生过多后延。

公共层数据不允许字段重复率大于60%的相同粒度数据表冗余，可以选择在原表基础上拓宽或者在下游应用中通过JOIN方式实现。

08数据拆分

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

在物理上划分核心模型和扩展模型，将其字段进行垂直划分。

将访问相关度较高的列在一个表存储，将访问相关度较低的字段分开存储。

将经常用到的Where条件按记录行进行水平切分或者冗余。水平切分可以考虑二级分区手段，以避免多余的数据复制与冗余。

将出现大量空值和零值的统计汇总表，依据其空值和零值分布状况可以做适当的水平和垂直切分，以减少存储和下游的扫描数据量。

09空值处理原则

汇总类指标的空值：空值处理，填充为零。

维度属性值为空：在汇总到对应维度上时，对于无法对应的统计事实，记录行会填充为-99（未知），对应维表会出现一条-99（未知）的记录。

10设计准则

一致性维度规范

公共层的维度表中相同维度属性在不同物理表中的字段名称、数据类型、数据内容必须保持一致。除了以下情况：

在不同的实际物理表中，如果由于维度角色的差异，需要使用其他的名称，其他名称也必须是规范的维度属性的别名。例如，定义一个标准的会员ID时，如果在一个表中，分别要表示买家ID，卖家ID，那么设计规范阶段就预先对会员ID分别定义买家ID和卖家ID。 如果由于历史原因，在暂时不一致的情况下，必须在规范的维度定义一个标准维度属性，不同的物理名也必须是来自标准维度属性的别名。

维度的组合与拆分

对于维度属性过多，涉及源较多的维度表（例如会员表），可以做适当拆分。 数据记录数较大的维度表（例如商品表），可以适当冗余一些子集合，以减少下游扫描数据量。 拆分为核心表和扩展表。核心表相对字段较少，刷新产出时间较早，优先使用。扩展表字段较多，且可以冗余核心表部分字段，刷新产出时间较晚，适合数据分析人员使用。 根据维度属性的业务不相关性，将相关度不大的维度属性拆分为多个物理表存储。 可以根据当天是否有行为，产出一个有活跃行为的相关维表，以减少应用的数据扫描量。 可根据所属业务扫描数据范围大小的不同，进行适当子集合冗余。 将维度所描述业务相关性强的字段在一个物理维表实现。相关性强是指经常需要一起查询或进行报表展现、两个维度属性间是否存在天然的关系等。例如，商品基本属性和所属品牌。 无相关性的维度可以适当考虑杂项维度（例如交易），可以构建一个交易杂项维度收集交易的特殊标记属性、业务分类等信息。也可以将杂项维度退化在事实表中处理，不过容易造成事实表相对庞大，加工处理较为复杂。 所谓的行为维度是经过汇总计算的指标，在下游的应用使用时将其当维度处理。如果有需要，度量指标可以作为行为维度冗余到维度表中。 组合原则。 拆分与冗余。 

11数据存储及生命周期管理规范

CDM公共维度层的表的类型为维度表，存储方式为按天分区。

模型设计者根据自身业务需求设置表的生命周期管理。您可依据3个月内的最大需要访问的跨度设置保留策略，具体计算方式如下：

当3个月内的最大访问跨度小于或等于4天时，建议将保留天数设为7天。 当3个月内的最大访问跨度小于或等于12天时，建议将保留天数设为15天。 当3个月内的最大访问跨度小于或等于30天时， 建议将保留天数设为33天。 当3个月内的最大访问跨度小于或等于90天时，建议将保留天数设为93天。 当3个月内的最大访问跨度小于或等于180天时， 建议将保留天数设为183天。 当3个月内的最大访问跨度小于或等于365天时，建议将保留天数设为368天。 

12总结

其实规范这个东西很重要，但是有时候它的设计不那么可续，例如我们公司的天分区字段是ds而不是pt，但是这个东西只要大家认可就行，但是不能因为不认可就不遵守。

规范其实就是约定，所以需要大家共同去维护和遵守。