Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...