I want to append rows in a single string
+--------------------+
| defectDescription|
+--------------------+
|ACEView NA : Daework|
|ACEView NA : Documen|
|ACEView NA : ACev |
|ACEView NA : Dragdro|
+--------------------+
Expected Output: ACEView NA : Daework ACEView NA : Documen ACEView NA : ACev ACEView NA : Dragdro
If you indeed want to get all the data into a single string you can do it using collect:
val rows = df.select("defectDescription").collect().map(_.getString(0)).mkString(" ")
You first select the relevant column (so you have just it) and collect it, it would give you an array of rows. the map turns each row to the string (there is just one column - 0). Then mkString would make an overall string of them with a space as the separator.
Note that this would bring the entire dataframe to the driver which might cause memory exceptions. If you need just some of the data you can use take(n) instead of collect to limit the number of rows to n.
collect and, indeed, if data is big enough, driver explodesval str1 = df.select("defectDescription").collect.mkString(",")
val str = str1.replaceAll("[\\[\\]]","")
Another way to do this is as follows:
The 1st line selects the particular columns then collects the subset, collects behaves as: Collect (Action) - Return all the elements of the dataset as an array at the driver program. This is usually useful after a filter or other operation that returns a sufficiently small subset of the data.
mkString - mkString method has an overloaded method which allows you to provide a delimiter to separate each element in the collection.
The 2nd line just replaces the additional brackets
df.createTempView(viewName="table")
val res=spark.sqlContext.sql(sqlText="select defectDescription from table").collectAsList.toString.replace("[", "").replace("]", "")
Initially create a temporary view of the dataframe, then convert into a list, and then string- Finally remove the brackets as per the required output.
Let's leverage parallel computing by not prematurely collecting the data while there is associative processing to be done:
def str(r: Row) = r.getString(0)
def cat(r0: Row, r1: Row) = Row(s"${str(r0)} ${str(r1)}")
str(df.select("defectDescription").reduce(cat))
This allows parallel concatenations to be done on all executors before concatenating their results in the driver.