数仓模型规范设计

模型架构设计

数仓架构一般从宏观上分为三层：操作数据层ODS、公共维度模型层CDM和数据应用层ADS。其中CDM又包含明细数据层DWD、汇总数据层DWS，维度层DIM、根据生产经验这里可在加入数据临时层TMP。架构图如下：
在这里插入图片描述

ODS
把操作系统的数据几乎无处理地存放在数据仓库系统中。

支持历史数据回溯
表结构、数据类型和数据源保持一致
可以过滤敏感字段
不同业务类型的表可采取不同分区策略

DWD
按业务概念组织细节数据，并进行名称、代码等标准化处理。

完成数据标准化和清洗，包括命名规范、算法统一
维表退化到事实表，减少事实表和维度表的关联
构建明细宽表，提高明细表的易用性

DWS
根据应用的高层和产品指标要求，构造通用粒度汇总指标表。

采用宽表化手段构建公共汇总层，提升指标复用性，减少重复计算

DIM
维度是对具体分析对象的分析角度，维度要具备丰富的属性，历史信息的可追溯性，对通用的维表要保持一致性。
TMP
用来降低加工过程计算难度，提高运行效率的临时表层。
ADS
这一层跟业务系统强相关（如各种BI、画像平台），可由业务方直接开发，也可由数仓团队开发，内容跟系统需求导向的。此层不作为数仓核心分层架构。

基本原则

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

公共规范

层次调用约定
应用数据层应优先调用聚合数据层及通用数据层数据，优先使用已产出的粗粒度汇总数据，不允许应用数据层跨过中间层从ODS层重复加工数据。
一方面，数仓团队应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他团队提供数据服务；另一方面，应用数据层开发时也应积极配合数仓团队进行持续的数据公共建设的改造。必须避免出现过度的引用ODS层、不合理的数据复制以及子集合冗余。

DW层(基础数据层及通用数据层)任务的深度不宜过大。
聚合数据层(DWS)应优先调用通用数据层(DWD)。在可累加类指标计算时，聚合数据层(DWS)尽量优先使用已经产出的同层粗粒度汇总数据，以避免大量汇总直接从海量的基础数据层计算。
基础数据层累计快照事实表优先调用同层事务型事实表，以保持数据的一致性产出。
避免应用层过度引用和依赖基础数据层明细数据，需要针对性地建设好DW公共汇总层。

数据冗余
宽表冗余维度属性时，应该遵循以下建议原则：

冗余字段与表中其它字段高频率同时访问。
冗余字段的引入不应造成任务本身的完成时间产生过多后延。
DW层(基础明细层、数据汇总层)数据不允许字段重复率大于60%的相同粒度数据表冗余，可以选择在原表基础上拓宽或者在下游应用中通过JOIN方式实现。

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

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

空值处理原则
【建议】

事实表的空值：空值处理，填充为零。因为数据库中null值对常用数字型字段的SQL过滤条件都不生效，比如大于、小于、等于。
维度属性值为空：在汇总到对应维度上时，对于无法对应的统计事实，记录行会填充为-99（未知），对应维表会出现一条-99（未知）的记录。

ODS规范设计

设计原则
【必须】一个源表只允许同步一次到数据仓库。
命名规范
（1）表命名规范
[层级][源库][源表]{业务标识}[刷新周期标识][单分区增量全量标识]
如：ods_netroam_misim_order_di
•ods表示层级
•netroam表示业务库名
•misim_order表示业务表名
•d表示刷新周期为按天更新(ODS层原则上只有按天分区数据)
h:小时/d:天/w:周/m:月
•i表示单分区数据为增量(f表示全量)

【必须】如来自不同业务系统表名产生冲突时，在[同步策略]之前增加{业务标识}来区分

【建议】如接入分库分表数据时，ODS表中增加source_ip/source_db/source_tb字段以示区分，便于数据校验及问题定位

（2）字段命名规范
【必须】字段默认与源系统字段名保持一致
【建议】如与数仓关键字产生冲突，如etl_tm,start_day,end_day等时，字段增加后缀_dup(duplicate)区分
生命周期规范
（1）对于业务库接入数据

数据表类型	存储方式	最长存储保留策略
全量	按天分区	ODS层数据默认保存30天，支持数据问题回溯
增量	按天分区	ODS层数据默认保存30天，支持数据问题回溯

（2）对于互联网日志数据

数据表类型	存储方式	最长存储保留策略
业务自打点且已落地工场	–	ODS层不冗余存储，直接使用业务日志作为ODS表
onetrack日志	按天分区	ODS层不冗余存储，直接使用tracking库作为ODS层，互联网业务日志TTL默认180天

数据质量规范
•【必须】业务数据ODS表必须配置主键唯一性监控(主键是后续拉链处理的关键)
•【必须】ODS表必须进行分区空数据监控
•【必须】ODS表所有字段均必须有注释
•【建议】ODS表的记录数设置上周同比无变化监控，用于监控源系统是否下线或者已迁移

DWD层规范设计

命名规范
（1）表命名规范
[DWD/DWS][主题缩写][业务][业务流程缩写/表内容描述][模型设计方式]
模型设计方式：流水表为{刷新周期标识}{单分区增量全量标识}，，拉链表为chain
如：dwd_event_ott_mitv_log_di/dwm_ot_device_active_chain
•dwd表示层级
•event表示主题
•ott表示业务
•mitv_log表示具体业务信息
•chain表示模型设计方式为拉链
（2）字段命名规范
这里可以维护一套词根表，让尽量让所有字段命名都统一。
生命周期规范
互联网业务日志TTL默认180天。

DIM层规范设计

设计原则
【必须】维度表中相同维度属性在不同物理表中的字段名称、数据类型、数据内容必须保持一致;
【建议】维度建模一般推荐将维度的属性层次合并至单个维度(反规范化)，换取简明性和查询性能；
维表的设计常见如下两种方式：
•全量快照
方式：每天保留一份全量快照数据；
优点：开发维护成本低，使用方便，易于理解；
缺点：存储冗余，未发生变化的记录需要每日冗余存储；
注意：避免过度使用这种方法，且必须要有对应的TTL机制，清除无用历史数据；
•历史拉链
方式：通过新增开闭链日期，以天为粒度记录数据变更，支持快速还原任意天的历史快照；
优点：极大地压缩了全量存储的成本
弊端：提高了数据使用门槛与解释成本
注意：对于频繁变化的属性，需要合理的进行水平或垂直拆分，保持核心维度的相对稳定；
命名规范
（1）表命名规范
DIM_{业务缩写}_{维度定义}
如：dim_dept_user
•dim表示层级
dept_user表示维度定义，部门用户
（2）字段命名规范
这里可以维护一套词根表，让尽量让所有字段命名都统一。
生命周期规范
历史拉链方式设计的维表默认无TTL约束，视实际场景合理设置；
全量快照方式设计的维表TTL建议为7天，视实际场景合理调整；

字段类型规范

数据仓库中的字段长度尽量满足相应源系统字段中最大长度的要求，当然也会考虑字段的业务含义，对于一些源系统定义过长，而从实际业务含义又不可能有那么长的字段，由仓库自行选择一个合适的长度定义；为了尽可能的保持仓库中数据类型的一致性以及规范性，数据仓库中的数据类型定义不宜过杂，建议只定义string、bigint、double类型，使得仓库中的字段类型保持整齐。

日期类型字段由于格式多样，造成在信息加工处理过程中的格式转换复杂且易出错，因此对日期类型字段统一制定如下规范。以"2020-01-01 12:57:45.233"为例说明日期字段类型规范内容：

在这里插入图片描述

分区定义规范

分区致力于解决支持大表和索引的关键问题，一旦分区被定义，SQL语句就可以访问的操作某一个分区而不是整个表，因而提高管理的效率。分区对于数据仓库应用程序非常有效，因为他们常常存储和分析巨量的历史数据，对于HIVE，分区实际就是对应HDFS文件系统上的的独立的文件夹，该文件夹下是该分区所有数据文件。在分区设计中通常需要从数据存储与数据访问角度考虑。以下为常用表设计的分区定义。

流水表：流水表通常的加载或加工方式为增量，在使用时也经常按天的方式进行应用，通常设计date=数据日期作为分区。
拉链表：拉链表设计目前采用单级分区设计，end_day=[数据日期，20991231]
全量快照表：全量快照表通常设计一个date=数据日期分区，一个分区代表该天全量数据快照情况。
增量快照表：全量快照表对于使用来说非常方便，但是针对于数据量巨大的表来说，这种设计通常存储巨大，访问非常慢，然而在使用时也并非需要全量的数据，这时可考虑设计为增量快照表，date=数据日期为当日修改的数据。由于一笔业务数据可能多次修改，也就是说会分布在多个dt分区，使用时需要结合业务日期使用或去重使用。
归档表：对于数据量巨大表的另外一种设计方式则为归档表，归档表则将某个时间以前的数据放置到归档分区，归档日期依据业务进行定义。归档一般使用二级分区方式，例如全量快照和拉链表最新快照，配合规范可设计为[dp=ACTIVE/HISTORY, date=数据日期]或[dp=ACTIVE/HISTORY, end_day=20991231/数据日期]