关于hive中复杂类型

1.array类型
array是数组类型,Array中存放相同类型的数据
源数据:
说明:name与locations之间制表符分隔，locations中元素之间逗号分隔
zhangsan beijing,shanghai,tianjin,hangzhou
wangwu changchun,chengdu,wuhan,beijin

建表语句
create external table hive_array(name string, work_locations array)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’;

导入数据（从本地导入，同样支持从HDFS导入）
load data local inpath ‘/export/data/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’);

4.2.5.2.map类型
map就是描述key-value数据
源数据:
说明：字段与字段分隔符: “,”；需要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<string,string>, age int
)
row format delimited
fields terminated by ‘,’
collection items terminated by ‘#’
map keys terminated by ‘:’;

导入数据
load data local inpath ‘/export/data/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;
#获取有指定key的数据
select * from hive_map where array_contains(map_keys(members), ‘brother’);
#查找包含brother这个键的数据，并获取brother键对应的值
select id,name, members[‘brother’] brother from hive_map where array_contains(map_keys(members), ‘brother’);

3.struct类型
源数据：
说明：字段之间#分割，第二个字段之间冒号分割
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 string, info struct<name:string, age:int>
)
row format delimited
fields terminated by ‘#’
collection items terminated by ‘:’;

导入数据
load data local inpath ‘/export/data/hivedatas/hive_struct.txt’ into table hive_struct;

常用查询
select * from hive_struct;
#根据struct来获取指定的成员的值
select ip, info.name from hive_struct;
中struct<array>类型简介
取树方法
结构体名.需要字段名[array中元素位置]