How to transpose row to column in spark/scala?

Question

I'm new to spark, but I can't find the answer to a simple question: How to change row to columns? For example, I have data like:
Type | Col1 | Col2 | Col3 |... | Col60
1 | 12 | 3 | 4 |... | 87
2 | 1 | 5 | 6 |... | 90

I want to change it to like

Type | ColName | Value
1 | Col1 | 12
1 | Col2 | 3
1 | Col3 | 4
...
1 | Col60 | 87
2 | Col1 | 1
2 | Col2 | 5
2 | Col3 | 6
...
2 | Col60 | 90

I've tried to search many places, but there is no answer exactly like what I need.

score 1 · Accepted Answer · answered Dec 07 '18 at 21:38

1

In Hive, you can use lateral view with explode by getting the columns into a map.

select type,colName,val
from tbl
lateral view explode(map('col1',col1,'col2',col2,'col3',col3)) t as colName,val --fill it with all the columns from the table

Spark SQL also has lateral view and explode which can be used.

answered Dec 07 '18 at 21:38

Vamsi Prabhala

48,685
4
36
58

Thanks!! But how can I save the table in spark? Use hiveContext.sql("*")? – user2654659 Dec 07 '18 at 21:50
And is there a programmable way to do this? – user2654659 Dec 07 '18 at 21:59

How to transpose row to column in spark/scala?

1 Answers1