how to write subquery and use "In" Clause in Hive

Question

How can I use In clause in Hive I want to write something like this in Hive select x from y where y.z in (select distinct z from y) order by x; But I am not finding any way of doing it.. I tried In clause in Hive 0.7 it was throwing error, Also I tried Find_in_Set .. using find_in_set(y.z,subquery).. but the job is getting failed.

I want to do this on Hive. Please help me if anybody knows how to do this in Hive..

Thanks & Regards, Atul

Answer 1

You can use semi join(https://cwiki.apache.org/Hive/languagemanual-joins.html):

LEFT SEMI JOIN implements the correlated IN/EXISTS subquery semantics in an efficient way. Since Hive currently does not support IN/EXISTS subqueries, you can rewrite your queries using LEFT SEMI JOIN. The restrictions of using LEFT SEMI JOIN is that the right-hand-side table should only be referenced in the join condition (ON-clause), but not in WHERE- or SELECT-clauses etc.

SELECT a.key, a.value
  FROM a
  WHERE a.key in
   (SELECT b.key
    FROM B);

can be rewritten to:

   SELECT a.key, a.val
   FROM a LEFT SEMI JOIN b on (a.key = b.key)

Answer 2

Hive 0.13 now do support IN/EXISTS in the WHERE-clause .. The issue https://issues.apache.org/jira/browse/HIVE-784 has been resolved after 4 years :)