blocks|key|762537|text|根据https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BSelect的说法|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|762538|“配置单元不支持WHERE子句中的IN、EXISTS或子查询。”|762539|您可能需要查看：https://issues.apache.org/jira/browse/HIVE-801|762540|https://issues.apache.org/jira/browse/HIVE-1799|762541|entityMap|0|LINK|mutability|MUTABLE|url|https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BSelect|1|https://issues.apache.org/jira/browse/HIVE-801|2^0|2|1Y|0|0|0|8|1A|1|0|0|1B|2|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|J|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]|R|$5|M|N|O|C|$P|S]]|T|$5|M|N|O|C|$P|I]]]]

According to <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select" rel="noreferrer">https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select</a>:

"Hive does not support IN, EXISTS or subqueries in the WHERE clause."

You might want to look at:
<a href="https://issues.apache.org/jira/browse/HIVE-801" rel="noreferrer">https://issues.apache.org/jira/browse/HIVE-801</a>

<a href="https://issues.apache.org/jira/browse/HIVE-1799" rel="noreferrer">https://issues.apache.org/jira/browse/HIVE-1799</a>

blocks|key|545947|text|蜂巢完全支持IN+..。它不支持WHERE子句中的子查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|545948|Facebook工程师从4年来就有了一个开放的功能门票...https://issues.apache.org/jira/browse/HIVE-784?focusedCommentId=13579059|offset|length|545949|entityMap|0|LINK|mutability|MUTABLE|url|https://issues.apache.org/jira/browse/HIVE-784?focusedCommentId=13579059^0|0|U|20|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Hive supports perfectly the IN ... it does not support the subquery in the WHERE clause

there is an open feature ticket from Facebook engineers since 4 years...
<a href="https://issues.apache.org/jira/browse/HIVE-784?focusedCommentId=13579059" rel="nofollow">https://issues.apache.org/jira/browse/HIVE-784?focusedCommentId=13579059</a>

blocks|key|546033|text|您可以使用semi+join(https://cwiki.apache.org/Hive/languagemanual-joins.html)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|546034|左半连接以一种有效的方式实现了相关的IN/EXISTS子查询语义。由于配置单元目前不支持IN/EXISTS子查询，因此您可以使用LEFT+SEMI+JOIN重写查询。使用LEFT+SEMI+JOIN的限制是只能在join条件(ON-子句)中引用右侧表，而不能在WHERE-或SELECT-子句等中引用。|546035|SELECT+a.key,+a.value
++FROM+a
++WHERE+a.key+in
+++(SELECT+b.key
++++FROM+B);|code-block|syntax|javascript|546036|可以重写为：|546037|+++SELECT+a.key,+a.val
+++FROM+a+LEFT+SEMI+JOIN+b+on+(a.key+=+b.key)|546038|entityMap|0|LINK|mutability|MUTABLE|url|https://cwiki.apache.org/Hive/languagemanual-joins.html^0|F|1J|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

You can use semi join(<a href="https://cwiki.apache.org/Hive/languagemanual-joins.html">https://cwiki.apache.org/Hive/languagemanual-joins.html</a>):

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. 

<pre><code>SELECT a.key, a.value
 FROM a
 WHERE a.key in
 (SELECT b.key
 FROM B);
</code></pre>

can be rewritten to:

<pre><code> SELECT a.key, a.val
 FROM a LEFT SEMI JOIN b on (a.key = b.key)
</code></pre>

blocks|key|762756|text|我使用的是配置单元版本0.7.1和SELECT+*+FROM+MYTABLE+WHERE+MYCOLUMN+IN+('thisThing','thatThing');|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|762757|我在列类型STRING上测试了这一点，所以我不确定这是否适用于所有数据类型，因为我注意到像上面提到的Wawrzyniec一样，Hive+Language+Manual说它不受支持，并改用LEFT+SEMI+JOIN，但它在我的测试中运行良好。|762758|entityMap^0|B|5|H|1U|0|5|6|2L|E|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

I'm using hive version <code>0.7.1</code> and <code>SELECT * FROM MYTABLE WHERE MYCOLUMN IN ('thisThing','thatThing');</code> 

I tested this on a column type <code>STRING</code> so I am not sure if this works universally on all data types since I noticed like Wawrzyniec mentioned above that the Hive Language Manual says that it is not supported and to instead use <code>LEFT SEMI JOIN</code> but it worked fine in my test.

blocks|key|762836|text|Hive+0.13现在在WHERE子句中支持IN/EXISTS。问题https://issues.apache.org/jira/browse/HIVE-784已在4年后得到解决:)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|762837|entityMap|0|LINK|mutability|MUTABLE|url|https://issues.apache.org/jira/browse/HIVE-784^0|Y|1A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

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

blocks|key|546222|text|假设table+t1(id,name)和table+t2(id,name)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|546223|只列出那些存在于t2中的t1中的ids+(基本上是IN子句)|546224|hive>select+a.id+from+t1+a+left+semi+join+t2+b+on+(a.id=b.id);|code-block|syntax|javascript|546225|只列出那些只存在于t1中但不存在于t2中的t1中的ids+(基本上是NOT+IN子句)|546226|hive>select+a.id+from+t1+a+left+outer+join+t2+b+on(a.id=b.id)+where+b.id+is+null;|546227|entityMap^0|2|H|K|H|0|C|2|P|2|0|0|9|2|L|2|Y|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]]|D|@]|E|$]]|$1|O|3|P|5|J|7|1A|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|R|$]]

assume <code>table t1(id,name)</code> and <code>table t2(id,name)</code>

listing only those ids from <code>t1</code> that exists in t2(basically <code>IN</code> clause)

<pre><code>hive&gt;select a.id from t1 a left semi join t2 b on (a.id=b.id);
</code></pre>

listing only those ids from <code>t1</code> that exists only in <code>t1</code> but not in t2(basically <code>NOT IN</code> clause)

<pre><code>hive&gt;select a.id from t1 a left outer join t2 b on(a.id=b.id) where b.id is null;
</code></pre>

blocks|key|546252|text|自Hive+0.13以来，Hive确实支持IN/EXISTS语句，但限制很少。请参考https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BSubQueries了解更多详细信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|546253|entityMap|0|LINK|mutability|MUTABLE|url|https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BSubQueries^0|16|22|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Hive does support IN/EXISTS statements since Hive 0.13 with few limitations. Please refer to <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries" rel="nofollow noreferrer">https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries</a> for more details.

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 &amp; Regards,
Atul

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

Hive

如何在配置单元中使用In子句我想在配置单元select x from y where y.z in (select distinct Z from y) order by x中编写类似这样的代码；但我找不到任何方法。我尝试了Hive 0.7中的In子句，它抛出了错误，我也尝试了Find_in_Set。使用find_in...

问如何在Hive中写入子查询和使用" in“子句
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Hive中写入子查询和使用" in“子句EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Hive中写入子查询和使用" in“子句
EN