hive中的Lateral View

最新推荐文章于 2025-09-22 11:34:39 发布

转载最新推荐文章于 2025-09-22 11:34:39 发布 · 792 阅读

文章标签：

#hive #hadoop

Hadoop 同时被 2 个专栏收录

38 篇文章

订阅专栏

Hive

12 篇文章

订阅专栏

本文介绍了如何利用Lateralview解决在Hive中直接使用UDTF时存在的限制，特别是针对多个UDTF或者UDTF与其他字段结合使用的情况。通过演示创建表、加载数据、应用Lateralview以及查询结果的过程，详细解释了如何将UDTF得到的行转列结果集合在一起提供服务，以满足实际需求。

通过Lateral view可以方便的将UDTF得到的行转列的结果集合在一起提供服务。
因为直接在SELECT使用UDTF会存在限制，即仅仅能包含单个字段，如下：

hive> select my_test(“abcef:aa”) as qq,my_test(“abcef:aa”) as ww from sunwg01;
FAILED: Error in semantic analysis: Only a single expression in the SELECT clause is supported with UDTF’s

hive> select my_test(“abcef:aa”) as qq,’abcd’ from sunwg01;
FAILED: Error in semantic analysis: Only a single expression in the SELECT clause is supported with UDTF’s

不光是多个UDTF，仅仅单个UDTF加上其他字段也是不可以，hive提示在UDTF中仅仅能有单一的表达式。

使用Lateral view可以实现上面的需求，Lateral view语法如下：

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*
fromClause: FROM baseTable (lateralView)*

hive> create table sunwg ( a array, b array )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’
> COLLECTION ITEMS TERMINATED BY ‘,’;
OK
Time taken: 1.145 seconds

hive> load data local inpath ‘/home/hjl/sunwg/sunwg.txt’ overwrite into table sunwg;
Copying data from file:/home/hjl/sunwg/sunwg.txt
Loading data to table sunwg
OK
Time taken: 0.162 seconds

hive> select * from sunwg;
OK
[10,11] ["tom","mary"]
[20,21] ["kate","tim"]
Time taken: 0.069 seconds

hive>
> SELECT a, name
> FROM sunwg LATERAL VIEW explode(b) r1 AS name;
OK
[10,11] tom
[10,11] mary
[20,21] kate
[20,21] tim
Time taken: 8.497 seconds

hive> SELECT id, name
> FROM sunwg LATERAL VIEW explode(a) r1 AS id
> LATERAL VIEW explode(b) r2 AS name;
OK
10 tom
10 mary
11 tom
11 mary
20 kate
20 tim
21 kate
21 tim
Time taken: 9.687 seconds