hive调优

发表于 | 分类于 | 次

hive调优

HIVE调优是一个很大的课题，涉及到hive本身的调优，hive底层的mapreduce计算引擎的调优，sql的调优，数据倾斜调优，小文件问题的调优，数据压缩的调优等

以下提供一些主要的调优总结：

1.数据的压缩与存储格式

hive底层的计算引擎是mapreduce，而mapreduce在运算时，免不了的就是要从hdfs中读取原始文件，然后在内部的map到reduce之间还要shuffle数据到各task所在的本地磁盘，最后的输出又避免不了要往HDFS中输出文件.

所以，在各个环节中，读写的数据量越小，读写的性能越高，对hive的整体执行效率肯定是有重要作用的，那么，如何降低这些环节上的文件io量以及提高文件io效率呢，一个最主要的办法就是“选择合适的文件格式”+选择合适的压缩编码

设置方式

  1. map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。
1
2
3
set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;
  1. 对最终输出结果压缩
1
2
3
set hive.exec.compress.output=true 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
## 当然，也可以在hive建表时指定表的文件格式和压缩编码

结论，一般选择orcfile/parquet + snappy 方式

2.合理利用分区、分桶

​ 分区是将表的数据在物理上分成不同的文件夹，以便于在查询时可以精准指定所要读取的分区目录，从来降低读取的数据量

​ 分桶是将表数据按指定列的hash散列后分在了不同的文件中，将来查询时，hive可以根据分桶结构，快速定位到一行数据所在的分桶文件，从来提高读取效率.

1
2
3
4
-- 创建分桶表示例
create table bucketed_user(id int,name string) clustered by (id)
 sorted by(name) into 4 buckets row format delimited fields terminated by '\t'
 stored as ORCFILE;

3.hive参数优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 让可以不走mapreduce任务的，就不走mapreduce任务
hive> set hive.fetch.task.conversion=more;
 
// 开启任务并行执行
 set hive.exec.parallel=true;
// 解释：当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all的时候）
 
 // 同一个sql允许并行任务的最大线程数 
set hive.exec.parallel.thread.number=8;
 
// 设置jvm重用
// JVM重用对hive的性能具有非常大的 影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
set mapred.job.reuse.jvm.num.tasks=10; 
 
// 合理设置reduce的数目
// 方法1：调整每个reduce所接受的数据量大小
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
// 方法2：直接设置reduce数量
set mapred.reduce.tasks = 20

4.sql优化

（1）where条件优化
优化前（关系数据库不用考虑会自动优化）：

1
select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';

优化后(where条件在map端执行而不是在reduce端执行）：

1
select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);

（2）union优化

尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重

（3）count distinct优化

不要使用count (distinct cloumn) ,使用子查询

1
select count(1) from (select id from tablename group by id) tmp;

（4） 用in 来代替join

如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join . in 要比join 快

1
2
3
select id,name from tb1  a join tb2 b on(a.id = b.id);
 
select id,name from tb1 where id in(select id from tb2);

（5）消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。 可以减少job的数量。

(6) join 优化：

Common/shuffle/Reduce JOIN 连接发生的阶段，发生在reduce 阶段， 适用于大表 连接 大表(默认的方式)

Map join ： 连接发生在map阶段 ， 适用于小表 连接 大表
大表的数据从文件中读取
小表的数据存放在内存中（hive中已经自动进行了优化，自动判断小表，然后进行缓存）

1
set hive.auto.convert.join=true;

SMB join
Sort -Merge -Bucket Join 对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发送生笛卡尔积连接（需要是两个桶表进行join）

1
2
3
4
set hive.auto.convert.sortmerge.join=true;  
set hive.optimize.bucketmapjoin = true;  
set hive.optimize.bucketmapjoin.sortedmerge = true;  
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

5.数据倾斜

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

原因：某个reduce的数据输入量远远大于其他reduce数据的输入量

1)、key分布不均匀

2)、业务数据本身的特性

3)、建表时考虑不周

4)、某些SQL语句本身就有数据倾斜

关键词 情形 后果
join 其中一个表较小，但是key集中 分发到某一个或几个Reduce上的数据远高于平均值
join 大表与大表，但是分桶的判断字段0值或空值过多 这些空值都由一个reduce处理，非常慢
group by group by 维度过小，某值的数量过多 处理某值的reduce非常耗时
count distinct 某特殊值过多 处理此特殊值reduce耗时

(1)参数调节

1
2
set hive.map.aggr=true  // map端聚合，降低传给reduce的数据量
set hive.groupby.skewindata=true // 开启hive内置的数倾优化机制

(2) 熟悉数据的分布，优化sql的逻辑，找出数据倾斜的原因。

比如，如果是在groupby中产生了数据倾斜，是否可以将group by的维度变得更细，如果没法变得更细，就可以在原分组key上添加随机数后分组聚合一次，然后对结果去掉随机数后再分组聚合

比如，在join时，有大量为null的join key，则可以将null转成一个随机字符串，也能让null key数据均匀分散到不同的reduce任务

6.合并小文件

小文件的产生有三个地方，map输入，map输出，reduce输出，小文件过多也会影响hive的分析效率：

设置map输入的小文件合并

1
2
3
4
5
6
7
set mapred.max.split.size=256000000;  
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)  
set mapred.min.split.size.per.rack=100000000;
//执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

设置map输出和reduce输出进行合并的相关参数：

1
2
3
4
5
6
7
8
//设置map端输出进行合并，默认为true
set hive.merge.mapfiles = true
//设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true
//设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000
//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=16000000

7.查看sql的执行计划

通过explain select …from ，来查看你的sql的执行计划，从来进行分析寻找，看是否有更优化的sql写法

1
explain sql

学会查看sql的执行计划，优化业务逻辑 ，减少job的数据量。 对调优也非常重要