Hive复杂数据类型

2019-03-19

几种复杂数据类型

1
2
3

array_type
map_type
struct_type

array_type：数组

特性：有序、数组内数据类型一样

基础数据hive_array.txt

1 2	dashu beijing,shanghai,tianjin,hangzhou xiaohua chongqing,chengdu,wuhan,beijing

建表语句

create table hive_array(
name string,
work_locations array<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

- 这里使用COLLECTION ITEMS TERMINATED指定数组之间的分隔符是','

导入数据

1	load data local inpath '/home/hadoop/data/hive_array.txt' overwrite into table hive_array;

查询数据

hive> select * from hive_array;
OK
hive_array.name	hive_array.work_locations
dashu	["beijing","shanghai","tianjin","hangzhou"]
xiaohua	["chongqing","chengdu","wuhan","beijing"]

取数据

- 取数组中的第0个元素：直接[坐标]取值
hive> select name,work_locations[0] from hive_array;
OK
name	_c1
dashu	beijing
xiaohua	chongqing

- 求每个人上班的地方有几个：借助size函数
hive> select name,size(work_locations) from hive_array;
OK
name	_c1
dashu	4
xiaohua	4

- 求在tianjin上班的人：借助array_contains函数
hive> select name from hive_array where array_contains(work_locations,'tianjin');
OK
name
dashu

map_type：字典

特点：key-value一一对应

基础数据hive_map.txt

1
2
3

1,zhangsan,father:xiaoming#mother:xiaohong,18
2,lisi,father:xiaoli#mother:xiaohua,23
3,wangwu,father:xiaowang#mother:xiaobai,46

建表语句

create table hive_map(
id int,
name string,
members map<string,string>,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';

- 使用COLLECTION ITEMS TERMINATED BY定义数组之间使用'#分隔'
- 使用MAP KEYS TERMINATED BY定义对象之间使用':'分隔

查看数据

hive> select * from hive_map;
OK
hive_map.id	hive_map.name	hive_map.members	hive_map.age
1	zhangsan	{"father":"xiaoming","mother":"xiaohong"}	18
2	lisi	{"father":"xiaoli","mother":"xiaohua"}	23
3	wangwu	{"father":"xiaowang","mother":"xiaobai"}	46

数据操作

- 查询每个人的父亲：使用字段名['字段值']
hive> select id,name,age,members['father'] from hive_map;
OK
id	name	age	_c3
1	zhangsan	18	xiaoming
2	lisi	23	xiaoli
3	wangwu	46	xiaowang

- 查询所有的keys：使用map_keys
hive> select map_keys(members) from hive_map;
OK
_c0
["father","mother"]
["father","mother"]
["father","mother"]

- 查询所有的values：使用map_values
hive> select map_values(members) from hive_map;
OK
_c0
["xiaoming","xiaohong"]
["xiaoli","xiaohua"]
["xiaowang","xiaobai"]

- 对象中值的数量
hive> select size(members) from hive_map;
OK
_c0
2
2
2

struct_type：结构体

特性：既可以放map也可以放list

基础数据hive_struct.txt

1
2
3

192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60

建表语句

create table hive_struct(
ip string,
userinfo struct<name:string,age:int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '#'
COLLECTION ITEMS TERMINATED BY ':';

加载数据

1	load data local inpath '/home/hadoop/data/hive_struct.txt' into table hive_struct;

查询数据：使用.进行取值

hive> select userinfo.name from hive_struct;
OK
name
zhangsan
lisi
wangwu