Search code examples
hadoophivekeyvaluepair

Explode Hive Map data object into long format

I have a map data type in a table with fairly large number of key, values (10-30). When I explode the key, values, I get below:

SELECT id, key,value
FROM tbl1
lateral view explode(map_field) feature_cols

Results:

id, key1, value1
id, key2, value2
id, key3, value3

However, I would like to see:

id, key1, key2, key3
1, value1, valu2, value3

Is there any command that either produces my desired format, or is there any command to convert exploded output to the long format I desire?

Solution

  • We need to transpose Columns into Rows after lateral view explode. You can write query like as stated below.

    Select 
id, 
 Case when key=key1 then value1 as      key1,
Case when key=key2 then value2 as key2,
Case when key=key3 then value3 as key3
From

    (SELECT id, key,value FROM tbl1 lateral view explode(map_field) feature_cols) temp