hive中复杂的数据类型array与map

1.array

//创建表

create table if not exists arr2(
name string,
score array<String>
)
row format delimited fields terminated by '\t'     //字段之间的分隔符
collection items terminated by ','          //array之间的分隔符
;

//数据
zhangsan	78,89,92,96
lisi 	67,75,83,94

//加载数据
load data local inpath '/root/test/arraydata' into arr2;

//查询数据
select name,score[1] from arr2 where size(score) > 3;

数组的某个值查询直接字段名[index]

//将数组的数据拆分开并插入到arr_temp表
create table arr_temp
as
select name,cj from arr2 lateral view explode(score) score as cj;
拆分后的结果为:

//统计某个学生的总成绩
select name,sum(cj) as totalscore 
from arr2 lateral view explode(score) score as cj group by name;

//将拆分的数据合并并写到arr_temp2表
create table arr_temp2
as
select name,collect_set(cj) from arr_temp group by name;
合并后的结果为:

2.map

//创建表
create table if not exists map2(
name string,
score map<string,int>
)
row format delimited fields terminated by ' '
collection items terminated by ','
map keys terminated by ':'
;

//数据
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9

load data local inpath '/root/test/mapdata' into map2;

//查询
查询数学大于35分的学生的英语和自然成绩：
select 
m.name,
m.score['english'] ,
m.score['nature']
from map2 m
where m.score['math'] > 35
;

map的某个值查询使用字段名[key]

//拆分数据
select explode(score) as (m_class,m_score) from map2;

//拆分数据并插入到map2_temp表中
create table map2_temp
as
select name,m_class,m_score from map2 
lateral view explode(score) score as m_class,m_score;

结果为：

//合并拆分的数据
select name,collect_set(concat_ws(":",m_class,cast(m_score as string)))
 from map2_temp group by name;