SQL常见故障多案例FAQ宝典 --项目总结(宝典二) SQL常见故障多案例FAQ宝典 --项目总结(宝典二) #SQL

#SQL常见故障 #大数据 #生产环境真实案例 #SQL #离线数据库 #整理 #经验总结

说明：此篇总结SQL常见故障案例处理方案结合自身经历总结不易 +关注 +收藏欢迎留言

SQL使用类常见故障案例如下：

join中非常规join on写法导致任务运行慢

问题

两表join未写为标准的a join b on格式，使用a,b where a.* xx***=b.** xx方式导致任务运行慢。

原因

该种SQL会造成笛卡尔积导致运行速度缓慢。针对该问题可以通过explain sql; 打印执行计划，此时笛卡尔积的sql key为空，例如下图中keys0和1后无内容，代表没有key值：

转存失败，建议直接上传图片文件

解决方法

修改为标准join on语法，例如：a join b on a.* xx***=b.** xx，修改完可再次打印执行计划看“keys”处是否有值了，有值则修改成功。

视图中多表union all，视图外指定分区查询慢

问题

视图定义为多表union all时，表的分区数据类型不同时，在视图外指定分区条件查询很慢，出现全表扫描。

原因

视图定义为多表union all时，表的分区数据类型不同时（varchar类型长度不同也为类型不同），在视图外指定分区条件不会下推到视图的表里，而是全表扫描后再做过滤。是否是全表扫描可以使用explain sql; 打印执行计划查看，可通过以下两点确认：

是否有分区筛选条件，有则为未下推，全表扫描。
统计信息很大时为未下推，全表扫描。

例如：

条件未下推，发生全表扫描执行计划：

转存失败，建议直接上传图片文件

条件下推，未发生全表扫描的执行计划：

转存失败，建议直接上传图片文件

解决方法

在创建视图时指定好分。
视图中的表的分区字段类型相同。

over（partition by orde by）使用同一字段，MapReduce任务运行慢

问题

over（partition by order by）中partition by与order by后字段一样，MapReduce任务运行缓慢。

原因

该语法是按指定字段分区后按指定字段排序，partition by与order by字段一致时order by无意义。partition by order by为快速排序，在存在大量相同数据时，且partition by的分区多的情况下任务运行缓慢。

解决方法

修改partition by与order by为不同字段。
删除命令中的order by。

join on条件中使用or

问题

表关联中带有or条件例如“a join b on a.xx=b.xx or a.xx=b.xx”，数据量大时启动的MapReduce任务缓慢。

原因

join on条件中带or，没有key会产生笛卡尔积，可以通过explain sql* *;**查看执行计划key是否有值：

转存失败，建议直接上传图片文件

解决方法

删除命令中的or，使用union all方式拼接。

SQL扫描分区过多，元数据SQL拼接过长报错

问题

SQL扫描表分区过多，拼接的元数据SQL过长导致客户端返回或者HiveServer与MetaStore运行日志里出现如下报错：

转存失败，建议直接上传图片文件

原因

SQL扫描表分区过多。

解决方法

修改SQL，减少分区遍历量，当前可以正常遍历2000以下分区。

join表重复数据多，任务运行慢

问题

MapReduce任务运行缓慢。

原因

join的两表关联的key值字段存在大量重复数据，join产生类似笛卡尔积，导致写出数据膨胀，任务运行慢。从MapReduce任务日志中可以看到写出很多数据：

转存失败，建议直接上传图片文件

解决方法

查询两表join on条件字段值的分布情况，可以将大key提取出来进行单独处理，结果集使用union all进行拼接。

SQL过于复杂，嵌套过多，导致HiveSQL编译时间过长

问题

HiveSQL编译慢，执行10小时以上。

原因

SQL嵌套过多，例如，以下命令中的SQL存在多层嵌套：

select * from (select * from (select * from (select * from student))));

解决方法

修改SQL，重新执行。可以删除无用的SQL嵌套，修改为简单明了的SQL，例如上述例子可修改为 ：select * from student; 。

join on中加判断，导致MapReduce任务运行慢

问题

启动的MapReduce任务运行速度慢。

原因

on条件中存在if或case when判断，在mapjoin时，会拿大表数据逐条与小表比对，同时逐条做判断导致任务运行慢。例如：

**Select ***

From tba t1

Join tbb t2

On t1.id=

Case where t1.type=’qwr’ THEN t2.type ELSE null END;

解决方法

将SQL语句拆分。例如上述示例SQL修改为：

**select ***

from tba t1

join tbb t2

on t1.id= t.type

where t1.type='qwr';

join on中带不等于条件，导查询的数据都为Null

问题

查询出的数据都为Null。

原因

当Hive CBO为true时，Hive2.2版本后支持join的on条件中存在不等于条件，若CBO为false则查询结果中所有字段都为Null，同时不同于条件会产生笛卡尔积。例如：

select t1.id

from student t1

join student_p t2

on t1.id>=t2.id;

解决方法

修改CBO为true，或整改SQL语句。

特殊写法导致大数据量只有一个reduce

问题

数据量很大，但是启动的任务只有一个reduce，导致任务执行慢。

原因

以下三种情况只有一个reduce：

聚合函数未加GroupBy。
Order By。
笛卡尔积。

解决方法

修改SQL语句。

cascade级联修改表字段

问题

级联修改表字段，例如：Alter table tb_1 add cloumns id string cascade; ，在分区和字段数多的情况下会导致修改慢，极端情况会导致DBService重启。

原因

加入cascade会将表分区的字段都进行修改，会在元数据库中启动事务新增“分区数字段数”条记录再删除之前的“分区数字段数”条记录。

解决方法

删除命令中的cascade，不级联修改时，若往历史分区插入数据，新增的字段由于无元数据，查询显示为Null。
重建表，历史数据重新导入新表。

alter table drop partition删除大量分区慢、报错

问题

alter table drop partition删除大量分区时，删除慢、报错等。

原因

删除分区会关联删除大量元数据表，例如：Partitions、partition_params，为重量级操作。

解决方法

分批删除，1000个分区以下一批。说明：需快速删除的场景可参考【Hive大分区表后台删除表方法】章节，该操作存在风险，请谨慎操作。

大分区表查询不带分区，查询慢

问题

查询慢。

原因

分区多，全表扫描使得读取元数据和HDFS数据缓慢。

解决方法

查询时带分区进行查询。
多分区查询优化：

配置HiveServer和MetaStore的以下参数：
- 配置“hive.metastore.million.partition.optimizer.batch.retrieve.max”参数的值为“1000”。
- 配置“hive.metastore.client.socket.timeout”参数的值为“86400”。
- 配置“hive.metastore.million.partition.optimizer.batch.delete.max”参数的值为“1000”。

两表关联，关联字段类型不一致

问题

两表关联，查询慢。

原因

关联字段类型不一致，关联前会做一个类型转换，导致时间变长。

解决方法

修改两表关联字段类型保持统一。

left join右表分区条件写在where后，查询慢

问题

查询慢，右表会进行全表扫描。

原因

例如：

select t1.id

from student_p t1

left join test0617 t2

on t1.id=t2.id

where t1.pt_dt<'2022-02-25' and t2.pt_dt<'20220616';

副表（即命令中的t2）表，where条件写在join后面，会导致先全表关联再过滤分区。虽然主表（命令中的t1）表分区条件也写在join后面，但是主表会谓词下推，先执行分区过滤再进行join。

打印执行计划可以看到SQL扫描了副表的不在条件内的分区：

图片.png 解决方法

将副表的分区过滤条件写在join中，上述例子可修改为：

select t1.id

from student_p t1

left join test0617 t2

on t1.id=t2.id and t2.pt_dt<'20220616'

where t1.pt_dt<'2022-02-25';

打印执行计划可以看到，20220617分区不再扫描，只扫描指定的分区：

图片.png

SparkSQL嵌套过多，大量case when

问题

编译解析SQL生成代码时，Driver内存调至70GB仍出现oom。

原因

当参数“spark.sql.codegen.wholeStage”值为“true”（默认值“true”）时，全阶段代码生成（Whole-stage Code Generation）这个是针对整个SQL进行的代码生成，当SQL嵌套过于复杂，逻辑过多，Driver在生成代码过程的压力就会过大，发生oom。

转存失败，建议直接上传图片文件

解决方法

整改SQL，删除无用嵌套。
修改“spark.sql.codegen.wholeStage”为“false”，开启后Driver侧不生成完整的Java代码，效率会慢一点。

SparkSQL没有group by的情况下使用having

问题

编译报错。

原因

正常情况下having使用在group by筛选分组后的各组数据，Spark2.3未规范校验having，执行正常，Spark2.4开始严格校验，导致版本间存在差异。

解决方法

整改SQL，用where代替having规范使用。
Spark2.4设置参数“spark.sql.legacy.parser.havingWithoutGroupByAsWhere”为“true”。

设置自定义参数出现递归调用

问题

报错。
自定义参数很长的情况下可能会导致整个HiveServer实例不停地重启。

原因

如下图中设置working_time变量时，变量中又存在working_time，会出现死循环：

转存失败，建议直接上传图片文件

解决方法

修改SQL语句，不能存在递归调用。

视图存在全表扫或者大量分区查询

问题

查询慢，大量扫描文件的情况下会导致hiveserver fullgc。

原因

Hive在用到视图时相当于是把视图的SQL拼在HiveSQL中执行的，实际执行的SQL还是全表扫，多次调用视图即是多次全表扫的过程。

解决方法

修改视图，规范使用视图。

大量使用with as并在SQL中多处调用

问题

任务运行慢。

原因

with as语句不会物化，每次调用会执行一遍，多次调用的情况下且with as逻辑复杂的情况下任务运行缓慢，例如下图中，“Stage-1”和“Stage-6”执行的为同一个with as中的任务：

转存失败，建议直接上传图片文件

解决方法

将with as中内容建立成临时表。

insert into table values形式插入大量数据

问题

Hiveserver fullgc。

原因

该使用即为把数据导出为insert into values的SQL语句，由于数据条数很多，导致导出的SQL很长，甚至一个SQL内容要几百兆。

解决方法

使用常规的数据导入方式，例如生成文本文件，再load入Hive表中。

多表关联过滤条件中按in的子查询进行过滤

问题

数据结果不符合预期。

原因

版本不支持该SQL，在hive.cbo.enable为False情况下，left join对应右表的where过滤条件会丢失。如下用例即可复现，打印执行计划会发现t3的id2=123的过滤条件丢失。

create table test101 (id string,id2 string);
create table test102 (id string,id2 string);
create table test103 (id string,id2 string);
create table test104 (id string,id2 string);

explain select * from test101 t1
left join test102 t2 on t1.id=t2.id
left join test103 t3 on t1.id=t3.id2
where t1.id in (select s.id from test104 s)
and t3.id2='123';

转存失败，建议直接上传图片文件

解决方法

调整SQL，设置hive.cbo.enable为true执行SQL。

string类型字段过滤不加引号

问题

数据结果与预期不符。

原因

当条件不带引号时，为Int类型，会把字段转为Int类型和条件做比较，当条件带引号时，为string类型，会把字段和条件直接对比。例如当条件字段id为string类型筛选条件为id=1时，id为1.0，1的都会被筛选出来，当id=‘1’时，只会筛选出id为1的。

图片.png

解决方法

业务按照标准进行查询，string类型的字段用string类型的条件进行查询。

使用order by null

问题

Group by结果不符合预期。

原因

Order by null为MySQL取消group by中隐式排序做的功能，hive不支持，会导致group by后二层group by分组按不排序的结果进行分组，结果不符合预期。例如：

select name,phone from test0223 t group by name,phone,data_id order by NULL

转存失败，建议直接上传图片文件

select m.name,m.phone,count(*) as count,current_date from

( select name,phone from test0223 t group by name,phone,data_id order by NULL)

as m group by m.name,m.phone having count(*) >0 ;

结果应该为name和phone分组，name和phone一致的应该统计在一起，实际zhangsan 1333333333按子查询的不排序结果，分了三组如下：

转存失败，建议直接上传图片文件

解决方法

取消order by null。

使用浮点数进行运算

问题

结果不符合预期。

原因

例如如下SQL的期望值为6556051，实际值为6556050.999999999。

select cast(65560.51 as double)100;

图片.png 计算机发展了如此长的一段时间，但它始终只能识别0和1（即二进制）。无论我们使用哪种编程语言，在哪种编译环境下工作，都要先把源代码翻译成二进制的机器码才能被计算机所识别。

举个简单的例子，在源程序里面2.4是十进制的，但计算机不能直接识别，要先编译成二进制。那么问题来了，2.4的二进制并非是精确的2.4，反而是最为接近的二进制表示是2.39999999996。原因在于浮点数由两部分组成：指数和尾数。如果知道怎么进行浮点数的二进制和十进制转换，应该不难理解。如果在这个转换过程中，浮点数参与了计算，那么在转换的过程中就会变得不可预知，并且变得不可逆。我们有理由相信，就是在这个过程中，发生了精度的丢失。

解决方法

Double或float转换为decimal。

并发插入同表或同分区

问题

任务失败报错临时文件找不到。

原因

并发插入时多个任务共同操作同一份临时文件，导致一个任务把另一个任务的文件移走导致找不到临时文件报错。

解决方法

当前版本以及业界都不支持并发插入，修改SQL串行运行。

最后

此篇总结SQL常见故障案例处理方案结合自身经历总结不易 +关注 +收藏欢迎留言

点关注(劳烦各位)

谢谢大家

@500佰

SQL常见故障多案例FAQ宝典 --项目总结(宝典二)

SQL使用类常见故障 案例如下：

join中非常规join on写法导致任务运行慢

视图中多表union all，视图外指定分区查询慢

over（partition by orde by）使用同一字段，MapReduce任务运行慢

join on条件中使用or

SQL扫描分区过多，元数据SQL拼接过长报错

join表重复数据多，任务运行慢

SQL过于复杂，嵌套过多，导致HiveSQL编译时间过长

join on中加判断，导致MapReduce任务运行慢

join on中带不等于条件，导查询的数据都为Null

特殊写法导致大数据量只有一个reduce

cascade级联修改表字段

alter table drop partition删除大量分区慢、报错

大分区表查询不带分区，查询慢

两表关联，关联字段类型不一致

left join右表分区条件写在where后，查询慢

SparkSQL嵌套过多，大量case when

SparkSQL没有group by的情况下使用having

设置自定义参数出现递归调用

视图存在全表扫或者大量分区查询

大量使用with as并在SQL中多处调用

insert into table values形式插入大量数据

多表关联过滤条件中按in的子查询进行过滤

string类型字段过滤不加引号

使用order by null

使用浮点数进行运算

并发插入同表或同分区

SQL使用类常见故障案例如下：