CODEWITHSUNDEEP
sqlhive
prashanth
prashanth

Reputation: 4485

Hive: joining multiple tables with same key

I am trying to join multiple tables in hive which have a common key col

create table merged_table as
select a.*,b.*,c.* from table1 a
join table 2 b
on a.col = b.col
join table3 c
on c.col = d.col

But this gives the following error:

FAILED: SemanticException [Error 10036]: Duplicate column name: col

These tables have many columns. Is there any way by which this can be done without explicitly writng the column names in the query?

Upvotes: 0

Views: 1082

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You cannot create a table with duplicate column names. While it is possible in SQL top level SELECT statements to produce ambiguous / duplicate column names, such queries cannot be used in CREATE TABLE AS SELECT. You should rename each individual column name, possibly prefixing them all with the origin table name:

create table merged_table as
select a.col1 as a_col1, a.col2 as a_col2, ..., b.col1 as b_col1, ...
from table1 a
join table2 b on a.col = b.col
join table3 c on b.col = c.col

Upvotes: 1

Related Questions