Do you know how to install the 'ODBC Driver 17 for PostgreSQL' on a Azure Databricks cluster?

Question

Do you know how to install the 'ODBC Driver 17 for PostgreSQL' on a Azure Databricks cluster?

AnujSinghCognizant-4026 90

I am attempting to run postgreSQL stored procedures , through Azure Databricks notebook.

We have stored procedure written in Azure Database for PostgreSQL and we want to run postgreSQL stored procedures through Azure Databricks Notebook (using Pyspark/SQL).

Step 1: Install required libraries.

Step 2: Connect with Azure Database for PostgreSQL using ODBC.

Step 3: Execute PostgreSQL stored procedure in Databricks.

I am referring to below link,

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

Sina Salam 29,016 Volunteer Moderator

Hi

Thank you for your post, it requires clarification in assumptions that your referred link is not detail enough for the listed steps or would prefer a broad steps:

Code Detail structure.

# Import necessary libraries
from pyspark.sql import SparkSession
# Create SparkSession
spark = SparkSession.builder \
    .appName("PostgreSQL Stored Procedure Example") \
    .getOrCreate()
# Define PostgreSQL connection properties
url = "jdbc:postgresql://your_postgresql_host:5432/your_database"
properties = {"user": "your_username", "password": "your_password", "driver": "org.postgresql.Driver"}
# Load data from PostgreSQL into a DataFrame
df = spark.read.jdbc(url=url, table="your_table", properties=properties)
# Register DataFrame as a temporary view
df.createOrReplaceTempView("your_temp_view")
# Execute stored procedure using SQL
spark.sql("CALL your_stored_procedure(arguments)")
# Alternatively, if you have specific JDBC code to execute stored procedure, you can use it here.

Based on the 3 steps you requested.

#Install Required Libraries

%pip install pyodbc

#
import pyodbc
# Connection details
server = 'your_postgresql_server'
database = 'your_database'
username = 'your_username'
password = 'your_password'
port = '5432'  # Default PostgreSQL port
# Create connection string
conn_str = f'DRIVER=ODBC Driver 17 for PostgreSQL;SERVER={server};DATABASE={database};UID={username};PWD={password};PORT={port}'
# Establish connection
conn = pyodbc.connect(conn_str)

# 
cursor = conn.cursor()
cursor.execute("EXEC your_stored_procedure(arguments)")
conn.commit()  # Commit the transaction

You can review the two code and let's know the one that's helpful for more insights if there is a need.

Regards,

PRADEEPCHEEKATLA 91,861 Reputation points

2024-05-02T03:26:17.9866667+00:00

@Anuj, Singh (Cognizant) - Just checking in to see if the below answer helped. 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.
AnujSinghCognizant-4026 90 Reputation points

2024-05-21T17:01:11.24+00:00

We are working on it and will update you. But could you please clarify why do I need to create a spark session as below. As per my understanding when we create a new notebook we have a new Spark Session

# Create SparkSession spark = SparkSession.builder \ .appName("PostgreSQL Stored Procedure Example") \ .getOrCreate()

Also Can you please let me know why you are using this step as under in the solution

# Load data from PostgreSQL into a DataFrame df = spark.read.jdbc(url=url, table="your_table", properties=properties)

I have simple requirement - We have stored procedure available in Azure Database for PostgreSQL and we want to call or run or execute the postgreSQL stored procedures through Azure Databricks Notebook
PRADEEPCHEEKATLA 91,861 Reputation points

2024-05-22T04:34:43.6066667+00:00

@Anuj, Singh (Cognizant) - The scope of your question is different compared to the original scope of the question asked.

I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.
AnujSinghCognizant-4026 90 Reputation points

2024-05-22T04:46:59.1066667+00:00

Thanks for the update . I have created a new thread on the same forum with as much details about the issue as possible.

https://learn.microsoft.com/en-us/answers/questions/1684756/how-to-run-stored-procedure-in-azure-database-for

Answer accepted by question author

0 additional answers

Your answer

Sina Salam 29,016 Reputation points Volunteer Moderator

2024-04-30T13:09:17.9933333+00:00

Hi

Thank you for your post, it requires clarification in assumptions that your referred link is not detail enough for the listed steps or would prefer a broad steps:

Code Detail structure.

# Import necessary libraries from pyspark.sql import SparkSession # Create SparkSession spark = SparkSession.builder \ .appName("PostgreSQL Stored Procedure Example") \ .getOrCreate() # Define PostgreSQL connection properties url = "jdbc:postgresql://your_postgresql_host:5432/your_database" properties = {"user": "your_username", "password": "your_password", "driver": "org.postgresql.Driver"} # Load data from PostgreSQL into a DataFrame df = spark.read.jdbc(url=url, table="your_table", properties=properties) # Register DataFrame as a temporary view df.createOrReplaceTempView("your_temp_view") # Execute stored procedure using SQL spark.sql("CALL your_stored_procedure(arguments)") # Alternatively, if you have specific JDBC code to execute stored procedure, you can use it here.

Based on the 3 steps you requested.

#Install Required Libraries %pip install pyodbc

# import pyodbc # Connection details server = 'your_postgresql_server' database = 'your_database' username = 'your_username' password = 'your_password' port = '5432' # Default PostgreSQL port # Create connection string conn_str = f'DRIVER=ODBC Driver 17 for PostgreSQL;SERVER={server};DATABASE={database};UID={username};PWD={password};PORT={port}' # Establish connection conn = pyodbc.connect(conn_str)

# cursor = conn.cursor() cursor.execute("EXEC your_stored_procedure(arguments)") conn.commit() # Commit the transaction

You can review the two code and let's know the one that's helpful for more insights if there is a need.

Regards,
PRADEEPCHEEKATLA 91,861 Reputation points

2024-05-02T03:26:17.9866667+00:00

@Anuj, Singh (Cognizant) - Just checking in to see if the below answer helped. 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.
AnujSinghCognizant-4026 90 Reputation points

2024-05-21T17:01:11.24+00:00

We are working on it and will update you. But could you please clarify why do I need to create a spark session as below. As per my understanding when we create a new notebook we have a new Spark Session

# Create SparkSession spark = SparkSession.builder \ .appName("PostgreSQL Stored Procedure Example") \ .getOrCreate()

Also Can you please let me know why you are using this step as under in the solution

# Load data from PostgreSQL into a DataFrame df = spark.read.jdbc(url=url, table="your_table", properties=properties)

I have simple requirement - We have stored procedure available in Azure Database for PostgreSQL and we want to call or run or execute the postgreSQL stored procedures through Azure Databricks Notebook
PRADEEPCHEEKATLA 91,861 Reputation points

2024-05-22T04:34:43.6066667+00:00

@Anuj, Singh (Cognizant) - The scope of your question is different compared to the original scope of the question asked.

I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.
AnujSinghCognizant-4026 90 Reputation points

2024-05-22T04:46:59.1066667+00:00

Thanks for the update . I have created a new thread on the same forum with as much details about the issue as possible.

https://learn.microsoft.com/en-us/answers/questions/1684756/how-to-run-stored-procedure-in-azure-database-for

Answer 1

@Anuj, Singh (Cognizant) - Thanks for the question and using MS Q&A platform.Run the following commands in a single cell to install MY SQL ODBC Driver on Azure Databricks cluster.

%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/

User's image

Run the following commands in a single cell to install pyodbc Driver on Azure Databricks cluster.

%sh    
apt-get -y install unixodbc-dev
/databricks/python/bin/pip install pyodbc

User's image

Connect to Azure SQL Database or Postgres using ODBC as mentined in the document:https://nachiketrajput44.medium.com/how-to-run-stored-procedure-in-azure-data-warehouse-using-databricks-caa912d123d5
User's image

For more details, refer to Connect Python and pyodbc to Azure Databricks.

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.

Share via

Do you know how to install the 'ODBC Driver 17 for PostgreSQL' on a Azure Databricks cluster?

0 additional answers

Your answer