hive列转行 （collect_set()）

在Hive的是用中，我们经常会有这种需求：

按照同一个id进行Ｇroup By，然后对另一个字段去重，例如下面得数据：

id pic

1 1.jpg

2 2.jpg

1 1.jpg

1 2 3 4

id pic 1 1.jpg 2 2.jpg 1 1.jpg

此时，是用DISTINCT或者2 col得Group By都是不行得，我们可以用这个UDAF：collect_set(col)，它将对同一个group by 得key进行set去重后，转换为一个array。

再举一个例子，我们可以对pic进行去重，拼接：

SELECT id, CONCAT_WS(',', COLLECT_SET(pic)) FROM tbl GROUP BY id

1	SELECT id , CONCAT_WS ( ',' , COLLECT_SET ( pic ) ) FROM tbl GROUP BY id

在这里CONCAT_WS是UDF，COLLECT_SET是UDAF，它将group后的pic去重，并转换为了array，方便udf是用。

PS：如果不需要去重，可以使用COLLECT_LIST。

练习题：

hive如何将

a       b       1

a       b       2

a       b       3

c       d       4

c       d       5

c       d       6

变为：

a       b       1,2,3

c       d       4,5,6

即为在col1分组下col3数据列变行

二、数据

test.txt

a       b       1

a       b       2

a       b       3

c       d       4

c       d       5

c       d       6

三、答案

1.建表

drop table tmp_jiangzl_test;

create table tmp_jiangzl_test

(

col1 string,

col2 string,

col3 string

)

row format delimited fields terminated by '\t'

stored as textfile;

load data local inpath '/home/jiangzl/shell/test.txt' into table tmp_jiangzl_test;

2.处理

select col1,col2,concat_ws(',',collect_set(col3))

from tmp_jiangzl_test  

group by col1,col2;