Ok I sorted this one out.
In file $SPARK_HOME/bin/conf/spark-defaults.conf
Set the parameter *spark.driver.extraClassPath* to the additional jar files
that you need --> ojdbc8.jar","oraclepki.jar","osdt_cert.jar","osdt_core.jar
.
spark.driver.extraClassPath
/home/hduser/jars/jconn4.jar:/home/hduser/jars/ojdbc8.jar:/home/hduser/jars/oraclepki.jar:/home/hduser/jars/osdt_cert.jar:/home/hduser/jars/osdt_core.jar
*I had this referring to the old ojdb6,jar previously so cause of errors!*
To diagnose the problem in Spark session find out what the JAVA CLASSPATH
set. Just open a session in Spark shell and do
scala> *System.getProperty("java.class.path")*
res0: String = /home/hduser/jars/jconn4.jar:
*/home/hduser/jars/ojdbc8.jar:/home/hduser/jars/oraclepki.jar:/home/hduser/jars/osdt_cert.jar:/home/hduser/jars/osdt_core.jar*
Ok they are there so the connection should work. If they are not there,
then JDBC connection to ADW is not going to work
Let us test this
import java.sql.DriverManager
import java.sql.Connection
import java.sql.DatabaseMetaData
import java.sql.ResultSet
import java.sql.SQLException
import java.util.ArrayList
import org.apache.spark.sql.functions._
import java.sql.{Date, Timestamp}
val driverName = "oracle.jdbc.OracleDriver"
//var url= "jdbc:oracle:thin:@rhes564:1521:mydb12" // Old example works up
to 12c
// Define URL for ADW. *DBAccess directory is the location of UNZIPPED
Wallet_<DB>.zip that you downloaded from ADW connection page*
var url = "jdbc:oracle:thin:@mydb_high
?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
var _username = "scratchpad"
var _password = "xxxxxxxxx"
var _dbschema = "SCRATCHPAD"
var _dbtable = "LL_18201960"
var e:SQLException = null
var connection:Connection = null
var metadata:DatabaseMetaData = null
// Define prop
val prop = new java.util.Properties
prop.setProperty("user", _username)
prop.setProperty("password",_password)
//
// Check Oracle is accessible
try {
connection = DriverManager.getConnection(url, _username, _password)
} catch {
case e: SQLException => e.printStackTrace
connection.close()
}
metadata = connection.getMetaData()
And this is the output
scala> try {
| connection = DriverManager.getConnection(url, _username,
_password)
| } catch {
| case e: SQLException => e.printStackTrace
| connection.close()
| }
AArray = [B@61cb973d
AArray = [B@3b1261ed
AArray = [B@23fd9be1
scala> metadata = connection.getMetaData()
metadata: java.sql.DatabaseMetaData =
oracle.jdbc.driver.OracleDatabaseMetaData@545ac5d5
You can of course add these to HDFS for YARN mode as below in my article "The
Operational Advantages of Spark as a Distributed Processing Framework
<https://www.linkedin.com/pulse/operational-advantages-spark-distributed-processing-mich/>
"
Putting Spark Jar files on HDFS
In Yarn mode, *it is important that Spark jar files are available
throughout the Spark cluster*. I have spent a fair bit of time on this and
I recommend that you follow this procedure to make sure that the
spark-submit job runs ok. Use the spark.yarn.archive configuration option
and set that to the location of an archive (you create on HDFS) containing
all the JARs in the $SPARK_HOME/jars/ folder, at the root level of the
archive. For example:
1) Create the archive:
jar cv0f spark-libs.jar -C $SPARK_HOME/jars/ .2) Create a directory
on HDFS for the jars accessible to the application
hdfs dfs -mkdir /jars3) Upload to HDFS:
hdfs dfs -put spark-libs.jar /jars4) For a large cluster, increase
the replication count of the Spark archive
so that you reduce the amount of times a NodeManager will do a remote copy
hdfs dfs -setrep -w 10 hdfs:///jars/spark-libs.jar (Change the amount of
replicas proportional to the number of total NodeManagers)3) In
$SPARK_HOME/conf/spark-defaults.conf file set
spark.yarn.archive to hdfs:///rhes75:9000/jars/spark-libs.jar. Similar to
below
spark.yarn.archive=hdfs://rhes75:9000/jars/spark-libs.jar
HTH
Mich
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On Thu, 27 Aug 2020 at 17:34, <kuassi.men...@oracle.com> wrote:
> Mich,
>
> That's right, referring to you guys.
>
> Cheers, Kuassi
> On 8/27/20 9:27 AM, Mich Talebzadeh wrote:
>
> Thanks Kuassi,
>
> I presume you mean Spark DEV team by "they are using ... "
>
> cheers,
>
> Mich
>
>
>
> LinkedIn *
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!M88sYUJxwSuzuyOSFENThsP9nncvOVkQvjolE69LmD9sUxRhpRDNkuuKPwHsPVs4NQ$>*
>
>
>
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
>
> On Thu, 27 Aug 2020 at 17:11, <kuassi.men...@oracle.com> wrote:
>
>> According to our dev team.
>>
>> From the error it is evident that they are using a jdbc jar which does
>> not support setting tns_admin in URL.
>> They might have some old jar in class-path which is being used instead of
>> 18.3 jar.
>> You can ask them to use either full URL or tns alias format URL with
>> tns_admin path set as either connection property or system property.
>>
>> Regards, Kuassi
>> On 8/26/20 2:11 PM, Mich Talebzadeh wrote:
>>
>> And this is a test using Oracle supplied JAVA
>> script DataSourceSample.java with slight amendment for login/password and
>> table. it connects ok
>>
>> hduser@rhes76: /home/hduser/dba/bin/ADW/src> javac -classpath
>> ./ojdbc8.jar:. DataSourceSample.java
>> hduser@rhes76: /home/hduser/dba/bin/ADW/src> java -classpath
>> ./ojdbc8.jar:. DataSourceSample
>> AArray = [B@57d5872c
>> AArray = [B@667a738
>> AArray = [B@2145433b
>> Driver Name: Oracle JDBC driver
>> Driver Version: 18.3.0.0.0
>> Default Row Prefetch Value is: 20
>> Database Username is: SCRATCHPAD
>>
>> DATETAKEN WEIGHT
>> ---------------------
>> 2017-09-07 07:22:09 74.7
>> 2017-09-08 07:26:18 74.8
>> 2017-09-09 07:15:53 75
>> 2017-09-10 07:53:30 75.9
>> 2017-09-11 07:21:49 75.8
>> 2017-09-12 07:31:27 75.6
>> 2017-09-26 07:11:26 75.4
>> 2017-09-27 07:22:48 75.6
>> 2017-09-28 07:15:52 75.4
>> 2017-09-29 07:30:40 74.9
>>
>>
>>
>> Regards,
>>
>>
>> LinkedIn *
>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!K1lIv4Tn9yeWXGcfb2Zru8i7NZguGuAy1VxoSqORVtoQ_AJbkZohU0cXYquoFAJWTA$>*
>>
>>
>>
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>>
>> On Wed, 26 Aug 2020 at 21:58, Mich Talebzadeh <mich.talebza...@gmail.com>
>> wrote:
>>
>>> Hi Kuassi,
>>>
>>> This is the error. Only test running on local mode
>>>
>>> scala> val driverName = "oracle.jdbc.OracleDriver"
>>> driverName: String = oracle.jdbc.OracleDriver
>>>
>>> scala> var url = "jdbc:oracle:thin:@mydb_high
>>> ?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
>>> url: String = jdbc:oracle:thin:@mydb_high
>>> ?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess
>>> scala> var _username = "scratchpad"
>>> _username: String = scratchpad
>>> scala> var _password = "xxxxxxxxxx" -- no special characters
>>> _password: String = xxxxxxxxxxx
>>> scala> var _dbschema = "SCRATCHPAD"
>>> _dbschema: String = SCRATCHPAD
>>> scala> var _dbtable = "LL_18201960"
>>> _dbtable: String = LL_18201960
>>> scala> var e:SQLException = null
>>> e: java.sql.SQLException = null
>>> scala> var connection:Connection = null
>>> connection: java.sql.Connection = null
>>> scala> var metadata:DatabaseMetaData = null
>>> metadata: java.sql.DatabaseMetaData = null
>>> scala> val prop = new java.util.Properties
>>> prop: java.util.Properties = {}
>>> scala> prop.setProperty("user", _username)
>>> res1: Object = null
>>> scala> prop.setProperty("password",_password)
>>> res2: Object = null
>>> scala> // Check Oracle is accessible
>>>
>>> *scala> try { *
>>> * | connection = DriverManager.getConnection(url, _username,
>>> _password)*
>>> * | } catch {*
>>> * | case e: SQLException => e.printStackTrace*
>>> * | connection.close()*
>>> * | }*
>>> *java.sql.SQLRecoverableException: IO Error: Invalid connection string
>>> format, a valid format is: "host:port:sid"*
>>> at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
>>> at
>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
>>> at
>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
>>> at
>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
>>> at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>>>
>>> Is this related to Oracle or Spark? Do I need to set up another
>>> connection parameter etc?
>>>
>>>
>>>
>>> Cheers
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>>
>>> On Wed, 26 Aug 2020 at 21:09, <kuassi.men...@oracle.com> wrote:
>>>
>>>> Mich,
>>>>
>>>> All looks fine.
>>>> Perhaps some special chars in username or password?
>>>>
>>>> it is recommended not to use such characters like '@', '.' in your
>>>> password.
>>>>
>>>> Best, Kuassi
>>>> On 8/26/20 12:52 PM, Mich Talebzadeh wrote:
>>>>
>>>> Thanks Kuassi.
>>>>
>>>> This is the version of jar file that work OK with JDBC connection via
>>>> JAVA to ADW
>>>>
>>>> unzip -p ojdbc8.jar META-INF/MANIFEST.MF
>>>> Manifest-Version: 1.0
>>>> Implementation-Title: JDBC
>>>> *Implementation-Version: 18.3.0.0.0*
>>>> sealed: true
>>>> Specification-Vendor: Sun Microsystems Inc.
>>>> Specification-Title: JDBC
>>>> Class-Path: oraclepki.jar
>>>> Implementation-Vendor: Oracle Corporation
>>>> Main-Class: oracle.jdbc.OracleDriver
>>>> Ant-Version: Apache Ant 1.7.1
>>>> Repository-Id: JAVAVM_18.1.0.0.0_LINUX.X64_180620
>>>> Created-By: 25.171-b11 (Oracle Corporation)
>>>> Specification-Version: 4.0
>>>>
>>>> And this the setting for TNS_ADMIN
>>>>
>>>> e*cho ${TNS_ADMIN}*
>>>> */home/hduser/dba/bin/ADW/DBAccess*
>>>>
>>>> hduser@rhes76: /home/hduser/dba/bin/ADW/DBAccess> *cat
>>>> ojdbc.properties*
>>>> *# Connection property while using Oracle wallets.*
>>>>
>>>> *oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))*
>>>> *# FOLLOW THESE STEPS FOR USING JKS*
>>>> *# (1) Uncomment the following properties to use JKS.*
>>>> *# (2) Comment out the oracle.net.wallet_location property above*
>>>> *# (3) Set the correct password for both trustStorePassword and
>>>> keyStorePassword.*
>>>> *# It's the password you specified when downloading the wallet from OCI
>>>> Console or the Service Console.*
>>>> *#javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks*
>>>> *#javax.net.ssl.trustStorePassword=<password_from_console>*
>>>> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
>>>> *#javax.net.ssl.keyStorePassword=<password_from_console>hduser@rhes76:
>>>> /home/hduser/dba/bin/ADW/DBAccess>*
>>>>
>>>> Regards,
>>>>
>>>> Mich
>>>>
>>>> LinkedIn *
>>>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!LxAFleT1w3dN53Njh2o9xm_GtQd-d0NTouqw1mBYLroe4Byzc1nvSN0rb-cnpRttfw$>*
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>>> any loss, damage or destruction of data or any other property which may
>>>> arise from relying on this email's technical content is explicitly
>>>> disclaimed. The author will in no case be liable for any monetary damages
>>>> arising from such loss, damage or destruction.
>>>>
>>>>
>>>>
>>>>
>>>> On Wed, 26 Aug 2020 at 20:16, <kuassi.men...@oracle.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> From which release is the ojdbc8.jar from? 12c, 18c or 19c? I'd
>>>>> recommend ojdbc8.jar from the latest release.
>>>>> One more thing to pay attention to is the content of the
>>>>> ojdbc.properties file (part of the unzipped wallet)
>>>>> Make sure that ojdbc.properties file has been configured to use Oracle
>>>>> Wallet, as follows (i.e., anything related to JKS commented out)
>>>>>
>>>>>
>>>>> *oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))*
>>>>> *#javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks*
>>>>> *#javax.net.ssl.trustStorePassword=<password_from_console>*
>>>>> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
>>>>> *#javax.net.ssl.keyStorePassword=<password_from_console>*
>>>>>
>>>>> Alternatively, if you want to use JKS< then you need to comment out
>>>>> the firts line and un-comment the other lines and set the values.
>>>>>
>>>>> Kuassi
>>>>> On 8/26/20 11:58 AM, Mich Talebzadeh wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> The connection from Spark to Oracle 12c etc are well established using
>>>>> ojdb6.jar.
>>>>>
>>>>> I am attempting to connect to Oracle Autonomous Data warehouse (ADW)
>>>>> version
>>>>>
>>>>> *Oracle Database 19c Enterprise Edition Release 19.0.0.0.0*
>>>>>
>>>>> Oracle document suggest using ojdbc8.jar
>>>>> <https://urldefense.com/v3/__http://ojdbc8.jar__;!!GqivPVa7Brio!Msuw5mr2YjeHSLbBSlNvs8rqL7T_-eWFfdsamiYduARIsECZqEzUTG8hd-v1x8KwcQ$>
>>>>> to
>>>>> connect to the database with the following URL format using Oracle Wallet
>>>>>
>>>>> "jdbc:oracle:thin:@mydb_high
>>>>> ?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
>>>>>
>>>>> This works fine through JAVA itself but throws an error with
>>>>> Spark version 2.4.3.
>>>>>
>>>>> The connection string is defined as follows
>>>>>
>>>>> val url = "jdbc:oracle:thin:@mydb_high
>>>>> ?TNS_ADMIN=/home/hduser/dba/bin/ADW/DBAccess"
>>>>>
>>>>> where DBAcess directory is the unzipped wallet for Wallet_mydb.zip as
>>>>> created by ADW connection.
>>>>>
>>>>> The thing is that this works through normal connection via java
>>>>> code.using the same URL
>>>>>
>>>>> So the question is whether there is a dependency in Spark JDBC
>>>>> connection to the ojdbc.
>>>>>
>>>>> The error I am getting is:
>>>>>
>>>>> java.sql.SQLRecoverableException: IO Error: Invalid connection string
>>>>> format, a valid format is: "host:port:sid"
>>>>> at
>>>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
>>>>> at
>>>>> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
>>>>> at
>>>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
>>>>> at
>>>>> oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
>>>>> at
>>>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
>>>>> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>>>>>
>>>>> This Oracle doc
>>>>> <https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-jdbc-thin-wallet.html#GUID-5ED3C08C-1A84-4E5A-B07A-A5114951AA9E>
>>>>> explains the connectivity.
>>>>>
>>>>> The unzipped wallet has the followiing files
>>>>>
>>>>> ls DBAccess/
>>>>> README cwallet.sso ewallet.p12 keystore.jks ojdbc.properties
>>>>> sqlnet.ora tnsnames.ora truststore.jks
>>>>>
>>>>>
>>>>> Thanks
>>>>>
>>>>> Mich
>>>>>
>>>>>
>>>>>
>>>>> LinkedIn *
>>>>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>> <https://urldefense.com/v3/__https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw__;!!GqivPVa7Brio!Msuw5mr2YjeHSLbBSlNvs8rqL7T_-eWFfdsamiYduARIsECZqEzUTG8hd-teislmnw$>*
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>>>> any loss, damage or destruction of data or any other property which may
>>>>> arise from relying on this email's technical content is explicitly
>>>>> disclaimed. The author will in no case be liable for any monetary damages
>>>>> arising from such loss, damage or destruction.
>>>>>
>>>>>
>>>>>
>>>>>
