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?
