集合类型元素操作

在hive中的数据类型

int  bigint  double  string  timestamp

struct 类似Java的对象  pojo类

array  数组

map  集合,键值对

姓名          朋友                  孩子                              地址

benben,fengjie_furong,xiaoben:18_daben:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

像这样的数据,在创建表格时就应该切割好了,之前都是使用  row format  delimited fields terminated by  ',' 

但这次,单单使用这个显然不行,不然数据存进去,取第一个朋友时,就没法取,这两个值没有切割开

list,array元素的切割:collection items terminated by  '_' 

map集合的切割:map  keys terminated by  ':'

使用这个数据,写一遍

create  table  tb_user(
name  string ,
friends  array<string> ,
children map<string ,int> ,
address struct<street:string , city:string>
)
row format  delimited fields terminated by  ',' 
collection items terminated by  '_' 
map  keys terminated by  ':'
lines terminated by  '\n';
load  data local  inpath "/home/collection/" into  table tb_user ;

存进去的显示结果

+---------------+-----------------------+--------------------------------------+----------------------------------------------+
| tb_user.name  |    tb_user.friends    |           tb_user.children           |               tb_user.address                |
+---------------+-----------------------+--------------------------------------+----------------------------------------------+
| benben        | ["fengjie","furong"]  | {"xiaoben":18,"daben":19}            | {"street":"hui long guan","city":"beijing"}  |
| yangyang      | ["caicai","susu"]     | {"xiao yang":18,"xiaoxiao yang":19}  | {"street":"chao yang","city":"beijing"}      |
+---------------+-----------------------+--------------------------------------+----------------------------------------------+

数组取值

select friends[if(1>xize(friends)),0,1] from tb_user;
为了防止索引越界,可以这样写

有足够的自信就不一样了
select frirends[0] from tb_user;
给其索引就行

map集合

根据key找value

select children['xiaoben'] from tb_user;

根据value找key

select children['18'] from tb_user;tb_user;

获取map集合中所有的keys

select map_keys(children) from tb_user;

获取map集合中所有的values

select map_values(children) from tb_user;