Created 12-11-2017 12:55 PM
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 ?
Created 12-13-2017 12:40 AM
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!!
Created 12-13-2017 12:40 AM
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!!
Created 12-13-2017 09:06 AM
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
Created 12-18-2017 12:45 AM
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