How to get a list of only keys from Hive map

user_11569666 0
Viewed 3609

I have a map stored in a column in Hive, where they keys of each row can be different. How can I get a list of only keys from each map?

hiveql hive dictionary arrays
1 Answers

Function map_keys(Map) returns an unordered array containing the keys of the input map.

Example, see comments in the code:

    with mydata as (
    select 1 id, map('key11','val11','key12','val12','key13','val13') as mymap
    union all
    select 2 id, map('key21','val21','key22','val22','key13','val13') as mymap --Key13 also exist in first row
    )

select id, map_keys(d.mymap) keys
  from mydata d
;

Result:

id  keys
1   ["key11","key12","key13"]
2   ["key21","key22","key13"]

If you need list of unique keys from all rows, explode array and collect again using collect_set, it will return array of distinct keys:

with mydata as (
select 1 id, map('key11','val11','key12','val12','key13','val13') as mymap
union all
select 2 id, map('key21','val21','key22','val22','key13','val13') as mymap --Key13 also exist in first row
)

select --id, 
       collect_set(key) as keys
  from mydata d
       lateral view outer explode(map_keys(d.mymap)) e as key
 --group by id   --without id in groupby you get the distinct list of keys in all rows
                 --with id in groupby you get list of map keys for each row
;

Result:

["key11","key12","key13","key21","key22"]
user_27003440
Related