Hive复杂类型操作

说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

zhangsan	  beijing,shanghai,tianjin,hangzhou
wangwu   	changchun,chengdu,wuhan,beijin

create table hive_array(name string,work_location array)
row format delimited fields terminated by 't'
COLLECTION ITEMS TERMINATED BY ',';

导入数据（从本地导入，同样支持从HDFS导入）

load data local inpath '/export/servers/hivedatas/work_locations.txt' overwrite into table hive_array;

常见查询：

常用查询：
-- 查询所有数据
select * from hive_array;
-- 查询loction数组中第一个元素
select name, work_locations[0] location from hive_array;
-- 查询location数组中元素的个数
select name, size(work_locations) location from hive_array;
-- 查询location数组中包含tianjin的信息
select * from hive_array where array_contains(work_locations,'tianjin'); 

说明：字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

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 hive_map(
id int,name string,members map,age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';

导入数据

load data local inpath '/export/servers/hivedatas/hive_map.txt' overwrite into table hive_map;

常见查询：

select * from hive_map;
select id, name, members['father'] father, members['mother'] mother, age from hive_map;
select id, name, map_keys(members) as relation from hive_map;
select id, name, map_values(members) as relation from hive_map;
select id,name,size(members) num from hive_map;
select * from hive_map where array_contains(map_keys(members), 'brother');
select id,name, members['brother'] brother from hive_map where array_contains(map_keys(members), 'brother');

源数据：

说明：字段之间#分割，第二个字段之间冒号分割

192.168.1.1#zhangsan:40
192.168.1.2#lisi:50
192.168.1.3#wangwu:60
192.168.1.4#zhaoliu:70

建表语句

create table hive_struct(
ip sting,info struct
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

导入数据

load data local inpath '/export/servers/hivedatas/hive_struct.txt' into table hive_struct;

常见查询

select * from hive_struct;
select ip, info.name from hive_struct;