hive用left semi join替代in子查询的方式

原文  http://blog.csdn.net/yeweiouyang/article/details/41645315

执行如下hive sql：

select * from trackinfo 
where ds=$date and session_id in (select session_id from rcmd_track_path where ds=$date and add_cart_flag>0 and product_id>0);</span>

提示报错如下：

FAILED: ParseException line 2:39 cannot recognize input near 'select' 'session_id' 'from' in expression specification

原因分析 & 解决方案 如下：

hive不支持in 子查询的用法，可以考虑用left semi join的方式来替换in，对上面的hive sql改写如下：

select * 
from trackinfo t1 
left semi join rcmd_track_path t2 
on (t1.session_id=t2.session_id and t2.add_cart_flag>0 and t2.product_id>0 and t1.ds=$date and t2.ds=$date);

left semi join用法参考：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins