hive数组使用

转：http://blog.sina.com.cn/s/blog_61c463090100rt4h.html

 

数据
afan@ubuntu:/usr/local/hadoop/hive$ cat test.txt
12,23,23,34 

   what,are,this
34,45,34,23,12    who,am,i,are

afan@ubuntu:/usr/local/hadoop/hive$ hive
Hive history file=/tmp/afan/hive_job_log_afan_201105240353_929616223.txt
hive> drop table t_afan_test;
OK
Time taken: 3.288 seconds
hive> create table t_afan_test
    > (
    > info1 array<int>,
    > info2 array<string>
    > )
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > COLLECTION ITEMS TERMINATED BY ','
    > ;
OK
Time taken: 0.58 seconds
hive> LOAD DATA LOCAL INPATH 'test.txt' OVERWRITE INTO TABLE t_afan_test;
Copying data from file:/usr/local/hadoop/hive/test.txt
Copying file: file:/usr/local/hadoop/hive/test.txt
Loading data to table default.t_afan_test
Deleted hdfs://localhost:9000/user/hive/warehouse/t_afan_test
OK
Time taken: 0.742 seconds
hive> select * from t_afan_test;
OK
[12,23,23,34]    ["what","are","this"]
[34,45,34,23,12]    ["who","am","i","are"]
Time taken: 0.429 seconds
hive> select size(info1), size(info2) from t_afan_test;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201105240347_0001, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201105240347_0001
Kill Command = /usr/local/hadoop/bin/../bin/hadoop job  -Dmapred.job.tracker=localhost:9001 -kill job_201105240347_0001
2011-05-24 03:55:49,564 Stage-1 map = 0%,  reduce = 0%
2011-05-24 03:55:52,597 Stage-1 map = 50%,  reduce = 0%
2011-05-24 03:55:55,653 Stage-1 map = 100%,  reduce = 0%
Ended Job = job_201105240347_0001
OK
4    3
5    4
Time taken: 20.171 seconds
hive> select info1[2], info2[0] from t_afan_test;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201105240347_0002, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201105240347_0002
Kill Command = /usr/local/hadoop/bin/../bin/hadoop job  -Dmapred.job.tracker=localhost:9001 -kill job_201105240347_0002
2011-05-24 03:56:37,513 Stage-1 map = 0%,  reduce = 0%
2011-05-24 03:56:40,543 Stage-1 map = 100%,  reduce = 0%
2011-05-24 03:56:43,611 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201105240347_0002
OK
23    what
34    who
Time taken: 10.88 seconds