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] expand.横向视图首先将 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 迁移 Presto 文档的示例:

This is example from Presto migration from Hive docs:

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

以及来自 Hive 横向视图的示例 文档:

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 嵌入列表 UDTFs 带有示例.您也可以编写自己的 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屋！