hive array、map、struct使用

hive提供了复合数据类型：
Structs： structs内部的数据可以通过DOT（.）来存取，例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a
Maps（K-V对）：访问指定域可以通过["指定域名称"]进行，例如，一个Map M包含了一个group-》gid的kv对，gid的值可以通过M['group']来获取
Arrays：array中的数据为相同类型，例如，假如array A中元素['a','b','c']，则A[1]的值为'b'

Struct使用

建表：

[plain] view plain copy print ?

1. hive> create table student_test(id INT, info struct<name:STRING, age:INT>)  
2.     > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','                         
3.     > COLLECTION ITEMS TERMINATED BY ':';                                   
4. OK  
5. Time taken: 0.446 seconds  

hive> create table student_test(id INT, info struct<name:STRING, age:INT>)
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','                       
    > COLLECTION ITEMS TERMINATED BY ':';                                 
OK
Time taken: 0.446 seconds

'FIELDS TERMINATED BY' ：字段与字段之间的分隔符
''COLLECTION ITEMS TERMINATED BY' ：一个字段各个item的分隔符 
导入数据：

[plain] view plain copy print ?

1. $ cat test5.txt   
2. 1,zhou:30  
3. 2,yan:30  
4. 3,chen:20  
5. 4,li:80  
6. hive> LOAD DATA LOCAL INPATH '/home/work/data/test5.txt' INTO TABLE student_test;  
7. Copying data from file:/home/work/data/test5.txt  
8. Copying file: file:/home/work/data/test5.txt  
9. Loading data to table default.student_test  
10. OK  
11. Time taken: 0.35 seconds  

$ cat test5.txt 
1,zhou:30
2,yan:30
3,chen:20
4,li:80
hive> LOAD DATA LOCAL INPATH '/home/work/data/test5.txt' INTO TABLE student_test;
Copying data from file:/home/work/data/test5.txt
Copying file: file:/home/work/data/test5.txt
Loading data to table default.student_test
OK
Time taken: 0.35 seconds

查询：

[plain] view plain copy print ?

1. hive> select info.age from student_test;  
2. Total MapReduce jobs = 1  
3. ......  
4. Total MapReduce CPU Time Spent: 490 msec  
5. OK  
6. 30  
7. 30  
8. 20  
9. 80  
10. Time taken: 21.677 seconds  

hive> select info.age from student_test;
Total MapReduce jobs = 1
......
Total MapReduce CPU Time Spent: 490 msec
OK
30
30
20
80
Time taken: 21.677 seconds

Array使用
建表：

[plain] view plain copy print ?

1. hive> create table class_test(name string, student_id_list array<INT>)  
2.     > ROW FORMAT DELIMITED                                              
3.     > FIELDS TERMINATED BY ','                                          
4.     > COLLECTION ITEMS TERMINATED BY ':';                               
5. OK  
6. Time taken: 0.099 seconds  

hive> create table class_test(name string, student_id_list array<INT>)
    > ROW FORMAT DELIMITED                                            
    > FIELDS TERMINATED BY ','                                        
    > COLLECTION ITEMS TERMINATED BY ':';                             
OK
Time taken: 0.099 seconds

导入数据：

[plain] view plain copy print ?

1. $ cat test6.txt   
2. 034,1:2:3:4  
3. 035,5:6  
4. 036,7:8:9:10  
5. hive>  LOAD DATA LOCAL INPATH '/home/work/data/test6.txt' INTO TABLE class_test ;  
6. Copying data from file:/home/work/data/test6.txt  
7. Copying file: file:/home/work/data/test6.txt  
8. Loading data to table default.class_test  
9. OK  
10. Time taken: 0.198 seconds  

$ cat test6.txt 
034,1:2:3:4
035,5:6
036,7:8:9:10
hive>  LOAD DATA LOCAL INPATH '/home/work/data/test6.txt' INTO TABLE class_test ;
Copying data from file:/home/work/data/test6.txt
Copying file: file:/home/work/data/test6.txt
Loading data to table default.class_test
OK
Time taken: 0.198 seconds

查询：

[plain] view plain copy print ?

1. hive> select student_id_list[3] from class_test;  
2. Total MapReduce jobs = 1  
3. ......  
4. Total MapReduce CPU Time Spent: 480 msec  
5. OK  
6. 4  
7. NULL  
8. 10  
9. Time taken: 21.574 seconds  

hive> select student_id_list[3] from class_test;
Total MapReduce jobs = 1
......
Total MapReduce CPU Time Spent: 480 msec
OK
4
NULL
10
Time taken: 21.574 seconds

Map使用
建表：

[plain] view plain copy print ?

1. hive> create table employee(id string, perf map<string, int>)       
2.     > ROW FORMAT DELIMITED                                          
3.     > FIELDS TERMINATED BY '\t'                                
4.     > COLLECTION ITEMS TERMINATED BY ','                       
5.     > MAP KEYS TERMINATED BY ':';                                    
6. OK  
7. Time taken: 0.144 seconds  

hive> create table employee(id string, perf map<string, int>)     
    > ROW FORMAT DELIMITED                                        
    > FIELDS TERMINATED BY '\t'                              
    > COLLECTION ITEMS TERMINATED BY ','                     
    > MAP KEYS TERMINATED BY ':';                                  
OK
Time taken: 0.144 seconds

‘MAP KEYS TERMINATED BY’ ：key value分隔符

导入数据：

[plain] view plain copy print ?

1. $ cat test7.txt   
2. 1       job:80,team:60,person:70  
3. 2       job:60,team:80  
4. 3       job:90,team:70,person:100  
5. hive>  LOAD DATA LOCAL INPATH '/home/work/data/test7.txt' INTO TABLE employee;  

$ cat test7.txt 
1       job:80,team:60,person:70
2       job:60,team:80
3       job:90,team:70,person:100
hive>  LOAD DATA LOCAL INPATH '/home/work/data/test7.txt' INTO TABLE employee;

查询：

[plain] view plain copy print ?

1. hive> select perf['person'] from employee;  
2. Total MapReduce jobs = 1  
3. ......  
4. Total MapReduce CPU Time Spent: 460 msec  
5. OK  
6. 70  
7. NULL  
8. 100  
9. Time taken: 20.902 seconds  
10. hive> select perf['person'] from employee where perf['person'] is not null;     
11. Total MapReduce jobs = 1  
12. .......  
13. Total MapReduce CPU Time Spent: 610 msec  
14. OK  
15. 70  
16. 100  
17. Time taken: 21.989 seconds  
18. hive>   
19. <SPAN style="FONT-FAMILY: Arial, Helvetica, sans-serif" sizcache="23" sizset="35"><SPAN style="WHITE-SPACE: normal">  
20. </SPAN></SPAN>  

hive> select perf['person'] from employee;
Total MapReduce jobs = 1
......
Total MapReduce CPU Time Spent: 460 msec
OK
70
NULL
100
Time taken: 20.902 seconds
hive> select perf['person'] from employee where perf['person'] is not null;   
Total MapReduce jobs = 1
.......
Total MapReduce CPU Time Spent: 610 msec
OK
70
100
Time taken: 21.989 seconds
hive> 
<span sizcache="23" sizset="35" style="font-family:Arial, Helvetica, sans-serif;"><span style="WHITE-SPACE: normal">
</span></span>