search

fixer-m / snowflake-db-net-client

Snowflake .NET Client
Apache License 2.0
51 stars 14 forks source link
api-client api-wrapper client dotnet rest-api rest-api-client snowflake snowflake-client snowflake-db snowflake-net

readme

NuGet Targets License

Snowflake.Client

.NET client for Snowflake REST API.
Provides API to execute SQL queries and map response to your models.
Read my blog post about the ideas behind it.

Installation

Add nuget package Snowflake.Client to your project: 

PM> Install-Package Snowflake.Client

Main features

Basic usage

// Creates new client
var snowflakeClient = new SnowflakeClient("user", "password", "account", "region");

// Executes query and maps response data to "Employee" class
var employees = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Executes query and returns raw response from Snowflake (rows, columns and query information)
var queryRawResponse = await snowflakeClient.QueryRawResponseAsync("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Executes query and returns value of first cell as string result
string useRoleResult = await snowflakeClient.ExecuteScalarAsync("USE ROLE ACCOUNTADMIN;");

// Executes query and returns affected rows count
int affectedRows = await snowflakeClient.ExecuteAsync("INSERT INTO EMPLOYEES Title VALUES (?);", "Dev");

Comparison with Snowflake.Data

Official Snowflake.Data connector implements ADO.NET interfaces (IDbConnection, IDataReader etc), so you have to work with it as with usual database, however under the hood it actually uses Snowflake REST API. In contrast Snowflake.Client is designed as REST API client wrapper with convenient API. Read more about it.

Improvements in Snowflake.Client vs Snowflake.Data:

New features in Snowflake.Client:

Missing features in Snowflake.Client:

Parameter binding

Snowflake supports two placeholder formats for parameter binding:

Both formats are supported. You can use positional placeholders to bind values of "simple" types (like Int, String or DateTime). To bind named parameters you can use classes, structs, anonymous types or dictionary. See examples below. 

// Positional placeholder, any "simple" type
var result1 = await snowflakeClient.QueryAsync<Employee>
              ("SELECT * FROM EMPLOYEES WHERE TITLE = ?", "Programmer");

// Positional placeholders, any IEnumerable<T>
var result2 = await snowflakeClient.QueryAsync<Employee>
              ("SELECT * FROM EMPLOYEES WHERE ID IN (?, ?, ?)", new int[] { 1, 2, 3 });

// Named placeholders, any custom class or struct
var result3 = await snowflakeClient.QueryAsync<Employee>  
              ("SELECT * FROM EMPLOYEES WHERE TITLE = :Title", new Employee() { Title = "Programmer" });

// Named placeholders, any anonymous class
var result4 = await snowflakeClient.QueryAsync<Employee>     
              ("SELECT * FROM EMPLOYEES WHERE TITLE = :Title", new { Title = "Junior" });

// Named placeholders, any IDictionary<T>
var result5 = await snowflakeClient.QueryAsync<Employee>
              ("SELECT * FROM EMPLOYEES WHERE TITLE = :Title", new Dictionary<string, string> {{ "Title", "Programmer" }});

Mapping basics

Use QueryAsync<T> method to get response data automatically mapped to your model (T): 

// Executes query and maps response data to "Employee" class
IEnumerable<Empolyee> employees = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Your model
public class Employee
{ 
    public int Id { get; set; }
    public float? Rating { get; set; }
    public bool? IsFired { get; set; }
    public string FirstName { get; set; }
    public string[] ContactLinks { get; set; } // supports arrays and lists
    public EmplyeeInfo Info { get; set; } // supports custom json ojects ("object" and "variant")
    public DateTimeOffset HiredAt { get; set; } // DateTimeOffset for "timestamp_ltz" and "timestamp_tz"
    public DateTime FiredAt { get; set; } // DateTime for "date", "time" and "timestamp_ntz"
    public byte[] Image { get; set; } // bytes array/list for "binary"
}

Internally it uses System.Text.Json to deserialize Snowflake data to your model. It uses default deserialize behavior, except PropertyNameCaseInsensitive is set to true, so your properties names don't have to be in the exact same case as column names in your tables.
You can override this behavior by providing custom JsonSerializerOptions. You can pass it in SnowflakeClient constructor or you can set it directly via SnowflakeDataMapper.SetJsonMapperOptions(jsonSerializerOptions).

Advanced usage

You may want to get raw response from Snowflake, for example, to get QueryID or some other information.
In this case you can use mapper explicitly: 

// Executes query and returns raw response from Snowflake (rows, columns and query information)
var queryDataResponse = await snowflakeClient.QueryRawResponseAsync("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Maps Snowflake rows and columns to your model (internally uses System.Text.Json)
var employees = SnowflakeDataMapper.MapTo<Employee>(queryDataResponse.Columns, queryDataResponse.Rows);

You can override internal http client. For example, this can be used to bypass SSL check:

var handler = new HttpClientHandler
{
    SslProtocols = SslProtocols.Tls12,
    CheckCertificateRevocationList = false, 
    ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true // i.e. bypass cert validation
};

var httpClient = new HttpClient(handler);
var snowflakeClient = new SnowflakeClient("user", "password", "account", "region");
snowflakeClient.SetHttpClient(httpClient);

Release notes

0.4.4

0.4.3

0.4.2

0.4.1

0.4.0

0.3.9

0.3.8

0.3.7

0.3.6

0.3.5

0.3.4

0.3.3

0.3.2

0.3.1

0.3.0

0.2.4

0.2.3

0.2.2