Join hive tables : Load values of multiple row in columns of another table

0
votes

I have 2 tables in hive : First table (table1):

id        val1        val2        val3
1         NULL        NULL        NULL
1         NULL        NULL        NULL
1         NULL        NULL        NULL
1         NULL        NULL        NULL

Second table (table2):

id        val        row_number
1         x          1
1         y          2
1         z          3

I want to join the tables such that the final result is like :

id        val1        val2        val3
1         x           y           z
1         x           y           z
1         x           y           z
1         x           y           z

It is assured that in table2, for a particular id, there will be exactly 3 records and each of the values (column 'val') has to be loaded in columns val1, val2, val3 of all the rows of that id in table1.

1
sqljoinhiveleft-join

1 Answers

1
votes

You would seem to want:

select t1.id, t21.val as val1, t22.val as val2, t23.val as val3
from t1 join
     t2 t21
     on t21.id = t1.id and t21.row_number = 1 join
     t2 t22
     on t22.id = t1.id and t22.row_number = 2 join
     t2 t23
     on t23.id = t1.id and t23.row_number = 3;