首页 > 代码库 > hive的union问题

hive的union问题

2024-07-03 09:23:47   224人阅读

内容来源：http://blog.csdn.net/azhao_dn/article/details/6921429

在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; 

执行结果如下：

  • 54211920 
  • 57691832 
  • 41080830 
  • 44067696 
  • 32052350 
  • 34341676 
  • 13968539 

 

 


联系
我们