一、SQL本身的优化
1、只select需要的列,避免select *
2、where条件写在子查询中,先过滤再关联
3、关联条件写在on中,而不是where中
4、数据量大时,用group by代替count distinct
5、数据量小时,用in代替join
6、避免笛卡尔积
7、join时大表放后面,使用相同的连接键
7、严格格式
Hive.mapred.mode,分 nonstrict,strict,默认是nonstrict,
如果设置为strict,对三种情况限制:
(1)分区表必须加分区。
(2)order by 必须使用limit
(3)存在笛卡尔积
二、数据倾斜的处理
数据倾斜的现象:
1、任务进度长时间维持在99%(或100%);
2、查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。
3、本地读写数据量很大。
导致数据倾斜的原因:
1、空值问题
2、数据类型不一致
3、业务数据本身的问题
1、小表关联大表,开启mapjoin
(1)设置参数
set hive.auto.convert.join=true;
hive.mapjoin.smalltable.filesize=25000000 即25M
(2)手动指定
select /+ mapjoin(A)/ f.a,f.b from A t join B f on ( f.a=t.a and f.ftime=0802)
2、加盐打散
(1)空值0值 或 关联不上的,用随机数
from a join b
on if(a.key=’’, rand()-1, a.key)=b.key
–rand() 0-1之间的小数
(2)都是有用的key,则加随机数后缀
group by concat(key, cast(round(rand()*10) as int))
缺点是分成10份是提前写好的,数据变更大时,还是会跑得慢。
3、开启combiner,即map端聚合
set hive.map.aggr=true;
4、开启负载均衡,会生成两个MRJob
set hive.groupby.skewindata=true;
第一个 MR Job 中,Map 的输出结果集合会随机分布到Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group ByKey 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce中),最后完成最终的聚合操作。
5、group by 代替count distinct
6、大key单独处理,再union回去
7、增大并行度
三、hive参数的调整
1、多个job无依赖(如union all),可设置并行执行
//开启任务并行执行
set hive.exec.parallel=true;
//同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
2、设置map和reduce个数
set mapred.max.split.size=100000000; 每个map的最大输入大小
set mapred.min.split.size.per.node=100000000;一个节点上split的至少的大小,决定了多个data node上的文件是否需要合并
set mapred.min.split.size.per.rack=100000000;一个交换机下split的至少的大小,决定了多个交换机上的文件是否需要合并
3、设置mapjoin
4、设置资源相关参数
【hive on mr】
set hive.execution.engine=mr;
set mapreduce.map.java.opts=-Xmx2048m;
set mapreduce.map.memory.mb=4096;
set mapreduce.reduce.java.opts=-Xmx2048m;
set mapreduce.reduce.memory.mb=4096;
【hive on spark】
set hive.execution.engine=spark;
set spark.executor.cores=1;
set spark.executor.memory=4g;
set spark.yarn.executor.memoryOverhead=2048;
四、小文件的处理
【产生原因】
1、动态分区
2、数据源是小文件
3、reduce个数多
【影响】
1、小文件会开很多map,初始化、启动、执行会浪费资源影响性能。
2、在HDFS中,每个小文件对象约占150byte,如果小文件过多会占用大量内存。
【解决方法】
1、Hadoop achieve命令把小文件归档
2、减少reduce个数
3、参数调节
(1)设置map输入合并小文件
(2)设置map和reduce输出合并小文件
4、少用动态分区,使用distribute by分区
五、数据压缩和存储格式
【数据的压缩与存储格式】
1、压缩 gzip bzip2 snappy
2、存储格式
【行式存储和列式存储】
TextFile:行式存储,Gzip压缩后不支持split
RCFile:数据按行分块,每块按列存储。头信息:行组记录数、每列字节数、
ORC:数据按行分块,每块按列存储,是rcfile的改良版本。头信息:每一列最大小值、该行的偏移量和长度
Parquet:列式存储(压缩比高)。头信息:数据量、偏移量。
六、其他
1、查看sql执行计划 explain sql
2、分区表、分桶表