Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

ADD TWO TABLES IN HIVE

SOLVED Go to solution

ADD TWO TABLES IN HIVE

Contributor

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

Accepted Solutions
Highlighted

Re: ADD TWO TABLES IN HIVE

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;



1 REPLY 1
Highlighted

Re: ADD TWO TABLES IN HIVE

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;