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