Connect SPARK with Oracle Databank

mario_borys — Mon, 11 Dec 2017 20:55:24 GMT

Hello,

I'm trying to Connect Spark with my Company Database and I'm getting this Error through Shell.

[hdfs@sandbox /]$ /usr/hdp/current/spark-client/bin/spark-shell --driver-class-path /usr/hdp/current/spark-client/lib/ojdbc8.jar

scala> val sqlContext = new org.apache.spark.sql.SQLContext(sc)

scala> val Connector = sqlContext.read.format("jdbc").options(Map("url" -> "jdbc:oracle:thin:@xxx.xxx.xxx:xxxx:xxxxxxxxxxxxxxxx?user=USERNAME&password=PASSWORD","dbtable" -> "PROCESS_DATA")).load().show()

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$2.apply(JdbcUtils.scala:61)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$2.apply(JdbcUtils.scala:52)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:120)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.<init>(JDBCRelation.scala:91)
at org.apache.spark.sql.execution.datasources.jdbc.DefaultSource.createRelation(DefaultSource.scala:57)
at org.apache.spark.sql.execution.datasources.ResolvedDataSource$.apply(ResolvedDataSource.scala:158)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:119)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:29)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:34)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:36)
at $iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:38)
at $iwC$$iwC$$iwC$$iwC.<init>(<console>:40)
at $iwC$$iwC$$iwC.<init>(<console>:42)
at $iwC$$iwC.<init>(<console>:44)
at $iwC.<init>(<console>:46)
at <init>(<console>:48)
at .<init>(<console>:52)
at .<clinit>(<console>)
at .<init>(<console>:7)
at .<clinit>(<console>)
at $print(<console>)
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:498)
at org.apache.spark.repl.SparkIMain$ReadEvalPrint.call(SparkIMain.scala:1065)
at org.apache.spark.repl.SparkIMain$Request.loadAndRun(SparkIMain.scala:1346)
at org.apache.spark.repl.SparkIMain.loadAndRunReq$1(SparkIMain.scala:840)
at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:871)
at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:819)
at org.apache.spark.repl.SparkILoop.reallyInterpret$1(SparkILoop.scala:857)
at org.apache.spark.repl.SparkILoop.interpretStartingWith(SparkILoop.scala:902)
at org.apache.spark.repl.SparkILoop.command(SparkILoop.scala:814)
at org.apache.spark.repl.SparkILoop.processLine$1(SparkILoop.scala:657)
at org.apache.spark.repl.SparkILoop.innerLoop$1(SparkILoop.scala:665)
at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$loop(SparkILoop.scala:670)
at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply$mcZ$sp(SparkILoop.scala:997)
at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
at scala.tools.nsc.util.ScalaClassLoader$.savingContextLoader(ScalaClassLoader.scala:135)
at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$process(SparkILoop.scala:945)
at org.apache.spark.repl.SparkILoop.process(SparkILoop.scala:1059)
at org.apache.spark.repl.Main$.main(Main.scala:31)
at org.apache.spark.repl.Main.main(Main.scala)
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:498)
at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:750)
at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:181)
at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:206)
at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:121)
at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:271)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
... 56 more

--- I think therefore I need to set the SID in the Database ? Someone a clue ?

Re: Connect SPARK with Oracle Databank

bkosaraju — Wed, 13 Dec 2017 08:40:41 GMT

Hi @Mario Borys,

The error is oiginated from Database front as the Connection URL doesn't consists of the SID information.

as per the driver URL documentation, we must provide the SID information in along with the port number.

so the new URL look like

"url" -> "jdbc:oracle:thin:<userName>/<Password>@<ip/hostname>:<port num ex: 1521>:<SID name>"
or

"url" -> "jdbc:oracle:thin:@<ip/hostname>:<port num ex: 1521>:<SID name>?user=USERNAME&password=PASSWORD" //for password with special charectorys

Hope this helps!!

Re: Connect SPARK with Oracle Databank

mario_borys — Wed, 13 Dec 2017 17:06:06 GMT

Hi bkosaraju,

it works, thank you very much!

If I wanna use the same comand in Zeppelin I need the ojdbc.jar. Do you know how I have to set the Classpath to the jar?

Greatings Mario

Re: Connect SPARK with Oracle Databank

bkosaraju — Mon, 18 Dec 2017 08:45:21 GMT

Hi @Mario Borys

Glad that it helped !!, by accepting the solution other HCC users find the answer directly.

now on to your other question, Yes it is possible by adding the spark.jars argument in interpreter configuration with ojdbc dirver jar file.

after you can create the context with same process how you did for the command line

more on how to configure Interpreter can be found at here

question Re: Connect SPARK with Oracle Databank in Archives of Support Questions (Read Only)

Connect SPARK with Oracle Databank

Re: Connect SPARK with Oracle Databank

Re: Connect SPARK with Oracle Databank

Re: Connect SPARK with Oracle Databank