Apache Spark SQL get values in dataframe from SQL query

Question

I'm trying to get a string value from a SQL query with Apache Spark 2.2.0 as follows:

val result = spark.sql("SELECT AnswerText FROM datatable WHERE participantUUID='010A0550' AND assessmentNumber=0 AND Q_id_string = '1_Age'")

assertResult("23") {
  result.collect.head.getString(0)
}

I get the following exception:

next on empty iterator
java.util.NoSuchElementException: next on empty iterator

I've tried collectAsList to return a row but not getting any joy from that, either. I simply want to return the actual value from the query in the DataFrame, not the column, row or field. In this case, the result is a string but it could also be an int - the age of the person = 23.

Alper t. Turker · Accepted Answer · 2017-12-09 14:47:28Z

1

This happens probably because query doesn't return any items. It would be better to use headOption

assertResult(Some("23")) {
  result.take(1).headOption.map(_.getAs[String]("AnswerText"))
}

or push it to SQL:

assertResult(1) {
  spark
    .sql("""SELECT AnswerText 
            FROM datatable 
            WHERE participantUUID='010A0550' AND 
                  assessmentNumber=0 AND
                  Q_id_string = '1_Age'""")
   .where($"AnswerText" === "23").count 
}

answered Dec 9, 2017 at 14:47

Alper t. Turker

35.3k9 gold badges89 silver badges118 bronze badges

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

Collectives™ on Stack Overflow

Apache Spark SQL get values in dataframe from SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related