Всем привет!
В этой статье я расскажу, как я сделал свой собственный .NET клиент для работы со Snowflake, чем он лучше официальных библиотек, как устроен и как им пользоваться.
Для работы со Snowflake в .NET приложении до недавнего времени у разработчиков было всего два варианта: ODBC драйвер и Snowflake .NET Connector. Я попробовал оба, но ни тот ни другой мне не понравились.
Мотивация
Процесс установки и настройки ODBC драйвера для Snowflake на Windows, мягко говоря, не самый простой и понятный. Правда, это относится ко всем ODBC драйверам, не только к Snowflake. У меня ушла пара часов чтобы наконец заставить его работать. К сожалению, его нужно установить и настроить на всех машинах, где запускается ваше приложение — т.е. это дополнительная зависимость. Из интереса я провел небольшое сравнение производительности со Snowflake Connector, и оказалось, что ODBC драйвер заметно медленней.
.NET Snowflake Connector (Snowlfake.Data) — это официальная библиотека с открытым исходным кодом, распространяется в виде NuGet пакета. Одна из существенных проблем, которые у неё есть — в ней не реализован пул соединений (connection pooling). Т.е. каждый раз, когда вы создаете новое соединение с БД, инициализируется новое соединение. Если вы следуете best practice и официальной документации, то ваш типичный код для чтения данных из Snowflake при использовании Snowflake.Data выглядит примерно так:
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM table;";
var reader = cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
Без пула соединений в этом примере произойдет три реквеста к Snowflake: создание новой сессии, выполнение SQL запроса и закрытие сессии. Т.е. по 3 реквеста на каждый SQL запрос. Это не очень эффективно и негативно влияет на производительность.
Как вы могли заметить из примера выше, Snowflake.Data — это ADO.NET коннектор. Разработчики официальной библиотеки почему-то решили реализовать ADO.NET интерфейсы, несмотря на то что Snowflake — это не традиционная база данных. Это скорее нативно-облачная база данных с REST API. Да, внутри этот коннектор использует REST API, но при этом реализует интерфейсы ADO.NET, прикидываясь классической БД для разработчика.
Реализация ADO.NET интерфейсов поверх REST API добавляет сложности в разработку, потому что это совершенно разные интерфейсы. Разрабатывая такой коннектор придется иметь дело с ограничениями, которые накладываются интерфейсами ADO.NET. Например, вам нужно реализовать передачу параметра в REST API запросе, но в ADO.NET попросту нет подходящей опции или фичи (или вам придется использовать существующие фичи не совсем естественным образом). С другой стороны, многие фичи ADO.NET просто не могут быть реализованы, потому что в REST API нет соответствующих фич. Вот почему в коде коннектора там много методов, которые просто выбрасывают NotImplementedException
.
На мой взгляд более естественным выбором была бы реализация клиента для REST API. Немного погуглив, я не нашел ничего похожего и решил написать свой клиент: Snowflake.Client.
Создание сессии
Новая сессия в Snowflake.Client создается очень просто: создайте новый клиент, и новая сессия инициализируется автоматически:
// Creates new client and initializes new session
var snowflakeClient = new SnowflakeClient("user", "password", "account", "region");
На данный момент поддерживается только базовая аутентификация с помощью пары логин/пароль. Для удобства есть несколько вариантов конструктора SnowflakeClient, в них можно передать дополнительные параметры. Например, можно передать свои настройки для маппинга. Созданная сессия будет использоваться для всех последующих вызовов, сделанных с помощью этого клиента. Информацию о текущей сессии можно посмотреть в свойстве Session
у клиента.
Выполнение запросов
В Snowflake.Client есть несколько методов для выполнения SQL запросов в Snowflake. Вот несколько примеров:
// Executes query and maps response data to your class
var employees = snowflakeClient.Query<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");
// Executes query and returns value of first cell as string result
string useRoleResult = snowflakeClient.ExecuteScalar("USE ROLE ACCOUNTADMIN;");
// Executes query and returns affected rows count
int affectedRows = snowflakeClient.Execute("INSERT INTO EMPLOYEES Title VALUES (?);", "Dev");
Как вы могли заметить, синтаксис очень похож на Dapper. Так что если вы работали с Dapper, то вы уже знаете, как пользоваться Snowflake.Client.
Параметризация запросов
Для безопасного выполнения SQL запросов, в составе которых есть данные, вводимые пользователем, используются параметры. Давайте посмотрим, как можно сделать параметризированный запрос с помощью Snowflake.Data:
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO table VALUES (?)";
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = 10;
p1.DbType = DbType.Int32;
cmd.Parameters.Add(p1);
var count = cmd.ExecuteNonQuery();
conn.Close();
}
Да, оригинальный интерфейс ADO.NET очень громоздкий. Во многих реализациях есть методы-обертки для сокращения кода, например AddWithValue()
. К сожалению, в реализации Snowflake ничего такого нет, и код очень быстро раздувается.
Snowflake REST API поддерживает два типа параметров:
- позиционные — знак вопроса (
?
) - именованные — имя параметра с двоеточием (
:name
)
Snowflake.Client поддерживает оба типа. Позиционные параметры используются со встроенными типами (string
, int
, DateTime
и др.):
// Positional binding with question marks:
var a = snowflakeClient.QueryScalar("SELECT COUNT(*) FROM EMPLOYEES WHERE TITLE = ?", "Programmer");
var b = snowflakeClient.QueryScalar("SELECT COUNT(*) FROM EMPLOYEES WHERE ID = ?", 3);
var c = snowflakeClient.QueryScalar("SELECT COUNT(*) FROM EMPLOYEES WHERE ID IN (?,?)", new int[] { 1, 2 });
Именованные параметры используются с классами, в том числе анонимными:
// Named binding with colons:
var d = snowflakeClient.QueryScalar("SELECT COUNT(*) FROM EMPLOYEES WHERE TITLE = :Title", new Employee() { Title = "Programmer" });
var e = snowflakeClient.QueryScalar("SELECT COUNT(*) FROM EMPLOYEES WHERE TITLE = :Title", new { Title = "Programmer" });
Маппинг
Snowflake REST API возвращает данные запроса в виде двух массивов: колонки и строки. Примерно так (сокращено для читабельности):
"data":{
"rowtype":[
{
"name":"created_on",
"type":"timestamp_ltz"
},
{
"name":"name",
"type":"text"
}
],
"rowset":[ "1579896098.349", "Eleanor" ]
}
Такой формат может быть полезен в некоторых ситуациях, но обычно мы хотим иметь данные в виде объектов. Для задач такого рода обычно используется Activator.CreateInstance()
и рефлексия. Однако, имея на руках JSON строку гораздо проще использовать JSON сериализатор. Осталось только преобразовать исходную JSON строку в другую, которая будет представлять возвращённый объект. Для примера выше она будет выглядеть вот так:
{ “created_on”: “1579896098.349”, “name”: “Eleanor” }
.
Пример запроса с маппингом:
var employees = snowflakeClient.Query<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");
Естественно, при этом Snowflake.Client делает конвертацию типов Snowflake в типы .NET. Чтобы изменить поведение маппера (т.е. JSON-сериализатора) можно передать кастомные опции (JsonSerializerOptions
) в конструктор клиента.
Другие фичи
Как я упоминал выше — сырой формат данных (колонки и строки) может быть полезен в некоторых ситуациях. Так что я добавил метод, который возвращает данные в нетронутом виде: QueryRaw()
.
Snowflake.Client в отличии от Snowflake.Data, поддерживает флаг describeOnly
для запросов. Если он установлен в true
, то в ответе будут только колонки, без самих данных. Это может быть полезно, если вы хотите получить информацию о колонках: тип данных, точность, размер и другие свойства.
Заключение
Работы ещё много, но уже сейчас Snowflake.Client готов для базового использования. Надеюсь, в будущем он сможет составить конкуренцию официальному коннектору.
P.S. На гитхабе создан специальный тред для идей и фидбека. Пулл реквесты и баг-репорты также приветствуются!
Dansoid
Спрошу на всякий случай. Есть у нас в linq2db исью: какие провайдеры надобы доделать https://github.com/linq2db/linq2db/issues/1014#issuecomment-601037110
Если есть желание, думаю за недельку другую вы бы написали DataProvider к linq2db и заодно получили бы плюшки его возможностей.
fixer_m Автор
Желания нет и вряд ли появится. Но я посмотрю как-нибудь, сколько может занять реализация провайдера. Разработчики Snowflake вряд ли будут сами делать поддержку, а на своем проекте мы пишем запросы руками — их не так много и все они простые.
Dansoid
Никто и не думает о разработчиках Snowflake. В первую очередь это понадобится тем кому не хотется на каждый чих писать запрос, а просто написать типобезопасно без строчек (по следам ваших запросв)
И не заботиться о маппинге
Параметризацией запросов или literal inlining с эскейпингом займется сам linq2db.
SQL у Snowflake весьма стандартен, написание такого провайдара это не rocket science. Все упирается в то что мы не можем такое поставить на CI — нету у Snowflake бесплатных опций.