hive join优化点(持续更新)

left semi join 代替 IN 字句的

select m.* from 大表1 m where m.ID in (select l.ID from 小表2 l);  -- 性能非常差，使用left semi join代替

select m.* from 大表1 m left semi join 小表2 l on m.ID = l.ID limit 10;
--但是 小表2 中的字段不能在where字句和select字句中出现，比如：下面的两个语句都会报错：
select m.*, l.* from 大表1 m left semi join 小表2 l on m.ID= l.ID limit 10;
select m.* from 大表1  m left semi join 小表2 l on m.ID= l.ID where l.url like "%234%" limit 10;

map-side join

将数据量比较小的表，直接缓存在内存，然后在map段完成join，性能提高不少
第一种方式，比较老的方式

select /*+ MAPJOIN(小表2)*/  m.*, l.* from 大表1 m join 小表2l on m.ID= l.ID limit 10;

第二种方式，设置参数的方式：

set hive.auto.convert.join=true;
select m.*, l.* from 大表1 m join 小表2 l on m.ID= l.ID limit 10;

bucket map join

当两张join的表都很大的时候使用这种方式(两个bucket表)
两张表对join字段进行分bucket，且buckets的数量是倍数关系，然后使用bucket map join性能会很高

set hive.optimize.bucketmapjoin=true;
select /*+ MAPJOIN(小表2)*/  m.*, l.* from 大表1 m join 小表2 l on m.ID = l.ID limit 10;

bucket sort merge map join

当数据在bucket中是按照顺序排列的，且两张表的相同的字段的buckets的数量是一样多的，则可以使用这种方式的join

set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
select /*+ MAPJOIN(小表2)*/  m.*, l.* from 大表1 m join 小表2 l on m.ID= l.ID limit 10;