Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the equivalent of Presto UNNEST function in Hive

Presto has an UNNEST function to explode columns made of arrays. Is there a similar one for Hive? See docs for UNNEST function of Presto here.

like image 511
ishan3243 Avatar asked Mar 06 '23 15:03

ishan3243


1 Answers

Use lateral view [outer] explode. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

This is example from Presto migration from Hive docs:

SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score;

And example from Hive Lateral View docs:

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

Use OUTER keyword to generate rows even when a LATERAL VIEW usually would not generate a row:

SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10; 

In this example the array is empty, but rows from src will be returned

Lateral view can be used not only with explode() UDTF. See the list of Hive embedded UDTFs with examples. Also you can write your own UDTF and use it with LATERAL VIEW.

like image 112
leftjoin Avatar answered Mar 11 '23 18:03

leftjoin