hive非基本类型（array、map、struct）

array

数据：
gaoyuanyuan0 87,98,97,60
gaoyuanyuan1 88,98,94,60
gaoyuanyuan2 89,98,97,60,100

建表语句

create table if not exists arr2(
name string,
score array<string>
)row format delimited
fields terminated by "\t"
collection items terminated by ","
;

load数据

load data inpath "/user/hdfs/arr.txt" i
nto table arr2;

普通查询

select name,
score[0] ,
score[1],
score[2]
 from arr2;

不定长全部查询

1.explode展开

--explode展开:
select explode(score)
 from arr2;

2.lateral view

--lateral view:
select name,mscore
from arr2 
lateral view explode(score) sc
 as mscore;

3.把查询结果放入表中

create table if not exists arr3
as
select name,mscore
from arr2
 lateral view explode(score) sc 
 as mscore;

collect_list:

create table if not exists aa as
select name,
concat_ws(",",collect_list(mscore))
from arr3
group by name;

map

数据
marong lazhu:2,pibian:1,huanghong:2
songjingjing lazhu:3,pibian:3,huanghong:4
songwei lazhu:3,pibian:3,shoukao:3,huanghong:4
创表语句

create table if not exists map1(
name string,
item map<string,int>)row format delimited
fields terminated by "\t"
collection items terminated by ","
map keys terminated by ":";

load数据

load data local inpath "/home/hdfs/map1.txt" 
into table map1;

查找买了shoukao的用户的pibian的购买量

select name,
item["pibian"]
from map1
where item["shoukao"] is not null;

求每一个商品的销售的数量

select explode(item) from map1;

leteral view :

select name,itemname,num
from map1 lateral view explode(item) it 
as itemname,num;

select itemname,sum(num)
from (
select name,itemname,num
from map1 lateral view explode(item) it as itemname,num
) a
group by itemname;

数据写入map：

• str_to_map:(str,deliminated1,deliminated2)
• map:(k1,m1,k2,m2…)

create table if not exists map_tmp
as
select name,itemname,num
from map1 lateral view explode(item) it as itemname,num;

select name,concat(itemname,":",num)
from  map_tmp; 


insert into map2
select name,str_to_map(
concat_ws(",",
    collect_list(
      concat(itemname,":",num)
      )
    )
)
from  map_tmp
group by name; 

struct

数据
cls japan,dongjing,xichuanxi18
wangxuejia japan,dongjing,sss123
建表语句

create table if not exists struct1(
name string,
addr struct<province:string,city:string,street:string>)
row format delimited
fields terminated by "\t"
collection items terminated by ","
;

load data

load data local inpath "/home/hdfs/struct.txt" 
into table struct1;

查询城市在dongjing的用户的省份

select 
name,addr.province,addr.street
from struct1
where addr.city="dongjing";