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

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

=======================================================================================

一、join优化

Join查找操作的基本原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。

Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。

案例：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一个mapre程序中执行join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在两个mapred程序中执行join

 

Map join的关键在于join操作中的某个表的数据量很小，案例：

SELECT /*+ MAPJOIN(b) */ a.key, a.value

  FROM a join b on a.key = b.key 

Mapjoin 的限制是无法执行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数：hive.join.emit.interval  hive.mapjoin.size.key  hive.mapjoin.cache.numrows

由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改为：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

在join操作的每一个mapred程序中，hive都会把出现在join语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中。当然，也可以手动指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

 

 

二、group by 优化

Map端聚合，首先在map端进行初步聚合，最后在reduce端得出最终结果，相关参数：

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

· hive.groupby.mapaggr.checkinterval = 100000在 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 中），最后完成最终的聚合操作。

 

三、合并小文件

文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

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

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

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

四、Hive实现(not) in

通过left outer join进行查询,（假设B表中包含另外的一个字段 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通过left semi join 实现 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join条件中右边的表只能出现在join条件中。

 

五、排序优化

Order by 实现全局排序，一个reduce实现，效率低

Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字 可以指定map 到 reduce端的分发key）

CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1

 

六、使用分区

Hive中的每个分区都对应hdfs上的一个目录，分区列也不是表中的一个实际的字段，而是一个或者多个伪列，在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区（只有一个），后面的为副分区

静态分区：静态分区在加载数据和使用时都需要在sql语句中指定

          案例：(stat_date='20120625',province='hunan')

动态分区：使用动态分区需要设置hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置set hive.exec.dynamic.partition.mode=nostrick，默认为strick

          案例：(stat_date='20120625',province)

 

七、Distinct 使用

Hive支持在group by时对同一列进行多次distinct操作，却不支持在同一个语句中对多个列进行distinct操作。

 

八、Hql使用自定义的mapred脚本

注意事项：在使用自定义的mapred脚本时，关键字MAP REDUCE 是语句SELECT TRANSFORM ( ... )的语法转换，并不意味着使用MAP关键字时会强制产生一个新的map过程，使用REDUCE关键字时会产生一个red过程。

自定义的mapred脚本可以是hql语句完成更为复杂的功能，但是性能比hql语句差了一些，应该尽量避免使用，如有可能，使用UDTF函数来替换自定义的mapred脚本

 

九、UDTF

UDTF将单一输入行转化为多个输出行，并且在使用UDTF时，select语句中不能包含其他的列，UDTF不支持嵌套，也不支持group by 、sort by等语句。如果想避免上述限制，需要使用lateral view语法，案例：

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.*

from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

 

其中，get_json_object为UDF函数，json_tuple为UDTF函数。

UDTF函数在某些应用场景下可以大大提高hql语句的性能，如需要多次解析json或者xml数据的应用场景。

 

十、聚合函数count和sum

Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了，但是在hive中count函数在计算distinct value时支持加入条件过滤。