Setup and connect to Snowflake with Ruby on Rails

At my current work, I'm working with Snowflake - a data warehouse as-a-service - and it takes me some times to setup and connect Rails to it. This post will focus on snippets for installing Snowflake on Ubuntu and how to connect to it with Ruby on Rails.

Install Snowflake on Ubuntu

Snowflake uses ODBC protocol. Below are commands to install unixodbc and Snowflake from deb file. The full references on how to install Snowflake on Linux is available here https://docs.snowflake.com/en/user-guide/odbc-linux.html

We need to install unixodbc-dev as well because one of the Ruby gems we use to connect Rails to Snowflake requires it.

apt-get update && apt-get install -y unixodbc unixodbc-dev
TEMP_DEB="$(mktemp)" && \
  wget -O "$TEMP_DEB" 'https://sfc-repo.snowflakecomputing.com/odbc/linux/latest/snowflake-odbc-2.21.0.x86_64.deb' && \
  dpkg -i "$TEMP_DEB"

The following command is useful to locate odbc config files

odbcinst -j

unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Snowfalke inserts its driver definition to /etc/odbcinst.ini

cat /etc/odbcinst.ini

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1
UsageCount=1

And we're going to add our own Snowflake config to /etc/odbc.ini

[TestSnowflake]
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=foo.snowflakecomputing.com
PORT=443
SSL=on
ACCOUNT=foo
Database=bar
Schema=baz
Query_Timeout=270

To test the config, run a test command:

isql -v TestSnowflake username password

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Make sure the test command is successful before moving to next step.

Connect to Snowflake from Rails

We will utilize rubu-odbc and sequel gems to connect to Snowflake and execute queries.

require 'odbc' # gem 'ruby-odbc'
require 'sequel' # gem 'sequel'

db = Sequel.odbc('TestSnowflake', user: 'username', password: 'password')

# List warehouses
db.fetch("show WAREHOUSES;").entries

# Select an active warehouse
db.execute("use warehouse FOO_WAREHOUSE;")

# A simple query
db.fetch("SELECT * FROM products LIMIT 10;") do |record|
  puts record
end

# or
db.fetch("SELECT * FROM products LIMIT 10;").entries