sqlarrayshiveprestounnest

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.

Solution

  • 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.