topic Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL in Data Engineering

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' :

zmsoft — Fri, 27 Sep 2024 03:27:27 GMT

Hi there,

My cluster version is 15.4 LTS, and the workspace has UC enabled. When I used the initialization script to install ODBC Driver 17 for SQL Server, there were no errors and the cluster started successfully. But when I use ODBC Driver 17 for SQL Server in the notebook to update the database record, I get an error.

Any suggestion?

Thanks & Regards

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

MarcusC — Fri, 27 Sep 2024 08:01:09 GMT

Is it not possible for you to use the built in sqlserver-driver?
Sample code for reading below

spark.read \

.format("sqlserver") \

.option("host", "xxx.database.windows.net") \

.option("database",database) \

.option("dbtable", table) \

.option("accessToken", access_token) \

.option("encrypt", "true") \

.option("hostNameInCertificate", "*.database.windows.net")

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

zmsoft — Fri, 27 Sep 2024 09:34:37 GMT

Hi @MarcusC

I want to update database rows using the odbc driver

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

APat449 — Tue, 29 Oct 2024 23:07:49 GMT

Is there any update on this issue? is it solved?

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

zmsoft — Wed, 30 Oct 2024 05:20:20 GMT

In Unity catalog environment, if you want to using odbc driver to update database rows, you need to create a volumn, and put the odbc install script file to volumn, then add allow list. After that you can modify computer configuration to add init script. When odbc driver install successfully, you can using odbc driver now.

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

APat449 — Wed, 30 Oct 2024 05:26:37 GMT

thanks for your quick response..
so if I understand correctly, you are asking to create an init script and put it in UC Volume, and then use that path in the DBK cluster's init script?

and as teh INIT script are you referring to something like this?

curl --silent https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# Install msodbcsql18

apt-get update

ACCEPT_EULA=Y apt-get --quiet ---yes install msodbcsql18

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

APat449 — Wed, 30 Oct 2024 05:28:59 GMT

Also is INIT script route is the only route? or is there any other option available?
the reason I am asking, some time back we had a call with DBK and then they mentioned like usage of INIT script is not the right way or so.. cant recall exact explanation though.

Re: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL

zmsoft — Wed, 30 Oct 2024 07:54:14 GMT

I think INIT script is the only way, because when you run the script in notebook, you will get error message, it says you are not root need password.