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)
Advertisement
Answer
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