HIVE 调优方法大全

Hive是目前应用最多最广的SQL on Hadoop工具，近期很多专业的大数据公司推出了很多新的，基于列式或者内存热数据的SQL工具，像Impala，Tez，Spark等等，但是Hive仍然是目前使用率最高和普及面最广的SQL on Hadoop的工具。在以前淘宝罗李的报告中，淘宝90%的业务跑在Hive上面。暴风影音的比例更高一些，大概95%以上都是跑在Hive上面。尽管很多人对Hive有看法，效率低，查询慢，bug多。但是并不可否认hive是一个开创性的工具，提供了更多的想象空间。而且，在生产集群上的数据查询程序的开发效率，Hive要远远高于自己写MR。

在默认情况下，Hive的配置参数比较保守，所以效率会比较差一点，修改配置会让查询效率有比较大的提升，记录几个对查询效率影响比较重要的参数。

首先拿到一个hive要修改的就是他的元数据存储，默认情况下，Hive是用Derby内存数据库存储元数据。

Hive参数优化：

hive.map.aggrtrue是否在 Map 端进行聚合，默认为 Truehive.groupby.mapaggr.checkinterval100000在 Map 端进行聚合操作的条目数目hive.groupby.skewindatatrue数据倾斜聚合优化，当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。mapred.reduce.tasks-1每任务最大使用reduce数hive.exec.reducers.max999Hive最大使用reduce槽位数javax.jdo.option.Multithreadedtruejdbc访问元数据库采用多线程并发hive.optimize.groupbytrue优化group byhive.optimize.union.removefalse在大量union情况下进行优化 默认false，需要带有partition的表才能用hive.exec.paralleltrue并行执行嵌套select，默认 falsehive.exec.parallel.thread.number16执行嵌套sql最大并行数

比较重要是头几个和后几个，尤其是最后两个，性能提升效果是最明显的。但是会同时开启更多的MR任务，这就需要一个平衡了。

嵌套SQL并行执行优化:

set hive.exec.parallel=true;

set hive.exec.parallel.thread.number=16;

效率可提升至少100%

某job需要11个stage：

非并行35分钟

并行8个job执行10分钟

并行16个job执行6分钟

Hive查询的优化：

一、数据量大的表和数据量小的表做关联的时候，把数据量小的表放到join前面去select。

   原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。

二、Join优化

   Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。

   例：

   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一个mapre程序中执行join

   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在两个mapred程序中执行join

   Map join的关键在于join操作中的某个表的数据量很小

   例：

   SELECT /*+ MAPJOIN(b) */ a.key, a.value  FROM a join b on a.key = b.key

三、用sum() group by的方式来替换count(distinct)。

四、排序优化

   Order by 实现全局排序，一个reduce实现，效率低

   Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字 可以指定map 到 reduce端的分发key）

   CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1.

五、合并小文件

   文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来尽量消除这样的影响

   hive.merge.mapfiles = true是否和并 Map 输出文件，默认为 True

   hive.merge.mapredfiles = false是否合并 Reduce 输出文件，默认为 False

   hive.merge.size.per.task = 256*1000*1000合并文件的大小。

   这里的参数没有写到上面的表格里是因为这是可以根据任务不同临时设置的，而不一定非要是全局设置。有时候全局设置了反而对大文件的操作有性能影响。

六、使用分区，RCFile，lzo，ORCFile等

   Hive中的每个分区都对应hdfs上的一个目录，分区列也不是表中的一个实际的字段，而是一个或者多个伪列，在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区（只有一个），后面的为副分区

   静态分区：静态分区在加载数据和使用时都需要在sql语句中指定

   例：(stat_date='20120625',province='hunan')

   动态分区：使用动态分区需要设置hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置set hive.exec.dynamic.partition.mode=nostrick，默认为strick

   例：(stat_date='20120625',province)

七、使用外部表而尽量少用内部表，这主要从数据的安全性上考量。