Adding elements to ArrayBuffer in from a for loop in scala

Question

import scala.collection.mutable.ArrayBuffer

spark.sql("set db=test_script")
spark.sql("set table=member_test")

val colDF = sql("show columns from ${table} from ${db}")
var tempArray = new ArrayBuffer[String]()
var temp
colDF.foreach { row => row.toSeq.foreach { col => 
 temp = "count(case when "+ col+ " ='X' then 1 else NULL END) AS count"+ col
 tempArray += temp
}}

println(tempArray) // getting empty array
println(temp) // getting blank string

Hi, I am new to scala programming. I am trying to loop through a dataframe and append the formatted String data to my ArrayBuffer. When I put the print statement inside the for loop, everything, seems to be fine, whereas If i try to access the arrayBuffer outside the loop, its empty. Is it something related to the scope of the variable? I am using arrayBuffer, because I got to know that list is mutable in Scala. Please suggest any better way if you have. Thanks in advance

Nick · Accepted Answer · 2020-09-15 19:09:09Z

1

The issue you are having is that spark is a distributed system, which means copies of your buffer are sent to each executor (And not returned back to the driver), hence why it is empty.

Also note that colDF is a DataFrame. This means that when you do

row => row.toSeq

The result of this is an Array(Any) (this isn't good practice). A better way of doing this would be:

val dataFrame: DataFrame = spark.sql("select * from test_script.member_test")
val columns: Array[String] = dataFrame.columns
val sqlStatement = columns.map(c => s"count(case when $c = 'X' then 1 else NULL END) as count$c")

However, even better is not to use SQL at all and use Spark!

val dataFrame: DataFrame = spark.sql("select * from test_script.member_test")
val columns: Array[String] = dataFrame.columns
val selectStatement: List[Column] = columns.map{ c =>
    count(when(col(c) === "X", lit(1)).as(s"count$c")
}.toList
dataFrame.select(selectStatement :_*)

answered Sep 15, 2020 at 19:09

Nick

6898 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anurag Kumar Pandey Over a year ago

Thanks so much, it works. Everything in this code is clear except the last line selectStatement :_* .

Nick Over a year ago

The method signature of select is: def select(cols: Column*) . It expects you to use it like this: dataFrame.select(col("a"),col("b"),col("c")...). ": _*" is special syntax to unpack a list so that it is interpreted as if you had just put them in comma separated into the method (without "List")).

Collectives™ on Stack Overflow

Adding elements to ArrayBuffer in from a for loop in scala

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related