好的网站模板,电子商务范围,系统软件开发培训机构,杭州建设工程招投标目录
一、Array
1.建表并插入数据 2.lateral view explode
二、Map
1、建表并插入数据
2、lateral view explode()
3、查询数据 一、Array
1.建表并插入数据
正确插入数据#xff1a;
create table tmp.test_lateral_view_movie_230829(movie string,category array
create table tmp.test_lateral_view_movie_230829(movie string,category arraystring);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 arraystring);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 mapstring,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》;
