今天在写一个sql的时候，使用了hive的union all，运行时报错。
sql如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select dimension_name,dt,dtype_name,uv,new_uv,old_uv,pv,stay_time from temp_bi.uv
union all
select '平台' dimension_name
,dt,app_platform dtype_name,count(distinct mid) uv
,count(distinct case when is_new = 1 then mid end) new_uv
,count(distinct case when is_new = 0 then mid end) old_uv
,sum(pv_num) pv
,sum(stay_time) stay_time --停留时长（秒）
from vipdw.dw_log_app_visit_ds1
where dt between get_dt_date(get_date(-1)) and get_dt_date(get_date(-1))
group by '平台' ,dt ,app_platform
|
报错信息如下：
|
1
|
FAILED: SemanticException 1:71 Top level UNION is not supported currently; use a subquery for the UNION. Error encountered near token 'app_platform'
|
hive不支持顶层的union all，使用子查询来解决即可：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
select * from (
select dimension_name,dt,dtype_name,uv,new_uv,old_uv,pv,stay_time from temp_bi.uv
union all
select '平台' dimension_name
,dt,app_platform dtype_name,count(distinct mid) uv
,count(distinct case when is_new = 1 then mid end) new_uv
,count(distinct case when is_new = 0 then mid end) old_uv
,sum(pv_num) pv
,sum(stay_time) stay_time --停留时长（秒）
from vipdw.dw_log_app_visit_ds1
where dt between get_dt_date(get_date(-1)) and get_dt_date(get_date(-1))
group by '平台' ,dt ,app_platform) a
|
本文转自菜菜光 51CTO博客，原文链接：http://blog.51cto.com/caiguangguang/1414086，如需转载请自行联系原作者