Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

ADD TWO TABLES IN HIVE

avatar
Explorer

I would like to add the table in a single table if anyone knows kindly let me know, I am getting below errors.

hive> use final;
OK
Time taken: 0.057 seconds
hive> show tables;
OK
crimeloc13
crimeloc14
crimeloc15
crimeloc16
Time taken: 0.209 seconds
hive> select a.loc,a.cnt13 from crimeloc13 a UNION ALL select b.cnt14 from crimeloc14 b;
FAILED: SemanticException 1:26 Top level UNION is not supported currently; use a subquery for the UNION. Error encountered near token 'cnt14'
hive> 
1 ACCEPTED SOLUTION

avatar
Super Guru
Hi,

As the error message indicated, UNION ALL is not supported at top level, you need to put it in a sub query, similar to below:

SELECT loc, cnt FROM (
select a.loc as loc, a.cnt13 cnt
from crimeloc13 a
UNION ALL
select b.loc as loc, b.cnt14 as cnt
from crimeloc14 b
) a;



View solution in original post

1 REPLY 1

avatar
Super Guru
Hi,

As the error message indicated, UNION ALL is not supported at top level, you need to put it in a sub query, similar to below:

SELECT loc, cnt FROM (
select a.loc as loc, a.cnt13 cnt
from crimeloc13 a
UNION ALL
select b.loc as loc, b.cnt14 as cnt
from crimeloc14 b
) a;