您的足迹:首页 > Hive >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分区键

摘自  http://blog.csdn.net/joe_007/article/details/8987422

本博客所有文章如无特别注明均为原创。作者:数据为王复制或转载请以超链接形式注明转自 数据为王
原文地址《hive的查询注意事项以及优化总结 .

相关推荐


  • blogger

发表评论

路人甲 表情
看不清楚?点图切换 Ctrl+Enter快速提交

网友评论(0)