Transpose hive table

Question

I have a table in hive A which is like

 | ID   |   Code   |  Proc1   |   Proc2 | 
 | i    |    A     |   B      |   C     |   
 | i    |    1     |   2      |   3     |

I want my output to be :

 | i    |    A     |   1      |  
 | i    |    B     |   2      | 
 | i    |    C     |   3      |

I have read a lot of other articles and figure out that there is no direct way to do transpose in hive. Any help would be appreciated.

Possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — ollaw, Jan 30 '17 at 10:23
Possible duplicate of [Is there a way to transpose data in Hive](http://stackoverflow.com/questions/37436710/is-there-a-way-to-transpose-data-in-hive) — o-90, Jan 31 '17 at 05:23
@gobrewers14 -- this is not what i am looking for. However i can try using map function and see if i can reach any solution. — salmanbw, Jan 31 '17 at 09:18

score 1 · Accepted Answer · answered Jan 31 '17 at 15:28

1

Here is a way to do what you are requesting. I must say though, with any reasonable amount of rows this would be quite impractical to write.

Query:

SELECT id
  , new[0] AS col0
  , new[1] AS col1
FROM (
  SELECT id
    , COLLECT_LIST(code)  AS a
    , COLLECT_LIST(proc1) AS b
    , COLLECT_LIST(proc2) AS c
  FROM database.table
  GROUP BY id ) x
LATERAL VIEW EXPLODE(ARRAY(a, b, c)) exptbl AS new

Output:

id      col0    col1
i       A       1
i       B       2
i       C       3

answered Jan 31 '17 at 15:28

o-90

17,045
10
39
63

I suppose that as an alternate to my answer, you could generate this code with a function to test what performs better. Note that this would also go beyond using just HQL. (With plain HQL you will be forced to hardcode as shown in this answer) – Dennis Jaheruddin Jan 31 '17 at 15:44

score 0 · Answer 2 · answered Jan 31 '17 at 15:42

There is no way to do this in Hive without hardcoding. Assuming your hive database is in a typical ecosystem, I would recommend the following steps:

Load the data
Transpose the data with a scripting language like python (or R)
Create the target table with the relevant scripting language
Write the data to the target table

Transpose hive table

2 Answers2