hive将list转成string_HIVE中关于collect_set与explode函数

例如，定义表：

create table example (

device_id string,

login_ip array,

user_info map>

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 id1

若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 id1

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

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

41

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