THIS IS A WORK IN PROGRESS. USE AT YOUR OWN RISK.

Snowflex ❄💪

This application encapsulates an ODBC connection pool for connecting to the Snowflake data warehouse.

Setup

The following config options can be set:

config :snowflex,
  driver: "/path/to/my/ODBC/driver" # defaults to "/usr/lib/snowflake/odbc/lib/libSnowflake.so"

Connection pools are not automatically started for you. You will need to define and establish each connection pool in your application module. Configuration values related to connection timeouts and the mapping of :null query values can be set here.

First, create a module to hold your connection information:

defmodule MyApp.SnowflakeConnection do
  use Snowflex.Connection,
    otp_app: :my_app,
    timeout: :timer.minutes(20),
    map_nulls_to_nil?: true
end

Define your configuration:

import Config

# ...

config :my_app, MyApp.SnowflakeConnection,
  connection: [
      role: "PROD",
      warehouse: System.get_env("SNOWFLAKE_POS_WH"),
      uid: System.get_env("SNOWFLAKE_POS_UID"),
      pwd: System.get_env("SNOWFLAKE_POS_PWD")
    ]

 # you may define multiple connection modules
 config :my_app, MyApp.MyOtherSnowflakeConnection,
    worker: MyApp.MockWorker # defaults to Snowflex.Worker (change for testing/development)
    connection: [
      role: "PROD",
      warehouse: System.get_env("SNOWFLAKE_ADVERTISING_WH"),
      uid: System.get_env("SNOWFLAKE_ADVERTISING_UID"),
      pwd: System.get_env("SNOWFLAKE_ADVERTISING_PWD")
    ]

The odbc driver will, by default, return :null for empty values returned from snowflake queries. This can be converted to nil by passing the map_nulls_to_nil?: true option to the use Snowflex.Connection macro. :map_nulls_to_nil? will default to false if not set.

Then, in your application module, you would start your connection:

def MyApp.Application do
  use Application

  def start(_type, _args) do

    children = [
      MyApp.SnowflakeConnection,
      MyApp.MyOtherSnowflakeConnection
    ]

    opts = [strategy: :one_for_one, name: MyApp.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Caveats

If you are planning to connect to the Snowflake warehouse, your local Erlang instance must have ODBC enabled. The erlang installed by Homebrew does NOT have ODBC support. The asdf version of erlang does have ODBC support. You will also need the Snowflake ODBC driver installed on your machine. You can download this from https://sfc-repo.snowflakecomputing.com/odbc/index.html.

Apple Silicon

Snowflake has a native macaarch64 driver available from https://sfc-repo.snowflakecomputing.com/odbc/macaarch64/index.html. However Erlang is unable to find the unixodbc files by default after Homebrew changed their installation directory from /usr/local to /opt/homebrew.

We can build Erlang with asdf and ensure the correct files included to make sure odbc.app is available when running Elixir.

We will need asdf and Homebrew installed.

Next, we should first remove any previous installations or builds of Elixir or Erlang to make sure they are not incorrectly targeted by mix when we run our applicatoin. This can be done like so:

brew uninstall elixir
brew uninstall erlang
asdf uninstall erlang
rm ~/.asdf/plugins/erlang/kerl-home/otp_builds
rm ~/.asdf/plugins/erlang/kerl-home/otp_installations

We can now get the neccesary ODBC and OpenSSL files from Brew, set their correct locations in the environment, and build Erlang and Elixir with asdf like so:

brew install unixodbc
brew install openssl@1.1
export KERL_CONFIGURE_OPTIONS="--with-odbc=$(brew --prefix unixodbc) --with-ssl=$(brew --prefix openssl@1.1)"
export CC="/usr/bin/gcc -I$(brew --prefix unixodbc)/include"
export LDFLAGS="-L$(brew --prefix unixodbc)/lib"
asdf install erlang
asdf install elixir
unset KERL_CONFIGURE_OPTIONS
unset CC
unset LDFLAGS

You will then need to change the following on /opt/snowflake/snowflakeodbc/lib/simba.snowflake.ini:

Change the CABundleFile config value to the place the cacert.pem file is in your ODBC driver installation (for version 2.25.8 it is in /opt/snowflake/snowflakeodbc/lib/cacert.pem). Sometimes it points to an invalid path, which would result in the error "OOB curl_easy_perform() failed: Problem with the SSL CA cert (path? access rights?)" when connecting to snowflake.
Comment all lines starting with ODBCInstLib
Add the line ODBCInstLib=/opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbcinst.dylib at the bottom

And finally ensure that your elixir config has the correct driver location

config :snowflex, driver: "/opt/snowflake/snowflakeodbc/lib/libSnowflake.dylib"

Installation

The package can be installed by adding :snowflex to your list of dependencies in mix.exs:

def deps do
  [
    {:snowflex, "~> 0.5.1"}
  ]
end

DBConnection Support

DBConnection support is currently in experimental phase, setting it up is very similar to current implementation with the expection of configuration options and obtaining the same results will require an extra step:

Configuration:

Setting a Module to hold the connection is very similar, but instead you'll use Snowflex.DBConnection:

Example:

defmodule MyApp.SnowflakeConnection do
  use Snowflex.DBConnection,
    otp_app: :my_app,
    timeout: :timer.minutes(5)
end

config :my_app, MyApp.SnowflakeConnection,
  pool_size: 5, # the connection pool size
  worker: MyApp.CustomWorker, # defaults to Snowflex.DBConnection.Server
  connection: [
      role: "PROD",
      warehouse: System.get_env("SNOWFLAKE_POS_WH"),
      uid: System.get_env("SNOWFLAKE_POS_UID"),
      pwd: System.get_env("SNOWFLAKE_POS_PWD")
    ]

Usage:

After setup, you can use your connection to query:

alias Snowflex.DBConnection.Result

{:ok, %Result{} = result} = MyApp.SnowflakeConnection.execute("my query")
{:ok, %Result{} = result} = MyApp.SnowflakeConnection.execute("my query", ["my params"])

As you can see we now receive an {:ok, result} tuple, to get results as expected with current implementation, we need to call process_result/1:

alias Snowflex.DBConnection.Result

{:ok, %Result{} = result} = MyApp.SnowflakeConnection.execute("my query")

[%{"col" => 1}, %{"col" => 2}] = SnowflakeDBConnection.process_result(result)

Copyright and License

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

pepsico-ecommerce/snowflex