什么是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嵌入式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屋!