Hive侧视图 Lateral View（单个、多个、outer）（附案例）

竹长大大

已于 2022-02-21 16:12:45 修改

阅读量5.6k

点赞数 8

分类专栏： # Hive 文章标签： hive 数据仓库 sql big data 大数据

于 2022-02-17 01:17:27 首次发布

原文链接：https://www.cnblogs.com/wadeyu/p/9806676.html

版权

Hive 专栏收录该内容

17 篇文章

订阅专栏

本文介绍了如何利用LateralView和UDTF在Hive中高效地操作表数据，通过连接、拆分和外连接操作，实现灵活的数据处理和分析。通过示例展示了单个、多个LateralView的使用，以及OuterLateralView在空结果处理上的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Lateral View 和UDTF类功能函数一起使用，表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表，可以对UDTF产生的记录设置字段名称，新加的字段可以使用在 sort by，group by 等语句中，不需要再套一层子查询。Lateral View 的作用是可以扩展原来的表数据。

Lateral View Syntax:

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

案例数据准备：

--创建表
create table lateral_test(
  name string,
  course string,
  hobby string
)
row format delimited
fields terminated by '\t'
lines terminated by '\n';
 
--样例数据
[root@wadeyu ~]# cat lateral_test.log 
wadeyu  c1,c2,c3    basketball,tennis
tom c2,c3,c5    swimming,trip
polly   c1,c2,c3    sz
 
--查询数据
0: jdbc:hive2://> select * from lateral_test;
OK
+--------------------+----------------------+---------------------+--+
| lateral_test.name  | lateral_test.course  | lateral_test.hobby  |
+--------------------+----------------------+---------------------+--+
| wadeyu             | c1,c2,c3             | basketball,tennis   |
| tom                | c2,c3,c5             | swimming,trip       |
| polly              | c1,c2,c3             | sz                  |
+--------------------+----------------------+---------------------+--+
3 rows selected (0.511 seconds)

示例一：单个 Lateral View

select 
  name, 
  course, 
  t_hobby.hobby 
from lateral_test 
lateral view explode(split(hobby, ',')) t_hobby as hobby;
 
OK
+---------+-----------+----------------+--+
|  name   |  course   | t_hobby.hobby  |
+---------+-----------+----------------+--+
| wadeyu  | c1,c2,c3  | basketball     |
| wadeyu  | c1,c2,c3  | tennis         |
| tom     | c2,c3,c5  | swimming       |
| tom     | c2,c3,c5  | trip           |
| polly   | c1,c2,c3  | sz             |
+---------+-----------+----------------+--+
5 rows selected (0.535 seconds)

示例二：多个 Lateral View

Lateral View左边的字段都可以使用

select 
  name, 
  t_course.course, 
  t_hobby.hobby 
from lateral_test 
lateral view explode(split(course, ',')) t_course as course 
lateral view explode(split(hobby, ',')) t_hobby as hobby;
 
+---------+------------------+----------------+--+
|  name   | t_course.course  | t_hobby.hobby  |
+---------+------------------+----------------+--+
| wadeyu  | c1               | basketball     |
| wadeyu  | c1               | tennis         |
| wadeyu  | c2               | basketball     |
| wadeyu  | c2               | tennis         |
| wadeyu  | c3               | basketball     |
| wadeyu  | c3               | tennis         |
| tom     | c2               | swimming       |
| tom     | c2               | trip           |
| tom     | c3               | swimming       |
| tom     | c3               | trip           |
| tom     | c5               | swimming       |
| tom     | c5               | trip           |
| polly   | c1               | sz             |
| polly   | c2               | sz             |
| polly   | c3               | sz             |
+---------+------------------+----------------+--+
15 rows selected (0.521 seconds)

示例三：Outer Lateral View

UDTF未产生行时，连接结果为空，如果想输入的行也输出，需要加上Outer关键字

# 未加Outer关键字，输出内容为空
select 
  name,
  course,
  col1
from lateral_test
lateral view explode(array()) et as col1;
 
+-------+---------+-------+--+
| name  | course  | col1  |
+-------+---------+-------+--+
+-------+---------+-------+--+
No rows selected (0.499 seconds)
 
# 加上Outer关键字，输出不为空
select 
  name,
  course,
  col1
from lateral_test
lateral view outer explode(array()) et as col1;
 
+---------+-----------+-------+--+
|  name   |  course   | col1  |
+---------+-----------+-------+--+
| wadeyu  | c1,c2,c3  | NULL  |
| tom     | c2,c3,c5  | NULL  |
| polly   | c1,c2,c3  | NULL  |
+---------+-----------+-------+--+
3 rows selected (0.543 seconds)