数据仓库设计和规范—数仓分层和规范
原创
01ODS层规范
一. 命名规范
通常的命名方式是：ODS_应用系统名(或缩写)_数据库类型_(数据库名称可省略)_数据表名_加载方式（增量还是全量），表名不能太长，一般不超过30字。如：
ods_tmall_mysql_odersys_oder_info_di 或者 ods_tmall_mysql_oder_info_di。
-S 表示实时加载；
-I 表示增量，比如每天增量同步DI，每小时增量同步等HI；
-A 表示全量，比如每天增量同步DA，每小时增量同步等HA；
-H 表示历史表。
二. 抽取规则和策略说明
①．ODS设计
抽取周期 | 日 |
|---|---|
抽取条件 | 每日全量，基于create_time或者是update_time字段进行数据抽取 |
②．数据源和ods的对应
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
phone | 用户手机 | phone | L4 | |
create_time | 创建时间 | create_time |
三. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和ods表名称相同。
02DWD层规范
一. 命名规范
通常的命名方式是：前缀为DWD_主题名（缩写）_加工方式。
_H，按时加工
_D，按日加工
二. 加工规则和策略说明
①．DWD设计
加工周期 | 日 |
|---|---|
加工条件 | 每日增量，基于create_time进行数据加工 |
②．数据源和ods的对应
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
create_time | 创建时间 | create_time | ||
create_day | 创建日期 | create_time | 基于时间戳变为yyyy-MM-dd格式，通常是date_format函数处理 | |
create_month | 创建月份 | create_time | 基于时间戳变为yyyy-MM格式，通常是date_format函数处理 | |
dt | 分区字段 | create_time | 基于时间戳变为yyyy-MM-dd格式，通常是date_format函数处理 |
三. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和表名称相同。
03DWM层规范
一. 命名规范
通常的命名方式是：前缀为DWM_主题名（缩写）_功能描述_加工方式。
二. 加工规则和策略说明
①．DWM设计
加工周期 | 日 |
|---|---|
加工条件 | 每日增量，基于create_time进行数据加工 |
②．数据源和dwd的对应
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
create_time | 创建时间 | create_time | ||
create_day | 创建日期 | create_day | ||
create_month | 创建月份 | create_month | ||
cnt_1d_orders | 订单数 | order_id,create_day | 用户日订单数，基于create_day分组count(oder_id) | |
dt | 分区字段 | create_day |
三. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和表名称相同。
04DWS层规范
一. 命名规范
通常的命名方式是：前缀为DWS_主题名（缩写）_功能描述。从DWD到DWM或者DWS层中，产生临时表或者加工视图，命名规范只是对应层前缀后加_tmp/v，如dws_tmp。 例如：
dws_sales_order_analysis
二. 加工规则和策略说明
①．DWS设计
加工周期 | 日 |
|---|---|
加工条件 | 每日增量，基于create_time进行数据加工 |
②．数据源和dwm的对应, 是基于dwm层数据
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
create_time | 创建时间 | create_time | ||
create_month | 创建月份 | create_month | ||
cnt_1m_orders | 订单数 | cnt_1m_orders | ||
dt | 分区字段 | create_day |
③. 数据源和dwd的对应，是基于dwd层的分析加工
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
create_time | 创建时间 | create_time | ||
create_month | 创建月份 | create_month | ||
mau | 月活跃数 | create_monthuser_id | 基于create_month的count(distinct user_id)去重统计 | |
dt | 分区字段 | create_day |
三. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和表名称相同。
05DWA层规范
一. 命名规范
通常的命名方式是：前缀为DWA_主题名（缩写）_功能描述，如果是视图的话是DWA_V_主题名（缩写）_功能描述。另外如果是直接导出到在线系统侧的数据，尊重在线系统测的命名，并做输出记录，方便数据追溯和业务沟通。
目前公司dwa命名是按照数据集市的方式，采用dm为前缀的方式。
二. 加工说明
①．DWA设计
加工周期 | 日 |
|---|---|
加工条件 | 每日增量，基于分区字段进行数据加工 |
②．数据源和dws层对应，视图方式
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
create_time | 创建时间 | create_time | ||
create_month | 创建月份 | create_month | ||
cnt_1m_orders | 订单数 | cnt_1m_orders |
③. 数据源和dws的对应
三. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和表名称相同。
06DIM层规范
一. 命名规范
通常的命名方式是：前缀为DIM_维表类别(用户，日期，地址，标签)，一般不超过30字。维表通常是一个大宽表，和事实数据配合方便上卷下钻进行分析。通常维表可能需要schema的变更，例如用户画像标签信息表，随着标签内容的增加，描述用户的维度信息增加，所以会基于用户基础信息表，用户画像标签信息表进行迭代加工，这时候应该保留历史数据和对应历史版本，设置保留存活时间TTL。
二. 维表设计开发
1. 维表设计字段冗余，为了使用时字段的全表扫描，采用列存储方式；
1. 针对缓慢变化维，保留历史数据和版本TTL为30天, 方便数据追踪，后续统一使用新的维表。
三. 抽取或者加工规则和策略说明
①．DIM设计
加工周期 | 月 |
|---|---|
加工条件 | 每月更新，或者基于业务需求新增维度字段 |
②．数据源和ods的对应
列名 | 描述 | 来源 | 转换规则 | 安全等级 |
|---|---|---|---|---|
user_id | 用户主键 | user_id | ||
phone | 用户手机 | phone | L4 | |
ID | 用户身份证号码 | id | L4 | |
create_time | 创建时间 | create_time |
四. 加工脚本命名和调度
通常加工脚本，调度任务名称名称和ods表名称相同。
07层次调用规范
一. 背景说明
完成数据仓库的分层后，针对各层次的数据之间的调用关系作出约定。
二. 调用规范
①. DWA应用层优先调用数仓的DWS层数据，通常不允许DWA层跨过DWS层，从DWD层重复加工数据；
②. DWS应该积极了解应用层数据的建设需求，将公用的数据沉淀后，提供数据服务。同时，DWA应用层也需积极配合DWS层进行持续的数据公共建设的改造。避免出现过度的DWD层引用、不合理的数据复制和子集合冗余。
总体遵循的层次调用原则如下：
- ODS层数据不能直接被应用层任务引用。如果DWD和DWS层没有沉淀的ODS层数据，则通过ODS层创建视图的方式访问。命名规范遵从DWD或者DWS的命名规范，视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。
- 从ODS到DWS层任务的深度不宜过大（建议不超过10层，包含临时表加工）。
- 一个计算刷新任务只允许一个输出表，特殊情况除外。
- DWM汇总层优先调用DWD明细层，可累加指标计算。DWM汇总层尽量优先调用已经产出的粗粒度汇总层，避免大量汇总层数据直接从海量的明细数据层中计算得出。
- 有针对性地建设DWM公共汇总层，避免应用层过度引用和依赖DWD层明细数据。
08规范执行说明
1. 主要是三个规范文档： 1.1 需求分析规范 1.2 数仓构建流程
1.2 层级转换设计规范 1.4.1 ODS层规范
1.3 指标沟通和开发规范 3.1 指标说明规范
2. 主要应用和风险
2.1 数据分层结合实际，公司已有的数仓开发，那些可以执行，那些需要变更执行，执行周期和对应迭代 。
层级 | 现状 | 后期 | 数据库命名规范 | 表命名规范 | 示例 | 执行类型 | 备注 |
|---|---|---|---|---|---|---|---|
dim | dim | dim | DIM_维表类别(用户，日期，地址，标签) | dim_user_infodim_user_profile | |||
ods | ods | ods如果业务库比较多，则ods_应用系统名称或数据库名称_数据库类型(可省略)， 不能归类的，则统称为ods | 表，脚本，etl任务命名：ODS_数据表名_加载方式（增量还是全量） | ods_tmall_mysql_ordersys_oder_info_di 或ods_tmall_mysql_order_info_di | 新同步表 | 对应规范文档开发时针对表字段很多列时工作量大 | |
dwd | dwd | dwd | dwd如果业务应用，数据分析场景比较多，则dwd_业务应用/主题， 不能归类的，则统称为dwd | 表，脚本，任务命名：DWD_主题名（或缩写）_加工方式 | dwd_sales_di | 新加工表 | 对应规范文档开发表字段很多列，字段转换规则复杂时时工作量大日分区： dt = yyyy-MM-dd |
dws | dws | dws | dws如果业务应用，数据分析场景比较多，则dws_业务应用/主题， 不能归类的，则统称为dws | 表，脚本，任务命名：DWS_主题名（缩写）_功能描述 | dws_sales_order_analysis | 新加工表 | 对应规范文档开发表字段很多列，字段转换规则复杂时时工作量大 |
dwa | dm | dm | dm如果业务应用，数据分析场景比较多，则dm_业务应用/主题， 不能归类的，则统称为dm | 表，脚本，任务命名：dm_主题名（缩写）_功能描述 | dm_sales_order_analysis_result_bi | 新加工表 | |
dwa | 表，脚本，任务命名：DWA_主题名（缩写）_功能描述 | dwa_sales_order_analysis_result_export | 新加工表 |
说明：考虑变动风险，目前库名称不变，现在分层命名不变，后续新表名和对应分析需求变为本次规范。
2.2 需求规范，和目前保持一致, 示例如下：
分析主题 | 销售情况分析 |
|---|---|
维度 | 时间，地区，产品类型 |
维度层级 | 时间：年-月-日， 最小粒度日 |
地区：省-市，最小粒度市 | |
产品类型：天猫，淘宝，聚划算 | |
分析指标 | DAU，订单数，支付金额 |
数据来源 | 用户日志埋点表，用户订单支付表 |
业务对象 | 数据大盘，APP运营，高层报告 |
备注 |
2.3 指标说明规范, 和目前一致，增加对应指标加工变更的版本保存，方便回溯或者追源。
指标说明 | |
|---|---|
英文名称 | |
中文名称 | |
指标描述 | |
计算逻辑 | |
值说明 | |
指标调度周期 | |
指标示例 | |
3. 规范发展
3.1 规范的完善和补充更改；
3.2 从无规范到文档规范（不好检索），文档规范到使用技术产品解决规范。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
