Apache Spark concatenate multiple rows into list i

问题:

This question already has an answer here:

• SPARK SQL replacement for mysql GROUP_CONCAT aggregate function 6 answers

I need to create a table(hive table/spark dataframe) from a source table that stores data of users in multiple rows into list in single row.

User table:
Schema:  userid: string | transactiondate:string | charges: string |events:array<struct<name:string,value:string>> 
----|------------|-------| ---------------------------------------
123 | 2017-09-01 | 20.00 | [{"name":"chargeperiod","value":"this"}]
123 | 2017-09-01 | 30.00 | [{"name":"chargeperiod","value":"last"}]
123 | 2017-09-01 | 20.00 | [{"name":"chargeperiod","value":"recent"}]
123 | 2017-09-01 | 30.00 | [{"name":"chargeperiod","value":"0"}]
456 | 2017-09-01 | 20.00 | [{"name":"chargeperiod","value":"this"}]
456 | 2017-09-01 | 30.00 | [{"name":"chargeperiod","value":"last"}]
456 | 2017-09-01 | 20.00 | [{"name":"chargeperiod","value":"recent"}]
456 | 2017-09-01 | 30.00 | [{"name":"chargeperiod","value":"0"}]

Output table should be

userid:String | concatenatedlist :List[Row]
-------|-----------------
123    | [[2017-09-01,20.00,[{"name":"chargeperiod","value":"this"}]],[2017-09-01,30.00,[{"name":"chargeperiod","value":"last"}]],[2017-09-01,20.00,[{"name":"chargeperiod","value":"recent"}]], [2017-09-01,30.00, [{"name":"chargeperiod","value":"0"}]]]
456    | [[2017-09-01,20.00,[{"name":"chargeperiod","value":"this"}]],[2017-09-01,30.00,[{"name":"chargeperiod","value":"last"}]],[2017-09-01,20.00,[{"name":"chargeperiod","value":"recent"}]], [2017-09-01,30.00, [{"name":"chargeperiod","value":"0"}]]]

Spark version: 1.6.2

回答1:

val rdd = sc.parallelize(Seq(("1","2017-02-01","20.00","abc"),("1","2017-02-01","30.00","abc2"),("2","2017-02-01","20.00","abc"),("2","2017-02-01","30.00","abc")))
val df = rdd.toDF("id","date","amt","array")
df.withColumn("new",concat_ws(",",$"date",$"amt",$"array")).select("id","new").groupBy("id").agg(concat_ws(",",collect_list("new")))