Hive SQL语法Explode 和 Lateral View

explode用法

在介绍如何处理之前，我们先来了解下Hive内置的 explode 函数，官方的解释是：explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW. 意思就是 explode() 接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。光看文字描述很不直观，咱们来看看几个例子吧。

select(array('1','2','3')) 

当explode函数的输入是array时，array中的每个元素都单独输出为一行。

 

select explode(map('A','1','B','2','C','3'))

当explode函数的输入是map时，map中的每对key-value会单独输出为一行，key为一列，value为一列。

 

lateral view 用法 

我们有这样的一份样本数据

刘德华    演员,导演,制片人 
李小龙    演员,导演,制片人,幕后,武术指导 
李连杰    演员,武术指导 
刘亦菲    演员 

这里我们希望转换成下面这样的格式

刘德华 演员 
刘德华 导演 
刘德华 制片人 
李小龙 演员 
李小龙 导演 
李小龙 制片人 
李小龙 幕后 
李小龙 武术指导 
create table ods.ods_actor_data( 
    username string, 
    userrole string 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; 
load data local inpath "/Users/panda/workspace/hive/lateral.data" overwrite into table ods.ods_actor_data; 

select explode(split(userrole,',')) from  ods.ods_actor_data; 

 

现在从表中选取多个字段的错误写法： 

select username,explode(split(userrole,',')) from  ods.ods_actor_data; 
这样写报错，因为explode 是一个UDTF，所以不能直接和其他字段一起使用
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

正确写法：

select 
   username,role 
from 
    ods.ods_actor_data 
LATERAL VIEW 
    explode(split(userrole,',')) tmpTable as role 
; 

 

 

lateral view outer

为什么会多了一个 OUTER 关键字呢，其实你也可以猜到了outer join 有点像，就是为了避免explode 函数返回值是null 的时候，影响我们主表的返回，注意是null 而不是空字符串

select 
   username,role 
from 
    ods.ods_actor_data 
LATERAL VIEW 
     explode(array()) tmpTable as role 
; 

 

  

加上outer 关键字之后：

select 
   username,role 
from 
    ods.ods_actor_data 
LATERAL VIEW outer 
    explode(array()) tmpTable as role 
; 

 

 

一个SQL你可以多次使用lateral view也是可以的,就像下面这样：

SELECT * FROM exampleTable 
LATERAL VIEW explode(col1) myTable1 AS myCol1 
LATERAL VIEW explode(myCol1) myTable2 AS myCol2; 

 

lateral view 中where 的使用

select 
    username,role 
from 
    ods.ods_actor_data 
        LATERAL VIEW 
    explode(split(userrole,',')) tmpTable as role 
where 
    role="导演" 
; 

 

  

使用lateral view的时候需要注意的几点：

1. lateral view的位置是from后where条件前；

2. 生成的虚拟表的表名不可省略；

3. from后可带多个lateral view,就会多次做笛卡尔积；

4. 如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失！

 

参考：

https://www.51cto.com/article/648416.html

https://blog.csdn.net/weixin_38753213/article/details/115364880