Extract data from mysql using Scala and spark

Question

I tried to run a Scala program to extract the data from mysql retail_db database. It throws SQLException.

This is my code:

import java.sql.DriverManager
import java.sql.Connection

case class Categories(id: Int, department: String, name: String){
  override def toString: String = {"id: " + id + "department: " + department + "name: " + name}
}
object Orders {
  def main(args: Array[String]): Unit ={
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://quickstart.cloudera:3306/retail_db"
    val username = "root"
    val password = "cloudera"
    Class.forName(driver)
    val connection = DriverManager.getConnection(url, username, password)
    val statement = connection.createStatement()
    val resultSet = statement.executeQuery(s"SELECT * FROM categories")

    while (resultSet.next()){
      val e = Categories(resultSet.getInt("id"),
        resultSet.getString("department"),
        resultSet.getString("name"))
         println(e)
    }
  }
}

Spark submit command:

spark-submit --class "Orders" \
--master local < path >/scala_2.10-0.1-SNAPSHOT.jar

Exception:

Exception in thread "main" java.sql.SQLException: Column 'id' not found."**

I don't see anything wrong with code. I think id column is not part of categories. — Rahul Sharma
– Rahul Sharma, Commented Nov 20, 2017 at 17:57

Michail N · Accepted Answer · 2017-11-20 17:49:25Z

1

It is very simple you don't have a column named id in categories table (or it is not a Int). I suggest to run

val resultSet = statement.executeQuery("SHOW COLUMNS FROM categories")

first which will return a description of your table and then

   while (resultSet.next()){
           println(resultSet.getString("Field"))
           println(resultSet.getString("Type"))
    }

With this way you would see the actual field name and type. Of course this assumes you don't have access to the host of MySQL or else you should simply connect there manually and see the table structure.

EDIT: The JDBC URL looked familiar, it is from cloudera's quickstart VM. Anyway the table's schema is here so as you can see it is not id but category_id.

answered Nov 20, 2017 at 17:49

Michail N

3,8854 gold badges36 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Extract data from mysql using Scala and spark

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