Re: Fwd: Writing to jdbc database from SparkR (1.5.2)

[Felix Cheung]

Unfortunately I couldn't find a simple workaround. It seems to be an issue with 
DataFrameWriter.save() that does not work with jdbc source/format
For instance, this does not work in Scala 
eitherdf1.write.format("jdbc").mode("overwrite").option("url", 
"jdbc:mysql://something.rds.amazonaws.com:3306?user=user&password=password").option("dbtable",
 "table").save()            
For Spark 1.5.x, it seems the best option would be to write a JVM wrapper and 
call it from R.

    _____________________________
From: Andrew Holway <andrew.hol...@otternetworks.de>
Sent: Saturday, February 6, 2016 11:22 AM
Subject: Fwd: Writing to jdbc database from SparkR (1.5.2)
To:  <dev@spark.apache.org>


       Hi,       
          I have a thread on     u...@spark.apache.org but I think this might 
require developer attention.    
         
             I'm reading data from a database: This is working well.     
     
                                             

> df <- read.df(sqlContext, source="jdbc", 
> url="jdbc:mysql://database.foo.eu-west-1.rds.amazonaws.com:3306?user=user&password=pass")
>                             
                          When I try and write something back to the DB I see 
this following error:                         
                      

> write.df(fooframe, path="NULL", source="jdbc", 
> url="jdbc:mysql://database.foo.eu-west-1.rds.amazonaws.com:3306?user=user&password=pass",
>  dbtable="db.table", mode="append")       


        

16/02/06 19:05:43 ERROR RBackendHandler: save on 2 failed        

Error in invokeJava(isStatic = FALSE, objId$id, methodName, ...) :  

  java.lang.RuntimeException: 
org.apache.spark.sql.execution.datasources.jdbc.DefaultSource does not allow 
create table as select. 

 at scala.sys.package$.error(package.scala:27) 

 at 
org.apache.spark.sql.execution.datasources.ResolvedDataSource$.apply(ResolvedDataSource.scala:200)
 

 at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:146) 

 at org.apache.spark.sql.DataFrame.save(DataFrame.scala:1855) 

 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 

 at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 

 at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 

 at java.lang.reflect.Method.invoke(Method.java:497) 

 at 
org.apache.spark.api.r.RBackendHandler.handleMethodCall(RBackendHandler.scala:132)
 

 at 
org.apache.spark.api.r.RBackendHandler.channelRead0(RBackendHandler.scala:79) 

 at 
org.apache.spark.api.r.RBackendHandler.channelRead0(RBackendHandler.scala:38) 

 at io.netty.channel.SimpleChannelIn




Any ideas on a workaround?




Thanks,




Andrew