hive优化使用技巧

参考：http://itindex.net/detail/53619-hive-优化

Hive是将符合SQL语法的字符串解析生成可以在Hadoop上执行的MapReduce的工具。使用Hive尽量按照分布式计算的一些特点来设计sql，和传统关系型数据库有区别，所以需要去掉原有关系型数据库下开发的一些固有思维。

基本原则：

1：尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段。
join时，对于where条件，可以使用子查询设计join。
例如：
select .. from (select .. from ..) A join (select .. from ..) B on ..

2：尽量原子化操作，尽量避免一个SQL包含复杂逻辑,可以使用中间表来完成复杂的逻辑.

3：单个SQL所起的JOB个数尽量控制在5个以下.

4：慎重使用mapjoin,一般行数小于2000行，大小小于1M(扩容后可以适当放大)的表才能使用,小表要注意放在join的左边，否则会引起磁盘和内存的大量消耗.

5：写SQL要先了解数据本身的特点，如果有join ,group操作的话，要注意是否会有数据倾斜,如果出现数据倾斜，应当做如下处理：

set hive.exec.reducers.max=200;
set mapred.reduce.tasks= 200;---增大Reduce个数
set hive.groupby.mapaggr.checkinterval=100000 ;--这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
set hive.groupby.skewindata=true; --如果是group by过程出现倾斜 应该设置为true
set hive.skewjoin.key=100000; --这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
set hive.optimize.skewjoin=true;--如果是join 过程出现倾斜 应该设置为true

6：如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%.

insert into table ..
select .. from (
   select .. from A
    union all 
   select .. from B
    union all 
   select .. from C
)

改为：

insert into table ..
select .. from A

insert into table ..
select .. from B

insert into table ..
select .. from C

7: 如何合并小文件，减少map数？
如果一个表中的map数特别多，可能是由于文件个数特别多，而且文件特别小照成的，可以进行如下操作，合并文件：

set mapred.max.split.size=100000000; // 100M
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; // 合并小文件

但是，当map的业务逻辑很复杂时，需要释放增加map数。可以将table进行随机分布，使用新的table代替原表。

set mapred.reduce.tasks=10; 
create table temp as  
select * from a  
distribute by rand(123);

8: hive如何确定reduce数， reduce的个数基于以下参数设定：
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1)
即，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务；所以调整以下参数：

set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
set mapred.reduce.tasks = 15;

9:  Count(distinct)

当count distinct 的记录非常多的时候，设置以下两个参数：

hive.map.aggr = true
set hive.groupby.skewindata=true;

10: Group by

Group By的方法是在reduce做一些操作，这样会导致两个问题：

map端聚合，提前一部分计算：hive.map.aggr = true,

同时设置间隔：hive.groupby.mapaggr.checkinterval

均衡处理：hive.groupby.skewindata

这是针对数据倾斜的，设为ture的时候，任务的reduce会把原来一个job拆分成两个，第一个的job中reduce处理处理不同的随即分发过来的key的数据，生成中间结果，再由最后一个综合处理。

11: Order by, Sort by ,Dristribute by,Cluster By

order by VS Sort by： order by是在全局的排序，只用一个reduce去跑，所以在set hive.mapred.mode=strict 模式下，order by 必须limit，否则报错。Sort by只保证同一个reduce下排序正确。

Distribute by with sort by: Distribute by 是按指定的列把map 输出结果分配到reduce里。所以经常和sort by 来实现对某一字段的相同值分配到同一个reduce排序。

Cluster by 实现了Distribute by+ sort by 的功能.

12: 合并MapReduce操作
Multi-group by
Multi-group by是Hive的一个非常好的特性，它使得Hive中利用中间结果变得非常方便。例如:

FROM (SELECT a.status, b.school, b.gender

FROM status_updates a JOIN profiles b

ON (a.userid = b.userid and

a.ds='2009-03-20' )

) subq1

INSERT OVERWRITE TABLE gender_summary

PARTITION(ds='2009-03-20')

SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender

INSERT OVERWRITE TABLE school_summary

PARTITION(ds='2009-03-20')

SELECT subq1.school, COUNT(1) GROUP BY subq1.school

上述查询语句使用了Multi-group by特性连续group by了2次数据，使用不同的group by key。这一特性可以减少一次MapReduce操作。

13: 参数调整
http://itindex.net/detail/53620-hive-%E4%BC%98%E5%8C%96