Sequel::DatabaseConnectionError: ODBC::Error: 01000 (0) [unixODBC][Driver Manager]Can't open lib 'libSnowflake.dylib'

Question

I am trying to connect to a snowflake database to a rails app. I followed the necessary instructions found at https://docs.snowflake.com/en/user-guide/odbc.html. After installing unixodbc and configuring the drivers. I try to run the server from my rails application. But I always get the following error:

Sequel::DatabaseConnectionError: ODBC::Error: 01000 (0) [unixODBC][Driver Manager]Can't open lib '/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib' : file not found

The "libSnowflake.dylib" appears to be present at the right location, I even checked the same in odbc.ini file.

The problem only appears to be happening in my Macbook M1 model, others seems to be just fine, I can vouch on this as I got the same error on my colleagues M1 device as well, while it installs perfectly on Intel enabled Macbook. Can someone please guide me what can be done in this case as I don't see much help on this specific topic.

The only difference I can make out is the location of my driver files. running "odbcinst -j" on my system gives following:
unixODBC 2.3.9 DRIVERS............: /opt/homebrew/etc/odbcinst.ini SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources USER DATA SOURCES..: /Users/raktim.bhowmick/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8.`
On my colleagues macbook the following shows up. unixODBC 2.3.9 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /Users/prashant.devani/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8.

Also I want to mention that I have a seperate profile on my macbook which has system priveledges.

Answer 1

It appears that ODBC driver Manager is unable to open the library. You may try to provide 777 permissions on this lib and see if that helps. Also, check if the driver manager was installed properly on the M1 machine or test with the latest driver manager if not done already. You may double-check the configurations using https://community.snowflake.com/s/article/How-to-create-Snowflake-ODBC-DSN-On-MacOS Do you see the same error through isql test instead of the rails application?

Answer 2

It seems like the core of the issue is that the odbc code is looking for arm64 architecture drivers but Snowflake is providing it in x86_64 architecture. By installing an x86_64 versions of odbc we are able to have it successfully talk to the driver.

First I uninstalled R and Rstudio. (it may be possible to sim-link or change things behind the scenes to make this work with existing installs but I am not sure).

Then install rosetta (apples software for translating between architectures) and a version of homebrew built with it. I am leaving my main version of homebrew in place.

softwareupdate --install-rosetta
arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Then use that version of homebrew to install odbc, R, and Rstudio.

arch -x86_64 /usr/local/Homebrew/bin/brew install unixodbc
arch -x86_64 /usr/local/Homebrew/bin/brew install --cask rstudio
arch -x86_64 /usr/local/Homebrew/bin/brew install --cask r

We then need to install the snowflake driver: https://sfc-repo.snowflakecomputing.com/odbc/mac64/latest/index.html

Click through all the install prompts.

Modify your files

/usr/local/etc/odbcinst.ini:

[Snowflake Driver]
Driver          = /opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib

/usr/local/etc/odbc.ini

[Snowflake]
Driver             = Snowflake Driver
uid                = <uid>
server             = <server>
role               = <role>
warehouse          = <warehouse>
authenticator      = externalbrowser

We also need to modify the simba.snowflake.ini file.

It is somewhat locked down so run:

sudo chmod 646 /opt/snowflake/snowflakeodbc/lib/universal/simba.snowflake.ini

Then

vim /opt/snowflake/snowflakeodbc/lib/universal/simba.snowflake.ini

And find the ODBCInstLib line that is uncommented and change it to:

ODBCInstLib=/usr/local/Cellar/unixodbc/2.3.9_1/lib/libodbcinst.dylib

After setting this up I was able to use this connection successfully:

install.packages("DBI")
install.packages("odbc")
con <- DBI::dbConnect(odbc::odbc(), "Snowflake")

Answer 3

If you have a new M1 chipset Mac, you will need the arm64 bit versions of the Snowflake driver and R.app. You don't need to do the arch swap stuff Scott mentions anymore due to Snowflake releasing the arm drivers earlier this year.

I had to also update '/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib' to be '/opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib'

For unixODBC, do install with brew and it will use the .ini files in /opt/homebrew/opt/unixodbc.

When updating /opt/snowflake/snowflakeodbc/lib/simba.snowflake.ini, I used the more universal linked file path that just points to the latest version instead of a version specific version like what Scott posted in this question: ODBCInstLib=/opt/homebrew/lib/libodbcinst.dylib

I was able to get both unixODBC and iODBC working on same system. Key was the ini file locations are different, but both can have the same content.

Answer 4

If you're on arm64 (m1 or m2 mac), installing the driver from snowflake's macaarch64 directory https://sfc-repo.snowflakecomputing.com/odbc/macaarch64/index.html should be all you need. No need to install Rosetta, change permissions or anything else.

Just make sure you have proper paths set in odbc.ini and odbcinst.ini afterwards. In my case there's no universal dir in the path: /opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib.