1. left semi join
let semi join 只是hive的一种join。
Left Semi-Join是可以高效实现IN/EXISTS子查询的语义。Hive本身是不支持exist和in语句的,以下SQL语义:
(1)SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM b);
未实现Left Semi-Join之前,Hive实现上述语义的语句是:
hive> select a.key from a left outer join (select distinct key from b) t on a.key = t.key where t.key is not null;
<span style="color:#ff0000;"><strong>Total MapReduce jobs = 3</strong></span>
(2)可被替换为Left Semi-Join如下:
hive> select a.key from a left semi join t on a.key = t.key;
<span style="color:#ff0000;"><strong>Total MapReduce jobs = 1</strong></span>
这一实现减少至少1次MapReuduce过程,注意Left Semi-Join的Join条件必须是等值,本例中减少了两个。
Map/Reduce 实现:两个表数据都流向Reduce端之后,只要判断某个join key的group(iterator)里面是否带有tag 为b表的记录,如果有显示exist。所以Map/Reduce 实现起来比较简单。
2. 空值处理
如果两个表join时,某个表的join key 上有大量空值,这样会造成reduce skew
解决方法一,在join前先过滤掉
select a.key,b.key from a join b on (a.key = b.key and b.key is not null)
解决方法二,把空值变为随机值
select a.uid from a aleft outer join b b.uid = case when a.uid is null or length(a.uid)=0 then concat('rd_sid',rand()) else a.uid end;