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;