HIVE中关于collect_set与explode函数

hive中的列支持使用三类复杂的集合数据类型，即：array，map及struct，这些类型的名称是保留字，具体用法可参见该篇博文，里面有关于三类基本集合数据类型的操作实例，注：map中可嵌套array类型。 

例如，定义表：

create table example (
    device_id string,
    login_ip array<string>,
    user_info map<string,array<string>>
    address structstring,city:string,state:string>
)
row format delimited
fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003' lines terminated by '\n' stored as RCFile;

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

在hive环境下运行，即可创建该管理表（区分外部表），如果有必要，可以直接使用load data加载数据。

collect_set函数（行转列）

collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。例如，如下数据记录，要统计每种no下的score，这里就可以配合group by 产生奇效。

no  score 1 2 1 3 1 3 2 2 2 4 2 4 ...... 

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

直接对no分组后对score进行collect_set操作，如下：

select no，collect_set(score) from tablss group by no;

这样，就实现了将列转行的功效，但是注意只限同列基本数据类型，函数只能接受一列参数。

有以下表

id	name
1001	A
1001	B
1001	C

实现以下功能

id	name
1001	A,B,C

即按照id 进行group by，将每个id的name组成一个list放到name字段中。

select id,collect_list(name) from table group by id 
					

						
						

							1
						

					

若name中有重复的值，可以用collect_set函数进行去重

collect_list函数返回的类型是array< ？ >类型，？表示该列的类型 
怎么转为string类型？

我们可以使用concat_ws函数，但是该函数仅支持string和array< string > 所以对于该列不是string的列，先转为string

select id,concat_ws(',',collect_list(cast (name as string))) from table group by id 
				

					
					

						1
					

				

最终实现了我们需要的输出结果

concat_ws函数的意思是用逗号将所有的string组合到一起，用逗号分割显示

select concat_ws(',',"A","B"); 
				

					
					

						1
					

				

上面代码的结果就是 
A,B

explode函数

explode(array)函数接受array类型的参数，其作用恰好与collect_set相反，实现将array类型数据行转列，例如，上述记录列转行后的形式如下：

no score_set 1 [2,3] 2 [2,4] 
					

						
						

							1
						

						

							2
						

						

							3
						

					

假设这样的数据类型以分区表存储，你要统计一段时间类no=1下的去重score，那么该怎么办了？这里可配合使用lateral view首先实现列转行的功能，如下所示：

select no,score from tablaa lateral view explode(score_set) xxx as score;

注：xxx代表虚表名称，不能缺少。

进一步深化上述代码解决统计一段时间的去重值，可写为：

select no，collect_set(score) from tablaa lateral view explode(score_set) xxx as score group by no;

这样，将两个函数结合实现了行转列或列转行的妙用。

列转行可用到explode

hive> select explode(split(concat_ws(',','1','2','3','4'),',')) from default.dual; 1
2
3
4
				

					
					

						1
					

					

						2
					

					

						3
					

					

						4
					

					

						5
					

				

表示1,2,3,4拼接起来，然后再用逗号分隔成列，应用到表中如下面所示

hive> select * from tt  s  lateral view explode(split("a b c d"," "))  t3 as sp; ------需要起别名，tt表中只有一个字段有数据。 A  a
A  b
A  c
A  d