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

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)