Hive - create table by select columns from different tables

Question

Here are my hive tables:

table1:

|a |b |c |
----------
|a1|b1|c1|
|a2|b2|c2|
|a3|b3|c3|
|a4|b4|c4|
|a5|b5|c5|

table2:

|x |y |z |
----------
|x1|y1|z1|
|x2|y2|z2|
|x3|y3|z3|
|x4|y4|z4|
|x5|y5|z5|

Desired output:

|a |b |x |y |
-------------
|a1|b1|x1|y1|
|a2|b2|x2|y2|
|a3|b3|x3|y3|
|a4|b4|x4|y4|
|a5|b5|x5|y5|

is it really possible in hive? Any help would be appreciated, Thank you!

Answer 1

You seem to want to "line up" the rows of both tables. Assuming that column a can be used to order the record in table1 (resp column x in table2), you can use row_number() as follows:

select t1.a, t1.b, t2.x, t2.y
from (select t1.*, row_number() over(order by a) rn from table1 t1) t1
inner join (select t2.*, row_number() over(order by x) rn from table2 t2) t2
    on t1.rn = t2.rn

If the tables may have a different number of rows, and you want to retain "additional" rows, you can just change the inner join to a full join.