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

[#1]

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]

@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

[#3]

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