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

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分区键



//------------------------------------------------------------------------------------------------

hive (mysql_anqu_chi)> select word, app, priority, searchapp from ansearchapp LATERAL VIEW OUTER explode(searchapp) s AS app where app = "1078801486" and app = "1119823457" order by priority desc;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = mysql1_20160802021656_8616bf73-55c1-48cf-8d0e-7df08e5d9aaf
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 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 mapreduce.job.reduces=<number>
Interrupting... Be patient, this might take some time.

//--
hive (mysql_anqu_chi)> set hive.exec.reducers.max=200;
hive (mysql_anqu_chi)> set mapreduce.job.reduces=3;

Hive常用Job配置 & Map,Reduce数量控制 ----------------------:

开启动态分区：
hive.exec.dynamic.partition=true
默认值：false
描述：是否允许动态分区
hive.exec.dynamic.partition.mode=nonstrict
默认值：strict
描述：strict是避免全分区字段是动态的，必须有至少一个分区字段是指定有值的。
读取表的时候可以不指定分区。

设置如下参数配置动态分区的使用环境：
hive.exec.max.dynamic.partitions.pernode=100
默认值：100
描述：each mapper or reducer可以创建的最大动态分区数
hive.exec.max.dynamic.partitions=1000
默认值：1000
描述：一个DML操作可以创建的最大动态分区数
hive.exec.max.created.files=100000
默认值：100000
描述：一个DML操作可以创建的文件数

设置如下参数取消一些限制(HIVE 0.7后没有此限制)：
hive.merge.mapfiles=false
默认值：true
描述：是否合并Map的输出文件，也就是把小文件合并成一个map
hive.merge.mapredfiles=false
默认值：false
描述：是否合并Reduce的输出文件，也就是在Map输出阶段做一次reduce操作，再输出

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

这个参数表示执行前进行小文件合并，
前面三个参数确定合并文件块的大小，大于文件块大小128m的，
按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），
进行合并,最终生成了74个块。

自0.7版本后Hive开始支持任务执行选择本地模式(local mode)，如此一来，对数据量比较小的操作，就可以在本地执行，这样要比提交任务到集群执行效率要快很多。
配置如下参数，可以开启Hive的本地模式：
hive> set hive.exec.mode.local.auto=true;(默认为false)

set mapred.reduce.tasks;
设置当前Session的map,reduce 的个数，默认值是-1，为系统自动匹配。
匹配原则是按照block的个数来启动map。

但并非map的数量越多越好，当有大量的小文件的时候，反而适合一个map负责读取多个文件对象。
因为每启动一个map都是一个开销。

一、控制hive任务中的map数:

1.通常情况下，作业会通过input的目录产生一个或者多个map任务。
主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小(目前为128M, 可在hive中通过set dfs.block.size;命令查看到，该参数不能自定义修改)；

2.举例：
a)假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数
b)假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m）,从而产生4个map数
即，如果文件大于块大小(128m),那么会拆分，如果小于块大小，则把该文件当成一个块。

3.是不是map数越多越好？
答案是否定的。如果一个任务有很多小文件（远远小于块大小128m）,则每个小文件也会被当做一个块，用一个map任务来完成，
而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。
而且，同时可执行的map数是受限的。

4.是不是保证每个map处理接近128m的文件块，就高枕无忧了？
答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，
但这个文件只有一个或者两个小字段，却有几千万的记录，
如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的问题3和4，我们需要采取两种方式来解决：即减少map数和增加map数；

如何合并小文件，减少map数？
 假设一个SQL任务：
 Select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’;
 该任务的inputdir  /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04
 共有194个文件，其中很多是远远小于128m的小文件，总大小9G，正常执行会用194个map任务。
 Map总共消耗的计算资源： SLOTS_MILLIS_MAPS= 623,020

 我通过以下方法来在map执行前合并小文件，减少map数：
 set mapred.max.split.size=100000000;
 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;
 再执行上面的语句，用了74个map任务，map消耗的计算资源：SLOTS_MILLIS_MAPS= 333,500
 对于这个简单SQL任务，执行时间上可能差不多，但节省了一半的计算资源。
 大概解释一下，100000000表示100M,
 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
 这个参数表示执行前进行小文件合并，
 
 前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，
 小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），
 进行合并,最终生成了74个块。
        
如何适当的增加map数？

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，
来使得每个map处理的数据量减少，从而提高任务的执行效率。

 假设有这样一个任务：
 Select data_desc,
  count(1),
  count(distinct id),
  sum(case when …),
  sum(case when ...),
  sum(…)
from a group by data_desc

如果表a只有一个文件，大小为120M，但包含几千万的记录，
如果用1个map去完成这个任务，肯定是比较耗时的，
这种情况下，我们要考虑将这一个文件合理的拆分成多个，
这样就可以用多个map任务去完成。

   set mapred.reduce.tasks=10;
   create table a_1 as
   select * from a
   distribute by rand(123);
                  
这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。
每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。
   
看上去，貌似这两种有些矛盾，一个是要合并小文件，一个是要把大文件拆成小文件，这点正是重点需要关注的地方，
根据实际情况，控制map数量需要遵循两个原则：使大数据量利用合适的map数；使单个map任务处理合适的数据量；

二、控制hive任务的reduce数：

1.Hive自己如何确定reduce数：
reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会猜测确定一个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任务；
如：
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;
 /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04 总大小为9G多，
 因此这句有10个reduce

2.调整reduce个数方法一：
调整hive.exec.reducers.bytes.per.reducer参数的值；
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 这次有20个reduce
        
3.调整reduce个数方法二；
set mapred.reduce.tasks = 15;
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;这次有15个reduce

4.reduce个数并不是越多越好；
同map一样，启动和初始化reduce也会消耗时间和资源；
另外，有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，
那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

5.什么情况下只有一个reduce；
很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；
其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：
a)没有group by的汇总，比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;
写成 select count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04';
这点非常常见，希望大家尽量改写。
b)用了Order by
c)有笛卡尔积
通常这些情况下，除了找办法来变通和避免，我暂时没有什么好的办法，
因为这些操作都是全局的，所以hadoop不得不用一个reduce去完成；

同样的，在设置reduce个数的时候也需要考虑这两个原则：
使大数据量利用合适的reduce数；使单个reduce任务处理合适的数据量。

hive.exec.parallel参数控制在同一个sql中的不同的job是否可以同时运行,默认为false.

下面是对于该参数的测试过程:
测试sql:
select r1.a
from (
   select t.a from sunwg_10 t join sunwg_10000000 s on t.a=s.b) r1
   join
   (select s.b from sunwg_100000 t join sunwg_10 s on t.a=s.b) r2
   on (r1.a=r2.b);

当参数为false的时候,三个job是顺序的执行
set hive.exec.parallel=false;

但是可以看出来其实两个子查询中的sql并无关系,可以并行的跑
set hive.exec.parallel=true;

总结:
在资源充足的时候hive.exec.parallel会让那些存在并发job的sql运行得更快,
但同时消耗更多的资源可以评估下hive.exec.parallel对我们的刷新任务是否有帮助.