专业的编程技术博客社区

网站首页 > 博客文章 正文

手撕数据仓库之「HQL规范篇」(数据仓库 sql)

baijin 2024-10-07 06:07:05 博客文章 4 ℃ 0 评论

导读:如何编写优雅可度性高的SQL?好的开发规范是必不可少的。

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数据库操作,目前此操作由运维管理。

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

本文暂时没有评论,来添加一个吧(●'◡'●)

欢迎 发表评论:

最近发表
标签列表