数仓-HQL规范

好的开发规范是必不可少的。

1. 规范目的

养成良好的编程习惯
写出清楚、易懂、易维护的程序代码
提高代码质量与生产率
减少软件编码中的不必要的错误

2. 开发规约

所有查询必须指定具体字段
统一采用小写字母编码(不包含参数、注解)，遵循SQL 92语法标准
所有查询表名必须添加数据库名+表名
HQL文件一定要设置任务名名称
HQL【会话临时表】必须要在末尾删除
别名一定要加AS关键字,多表关联必须要AS别名
字符串用单引号包括
不能使用
SQL 必须经过验证，更新数仓再执行
SQL代码中严格禁止使用硬编码，排除脏数据清洗编码、默认值。

3.1. 注释

此注释格式适用于SQL。

3.1.1. 头部注释

行号	注解名称	注释说明	是否选
第一行	注释分割线		必选
第二行	业务范围	此脚本所属业务范围	必选
第三行	创建人	填写公司邮箱@之前部分	必选
第四行	创建日期	脚本创建日期	必选
第五行	脚本用途	描述此脚本的作用或预期目标	必选
第六行	执行引擎	如：MySQL、Hive、Spark等	必选
第七行	执行周期	如：时、天、周、月	必选
第八行	对应报表	对应报表名称	可选
第九行	质量监控	对应数据质量校验的名称	可选
第十行	变更记录	变更记录	必选
第十一行	变更格式	变更日期、变更人、变更描述	必选
末行	注释分割线	必选

注释样例

-----------------------------------------------------------------------
– 业务范围: 常规任务
– 创建人: username
– 创建日期: 2020-01-01
– 脚本用途: 这是一个测试样例
– 执行引擎: hive
– 执行周期: 天
– 对应报表: 这是一个测试报表
– 质量监控: 这是一个测试监控
– 变更记录:
– 变更日期 变更人 变更描述
– 2020-02-02 test 注释相应表

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

3.1.2. 脚本注释

脚本注释可分为：分段注释、临时表注释、语句块注释

• 注释样例

-- 以下属于临时处理IP地址库(分段注释)
 
-- 此表用来临时存放常用IP相关数据(临时表注释)
drop table if exists temp_db.temp_tableName_yyyyMMdd;
create table temp_db.temp_tableName_yyyyMMdd
as
select 
 ip
,city_name
-- 为空时则使用xxx当默认值(语句块注释)
,nvl(city_id,'xxx') as city_id
from temp_db.temp_tableName
where p_dt='2019-04-15'
-- IP地址必须以数字开头(语句块注释)
and ip rlike '^[\\d]+'

3.2. DQL

3.2.1. 查询

特别说明，查询表全部字段，可以使用select * 且在代码上方备注"查询全部字段"，否则需要使用select 具体字段。

– 示例
– 查询全部字段
select
*
from db.table_name

常规查询 - SELECT关键字后换行 - 查询时必须指定具体字段 - 多个字段通过换行进行分割 - 首个字段前面空一空，非首个字段前面则写逗号(以便排查) - FROM后面必须接具体表名

– 示例
select
pk_column
,column1
,column2
,column3
from db.table_name

子查询 - 查询嵌套时子查询前面必须要有两个空格 - 子查询必须取别名，不能使用匿名方式 - 子查询与括号不能在出现在同一行。

– 示例
select
m.column1
,m.column2
,m.column3
from (
select
m.column1
,m.column2
,m.column3
from db.table_name_a as m
) as m

3.2.2. 关联

多表关联查询时必须使用[别名+字段]，不能直接写字段名
表关联时关键字必须全写，不能简写，如：INNER JOIN写成JOIN
表关联时必须先换行再写INNER JOIN关键字
表关联时换行后空两格ON关键字后必须紧接着关联条件
如果是子查询则直接写ON关键字后必须紧接着关联条件
如果关联条件超过两个必须换行，每行各一个关联条件且前面空两格，见示例3、示例4

-- 示例1
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
  on m.pk_column=a.id and m.column1=a.id 

-- 示例2
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join (
  select
   id
  ,name
  from db.table_name_a
) as a on m.pk_column=a.id

-- 示例3
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
  on m.pk_column=a.id
  and m.pk_column=a.id
  and m.pk_column=a.id

-- 示例4
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join (
  select
   id
  ,name
  from db.table_name_a
) as a on m.pk_column=a.id
  and m.pk_column=a.id
  and m.pk_column=a.id

3.2.3. 条件

优先将数据过滤最多的写在第一位
先换行再写关键字，然后再写过滤条件，多个过滤条件必须先换行再写关键字+条件

-- 示例
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
on m.pk_column=a.id
where a.column7='xxxx'
and a.column8='xxxx'

3.2.4. 分组

先换行再写关键字
第一个分组字段先空格再写字段名，非首个字段前面则写逗号(以便排查)

-- 示例
select
 m.column1
,m.column2
,m.column3
,count(1) as cnt
from db.table_name as m
group by
 m.column1
,m.column2
,m.column3

3.2.5. 合并

多结果集合并时，两结果集之间必须空一行再写关键字

-- 示例
select
 m.column1
,m.column2
,m.column3
from db.table_name_a as m
 
union all
select
 m.column1
,m.column2
,m.column3
from db.table_name_b as m

3.2.6. 聚合筛选

脚本尽量不用

-- 示例
select
 m.column1
,m.column2
,m.column3
,count(*) as pv
from db.table_name as m
group by
 m.column1
,m.column2
,m.column3
having count(*)>1

3.2.7. 排序

先换行再写关键字。
第一个排序字段先空格再写字段名，非首个字段前面则写逗号(以便排查)
必须明确指定排序关键字(DESC|ASC)

-- 示例
select
 m.column1
,m.column2
,m.column3
from db.table_name as m
order by
 m.column1 desc
,m.column2 asc

3.3. DML

Hive只做INSERT操作，MySQL可做INSERT、UPDATE、DELETE操作

3.3.1. INSERT

• hive

语法1：
insert overwrite table table_name [partition(column)]
select 
 column1
,column2 
from table_name
;
 
语法2：
insert into table table_name(column[...])
select 
 column1
,column2 
from table_name
;
 
语法3：
insert into table table_name(column[...])
values(column[...])
;

• MySQL

INSERT INTO [ 资料表或可更新检视表物件名称] ( 栏位1 , 栏位2 , 栏位3 , ...) VALUES ( 值1 , 值2 , 值3 , ...) --只需要根据指定的栏位来设定插入值。

INSERT INTO [ 资料表或可更新检视表物件名称] VALUES ( 值1 , 值2 , 值3 , ...) 　--必须要指定所有栏位的插入值。

3.3.2. UPDATE

UPDATE [ 资料表或可更新检视表物件名称] SET 栏1 = 值1 , 栏2 = 值2 , 栏3 = 值3 , ... WHERE [ 指定条件]

3.3.3. DELETE

DELETE FROM [资料表或可更新检视表名称] WHERE [给定条件]

DELETE * FROM [资料表或可更新检视表名称] WHERE [给定条件]

3.4. DDL

3.4.1. 常规表

表必须指定存储类型、字段分割符、换行符、表描述
首个字段空一格，其余换行使用逗号+字段
字段必须有字段描述，字符必须是英文字符，编码类字段描述格式为：1:是,2:否
字段类型必须与源字段类型一致
首先必须注释表删除语法(防止表名重复)
如果是对应有固定报表、数据分析平台的dm表，是维度字段的字段描述后面要增加’维度’二字
其余未描述规范与示例保持一致

-- 示例1

-- drop table if exists 库名.表名;

create table 库名.表名 (

column1 string comment 'column1'

) comment 'XXXXXXXX'

partitioned by (p_dt string comment '日期(yyyy-MM-dd)')

row format delimited

fields terminated by '\t'

collection items terminated by '\n'

stored as orc

;

3.4.2. 临时表

• 会话临时表

先删除后创建原则

-- 示例

drop table if exists temp_db.temp_ods_userinfo_20190201;

create table temp_db.temp_ods_userinfo_20190201

as

select

column1

,column2

,column3

,column4

from ods.ods_userinfo

• 常规临时表

参考常规建表规范

3.5. DCL

目前只针对MySQL数据库操作，目前此操作由运维管理。

结语：后续在开发过程中会不断迭代与修改，最终将规范形成平台化。