vendasta / sequel-snowflake Goto Github PK

Sequel adapter for Snowflake

License: MIT License

Ruby 100.00%

database ruby sequel snowflake snowflakedb yesware

sequel-snowflake's Introduction

sequel-snowflake

An adapter to connect to Snowflake databases using Sequel. This provides proper types for returned values, as opposed to the ODBC adapter.

Installation

Add this line to your application's Gemfile:

gem 'sequel-snowflake'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install sequel-snowflake

You'll also need unixODBC (if on Linux/macOS) and the appropriate Snowflake ODBC driver in order to use this adapter. Follow the Snowflake documentation on their ODBC Driver here before proceeding.

Usage

When establishing the connection, specify :snowflake as the adapter to use.

DB = Sequel.connect(adapter: :snowflake,
                    drvconnect: conn_str)

Testing

In order to run specs, you'll need a Snowflake account. A connection string should be provided as an environment variable SNOWFLAKE_CONN_STR. For example, on macOS, our connection string would resemble:

DRIVER=/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib;
SERVER=<account>.<region>.snowflakecomputing.com;
DATABASE=<database>;
WAREHOUSE=<warehouse>;
SCHEMA=<schema>;
UID=<user>;
PWD=<password>;
CLIENT_SESSION_KEEP_ALIVE=true;

The test will create a temporary table on the specified database to run tests on, and this will be taken down either via the after(:each) blocks or when the connection is closed.

GitHub Actions

We have two workflows included in this project:

Ruby (ruby.yml): This runs the specs for this gem against Ruby 3.0 and 3.1. Note that this requires the secret SNOWFLAKE_CONN_STR to be set (see above for example connection string), as we need to connect to Snowflake to run tests. These specs will be run for every pull request, and is run after every commit to those branches.
Ruby Gem (gem-push.yml): This builds and pushes this gem to RubyGems, acting only on successful pushes to the main branch. Note that this workflow requires a RUBYGEMS_AUTH_TOKEN secret to be set to authenticate with RubyGems.

Contributing

Fork it ( https://github.com/vendasta/sequel-snowflake/fork )
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create a new Pull Request

sequel-snowflake's People

Contributors

Stargazers

Watchers

Forkers

benalavi

sequel-snowflake's Issues

How to execute copy into <location> command ?

 db = Sequel.connect(adapter: :snowflake, drvconnect: conn_str)
 db.execute("copy into 'azure://xxxxxxxx.blob.core.windows.net/test/test.csv' from
 ( select col1  , col2 from table1 where col1 in (1,2,3) )
 storage_integration = INTEGRATION_NAME file_format = (format_name = export_csv_v1) HEADER = TRUE")

Getting Error code: #<Sequel::DatabaseError: ODBC::Error: 0 (2003) S>

@kwong-yw - Can you please help here. Can execute be used to run this command ?

Sequel - ODBC - error - when executing copy-into

Working with Variant columns

Hello @kwong-yw,

Thank you for working on this Sequel adapter. It is a big help!

I am tasked with copying one Snowflake database over to another. Some of the tables have VARIANT columns. When I retrieve those rows using this gem, they come in as String.

I thought to convert the variant column to JSON before inserting it into the new table. However, it's breaking (as described below)

Is that the right approach? (To convert a variant string to JSON?)

If not, what would you suggest?

If so, I get the following user experience:

from_table.each do |row|
  row[:data] = JSON.parse(row[:data])
  to_table.insert(row)
end

Sequel::DatabaseError: ODBC::Error: XX000 (603) SQL execution internal error:
Processing aborted due to error 300002:1440237446; incident 5745559.
from /Users/volt/.rbenv/versions/3.1.1/lib/ruby/gems/3.1.0/gems/sequel-5.56.0/lib/sequel/adapters/odbc.rb:56:in `do'
Caused by ODBC::Error: XX000 (603) SQL execution internal error:
Processing aborted due to error 300002:1440237446; incident 5745559.
from ....../sequel-5.56.0/lib/sequel/adapters/odbc.rb:56:in `do'

P.S. - This issue is loosely related to the discussion I posted in sequel
when trying to sort out explain during my debugging process.

connection pool size

after connecting,

DB = Sequel.odbc CONNECTION_PARAMETERS

DB.pool.size returns 1.

Setting "max_connections" in the parameters doesn't change this. Not sure how to change the pool size using sequel directly.

Does your adapter allow us to set connection pool size?

Sequel::Snowflake::Dataset#fetch aborts abruptly

Using Ruby 2.7.2 (also tried 3.2.1 & 3.2.2) with the latest Snowflake ODBC driver (3.1.3) on Mac OS X Sonoma 14.0. Using the latest sequel-snowflake version (also tried 2.0.0).

ODBC driver connection working properly per docs using iodbctest (https://docs.snowflake.com/developer-guide/odbc/odbc).

When running the following in a Ruby console, this aborts the console abruptly. No exceptions thrown. Few lines in the logs I've found below.

db = Sequel.connect(adapter: :snowflake, drvconnect: connection_str)
db.fetch("select current_version();").all

Connection string:

"driver=/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib;LogLevel=5;server=server.snowflakecomputing.com;uid=username;pwd=password"

Notable lines from logs...

snowflake_odbc_connection.log:

2023-11-15T16:53:45.441 ERROR 4527 Simba::ODBC::Statement::SQLFetchScroll: [Snowflake][ODBC] (10480) Fetch type not supported, as fetch orientation is not compatible with current settings.

snowflake_odbc_generic.log:

2023-11-15T16:53:45.294 INFO  4527 Simba::Snowflake::SFDriver::appendLatestVersion: Your driver version (3.1.3) is different from the latest driver version supported by Snowflake (2.21.1)

Any idea on solution or further debugging? Thanks in advance!