CODEWITHSUNDEEP
oracle-databasedatabricks
Raju
Raju

Reputation: 185

Connectivity to Oracle from Databricks

I am trying to connect to an Oracle DB from Databricks. However I can not find the exact syntax in any documentation.

Could any one help with exact syntax? Or step by step process for connection establishment?

This is my attempt so far:

dbutils.widgets.text("sql_instance_name", "serveraddress")
jdbcHostname = getArgument("sql_instance_name")
jdbcPort = 1521
jdbcDatabase = "secrete"
username = dbutils.secrets.get(scope = "masked", key = "username")
password = dbutils.secrets.get(scope = "masked", key = "password")
jdbcUrl = "jdbc:oracle:thin:username/password@jdbcHostname:1521:jdbcDatabase"
connectionProperties = {
  "user" : username,
  "password" : password,
  "driver" : "oracle.jdbc.driver.OracleDriver"
}

Upvotes: 1

Views: 18583

Answers (1)

CHEEKATLAPRADEEP
CHEEKATLAPRADEEP

Reputation: 12778

The below code snippet shows you how to read in data from an Oracle Database. This method requires that you have the correct driver installed on the cluster.

%sql

CREATE TABLE oracle_table
USING org.apache.spark.sql.jdbc
OPTIONS (
  dbtable 'table_name',
  driver 'oracle.jdbc.driver.OracleDriver',
  user 'username',
  password 'pasword',
  url 'jdbc:oracle:thin://@<hostname>:1521/<db>')

Python code to read the data from the database.

empDF = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:oracle:thin:username/password@//hostname:portnumber/SID") \
    .option("dbtable", "hr.emp") \
    .option("user", "db_user_name") \
    .option("password", "password") \
    .option("driver", "oracle.jdbc.driver.OracleDriver") \
    .load()

For more details, refer "Azure Databricks- Data Sources".

Hope this helps.

Upvotes: 2

Related Questions