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

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

Explorer

Hi,

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)

 

Schema:

Column Type
name STRING
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?

 

2 REPLIES 2

Guru
@Eediej,

I believe that you are looking for this feature:
https://issues.apache.org/jira/browse/IMPALA-2083

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:
https://community.cloudera.com/t5/Support-Questions/Hive-Transpose-the-set-of-rows/td-p/148685

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

Cheers
Eric

Explorer

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;`