集合数据类型

一、array

hadoop@hadoop:~/input/hive/collection$ sudo vim person.txt

person.txt内容、里面的制表符使用\t(tab键)

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

 

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

OK
Time taken: 5.065 seconds

hive> load data local inpath '/home/hadoop/input/hive/collection/person.txt' overwrite into table person;
Loading data to table default.person
OK
Time taken: 1.971 seconds
hive> select * from person;
OK
biansutao    ["beijing","shanghai","tianjin","hangzhou"]
linan    ["changchu","chengdu","wuhan"]
Time taken: 0.222 seconds, Fetched: 2 row(s)
hive> select name from person;
OK
biansutao
linan
Time taken: 0.209 seconds, Fetched: 2 row(s)
hive> select work_locations[0] from person;
OK
beijing
changchu
Time taken: 0.217 seconds, Fetched: 2 row(s)
hive> select work_locations from person;   
OK
["beijing","shanghai","tianjin","hangzhou"]
["changchu","chengdu","wuhan"]
Time taken: 0.21 seconds, Fetched: 2 row(s)
hive> select work_locations[3] from person;   
OK
hangzhou
NULL
Time taken: 0.215 seconds, Fetched: 2 row(s)
hive> select work_locations[4] from person;   
OK
NULL
NULL
Time taken: 0.2 seconds, Fetched: 2 row(s)
hive>

 

二、map

hadoop@hadoop:~/input/hive/collection$ sudo vim score.txt

score.txt

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

 

hive> create table score(name string, score map<string,int>)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > COLLECTION ITEMS TERMINATED BY ','
    > MAP KEYS TERMINATED BY ':';
OK
Time taken: 0.513 seconds
hive> load data local inpath '/home/hadoop/input/hive/collection/score.txt' overwrite into table score;
Loading data to table default.score
OK
Time taken: 1.077 seconds
hive>  select * from score;
OK
biansutao    {"'数学'":80,"'语文'":89,"'英语'":95}
jobs    {"'语文'":60,"'数学'":80,"'英语'":99}
Time taken: 0.198 seconds, Fetched: 2 row(s)
hive>  select name from score;
OK
biansutao
jobs
Time taken: 0.18 seconds, Fetched: 2 row(s)
hive>  select t.score from score t;
OK
{"'数学'":80,"'语文'":89,"'英语'":95}
{"'语文'":60,"'数学'":80,"'英语'":99}
Time taken: 0.18 seconds, Fetched: 2 row(s)

hive> select t.score["'语文'"] from score t;
OK
89
60
Time taken: 0.24 seconds, Fetched: 2 row(s)
hive> select t.score["'英语'"] from score t;
OK
95
99
Time taken: 0.187 seconds, Fetched: 2 row(s)
hive>

 

三、Struts

hadoop@hadoop:~/input/hive/collection$ sudo vim test.txt

 

1       english,80
2       math,89
3       chinese,95

 

hive> CREATE TABLE test(id int,course struct<course:string,score:int>)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    COLLECTION ITEMS TERMINATED BY ',';
OK
Time taken: 0.376 seconds
hive> load data local inpath '/home/hadoop/input/hive/collection/test.txt' overwrite into table test;
Loading data to table default.test
OK
Time taken: 0.709 seconds
hive> select * from test ;
OK
1    {"course":"english","score":80}
2    {"course":"math","score":89}
3    {"course":"chinese","score":95}
Time taken: 0.186 seconds, Fetched: 3 row(s)
hive> select course from test;
OK
{"course":"english","score":80}
{"course":"math","score":89}
{"course":"chinese","score":95}
Time taken: 0.187 seconds, Fetched: 3 row(s)
hive> select t.course.score from test t;
OK
80
89
95
Time taken: 0.198 seconds, Fetched: 3 row(s)

hive> select t.course.course from test t;
OK
english
math
chinese
Time taken: 0.187 seconds, Fetched: 3 row(s)
hive>

 

四、集合

hadoop@hadoop:~/input/hive/collection$ sudo vim test1.txt

 

1       english:80,90,70
2       math:89,78,86
3       chinese:99,100,82

 

hive> 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 ':';
OK
Time taken: 0.636 seconds
hive> select * from test1;
OK
Time taken: 0.184 seconds
hive> load data local inpath '/home/hadoop/input/hive/collection/test1.txt' overwrite into table test1;
Loading data to table default.test1
OK
Time taken: 0.643 seconds
hive> select * from test1;
OK
1    {"english":["80"],"90":null,"70":null}
2    {"math":["89"],"78":null,"86":null}
3    {"chinese":["99"],"100":null,"82":null}
Time taken: 0.174 seconds, Fetched: 3 row(s)
hive>