今天在写一个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，如需转载请自行联系原作者
