数仓建模与规范

一、数仓基本概念

1、数据仓库定义

数据仓库定义有很多，我主要从它的应用和目的来说一下我的理解，数据仓库主要应用于OLAP（联机分析处理），这里的重点是分析，那分析什么呢？

传统的数据库主要应用是OLTP（联机事务处理），用来记录某类业务事件的发生，比如淘宝的购买商品中有下订单、支付等步骤，而这些操作都会被作为一条记录存在数据库中，会有增删改各种操作；

当上面的行为数据积累到一定的时间之后，会有一些业务需要对以往的数据进行总结分析得出分析的结果，根据这个结果做出一定的决策或者营销，这个就是分析的过程；

此时又会诞生新的问题，数据库也支持查询分析啊，为什么要用到数据仓库呢？是的，单纯的某个业务的分析，某个业务的数据库就可以做到，但是不同业务的数据分散在不同的业务系统中，所以此时要把所有的业务数据整合到数据仓库中，然后在数据仓库中来提供OLAP分析。

2、维度建模

维度建模重点解决用户如何更快速完成分析需求，同时还有较好的大规模复杂查询的响应性能；

事实表：对分析主题的度量。

维度表：对分析主题所属类型的描述。

示例：张三昨天在南京新街口花了100元买了一件衣服；

我们从下面的图可以看出事实表存储的就是记录一件已经发生的事，而维度表存储的就是发生的这件事中所涉及的各个类型；

星型模型：所有维度表与事实表直接关联，维度表之间没有关联，上面的例子就是一个标准的星型模型建模；

雪花模型：雪花模型是对星型模型的扩展，维表可向外连接子维表，如下面这个建模方式，事实表记录如果想知道城市名称和省份名称得通过地点表与城市表和省份表进行嵌套关联才能获取；

优缺点分析：星型模型的维表相对于雪花模型要更占空间，不满足范式设计，数据冗余多，但是由于不需要多层关联查询，查询分析效率快，然后数仓设计中数据冗余问题并不严重，更注重提高查询分析效率，以及降低开发难度，所以大部分的数仓建模采取的都是星型模型，简单高效；

3、数仓模型设计

下面是现在数仓行业标准化的分层架构，可能不同的公司会有对于每一层会有不一样的名字，但是大致都是分为这四层：ODS、DWD、DWS、ADS

3.1、数仓完善度、复用性、规范性

那什么才是一个好的数仓模型设计呢？⼀个理想的数仓模型设计应该具备的因素，是“数据模型可复⽤，完善且规范”，这样说太过于抽象了，我们通过下图几个指标去量化一个数仓模型设计的好坏；

1、完善度

DWD层完善度：衡量DWD层是否完善，最好看ODS层有多少表被DWS/ADS/DM层引⽤。因为DWD以上的层引⽤的越多，就说明越多的任务是基于原始数据进⾏深度聚合计算的，明细数据没有积累，⽆法被复⽤， 数据清洗、格式化、集成存在重复开发。

跨层引⽤率：ODS层直接被DWS/ADS/DM层引⽤的表，占所有ODS层表（仅统计活跃表）⽐例。

跨层引⽤率越低越好，在数据中台模型设计规范中，要求不允许出现跨层引⽤，ODS层数据只能被DWD引⽤。

DWS/ADS/DM层完善度：考核汇总数据的完善度，主要看汇总数据能直接满⾜多少查询需求（也就是⽤汇总层数据的查询⽐例衡量）。如果汇总数据⽆法满⾜需求，使⽤数据的⼈就必须使⽤明细数据，甚⾄是原始数据。

汇总数据查询比例：DWS/ADS/DM层的查询占所有查询的比例。

汇总数据查询比例不像跨层引用率，做不到百分百，但是值越高说明数仓上层模型的建设越完善；

2、复用性

数据中台模型设计的核⼼是追求模型的复⽤和共享，通过元数据中⼼的数据⾎缘图，可以看到，⼀个⽐较差的模型设计，⾃下⽽上是⼀条线。⽽⼀个理想的模型设计，它应该是交织的发散型结构；

模型引用系数：一个模型被读取，直接产生下一个模型的平均数量，比如DWD层，一个表被DWS中5个表使用到，引用次数为5，DWD层的总引用次数/DWD表总数 就是平均引用系数，这个值一般低于2就代表敷用性比较差，大于3代表好。

3、规范性

“无规矩，不成方圆”，在数仓中，我理解规范是最重要的，因为数仓的建设会涉及很多人，如果我们的表命名、表设计都没有规范，那别人在使用这张表的时候根本不知道这个表有什么用、该怎么用，表生成的逻辑是什么，这些都不知道；这样的数仓就会产生很多重复模型，最终只会被遗弃。

下面在第四章会介绍我前公司的数仓建设规范案例

4、数仓建设案例

1、分层规范

序号	分层	分层标记	分层标记缩写	物理表	备注
1	基础层	BDS	BDS	BDS	basic data storage。物理表沿用当前命名情况。
2	公共层	DWD	DWD	DWD	data warehouse detail公共数据明细层
3		DWA	DWA	DWA	data warehouse aggregate data公共数据汇总层/应用层
4	萃取层	ESD	ESD	ESD	ESD Extraction Subject-oriented Data 萃取主题数据
5	码表数据层	DIM	DIM	DIM	码表：省份编码、产品编码。

2、分域规范（DWD、DWA）

主题域	代码
客户域	CUS
产品域	PRD
市场营销域	MRT
事件域	EVT
业务使用域	USE（B域）/IA（上网日志）
账务域	ACC
合作伙伴域	PRT
资源域	RES
公共域	PUB
企业管理域	BUS

3、萃取层（ESD）分域

序号	主题域	标记	标记缩写
1	自然人视图	natural person view	NPV
2	政企视图	government enterprise view	GEV
3	产品视图	product view	PRV
4	渠道视图	channel view	CHV
5	物联网视图	Internet of Things(iot) view	IOV
6	终端视图	terminal view	TEV
7	家庭视图	home view	HOV
8	基站视图	base station view	BSV
9	小区/楼宇视图	building view	BUV

4、周期规范

周期类型	代码
年	Y（Year）
季	Q（Quarter）
月	M（Month）
周	W（Week）
日	D（Day）
小时	H(Hour)
分钟	MI(Minute)
实时	T(Time)

5、业务分类规范

业务类型	代码
全网	AL
移动业务(省B)	MB
固话业务	FX
互联网业务	BB
cBSS业务	CB
移网业务业务（省B、cBSS）	NM

6、表类型规范

表类型	代码	备注
全量表	ALL	表中无分区，每天都是数据的最新状态，不建议使用
增量表	INC（INCREASE）	每日新增一个日分区，新增的数据插入新的分区中，适合无更新操作表
快照表（分区全量表）	SNA（SNAPSHOT）	快照表就是我们平常说的日全表、月全表，根据分区更新策略，每日（每个分区）存储截止T-1日的全量表数据，缺点：冗余数据多，浪费空间，优点：操作简单，逻辑简单，可维护性高，可以保存一定周期的历史数据，一般全表数据量不太大时建议使用。
拉链表	HIS（HISTORY）	表中会有start_date和end_data两个分区字段，end_date等于‘9999-12-31’为最新数据信息，表中每日只会增加更新的记录条数，当使用最新数据时通过end_date字段即可，也有历史数据分析，不像日全表每日都会成倍的增长数据量，但是设计逻辑复杂，同步逻辑复杂， 需要通过中间表进行转换，适合单表数据量特别大以至于数仓无法承受这么大的冗余数据时操作。

快照表示例：

user_id	name	age	phone	month	day
user_1	张三	20	18811112222	2021-09	10
user_2	李四	25	18800006666	2021-09	10
user_1	张三	20	18805642121	2021-09	11
user_2	李四	25	18800006666	2021-09	11
user_3	王二	22	18899997777	2021-09	11

拉链表示例：

2021-09-10

user_id	name	age	phone	start_date	end_date
user_1	张三	20	18811112222	2021-09-10	9999-12-31
user_2	李四	25	18800006666	2021-09-10	9999-12-31

2021-09-11

user_id	name	age	phone	start_date	end_date
user_1	张三	20	18811112222	2021-09-10	2021-09-10
user_2	李四	25	18800006666	2021-09-10	9999-12-31
user_1	张三	20	18805642121	2021-09-11	9999-12-31
user_3	王二	22	18899997777	2021-09-11	9999-12-31

4.1、表命名规范

规范：统一使用大写英文字母、数字或下划线，关键词统一使用英文单词或者缩写

命名格式：<分层名>_<周期>_<主题域名>_实体(E)/过程（P）_<业务分类可选>_关键词

示例：

DWD全网客户信息快照表(日) :DWD_D_CUS_E_AL_SNA_CUSTOMER

DWA汇总全网客户积分信息汇总快照表（月）:DWA_M_CUS_AL_SNA_SCORE

注：根据日常使用经验建议，数仓中最常定义的就是增量表（无更新操作），快照表两种，其次是拉链表，拉链表适合超大数据量表

中间表：MID_最终结果表名_(0~9)

临时表：T_创建人姓名英文缩写_关键字_时间

码表：DIM_关键字

4.2、物理模型创建规范

存储格式：由于是基于hdfs存储，hive分析的，所以数仓中建表默认用的都是PARQUET存储格式

字符集：utf-8

约定：

1、所有的表均为内部表，location根据权限分组默认即可；

2、分区表必须包含ymd；

3、使用动态分区需要相关参数设置：set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;

4、理论上数仓的表字段不为null，用空字符串或者0代替；

5、注释必须有，包括表名、字段注释；

6、不可跨层查询引用

4.3、流程命名规范

格式：数据域（PRT/MGR/ACC/MRT/SHR/RES/PRD）-周期（Y/M/D/MI/S/T（实时））-数据层(DWD/DWA/ESD/DIM)-流程关键字

示例：PRT_D_DWD_CB_USER