Apache Spark concatenate multiple rows into list in single row

Question

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

Which language are you using? Scala? – Shaido Sep 29 '17 at 06:17 — Shaido, Sep 29 '17 at 06:17
@moe That is not solution to this question – satish Sep 29 '17 at 21:35 — satish, Sep 29 '17 at 21:35

score 8 · Answer 1 · edited Mar 12 '20 at 08:23

8

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"))
.toDF("id", "date", "amt", "array")

df.withColumn("new", concat_ws(",", $"date", $"amt", $"array"))
  .select("id", "new")
  .groupBy("id")
  .agg(concat_ws(",", collect_list("new")))

edited Mar 12 '20 at 08:23

Insung Park

401
3
15

answered Sep 29 '17 at 09:02

hd16

262
2
4

Thanks @hd16. concat_ws is working for Array[String] but not for array> – satish Sep 29 '17 at 16:21

Apache Spark concatenate multiple rows into list in single row

1 Answers1

Linked