--- title: The Long, Long Journey of Connecting to Snowflake with Ruby description: DSN-less Snowflake data warehouse connection with Ruby and ActiveRecord image: https://engineering.ezcater.com/hubfs/Screen%20Shot%202018-07-16%20at%201.46.23%20PM.png --- [![ezCater](https://engineering.ezcater.com/hs-fs/hubfs/RGB-ezCater-large.png?width=102&height=26&name=RGB-ezCater-large.png "ezCater")](http://www.ezcater.com) # The Long, Long Journey of Connecting to Snowflake with Ruby Aug 8 [Jon Gherardini](https://engineering.ezcater.com/author/jon-gherardini) ## Charting a Path from Redshift to Snowflake ezCater is a data-driven company. For a long time, the principal source for our metrics was our Redshift data warehouse. As a result, we invested heavily in building out the data systems that fed Redshift. We also staffed up a team of engineers dedicated to building, enhancing, and growing our data systems. ([And yes, we're hiring!](https://www.ezcater.com/tech)) To ingest external data into the warehouse, we make extensive use of Stitch (as detailed [on their site](https://www.stitchdata.com/customers/ezcater-enterprise-etl/)). That gets the data into a set of staging schemas, but that's where it ends. Stitch is great, but it's not an all-in-one ETL solution. Further, we need to import a sanitized version of our production data into the warehouse as well. That's where our homegrown Ruby/Rake ETL process takes over. We copy data from a dedicated production read-replica, translate Stitch's raw imports into tables that are digestible by our data analysts, and ensure that users and permissions are set up exactly as configured. However, we reached a scale and a level of sophistication that made Redshift less appealing than competing options. So, we made the decision to migrate to Snowflake. There was one major challenge with that choice: Snowflake unfortunately does not provide an official SDK for Ruby. Thankfully our friends over at Localytics have [cleared a path for Rails](https://eng.localytics.com/connecting-to-snowflake-with-ruby-on-rails/) with their [ODBC adapter](https://github.com/localytics/odbc_adapter), which we figured we could modify for our use case since we use ActiveRecord to manage our database connections. The conversion from Redshift to Snowflake is now complete, and we've decommissioned our Redshift cluster. What follows is the account of how we resolved all the roadblocks that stood in our way. If you're in a TL;DR kind of mood, feel free to scroll to the end for a quick summary. ### The Journey Begins in Development We started by looking at Localytics' blog entries and the [Snowflake ODBC how-to guides](https://docs.snowflake.net/manuals/user-guide/odbc.html) to get the ETL code talking to Snowflake. Just a cursory glance at the Snowflake docs presented a daunting picture rife with dependencies on drivers, libraries, and arcane configuration parameters in INI files. Looking at the table of contents, you can see it's a (supposedly) simple five-step setup: ![Lengthy list of steps for setting up ODBC on Linux](https://engineering.ezcater.com/hs-fs/hubfs/Screen%20Shot%202018-07-16%20at%201.46.23%20PM.png?width=670&name=Screen%20Shot%202018-07-16%20at%201.46.23%20PM.png) But let's face the facts: ODBC is 26 years old and was built for Windows 3.0. It isn't a surprise if it's a little clunky. For speed of iteration, we started working in a local Mac development environment, installing iODBC, the Homebrew `unixodbc` tap, and the Snowflake driver. (Note that the library you'll need is `unixodbc-dev` elsewhere!) Setting up the INI files properly proved to be frustrating, as the default error isn't all that helpful to explain what went wrong. You can see that error below: > `[unixODBC][Driver Manager]Data source name not found, and no default driver specified` *Commence tearing of hair and rending of garments.* Digging into the `odbc_adapter` code, we noted that the `odbc_connection` method had an option to connect with an ODBC connection string. If `dsn` is in the config hash, it uses a traditional ODBC DSN lookup to pull together the configuration and connect. If it's *not* and `conn_str` is specified instead, then it uses that for a DSN-less connection. The block comment says it all: > `# Connect using ODBC connection string` > `# Supports DSN-based or DSN-less connections` > `# e.g. "DSN=virt5;UID=rails;PWD=rails"` > # ย  ย  ย `"DRIVER={OpenLink Virtuoso};HOST=carlmbp;UID=rails;PWD=rails"` So we started down that path. Our connection manager class pulls the Redshift password from the environment, so it seemed like an easy patch to pull a `pwd` value for the Snowflake password if it was configured to talk to Snowflake. Also, since we parse out all our connection configuration from a traditional `database.yml`, it seemed logical to dump all the Snowflake connection parameters into a new section in there. We could then use that to build a connection string in code. Sure enough, it worked! Hurdle #1 surmounted -- we could run basic ETL code against Snowflake from a dev machine with no INI files and *no password on disk.* Commence celebration. ๐ŸŽ‰ ### Speed Bumps The celebration was short-lived, as our builds immediately began failing on CircleCI. The build containers didn't have `unixodbc-dev` installed, and thus `bundler` couldn't install the `odbc_adapter gem`. Since we didn't need it for our test suite, it was simple enough to put the gem in a `production` group and exclude that group with `bundle install --without production`. Then, another complication crept in: the ODBC adapter supports a single SQL statement at a time. As a result, we'd also put in a query-splitter, since our ETL SQL often will batch a `CREATE TABLE` alongside the `INSERT` in the same file. Splitting on `;` seemed safe, but proved troublesome for queries that legitimately had strings with semicolons in them. A little creative application of a better regex did the trick, but it would have been a bigger deal if there was any transaction management involved. Opening a transaction and then failing on a separate single statement later in the file would have needed careful error handling to roll things back. Once we applied these fixes, it was time to replicate our dev environment success in the staging and production environments. ### Onward to Production! Our ETL currently runs on a node that's configured by Chef. In broad terms, testing Chef is easy if you use Test Kitchen -- just fire up a VM with Test Kitchen and iteratively modify your Chef cookbooks & recipes until they do what you want. In our case, it looked as though we needed to do a few things: - Modify the `yml` template to add a Snowflake section - Add a recipe to set the variables that populate the template - Add a recipe to install `unixodbc-dev` as a prerequisite for the `odbc_adapter` gem - Manually download and add the Snowflake Debian package to Chef; `dpkg -i` to install it (Yes, manually. Snowflake doesn't support a simple `apt-get` from anywhere, and a GET request to fetch the package from their website requires an auth token.) All that went swimmingly in our production-like staging environment, but then came the dreaded "`Data source name not found, and no default driver specified`" errors. Lots and lots of them. We backed off and set up INI files, using `odbcinst -j` to confirm that we had set up the files in the right locations with the correct values (matching what worked in development). That worked, and we re-commenced a short-lived celebration. ๐ŸŽ‰ Having confirmed there wasn't anything about the staging environment that intrinsically prevented us from connecting, we tried the DSN-less connection again. Nothing. Our Google searches didn't help either, since there weren't any specific keywords we could use to hone in on our particular issue. So we dove into some C code spelunking, noting that the trail kept dead-ending at this line in the Ruby ODBC driver: > `connection = ODBC::Database.new.drvconnect(driver)` We pulled down the `ruby_odbc` code and looked for "`drvconnect"` to get more context into what that function was looking for. That search led to the `dbc_drvconnect` function, which in turn allocates a connection object, connects, and then deallocates the connection. The connection function is `SQLDRIVERCONNECT`, which is a `#define` for `SQLDriverConnect`. Finding no further mention of that, it was apparent that `SQLDriverConnect` was a function in the underlying unixODBC library. The spelunking continued. Sure enough, we found release notes on [unixodbc.org](http://unixodbc.org) mentioning numerous changes to that function, including a enhancement in version 2.3.1 regarding command-line flags for `isql` allowing a DSN-less connection -- and this interesting comment: > Allow setting the DM overrive \[sic\] values in the connection string to SQLDriverConnect for example "DRIVER={Easysoft ODBC-SQL Server};Server=myserver;UID=user;PWD=pass;DMStmtAttr=SQL\_QUERY\_TIMEOUT=10;" Running `odbcinst -j` one more time, it became clear. On Ubuntu Trusty, the OS on our staging box, the version of the `unixodbc` library is 2.2.14. The version installed by Homebrew is 2.3.6, the latest. We should've seen that one before. Facepalm. Trusty's `unixodbc-dev` is **about 10 years old**. Upgrading to Xenial would pick up version 2.3.1, but even that is **about 7 years old**. The latest security fix had been made in March 2018 with version 2.3.6, so that seemed to be the version we'd want. So, amongst the available options, we chose to build our own 2.3.6 binaries, package them, and then `dpkg -i` them with Chef since we're already doing that with the Snowflake package. And wouldn't you know, once we did that, everything worked exactly as intended the first time. Celebration. And naptime. ๐ŸŽ‰ ### Lessons Learned about Using a DSN-less ODBC Connection 1. Get the right library versions, even if it means compiling it yourself 2. Explicitly include the initializer if you're not using Rails 3. The ODBC infrastructure described here doesn't handle concurrency seamlessly, so: 1. Synchronize creating your connection manager, if you have one 2. Synchronize opening a connection, if you run concurrently ## The TL;DR Version ### Setting up a DSN-less Snowflake Connection with Ruby/ActiveRecord We have encapsulated the following configuration into an internal gem which we hope to open source, but for now here are the steps to get you going: 1. Install everything. This includes the `unixodbc-dev` system library (`unixodbc` in Homebrew), the Snowflake driver, and the `odbc_adapter` gem (which depends on `ruby-odbc`). You **must** install a version of `unixodbc` that is >= 2.3.1. 2. Create a Snowflake ODBC initializer, as detailed in the [Localytics blog](https://eng.localytics.com/connecting-to-snowflake-with-ruby-on-rails/). Add the necessary `require` statements in your code, since you won't be able to rely on Rails magic. 3. Construct a connection string by concatenating your config key=value pairs using semicolons. You'll need this in the next step to set the value for the `conn_str` key in the config hash. Snowflake lists [the required and optional connection parameters](https://docs.snowflake.net/manuals/user-guide/odbc-parameters.html#connection-parameters) on their site. Remember not to specify a `dsn` key or else the ODBC adapter will assume it should look for INI files! 4. Add the appropriate configuration to ActiveRecord. We read ours from our `database.yml`, but it boils down to something like this once you add in the connection string: 5. Connect and do things! For example: Tags: [data warehousing](https://engineering.ezcater.com/topic/data-warehousing), [snowflake](https://engineering.ezcater.com/topic/snowflake), [ruby](https://engineering.ezcater.com/topic/ruby) ## Work With Us Weโ€™re always looking for highly skilled full stack engineers to help execute our technology goals while riding this rocket ship of growth. Our people are terrifically talented, friendly people who love what they do. And everyone is generous and kind, too โ€” we have a strict no jerk policy. [![View ezCater Opportunities](https://no-cache.hubspot.com/cta/default/1703639/47d2a3d8-ffb7-4b28-8c3a-35bdb6d0605b.png)](https://cta-redirect.hubspot.com/cta/redirect/1703639/47d2a3d8-ffb7-4b28-8c3a-35bdb6d0605b) ### About ezCater We're the #1 online โ€“ and the only nationwide โ€“ marketplace for business catering in the United States. We make it easy to order food online for your office. From routine office lunches to offsite client meetings, from 5 to 2,000 people, we have a solution for you. ezCater connects business people with over 50,000 reliable local caterers and restaurants across the U.S. ### ezCater is hiring! Weโ€™re always looking for highly skilled full stack and iOS engineers to help execute our technology goals while riding this rocket ship of growth. Our people are terrifically talented straight-shooters who love what they do. And everyone is generous and kind, too โ€” we have a strict no jerk policy. [![View ezCater Opportunities](https://no-cache.hubspot.com/cta/default/1703639/47d2a3d8-ffb7-4b28-8c3a-35bdb6d0605b.png)](https://cta-redirect.hubspot.com/cta/redirect/1703639/47d2a3d8-ffb7-4b28-8c3a-35bdb6d0605b) ### Recent Posts ### Posts by Topic - [experiments (6)](https://engineering.ezcater.com/topic/experiments) - [data warehousing (4)](https://engineering.ezcater.com/topic/data-warehousing) - [engineering (4)](https://engineering.ezcater.com/topic/engineering) - [ezcater (4)](https://engineering.ezcater.com/topic/ezcater) - [ruby (4)](https://engineering.ezcater.com/topic/ruby) - [activerecord (2)](https://engineering.ezcater.com/topic/activerecord) - [coding (2)](https://engineering.ezcater.com/topic/coding) - [development (2)](https://engineering.ezcater.com/topic/development) - [github (2)](https://engineering.ezcater.com/topic/github) - [javascript (2)](https://engineering.ezcater.com/topic/javascript) - [ltv (2)](https://engineering.ezcater.com/topic/ltv) - [machine learning (2)](https://engineering.ezcater.com/topic/machine-learning) - [rails (2)](https://engineering.ezcater.com/topic/rails) - [react (2)](https://engineering.ezcater.com/topic/react) - [redshift (2)](https://engineering.ezcater.com/topic/redshift) - [women\_in\_tech (2)](https://engineering.ezcater.com/topic/women_in_tech) - [active record (1)](https://engineering.ezcater.com/topic/active-record) - [creating (1)](https://engineering.ezcater.com/topic/creating) - [culture (1)](https://engineering.ezcater.com/topic/culture) - [database (1)](https://engineering.ezcater.com/topic/database) - [design (1)](https://engineering.ezcater.com/topic/design) - [devops (1)](https://engineering.ezcater.com/topic/devops) - [dynamodb (1)](https://engineering.ezcater.com/topic/dynamodb) - [growth (1)](https://engineering.ezcater.com/topic/growth) - [hubspot (1)](https://engineering.ezcater.com/topic/hubspot) - [jest (1)](https://engineering.ezcater.com/topic/jest) - [kafka (1)](https://engineering.ezcater.com/topic/kafka) - [kubernetes (1)](https://engineering.ezcater.com/topic/kubernetes) - [migrations (1)](https://engineering.ezcater.com/topic/migrations) - [snapshot testing (1)](https://engineering.ezcater.com/topic/snapshot-testing) - [snowflake (1)](https://engineering.ezcater.com/topic/snowflake) - [sql (1)](https://engineering.ezcater.com/topic/sql) - [time (1)](https://engineering.ezcater.com/topic/time) - [time zones (1)](https://engineering.ezcater.com/topic/time-zones) - [timeofday (1)](https://engineering.ezcater.com/topic/timeofday) - [unit testing (1)](https://engineering.ezcater.com/topic/unit-testing) - [values (1)](https://engineering.ezcater.com/topic/values) see all ### Subscribe to Email Updates ยฉ 2026 ezCater, Inc. | [Terms & Conditions](https://www.ezcater.com/terms_of_use) | [Privacy](https://www.ezcater.com/privacy_policy)