复合数据类型

-- 数组
-- 有如下数据：
战狼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