hive数据类型--复合类型

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_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

相当于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.addr
from t_user;