什么是Hive中的Presto UNNEST函数的等效项 [英] What is the equivalent of Presto UNNEST function in Hive

问题描述

Presto具有UNNEST函数，以爆炸由数组组成的列. Hive是否有类似的产品? 有关Presto的UNNEST功能的文档，请参见此处.

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.

推荐答案

使用lateral view [outer] explode.侧视图首先将UDTF应用于基表的每一行，然后将结果输出行与输入行连接起来以形成具有所提供表别名的虚拟表.

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.

此示例来自> 从Hive进行迁移文档:

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;

使用OUTER关键字生成行，即使LATERAL VIEW通常不会生成行:

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; 

在此示例中，array为空，但将返回src中的行

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

横向视图不仅可以与explode() UDTF一起使用.请参阅Hive嵌入式 UDTF (带有示例).您也可以编写自己的UDTF并将其与LATERAL VIEW一起使用.

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.

这篇关于什么是Hive中的Presto UNNEST函数的等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！