CODEWITHSUNDEEP
python-3.xpyodbcdatabricksazure-databricks
pythonUser
pythonUser

Reputation: 213

How to install PYODBC in Databricks

I have to install pyodbc module in Databricks. I have tried using this command (pip install pyodbc) but it is failed due to below error.

Error message

Upvotes: 4

Views: 22986

Answers (4)

Zenville Erasmus
Zenville Erasmus

Reputation: 109

The following works for me as a Workspace init script on Databricks Runtime 15.4 LTS for Microsoft ODBC 18, i.e. cluster_dependencies.sh:

#!/bin/bash

if ! [[ "18.04 20.04 22.04 23.04 24.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
sudo apt-get install -y unixodbc-dev python3-dev
pip install pyodbc

This has been sourced from here.

Start up the cluster. Then in a notebook import pyodbc.

Upvotes: 0

Nikhil Redij
Nikhil Redij

Reputation: 1051

I was having the same issue for installation. This is what I tried and it worked.

  • Databricks does not have default ODBC Driver. Run following commands in a single cell to install MS SQL ODBC driver
%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17
  • Run this in notebook
dbutils.fs.put("/databricks/init/<YourClusterName>/pyodbc-install.sh","""
#!/bin/bash
sudo apt-get update
sudo apt-get -q -y install unixodbc unixodbc-dev
sudo apt-get -q -y install python3-dev
/databricks/python/bin/pip install pyodbc
""", True)

  • Restart the cluster

  • Import pyodbc in Code

Upvotes: 12

simon_dmorias
simon_dmorias

Reputation: 2483

I had some problems a while back with connecting using pyobdc, details of my fix are here: https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark

I think the problem stems from PYTHONPATH on the databricks clusters being set to the Python 2 install.

I suspect the lines: 

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

Will work for you.

Update: Even simpler (though you will still need unixodbc-dev from above):

%sh 
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc

Upvotes: 6

Fabio Schultz
Fabio Schultz

Reputation: 527

Right-click the Workspace folder where you want to store the library.

Select Create > Library.

Look this https://docs.databricks.com/user-guide/libraries.html for detailed information

Upvotes: 1

Related Questions