CODEWITHSUNDEEP
sqlscalaapache-spark
MarkTeehan
MarkTeehan

Reputation: 313

Spark scala: SELECT in a foreach loop returns java.lang.NullPointerException

I need to iterate over the contents of a DF with various SELECT statements inside a foreach loop, writing output to text files. Any SELECT statement inside the foreach loop returns a NullPointerException. I am unable to see why this is. A SELECT statement inside a "for" loop does not return this error.

This is the test case.

// step 1 of 6: create the table and load two rows
vc.sql(s"""CREATE TEMPORARY TABLE TEST1 (
 c1       varchar(4)
,username varchar(5)
,numeric integer) USING com.databricks.spark.csv OPTIONS (path "/tmp/test.txt")""")

// step 2 of 6: confirm that the data is queryable
vc.sql("SELECT * FROM TEST1").show()
+----+--------+-------+
|  c1|username|numeric|
+----+--------+-------+
|col1|   USER1|      0|
|col1|   USER2|      1|
+----+--------+-------+

// Step 3 of 6: create a dataframe for the table
var df=vc.sql("""SELECT * FROM TEST1""")


// step 4 of 6: create a second dataframe that we will use as a loop iterator
var df_usernames=vc.sql(s"""SELECT DISTINCT username FROM TEST1 """)

// step 5 of 6: first foreach loop works ok:
df_usernames.foreach(t => 
    {
      println("(The First foreach works ok: loop iterator t is " + t(0).toString() )
    }
)
(The First foreach works ok: loop iterator t is USER1
(The First foreach works ok: loop iterator t is USER2

// step 6 of 6: second foreach with any embedded SQL returns an error
df_usernames.foreach(t => 
    {
      println("(The second foreach dies: loop iterator t is " +     t(0).toString() )
      vc.sql("""SELECT c1 FROM TEST1""").show()
    }
)    
The second foreach dies: loop iterator t is USER1
org.apache.spark.SparkException: Job aborted due to stage failure: Task 158     in stage 94.0 failed 1 times, most recent failure: Lost task 158.0 in stage 94.0 (TID 3525, localhost): java.lang.NullPointerException
    at org.apache.spark.sql.SQLContext.parseSql(SQLContext.scala:195)

Upvotes: 4

Views: 3893

Answers (1)

user6022341
user6022341

Reputation:

It can't be done. You cannot start SQL query inside foreach without calling collect first 

>>> df_usernames.collect.foreach(
... lambda x: sqlContext.sql("""SELECT c1 FROM TEST1""").show())

Upvotes: 6

Related Questions