My hive table has a column of type map <String, String>. I would like to explode the map and move them to columns instead of rows

Question

Below is the structure of the hive table

data_dt                 string
id                      string
records                 map<string,string>

It is broken into data_dt.

When I run the query,

select id, key, val from test
lateral view explode(records) t as key, val

According to the Hive doc, my map is recordsblown into data rows. I need spaced data in columns, not rows.

Ex: The above query will give me

abc | k1 | v1
abc | k2 | v2
abc | k3 | v3
zxc | k1 | v1
zxc | k3 | v3

I need it instead

id  | k1 | k2 | k3
abc | v1 | v2 | v3
zxc | v3 | /N | v3

I know that explode is UDTF, and therefore it will return the results as rows, not columns. But is there any way to get data in the form of columns instead of rows?

Answer 1

You can run the query as follows:

select id, max(k1) as k1, max(k2) as k2, max(k3) as k3 from (
select id, case when c2 = 'k1' then c3 end as k1,
case when c2 = 'k2' then c3 end as k2,
case when c2 = 'k3' then c3 end as k3
from table_name) q;