😍 数仓规范篇、02.开发规范

😍 数仓规范篇、02.开发规范

HIVE SQL 作为数仓操作语言,良好的代码习惯能我们高效地完成数据分析工作。除了根据 SQL 执行顺序上的优化外,按以下规范编写 SQL 代码既能使代码更加清晰,方便自己后期维护,也方便其他同事接手。

一、注释规范

  • 注释内容要清晰明了,含义准确,避免歧义

  • 字段注释紧跟在字段后面

  • 应对不易理解的分支条件表达式加注释

  • 对重要的计算应说明其功能

  • 过长的函数实现,应将其语句按实现的功能分段加以概括性说明

  • 原则上所有表、字段、任务都需要添加注释,任务有特定的注释规范,见下文任务注释说明

任务注释说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- [hive][ads][营销类应收未付明细报表]ads_fin_receivable_unpaid_marketing_detail_df

-- **************************************************************************
-- ** 创建者 : baihe
-- ** 创建日期 : 20210326
-- ** 功能描述 :营销类应收未付明细报表
-- **************************************************************************
-- **************************** 修改日志 *************************************
-- 2021.3.10 修正应收未付金额算法逻辑为直接取值
--
-- **************************************************************************
-- ** 依赖表:
-- dws_fin_receivable_unpaid_marketing_detail_df 营销类应收未付汇总表
--
-- **************************************************************************
-- ** 输出表:
-- ads_fin_receivable_unpaid_marketing_detail_df 营销类应收未付明细报表
-- **************************************************************************
--
-- ******************* CURRENT_VERSION : V.1.1 ***************************
-- **************************************************************************

任务注释注意事项补充

1、提供任务名,方便任务创建时任务的获取以及平台中任务查询
2、提供创建者、创建日期、功能描述等信息,方便后期维护跟踪
2、提供代码变更历史,便于了解代码演进历史及依据
3、提供脚本依赖表清单,方便后续任务依赖配置
4、提供输出表清单,方便确认是否单个目标表

二、存储格式规范

所谓的存储格式就是在Hive建表的时候指定的将表中的数据按照什么样子的存储方式,如果指定了方式,那么在向表中插入数据的时候,将会使用该方式向HDFS中添加相应的数据类型。建表如果无特殊要求,一律使用下面的row format格式及存储格式。

1
2
row format serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
stored as RCFile;

三、建表语句规范

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--参考实例
create table if not exists ads_sg.ads_fin_receivable_unpaid_marketing_detail_df
(
`business_id` STRING comment '关联id',
`contract_id` STRING comment '合同id',
`region_id` STRING COMMENT '所属大区ID',
`region_name` STRING COMMENT '所属大区',
`city_company_id` STRING COMMENT '所属城市公司ID',
`city_company_name` STRING COMMENT '所属城市公司名称',
`plaza_id` STRING COMMENT '广场主数据id',
`plaza_name` STRING COMMENT '广场简称',
`category` STRING COMMENT '类别',
`budget_amt` DECIMAL(18, 2) COMMENT '预算金额',
`contract_type` STRING COMMENT '合同类型',
`supplier_name` STRING COMMENT '乙方单位',
`contract_amt` DECIMAL(18, 2) COMMENT '合同金额',
`change_amt` DECIMAL(18, 2) COMMENT '预计变更金额',
`sign_off_amt` DECIMAL(18, 2) COMMENT '预计结算金额',
`payable_amt` DECIMAL(18, 2) COMMENT '累计应付金额',
`payable_rat` DECIMAL(18, 4) COMMENT '累计应付比例',
`payment_amt` DECIMAL(18, 2) COMMENT '累计已付金额',
`unpaid_amt` DECIMAL(18, 2) COMMENT '累计应付未付金额(计算)',
`is_acceptance_form` INT COMMENT '是否有验收单',
`acceptance_time` STRING COMMENT '验收时间',
`risk_prompt` STRING COMMENT '风险提示',
`last_update_time` timestamp() COMMENT 'etl时间'
)
comment '营销类应收未付明细报表'
partitioned by ( stat_date bigint comment '日期分区')
row format serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
stored as RCFile;

四、数据类型规范

1、一般情况下大部分字段都采用 string 类型
2、金额类及其它小数点数据等非整型数据统一使用decimal
3、分区字段比较特殊,在数仓中统一使用bigint(平台特性原因),非平台项目可以使用date或string
4、金额类数据如果单位是元,则采用decimal(16,4) 长度类型,如果单位是万元或亿元,则采用 decimal(10,6) 长度类型,其它数据字段类型视具体情况而定
5、整形数值统一使用BIGINT或INT,最大值10位及以上的整数使用BIGINT,最大值在3-9位的整数使用INT
6、时间类型数据不做具体要求,可以使用string、也可以根据日期格式使用date或timestamp,对于只包含年月日的数据字段可采用 date 类型;对于既包含年月日也包含时分秒的数据字段可采用 timestamp 类型

五、SQL编码原则及规范

1、SELECT 查询语句中禁止使用 select *,所有操作必须明确指定列名
2、QL代码中应用到的所有关键字、保留字都使用大写,如select、from、where、and、or、union、insert、delete、group、having、count等。
3、SQL语句多表连表时,应使用表的别名,同时表别名形式来引用列
4、SELECT语句排列样式上,对于字段应每行一个进行编排,两个字段之间的逗号分割符紧跟在第一个字段后面或第二个字段的前面,as语句应与相应的字段在同一行,多个字段的as建议尽量对齐在同一列上

5、CASE语句的编写排列样式上,when语句应在case语句下一行,缩进一个缩进量后开始编写,每个when语句一行,当然如果语句较长可换行编排,case语句必须包含else子语,else子句与when子句对齐。


6、SQL代码中应用到的除关键字、保留字之外的代码,也都使用小写,如字段名、表别名等
7、代码中开发人员应有必要的注释从而增加代码的可读性
8、开发人员编写的代码应功能完善及健壮,同时注重美观行列段落划分整洁清晰
9、代码编写应充分考虑执行速度最有原则

六、空值处理

对于表中的空值,尽量用如下规则统一,如果有特殊情况请视情况而定。

字段类型 空值替换值
STRING -
DECIMAL(16,4) 0.0000
DECIMAL(16,2) 0.00
FLOAT 0.00
INT、BIGINT 0
DATE 1970-01-01
TIMESTAMP 1970-01-01 00:00:00

特殊说明:
一般而言上述情况基本可以满足需求,但是不排除有特殊情况,需求要求不按照上述规范或者上述不足覆盖的场景,视情况而定。除了将空值替换为特定的值之外,业务也有直接过滤掉数据的情况。

规范要求的项,并非强制性约束,在实际应用中在不违反常规要求的前提下允许存在可理解的偏差。同时在研发过程中,如遇到问题以及好的建议,及时沟通补充此规范。也希望规范在对日常的代码开发工作起到指导作用的同时也将得到不断的完善和补充。

评论