Many people have had quite a bit of trouble with this based on the documentation/tutorials online. If you want to connect to snowflake but have an M1 mac, the tutorials online as of this post are misleading at best. After figuring it out, I created this to leave breadcrumbs for other engineers.
You may be following this tutorial - https://community.snowflake.com/s/article/How-To-Connect-Snowflake-with-R-RStudio-using-RODBC-driver-on-Windows-MacOS-Linux
Step 1 is to follow this tutorial - https://docs.snowflake.com/en/developer-guide/odbc/odbc-mac
Requirement is to download and install iodbc from here - https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads macOS 10.15 thru 12.x (and above) (Monterey (12.x), Big Sur (11.x), Catalina (10.15.x)) on x86_64 and arm64: iODBC-SDK-3.52.15-macOS11.dmg Note: This is misleading as this is not needed.
Step 1: Install the ODBC Driver Download from here: https://developers.snowflake.com/odbc/ Find and install this particular version for arm64 based M1 architecture - snowflake_odbc_mac_arm64-2.25.10.dmg.
If you continue with the tutotial, your iodbctest results may be showing the following error:
1: SQLDriverConnect = [iODBC][Driver Manager]dlopen(/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib, 0x0006): tried: '/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/opt/snowflake/snowflakeodbc/ (0) SQLSTATE=00000
2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003
Indeed, that path does not exist. There is no universal folder because we are using the version of the snowflake driver for M1 macs.
Just attempting to update the path in both ini files to remove the universal folder: (Driver = /opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib) because that is where the file resides now does not work:
1: SQLDriverConnect = [Snowflake][Support] (50404) Invalid connection string. (50404) SQLSTATE=HY000
1: ODBC_Connect = [Snowflake][Support] (50404) Invalid connection string. (50404) SQLSTATE=HY000
Have a nice day.%
Copying the files to the universal path to mimic what iodbc is expecting does not succeed either --
❯ cd /opt/snowflake/snowflakeodbc
sudo mkdir lib/universal
sudo cp lib/libSnowflake.dylib lib/universal/libSnowflake.dylib
sudo cp -R ErrorMessages/en-US/ /opt/snowflake/snowflakeodbc/lib/universal/en-US/
Will yield:
1: SQLDriverConnect = [Snowflake][Support] (50404) Invalid connection string. (50404) SQLSTATE=HY000
1: ODBC_Connect = [Snowflake][Support] (50404) Invalid connection string. (50404) SQLSTATE=HY000
Have a nice day.%
It's not a problem with the connection string, iodbc is just broken. I tested dozens of combinations based on the examples and documentation.
When I attempted to just go ahead and try this from R, I am getting errors because the snowflake driver doesn't even have the required error messages in the XML now (Before I moved the error message files, this error was because the error message files didn't even exist in universal):
> myconn <- DBI::dbConnect(odbc::odbc(), "snowflake_dsn", uid="test", pwd='test')
Error: nanodbc/nanodbc.cpp:1118: 00000: [Snowflake][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Snowflake][DSI] An error occurred while attempting to retrieve the error message for key 'LibsLoadErr' with message parameters ['""'] and component ID 3: Message not found in file "/opt/snowflake/snowflakeodbc/lib/universal/en-US/ODBCMessages.xml"
Unlike what you have seen in the documentation, iODBC is not needed at all.
If you have it, you can go ahead and remove it:
sudo rm -rf /Library/Application\ Support/iODBC/
sudo rm -rf /Applications/IODBC
brew update && brew install unixodbc freetds
Note: This step was missing from the original tutorial
- Go here: https://developers.snowflake.com/odbc/
- Scroll down to
Download all Versions, and click theMACOStab. - Make sure to get the ARM version like
snowflake_odbc_mac_arm64-2.25.10.dmg
Run
odbcinst -j
This should show you something like this:
❯ odbcinst -j
unixODBC 2.3.11
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/jp/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Navigate to the folder with the files with VS Code like this:
code /opt/homebrew/etc/
Replace the contents of odbcinst.ini (it might have universal in the Driver path and be pointing to the wrong place) with this:
[ODBC Drivers]
SnowflakeDSIIDriver=Installed
[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib
DriverODBCVer=03.52
SQLLevel=1
Set up odbc.ini like this... simple is better:
[ODBC Data Sources]
SNOWFLAKE_ODBC = Snowflake
[SNOWFLAKE_ODBC]
Driver = /opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib
Description =
server = suchandsuch.snowflakecomputing.com
role = sysadmin
tracing = 6
Note: You may need to retry as sudo to save some of these files.
Run
odbcinst -q -s
It should look like this:
❯ odbcinst -q -s
[SNOWFLAKE_ODBC]
- Open up your snowflake simba file with this command:
code /opt/snowflake/snowflakeodbc/lib/simba.snowflake.ini
If it doesn't exist, you might have installed the wrong snowflake odbc driver version. Make sure you have the arm64 version as mentioned above.
- Look for line 31 which should read:
ODBCInstLib=libiodbcinst.dylib
We need to change the path to the correct one.
- Run this to find all libiodbcinst.dylib files on your system
sudo find / -iname libodbcinst.dylib -print 2>/dev/null > output.txt
- Once it's done, inspect it with cat output.txt, and look for the version under homebrew's Cellar.
- Replace the line with the path. Mine looks like this:
ODBCInstLib=/opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbcinst.dylib
- Save the file
Ensure that you can connect through isql by running this command:
isql -v SNOWFLAKE_ODBC jcassil test
You should get the error below:
❯ isql -v SNOWFLAKE_ODBC test test
[28000][unixODBC]Incorrect username or password was specified.
[ISQL]ERROR: Could not SQLConnect
- You can change the username to your username and the password to your password, surrounded in single quotes (in case there are special characters):
❯ isql -v SNOWFLAKE_ODBC username 'mycr@zyPassw0rd!' +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | echo [string] | | quit | | | +---------------------------------------+ SQL>
If you have made it this far, we are almost done!
Run this command to copy your odbc.ini to your user directory library:
cp /opt/homebrew/etc/odbc.ini ~/Library/ODBC/
This is the version that is actually used for R and Python. Open it up and add your user and password, and other config if you want:
code ~/Library/ODBC/odbc.ini
uid = your_username
pwd = mycr@zyPassw0rd!
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
myconn <- DBI::dbConnect(odbc::odbc(), "SNOWFLAKE_ODBC")
mydata <- DBI::dbGetQuery(myconn,"SELECT * FROM DUAL")
head(mydata)
# COLUMN1
#1 <NA>
- First, install pyodbc
- If you already have it, you may need to uninstall and reinstall from binary source or you will get this error :
pip uninstall pyodbc
pip install --no-binary :all: pyodbc
- Then test after opening a python shell:
import pyodbc
import sys
import os
con = pyodbc.connect('DSN=SNOWFLAKE_ODBC')
con.setencoding(encoding='utf-8')
con.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cursor=con.cursor()
cursor.execute("SELECT * FROM DUAL")
while True:
row=cursor.fetchone()
if not row:
break
print(row)