spark scala - Merge mutiple rows into one

Question

I have a dataframe

|--id:string (nullable = true)
|--ddd:struct (nullable = true)
  |-- aaa: string (nullable = true)
  |-- bbb: long(nullable = true)
  |-- ccc: string (nullable = true)
  |-- eee: long(nullable = true)

I am having output like this

 id     |  ddd
--------------------------
   1    | [hi,1,this,2]
   2    | [hello,6,good,3]
   1    | [hru,2,where,7]
   3    | [in,4,you,1]
   2    | [how,4,to,3]

I want the expected o/p as:

   id   |  ddd
  --------------------
   1    | [hi,1,this,2],[hru,2,where,7]
   2    | [hello,6,good,3],[how,4,to,3]
   3    | [in,4,you,1]

Please help

Is that even possible ? Because you are just changing the structure of your column *dddd*, it becomes an array of struct with the aggregation — tricky, Sep 01 '17 at 11:56

Ramesh Maharjan · Answer 1 · 2017-09-01T12:22:01.333

8

you can collect_list as following

import org.apache.spark.sql.functions._
df.groupBy("id").agg(collect_list("ddd").as("ddd"))

collect_set works as well

df.groupBy("id").agg(collect_set("ddd").as("ddd"))

edited Sep 01 '17 at 12:22

answered Sep 01 '17 at 12:06

Ramesh Maharjan

41,071
6
69
97

collect_list is not working . – gayathri Sep 01 '17 at 12:14
whats the error . its working for me though – Ramesh Maharjan Sep 01 '17 at 12:15
it says undefined function collect_list – gayathri Sep 01 '17 at 12:31
i am using spark 1.6 – gayathri Sep 01 '17 at 12:31
Even with these two lines : import org.apache.spark.sql.functions._ and import org.apache.spark.sql.expressions._ ? – tricky Sep 01 '17 at 12:32
1

Ah yeah you're right, import hive context maybe ? – tricky Sep 01 '17 at 12:33
https://stackoverflow.com/questions/35528966/use-collect-list-and-collect-set-in-spark-sql – tricky Sep 01 '17 at 12:33
i didnt import org.apache.spark.sql.expressions._ . When i type this in intellij, its disappearing – gayathri Sep 01 '17 at 12:38

spark scala - Merge mutiple rows into one

1 Answers1