Alternative .NET Client for Snowflake

Ilya Bystrov
Oct 13, 2020 · 5 min read

TL;DR: I have created .NET client library for Snowflake REST API and it can be used as an alternative to the official ADO.NET connector.

Until recently, to connect to Snowflake from your .NET application, you had only a couple of options: ODBC Driver and Snowflake ADO.NET Connector. I have tried both, but I liked neither and decided to build my own.

Motivation

Snowflake ODBC Driver has a confusing configuration process on Windows. To be fair, this actually applies to all ODBC drivers, not just the Snowflake one. I have spent a few hours to make it actually work. What’s worse — you have to install and configure it on each machine where you want to run your application, i.e., this is an additional dependency. Just out of curiosity, I have compared its performance with the Snowflake Connector, and it appears that the ODBC driver is notably slower.

.NET Snowflake Connector (Snowflake.Data) is an open-source official library distributed via NuGet package. One of the biggest issues it has — it doesn’t implement connection pooling. I.e. when you are creating a new connection, it doesn’t try to re-use the existing connection — it actually creates a new one. If you follow best practices and official guide your code to execute some query with Snowflake.Data will look like this:

Without connection pooling in this example, it actually makes three requests to Snowflake: create a new session, execute a query, and close this session. Right, 3 round-trips for each single SQL query. This is not very effective and affects performance for sure.

As you can notice from the example above Snowflake.Data is an ADO.NET connector. Snowflake developers decided to implement ADO.NET interfaces, despite Snowflake not being a traditional on-premises database. It is a native cloud database with REST API. Under the hood, this ADO.NET connector actually uses a REST API. While this decision enables the official connector to work with the existing ecosystem of ADO.NET applications, it can be more cumbersome to use in scenarios that do not require ADO.NET.

Implementing ADO.NET interfaces on top of the REST API adds complexity, because these interfaces are very different. As a connector developer, you will have to deal with ADO.NET interface restrictions. For example, you want to pass some specific REST API query parameter, but you can’t because there is no such option or feature in ADO.NET interfaces (or you have to use existing features unnaturally). As well as some ADO.NET features cannot be implemented because REST APIs don’t have related features. That’s why the connector code base has a lot of methods, which raises NotImplementedException. I’m not even mentioning that original ADO.NET syntax is way too verbose.

In my opinion, a more natural choice would be to implement a client library for REST API. I did some quick research, but haven’t found such libraries. So I decided to implement my own: Snowflake.Client.

Creating a New Session

Creating a new Snowflake session in Snowflake.Client is pretty straightforward. Just create a new client object and it will initialize a new session.

Currently, it supports only basic Snowflake authentication by username and password. For convenience, there are few overloads that accept more parameters. For example, you can specify custom mapping options. As you expect — a created session will be used for all the following requests made with this client. You can examine current session information in snowflake Client.Session property.

Executing a Query

Snowflake.Client provides a few ways to execute a SQL query in Snowflake. Here are some examples:

As you can notice public API methods resembles Dapper. So if you have ever used Dapper — you already know how to use Snowflake.Client.

Binding Parameters

If you want to construct SQL queries using user input safely — you have to use SQL parameters. Let's take a look at how this is done with Snowflake.Data:

Yeah, original ADO.NET interfaces are so verbose that many implementations have handy wrapper methods like AddWithValue(). Unfortunately Snowflake.Data doesn’t have any of these, so this code becomes bloated very quickly.

Snowflake REST API supports two placeholder formats for parameter binding:

  • Positional — with a ? placeholders
  • Named — parameter name prefixed with a : char

Snowflake.Client supports both of them.

Positional binding can be used with built-in types (string, int, DateTime, etc) or with a collection of a built-in type like this:

To use named parameters you can pass any class instance, including anonymous types.

Mapping Result

Snowflake REST API returns query data in two arrays: columns and rows. Like this (shortened for readability):

This format can be useful in some cases, but usually, we want to get the instance(s) of some type. For this kind of task, you would use things like Activator.CreateInstance() and Reflection. However, since we already have JSON string it will be much easier to use some JSON serializer. The only thing left is to transform the raw response into another JSON string that represents the returned object. For the example above it will look like this: { “created_on”: “1579896098.349”, “name”: “Eleanor” }.

Here is how you can get mapped result from Snowflake:

var employees = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

Of course Snowflake.Client does data type conversion from Snowflake custom types to .NET types behind the scenes. To change deserialization behavior you can pass custom serializer options (JsonSerializerOptions) in client constructor.

Other Features

As I mentioned — raw response data (columns and rows) can be useful in some cases. So I have added a separate method to expose this raw data: QueryRawResponseAsync().

Snowflake.Client supports query flag describeOnly. If it’s set to true the response will contain only columns information without rows data. This can be useful if you want to get table columns info.

Closing

There is still a lot to work to do on Snowflake.Client, but it is ready for basic usage. I think eventually it can become the best option to connect to Snowflake from .NET applications.

P.S. If you are interested in this project — you can help by leaving your suggestions and comments in a special General Feedback issue on GitHub. Pull requests are welcomed as well.

References

Snowflake

Articles for engineers, by engineers.