-- 数组 -- 有如下数据： 战狼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');
#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
array数组-- 数组-- 有如下数据：战狼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_showfrom t_movie where array_contains(actors,'吴刚');
-- 查询数组的长度select movie_name,size(actors) as actor_number,first_showfrom t_movie;1234567891011121314151617181920212223242526272829map集合-- 有如下数据：1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,282,lisi,father:mayun#mother:huangyi#brother:guanyu,223,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,294,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查vlueselect id,name,family_members["father"] as father,family_members["sister"] as sister,agefrom t_family;
-- 查出每个人有哪些亲属关系//查询所有的keyselect id,name,map_keys(family_members) as relations,agefrom t_family;
-- 查出每个人的亲人名字//查询所有的valueselect id,name,map_values(family_members) as relations,agefrom t_family;
-- 查出每个人的亲人数量//查询map的长度select id,name,size(family_members) as relations,agefrom 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');1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253struct相当于java中的类
/* hive数据类型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.addrfrom t_user;————————————————版权声明：本文为CSDN博主「安安DE爸爸」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/weixin_39971163/java/article/details/99291816