You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by srinivas <ku...@gmail.com> on 2014/07/31 00:55:26 UTC
Data from Mysql using JdbcRDD
Hi,
I am trying to get data from mysql using JdbcRDD using code The table have
three columns
val url = "jdbc:mysql://localhost:3306/studentdata"
val username = "root"
val password = "root"
val mysqlrdd = new org.apache.spark.rdd.JdbcRDD(sc,() => {
Class.forName("com.mysql.jdbc.Driver")
DriverManager.getConnection(url, username, password)
},"SELECT * FROM student_info",
1, 20, 2, r => r.getString("studentname"))
mysqlrdd.saveAsTextFile("/home/ubuntu/mysqljdbc")
I am getting runtime error as
14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648 to
force MySQL streaming
14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648 to
force MySQL streaming
14/07/30 22:05:04 INFO JdbcRDD: closed connection
14/07/30 22:05:04 INFO JdbcRDD: closed connection
14/07/30 22:05:04 ERROR Executor: Exception in task ID 0
java.sql.SQLException: Parameter index out of range (1 > number of
parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
Can anyone help. And let me know if i am missing anything.
Thanks,
-Srini.
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
RE: Data from Mysql using JdbcRDD
Posted by chutium <te...@gmail.com>.
have a look on this commit:
https://github.com/apache/spark/pull/1612/files#diff-0
try this:
- stmt.setLong(1, part.lower)
- stmt.setLong(2, part.upper)
+ val parameterCount = stmt.getParameterMetaData.getParameterCount
+ if (parameterCount > 0) stmt.setLong(1, part.lower)
+ if (parameterCount > 1) stmt.setLong(2, part.upper)
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994p11331.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org
RE: Data from Mysql using JdbcRDD
Posted by srinivas <ku...@gmail.com>.
Hi Thanks All....i have few more questions on this
suppose i don't want to pass where caluse in my sql and is their a way that
i can do this.
Right now i am trying to modify JdbcRDD class by removing all the paramaters
for lower bound and upper bound. But i am getting run time exceptions.
Is their any work around solution to do normal sql queries with or without
using where clause or like selecting values for particular value?
Please help
-Srini.
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994p11174.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
RE: Data from Mysql using JdbcRDD
Posted by "Cheng, Hao" <ha...@intel.com>.
Probably you need to update the SQL like "SELECT * FROM student_info where id >= ? and id <= ?".
-----Original Message-----
From: srinivas [mailto:kusamsrinivas@gmail.com]
Sent: Thursday, July 31, 2014 6:55 AM
To: user@spark.incubator.apache.org
Subject: Data from Mysql using JdbcRDD
Hi,
I am trying to get data from mysql using JdbcRDD using code The table have three columns
val url = "jdbc:mysql://localhost:3306/studentdata"
val username = "root"
val password = "root"
val mysqlrdd = new org.apache.spark.rdd.JdbcRDD(sc,() => {
Class.forName("com.mysql.jdbc.Driver")
DriverManager.getConnection(url, username, password)
},"SELECT * FROM student_info",
1, 20, 2, r => r.getString("studentname"))
mysqlrdd.saveAsTextFile("/home/ubuntu/mysqljdbc")
I am getting runtime error as
14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648 to force MySQL streaming
14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648 to force MySQL streaming
14/07/30 22:05:04 INFO JdbcRDD: closed connection
14/07/30 22:05:04 INFO JdbcRDD: closed connection
14/07/30 22:05:04 ERROR Executor: Exception in task ID 0
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
Can anyone help. And let me know if i am missing anything.
Thanks,
-Srini.
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
Re: Data from Mysql using JdbcRDD
Posted by Josh Mahonin <jm...@interset.com>.
Hi Srini,
I believe the JdbcRDD requires input splits based on ranges within the
query itself. As an example, you could adjust your query to something like:
SELECT * FROM student_info WHERE id >= ? AND id <= ?
Note that the values you've passed in '1, 20, 2' correspond to the lower
bound index, upper bound index, and number of partitions. With that example
query and those values, you should end up with an RDD with two partitions,
one with the student_info from 1 through 10, and the second with ids 11
through 20.
Josh
On Wed, Jul 30, 2014 at 6:58 PM, chaitu reddy <ch...@gmail.com> wrote:
> Kc
> On Jul 30, 2014 3:55 PM, "srinivas" <ku...@gmail.com> wrote:
>
>> Hi,
>> I am trying to get data from mysql using JdbcRDD using code The table
>> have
>> three columns
>>
>> val url = "jdbc:mysql://localhost:3306/studentdata"
>> val username = "root"
>> val password = "root"
>> val mysqlrdd = new org.apache.spark.rdd.JdbcRDD(sc,() => {
>> Class.forName("com.mysql.jdbc.Driver")
>> DriverManager.getConnection(url, username, password)
>> },"SELECT * FROM student_info",
>> 1, 20, 2, r => r.getString("studentname"))
>> mysqlrdd.saveAsTextFile("/home/ubuntu/mysqljdbc")
>>
>> I am getting runtime error as
>>
>> 14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648
>> to
>> force MySQL streaming
>> 14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648
>> to
>> force MySQL streaming
>> 14/07/30 22:05:04 INFO JdbcRDD: closed connection
>> 14/07/30 22:05:04 INFO JdbcRDD: closed connection
>> 14/07/30 22:05:04 ERROR Executor: Exception in task ID 0
>> java.sql.SQLException: Parameter index out of range (1 > number of
>> parameters, which is 0).
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
>>
>> Can anyone help. And let me know if i am missing anything.
>>
>> Thanks,
>> -Srini.
>>
>>
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994.html
>> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>>
>
Re: Data from Mysql using JdbcRDD
Posted by chaitu reddy <ch...@gmail.com>.
Kc
On Jul 30, 2014 3:55 PM, "srinivas" <ku...@gmail.com> wrote:
> Hi,
> I am trying to get data from mysql using JdbcRDD using code The table have
> three columns
>
> val url = "jdbc:mysql://localhost:3306/studentdata"
> val username = "root"
> val password = "root"
> val mysqlrdd = new org.apache.spark.rdd.JdbcRDD(sc,() => {
> Class.forName("com.mysql.jdbc.Driver")
> DriverManager.getConnection(url, username, password)
> },"SELECT * FROM student_info",
> 1, 20, 2, r => r.getString("studentname"))
> mysqlrdd.saveAsTextFile("/home/ubuntu/mysqljdbc")
>
> I am getting runtime error as
>
> 14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648
> to
> force MySQL streaming
> 14/07/30 22:05:04 INFO JdbcRDD: statement fetch size set to: -2147483648 to
> force MySQL streaming
> 14/07/30 22:05:04 INFO JdbcRDD: closed connection
> 14/07/30 22:05:04 INFO JdbcRDD: closed connection
> 14/07/30 22:05:04 ERROR Executor: Exception in task ID 0
> java.sql.SQLException: Parameter index out of range (1 > number of
> parameters, which is 0).
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
>
> Can anyone help. And let me know if i am missing anything.
>
> Thanks,
> -Srini.
>
>
>
>
>
>
> --
> View this message in context:
> http://apache-spark-user-list.1001560.n3.nabble.com/Data-from-Mysql-using-JdbcRDD-tp10994.html
> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>