Spark Streaming Oracle JDBC sink with wallet

Question

I am developing a Spark Streaming application which would listen to a folder (partitioned as yyyyMMdd) and aggregate the number of records written per minutes then persist the results to an Oracle table.

I have developed a JDBCSink (ForeachWriter) and in the open method I'm trying to open a connection to Oracle but I am getting "oracle.net.ns.NetException: could not resolve the connect identifier" exception while creating the Oracle connection. I am using Oracle wallet (SSO) and I'm able to connect over sqlplus using this wallet by setting TNS_ADMIN environment variable.

I am pushing the tnsnames.ora, sqlnet.ora, cwallet.sso and ewallet.p12 with the spark-submit --files option, and I have verified the files are pushed to the executors with the SparkFiles.get method in the sink class. I have also added third party Oracle dependencies for Oracle wallet with spark-submit --jars option (namely ojdbc7.jar,oraclepki.jar,osdt_cert.jar,osdt_core.jar)

The code piece for opening the connection is as follows:

Class.forName("oracle.jdbc.driver.OracleDriver")
  
System.setProperty("oracle.net.tns_admin", new Path(SparkFiles.get("tnsnames.ora")).getParent.getName)
val ds = new OracleDataSource()
val props = new Properties()
props.setProperty(OracleConnection.CONNECTION_PROPERTY_WALLET_LOCATION, 
      new Path(SparkFiles.get("cwallet.sso")).getParent.getName)
ds.setConnectionProperties(props)
ds.setURL("jdbc:oracle:thin:@xe")

I have tried to isolate the problem The Oracle version is 12.1.0.2 (I am using a Docker image)

spark-submit2 ^
    --master local ^
    --files "%CWD%\wlt\tnsnames.ora,%CWD%\wlt\sqlnet.ora,%CWD%\wlt\cwallet.sso,%CWD%\wlt\ewallet.p12" ^
    --jars "%CWD%\lib\ojdbc7.jar,%CWD%\lib\oraclepki.jar,%CWD%\lib\osdt_cert.jar,%CWD%\lib\osdt_core.jar" ^
    --class OJDBCSinkMain ^
    .\target\spark-streaming-ojdbc-sink-1.0-SNAPSHOT-jar-with-dependencies.jar 

My sqlnet.ora file is as follows:

NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE=TRUE
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_VERSION=0

and my tnsnames.ora file is:

xe =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

I also verified the credentials for Oracle service xe exists in my wallet:

comment mkstore -wrl . -listCredential

List credential (index: connect_string username)
1: xe system

Do you have any comments? Thanks in advance.

Answer 1

My mistake, I needed a rubber dock to spot!!! SparkFiles.get("tnsnames.ora")).getParent.getName returns relative path, not the absolute. My problem is solved now.