Hive提高查询效率的八条军规

[[423380]]

大家好，我是一哥，今天分享一下Hive如何提升查询效率。Hive作为最常用的数仓计算引擎，是我们必备的技能，但是很多人只是会写Hql，并不会优化，也不知道如何提升查询效率，今天分享8条军规：

我是一哥，现任某500强大数据分析负责人，在这里和大家分享大数据实践的那些事，个人专注数据仓库架构，数据分析，MPP数据库研究、流处理计算等领域~

1、开启FetchTask

一个简单的查询语句，是指一个没有函数、排序等功能的语句，当开启一个Fetch Task功能，就执行一个简单的查询语句不会生成MapRreduce作业，而是直接使用FetchTask，从hdfs文件系统中进行查询输出数据，从而提高效率。

设置的方式：

Hive.fetch.task.conversion 默认为minimal 
  
修改配置文件hive-site.xml 
<property> 
  <name>hive.fetch.task.conversion</name> 
  <value>more</value> 
  <description> 
    Some select queries can be converted to single FETCH task  
    minimizing latency.Currently the query should be single  
    sourced not having any subquery and should not have 
    any aggregations or distincts (which incurrs RS),  
    lateral views and joins. 
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 
    2. more    : SELECT, FILTER, LIMIT only (+TABLESAMPLE, virtual columns) 
  </description> 
</property>  
  
  
或者当前session修改 
hive> set hive.fetch.task.conversion=more; 
执行SELECT id, money FROM m limit 10; 不走mr 

2、合并中间表

一个日志文件中，每一行记录，会有很多很多字段，四五十个字段很正常。实际分析中，常常使用少数几个字段将原始的表中数据，依据业务需求提取出要分析的字段，数据放入到对应的业务表(子表)中，实际的业务针对业务表进行分析。

在实际中，我们会发现，有些业务处理，会有共同数据集用户表、订单表、商品表，三个表需要进行join的操作，join 会产生一个结果集，会有很多的业务是针对此jion结果集进行分析。

优化：将众多的业务中相同的中间结果集，抽取到一个Hive中的表中去。

3、合理使用分区表

外部表、分区表，结合使用，采用多级分区。数据采用存储格式(textfile、orcfile、parquet)或者数据压缩(snappy)。

明细数据我们一般采用按天分区，对于特别大的表，可以采用子分区，每个分区其实对应到HDFS上就是一个目录。数据存储方式我们可以采用parquet列式存储，同时具有很好的压缩性能;同时可以减少大量的表扫描和反序列化的时间。在OLAP查询场景下，我们选择需要的列信息进行查询，而不是直接select * 查询所有字段。

4、jvm重用

JVM重用是hadoop调优参数的内容，对hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。hadoop默认配置是使用派生JVM来执行map和reduce任务的，这是jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。JVM重用可以使得JVM实例在同一个JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中进行设置

mapred.job.reuse.jvm.num.tasks 1 

也可在hive的执行设置：

set mapred.job.reuse.jvm.num.tasks = 10; 

JVM的一个缺点是，开启JVM重用将会一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡“的job中有几个reduce task 执行的时间要比其他reduce task消耗的时间多得多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。

5、speculative execution(推测执行)

所谓的推测执行，就是当所有task都开始运行之后，Job Tracker会统计所有任务的平均进度，如果某个task所在的task node机器配置比较低或者CPU load很高(原因很多)，导致任务执行比总体任务的平均执行要慢，此时Job Tracker会启动一个新的任务(duplicate task)，原有任务和新任务哪个先执行完就把另外一个kill掉。

推测执行需要设置Job的两个参数：

mapred.map.tasks.speculative.execution=true 
mapred.reduce.tasks.speculative.execution=true 

6、合理设置reduce个数

reduce个数

参数1：

hive.exec.reducers.bytes.per.reducer=256000000 //每个reduce任务处理的数据量 

参数2：

hive.exec.reducers.max=1009 //每个任务最大的reduce数目 

计算公式：reducer个数=min(参数2,总输入数据量/参数1)

set mapred.reduce.tasks =N：

每个任务默认的reduce数目。典型为0.99* reduce槽数，hive默认为-1，即自动确定reduce数目。

reduce个数并不是越多越好

同map一样，启动和初始化reduce也会消耗时间和资源;另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题。小文件过多会非常影响查询效率，文件越多造成的IO就越多，同时还会增加元数据(namenode)的压力。在生产环境中，一定要避免小文件问题，如果核查发现，及时合并文件!!

7、开启并行执行

并行执行，意思是同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短

hive.exec.parallel.thread.number    8//job并行执行的数目，一个SQL语句可能有很多mapreduce任务，限制 
hive.exec.parallel  false 

hive执行开启：

set hive.exec.parallel=true 

8、优化sql

• where条件优化

优化前(关系数据库不用考虑会自动优化)：

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

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

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

• union优化

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

• count distinct优化

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

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

• 用in 来代替join

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

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); 

in 要比join 快

• 消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以减少job的数量。
• join 优化：

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

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

set hive.auto.convert.join=true; 

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

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;