how to use all JDBC methods i have an issue when i am trying to use sparkSession.Read().Jdbc(string url,string table, string columnName, string loweBound, string upperBound, int numPArtitions, Dictionary(string, string) connectionProperties)

[jayprajapati857]

i want to use the jdbc method for data persistance and i need to use pertition column and have to do partition on to mysql table using spark.

i am using currently the method like

SparkSession sparkSession = SparkSession.Builder().Master(ConfigurationSettings.AppSettings["SparkMaster"]).Config("spark.executor.extraClassPath", ConfigurationSettings.AppSettings["MySqlConnector"]).GetOrCreate();

var data = sparkSession.Read().Jdbc(SparkQueryProcessing.connectionString, ConfigurationSettings.AppSettings["TableToPersist"], "call_id", "687758", "5462180",4,properties);

but there is an error

sparkclr-submit --exe SparkQueryProcessing.exe C:\Projects\C
mAnalytics\SparkQueryProcessing\bin\Debug
[sparkclr-submit.cmd] SPARKCLR_JAR=spark-clr_2.11-2.0.200.jar
[sparkclr-submit.cmd] LAUNCH_CLASSPATH="C:\SparkClr\runtime\lib\spark-clr_2.11-2
.0.200.jar;C:\Mobius\build\tools\spark-2.0.0-bin-hadoop2.6\jars*"
[sparkclr-submit.cmd] Command to run --name SparkQueryProcessing --class org.apa
che.spark.deploy.csharp.CSharpRunner C:\SparkClr\runtime\lib\spark-clr_2.11-2.0.
200.jar C:\Projects\CmAnalytics\SparkQueryProcessing\bin\Debug C:\Projects\CmAna
lytics\SparkQueryProcessing\bin\Debug\SparkQueryProcessing.exe
[2017-10-19T06:29:26.4538812Z] [SUPP-INF-DEV-11] [Info] [ConfigurationService] C
onfigurationService runMode is LOCAL
[2017-10-19T06:29:26.4838785Z] [SUPP-INF-DEV-11] [Info] [SparkCLRConfiguration]
CSharpBackend successfully read from environment variable CSHARPBACKEND_PORT
[2017-10-19T06:29:26.4838785Z] [SUPP-INF-DEV-11] [Info] [SparkCLRIpcProxy] CShar
pBackend port number to be used in JvMBridge is 51324
[2017-10-19T06:29:26.5638713Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark app na
me set to CustomeFilterCalculations
[2017-10-19T06:29:26.5638713Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark master
set to spark://192.168.0.222:7077
[2017-10-19T06:29:26.5638713Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark config
uration key-value set to spark.storage.StorageLevel=DISK_ONLY
[2017-10-19T06:29:26.5638713Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark config
uration key-value set to spark.executor.memory=4G
[2017-10-19T06:29:27.6907777Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark config
uration key-value set to spark.master=spark://192.168.0.222:7077
[2017-10-19T06:29:27.6917777Z] [SUPP-INF-DEV-11] [Info] [SparkConf] Spark config
uration key-value set to spark.executor.extraClassPath=C:\Mobius\build\tools\spa
rk-2.0.0-bin-hadoop2.6\mysql_connector\mysql.jar
First_call_id : 687758 LAst_calll_id : 5462180
[2017-10-19T06:29:28.8327111Z] [SUPP-INF-DEV-11] [Info] [SparkSession] Using Dat
aFrameReader to read input data from external data source
[2017-10-19T06:29:28.8327111Z] [SUPP-INF-DEV-11] [Info] [DataFrameReader] Constr
ucting DataFrame using JDBC source. Url=jdbc:mysql://192.168.0.31:3306/cmanalyti
cs?zeroDateTimeBehavior=convertToNull, tableName=temp_call_stats, columnName=cal
l_id
17/10/19 11:59:28 WARN CSharpBackendHandler: cannot find matching method class o
rg.apache.spark.sql.DataFrameReader.jdbc. Candidates are:
17/10/19 11:59:28 WARN CSharpBackendHandler: jdbc(class java.lang.String,class j
ava.lang.String,class [Ljava.lang.String;,class java.util.Properties)
17/10/19 11:59:28 WARN CSharpBackendHandler: jdbc(class java.lang.String,class j
ava.lang.String,class java.util.Properties)
17/10/19 11:59:28 WARN CSharpBackendHandler: jdbc(class java.lang.String,class j
ava.lang.String,class java.lang.String,long,long,int,class java.util.Properties)

17/10/19 11:59:28 ERROR CSharpBackendHandler: methods:
17/10/19 11:59:28 ERROR CSharpBackendHandler: args:
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.String, argValu
e: jdbc:mysql://192.168.0.31:3306/cmanalytics?zeroDateTimeBehavior=convertToNull

17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.String, argValu
e: temp_call_stats
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.String, argValu
e: call_id
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.String, argValu
e: 687758
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.String, argValu
e: 5462180
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.lang.Integer, argVal
ue: 4
17/10/19 11:59:28 ERROR CSharpBackendHandler: argType: java.util.Properties, arg
Value: {user=root, password=FaNG4cY}
[2017-10-19T06:29:28.8427102Z] [SUPP-INF-DEV-11] [Error] [JvmBridge] JVM method
execution failed: Nonstatic method jdbc failed for class 13 when called with 7 p
arameters ([Index=1, Type=String, Value=jdbc:mysql://192.168.0.31:3306/cmanalyti
cs?zeroDateTimeBehavior=convertToNull], [Index=2, Type=String, Value=temp_call_s
tats], [Index=3, Type=String, Value=call_id], [Index=4, Type=String, Value=68775
8], [Index=5, Type=String, Value=5462180], [Index=6, Type=Int32, Value=4], [Inde
x=7, Type=JvmObjectReference, Value=14], )
[2017-10-19T06:29:28.8427102Z] [SUPP-INF-DEV-11] [Error] [JvmBridge]
[2017-10-19T06:29:28.8427102Z] [SUPP-INF-DEV-11] [Exception] [JvmBridge] JVM met
hod execution failed: Nonstatic method jdbc failed for class 13 when called with
7 parameters ([Index=1, Type=String, Value=jdbc:mysql://192.168.0.31:3306/cmana
lytics?zeroDateTimeBehavior=convertToNull], [Index=2, Type=String, Value=temp_ca
ll_stats], [Index=3, Type=String, Value=call_id], [Index=4, Type=String, Value=6
87758], [Index=5, Type=String, Value=5462180], [Index=6, Type=Int32, Value=4], [
Index=7, Type=JvmObjectReference, Value=14], )
at Microsoft.Spark.CSharp.Interop.Ipc.JvmBridge.CallJavaMethod(Boolean isStat
ic, Object classNameOrJvmObjectReference, String methodName, Object[] parameters
)

Unhandled Exception: System.Exception: JVM method execution failed: Nonstatic me
thod jdbc failed for class 13 when called with 7 parameters ([Index=1, Type=Stri
ng, Value=jdbc:mysql://192.168.0.31:3306/cmanalytics?zeroDateTimeBehavior=conver
tToNull], [Index=2, Type=String, Value=temp_call_stats], [Index=3, Type=String,
Value=call_id], [Index=4, Type=String, Value=687758], [Index=5, Type=String, Val
ue=5462180], [Index=6, Type=Int32, Value=4], [Index=7, Type=JvmObjectReference,
Value=14], )
at Microsoft.Spark.CSharp.Interop.Ipc.JvmBridge.CallJavaMethod(Boolean isStat
ic, Object classNameOrJvmObjectReference, String methodName, Object[] parameters
)
at Microsoft.Spark.CSharp.Interop.Ipc.JvmBridge.CallNonStaticJavaMethod(JvmOb
jectReference objectId, String methodName, Object[] parameters)
at Microsoft.Spark.CSharp.Proxy.Ipc.DataFrameReaderIpcProxy.Jdbc(String url,
String table, String columnName, String lowerBound, String upperBound, Int32 num
Partitions, Dictionary2 connectionProperties) at Microsoft.Spark.CSharp.Sql.DataFrameReader.Jdbc(String url, String table, String columnName, String lowerBound, String upperBound, Int32 numPartitions, Di ctionary2 connectionProperties)
at SparkQueryProcessing.DataPersistance.PersistCallStatsData() in c:\Projects
\CmAnalytics\SparkQueryProcessing\DataPersistance.cs:line 21
at SparkQueryProcessing.SparkQueryProcessing.Main(String[] args) in c:\Projec
ts\CmAnalytics\SparkQueryProcessing\SparkQueryProcessing.cs:line 82
17/10/19 12:01:26 ERROR CSharpBackendHandler: Exception caught:
java.io.IOException: An existing connection was forcibly closed by the remote ho
st
at sun.nio.ch.SocketDispatcher.read0(Native Method)
at sun.nio.ch.SocketDispatcher.read(Unknown Source)
at sun.nio.ch.IOUtil.readIntoNativeBuffer(Unknown Source)
at sun.nio.ch.IOUtil.read(Unknown Source)
at sun.nio.ch.SocketChannelImpl.read(Unknown Source)
at io.netty.buffer.UnpooledUnsafeDirectByteBuf.setBytes(UnpooledUnsafeDi
rectByteBuf.java:447)
at io.netty.buffer.AbstractByteBuf.writeBytes(AbstractByteBuf.java:881)
at io.netty.channel.socket.nio.NioSocketChannel.doReadBytes(NioSocketCha
nnel.java:242)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(Abstra
ctNioByteChannel.java:119)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.jav
a:511)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEve
ntLoop.java:468)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.ja
va:382)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThread
EventExecutor.java:111)
at io.netty.util.concurrent.DefaultThreadFactory$DefaultRunnableDecorato
r.run(DefaultThreadFactory.java:137)
at java.lang.Thread.run(Unknown Source)
17/10/19 12:01:26 ERROR CSharpBackendHandler: Exception caught:
java.io.IOException: An existing connection was forcibly closed by the remote ho
st
at sun.nio.ch.SocketDispatcher.read0(Native Method)
at sun.nio.ch.SocketDispatcher.read(Unknown Source)
at sun.nio.ch.IOUtil.readIntoNativeBuffer(Unknown Source)
at sun.nio.ch.IOUtil.read(Unknown Source)
at sun.nio.ch.SocketChannelImpl.read(Unknown Source)
at io.netty.buffer.UnpooledUnsafeDirectByteBuf.setBytes(UnpooledUnsafeDi
rectByteBuf.java:447)
at io.netty.buffer.AbstractByteBuf.writeBytes(AbstractByteBuf.java:881)
at io.netty.channel.socket.nio.NioSocketChannel.doReadBytes(NioSocketCha
nnel.java:242)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(Abstra
ctNioByteChannel.java:119)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.jav
a:511)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEve
ntLoop.java:468)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.ja
va:382)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThread
EventExecutor.java:111)
at io.netty.util.concurrent.DefaultThreadFactory$DefaultRunnableDecorato
r.run(DefaultThreadFactory.java:137)
at java.lang.Thread.run(Unknown Source)
17/10/19 12:01:26 WARN TransportChannelHandler: Exception in connection from /19
2.168.0.222:51385
java.io.IOException: An existing connection was forcibly closed by the remote ho
st
at sun.nio.ch.SocketDispatcher.read0(Native Method)
at sun.nio.ch.SocketDispatcher.read(Unknown Source)
at sun.nio.ch.IOUtil.readIntoNativeBuffer(Unknown Source)
at sun.nio.ch.IOUtil.read(Unknown Source)
at sun.nio.ch.SocketChannelImpl.read(Unknown Source)
at io.netty.buffer.PooledUnsafeDirectByteBuf.setBytes(PooledUnsafeDirect
ByteBuf.java:313)
at io.netty.buffer.AbstractByteBuf.writeBytes(AbstractByteBuf.java:881)
at io.netty.channel.socket.nio.NioSocketChannel.doReadBytes(NioSocketCha
nnel.java:242)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(Abstra
ctNioByteChannel.java:119)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.jav
a:511)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEve
ntLoop.java:468)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.ja
va:382)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:354)
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThread
EventExecutor.java:111)
at java.lang.Thread.run(Unknown Source)
17/10/19 12:01:26 WARN Dispatcher: Message RemoteProcessConnectionError(java.io.
IOException: An existing connection was forcibly closed by the remote host,192.1
68.0.222:51385) dropped. Could not find BlockManagerMaster.
17/10/19 12:01:26 WARN Dispatcher: Message RemoteProcessDisconnected(192.168.0.2
22:51385) dropped. RpcEnv already stopped.
17/10/19 12:01:26 WARN Dispatcher: Message RemoteProcessDisconnected(192.168.0.2
22:51385) dropped. RpcEnv already stopped.