Not able to set System property in spark application [In client and cluster mode]

Question

To set System.setProperty("oracle.net.tns_admin",path_of_ora) I have tried for below approaches.

Approach 1: Used broadcast variable to copy tns.ora file to all executors sparkSession.sparkContext().broadcast("/tmp/conf/”,classTagTest)

Problem with this approach is "/tmp/conf/” is considered as string variable instead of path

Approach 2: Used --files parameter in spark submit command to pass tns.ora file and set --conf spark.executor.extraJavaOptions=-Doracle.net.tns_admin=tnsnames.ora

Approach 3: Used sparkSession.addFile(“tnsnames.ora”) set --conf spark.executor.extraJavaOptions=-Doracle.net.tns_admin=tnsnames.ora

According to our analysis we found that system property is set when spark application is run with master as local[*] and mode client.

Even in client mode only local path is recognized and when we tried setting systemProperty with hdfs path it is not recognized.

But the requirement is to set the system property and run in cluster mode.

spark-submit --class Demo1 --master local[*] --deploy-mode client --driver-memory 5g --executor-memory 10g --executor-cores 5 --num-executors 36 --jars ojdbc15-11.1.0.6.jar --files /tmp/tnsnames.ora --conf spark.executor.extraJavaOptions=-Doracle.net.tns_admin=tnsnames.ora --driver-java-options -Doracle.net.tns_admin=tnsnames.ora test.jar tnsnames.ora

SparkConf sparkConf = new SparkConf()
               // .setMaster("local[*]")
                .setAppName("Iden");

SparkSession sparkSession = SparkSession.builder()
        .config(sparkConf).getOrCreate();

String username = "etldevapp";
String password = "etldevappstg";
String thinConn = "jdbc:oracle:thin:" + username + "/" + password + "@xxxxxxx";

System.setProperty("oracle.net.tns_admin","/tmp/");

try {
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(thinConn, username, password);

    PreparedStatement st = conn.prepareStatement("select * from xxxx where rownum < 10");

    final ResultSet rs = st.executeQuery();
    System.out.println(rs.getFetchSize());
} catch (Exception e) {
    System.out.println("Exception from try1 ");
}

User class threw exception: java.sql.SQLException: Io exception: could not resolve the connect identifier "xxxxx"

Mike Pone Mike Pone · Accepted Answer · 2019-08-21T15:36:27

Here's what I've done. I set it as a property in the Spark config file and then in code set it as a system property. It's important that the property name start with "spark.", So something like this:

System.setProperty("oracle.net.tns_admin",conf.get("spark.your.property.name"));

Not able to set System property in spark application [In client and cluster mode]

1 Answers