Support Questions
Find answers, ask questions, and share your expertise

Impala: Possible to project map<string, string> keys as columns?



In Impala, is it possible to project map keys from a MAP<string, string> as actual columns in the result set?  I'm ingesting a dataset where we can't know all the possible attributes ahead of time and so we're using a map<string, string> column for maximum flexibility.  However, at query time we still want to pull those attributes out as their own column.


Simplified example below with a few unique keys (in reality there are hundreds of keys in our dataset)



Column Type
props MAP<string, string>



Raw Table:

Name Props
Bob {"age": "39", "fav_color": "green"}
Alice {"fav_animal": "dog", "fav_color": "blue"}



Desired result:

name age fav_color fav_animal
Bob 39 green NULL
Alice NULL blue dog



I can get all the results in a row-oriented manner by

select name, props.key, props.value from table, table.props props;


But, I've not been able to figure out how to make those keys from the MAP type column into their own columns.  Is there a efficient way to do this in Impala?




I believe that you are looking for this feature:

But it is still not resolved, so I guess not supported in Impala.

Not sure if below can help you to implement in Hive, I haven't tried myself:

If you end up get it working, please share result here so that other members can benefit.



Yes looks like PIVOT would definitely work but that ticket appears to be 4 years old so I won't hold my breath on it 😉


In the mean time, I'll continue to use HIVE which provides this functionality with a simple dictionary like syntax (not quite PIVOT, but works and is intuitive for python programmers)


`select name, props["age"], props["fav_animal"] from table;`




; ;