复合数据类型
-- 数组 -- 有如下数据: 战狼2,吴京:吴刚:龙母,2017-08-16 三生三世十里桃花,刘亦菲:痒痒,2017-08-20 普罗米修斯,苍老师:小泽老师:波多老师,2017-09-17 美女与野兽,吴刚:加藤鹰,2017-09-17 -- 建表映射: create table t_movie(movie_name string,actors array<string>,first_show date) row format delimited fields terminated by ',' //在这里有一个新的分割方式以“:”为分割点将数据放入数组中 collection items terminated by ':'; -- 导入数据 load data local inpath '/root/hivetest/actor.dat' into table t_movie; load data local inpath '/root/hivetest/actor.dat.2' into table t_movie; -- 查询数组中的所有列 select movie_name,actors[0],first_show from t_movie; -- 查询数组中的带吴刚的列 select movie_name,actors,first_show from t_movie where array_contains(actors,'吴刚'); -- 查询数组的长度 select movie_name ,size(actors) as actor_number ,first_show from t_movie;
#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 '/root/hivetest/fm.dat' into table t_family; +--------------+----------------+----------------------------------------------------------------+---------------+--+ | t_family.id | t_family.name | t_family.family_members | t_family.age | +--------------+----------------+----------------------------------------------------------------+---------------+--+ | 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 | +--------------+----------------+----------------------------------------------------------------+---------------+--+ -- 查出每个人的 爸爸、姐妹//通过key查vlue select id,name,family_members["father"] as father,family_members["sister"] as sister,age from t_family; -- 查出每个人有哪些亲属关系//查询所有的key select id,name,map_keys(family_members) as relations,age from t_family; -- 查出每个人的亲人名字//查询所有的value select id,name,map_values(family_members) as relations,age from t_family; -- 查出每个人的亲人数量//查询map的长度 select id,name,size(family_members) as relations,age from t_family; -- 查出所有拥有兄弟的人及他的兄弟是谁 -- 方案1:一句话写完 /*array_contains(map_keys(family_members),'brother'): 代表着将所有的拥有兄弟的列查出来*/ select id,name,age,family_members['brother'] from t_family where array_contains(map_keys(family_members),'brother'); -- 方案2:子查询 select id,name,age,family_members['brother'] from (select id,name,age,map_keys(family_members) as relations,family_members from t_family) tmp where array_contains(relations,'brother');
#######################################################################################################
hive里直接执行:
select map(1,'a',2,'b')
#struct 假如有以下数据: 1,zhangsan,18:male:深圳 2,lisi,28:female:北京 3,wangwu,38:male:广州 4,赵六,26:female:上海 5,钱琪,35:male:杭州 6,王八,48:female:南京 */ -- 建表映射上述数据 drop table if exists t_user; create table t_user(id int,name string,info struct<age:int,sex:string,addr:string>) row format delimited fields terminated by ',' collection items terminated by ':'; -- 导入数据 load data local inpath '/root/hivetest/user.dat' into table t_user; -- 查询每个人的id name和地址 //info.addr就是相当于java中的对象点属性 select id,name,info.addr from t_user;
————————————————
版权声明:本文为CSDN博主「安安DE爸爸」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_39971163/java/article/details/99291816