How to run stored procedure in Azure Database for PostgreSQL using Azure Databricks Notebook

Anuj, Singh (Cognizant) 50 Reputation points
2024-05-22T04:46:02.37+00:00

We have Stored Procedure available in Azure Database for PostgreSQL and we want to call or run or execute the postgreSQL stored procedures in Azure Databricks through Notebook

We are attempting to run PostgreSQL stored procedures, through Azure Databricks notebook and would need help how to execute a stored procedure. I would need clarity in below points

  1. What are the required libraries that needs to be installed in Databricks cluster (if any)?
  2. How to connect with Azure Database for PostgreSQL using ODBC?
  3. How to Execute PostgreSQL stored procedure in Databricks?

I am referring to below link,

https://nachiketrajput44.medium.com/how-to-run-stored-procedure-in-azure-data-warehouse-using-databricks-caa912d123d5

Let me know if anyone has step-by-step approach how to solve this issue.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,630 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,047 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 84,531 Reputation points Microsoft Employee
    2024-05-22T11:25:18.69+00:00

    @Anuj, Singh (Cognizant) - Thank for the question and using MS Q&A platform.To connect to Azure Database for PostgreSQL using Azure Databricks you can use JDBC driver and PostgreSQL connector without installing any libraries on the Azure Databricks clusters.

    For more details, refer to Query PostgreSQL with Azure Databricks.

    The sample queries to connect to Azure Database for PostgreSQL using Azure Databricks using JDBC driver:

    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()
)

    Here is the screenshot of the successful connection:
    User's image

    The sample queries to connect to Azure Database for PostgreSQL using Azure Databricks using PostgreSQL connector:

    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()
)

    Here is the screenshot of the successful connection:

    User's image

    How to run stored procedure in Azure Database for PostgreSQL using Azure Databricks Notebook?
    Once you are able to successfully be connected to Azure Database for PostgreSQL using Azure Databricks you can simply run the stored procedure without any issue.User's image

    For more details, refer to Query databases using JDBC.

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.