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;