Query PostgreSQL with Azure Databricks
This example queries PostgreSQL using its JDBC driver. For more details on reading, writing, configuring parallelism, and query pushdown, see Query databases using JDBC.
Note
You may prefer Lakehouse Federation for managing queries to PostgreSQL. See What is Lakehouse Federation.
Using JDBC
Python
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()
)
Scala
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.3 LTS and above, you can use the named connector to query PosgresQL. See the following examples:
Python
remote_table = (spark.read
.format("postgresql")
.option("dbtable", "schema_name.table_name") # if schema_name not provided, default to "public".
.option("host", "database_hostname")
.option("port", "5432") # Optional - will use default port 5432 if not specified.
.option("database", "database_name")
.option("user", "username")
.option("password", "password")
.load()
)
SQL
DROP TABLE IF EXISTS postgresql_table;
CREATE TABLE postgresql_table
USING postgresql
OPTIONS (
dbtable '<schema-name>.<table-name>' /* if schema_name not provided, default to "public". */,
host '<database-host-url>',
port '5432', /* Optional - will use default port 5432 if not specified. */
database '<database-name>',
user '<username>',
password '<password>'
);
Scala
val remote_table = spark.read
.format("postgresql")
.option("dbtable", "schema_name.table_name") # if schema_name not provided, default to "public".
.option("host", "database_hostname")
.option("port", "5432") # Optional - will use default port 5432 if not specified.
.option("database", "database_name")
.option("user", "username")
.option("password", "password")
.load()
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for