NOT IN in Hive QL

Question

I'm converting a SQL Server stored procedure to HiveQL.

How can I convert something like:

SELECT * FROM table1 WHERE id NOT IN (7,6,5,4,2,12)

@bonCodigo, Why was this retagged with hibernate? That's not the same as hive. — Tim Goodman, Apr 22 '13 at 09:58
NOT IN is now supported in Hive. See https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF — Solomon, Feb 27 '14 at 23:56

score 5 · Answer 1 · answered Feb 27 '14 at 23:55

5

answered Feb 27 '14 at 23:55

Solomon

score 2 · Answer 2 · answered Jan 08 '13 at 22:13

2

Try this:

SELECT * FROM table1 WHERE NOT array_contains(array(7,6,5,4,2,12), id)

answered Jan 08 '13 at 22:13

thyme

upvoting because NOT ARRAY_CONTAINS solves another problem of neither A nor B should be present in an array.. like NOT ARRAY_CONTAINS(Array, A) and NOT ARRAY_CONTAINS(Array, B) – Shubham Patel Nov 17 '21 at 18:04

score -4 · Accepted Answer · edited May 23 '17 at 11:46

-4

According to the documentation it says you can use not in:

The negated forms can be written as follows:

from DomesticCat cat where cat.name not between 'A' and 'B'

from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Are you getting an error when you try your query in the question?

Please try based on the references as well.

edited May 23 '17 at 11:46

Community

answered Jan 08 '13 at 22:04

bonCodigo

5

The original question was about HiveQL, not HibernateQL – Alex F Apr 12 '13 at 11:39

3 Answers3