Cross joining to an unnested mapping field in HQL (works in athena, not in Hive)

Question

So I have two (mapping) fields I need to unpack and break out into rows. In athena, I can use the following approach (to unpack iether of them:

SELECT
unique_id,
key,
value
FROM
(
select
unique_id,
purchase_history
from table
)
CROSS JOIN unnest(purchase_history) t(key,value)

This works perfectly in athena, I get 1 row for each purchase along with their unique identifier. However, when I try to test it in Hive it doesn't work. Is there anything specific in here that doesn't fly in HQL? I think cross joins are allowed, but perhaps the way I am calling the field isn't working? Or is it the "unnest"? Please let me know if you need further explanation.

Answer 1

You can do the same in Hive using lateral view explode, if purchase_history is of type map, this will work:

SELECT
s.unique_id,
t.key,
t.value
FROM
(
select
unique_id,
purchase_history
from table
) s --alias for sub-queries is a must in Hive 
 lateral view explode(s.purchase_history) t as key,value