hive优化

http://shiyanjun.cn/archives/588.html

 

 

http://www.cnblogs.com/xd502djj/p/3799432.html

 

https://www.2cto.com/net/201708/668075.html

 

http://dacoolbaby.iteye.com/blog/1879002

 

 

基本原则：

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

select... from A

joinB

on A.key= B.key

whereA.userid>10

     andB.userid<10

       and A.dt='20120417'

       and B.dt='20120417';

应该改写为：

select.... from (select .... from A

                  wheredt='201200417'

                                  and userid>10

                             ) a

join (select .... from B

       wheredt='201200417'

                    and userid <10   

     )b

on a.key= b.key;

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

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

droptable if exists tmp_table_1;

createtable if not exists tmp_table_1 as

select......;

 

droptable if exists tmp_table_2;

createtable if not exists tmp_table_2 as

select......;

 

droptable if exists result_table;

createtable if not exists result_table as

select......;

 

droptable if exists tmp_table_1;

droptable if exists tmp_table_2;

 

 

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

 

4：慎重使用mapjoin,一般行数小于2000行，大小小于1M(扩容后可以适当放大)的表才能使用,小表要注意放在join的左边（目前TCL里面很多都小表放在join的右边）。

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

 

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

如果出现数据倾斜，应当做如下处理：

sethive.exec.reducers.max=200;

setmapred.reduce.tasks= 200;---增大Reduce个数

sethive.groupby.mapaggr.checkinterval=100000;--这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置

sethive.groupby.skewindata=true; --如果是group by过程出现倾斜 应该设置为true

sethive.skewjoin.key=100000;--这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置

sethive.optimize.skewjoin=true;--如果是join 过程出现倾斜应该设置为true

 、

Group By 语句

• Map 端部分聚合：
• • 并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce端得出最终结果。
  • 基于 Hash
  • 参数包括：
  • • hive.map.aggr = true 是否在 Map 端进行聚合，默认为True
    • hive.groupby.mapaggr.checkinterval =100000 在 Map 端进行聚合操作的条目数目
• 有数据倾斜的时候进行负载均衡
• • hive.groupby.skewindata = false
  • 当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group ByKey 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce中），最后完成最终的聚合操作。

hive.groupby.skewindata变量

从上面groupby语句可以看出，这个变量是用于控制负载均衡的。当数据出现倾斜时，如果该变量设置为true，那么Hive会自动进行负载均衡。

 

 

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

insertoverwite table tablename partition (dt= ....)

select..... from (

                   select... from A

                   unionall

                   select... from B

                  union all

                   select... from C

                              ) R

where...;

 

可以改写为：

insertinto table tablename partition (dt= ....)

select.... from A

WHERE...;

 

insertinto table tablename partition (dt= ....)

select.... from B

WHERE...;

 

insertinto table tablename partition (dt= ....)

select.... from C

WHERE...; 

 

 

 

hive在跑数据时经常会出现数据倾斜的情况，使的作业经常reduce完成在99%后一直卡住，最后的１%花了几个小时都没跑完，这种情况就很可能是数据倾斜的原因，解决方法要根据具体情况来选择具体的方案

１、join的key值发生倾斜，key值包含很多空值或是异常值

这种情况可以对异常值赋一个随机值来分散key

如：

selectuserid , name

fromuser_info a

join (

select  case  when userid  is  null  then  cast ( rand ( 47 )* 100000  as i nt )

elseuserid

fromuser_read_log

)b  on a . userid  = b . userid

通过rand函数将为null的值分散到不同的值上，在key值比较就能解决数据倾斜的问题

注：对于异常值如果不需要的话，最好是提前过滤掉，这样计算量可以大大减少

2、当key值都是有效值时，解决办法为设置以下几个参数

set hive.exec.reducers.bytes.per.reducer = 1000000000

也就是每个节点的reduce 默认是处理1G大小的数据，如果你的join 操作也产生了数据倾斜，那么你可以在hive 中设定

set hive.optimize.skewjoin = true;

set hive.skewjoin.key = skew_key_threshold （default = 100000）

hive 在运行的时候没有办法判断哪个key 会产生多大的倾斜，所以使用这个参数控制倾斜的阈值，如果超过这个值，新的值会发送给那些还没有达到的reduce, 一般可以设置成你

（处理的总记录数/reduce个数）的2-4倍都可以接受.

倾斜是经常会存在的，一般select 的层数超过2层，翻译成执行计划多于3个以上的mapreduce job 都很容易产生倾斜，建议每次运行比较复杂的sql 之前都可以设一下这个参数. 如果你不知道设置多少，可以就按官方默认的1个reduce 只处理1G 的算法，那么  skew_key_threshold  = 1G/平均行长. 或者默认直接设成250000000 (差不多算平均行长4个字节)

3、reduce数太少

set mapred.reduce.tasks=800;

默认是先设置hive.exec.reducers.bytes.per.reducer这个参数，设置了后hive会自动计算reduce的个数，因此两个参数一般不同时使用

4、对于group by 产生倾斜的问题

set hive.map.aggr=true (开启map端combiner); //在Map端做combiner,假如map各条数据基本上不一样, 聚合没什么意义，做combiner反而画蛇添足,hive里也考虑的比较周到通过参数hive.groupby.mapaggr.checkinterval = 100000 (默认)

hive.map.aggr.hash.min.reduction=0.5(默认)

两个参数的意思是：预先取100000条数据聚合,如果聚合后的条数/100000>0.5，则不再聚合

set hive.groupby.skewindata=true；// 决定  group by 操作是否支持倾斜的数据。注意：只能对单个字段聚合. 控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce做次预汇总,减少某些key值条数过多某些key条数过小造成的数据倾斜问题

5、小表与大表关联

此时，可以通过mapjoin来优化，

set hive.auto. convert . join =  true ; //将小表刷入内存中  

set hive.mapjoin.smalltable.filesize = 2500000 ;//刷入内存表的大小(字节)