CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Kepler
Kepler

Reputation: 429

passing Dataframe contents into sql stored procedure

I am trying to pass the contents in the dataframe into my sql stored procedure. I use a map function to iterate it through the dataframe contents and send them into the db. I have an error when trying to do it.

I am getting an error called No Encoder found for Any - field (class: "java.lang.Object", name: "_1") - root class: "scala.Tuple2"

Could anybody help me to correct this.

Below is my code 

 val savedDataFrame = dataFrame.map(m => sendDataFrameToDB(m.get(0), m.get(1), m.get(2), m.get(3)))
   savedDataFrame.collect()

 def sendDataFrameToDB(firstName : String, lastName : String, address : String, age : Long) = {
var jdbcConnection: java.sql.Connection = null

try {
  val jdbcTemplate = new JDBCTemplate()
  jdbcTemplate.getConfiguration()
  jdbcConnection = jdbcTemplate.getConnection

  if (jdbcConnection != null) {
    val statement = "{call insert_user_details (?,?,?,?)}"

    val callableStatement = jdbcConnection.prepareCall(statement)

    callableStatement.setString(1, firstName)
    callableStatement.setString(2, lastName)
    callableStatement.setString(3, address)

    callableStatement.setLong(4, age)

    callableStatement.executeUpdate
  }
} catch {
  case e: SQLException => logger.error(e.getMessage)
} 
}

Upvotes: 1

Views: 1462

Answers (1)

Dilan
Dilan

Reputation: 1449

passing Dataframe contents into sql stored procedure

dataFrame.map(m => sendDataFrameRDBMS(f.getAs("firstname").toString, f.getAs("lastname").toString, f.getAs("address").toString, f.getAs("age").toString.toLong))

m.get(0) belongs to the type of Any and it cannot be passed to String typed firstName directly according to your example. Datframe is different from RDD. "DataFrame is a Dataset organized into named columns. It is conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations under the hood" link

When u make the Dataframe makes the columns such as 

val dataFrame = dataSet.toDF("firstname", "lastName", "address", "age")

Then you can access elements in the dataframe as below and pass into whatever your method

dataFrame.map(m => sendDataFrameRDBMS(f.getAs("firstname").toString, f.getAs("lastname").toString, f.getAs("address").toString, f.getAs("age").toString.toLong))

Upvotes: 1

Related Questions