hive lateral view 实践记录（Array和Map数据类型）

目录

一、Array

1.建表并插入数据

 2.lateral view explode

二、Map

1、建表并插入数据

2、lateral view explode()

3、查询数据

---

一、Array

1.建表并插入数据

正确插入数据：

create table tmp.test_lateral_view_movie_230829(movie string,category array<string>);insert into tmp.test_lateral_view_movie_230829 select '《战狼3》',array('战争','动作','剧情');
insert into tmp.test_lateral_view_movie_230829 select '《疑犯追踪》',array('悬疑','动作','科幻','剧情');select * from tmp.test_lateral_view_movie_230829;

原数据

 2.lateral view explode

select movie,cate_name 
from tmp.test_lateral_view_movie_230829 
lateral view explode(category) tmp_view as cate_name

结果：

 --------最开始错误的插入数据法-------

 原数据

create table tmp.test_lateral_view_movie_230828(movie string,category array<string>);select * from tmp.test_lateral_view_movie_230828;insert into tmp.test_lateral_view_movie_230828 select '《疑犯追踪》',array('悬疑,动作,科幻,剧情');
insert into tmp.test_lateral_view_movie_230828 select '《疑犯追踪2》',array('悬疑,动作,科幻,剧情');
insert into tmp.test_lateral_view_movie_230828 select '《战狼》',array('战争,动作,剧情');
insert into tmp.test_lateral_view_movie_230828 select '《战狼2》',array('战争,动作,剧情');
insert into tmp.test_lateral_view_movie_230828 select '《战狼3》',array('战争,动作,剧情');

 step1：

select 
movie
,category_detail
from tmp.test_lateral_view_movie_230828 lateral view explode(category) tmp as category_detail

step2：

select movie,category_detail_name
from 
(select movie,category_detailfrom tmp.test_lateral_view_movie_230828 lateral view explode(category) tmp as category_detail 
) a
lateral view explode(split(category_detail,',')) tmp as category_detail_name

备注：

select a.movie,split(a.category_detail,',') aaa,b.category bbb 
from 
(select movie,category_detailfrom tmp.test_lateral_view_movie_230828 lateral view explode(category) tmp as category_detail 
) a
left join 
(
select * from tmp.test_lateral_view_movie_230828
) b 
on a.movie = b.movie

比原表数据少了 双引号

综上，以上的插入数据是不对的！！！

-----------

注意：

1.array类型数据，建表时怎么插入？

array('悬疑','动作','科幻','剧情')

2.array类型的数据，怎么根据下标获取里面的值？

select movie,category[0] ,category[1] ,category[2] 
from tmp.test_lateral_view_movie_230829 

二、Map

1、建表并插入数据

--map类型测试
create table tmp.test_lateral_view_movie_230830_map(movie string,category map<string,string>);insert into tmp.test_lateral_view_movie_230830_map select '《战狼3》',str_to_map('1:战争,2:动作,3:剧情');
insert into tmp.test_lateral_view_movie_230830_map select '《疑犯追踪》',str_to_map('a:悬疑,b:动作,c:科幻,d:剧情');select * from tmp.test_lateral_view_movie_230830_map;

注：通过str_to_map（）函数实现插入数据

2、lateral view explode()

selectmovie,category_id,category_name
from tmp.test_lateral_view_movie_230830_map
lateral view explode(category) tmp_view as category_id,category_name 
;

注：as 后是两个参数

结果

3、查询数据

select movie,category['1'] from tmp.test_lateral_view_movie_230830_map where movie = '《战狼3》';