Query PostgreSQL with Databricks

This example queries MySQL using its JDBC driver. For more details on reading, writing, configuring parallelism, and query pushdown, see Query databases using JDBC.

Using JDBC

driver = "org.postgresql.Driver"

database_host = "<database-host-url>"
database_port = "5432" # update if you use a non-default port
database_name = "<database-name>"
table = "<table-name>"
user = "<username>"
password = "<password>"

url = f"jdbc:postgresql://{database_host}:{database_port}/{database_name}"

remote_table = (spark.read
  .format("jdbc")
  .option("driver", driver)
  .option("url", url)
  .option("dbtable", table)
  .option("user", user)
  .option("password", password)
  .load()
)

val driver = "org.postgresql.Driver"

val database_host = "<database-host-url>"
val database_port = "5432" # update if you use a non-default port
val database_name = "<database-name>"
val table = "<table-name>"
val user = "<username>"
val password = "<password>"

val url = s"jdbc:postgresql://${database_host}:${database_port}/${database_name}"

val remote_table = spark.read
  .format("jdbc")
  .option("driver", driver)
  .option("url", url)
  .option("dbtable", table)
  .option("user", user)
  .option("password", password)
  .load()

Using the PostgreSQL connector in Databricks Runtime

In Databricks Runtime 11.2 and above, you can use the named connector to query PosgresQL. See the following examples:

remote_table = (spark.read
  .format("postgresql")
  .option("dbtable", table_name)
  .option("host", database_host_url)
  .option("port", 5432)
  .option("database", database_name)
  .option("user", username)
  .option("password", password)
  .load()
)

DROP TABLE IF EXISTS postgresql_table;
CREATE TABLE postgresql_table
USING postgresql
OPTIONS (
  dbtable '<table-name>',
  host '<database-host-url>',
  port '5432',
  database '<database-name>',
  user '<username>',
  password '<password>'
);

val remote_table = spark.read
  .format("postgresql")
  .option("dbtable", table_name)
  .option("host", database_host_url)
  .option("port", 5432)
  .option("database", database_name)
  .option("user", username)
  .option("password", password)
  .load()