explode和inline函數(shù)可以將單列擴展成多列或者多行。
1.explode將單列擴展成多行
select explode(subordinates) from employees;
image.png
explode的參數(shù)可以是array還可以是map淑翼，如果是map,將生成2個字段毡琉，一個是map的鍵,字段名為key乡话，一個是map的值,字段的名為：value,如下：
select explode(deductions) from employees ;
image.png
explode的限制荞估，如下sql不支持：
- 不能和其他字段一起使用
select name,explode(subordinates) from employees;
image.png
- 不支持函數(shù)嵌套
select explode(explode(subordinates)) from employees
image.png
- 不能和group by貌笨、sort by 和cluster by一起使用
select explode(subordinates)
from employees
group by explode(subordinates)
image.png
2.inline
inline的參數(shù)形式：inline(ARRAY<STRUCT[,STRUCT]>)
inline一般結(jié)合lateral view使用
select t1.col1 as name,t1.col2 as sub1
from employees
lateral view inline(array(struct(name,subordinates[0]))) t1
image.png
inline 嵌套多個struct,
select t1.col1 as name,t1.col2 as sub
from employees
lateral view inline(array(struct(name,subordinates[0]),
struct(name,subordinates[1]))) t1
where t1.col2 is not null
image.png
還可以給inline的字段取別名：
select t1.name,t1.sub
from employees
lateral view inline(array(struct(name,subordinates[0]),
struct(name,subordinates[1]))) t1 as name,sub
where t1.sub is not null
image.png
