Hive 表优化

小表Join大表

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率。

说明：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。

示例：

insert overwrite table jointable
select b.*
from  smalltable s left join bigtable b 
on b.id =s.id

大表Join小表

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。

示例：舍弃key为空的字段

insert overwrite table jointable
select n.*
from (select * from nullidtable where id is not null) n left join bigtable b
on b.id =n.id;

有时虽然空key对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。 示例：

insert overwrite table jointable
select n.*
from nullidtable n left join bigtable b
on case when n.id is null then  concat('id',rand()) else n.id end =b.id;

附：case语句语法：

CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END

a为真返回b；c为真返回d；否则返回e

MapJoin

如果不指定MapJoin，那么Hive解析器会将Join操作转换成Common Join，从而在Reduce阶段完成join。 这样很容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。 开启MapJoin的参数设置：

• 设置自动选择Mapjoin set hive.auto.convert.join = true; 默认为true
• 大表小表的阈值设置（默认25M以下是小表）： set hive.mapjoin.smalltable.filesize=25000000;

Group By

默认情况下，Map阶段同一Key的数据分发给一个reduce，当一个key数据过大时会发生数据倾斜。 其实，并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

开启Map端聚合的参数设置：

• 是否在Map端进行聚合，默认为True hive.map.aggr = true
• 在Map端进行聚合操作的条目数目 hive.groupby.mapaggr.checkinterval = 100000
• 有数据倾斜的时候进行负载均衡（默认是false） 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中），最后完成最终的聚合操作。

Count(Distinct)去重统计

当数据量很大的时候，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这个Reduce需要处理的数据量太大，就会导致整个Job很难完成。 一般COUNT DISTINCT使用先GROUP BY再COUNT的方式。

示例：

行列过滤

列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。 行处理：在分区查找时，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，建议优先使用子查询的方式。

示例：

select b.id
from bigtable b join (select id from smalltable where id