Writing data to azure sql using python on azure databricks

Question

I am new to databricks and I need to schedule some code that transforms and writes data to an azure sql database using python.

I get the following error: DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

Running the code locally works fine and writes the data to the azure sql db.

Herebelow a sample of my code

server = "example.database.windows.net"
database = ""
username = ""
password = ""

driver = '{ODBC Driver 17 for SQL Server}'

odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;UID='+username+';DATABASE='+ database + ';PWD='+ password
connect_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(odbc_str)
engine = create_engine(connect_str)

df = pd.DataFrame({"Def": [1,2,3,4,5]})

def to_sql(df, table):
    df.to_sql(table, engine, if_exists = "replace", index=False, chunksize = 100)

to_sql(df, "Def")

According to the error, you need to install odbc driver in Azure databricks : learn.microsoft.com/en-us/azure/databricks/integrations/bi/…. Besides, in azure databricks, we can directly use jdbc driver to connect Azure SQL : learn.microsoft.com/en-us/azure/databricks/data/data-sources/… — Jim Xu
– Jim Xu, Commented Nov 10, 2020 at 3:23

CHEEKATLAPRADEEP · Accepted Answer · 2020-11-10 07:29:08Z

2

By default, Azure Databricks does not have ODBC Driver installed.

Make sure you have installed MY SQL ODBC Driver before running the above command.

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

%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

answered Nov 10, 2020 at 7:29

CHEEKATLAPRADEEP

12.8k1 gold badge22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luukv93 Over a year ago

One more question, I pull data from a REST API using Python in databricks and write the output to an azure sql db. Pulling the data from the api goes rapidly but writing it to azure sql goes terribly slow. Is this a recommended approach or would you advise differently?

Collectives™ on Stack Overflow

Writing data to azure sql using python on azure databricks

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related