Hive之侧视图(Lateral View)

• 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)

参考资料

【0】Hive wiki - LanguageManual LateralView