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

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

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

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

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

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

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