（八）hive复杂数据类型array map struct

1.array

create table hive_array(

id string,

work_locations array<string>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ',';

load data local inpath '/root/hive_array.txt'

overwrite into table hive_array;

1 ["a","b","c","l"]

2 ["e","f","g"]

3 ["u","ip","i"]

4 ["w","g","m"]

5 ["t","g","j"]

6 ["a","u","j"]

 

array_contains函数：判断数组是否包含指定元素

select * from hive_array where array_contains(work_locations,'a');

1 ["a","b","c","l"]

6 ["a","u","j"]

2.map : key-value

father:xiaoming#mother:xiaohuang#brother:xiaoxu

create table hive_map(

id string,

str map<string,string>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY '#'

MAP KEYS TERMINATED BY ':';

load data local inpath '/root/hive_map.txt'

overwrite into table hive_map;

 

map_keys、map_values：返回key和value的数组，可以和array的操作函数配合使用

hive (test_hive)> select * from hive_map;

OK

1 {"a":"aa","b":"bb","c":"cc"}

2 {"d":"aa","e":"ee","f":"ff"}

3 {"a":"hh","s":"ss"}

返回key =a 的值

select id , str['a'] from hive_map;

1 aa

2 NULL

3 hh

返回map搜有的key

select id , map_keys(str) from hive_map;

返回map所有的values

select id , map_values(str) from hive_map;

 

3.struct可以自定义复杂数据类型

create table hive_struct(

id string,

userinfo struct<name:string,age:int>

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ':';

load data local inpath '/root/hive_struct.txt'

overwrite into table hive_struct;

 

hive (test_hive)> select * from hive_struct;

OK

1 {"name":"张三","age":18}

2 {"name":"李四","age":30}

3 {"name":"王五","age":44}

取值

select id,userinfo.name,userinfo.age from hive_struct;

1 张三 18

2 李四 30

3 王五 44