Alternative .NET Client for Snowflake

Ilya Bystrov
Oct 13 · 5 min read
Image for post
Image for post

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

.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

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

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

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

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 = snowflakeClient.Query<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

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

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.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store