在hive上执行查询:
报错:
原来hive不支持顶层union,只能将union封装在子查询中;且必须为union的查询输出定义别名,正确的hql如下:
执行结果如下:
不过查询出来的结果和hql语句中union的顺序不一致,union子查询中第一个子查询的结果应该是57691832,所以将union子查询改为:
- 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
不过查询出来的结果和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