Hive两个查询执行union操作时出现编译报错该如何解决

这是Hive SQL和标准ANSI SQL的语法差异导致的：

• Hive 不支持直接使用不带ALL关键字的UNION语法，解析器识别到UNION后会强制要求后续紧跟ALL，你直接在UNION后写第二个查询的SELECT语句，就会触发缺失ALL关键字的解析异常。
• 两个子查询单独执行时没有UNION操作，不会触发对应语法校验规则，所以运行正常。

分两种场景处理：

场景1：不需要对合并结果去重 #

直接将语句中的UNION替换为UNION ALL即可，修改后的查询语句如下：

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 all
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

场景2：需要对合并结果去重（对应标准SQL中UNION的效果） #

Hive没有原生支持隐式去重的UNION语法，你可以先通过UNION ALL合并结果，再在外层查询加去重逻辑：

select distinct * from (
    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 all
    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
) t;

内容的提问来源于stack exchange，提问作者Gaurang Shah