Hive的三种复杂数据类型

Hive的复杂数据类型主要分为3类：array、map 和 struct。

1. array

测试数据: 列间用 \t 分隔，第二列用逗号分隔。

zhangsan chengdu,shanghai,beijing lisi tianjin,taiyuan,chongqing wangwu xian,nanning,beijing

建表：

create table hive_array( name string, locations array<string> ) row format delimited fields terminated by '\t' collection items terminated by ',';

查询：

//取出数组的第一个元素 select name, locations[0] from hive_array; //返回数组长度 select name, size(locations) from hive_array; //包含返回true，否则false select * from hive_array where array_contains(locations,'tianjin');

2. map

测试数据:

1,zhangsan,father:xiaoming#mother:xiaohuang,28 2,lisi,father:xiaoxi#mother:xiaofang#brother:xiaoli,22 3,wangwu,father:xiaoqiang#mother:xiaohong#brother:xiaowang,35

建表：

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 ':'; //组内分隔符

查询：

select name, members['father'], members['mother'] from hive_map; select name, map_keys(members) from hive_map; select name, size(members) from hive_map; select name, members['brother'] from hive_map where array_contains(map_keys(members),'brother'); //拥有兄弟的人及兄弟名称

3. struct

测试数据:

192.168.1.1#zhangsan:40 192.168.1.2#lisi:25 192.168.1.3#wangwu:38

建表：

create table hive_struct( ip string, userinfo struct<name:string,age:int>, ) row format delimited fields terminated by '#' collection items terminated by ':'; //组间分隔符

查询：

select ip, userinfo.name, userinfo.age from hive_struct;