Lateral View往往与explode或是split函数结果使用
样例表：
Jack,male,eat-play,北京:海淀-上海:浦东
Miso,male,eat-net-play,河北:保定-北京:海淀
小明,male,eat-net,河北:张家口-上海:浦东
小红,male,net-play,河北:保定-北京:海淀
Lily,female,eat-net,河北:保定-北京:海淀
Lucy,female,play-eat,北京:海淀-上海:浦东
Jack,male,eat-net-play,河北:保定-北京:海淀
Miso,male,online-net-eat,河北:保定-北京:海淀
create table tb_address(
name string,
sex string,
likes array<string>,
address map<string,string>
)
row format delimited fields terminated by ‘,’
collection items terminated by ‘-‘
map keys terminated by ‘:’
lines terminated by ‘\n’;
load data local inpath ‘/home/user_address.txt’ into table tb_address;
create table tb_count(likecount int,citycount int,areacount int) row format delimited fields terminated by ‘,’;
from tb_address
insert into tb_count
select count(distinct(t1.like1)) as lc,count(distinct(t2.kcity)) as ca
lateral view explode(likes) t1 as like1
lateral view explode(address) t2 as kcity,karea;