hive中的三种复杂类型

一、数组类型:array
1、建表语句

create table citys(
city String,
area array<String>)
row fromat delimited
fields terminated by ' ' ##表示行字段按空格切割
collection items terminated by ','; ##表示数组字段按逗号切割

2、将数据导入表中

load data local inpath '/1/city.txt' into table citys;

3、查询语句

3.1只查询数组的语句
select explode(area) from citys;
3.1和其他字段一起查询
select city,areas from citys lateral view explode(area) citys as areas;
3.2进行条件查找
select city,areas from citys lateral view explode(area) citys as areas where city='武汉';
3.3分组查询
select city,count(areas) from citys lateral view explode(area) city as areas group by city;

二、map类型
1、建表语句

create table person(
name String,
object map<String,int>)
row format delimited
fields terminated by ' '
collection items terminated by ','
map keys terminated by ':';

2.将数据导入表中

load data local inpath '/1/object.txt' into table person

3.查询语句

1.查询所有
select * from person;
2.查询map里面的数据
select explode(object) from person;
3.查询所有的向下信息
select name,objects,numbers from person lateral view explode(object) person as objects,numbers;
4.使用聚合函数查询
select objects,sum(numbers) from person lateral view explode(object) person as objects,numbers
group by objects;

三、struct类型
1.建表语句

create table addr_city(
name String,
addr struct<city:String,qu:String,jiedao:String>)
row format delimited
fields terminated by ' '
collection items terminated by ',';

2.导数据

load data local inpath '/1/addr.txt' into table addr_city;

3.查询语句

3.1查询所有
select * from addr_city;
3.2查询struce里面的内容，其中struct类似于java中的对象
select name,addr.city from addr_city;