Hive中有哪些集合数据类型

这篇文章给大家介绍Hive中有哪些集合数据类型，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

除了使用础的数据类型string等，Hive中的列支持使用struct, map, array集合数据类型。

数据类型描述语法示例
STRUCT 和C语言中的struct或者"对象"类似，都可以通过"点"符号访问元素内容。 struct{'John', 'Doe'}
MAP MAP是一组键-值对元素集合，使用key可以访问元素。 map('fisrt', 'John', 'last', 'Doe')
ARRAY 数组是一组具有相同数据类型和名称的变量的集合。 Array('John', 'Doe')

1. Array的使用

创建数据库表，以array作为数据类型

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

数据

biansutao beijing,shanghai,tianjin,hangzhou linan changchu,chengdu,wuhan

入库数据

LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;

查询

hive> select * from person; biansutao ["beijing","shanghai","tianjin","hangzhou"] linan ["changchu","chengdu","wuhan"] Time taken: 0.355 seconds hive> select name from person; linan biansutao Time taken: 12.397 seconds hive> select work_locations[0] from person; changchu beijing Time taken: 13.214 seconds hive> select work_locations from person; ["changchu","chengdu","wuhan"] ["beijing","shanghai","tianjin","hangzhou"] Time taken: 13.755 seconds hive> select work_locations[3] from person; NULL hangzhou Time taken: 12.722 seconds hive> select work_locations[4] from person; NULL NULL Time taken: 15.958 seconds

2. Map 的使用

创建数据库表

create table score(name string, score map<string,int>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';

要入库的数据

biansutao '数学':80,'语文':89,'英语':95 jobs '语文':60,'数学':80,'英语':99

入库数据

LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;

查询

hive> select * from score; biansutao {"数学":80,"语文":89,"英语":95} jobs {"语文":60,"数学":80,"英语":99} Time taken: 0.665 seconds hive> select name from score; jobs biansutao Time taken: 19.778 seconds hive> select t.score from score t; {"语文":60,"数学":80,"英语":99} {"数学":80,"语文":89,"英语":95} Time taken: 19.353 seconds hive> select t.score['语文'] from score t; 60 89 Time taken: 13.054 seconds hive> select t.score['英语'] from score t; 99 95 Time taken: 13.769 seconds

修改map字段的分隔符

Storage Desc Params: colelction.delim ## field.delim \t mapkey.delim = serialization.format \t

可以通过desc formatted tableName查看表的属性。

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。