两个查询的Hive Union给出编译错误

Question

我不确定以下hive查询有什么问题。但是，它抛出错误。

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

我能够单独运行这个查询。但是，在使用union

运行时面临的问题误差

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

Answer 1

1.2.0之前的Hive版本只支持UNION ALL

解决方法:使用UNION ALL而不是UNION，将所有包入子查询并应用不同的

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

UNION与UNION ALL + DISTINCT相同