Snowflake ODBC Connection with Mac OS

Snowflake ODBC Connection with Mac OS

Recently I received a request to integrate a https://www.snowflake.com/ connection with an existing Rails application. After some research I came to the conclusion that one of the only ways to query the Snowflake views was through an ODBC connection using a combination of https://github.com/larskanis/ruby-odbc and https://github.com/jeremyevans/sequel.

Before digging into this integration I wanted to get my local environment set up so that I could experiment with querying the data outside of the application while we were settling on the presentation of what was to be included in the Snowflake views.

Snowflake actually has pretty good documentation for setting up a local environment for Mac OS. These docs are located here: https://docs.snowflake.com/en/user-guide/odbc-mac.html.

Installing iODBC

The first step is to install iODBC:
http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads#Mac%20OS%20X.

At the time of writing the link in the Snowflake documentation (http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads) is not currently working.

I chose to not install the optional ODBC Manager and configure the connection manually through the odbcinst.ini and odbc.ini files.

Installing The Snowflake Driver

After iODBC has been installed the next step is to actually install the ODBC driver. This driver can be found here: https://sfc-repo.snowflakecomputing.com/odbc/index.html.

This driver has its configuration files in ~/Library/ODBC/odbcinst.ini and ~/Library/ODBC/odbc.ini.

The ~/Library/ODBC/odbcinst.ini should be set up properly. The real configuration comes into play in the ~/Library/ODBC/odbc.ini file.

Here is an example of my local odbc.ini file:

[ODBC Data Sources]
snowflake = Snowflake

[snowflake]
Driver      = /opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
Description = SnowflakeDB
Locale      = en-US
server      = <organization_id>.<account_id>.snowflakecomputing.com
port        = 443
ssl         = on
account     = <organization_id>.<account_id>

Testing the Snowflake ODBC Installation and Configuration

With this file updated with your Snowflake information you can now test the connection using the following command that is included with iODBC:
/Library/Application Support/iODBC/bin/iodbctest
This will allow you to enter an iODBC connection string for any of the DSNs listed using the ? command. This connection string should not be enclosed in quotes and should be in the following format:

dsn=<dsn_from_odbc.ini>;uid=<username>;pwd=<password>;warehouse=<warehouse>;database=<database>

If everything is set up properly you will see a “SQL>” prompt and be able to query the database that you have specified in the connection string.

unixodbc Installation and Configuration

Now, that was a lot of setup just to get to a prompt where you are able to query Snowflake. Now that you have all of that working you would expect to be able to install the ruby-odbc gem and start working in your Rails environment. When you go to install the ruby-odbc gem you will run into an error:

checking for version.h... no
checking for sql.h... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary libraries and/or headers. Check the mkmf.log file for more details. You may need configuration options.

Even though the driver has been installed so that iODBC can use it, the development headers for the ruby-odbc gem do not exist yet.

This can be solved by installing unixodbc:

brew install unixodbc

If you are on a new M1 Mac you may need to pass in the odbc directory:

gem install ruby-odbc -- --with-odbc-dir=$(brew --prefix unixodbc)

This program stores its configuration files in a different place than iODBC and these files will be listed by running:

odbcinst -j

This is an example of the odbcinst.ini file using the same driver that was installed above with a different label that does not match the local driver.

[ODBC Drivers]
SnowflakeDSIIDriver=Installed

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
DriverODBCVer=03.52
SQLLevel=1

The odbc.ini file should match the local one from above using the new label for the driver as it is set above.

[ODBC Data Sources]
snowflake = SnowflakeDSIIDriver

[snowflake]
Driver      = /opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
Description = SnowflakeDB
Locale      = en-US
server      = <organization_id>.<account_id>.snowflakecomputing.com
port        = 443
ssl         = on
account     = <organization_id>.<account_id>

Using Rails and Sequel to Query Snowflake

Now that is all set up we can finally install the Sequel gem and fire up our Rails console and test this out:

warehouse_name = <warehouse_name>
database_name = <database_name>
view_table = <view_table>
db = Sequel.odbc('snowflake', user: ENV[‘SNOWFLAKE_USER’], password: ENV[‘SNOWFLAKE_PASSWORD’])

db.execute("USE WAREHOUSE #{warehouse_name};")

db.fetch("SELECT * FROM #{database_name}.ANALYTICS.#{view_table};").entries

After supplying your warehouse, database, and view you should get back an array of hashes of data from your Snowflake view.