Hive: Cant perform union query with limit

Question

I am trying to run a union all query in hive

select * from tabName where col1='val1' and col2 = 'val2' limit 10 union all select * from tabName where col1='val1' and col2 = 'val3' limit 10;

but i get

FAILED: ParseException line 1:105 missing EOF at 'union' near '10'

I also tried

( select * from tabName where col1='val1' and col2 = 'val2' limit 10 ) as a union all ( select * from tabName where col1='val1' and col2 = 'val3' limit 10 ) as b;

but i got

FAILED: ParseException line 1:109 missing EOF at 'as' near ')'

what am i doing wrong ?

score 6 · Accepted Answer · answered Jun 26 '19 at 17:07

6

Use select from subquery:

select * from
( select * from tabName where col1='val1' and col2 = 'val2' limit 10 ) a 
union all 
select * from
( select * from tabName where col1='val1' and col2 = 'val3' limit 10 ) b;

answered Jun 26 '19 at 17:07

leftjoin

36,950
8
57
116

score 0 · Answer 2 · answered Nov 30 '22 at 03:09

I offer another way using with clause:

with query1 as (
  select *
  from tabName
  where col1 = 'val1' and col2 = 'val2'
  limit 10
),
query2 as (
  select *
  from tabName
  where col1 = 'val1' and col3 = 'val3'
  limit 10
)
select * from query1
union all
select * from query2

Hive: Cant perform union query with limit

2 Answers2