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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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 [hidden email]:



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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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=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 Wed, 26 Aug 2020 at 21:39, Jörn Franke <[hidden email]> wrote:
Is the directory available on all nodes ?

Am 26.08.2020 um 22:08 schrieb [hidden email]:



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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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=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 Wed, 26 Aug 2020 at 21:39, Jörn Franke <[hidden email]> wrote:
Is the directory available on all nodes ?

Am 26.08.2020 um 22:08 schrieb [hidden email]:



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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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

Mich Talebzadeh
In reply to this post by kuassi.mensah
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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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=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 Wed, 26 Aug 2020 at 21:58, Mich Talebzadeh <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 



---------------------------------------------------------------------
To unsubscribe e-mail: [hidden email]

DataSourceSample.java (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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=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 Wed, 26 Aug 2020 at 21:58, Mich Talebzadeh <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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=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:11, <[hidden email]> 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

 



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 <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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=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:11, <[hidden email]> 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

 



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 <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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_<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"

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 /jars
3) Upload to HDFS: 
   hdfs dfs -put spark-libs.jar /jars
4) 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

 



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, <[hidden email]> 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

 



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, <[hidden email]> 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

 



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 <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.

 

Reply | Threaded
Open this post in threaded view
|

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

kuassi.mensah

Awesome!

Thank you for sorting this out and sharing the troubleshooting tips.

Best, Kuassi

On 8/28/20 9:16 AM, Mich Talebzadeh wrote:
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"

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 /jars
3) Upload to HDFS: 
   hdfs dfs -put spark-libs.jar /jars
4) 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

 



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, <[hidden email]> 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

 



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, <[hidden email]> 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

 



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 <[hidden email]> 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, <[hidden email]> 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

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

 



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, <[hidden email]> 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 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 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

 



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.