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 | +-----------+--------+----------------+--+ ```