大数据开发之 Hive 如何提高查询效率

​

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

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=truemapred.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; 

文章来源于数据社

​