Python and Dremio ODBC gets "Driver File Not Found" Error on M1 Mac

Overview

When trying to connect to Dremio via pyodbc via an m1 mac you will see this sort of error

[unixODBC][Driver Manager]Can't open lib 'Dremio ODBC Driver 64-bit' : file not 
found (0) (SQLDriverConnect)"

or if using the file directly in the connection string

[unixODBC][Driver Manager]Can't open lib '/Library/Dremio/ODBC/lib/libdrillodbc_sbu.dylib' :
 file not found (0) (SQLDriverConnect)"

even if the file is present on the file system.

ls -l /Library/Dremio/ODBC/lib
total 191208
-rwxr-xr-x 1 user staff    880 Sep 13 2021 DrillODBC.did
-rwxr-xr-x 1 user staff  207949 Sep 13 2021 cacerts.pem
-rw-r--r-- 1 user staff    188 Jun 29 14:12 dremio.drillodbc.ini
-rwxr-xr-x 1 user staff 97679376 Sep 20 2021 libdrillodbc_sbu.dylib

Applies To

Dremio ODBC Drivers running 1.5.x  on M1 Macs when using Python and unixodbc to access Dremio

Details

You can verify this is in play by running ld on the library the error message cannot load, you will note the dylib has no symbols for arm64

ld /Library/Dremio/ODBC/lib/libdrillodbc_sbu.dylib
ld: warning: platform not specified
ld: warning: -arch not specified
ld: warning: No platform min-version specified on command line
ld: warning: ignoring file /Library/Dremio/ODBC/lib/libdrillodbc_sbu.dylib, file is universal (i386,x86_64) but does not contain the unknown architecture: /Library/Dremio/ODBC/lib/libdrillodbc_sbu.dylib
Undefined symbols for architecture unknown:
  "_main", referenced from:
     implicit entry/start for main executable
ld: symbol(s) not found for architecture unknown

Cause

1. The dremio ODBC driver is only compiled for Intel chips, the fix is to run everything through Rosetta which is Apple's translation layer
2. However, the mac included Python is compiled for the M1 chip and therefore we cannot use Rosetta with it, therefore breaking the Dremio driver
3. The paths for homebrew for the M1 chip do not match up with the paths needed for the dremio ODBC driver
4. To use unixodbc, we need to update the dremio odbc .ini to point to the correct DLL

Workaround

Do the following workaround after installing the ODBC driver

1. Add the following line to /Library/Dremio/ODBC/lib/dremio.drillodbc.ini ODBCInstLib=/usr/local/homebrew/opt/unixodbc/lib/libodbcinst.2.dylib

2. /usr/sbin/softwareupdate --install-rosetta

3. cd ~/Downloads

4. mkdir homebrew

5. curl -L https://github.com/Homebrew/brew/tarball/master | tar xz --strip 1 -C homebrew

6. sudo mv homebrew /usr/local/homebrew

7. arch -x86_64 /usr/local/homebrew/bin/brew install python unixodbc

8. /usr/local/homebrew/bin/python3 -m venv venv

9. ./venv/bin/pip install pyodbc

10. ./venv/bin/python whatever_your_python_script_is_called.py 

Further Reading

http://www.unixodbc.org/

https://github.com/mkleehammer/pyodbc