You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Richard Xin <ri...@yahoo.com.INVALID> on 2019/06/18 22:49:24 UTC
tcps oracle connection from spark
HI, I need help with tcps oracle connection from spark (version: spark-2.4.0-bin-hadoop2.7)
<pre>Properties prop = new Properties();prop.putAll(sparkOracle); // username/password
prop.put("javax.net.ssl.trustStore", "path to root.jks");prop.put("javax.net.ssl.trustStorePassword", "password_here");
df.write().mode(SaveMode.Append).option("driver", "oracle.jdbc.driver.OracleDriver").jdbc("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcps)(HOST=host.mycomapny.com)(PORT=1234)))(CONNECT_DATA=(SERVICE_NAME=service_name)))","tableName", prop);</pre>
note "PROTOCOL=tcps" in the connection string.
The code worked fine for "tcp" hosts, but some of our servers use "tcps" only, I got following errors when hitting oracld tcps hosts, can someone shed some light? Thanks a lot!
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Remote host terminated the handshake at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564) at org.apache.spark.sql.execution.datasources.jdbc.DriverWrapper.connect(DriverWrapper.scala:45) at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63) at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54) at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:48) at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68) at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:86) at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:131) at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:127) at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:155) at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151) at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:152) at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:127) at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:80) at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:80) at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668) at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668) at org.apache.spark.sql.execution.SQLExecution$$anonfun$withNewExecutionId$1.apply(SQLExecution.scala:78) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:125) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:73) at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:668) at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:276) at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:270) at org.apache.spark.sql.DataFrameWriter.jdbc(DataFrameWriter.scala:506) at com.apple.jmet.pallas.data_migration.DirectMigrationWConfig.main(DirectMigrationWConfig.java:103) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.spark.deploy.JavaMainApplication.start(SparkApplication.scala:52) at org.apache.spark.deploy.SparkSubmit.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:849) at org.apache.spark.deploy.SparkSubmit.doRunMain$1(SparkSubmit.scala:167) at org.apache.spark.deploy.SparkSubmit.submit(SparkSubmit.scala:195) at org.apache.spark.deploy.SparkSubmit.doSubmit(SparkSubmit.scala:86) at org.apache.spark.deploy.SparkSubmit$$anon$2.doSubmit(SparkSubmit.scala:924) at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:933) at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)Caused by: javax.net.ssl.SSLHandshakeException: Remote host terminated the handshake at java.base/sun.security.ssl.SSLSocketImpl.handleEOF(SSLSocketImpl.java:1321) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1160) at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1063) at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:402) at java.base/sun.security.ssl.SSLSocketImpl.ensureNegotiated(SSLSocketImpl.java:716) at java.base/sun.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:970) at oracle.net.ns.Packet.send(Packet.java:419) at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241) at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:151) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486) ... 42 moreCaused by: java.io.EOFException: SSL peer shut down incorrectly at java.base/sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:167) at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:108) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1152) ... 52 more
RE: tcps oracle connection from spark
Posted by Luca Canali <Lu...@cern.ch>.
Connecting to Oracle from Spark using the TPCS protocol works OK for me.
Maybe try to turn debug on with -Djavax.net.debug=all?
See also:
https://blogs.oracle.com/dev2dev/ssl-connection-to-oracle-db-using-jdbc%2c-tlsv12%2c-jks-or-oracle-wallets
Regards,
L.
From: Richard Xin <ri...@yahoo.com.INVALID>
Sent: Wednesday, June 19, 2019 00:51
To: User <us...@spark.apache.org>
Subject: Re: tcps oracle connection from spark
and btw, same connection string works fine when used in SQL Developer.
On Tuesday, June 18, 2019, 03:49:24 PM PDT, Richard Xin <ri...@yahoo.com>> wrote:
HI, I need help with tcps oracle connection from spark (version: spark-2.4.0-bin-hadoop2.7)
<pre>
Properties prop = new Properties();
prop.putAll(sparkOracle); // username/password
prop.put("javax.net.ssl.trustStore", "path to root.jks");
prop.put("javax.net.ssl.trustStorePassword", "password_here");
df.write()
.mode(SaveMode.Append)
.option("driver", "oracle.jdbc.driver.OracleDriver")
.jdbc("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcps)(HOST=host.mycomapny.com)(PORT=1234)))(CONNECT_DATA=(SERVICE_NAME=service_name)))","tableName", prop)
;
</pre>
note "PROTOCOL=tcps" in the connection string.
The code worked fine for "tcp" hosts, but some of our servers use "tcps" only, I got following errors when hitting oracld tcps hosts, can someone shed some light? Thanks a lot!
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Remote host terminated the handshake
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
at org.apache.spark.sql.execution.datasources.jdbc.DriverWrapper.connect(DriverWrapper.scala:45)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:48)
at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45)
at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70)
at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68)
at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:86)
at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:131)
at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:127)
at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:155)
at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:152)
at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:127)
at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:80)
at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:80)
at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668)
at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668)
at org.apache.spark.sql.execution.SQLExecution$$anonfun$withNewExecutionId$1.apply(SQLExecution.scala:78)
at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:125)
at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:73)
at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:668)
at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:276)
at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:270)
at org.apache.spark.sql.DataFrameWriter.jdbc(DataFrameWriter.scala:506)
at com.apple.jmet.pallas.data_migration.DirectMigrationWConfig.main(DirectMigrationWConfig.java:103)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.spark.deploy.JavaMainApplication.start(SparkApplication.scala:52)
at org.apache.spark.deploy.SparkSubmit.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:849)
at org.apache.spark.deploy.SparkSubmit.doRunMain$1(SparkSubmit.scala:167)
at org.apache.spark.deploy.SparkSubmit.submit(SparkSubmit.scala:195)
at org.apache.spark.deploy.SparkSubmit.doSubmit(SparkSubmit.scala:86)
at org.apache.spark.deploy.SparkSubmit$$anon$2.doSubmit(SparkSubmit.scala:924)
at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:933)
at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
Caused by: javax.net.ssl.SSLHandshakeException: Remote host terminated the handshake
at java.base/sun.security.ssl.SSLSocketImpl.handleEOF(SSLSocketImpl.java:1321)
at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1160)
at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1063)
at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:402)
at java.base/sun.security.ssl.SSLSocketImpl.ensureNegotiated(SSLSocketImpl.java:716)
at java.base/sun.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:970)
at oracle.net.ns.Packet.send(Packet.java:419)
at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241)
at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:151)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
... 42 more
Caused by: java.io.EOFException: SSL peer shut down incorrectly
at java.base/sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:167)
at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:108)
at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1152)
... 52 more
Re: tcps oracle connection from spark
Posted by Richard Xin <ri...@yahoo.com.INVALID>.
and btw, same connection string works fine when used in SQL Developer.
On Tuesday, June 18, 2019, 03:49:24 PM PDT, Richard Xin <ri...@yahoo.com> wrote:
HI, I need help with tcps oracle connection from spark (version: spark-2.4.0-bin-hadoop2.7)
<pre>Properties prop = new Properties();prop.putAll(sparkOracle); // username/password
prop.put("javax.net.ssl.trustStore", "path to root.jks");prop.put("javax.net.ssl.trustStorePassword", "password_here");
df.write().mode(SaveMode.Append).option("driver", "oracle.jdbc.driver.OracleDriver").jdbc("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcps)(HOST=host.mycomapny.com)(PORT=1234)))(CONNECT_DATA=(SERVICE_NAME=service_name)))","tableName", prop);</pre>
note "PROTOCOL=tcps" in the connection string.
The code worked fine for "tcp" hosts, but some of our servers use "tcps" only, I got following errors when hitting oracld tcps hosts, can someone shed some light? Thanks a lot!
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Remote host terminated the handshake at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564) at org.apache.spark.sql.execution.datasources.jdbc.DriverWrapper.connect(DriverWrapper.scala:45) at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63) at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54) at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:48) at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68) at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:86) at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:131) at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:127) at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:155) at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151) at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:152) at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:127) at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:80) at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:80) at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668) at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:668) at org.apache.spark.sql.execution.SQLExecution$$anonfun$withNewExecutionId$1.apply(SQLExecution.scala:78) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:125) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:73) at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:668) at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:276) at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:270) at org.apache.spark.sql.DataFrameWriter.jdbc(DataFrameWriter.scala:506) at com.apple.jmet.pallas.data_migration.DirectMigrationWConfig.main(DirectMigrationWConfig.java:103) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.spark.deploy.JavaMainApplication.start(SparkApplication.scala:52) at org.apache.spark.deploy.SparkSubmit.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:849) at org.apache.spark.deploy.SparkSubmit.doRunMain$1(SparkSubmit.scala:167) at org.apache.spark.deploy.SparkSubmit.submit(SparkSubmit.scala:195) at org.apache.spark.deploy.SparkSubmit.doSubmit(SparkSubmit.scala:86) at org.apache.spark.deploy.SparkSubmit$$anon$2.doSubmit(SparkSubmit.scala:924) at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:933) at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)Caused by: javax.net.ssl.SSLHandshakeException: Remote host terminated the handshake at java.base/sun.security.ssl.SSLSocketImpl.handleEOF(SSLSocketImpl.java:1321) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1160) at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1063) at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:402) at java.base/sun.security.ssl.SSLSocketImpl.ensureNegotiated(SSLSocketImpl.java:716) at java.base/sun.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:970) at oracle.net.ns.Packet.send(Packet.java:419) at oracle.net.ns.ConnectPacket.send(ConnectPacket.java:241) at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:151) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:263) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486) ... 42 moreCaused by: java.io.EOFException: SSL peer shut down incorrectly at java.base/sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:167) at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:108) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1152) ... 52 more