Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

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_.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 = "x"
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

"

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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,  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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[kuassi . mensah]

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw 
/




*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, > 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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

/



*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
mailto: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 = "xx" -- no special characters
_password: String = xxx
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.(PhysicalConnection.java:553)
      at

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

Thanks Kuassi,

I presume you mean Spark DEV team by "they are using ... "

cheers,

Mich



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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,  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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
>
>
> *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 
> 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 = "xx"  -- no special characters
>> _password: String = xxx
>> 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.(PhysicalConnection.java:553)
>> at oracle.jdbc.driver.T4CConnection.(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 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[kuassi . mensah]

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw 
/




*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 
mailto: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 = "xx" -- no special characters
_password: String = xxx
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.(PhysicalConnection.java:553)
        at
oracle.jdbc.driver.T4CConnection.(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, mailto: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 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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 
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 = "xx"  -- no special characters
> _password: String = xxx
> 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.(PhysicalConnection.java:553)
> at oracle.jdbc.driver.T4CConnection.(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,  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.*
>>

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

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 = "xx"  -- no special characters
_password: String = xxx
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.(PhysicalConnection.java:553)
at oracle.jdbc.driver.T4CConnection.(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,  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=*
> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
> *#javax.net.ssl.keyStorePassword=hduser@rhes76:
> /home/hduser/dba/bin/ADW/DBAccess>*
>
> Regards,
>
> Mich
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
>
>
> *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,  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 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[kuassi . mensah]

Fwiw here are our write-ups on Java connectivity to Database Cloud 
Services: 
https://www.oracle.com/database/technologies/appdev/jdbc-db-cloud.html


Kuassi

On 8/26/20 1:50 PM, Mich Talebzadeh wrote:

Thanks Jorn,

Only running in REPL in local mode

This works fine connecting with ojdbc6.jar to Oracle 12c.

cheers



LinkedIn 
/https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw 
/




*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:39, Jörn Franke > wrote:


Is the directory available on all nodes ?


Am 26.08.2020 um 22:08 schrieb kuassi.men...@oracle.com
:



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=*
*#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
*#javax.net.ssl.keyStorePassword=hduser@rhes76:
/home/hduser/dba/bin/ADW/DBAccess>*

Regards,

Mich

LinkedIn

/https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

/



*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, mailto: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=//
//#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks//
//#javax.net.ssl.keyStorePassword=/

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 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

Thanks Jorn,

Only running in REPL in local mode

This works fine connecting with ojdbc6.jar to Oracle 12c.

cheers



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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:39, Jörn Franke  wrote:

> Is the directory available on all nodes ?
>
> Am 26.08.2020 um 22:08 schrieb kuassi.men...@oracle.com:
>
> 
>
> 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=*
> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
> *#javax.net.ssl.keyStorePassword=hduser@rhes76:
> /home/hduser/dba/bin/ADW/DBAccess>*
>
> Regards,
>
> Mich
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
>
>
> *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,  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=*
>> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
>> *#javax.net.ssl.keyStorePassword=*
>>
>> 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
>> 
>>  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"
>>
>> 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Jörn Franke]

Is the directory available on all nodes ?

> Am 26.08.2020 um 22:08 schrieb kuassi.men...@oracle.com:
> 
> 
> 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
>> 
>> echo ${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=
>> #javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
>> #javax.net.ssl.keyStorePassword=hduser@rhes76: 
>> /home/hduser/dba/bin/ADW/DBAccess>
>> 
>> Regards,
>> 
>> Mich
>> 
>> LinkedIn  
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>  
>> 
>> 
>> 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,  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=
>>> #javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
>>> #javax.net.ssl.keyStorePassword=
>>> 
>>> 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 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.(PhysicalConnection.java:553)
 at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:254)
 at 
 oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
 at 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[kuassi . mensah]

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=*
*#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
*#javax.net.ssl.keyStorePassword=hduser@rhes76: 
/home/hduser/dba/bin/ADW/DBAccess>*


Regards,

Mich

LinkedIn 
/https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw 
/




*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, > 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=//
//#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks//
//#javax.net.ssl.keyStorePassword=/

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


 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.(PhysicalConnection.java:553)
        at
oracle.jdbc.driver.T4CConnection.(T4CConnection.java:254)
        at

oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

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=*
*#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
*#javax.net.ssl.keyStorePassword=hduser@rhes76:
/home/hduser/dba/bin/ADW/DBAccess>*

Regards,

Mich

LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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,  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=*
> *#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks*
> *#javax.net.ssl.keyStorePassword=*
>
> 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
> 
>  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.(PhysicalConnection.java:553)
> at oracle.jdbc.driver.T4CConnection.(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
> 
> 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
>
> 

Re: Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[kuassi . mensah]

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=//
//#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks//
//#javax.net.ssl.keyStorePassword=/

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 
 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.(PhysicalConnection.java:553)

        at oracle.jdbc.driver.T4CConnection.(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 
 
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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw 
/




*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.

Connecting to Oracle Autonomous Data warehouse (ADW) from Spark via JDBC

[Mich Talebzadeh]

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 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.(PhysicalConnection.java:553)
at oracle.jdbc.driver.T4CConnection.(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

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=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*





*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.