一、array
hadoop@hadoop:~/input/hive/collection$ sudo vim person.txtperson.txt内容、里面的制表符使用 (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 ' '
> 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 ' '
> 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 ' '
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 ' '
> 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>