概述

MAP：MAP包含key->value键值对，可以通过key来访问元素。比如”userlist”是一个map类型，其中username是key，password是value；那么我们可以通过userlist['username']来得到这个用户对应的password；


操作实例

1、创建表

hive> create table map_test(id string,perf map<string,int>)
 
> row format delimited fields terminated by "\t"
 
> collection items terminated by ','
 
> map keys terminated by ':'
 
> ;

与struct的区别：

hive> create table map_test2(id string,perf map<string,int>)
 
 
> row format delimited fields terminated by "\t"
 
 
> collection items terminated by ','
 
 
> map keys terminated by ':'
 
 
> ;

再建一张map_test2，测试map字段名不一致会如何

hive> create table map_test2(id string,perf map<string,int>)
 
 
> row format delimited fields terminated by "\t"
 
 
> collection items terminated by ','
 
 
> map keys terminated by ':'
 
 
> ;

2、准备文件

---------字段名一致-------
[root@hello110 data]# vi  map_test
1001    job:80,team:123,person:700
 1002    job:90,team:234,person:800
 1003    job:70,team:345,person:900
 1004    job:60,team:456,person:1000
 1005    job:59,team:678,person:844
 1006    job:98,team:832,person:866
---------字段名不一致------
[root@hello110 data]# vi  map_test2
 1001    job_1:80,team:123,person:700
 1002    job_2:90,team:234,person:800
 1003    job:70,team:345,person:900
 1004    job:60,team:456,person:1000
 1005    job:59,team:678,person:844
 1006    job:98,team:832,person:866

3、文件导入表

load data local inpath "/data/map_test" into table map_test; 

 

  load data local inpath "/data/map_test2" into table map_test2;

4、查看表

------map_test表-----

hive> select * from map_test;
 
OK
 
1001 {"job":80,"team":123,"person":700}
 
1002 {"job":90,"team":234,"person":800}
 
1003 {"job":70,"team":345,"person":900}
 
1004 {"job":60,"team":456,"person":1000}
 
1005 {"job":59,"team":678,"person":844}
 
1006 {"job":98,"team":832,"person":866}
 
Time taken: 2.264 seconds, Fetched: 6 row(s)
 
---------用[''] 的方式获取-----------
 
hive> select perf['job'],perf['team'],perf['person'] from map_test;
 
OK
 
80 123 700
 
90 234 800
 
70 345 900
 
60 456 1000
 
59 678 844
 
98 832 866
 
Time taken: 4.377 seconds, Fetched: 6 row(s)
 
hive> select perf['job'],perf['team'],perf['person123'] from map_test;
 
OK
 
80 123 NULL
 
90 234 NULL
 
70 345 NULL
 
60 456 NULL
 
59 678 NULL
 
98 832 NULL
 
Time taken: 4.212 seconds, Fetched: 6 row(s)
 
hive> select perf from map_test;
 
OK
 
{"job":80,"team":123,"person":700}
 
{"job":90,"team":234,"person":800}
 
{"job":70,"team":345,"person":900}
 
{"job":60,"team":456,"person":1000}
 
{"job":59,"team":678,"person":844}
 
{"job":98,"team":832,"person":866}
 
Time taken: 4.118 seconds, Fetched: 6 row(s)
 
hive>

----------map_test2-------

hive (default)> select * from map_test2;
 OK
 map_test2.id    map_test2.perf
 1001    job_1:80,team:123,person:700    NULL
 1002    job_2:90,team:234,person:800    NULL
 1003    job:70,team:345,person:900      NULL
 1004    job:60,team:456,person:1000     NULL
 1005    job:59,team:678,person:844      NULL
 1006    job:98,team:832,person:866      NULL
 Time taken: 0.086 seconds, Fetched: 6 row(s)----------------
 hive (default)> select perf['job_1'] from map_test2;
 OK
 c0
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 Time taken: 0.284 seconds, Fetched: 6 row(s)-----------------------
 hive (default)> select perf['job'] from map_test2;
 OK
 c0
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 Time taken: 0.084 seconds, Fetched: 6 row(s)-------------------------------
 hive (default)> select perf['job_2'] from map_test2;
 OK
 c0
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 Time taken: 0.074 seconds, Fetched: 6 row(s)------------------------------
 hive (default)> select perf from map_test2;
 OK
 perf
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 Time taken: 0.076 seconds, Fetched: 6 row(s) -----------------原来：如果map字段名不一致，会被当成id字段来处理了---------
 hive (default)> select id from map_test2;
 OK
 id
 1001    job_1:80,team:123,person:700
 1002    job_2:90,team:234,person:800
 1003    job:70,team:345,person:900
 1004    job:60,team:456,person:1000
 1005    job:59,team:678,person:844
 1006    job:98,team:832,person:866
 Time taken: 0.064 seconds, Fetched: 6 row(s)


5、hadoop中的文件内容

hive创建map类型 hive的map类型_hive