Hive的查询注意事项以及优化总结

[[187643]]

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

基本原则：

1：尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段

select ... from A 
 
join B 
 
on A.key = B.key 
 
where A.userid>10 
 
     and B.userid<10 
 
        and A.dt='20120417' 
 
        and B.dt='20120417'; 
 
应该改写为： 
 
select .... from (select .... from A 
 
                  where dt='201200417' 
 
                                    and userid>10 
 
                              ) a 
 
join ( select .... from B 
 
       where dt='201200417' 
 
                     and userid < 10    
 
     ) b 
 
on a.key = b.key; 

2、对历史库的计算经验 (这项是说根据不同的使用目的优化使用方法)

历史库计算和使用，分区

3：尽量原子化操作，尽量避免一个SQL包含复杂逻辑

可以使用中间表来完成复杂的逻辑

4 jion操作 小表要注意放在join的左边(目前TCL里面很多都小表放在join的右边)。

否则会引起磁盘和内存的大量消耗

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

insert overwite table tablename partition (dt= ....) 
 
select ..... from ( 
 
                   select ... from A 
 
                   union all 
 
                   select ... from B 
 
                   union all 
 
                   select ... from C 
 
                               ) R 
 
where ...; 

可以改写为：

insert into table tablename partition (dt= ....) 
 
select .... from A 
 
WHERE ...; 
 
 
insert into table tablename partition (dt= ....) 
 
select .... from B 
 
WHERE ...; 
 
  
 
insert into table tablename partition (dt= ....) 
 
select .... from C 
 
WHERE ...;  

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

(1) 启动一次job尽可能的多做事情，一个job能完成的事情,不要两个job来做

通常来说前面的任务启动可以稍带一起做的事情就一起做了,以便后续的多个任务重用,与此紧密相连的是模型设计,好的模型特别重要.

(2) 合理设置reduce个数

reduce个数过少没有真正发挥hadoop并行计算的威力，但reduce个数过多，会造成大量小文件问题，数据量、资源情况只有自己最清楚，找到个折衷点,

(3) 使用hive.exec.parallel参数控制在同一个sql中的不同的job是否可以同时运行，提高作业的并发

2、让服务器尽量少做事情，走***的路径，以资源消耗最少为目标

比如:

(1) 注意join的使用

若其中有一个表很小使用map join，否则使用普通的reduce join，注意hive会将join前面的表数据装载内存,所以较小的一个表在较大的表之前,减少内存资源的消耗

(2)注意小文件的问题

在hive里有两种比较常见的处理办法

***是使用Combinefileinputformat，将多个小文件打包作为一个整体的inputsplit，减少map任务数

set mapred.max.split.size=256000000; 
set mapred.min.split.size.per.node=256000000 
set Mapred.min.split.size.per.rack=256000000 
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat 

第二是设置hive参数，将额外启动一个MR Job打包小文件

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

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

(3)注意数据倾斜

在hive里比较常用的处理办法

***通过hive.groupby.skewindata=true控制生成两个MR Job,***个MR Job Map的输出结果随机分配到reduce做次预汇总,减少某些key值条数过多某些key条数过小造成的数据倾斜问题

第二通过hive.map.aggr = true(默认为true)在Map端做combiner,假如map各条数据基本上不一样, 聚合没什么意义，做combiner反而画蛇添足,hive里也考虑的比较周到通过参数hive.groupby.mapaggr.checkinterval = 100000 (默认)hive.map.aggr.hash.min.reduction=0.5(默认),预先取100000条数据聚合,如果聚合后的条数/100000>0.5，则不再聚合。

(4)善用multi insert,union all

multi insert适合基于同一个源表按照不同逻辑不同粒度处理插入不同表的场景，做到只需要扫描源表一次，job个数不变，减少源表扫描次数

union all用好，可减少表的扫描次数，减少job的个数,通常预先按不同逻辑不同条件生成的查询union all后，再统一group by计算,不同表的union all相当于multiple inputs,同一个表的union all,相当map一次输出多条

(5) 参数设置的调优

集群参数种类繁多,举个例子比如

可针对特定job设置特定参数,比如jvm重用,reduce copy线程数量设置(适合map较快，输出量较大)

如果任务数多且小，比如在一分钟之内完成，减少task数量以减少任务初始化的消耗。可以通过配置JVM重用选项减少task的消耗

一、控制Hive中Map和reduce的数量

Hive中的sql查询会生成执行计划，执行计划以MapReduce的方式执行，那么结合数据和集群的大小，map和reduce的数量就会影响到sql执行的效率。

除了要控制Hive生成的Job的数量，也要控制map和reduce的数量。

1、 map的数量，通常情况下和split的大小有关系，之前写的一篇blog“map和reduce的数量是如何定义的”有描述。

hive中默认的hive.input.format是org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，对于combineHiveInputFormat,它的输入的map数量

由三个配置决定，

mapred.min.split.size.per.node， 一个节点上split的至少的大小

mapred.min.split.size.per.rack 一个交换机下split至少的大小

mapred.max.split.size 一个split***的大小

它的主要思路是把输入目录下的大文件分成多个map的输入, 并合并小文件, 做为一个map的输入. 具体的原理是下述三步:

a、根据输入目录下的每个文件,如果其长度超过mapred.max.split.size,以block为单位分成多个split(一个split是一个map的输入),每个split的长度都大于mapred.max.split.size, 因为以block为单位, 因此也会大于blockSize, 此文件剩下的长度如果大于mapred.min.split.size.per.node, 则生成一个split, 否则先暂时保留.

b、现在剩下的都是一些长度效短的碎片,把每个rack下碎片合并, 只要长度超过mapred.max.split.size就合并成一个split, ***如果剩下的碎片比mapred.min.split.size.per.rack大, 就合并成一个split, 否则暂时保留.

c、把不同rack下的碎片合并, 只要长度超过mapred.max.split.size就合并成一个split, 剩下的碎片无论长度, 合并成一个split.

举例: mapred.max.split.size=1000

mapred.min.split.size.per.node=300  
mapred.min.split.size.per.rack=100 

输入目录下五个文件,rack1下三个文件,长度为2050,1499,10, rack2下两个文件,长度为1010,80. 另外blockSize为500.

经过***步, 生成五个split: 1000,1000,1000,499,1000. 剩下的碎片为rack1下:50,10; rack2下10:80

由于两个rack下的碎片和都不超过100, 所以经过第二步, split和碎片都没有变化.

第三步,合并四个碎片成一个split, 长度为150.

如果要减少map数量, 可以调大mapred.max.split.size, 否则调小即可.

其特点是: 一个块至多作为一个map的输入，一个文件可能有多个块，一个文件可能因为块多分给做为不同map的输入， 一个map可能处理多个块，可能处理多个文件。

2、 reduce数量

可以在hive运行sql的时，打印出来，如下：

Number of reduce tasks not specified. Estimated from input data size: 1  
In order to change the average load for a reducer (in bytes):  
set hive.exec.reducers.bytes.per.reducer=<number>  
In order to limit the maximum number of reducers:  
set hive.exec.reducers.max=<number>  
In order to set a constant number of reducers:  
set mapred.reduce.tasks=<number> 

reduce数量由以下三个参数决定，

mapred.reduce.tasks(强制指定reduce的任务数量)

hive.exec.reducers.bytes.per.reducer(每个reduce任务处理的数据量，默认为1000^3=1G)

hive.exec.reducers.max(每个任务***的reduce数，默认为999)

计算reducer数的公式很简单N=min( hive.exec.reducers.max ，总输入数据量/ hive.exec.reducers.bytes.per.reducer )

只有一个reduce的场景：

a、没有group by 的汇总

b、order by

c、笛卡尔积

二、join和Group的优化

对于普通的join操作，会在map端根据key的hash值，shuffle到某一个reduce上去，在reduce端做join连接操作，内存中缓存join左边的表，遍历右边的表，一次做join操作。所以在做join操作时候，将数据量多的表放在join的右边。

当数据量比较大，并且key分布不均匀，大量的key都shuffle到一个reduce上了，就出现了数据的倾斜。

对于Group操作，首先在map端聚合，***在reduce端坐聚合，hive默认是这样的，以下是相关的参数

· hive.map.aggr = true是否在 Map 端进行聚合，默认为 True

· hive.groupby.mapaggr.checkinterval = 100000在 Map 端进行聚合操作的条目数目

对于join和Group操作都可能会出现数据倾斜。

三、以下有几种解决这个问题的常见思路

1、参数hive.groupby.skewindata = true,解决数据倾斜的***钥匙，查询计划会有两个 MR Job。***个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中)，***完成最终的聚合操作。

2、where的条件写在join里面，使得减少join的数量(经过map端过滤，只输出复合条件的)

3、mapjoin方式，无reduce操作，在map端做join操作(map端cache小表的全部数据)，这种方式下无法执行Full/RIGHT OUTER join操作

4、对于count(distinct)操作，在map端以group by的字段和count的字段联合作为key，如果有大量相同的key，那么会存在数据倾斜的问题

5、数据的倾斜还包括，大量的join连接key为空的情况，空的key都hash到一个reduce上去了，解决这个问题，***把空的key和非空的key做区分

空的key不做join操作。

当然有的hive操作，不存在数据倾斜的问题，比如数据聚合类的操作，像sum、count，因为已经在map端做了聚合操作了，到reduce端的数据相对少一些，所以不存在这个问题。

四、小文件的合并

大量的小文件导致文件数目过多，给HDFS带来压力，对hive处理的效率影响比较大，可以合并map和reduce产生的文件

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

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

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

五、in/exists(not)

通过left semi join 实现 in操作，一个限制就是join右边的表只能出现在join条件中

六、分区裁剪

通过在条件中指定分区，来限制数据扫描的范围，可以极大提高查询的效率

七、排序

order by 排序，只存在一个reduce，这样效率比较低。

可以用sort by操作,通常结合distribute by使用做reduce分区键