谈笑间学会数仓—开发规范

谈笑间学会数仓—开发规范

前言

为提高开发的进度，大数据中台开发，需要一套约定的数据库表与字段的命名规范，避免在后续开发中使用原来数据库表时，因为数据库存表的可读性，表字段规则不统一，造成数据查询，数据使用效率低的问题。

• 命名规范的统一，包括数据库名、表名与字段名等统一。

  (1) 数据库名称长度尽量不超过 10 个字符，表与字段名称长度尽量不超过 30 个字符。

  (2) 数据库、表与字段名称统一为小写，多个单词组成一个字段用下划线分开，可以简写。

  (3) 数据库、表与字段名称用英文字母、数字与下划线，禁用特殊符号。

  (4) 表名与字段名必须添加注释。

  (5) 在实际应用中，只要不违反常规要求，允许存在可理解的偏差。

• 字段类型的统一。相同与相似字段的字段类型统一。

• 公共代码及代码值的统一。公共代码及标志性字段的数据类型、命名方式等统一。

• 业务含义相同的表的统一。主要依据高聚合、低耦合的理念，在物理实现中，将业务关系大、源系统

  影响差异小的表进行整合；将业务关系小、源系统影响差异大的表进行拆分；

数据库/表命名规范

数据库

序号	架构层名称	架构层英文	架构层简写	数据库名称	数据存储
1	公共维表层	Dimension data store	dim	xxx_dim	HDFS
2	公共明细层	Data Warehouse Detail	dwd	xxx_dwd	HDFS
3	公共汇总层	Data Warehouse Service	dws	xxx_dws	HDFS
4	累积汇总层	Data Warehouse Total	dwt	xxx_dwt	HDFS
5	应用数据层	Application Data Store	ads	xxx_ads_系统简写	HDFS
6	临时存储	Temporary data store	tmp	xxx_tmp	HDFS

注意点：

3、4两层呢也有些公司在数仓设计的时候放在了一层，也有些不通的说法，有兴趣可以了解 数据仓库分层介绍

数据库名称长度尽量不超过 10 个字符。

数据库名开头带上 xxx，后面带上数据架构层的简写，多个单词组成的，中间以下划线分割。

公共维表 dim

公共维度层（dim），将同类型或相似的信息进行分类，包含组织、货品、顾客/会员、人事等维度的数据。分类如下：

序号	维度名称	维度英文	维度简写	备注
1	组织	organ	org	店铺信息、仓库信息、货管单位信息等
2	货品	product	pro	货品信息等
3	顾客/会员	member	mem	顾客信息、会员信息等
4	人事	employee	emp	人事信息等
5	时间	time	time	时间信息等
6	客户	customer	cust	批发客户信息
7	其它	other	oth	其它信息

命名规则：dim_{维度简写}_{维度定义(_自定义命名标签)}。维度定义为富有意义英文词汇。

命名时参照需求分析和常用系统词汇，多个单词组成一个字段用下划线分开，可以简写。如：产品信息表，可以命名为dim_pro_allinfo。 缓慢变化的表以 scd 结尾，如：店仓缓慢变化维度表，可以命名为 dim_org_store_scd 维度中的空值统一用“~”填充，特殊情况时可以允许偏差。

公共明细层 dwd

命名规则：dwd_{主题简写}_{业务过程(_自定义表命名标签)}。业务过程为富有意义英文词汇或简写。

命名时参照需求分析和常用系统词汇,多个单词组成一个字段用下划线分开，可以简写。如：订单明细表，可以命名为 dwd_ord_order_detail 或者 dwd_ord_order_dtl。

公共汇总层 dws

公共汇总层（dws），对公共明细层的数据按照不同粒度进行进一步聚合，如：时间粒度、产品粒度、 组织粒度等。

命名规则：dws_{粒度简写}_{业务过程(_自定义表命名标签)}。业务过程为富有意义英文词汇或简写。

命名时参照需求分析和常用系统词汇，多个单词组成一个字段用下划线分开，可以简写。如：日机构产品尺码销售表，可以命名为 dws_day_org_pro_size_sal

应用数据层 ads

应用数据层（ads），满足具体分析需求采用星型或雪花型模型设计方法构建的数据汇总层，快速响应查询。

命名规则：dws_{项目简写}{粒度简写}{业务过程(_自定义表命名标签)}。业务过程为富有意义英文词汇或缩写。

命名时参照需求分析和常用系统词汇,多个单词组成一个字段用下划线分开，可以简写。如：微信日机构销售表，可以命名为 ads_wx_day_org_sal

临时存储层 tmp

临时存储层（tmp），用来降低加工过程计算难度，提高运行效率的临时表层

序号	名称	英文	缩写
1	临时表	temporary	tmp

命名规则：tmp_{架构层简写}_{目标名各词汇第一各字母}_业务过程(_自定义表命名标签)。业务过程为富有意义英文词汇或缩写。

命名时参照需求分析和常用系统词汇，多个单词组成一个字段用下划线分开，可以简写。

如：汇总 dws 层表（dws_day_pro_sal）所用的临时表：tmp_dws_day_pro_sal。

字段命名规范

字段名称长度尽量不要超过 30 个字符。

字段名统一为小写，字段的命名需富有意义英文词汇，多个单词组成的，中间以下划线分割。

字段命名只能使用英文字母、数字和下划线，禁止使用特殊字符。

相同与相似字段的字段类型需统一， 公共代码及代码值需统一。特殊情况除外，如：单据中的发方店仓、收方店仓，可以对店仓的字段分别定义。

命名时参照需求分析和常用系统词汇。如：销售量 sal_qty。

避免使用数据库中的关键字作为字段名， 如：date, select 等。字段名与数据库关键字冲突时，在源字段名后加上 col，即源字段名_col。

关键字

关键字枚举如下：

序号	关键字	序号	关键字
1	add	32	is
2	all	33	join
3	and	34	key
4	as	35	left
5	asc	36	like
6	avg	37	max
7	between	38	min
8	by	39	name
9	case	40	no
10	char	41	not
11	clob	42	number
12	code	43	or
13	commit	44	order
14	create	45	partition
15	current_date	46	right
16	ddl	47	select
17	delete	48	set
18	desc	49	size
19	distinct	50	start
20	drop	51	sum
21	end	52	sys
22	file	53	sysdate
23	for	54	test
24	group	55	time
25	having	56	trim
26	if	57	type
27	in	58	update
28	index	59	varchar
29	inner	60	when
30	insert	61	where
31	into	62	with

字段类型规范

序号	类型	常量示例	描述
1	tinyint	1Y、-127Y	8位有符号整型。取值范围：-128~127。
2	smallint	32767S、-100S	16位有符号整型。取值范围：-32768~32767。
3	int	1000、-15645787	32位有符号整型。取值范围：-2 31~2 31-1。
4	bigint	100000000000L、-1L	64 位有符号整型。 取值范围：-2 63 +1~2 63 -1。
5	double	3.1415926 1E+7	64 位二进制浮点型。
6	decimal(precision,scale)	3.5BD、 99999999999.9999999BD	10 进制精确数字类型。 precision：表示最多可以表示多少位的数字。 取值范围：1<=precision<=38。 scale：表示小数部分的位数。取值范围： 0<=scale<=18。 如果不指定以上两个参数，则默认为 decimal(38,18)。 说明：同一个表中不能同时存在新老 decimal 数据类型。decimal(precision, scale)类型在数据导入(tunnel upload)和 sql 运算过程中，如果数据的小数位数超过 scale 的大小，则会进行四舍五入的处理。如果整数部分超出限制，则会报错。
7	string	abc’、‘bcd’、‘alibaba’、‘inc’	字符串类型，目前长度限制为 8MB。
8	date	date ‘2017-11-11’	日期类型，格式为 yyyy-mm-dd。取值范围：0000-01-01~9999-12-31。
9	datetime	datetime ‘2017-11-11 00:00:00’	日期时间类型。 取值范围：0000-01-01 00:00:00.000~9999-12-31 23.59:59.999，精 确到毫秒。
10	timestamp	timestamp ‘2017-11-11 00:00:00.123456789’	与时区无关的时间戳类型。 取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999，精确到纳秒。 说明 对于部分时区相关的函数，例如 cast( as string)，要求 timestamp 按 照与当前时区相符的方式来展现。

常用系统词汇

序号	分类	名词中文名	名词全称	名词简称
1	指标属性	平均深度	average depth	avg_depth
2	指标属性	排名	rank	rank
3	财务类型	顺序号、序号、流水号	sequence number	seq_num
…	…	…	…	…

SQL 编码规范

编码原则

代码行清晰、整齐，具有一定的可观赏性。

代码编写要充分考虑执行速度最优原则。

代码行整体层次分明、结构化强。

代码中应有必要的注释以增强代码的可读性。

规范要求非强制性地约束代码开发人员的代码编写行为，在实际应用中，只要不违反常规要求，允许存在可理解的偏差。

基本需求

代码段中应用到的所有 SQL 关键字、保留字都需使用全小写，如： insert into、select、from、where、and、or等。不能使用大小写混合的方式，如 Select 或 sELECT 等方式。

代码段中应用到的除关键字、保留字之外的代码，都要求使用小写。如：select name from table1 where no = ‘xxx’;

四个空格为一个缩进量，所有的缩进均为一个缩进量的整数倍。

禁止使用 select *操作，所有操作必须明确指定列名。

通常要求对应的括号在同一列上。

较长的语句和方法声明（>80 字符）要分成多行书写，长表达式要在低优先级操作符外划分新行，操作符放在新行之首，划分出的新行要进行适当的缩进，使排版整齐，语句可读。

编码规范

主要是针对通过 SQL 进行数据汇总时，在的数据开发工作台上进行代码编辑的规范。

代码头部

代码头部添加主题、功能描述、作者、日期等信息，并预留修改日志及标题栏，以便后续修改人员添

加修改记录。

范例如下：

-----------------------------------------------------------------

–author zhang.cg

–version 1.00 2020/07/03

–功能：

–算法：

–性能参考：

–修改: (1) zhang.cg 1.01 2020/07/07

-----------------------------------------------------------------

字段排序要求

select 语句选择的字段按每行一个字段方式编排。

select 单字后面一个缩进量后应直接跟首个选择的字段，即字段离首起一个缩进量。

逗号“,”放置字段名后。

两个字段之间的逗号“,”分割符紧跟在第二个字段的前面。

'as’语句应与相应的字段在同一行，多个字段的’as’建议尽量对齐在同一列上。

示例如下：

select
  t1.no as no, 
  t1.code as code, 
  t1.ename as ename, 
  t1.cname as cname, 
  t1.order_no as order_no 
from xxx_ods.xxx_org_xxx_ods t1 
;

select 子句排列与运算符前后间隔要求

select 语句中所用到的 from、where、group by、having、order by、join、union 等子句，需遵循如下要求：

(1) 换行编写。

(2) 与相应的 select 语句末字母对齐编排。

(3) 子句后续的代码离子句首字母二个缩进量起编写。

(4) where 子句下的逻辑判断符 and、or 等与 where 末字母对齐编排。

(5) 超过两个缩进量长度的子句加一空格后编写后续代码，如：order by、group by 等。

算术运算符、逻辑运算符的前后要保留一个空格。

示例如下：

select 
	...
from xxx_dws.xxx t1, xxx_dim.xxx t2 
where 1 = 1 
  and t1.x = t2.x
group by t1.x 
order by t1.x
;

子查询嵌套编写与别名定义

在数据仓库系统 ETL 开发中经常需要用到子查询嵌套，因此代码的分层编排变得非常重要，每层嵌套以四个缩进为准。

将所有的表加上别名。表别名采用简单字符命名，避免使用关键字。从第一层次至第四层次，分别用 t、tt、ttt、tttt 表示，对于同一层次的多个子句，可以在字母后加 1、2、3、4 区分，必要时为表别名添加注释。

示例如下：

select 
	...... 
from(
  select 
  	...... 
  from table1 **t1,** 
  		 table **t2** 
  where ...... 
)**tt1,** 
table3 **tt2** 
where ...... 
;

注释总体说明

每段代码需要添加注释说明(符号“–”和说明文)。

全部使用中文（模版给定的固定说明除外），不得使用其它语言进行注释，注释的结尾无分号、句号等标点符号。

注释应与其描述的代码相近，放在其上方或右方（对单条语句的注释）相邻位置，不可放在下面。

注释和代码之间不允许有空行。

对所有得变量的定义和分支语句（条件分支、循环语句、case 语句等）必须编写注释。

添加或注释掉的代码行需编写注释(更改人、版本、更改时间)。

示例如下：

-- 汇总xxx表 
insert into bdc_dim.dim_brand_info 
...

表设计规范

各架构层的表在不同类型计算任务作为操作对象(源表或目标表)。表设计是否合理将影响存储和计算的

性能，进而影响到存储和计算的计费。

表设计的原则：

• 降低存储成本：合理的表设计可以降低数据分层设计上的冗余存储，减少中间表的数据量大小。对表数据的生命周期进行正确地管理，也能够直接降低存储的数据量及存储成本。

• 降低计算成本：规范化的表设计可以优化数据的读取，从而减少计算过程中的冗余读写和计算，提升计算性能，降低计算成本。

• 降低维护成本：规范化的表分层设计能够直接体现业务的特点。

表设计

所有的表和字段名要使用统一的命名规范，具体命名规则参考“表命名规范”，表必须包含 ETL 时间(etl_time)字段。

设计步骤：

• 确定所属架构层，依据业务过程规划表类型，分析数据层次。

• 定义表描述，进行权限定义与所有者定义。

• 依据数据量、数据集成特点定义分区表或非分区表。

• 定义字段或分区字段。

• 创建表和转换表。

• 明确导入数据场景的相关因素（包括批量数据写入、流式数据写入、周期性条式数据插入）。

• 定义表和分区的生命周期。数据开发规范

分区设计

一张表里有很多个一级分区，大数据量的事实表中需要创建分区（建议先按日期分区，然后按交易类型分区），维度表不需要分区

设计需要注意：

• 设置分区的数量上限。单表支持的分区数量上限为 6 万个。

• 避免每个分区中只存少量数据。

• 以方便数据查询和计算为前提设置分区列。

• 避免每个分区中出现多次数据写入。

• 分区字段统一用 partition_业务含义。如：partition_day、partition_week、partition_month 等。

表数据存储规范

• 各架构层按数据实际的应用规划数据的生命周期

• dwd 层对各主题保留一份完整的全量数据(对 ods 层的数据进行清洗，删除非必要的冗余字段)。可以适当的拆分或者做分区来提高性能。

• 记录热应用字段的历史缓慢变化情况，以便追溯某个时点的值。 -

• 对维度表适当的进行垂直拆分或水平拆分，这使得数据结构变得灵活、易于扩展，数据一致性得到了增强，可以更加方便地管理数据。

• 事实表可以根据应用情况冗余维表的字段，方便使用，无需关联多张表查询数据。主要适用于 ads 层。

• 将事实表中更改频繁的数据，适当的进行拆分处理，可以使数据汇总更加灵活。如：会员表中的“会员等级”。