1.表设计优化
(1)利用分区表优化
建表的时候建立分区,能够提高查询速度,如果筛选条件里面有分区字段的话,那么Hive只需要遍历对用分区目录下的文件即可,不需要遍历全局数据,使得处理的数据量大大减少。
(2)利用桶表优化
指定桶的个数后,存储数据时,根据某一个字段进行哈希后,确定存储在哪个桶里,跟分区表类似也是使得筛选时不用全局遍历所有的数据,只需要遍历所在桶就可以了。
2.存储压缩优化
(1)选择合适的文件存储格式
TextFile
默认格式,如果建表时不指定默认为此格式。
存储方式:行存储。
ORC
存储方式:数据按行分块,每块按照列存储
Hive 提供的新格式,属于 RCFile 升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快,快速列存取
(2)选择合适的压缩方式
压缩方式有好多种,可酌情选择,gzip,lzo,snappy,bzip2四种方式
压缩格式 | 工具 | 算法 | 文件扩展名 | 是否可切分 | 对应的编码/解码器 |
DEFLATE | 无 | DEFLATE | .deflate | 否 | org.apache.hadoop.io.compress.DefaultCodec |
Gzip | gzip | DEFLATE | .gz | 否 | org.apache.hadoop.io.compress.GzipCodec |
bzip2 | bzip2 | bzip2 | .bz2 | 是 | org.apache.hadoop.io.compress.BZip2Codec |
LZO | lzop | LZO | .lzo | 是 | com.hadoop.compression.lzo.LzopCodec |
Snappy | 无 | Snappy | .snappy | 否 | org.apache.hadoop.io.compress.SnappyCodec |
3.SQL参数优化
(1)作业有多个可并行的job时,设置任务并行及并行个数:
开启任务并行执行 set hive.exec.parallel=true;
同一个sql允许并行任务的最大线程数 set hive.exec.parallel.thread.number=8;
(2)map和reduce优化。
- 当出现小文件过多,需要合并小文件。可以通过set hive.merge.mapfiles=true来解决。
- 单个文件大小稍稍大于配置的block块的大写,此时需要适当增加map的个数。解决方法:set mapred.map.tasks个数
- 文件大小适中,但map端计算量非常大,如select id,count(*),sum(case when...),sum(case when...)...需要增加map个数。解决方法:set mapred.map.tasks个数,set mapred.reduce.tasks个数
(3)使用mapjoin
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize=50000000; -- 默认是25M
set hive.auto.convert.join.noconditionaltask = true; -- 默认值
set hive.auto.convert.join.noconditionaltask.size = 10000000; --默认值
(4)使用TEZ查询引擎 set hive.execution.engine=tez;
(5)合并输入输出小文件
输出合并小文件
set hive.merge.mapredfiles=true;
set hive.merge.smallfiles.avgsize=512000000;
set hive.merge.size.per.task=1024000000; --默认值 256M
输入合并小文件,也用于调节map数量
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; --默认值
set mapred.max.split.size=512000000;
set mapred.min.split.size.per.node=256000000;
set mapred.min.split.size.per.rack=256000000;
set mapred.min.split.size=256000000;
(6)数据倾斜
set hive.map.aggr=true;--在map中会做部分聚集操作,效率更高但需要更多的内存。
set hive.groupby.skewindata=true;-- 当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。
4.SQL语句优化
(1)分区裁剪/行裁剪
将可以最大过滤数据的条件放在最前面
(2)列裁剪
只读取查询需要用到的列
hive.optimize.cp=true
(3)避免笛卡尔积
尽量避免笛卡尔积,join的时候不加on条件,hive只能使用1个reduce来完成笛卡尔积。(on条件只支持等值操作)
(4)union all
先做union all 再做join或group by 等操作可以有效减少MR过程,尽管又多一个select但是只有一个MR过程
若只是单纯union all,超过3个以后,建议调整为直接向表中插入数据
(5)Map端部分聚合
set.hive.map.aggr=true,进行聚合函数操作sum、count等操作时,会在map端先进行一次聚合操作
可通过设置hive.groupby.mapaggr.checkinterval来控制map端聚合的数目,默认是100000
合并小文件
hive.merge.mapfiles = TRUE 是否合并map-only job的输出文件,默认为true
hive.merge.mapredfiles = FALSE 是否合并map-reduce的输出文件,默认为false,建议开启,
降低文件数据过多对hdfs带来的压力hive.merge.size.per.task=256000000 合并文件的大小,默认为256M
(6)善用order by、sort by、distribute by、cluster by
order by 全局排序,只有一个reduce
sort by 局部排序,只能保证在同一reduce输出有序,进入reduce前完成排序
distribute by 根据distribute by指定的内容将数据分到同一reduce
cluster by 除了具有distiribute by的功能外,还会对字段进行排序,可以看做sort by + distribute by,只能是倒序排序,不能指定是asc还是desc
(7)hive join优化
说明一点:on值后面添加条件操作,和放在where后面条件判断,查询效率是一致的。但是对于外连接就不要这样子,会影响查询结果。