Search code examples
sqlhiveunionhiveqlhive-query

Hive Union of two queries is giving compile error

I am not sure what is wrong with following hive query. However, it's throwing error.

select h.database_name, h.table_name, h.frequency as hive_frequency, i.frequency as impala_frequency    
from hive_access_frequency h
left join impala_access_frequency i
on 
h.database_name = i.database_name and 
h.table_name = i.table_name
union
select i.database_name, i.table_name, h.frequency as hive_frequency, i.frequency as impala_frequency    
from impala_access_frequency i
left join hive_access_frequency h
on 
h.database_name = i.database_name and 
h.table_name = i.table_name

I am able to run this queries individually. However, facing issues while running with union

Error

Error: Error while compiling statement: FAILED: ParseException line 8:0 missing ALL at 'select' near '<EOF>' (state=42000,code=40000)
Solution

  • Hive versions prior to 1.2.0 only support UNION ALL

    Workaround: Use UNION ALL instead of UNION, wrap all into subquery and apply distinct

    select DISTINCT <column_list>
from
(
select ...
UNION ALL
select ...
)s;

    UNION is the same as UNION ALL + DISTINCT