网站首页 > 博客文章 正文
导读:如何编写优雅可度性高的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数据库操作,目前此操作由运维管理。
结语:后续在开发过程中会不断迭代与修改,最终将规范形成平台化。
猜你喜欢
- 2024-10-07 Flink1.10集成Hive快速入门(flink集群)
- 2024-10-07 Spark源码阅读:SparkSession类之spark对象的使用
- 2024-10-07 存储过程转hivesql有哪些注意事项
- 2024-10-07 0277-Impala并发查询缓慢问题解决方案
- 2024-10-07 画像笔记25-用户画像应用(10)-用户行为分析
- 2024-10-07 CASE … WHEN和cast类型转换(日志案例分析应用)
- 2024-10-07 hbase和hive集成映射(hive与hbase集成)
- 2024-10-07 Hive 导数据的两种方案(hive导入)
- 2024-10-07 Hive SQL常用命令总结,大数据开发人员按需收藏
- 2024-10-07 hive 之前操作脚本汇总(hive shell脚本)
你 发表评论:
欢迎- 07-08Google Cloud Platform 加入支持 Docker 的容器引擎
- 07-08日本KDDI与Google Cloud 签署合作备忘录,共探AI未来
- 07-08美国Infoblox与Google Cloud合作推出云原生网络和安全解决方案
- 07-08GoogleCloud为Spanner数据库引入HDD层,将冷存储成本降低80%
- 07-08谷歌推出Cloud Dataproc,缩短集群启动时间
- 07-08Infovista与Google Cloud携手推进射频网络规划革新
- 07-08比利时Odoo与Google Cloud建立增强合作,扩大全球影响力
- 07-08BT 和 Google Cloud 通过 Global Fabric 加速 AI 网络
- 最近发表
-
- Google Cloud Platform 加入支持 Docker 的容器引擎
- 日本KDDI与Google Cloud 签署合作备忘录,共探AI未来
- 美国Infoblox与Google Cloud合作推出云原生网络和安全解决方案
- GoogleCloud为Spanner数据库引入HDD层,将冷存储成本降低80%
- 谷歌推出Cloud Dataproc,缩短集群启动时间
- Infovista与Google Cloud携手推进射频网络规划革新
- 比利时Odoo与Google Cloud建立增强合作,扩大全球影响力
- BT 和 Google Cloud 通过 Global Fabric 加速 AI 网络
- NCSA和Google Cloud合作开发AI驱动的网络防御系统,加强泰国网络空间的安全性
- SAP将在沙特阿拉伯 Google Cloud 上推出BTP服务
- 标签列表
-
- ifneq (61)
- 字符串长度在线 (61)
- googlecloud (64)
- messagesource (56)
- promise.race (63)
- 2019cad序列号和密钥激活码 (62)
- window.performance (66)
- qt删除文件夹 (72)
- mysqlcaching_sha2_password (64)
- ubuntu升级gcc (58)
- nacos启动失败 (64)
- ssh-add (70)
- jwt漏洞 (58)
- macos14下载 (58)
- yarnnode (62)
- abstractqueuedsynchronizer (64)
- source~/.bashrc没有那个文件或目录 (65)
- springboot整合activiti工作流 (70)
- jmeter插件下载 (61)
- 抓包分析 (60)
- idea创建mavenweb项目 (65)
- vue回到顶部 (57)
- qcombobox样式表 (68)
- tomcatundertow (58)
- pastemac (61)
本文暂时没有评论,来添加一个吧(●'◡'●)