Minus query in HIVE

Question

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?

Answer 1

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)

Answer 2

HQL DOES NOT support minus but you can always use Patrick Tucci solution which works fine when your select-list contains only a few fields. In my case I wanted to find the differences between an entire table (30+ fields) and a backup copy to find records that were different. Here is my solution:

select <all-my-fields>, count(*)
  from (
        select <all-my-fields> from mytable
        union all
        select <all-the-fields> from mybackuptable
       ) merged_data
group by <all-my-fields>
having count(*) = 1

Now this is not completly a "minus" since single records from mybackuptable would show up in the result which is what I wanted. To make it a complete "minus" equivalent I've added this:

select <all-my-fields>
  from (
        select max(source) source, <all-my-fields>, count(*)
          from (
                select 1 source, <all-my-fields> from mytable
                union all
                select 2 source, <all-the-fields> from mybackuptable
               ) merged_data
        group by <all-my-fields>
        having count(*) = 1
       ) minus_data
 where source = 1

Answer 3

The above query might be easier to read if it was formatted slightly differently:

SELECT col1
FROM table1
WHERE NOT EXISTS (
    SELECT col2
    FROM table2 
    WHERE table1.col1 = table2.col2
)

This is a correlated sub-query. That is, the outer query is correlated to the inner (sub) query via the join contained in the subquery that references the outer query.

After joining tbl1 to tbl2, the result of that join (i.e. in this case, the list of col2 values that exist both in table1.col1 and table2.col2) the NOT Exists is applied to remove those values from the list of col1 values in Table1. The result is the list of col1 values in table1 that do not appear in table2.

Any "extra" values in Table2 that do not exist in table1 do not play a part in this query as they are removed by the inner join. This is fine, because the query is trying to return just those values in table1 that are not in table2 - extra values in table2 aren't in table1 in the first place - hence they are irrelevant.