hive提供了复合数据类型array,map,struct,下面来依次介绍
一.array介绍
语法：array<int> 例如：[1,2,3]
array<string> 例如 ['a','b','c']
描述：数组是一组具有相同类型和名称的变量的集合。这些变量成为数组的元素，每个数组元素都有一个编号，编号从0开始。例如，数组['Jack','David'],那么第二个元素可以用数组名[1]进行引用。
创建数组
create table array_test
(
name string
,dept_id array<int>
,status array<string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
;ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 设置字段间的分隔符为tab
COLLECTION ITEMS TERMINATED BY ',' 设置字段中每个元素间的分隔符为逗号，数组中必须使用该设置
准备数据：array_test.txt
zs 1,2,3,4 ok,return,reject,fail
ls 11,22,33,41 ok,reject,fail
wl 100加载数据
LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/array_test.txt' OVERWRITE INTO TABLE array_test;查询数据
hive (test)> select * from array_test;
array_test.name array_test.dept_id array_test.status
zs [1,2,3,4] ["ok","return","reject","fail"]
ls [11,22,33,41] ["ok","reject","fail"]
wl [100] NULL查询数组字段的长度：size(array)
hive (test)> select name,size(dept_id),size(status) from array_test;
name _c1 _c2
zs 4 4
ls 4 3
wl 1 -1注意：由上面的结果可看出当数组为null时，查出的长度为-1
查询数组字段中某一位置的元素：array[index]
hive (test)> select name,dept_id[0],status[2] from array_test;
name _c1 _c2
zs 1 reject
ls 11 fail
wl 100 NULL查询数组中是否包含某元素：array_contains(数组名，值)
array_contains方法返回一个Boolean对象，可以配合if函数使用，例如 if(array_contains(cloumn,'A'),'包含A','不包含A')
hive (test)> select name,array_contains(status,'ok') from array_test;
name _c1
zs true
ls true
wl false
hive (test)> select name,if(array_contains(status,'ok'),'yes','no') from array_test;
name _c1
zs yes
ls yes
wl no查询status中包含'ok'的数据
hive (test)> select * from array_test where array_contains(status,'ok');
array_test.name array_test.dept_id array_test.status
zs [1,2,3,4] ["ok","return","reject","fail"]
ls [11,22,33,41] ["ok","reject","fail"]此处注意，如果不使用该函数，而直接使用 “值 in/not in 数组”，会报错，会报类型不匹配的错误
hive (test)> select * from array_test where status in ('ok');
FAILED: SemanticException Line 0:-1 Wrong arguments ''ok'': The arguments for IN should be the same type! Types are: {array<string> IN (string)}查询数组中的每一个元素：explode()
注意：explode()函数只是生成了一个数据的展示方式，无法在表中产生一个新的数据列，即select name,explode(dept_id) from array_test; 会报错的
col
1
2
3
4
11
22
33
41
100
hive (test)> select name,explode(dept_id) from array_test;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions要想展示其他列，可以使用下面的lateral view explode(array)来实现
一行转多行（行转列）：lateral view explode(array)
lateral view 会将explode生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行即每个name进行join,来达到数据聚合的目的。
hive (test)> select name ,a_dept_id from array_test
> lateral view explode(dept_id) a as a_dept_id -- 要进行聚合的虚拟表，lateral view explode(字段) 虚拟表名 as 虚拟表字段
> ;
name a_dept_id
zs 1
zs 2
zs 3
zs 4
ls 11
ls 22
ls 33
ls 41
wl 100split()将字符串转成字符串数组
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
hive (test)> select * from split_test;
OK
split_test.id split_test.info
1 a,b,c
2 d,e,f
3
4 NULL将split_test表中的info字段转成数组
hive (test)> select id,split(info,',') as array_info, size(split(info,',')) as array_info_size from split_test;
id array_info array_info_size
1 ["a","b","c"] 3
2 ["d","e","f"] 3
3 [""] 1
4 NULL -1二.map介绍
语法：
map(k1,v1,k2,v2,...)
描述：
map是一组键-值对元组集合,map类型的数据可以通过'列名['key']的方式访问
创建map
create table map_test
(
id int
,course map<string,int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
;MAP KEYS TERMINATED BY ：key value分隔符
准备数据: map_test.txt
1 chinese:80,math:90,english:100
2 chinese:95,math:70
3 加载数据
LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/map_test.txt' OVERWRITE INTO TABLE map_test;
查询数据
hive (test)> select * from map_test;
map_test.id map_test.course
1 {"chinese":80,"math":90,"english":100}
2 {"chinese":95,"math":70}
3 {}查询map的长度：size(map)
hive (test)> select id,size(course) from map_test;
id _c1
1 3
2 2
3 0查询map字段中的某个元素：map['parame']
hive (test)> select id,course['math'] as math from map_test;
OK
id math
1 90
2 70
3 NULL查询map中所有的key， 返回值类型为 array：map_keys(map)
hive (test)> select id,map_keys(course) as key from map_test;
id key
1 ["chinese","math","english"]
2 ["chinese","math"]
3 []查询map中所有的value，返回值类型为array：map_values(map)
hive (test)> select id,map_values(course) as value from map_test;
id value
1 [80,90,100]
2 [95,70]
3 []判断map中是否包含某个key值：array_contains(map_keys(map))
hive (test)> select * from map_test where array_contains(map_keys(course),'math');
map_test.id map_test.course
1 {"chinese":80,"math":90,"english":100}
2 {"chinese":95,"math":70}一行转多行（行转列）：lateral view explode(map)
hive (test)> select id,subject,score from map_test
> lateral view explode(course) a as subject,score;
id subject score
1 chinese 80
1 math 90
1 english 100
2 chinese 95
2 math 70三.struct介绍
语法：
struct(val1, val2, val3, ...) ,struct内部的元素类型可以为array,map,struct复杂结构
描述：
和C语言中的struct或者对象类似，都可以通过"点"符号访问元素内容。例如，如果某个列的数据类型为struct{first string,last string},那么第一个元素可以通过字段名.first来引用
创建struct表
create table struct_test
(
id int
,info struct<name:string,age:int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
;准备数据：
LOAD DATA LOCAL INPATH '/Users/admin/Documents/bigdata/test_file/struct_test.txt' OVERWRITE INTO TABLE struct_test;查询数据：
hive (test)> select * from struct_test;;
struct_test.id struct_test.info
1 {"name":"zs","age":18}
2 {"name":"ls","age":22}
3 {"name":"ww","age":30}struct 无size()函数
hive (test)> select id,size(info) from struct_test;
FAILED: SemanticException [Error 10016]: Line 1:15 Argument type mismatch 'info': "map" or "list" is expected at function SIZE, but "struct<name:string,age:int>" is found查询struct中的元素：struct.元素名称
hive (test)> select id,info.name,info.age from struct_test;
id name age
1 zs 18
2 ls 22
3 ww 30