hive调优总结

（1）本地模式

hadoop默认会将job提交到YARN集群，如果在测试阶段数据量比较小，可开启本地模式，加快程序执行的速度。
set hive.exec.mode.local.auto=true;

（2）小表join大表

1、保证大表在后，小表在前；
原因：多表join时，hive假定查询中最后一个表是最大的表，在对每行记录进行join操作时，他会尝试将其他表缓存起来，然后扫描最后那个表进行计算。
（这一实现有助于在reduce端减少内存的使用量）

2、开启MapJoin配置
如果所有表中有一个表足够小（默认25M以下是小表），完全可以加载到内存中执行。这时hive可以执行一个map-side join，当大表进行mapper操作时，直接和内存中小表逐一匹配，从而省掉常规join操作所需的reduce过程。

（set hive.auto.convert.join = true,默认开启
set hive.mapjoin.smalltable.filesize=25000000）
（3）大表join大表
有时join时某些key对应的数据太多，而相同key对应的数据会发送到同一个reduce上，从而导致内存不够。

1、空key过滤

针对的是key对应的数据是异常数据，需要在sql中进行过滤掉
普通写法：

insert overwrite table jointable
select n.* from nullidtable n 
left join bigtable b
on n.id = b.id

过滤写法：

insert overwrite table jointable
select n.* from 
(select * from nullidtable where id is not null)n 
left join bigtable b
on n.id = b.id

2、空key转换

针对的是key为空对应的数据很多，但不是异常数据，必须包含在join的结果中。此时我们为表中key为null的字段赋一个随机值，目的就是使数据随机均匀的分发到不同的reduce上。
转换写法：

insert overwrite table jointable
select n.* from nullidtable n 
full join bigtable b on 
case when n.id is null 
then concat('hive',rand())
else n.id = b.id

补充：大表分割

如a表只有一个文件，但包含很多记录，使用一个map处理十分耗时；
优化思路：基于a表创建多个输入文件的临时表后，再基于临时表计算；
创建临时表：

Create table a_tpm as
		Select * from a distribute by rand(123);

通过切分输入文件以增加map数，减少单个map的处理数据量。
（4）group by

select dept,count(name) from test group by dept;

这条sql语句dept作为key，map阶段<dept,name>会将同一dept的数据分发给一个reduce，reduce阶段根据相同的dept计数。当一个key过大时就会造成数据倾斜。

并不是所有的聚合操作都需要在reduce端完成，很多聚合操作都可以先在map端进行部分聚合，最后在reduce端得出结果。

具体做法：开启Map端聚合参数设置

1、是否在map端进行聚合，默认为true
hive.map.aggr=true

2、在map端进行聚合操作的数目：
hive.groupby.mapaggr.checkinterval = 100000

3、有数据倾斜时进行负载均衡（默认是false）
hive.groupby.skewindata = true

当选项为true时，生成的查询计划会有两个MR job。第一个MR job中，Map的输出结果会随机分配到reduce中，每个reduce做部分聚合操作，并输出结果，这样处理的结果是相同的group by key有可能被分发到不同的reduce中，从而达到负载均衡的目的；第二个job再根据预处理的结果按照group by key分布到reduce中（这个过程可以保证相同的key被分发到同一个reduce中），最后完成最终的聚合操作。

（5）count（distinct）去重统计
select count(distinct id) from bigtable;
数据量大的情况下，由于distinct必须要由一个reduce去完成,这一个reduce因为要处理的数据量过大，会导致整个job很难完成。
一般采用先group by再count的方式替换count(distinct)去重：

set mapreduce.job.reduces = 5;
select count(id) from (select id from bigtable group by id)A;

子查询会负责去重工作，去重工作会分给5个reduce去做；

(6)行列过滤：
列处理：只拿需要的列，少用select *；
知乎

行处理：当多表join时，如果将副表的过滤条件写在where后面，就会出现先全表关联，之后再过滤。比如：

select A.id from A join B on A.id=B.id where A.id<=10;

优化策略是：先通过子查询后，再关联表

select C.id from B join (select id from A where A.id<=10)C
on C.id=B.id