hive

Minus query in HIVE

Minus query seems to not work in HIVE.

Tried ex: 

select x from abc 
minus 
select x from bcd ;

Am I doing this wrong or minus query isn't defined for HIVE? If so, is there any other way to get the result for this?

Solution

  • It does not appear that HQL supports the MINUS operator. See this relevant, albeit a bit old, resource:

    http://www.quora.com/Apache-Hive/What-are-the-biggest-feature-gaps-between-HiveQL-and-SQL

    What you want to do can be done with a LEFT JOIN or NOT EXISTS:

    SELECT x
FROM abc
LEFT JOIN bcd
ON abc.x = bcd.x
WHERE bcd.x IS NULL

    EDIT: Per comments below, NOT EXISTS is not supported.

    SELECT x 
FROM abc
WHERE NOT EXISTS (SELECT x FROM bcd)