hive sql优化中的那些参数和使用方式

自动化 MapJoin

set hive.auto.convert.join=true;

自动化了 MapJoin，之后我们就不需要在 query 中再写了。MapJoin 用于小表 Join 大表的场景，在大表通过 Mapper 时，小表会被完全放入内存中，Hive 会在 map 端进行连接，因为 Hive 可以和内存中的小表进行逐一匹配，从而省略掉 reduce 过程。

任务并行

set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=100;

参数 hive.exec.parallel 控制着同一个 sql 中是否允许并行执行，默认为 false，设置为 true 则开启任务的并行执行。并通过设置 hive.exec.parallel.thread.number 来指定同一个 sql 允许并行运行的最大线程数。

设置超时时间

set mapred.task.timeout=600000000;

这里设置的是 Job 运行多久后会因超时而被 kill 掉，根据数据量而定。我目前工作中处理的数据有上亿条，维度近千，所以设置得比较大。

“健康监测”

set mapred.healthChecker.script.timeout=600000000;

这个参数的作用是，若检测到脚本在一定时间内无响应，那么 NodeHealthCheckerService 线程会将该节点的监控状态标注为 “unhealthy”。但 Hadoop 2.x 以后，参数 mapred.healthChecker.script.timeout 的名字已经改为 mapreduce.tasktracker.healthchecker.script.timeout，虽然原名还可用，最好还是使用新的参数名。

设置容量大小

set hive.tez.container.size=10240;

Tez 是 YARN 的作业模式，hive.tez.container.size 参数可设置堆的大小，即增大内存容量。由于 D12 计算机具有 28GB 内存，因此我们常使用 10GB (10240MB) 大小的容器。

设置聚集操作执行时机

set hive.map.aggr=true;

若将该参数设置为 true，则会在 mapper 端先进行 group by，然后执行 merge 操作，减少 reduce 要处理的数据量，效率更高但需要更多内存。若将该参数设置为 false，则把前面的步骤放在了 reduce 阶段再进行。设置 reduce 个数set mapred.reduce.tasks=500;该参数用于设置 reduce 的任务个数，设置为500，即生成500个文件。根据具体数据量进行调整，使得单个 map 能处理合适的数据量。

有数据倾斜的时候进行负载均衡

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 中），最后完成最终的聚合操作。

小文件合并

主要问题是小文件太多，单个DataNode的block数量阈值是500,000，而现在单个DataNode的block为2,631,218，约为阈值的5倍，现在所有DataNode都处于黄色不健康状态。
小文件问题会直接带来NameNode的压力巨大，从而导致HDFS的稳定性，同时对HDFS日常的数据读写带来性能下降。目前已可以看到集群的NameNode频繁出现检查点告警问题。
解决此问题的方法主要为两个方面；一是从源头解决小文件问题，在导数的过程中对作业进行优化，以减少小文件的输出，此方法需要业务方解决；二是合并平台上已有的小文件；下面描写合并平台小文件的方案。

1、新建备表，表结构与原表保持一致

create table test_part_bak like test_part; 

2、设置如下参数,使支持合并（这是Map/Reduce输出合并）

## 是否合并Map输出文件, 默认值为true
SET hive.merge.mapfiles = true; 
## 是否合并Reduce端输出文件,默认值为false
SET hive.merge.mapredfiles = true;
## 合并文件的大小,默认值为256000000
SET hive.merge.size.per.task = 256000000;
## 当输出文件的平均大小小于128M时，启动一个独立的map-reduce任务进行文件merge
SET hive.merge.smallfiles.avgsize = 134217728;
## 默认false，是否对输出结果压缩
SET hive.exec.compress.output = true;
## 压缩格式设置
SET parquet.compression = snappy;
## 开启动态分区
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.dynamic.partition = true;

3、使用insert overwrite语句查询原表数据覆盖备表

insert overwrite table test_part_bak partition(date_str) select * from test_part;

4、删除原表，将备用表表名修改为原表名

alter table test_part_bak rename to test_part;