Spark Oracle Datasource Examples

Examples of using Spark Oracle Datasource with Data Flow.

Here are examples each for Java, Python, Scala, and SQL, they use an Oracle library:

For complete working examples, see Oracle Data Flow Samples on GitHub.

Java Examples

Example code for Spark Oracle Datasource with Java.

Loading data from an autonomous database at the root compartment:

// Loading data from autonomous database at root compartment.
// Note you don't have to provide driver class name and jdbc url.
Dataset<Row> oracleDF = spark.read()
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load();

Loading data from an autonomous database and overriding the net service name:

// Loading data from autonomous database overriding net service name.
// Note using map for configuration/options.Map can be mutable or immutable.
Map<String, String> options = new HashMap<String, String>();
        options.put("adbId", "ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>");
        options.put("connectionId", "database_high");
        options.put("user", "username");
        options.put("password", "password");
        options.put("dbtable", "schema.tablename");
 
Dataset<Row> oracleDF1 = spark.read()
  .format("oracle")
  .options(options)
  .load()

Loading data from an Oracle database with a wallet from Object Storage:

// Loading data from oracle database with wallet from oci object storage.
Dataset<Row> oracleDF2 = spark.read()
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip")
  .option("connectionId","database_medium")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()

Loading data from an Oracle database using a wallet from Object Storage and auto-login enabled in wallet. No user name or password are required:

// Loading data from oracle database with wallet from oci object storage and auto-login enabled in wallet, no username and password required.
Dataset<Row> oracleDF2 = spark.read()
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip")
  .option("connectionId","database_medium")
  .option("dbtable", "schema.tablename")
  .load()

Saving data to an autonomous database at the root compartment:

// Saving data to autonomous database at root compartment.
oracleDF.write
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

Saving data to an autonomous database at the root compartment, and overriding the net service name:

// Saving data to autonomous database at root compartment overriding net service name.
oracleDF.write
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("connectionId","database_low)
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

Saving data to an Oracle database with a wallet from Object Storage:

// Saving data to oracle database with wallet from object storage.
oracleDF.write
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip"
  .option("connectionId","database_low)
  .option("user", "username")
  .option("password", "password")
  .save()

Python Examples

Example code for Spark Oracle Datasource with Python.

Loading data from an autonomous database at the root compartment:

// Loading data from autonomous database at root compartment.
// Note you don't have to provide driver class name and jdbc url.
oracle_df = spark.read \
    .format("oracle") \
    .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .load()

Loading data from an autonomous database and overriding the net service name:

// Loading data from autonomous database overriding net service name.
// Note using map for configuration/options.Map can be mutable or immutable.
properties = {"adbId":"ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>","dbtable":"schema.tablename","connectionId":"database_high","user":"username","password":"password"}
oracle_df1 = spark.read \
    .format("oracle") \
    .options(**properties) \
    .load()

Loading data from an Oracle database with a wallet from Object Storage:

// Loading data from oracle database with wallet from oci object storage.
oracle_df2 = spark.read \
    .format("oracle") \
    .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip") \
    .option("connectionId","database_medium") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .load()

Loading data from an Oracle database using a wallet from Object Storage and auto-login enabled in wallet. No user name or password are required:

// Loading data from oracle database with wallet from oci object storage and auto-login enabled in wallet, no username and password required.
oracle_df2 = spark.read \
    .format("oracle") \
    .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip") \
    .option("connectionId","database_medium") \
    .option("dbtable", "schema.tablename") \
    .load()

Saving data to an autonomous database at the root compartment:

// Saving data to autonomous database at root compartment.
oracle_df.write \
    .format("oracle") \
    .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .save()

Saving data to an autonomous database at the root compartment, and overriding the net service name:

// Saving data to autonomous database at root compartment overriding net service name.
oracle_df.write \
    .format("oracle") \
    .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>") \
    .option("connectionId","database_low) \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .save()

Saving data to an Oracle database with a wallet from Object Storage:

// Saving data to oracle database with wallet from object storage.
oracle_df.write \
    .format("oracle") \
    .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip") \
    .option("connectionId","database_medium") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .save()

Scala Examples

Example code for Spark Oracle Datasource with Scala.

Loading data from an autonomous database at the root compartment:

// Loading data from autonomous database at root compartment.
// Note you don't have to provide driver class name and jdbc url.
val oracleDF = spark.read
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()

Loading data from an autonomous database and overriding the net service name:

// Loading data from autonomous database overriding net service name.
// Note using map for configuration/options.Map can be mutable or immutable.
var options = Map[String, String]()
options += ("adbId" -> "ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>", "connectionId" -> "database_high", "dbtable" -> "schema.tablename", "user"-> "username", "password" ->"password")
 
val oracleDF1 = spark.read
  .format("oracle")
  .options(options)
  .load()

Loading data from an Oracle database with a wallet from Object Storage:

// Loading data from oracle database with wallet from oci object storage.
val oracleDF2 = spark.read
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip")
  .option("connectionId","database_medium")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()

Loading data from an Oracle database using a wallet from Object Storage and auto-login enabled in wallet. No user name or password are required:

// Loading data from oracle database with wallet from oci object storage and auto-login enabled in wallet, no username and password required.
val oracleDF2 = spark.read
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip")
  .option("connectionId","database_medium")
  .option("dbtable", "schema.tablename")
  .load()

Saving data to an autonomous database at the root compartment:

// Saving data to autonomous database at root compartment.
oracleDF.write
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

Saving data to an autonomous database at the root compartment, and overriding the net service name:

// Saving data to autonomous database at root compartment overriding net service name.
oracleDF.write
  .format("oracle")
  .option("adbId","ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>")
  .option("connectionId","database_low)
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

Saving data to an Oracle database with a wallet from Object Storage:

// Saving data to oracle database with wallet from object storage.
oracleDF.write
  .format("oracle")
  .option("walletUri","oci://<bucket>@<namespace>/Wallet_DATABASE.zip"
  .option("connectionId","database_low)
  .option("user", "username")
  .option("password", "password")
  .save()

SQL Examples

Example code for Spark Oracle Datasource with SQL.

Loading data from an autonomous database at the root compartment:

-- Loading data from autonomous database at root compartment.
 
CREATE TEMPORARY VIEW oracle_db
USING oracle
OPTIONS (
adbId "ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>",
dbtable "schema.tablename",
user "uname",
password "pwd"
);
 
SELECT count(*) FROM oracle_db;
DROP TABLE oracle_db;

Loading data from an autonomous database and overriding the net service name:

-- Loading data from autonomous database overriding net service name.
 
CREATE TEMPORARY VIEW oracle_db1
USING oracle
OPTIONS (
adbId "ocid1.autonomousdatabase.<REALM>.[REGION][.FUTURE USE].<UNIQUE ID>",
connectionId "database_high",
dbtable "schema.tablename",
user "uname",
password "pwd"
);
 
SELECT count(*) FROM oracle_db1;
DROP TABLE oracle_db1;

Loading data from an Oracle database with a wallet from Object Storage:

-- Loading data from oracle database with wallet from object storage.
 
CREATE TEMPORARY VIEW oracle_db1
USING oracle
OPTIONS (
walletUri "oci://<bucket>@<namespace>/Wallet_DATABASE.zip",
connectionId "database_high",
dbtable "schema.tablename",
user "uname",
password "pwd"
);
 
SELECT count(*) FROM oracle_db1;
DROP TABLE oracle_db1;

Loading data from an Oracle database using a wallet from Object Storage and auto-login enabled in wallet. No user name or password are required:

-- Loading data from oracle database with wallet from oci object storage and auto-login enabled in wallet, no username and password required.
CREATE TEMPORARY VIEW oracle_db2
USING oracle
OPTIONS (
walletUri "oci://<bucket>@<namespace>/Wallet_DATABASE.zip",
connectionId "database_high",
dbtable "schema.tablename"
);
 
SELECT count(*) FROM oracle_db2;
DROP TABLE oracle_db2;

Oracle Cloud Infrastructure Documentation

Spark Oracle Datasource Examples

Java Examples

Python Examples

Scala Examples

SQL Examples