impala,hive行转列

Hive

For Hive, I use collect_set() + concat_ws() from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF.
But if you want to remove duplicated elements, write your own UDF should be the only choice till now.

hive> select uid, concat_ws(',', collect_set(tag)) from test group by uid; FAILED: SemanticException [Error 10016]: Line 1:27 Argument type mismatch 'tag': Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<int>" was found. hive> select uid, concat_ws(',', collect_set(CAST(tag AS STRING))) from test group by uid; ... Job 0: Map: 3 Reduce: 1 Cumulative CPU: 8.43 sec HDFS Read: 890 HDFS Write: 18 SUCCESS Total MapReduce CPU Time Spent: 8 seconds 430 msec OK 1 2,1,3 2 1,4 3 5

1 2 3 4 5 6 7 8 9 10

hive > select uid , concat_ws ( ',' , collect_set ( tag ) ) from test group by uid ; FAILED : SemanticException [ Error 10016 ] : Line 1 : 27 Argument type mismatch 'tag' : Argument 2 of function CONCAT_WS must be "string or array<string>" , but "array<int>" was found . hive > select uid , concat_ws ( ',' , collect_set ( CAST ( tag AS STRING ) ) ) from test group by uid ; . . . Job 0 : Map : 3    Reduce : 1    Cumulative CPU : 8.43 sec   HDFS Read : 890 HDFS Write : 18 SUCCESS Total MapReduce CPU Time Spent : 8 seconds 430 msec OK 1 2 , 1 , 3 2 1 , 4 3 5

Impala

Impala also has a group_concat() but different from mysql

group_concat(string s [, string sep])
Purpose: Returns a single string representing the argument value concatenated together for each row of the result set. If the optional separator string is specified, the separator is added between each pair of concatenated values.
Return type: string

Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.

By default, returns a single string covering the whole result set. To include other columns or values in the result set, or to produce multiple concatenated strings for subsets of rows, include a GROUP BY clause in the query.

group_concat(string s [, string sep]) 和分组函数配合使用，group_concat(字段, 分隔符) ，下面例子：

[hadoop4.xxx.com:21000] > select uid, group_concat(cast(tag as string), ',') as tag_list from test3 group by uid; Query: select uid, group_concat(cast(tag as string), ',') as tag_list from test3 group by uid +-----+----------+ | uid | tag_list | +-----+----------+ | 3 | 5 | | 2 | 1,4 | | 1 | 1,2,3 | +-----+----------+ Returned 3 row(s) in 0.68s

1 2 3 4 5 6 7 8 9 10

[ hadoop4 . xxx . com : 21000 ] > select uid , group_concat ( cast ( tag as string ) , ',' ) as tag_list from test3 group by uid ; Query : select uid , group_concat ( cast ( tag as string ) , ',' ) as tag_list from test3 group by uid + -- -- - + -- -- -- -- -- + | uid | tag_list | + -- -- - + -- -- -- -- -- + | 3    | 5          | | 2    | 1 , 4        | | 1    | 1 , 2 , 3      | + -- -- - + -- -- -- -- -- + Returned 3 row ( s