Connection to Snowflake from Phoenix app

Hello everyone!

This is my first post, I hope I will be able to make it useful to others as well.

I am trying to setup a connection to the cloud analytical DB Snowflake (https://www.snowflake.com/en/) from a Phoenix app using Snowflex (https://github.com/pepsico-ecommerce/snowflex).

I followed the readme on the Snowflex page, but when I run my server locally I keep getting the following error:

[warning] Unable to connect to snowflake: '[Snowflake][ODBC] (10380) Unable to establish connection with data source. Missing settings: {[SERVER]} SQLSTATE IS: 08001 Connection to database failed.'

I am using:

MacOS 12.6 (Apple Silicon M1)

Erlang version 25.1.1, installed with asdf to have ODBC

Elixir version 1.14.2, installed with asdf

Unixodbc 2.3.12 (installed through Homebrew following the Snowflex readme).

This is how my config file looks like for development, with placeholder secrets. I just need to test pushing some data from local for the time being.

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

config :my_app, SnowflakeConnection,
  connection: [
      server: "snowflake-server-address",
      role: "DEV",
      warehouse: "name-of-warehouse",
      uid: "user",
      pwd: "pwd"
  ]

I have looked extensively, but did not find any similar error for a Phoenix app and I don’t know what to do to fix it.

It would be easier for readers if you copy-pasted the errors into a code-formatted ``` block and not post screenshot with a tiny font.

That being said, also please post your configuration so potential helpers can try and offer you modifications.

Thanks a lot @dimitarvp . I have edited my post with the proper formatting for the error message.
What else is needed in terms of providing a configuration, other than the info I posted? Do you mean my mix file? Thanks a lot for trying to help!

I meant what is in your config/*.exs files that relate to connecting to Snowflake.

@dimitarvp , I added what I have in my config/dev.exs. Only trying to connect from local for now.

Admittedly I never worked with Snowflake but have you tried connecting to it outside Elixir, with some dedicated tool? Similar to how people test connecting to their Postgres DB with the psql CLI tool.

yes, I just tested the actual connection with a Snowflake tool called snowsql that works just like psql and I can connect without any issue, using the same connection data I am using in my config file. I very much think the issue is in the ODBC setup on my local machine, but I am not being able to debug it as there are so many different pieces that need to work together.

Thanks a lot anyways for your time and your suggestions on how to improve my question.

1 Like

what is the name of your app? is it really :my_app? if not, then the config is pruned.

Hi @cevado. No, I just substituted the real name of the app. Is it important what it is called?

Highly likely I am afraid. I gave up using ODBC long time ago (more than a decade) but I understand – work is work.

Hope you fix it.

1 Like

I will share a solution in case I am able to make it work, might help others. I have not found much info anywhere so I collected my courage to write a post here :slight_smile:

1 Like

not really, just that if the app is not in the list of apps that is started, the config is pruned. so it needs to be a name of an app that is started, not a random atom that you pass in the use Snowflex macro.

@cevado, no I am using the real name of the app that it is started in the real config. Those are just placeholder values.

Hi! I’m afraid this won’t be too helpful, but let’s see:

I have looked extensively, but did not find any similar error

Yes, the errors are difficult to read/reason about (we had some connection errors that would not allow re-connecting, so eventually the machine would just die). We were only able to prove that it happened by attaching a connection listener to the connection and cross referencing the logs in the odbc driver. Once a connection died it would not come back up.

As far as your issue:

Unixodbc 2.3.12 (installed through Homebrew following the Snowflex readme).

I’d check the snowflake docs (not the library’s) to make sure you have the correct ODBC driver installed: ODBC Driver | Snowflake Documentation

This is the best hunch I have at the moment :frowning: I say this because a lot has changed with the M1 and maybe that’s the issue? Maybe the version is not the right one? I’d check that first.

I’d open an issue on the Snowflex repo as well, as I bet the folks using it maybe have seen this issue before and know exactly how to handle it. The M1 has been around for a while now, I’m sure some folks that are using this have seen it before.

General thoughts on odbc

The ODBC route was difficult to use after a while. I’m not a fan of “here is a suggestion that changes all of your current workflow”. BUT, I was part of a project that dealt with this problem before and the result was moving away from odbc (last code commit in the erlang file was in 2010) and started using Snowflake’s api.

We have GitHub - HGInsights/avalanche: Avalanche is an Elixir Snowflake Connector built on top of the Snowflake SQL API v2. and even telemetry for it: GitHub - HGInsights/opentelemetry_avalanche: Telemetry handler that creates OpenTelemetry spans from Avalanche (Snowflake) query events.. Ever since the switch, it became a non-issue, we don’t talk about this anymore :slight_smile:

1 Like

Hey @pdgonzalez872,
thanks a lot for your reply, I think it is helping more than you could imagine.
There is a lot of insights, especially your thoughts and considerations on ODBC.

As this is not a requirement, I will gladly follow your suggestion and try out a setup using Avalanche.

I had planned to open an issue on the Snowflex repo, it might still be useful for others who cannot choose the non-ODBC route.

Thanks a lot!