CODEWITHSUNDEEP
hadoopunionhivehiveql
dum_dum_dummy
dum_dum_dummy

Reputation: 161

HiveQL UNION ALL

I have table_A:

id var1 var2
1   a    b
2   c    d

Table_B:

id var1 var2  
3   e    f
4   g    h

All I want is table, combined:

id var1 var2
1   a    b
2   c    d
3   e    f
4   g    h

This is my .hql:

CREATE TABLE combined AS
SELECT all.id, all.var1, all.var2
FROM (
  SELECT a.id, a.var1, a.var2  
  FROM table_A a  
  UNION ALL  
  SELECT b.id, b.var1, b.var2  
  FROM table_B b
) all;

I'm coding straight from page 112 of Programming Hive by Edward Capriolo, et al.

The error I get, no matter what ostensibly reasonable variation of the above that I try, is

cannot recognize input near '.' 'id' ',' in select expression.

I have tried using AS between the table name and the alias, asterisks since I want everything from both tables. Same error. I've tried other things and gotten other errors... All I want to do is UNION two tables. (I've tried UNION instead of UNION ALL — same error).

Upvotes: 16

Views: 54317

Answers (3)

kalpesh
kalpesh

Reputation: 328

I have similar query working. Just changing table name and column names. Try this. Hope this helps you.

CREATE TABLE new_table AS
SELECT DISTINCT
    id,
    name
FROM
    table1
UNION ALL
SELECT DISTINCT
    id,
    name
FROM
    table2;

Upvotes: 2

Mahesh Kumar
Mahesh Kumar

Reputation: 59

Try this, it worked for me.

CREATE TABLE combined AS
SELECT id, var1, var2
FROM (
  SELECT id, var1, var2  
  FROM table_A
  UNION ALL  
  SELECT id, var1, var2  
  from table_B
) a;

Upvotes: 1

Lorand Bendig
Lorand Bendig

Reputation: 10650

Just replace all with another word. It seems to be a reserved keyword. E.g:

CREATE TABLE combined AS
SELECT unioned.id, unioned.var1, unioned.var2
FROM (
  SELECT a.id, a.var1, a.var2  
  FROM table_A a  
  UNION ALL  
  SELECT b.id, b.var1, b.var2  
  from table_B b
) unioned;

Upvotes: 15

Related Questions