以前在数据库工具上执行Hive QL用到union all时
直接这样写即可
select a
union all
select b
union all
select c
但是换到SparkSQL中执行却提示错误
diagnostics: User class threw exception: org.apache.spark.sql.catalyst.parser.ParseException:
mismatched input ‘union’ expecting (line 18, pos 0)
后面查了查资料后发现,Hive中对union all的语法有一下要求
- union all 的每张表都要用简称命名,否则报错
例如:
select * from a_1 t1
union all
select * from a_2 t2 - union all的每张表都不支持limit、order by等函数,否则报错
例如:
select * from (select * from a_1 t1 order by XXX limit n) x1
union all
select * from (select * from a_2 t2 order by XXX limit n) x2
参考资料:
https://www.jianshu.com/p/f59cdb3bcdb6