CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Amit Porwal
Amit Porwal

Reputation: 43

Spark SQL for dividing count from two different queries and store the output as Double

I am working on Spark SQL using Scala. I have a requirement where I need first convert the o/p of each query to double and then divide them.This is what I tried.

Query1 - 

scala> var noofentry = sqlContext.sql("select count(*) from bankdata")
noofentry: org.apache.spark.sql.DataFrame = [count(1): bigint]

Query2

var noofsubscribed = sqlContext.sql("select count(*) from bankdata where y='yes'")
noofsubscribed: org.apache.spark.sql.DataFrame = [count(1): bigint]

Now, I need to convert the output of each query to double and divide them.

scala > var result = noofsubscribed.head().getDouble(0) / noofentry.head().getDouble(0)

On doing this I've ended up with the following error.

 java.lang.ClassCastException: java.lang.Long cannot be cast to 
 java.lang.Double
 at scala.runtime.BoxesRunTime.unboxToDouble(BoxesRunTime.java:114)
 at org.apache.spark.sql.Row$class.getDouble(Row.scala:248)
 at org.apache.spark.sql.catalyst.expressions.GenericRow.getDouble(rows.scala:165)... 50 elided

Upvotes: 0

Views: 1085

Answers (2)

eliasah
eliasah

Reputation: 40370

As much as I agree with @DNA's answer, I'd like to attack this question in another way maybe considering best practice.

Doing the following is just meaningless : 

scala> var noofentry = sqlContext.sql("select count(*) from bankdata")

You ought doing the following instead :

scala> val noofentry = sqlContext.sql("select * from bankdata").count

Same thing for the other query :

scala> val noofsubscribed = sqlContext.sql("select * from bankdata where y='yes'").count

now you'll just need to convert one of them :

scala > val result = noofsubscribed.toDouble / noofentry

So this is actually a code review and an answer at the same time.

Upvotes: 2

DNA
DNA

Reputation: 42597

You are getting a ClassCastException because the value in your Row is a Long, but you are calling getDouble(0) which expects a Double, as shown in the exception message:

java.lang.Long cannot be cast to java.lang.Double

You need to call getLong(0) first, then apply toDouble to convert the Long to Double. For example:

noofsubscribed.head().getLong(0).toDouble

Upvotes: 1

Related Questions