Hive中的複雜資料類型

在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;

time taken: 15.958 seconds

2 map的使用

建立資料庫表 

create table score(name string, score map<string,int>)

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

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>)

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

time taken: 13.235 seconds

4 資料組合（不支援組合的複雜資料類型）

create table test1(id int,a map<string,array<string>>)

row format delimited fields terminated by '\t' 

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;

原貼位址：http://blog.csdn.net/wf1982/article/details/7474601