CODEWITHSUNDEEP
apachehadoophivehql
NNN
NNN

Reputation: 41

Subqueries in Hive - Getting an error "only subquery expressions that are top level conjuncts are allowed"

Trying to run this query in Hive with a subquery that goes as follows:

select y,m,d,h from A
where d not in (select d from B)

I get an error that "only subquery expressions that are top level conjuncts are allowed"

Anybody have any ideas what this means and how to get around it?

Thanks in advance!

Upvotes: 2

Views: 15044

Answers (1)

Nadine
Nadine

Reputation: 1638

I'm assuming you are using a hive version < 0.13. Some subquery expressions aren't allowed, and that is one of them. Try this left outer join instead:

SELECT A.y, A.m, A.d, A.h
FROM A
LEFT OUTER JOIN B ON A.d = B.d
WHERE B.d IS NULL;

If you move to hive 0.13, you should be able to use it this way:

SELECT A.y, A.m, A.d, A.h FROM A WHERE A.d NOT IN (SELECT d FROM B);

Upvotes: 1

Related Questions