Hive的数据类型

Hive支持的数据类型如下：

原生类型:

• TINYINT
• SMALLINT
• INT
• BIGINT
• BOOLEAN
• FLOAT
• DOUBLE
• STRING
• BINARY (Hive 0.8.0以上才可用)
• TIMESTAMP (Hive 0.8.0以上才可用)

复合类型:

• arrays: ARRAY<data_type>
• maps: MAP<primitive_type, data_type>
• structs: STRUCT<col_name : data_type [COMMENT col_comment], ...>
• union: UNIONTYPE<data_type, data_type, ...>

我没有从数据类型本身来讲Hive支持的数据类型，这里只是举几个有关复合类型的例子。有一点面向对象编程知识的，很容易理解这几种复合数据类型，ARRAY类似于java数组，Map类似于java集合，struct类似于json。下面的例子转发自：http://blog.csdn.net/wf1982/article/details/7474601

在Hive 中如何使用符合数据结构  maps，array，structs

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

 

3 Struct 的使用

 创建数据表

 

CREATE TABLE test(id int,course struct<course:string,score:int>)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ',';

 

数据

1 english,80

2 math,89

3 chinese,95

 

入库

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

 

 

查询

 

 

hive> select * from test;

OK

1       {"course":"english","score":80}

2       {"course":"math","score":89}

3       {"course":"chinese","score":95}

Time taken: 0.275 seconds

 

hive> select course from test;

输出： 

{"course":"english","score":80}

{"course":"math","score":89}

{"course":"chinese","score":95}

Time taken: 44.968 seconds

 

select t.course.course from test t; 

输出： 

english

math

chinese

Time taken: 15.827 seconds

 

hive> select t.course.score from test t;

输出： 

80

89

95

Time taken: 13.235 seconds

 

4. 数据组合 （不支持组合的复杂数据类型）

 

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

create table test1(id int,a MAP<STRING,ARRAY<STRING>>)

row format delimited fields terminated by '\t' 

collection items terminated by ','

MAP KEYS TERMINATED BY ':';

 

1 english:80,90,70

2 math:89,78,86

3 chinese:99,100,82

 

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