Lateral view与表生成函数UDTF，如`explode()`等函数一起使用。<br/> UDTF 为每个输入行生成零个或多个输出行。Lateral view首先将 UDTF 应用于基础表的每一行，然后将产生的输出行连接到输入行，形成一个具有提供表别名的虚拟表。 <br/> 即使侧视图通常不会生成一行，用户也可以指定可选的 `OUTER` 关键字来生成行。当使用的 UDTF 不生成任何行时，就会发生这种情况。在这种情况下，源行永远不会出现在结果中。可以使用 `OUTER` 来防止这种情况，并在来自UDTF的列中使用空值生成行。`OUTER`关键字可以把不输出的 UDTF 的空结果，输出成 NULL，防止丢失数据。<br/> Lateral view通常用于规范化行或解析JSON。<br/> （1）示例数据`laterview.txt` ```xml 1,zhangsan,20/30,15555555550/155555555551 2,lisi,22/33,16666666660/16666666661 3,wangwu,23/44,17777777770/17777777771 ``` （2）创建一个laterview表并导入数据 ```sql create table if not exists laterview( id int, name string, age string, phone string ) row format delimited fields terminated by ','; load data local inpath "/hdatas/laterview.txt" into table laterview; 0: jdbc:hive2://hadoop101:10000> select * from laterview; +---------------+-----------------+----------------+---------------------------+--+ | laterview.id | laterview.name | laterview.age | laterview.phone | +---------------+-----------------+----------------+---------------------------+--+ | 1 | zhangsan | 20/30 | 15555555550/155555555551 | | 2 | lisi | 22/33 | 16666666660/16666666661 | | 3 | wangwu | 23/44 | 17777777770/17777777771 | +---------------+-----------------+----------------+---------------------------+--+ ``` （3）侧视图应用 ```sql #### 单个侧视图 #### 0: jdbc:hive2://hadoop101:10000> select name, age, t_phone.phone from laterview . . . . . . . . . . . . . . . .> lateral view explode(split(phone, '/')) t_phone as phone; +-----------+--------+----------------+--+ | name | age | t_phone.phone | +-----------+--------+----------------+--+ | zhangsan | 20/30 | 15555555550 | | zhangsan | 20/30 | 155555555551 | | lisi | 22/33 | 16666666660 | | lisi | 22/33 | 16666666661 | | wangwu | 23/44 | 17777777770 | | wangwu | 23/44 | 17777777771 | +-----------+--------+----------------+--+ #### 多个侧视图 #### 0: jdbc:hive2://hadoop101:10000> select name, t_age.age, t_phone.phone from laterview . . . . . . . . . . . . . . . .> lateral view explode(split(phone, '/')) t_phone as phone . . . . . . . . . . . . . . . .> lateral view explode(split(age, '/')) t_age as age; +-----------+------------+----------------+--+ | name | t_age.age | t_phone.phone | +-----------+------------+----------------+--+ | zhangsan | 20 | 15555555550 | | zhangsan | 30 | 15555555550 | | zhangsan | 20 | 155555555551 | | zhangsan | 30 | 155555555551 | | lisi | 22 | 16666666660 | | lisi | 33 | 16666666660 | | lisi | 22 | 16666666661 | | lisi | 33 | 16666666661 | | wangwu | 23 | 17777777770 | | wangwu | 44 | 17777777770 | | wangwu | 23 | 17777777771 | | wangwu | 44 | 17777777771 | +-----------+------------+----------------+--+ #### 不使用outer #### 0: jdbc:hive2://hadoop101:10000> select name, age, t_phone.phone from laterview . . . . . . . . . . . . . . . .> lateral view explode(split(null, '/')) t_phone as phone; +-------+------+----------------+--+ | name | age | t_phone.phone | +-------+------+----------------+--+ +-------+------+----------------+--+ #### 使用outer #### 0: jdbc:hive2://hadoop101:10000> select name, age, t_phone.phone from laterview . . . . . . . . . . . . . . . .> lateral view outer explode(split(null, '/')) t_phone as phone; +-----------+--------+----------------+--+ | name | age | t_phone.phone | +-----------+--------+----------------+--+ | zhangsan | 20/30 | NULL | | lisi | 22/33 | NULL | | wangwu | 23/44 | NULL | +-----------+--------+----------------+--+ ```