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;