ADD TWO TABLES IN HIVE

[#1]

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> 

[#2 · Accepted solution]

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;