hive复杂结构之array,map,struct（hive array map）

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	100

split()将字符串转成字符串数组

语法: 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