复合数据类型map
数据如下
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26
建表
create table t_family(id int, name string, family_members map<string,string>, age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':'; -----注意!!!!
插入数据
load data local inpath '/home/map.txt' into table t_family;
查出每个人的爸爸
select id,name,family_members["father"] from t_family;
查出每个人有哪些亲属关系
select id,name,map_keys(family_members) as ralations from t_family;
查出每个人的亲人名字
select id,name,map_values(family_members) as ralations from t_family;
查出每个人的亲人数量
select id,name,size(family_members) as relations,age
from t_family;
查出所有拥有兄弟的人及他的兄弟是谁
select id,name,age, family_members["brother"] as brother from t_family where array_contains(map_keys(family_members),"brother");