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";