hive union 的问题

最新推荐文章于 2025-03-16 12:01:49 发布

原创最新推荐文章于 2025-03-16 12:01:49 发布 · 2.5w 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#user #subquery #semantic

hive 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了在Hive中正确使用UNION及UNION ALL的方法，解决因顶级UNION导致的错误，并通过添加输出别名来确保查询结果的准确性。

在hive上执行查询：

select count(*) from user_active_vv_20110801_31 where active_type_3>0
UNION ALL
select count(*) from user_active_vv_20110801_31  where active_type_7>0
union all
select count(*) from user_active_vv_20110801_31 where active_type_9>0
union all
select count(*) from user_active_vv_20110801_31 where active_type_11>0
union all
select count(*) from user_active_vv_20110801_31 where active_type_12>0
union all
select count(*) from user_active_vv_20110801_31 where active_type_17>0
union all
select count(*) from user_active_vv_20110801_31 where active_type_22>0;

报错：

FAILED: Error in semantic analysis: Top level UNION is not supported currently; use a subquery for the UNION

原来hive不支持顶层union，只能将union封装在子查询中；且必须为union的查询输出定义别名，正确的hql如下：

select * from (select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_3>0
UNION ALL
select count(*) as type3 from user_active_vv_20110801_31  where user_active_vv_20110801_31.active_type_7>0
union all
select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_9>0
union all
select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_11>0
union all
select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_12>0
union all
select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_17>0
union all
select count(*) as type3 from user_active_vv_20110801_31 where user_active_vv_20110801_31.active_type_22>0) tmp;

执行结果如下：

不过查询出来的结果和hql语句中union的顺序不一致，union子查询中第一个子查询的结果应该是57691832，所以将union子查询改为：

select count(*) as type3 ,'7' as union_order from user_active_vv_20110801_31  where user_active_vv_20110801_31.active_type_7>0

还有一点需要注意的是：hive在创建别名时不能使用关键字 as