CODEWITHSUNDEEP
sqlhadoophivehiveqlunpivot
NG Algo
NG Algo

Reputation: 3890

Converting columns to rows (UNPIVOT) in hiveql

I have a table with a structure like this:

column1, column2, column3, X1, X2, X3, X4
A1,      A2,      A3,      5,  6,  1,  4

I would like to convert this into 

column1, column2, column3, Key, Value 
 A1,      A2,      A3,      X1,  5
 A1,      A2,      A3,      X2,  6 
 A1,      A2,      A3,      X3,  1
 A1,      A2,      A3,      X4   4

I was able to do this already using 4 queries stitched together with "UNION ALL", but since the table is huge and each select translates into a lengthy map-reduce, using UNION makes the query takes N times the time it should ideally take. Where N is number of columns to pivot.

I tried exploring the explode() pre-defined UDTF, but I am not able to work it in this example. I tried something like the following, but am not able to make the syntax work.

select column1, column2, column3, explode(Map('X1':X1, 'X2':X2, ..))

Can someone please point out exactly how to make this work?? I am guessing I could roll my own UDTF, but am hoping this is something pretty standard?

EDIT: There is another question on stackoverflow where something similar was asked, but the formulation is convoluted and in my opinion the wrong answer is currently marked as the correct answer. I think this question is more succint and to the point.

Upvotes: 4

Views: 8202

Answers (1)

NG Algo
NG Algo

Reputation: 3890

Whoops, posted this in a hurry it seems. I have the answer. Posting it here for others who might find this useful. Here is the correct syntax to deal with map and explode to achieve this.

select column1, column2, column3, m_key, m_val from
    (select column1, column2, column3, map("X1", X1, "X2", X2, "X3", X3, "X4", X4) as map1
    from table1) as t1
lateral view explode(map1) xyz as m_key, m_val

Upvotes: 5

Related Questions