Oracle Database JDBC driver cannot read wallet file from Spark

Question

Objective

I'm trying to write to Oracle's ADWC (basically oracle database) from a Spark application running on Yarn. The only way to connect to this database is by using an Oracle Wallet file, which is basically a Java keystore.

Problem

The problem arises when the JDBC driver tries to read the wallet from HDFS. If I include the hdfs:// prefix the parser in the JDBC driver throws an error and if I don't then it cannot find the file.

Previous Attempts

including the directory in the connect string (prefixed and non) jdbc:oracle:thin:@luigi_low?TNS_ADMIN=/user/spark/wallet_LUIGI
including the directory as an spark.driver.extraJavaOptions with -Doracle.net.tns_admin and -Doracle.net.wallet_location

All the code is on GitHub, and specifically, the error messages are here https://github.com/sblack4/kafka-scala-jdbc/blob/master/ERROR.md

I've got a working example of the same connection here https://github.com/sblack4/scala-jdbc-adwc

help me StackOverflow. you are my only hope

If you need any more clarification don't hesitate :)

update (`SparkFiles` attempt)

the code is on a separate branch of the same repository, https://github.com/sblack4/kafka-scala-jdbc/tree/sparkfiles

This error message mystifies me as it seems my JDBC library has stopped trying to read the wallet files. It may be unrelated to the previous problem

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"

I've deleted the other JDBC libraries from my classpath through Ambari as this error could be related to spark picking up an older version of my JDBC library

Have you gone through docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/… ? — thatjeffsmith
In a nutshell: the code inside the JDBC driver has no concept of HDFS, it expects a plain file in a plain local filesystem (or maybe a InputStream?) So, why don't you use Spark to download the wallet to a local (temp) file (or maybe open a stream on HDFS ?) and pass that to the driver?? — Samson Scharfrichter
@SamsonScharfrichter I'll try the InputStream idea but am not sure how I'd pursue downloading the driver locally. That sounds fairly simple if I can then pass that driver-local path to the JDBC in the connection string — Steven Black
Do you open JDBC connections in the Spark driver and executors (i.e. to read/write Oracle data to/from a DataFrame in parallel) with multiple "independant" sessions, or in just in the driver (w/ custom code to run commands not supported by Spark JDBC interface)? Because you can tell the driver to distribute files to its executors, but then the local path will be different and the JDBC URL would also be different... — Samson Scharfrichter

Kris Rice Kris Rice · Accepted Answer · 2018-08-27T13:00:30

Here's some code that will help diagnose what the issues is. It checks and configures everything required to connect.

JDBC Driver version
JCE Installed
Classpath dependencies

Configures

tns_admin
ssl settings
trust/key stores

This is a slimmed down version of what's in sqldev/sqlcl

import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Properties;

import javax.crypto.Cipher;

import oracle.jdbc.OracleConnection;

public class JDBCTest {

  public static void fail(String msg){
    System.err.println(String.join("", Collections.nCopies(20, "*")));
    System.err.println(msg);
    System.err.println(String.join("", Collections.nCopies(20, "*")));
    System.exit(1);
  }
  public static void main(String[] args) throws SQLException {
    System.out.println("JDBC Driver Version:" + oracle.jdbc.OracleDriver.getDriverVersion());

    // Check JDBC Driver Version
    if (!oracle.jdbc.OracleDriver.getDriverVersion().startsWith("18.")) {
      fail(" DRIVER TOOO OLD!!!");
    }

    // Check JCE Installed
    int maxKeySize = 0;
    try {
      maxKeySize = Cipher.getMaxAllowedKeyLength("AES");
    } catch (NoSuchAlgorithmException e) {
    }
    if (maxKeySize < 129 ) {
      fail(" JCE Policy not unlimited!!!");      
    }

    // Check Classpath

    String cp = System.getProperty("java.class.path");

    String[] cpFiles = {"ojdbc8.jar","oraclepki.jar","osdt_cert.jar","osdt_core.jar"};

    for (String file:cpFiles){
      if ( cp.indexOf(file) == -1 ){
        fail("CLASSPATH Missing:" + file);

      }
    }
    // Wallet unziped location
    String unzippedWalletLocation = "/Users/klrice/workspace/12.2JDBC/wallet";



    String conString = "jdbc:oracle:thin:@sqldev_medium";


    Properties props = new Properties();
    props.setProperty("oracle.net.wallet_location",unzippedWalletLocation);

    props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT, "2000");

    // unzipped includes a tnsnames.ora
    props.setProperty("oracle.net.tns_admin",unzippedWalletLocation);
    props.setProperty("javax.net.ssl.trustStore","truststore.jks");
    props.setProperty("javax.net.ssl.trustStorePassword","<password>");
    props.setProperty("javax.net.ssl.keyStore","keystore.jks");
    props.setProperty("javax.net.ssl.keyStorePassword","<password>");
    props.setProperty("oracle.net.ssl_server_dn_match","true");    
    props.setProperty("oracle.net.ssl_version","1.2");


    props.setProperty("user", "ADMIN");
    props.setProperty("password", "<password>");

    try { 
      // now Connect 
      Connection conn = DriverManager.getConnection(conString,props);
    } catch (Exception e){
      e.printStackTrace();
      fail(e.getLocalizedMessage());
    }

    System.out.println("SUCCESS!!");


  }

}