魏长东

weichangdong

东邪

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'

数据源

[root@swordman ~]# cat test5.txt
1,zhou:30  
2,yan:30  
3,chen:20  
4,li:80 
[root@swordman ~]# cat test6.txt
034,1:2:3:4
035,5:6
036,7:8:9:10
[root@swordman ~]# cat test7.txt
1	job:80,team:60,person:70
2	job:60,team:80
3	job:90,team:70,person:100



Struct使用

建表:

create table student_test(id INT, info struct<name:STRING, age:INT>) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY ':'; 
'FIELDS TERMINATED BY' :字段与字段之间的分隔符

'COLLECTION ITEMS TERMINATED BY' :一个字段各个item的分隔符 

导入数据:
LOAD DATA LOCAL INPATH '/root/test5.txt' INTO TABLE student_test; 
查询:

hive> select  *  from  student_test;
OK
1	{"name":"zhou","age":null}
2	{"name":"yan","age":null}
3	{"name":"chen","age":null}
4	{"name":"li","age":null}
Time taken: 0.046 seconds, Fetched: 4 row(s)
Array使用

建表:

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

LOAD DATA LOCAL INPATH '/root/test6.txt' INTO TABLE class_test ; 

查询:
hive> select  *  from  class_test;   
OK
034	[1,2,3,4]
035	[5,6]
036	[7,8,9,10]
Time taken: 0.154 seconds, Fetched: 3 row(s)
Map使用

建表:
create table employee(id string, perf map<string, int>) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':';
LOAD DATA LOCAL INPATH '/root/test7.txt' INTO TABLE employee;
查询:
hive> select  *  from  employee;
OK
1	{"job":80,"team":60,"person":70}
2	{"job":60,"team":80}
3	{"job":90,"team":70,"person":100}
Time taken: 0.065 seconds, Fetched: 3 row(s)