Share:

Дружба между SQL Server и ClickHouse. SQLCLR снова с нами

YPermitinв.NET

2025-02-18

#C#

#.NET

#SQLServer

#ClickHouse

#SQLCLR

Настоящая дружба правдива и отважна.
(с) Байрон Д.

Создаем дружбу между SQL Server и ClickHouse через SQLCLR (SELECT, BULK INSERT и любые другие операции).

Содержание

Интеграция - вечная задача

Мы живем в мире разнообразного ПО, написанного на разных языках, платформах, для разных ОС и выполняющего абсолютно (или почти) разные задачи. Таков наш путь, а значит и таковы задачи.

В таких условиях задача интеграции будет всегда актуальной. Не обошел этот вопрос стороной и интеграцию между такими СУБД как SQL Server и ClickHouse. Причин подружить эти две великолепные СУБД может быть много. Да даже организация массовых выгрузок из OLTP-базы на SQL Server в DWH на ClickHouse может быть весьма распространённой задачей.

Сегодня мы как раз этим делом и займемся, но обо всем по порядку. Сначала мы рассмотрим в общих чертах самые популярные пути, а после остановимся на одном из них немного подробней. Речь идет, конечно, об интеграции SQL Server с ClickHouse средствами расширения SQLCLR.

В статье мы пройдем весь путь от подготовки клиентской библиотеки и ее корректной установки в среде CLR для SQL Server, до установки расширения и его использования различными способами. Для тех, кто хочет сразу посмотреть на готовую библиотеку и примеры использования - прошу перейти к GitHub на страницу проекта.

Для любопытных - добро пожаловать в дальнейшее путешествие! Настало время погрузиться в перекрестный мир .NET / SQL Server / ClickHouse!

Раскройте все карты

Существуют различные пути реализовать интеграцию SQL Server с базами ClickHouse. Вот самые распространенные решения:

  • Выгрузка данных из SQL Server в файлы и их последующая загрузка в ClickHouse. Это могут быть обычные csv / tsv файлы, которые ClickHouse обрабатывает достаточно эффективно. Выгрузку из SQL Server можно выполнять средствами штатной утилиты BCP или любым другим удобным способом.
  • Linked Server - еще одно решение. Со стороны SQL Server можно подключить инстанс / базы ClickHouse в качестве Linked Server'а и работать с ней через обычные запросы T-SQL, но, к сожалению, с существенными ограничениями. Более подробную информацию можно найти здесь, в том числе пример настройки. Работа с ClickHouse при это будет выполняться через ODBC-драйвер, а обработку типов и вообще любой результат запроса нужно будет обрабатывать вручную.
  • Подключение ClickHouse к SQL Server через ODBC - еще один альтернативный способ интеграции. Можно делать запросы через ODBC-драйвер со стороны ClickHouse до SQL Server. Для этого можно либо использовать конструкцию:
    select * from odbc([СтрокаПодключенияКSQLServer],[Схема],[ИмяТаблицы])
    Либо создать таблицу с движком OBDC. Дополнительную информацию можно узнать в официальной документации.
  • Промежуточное ПО - можно создать свое или купить готовое ПО, которое выступит посредником в передаче данных между SQL Server и ClickHouse (в обоих направлениях). Тут уже все ограничено лишь воображением, готовыми техническими инструментами для выбранного языка / платформы, ну и, конечно же, бюджетом!

Но мы пойдем другим путем, чтобы получить большую гибкость в функционале и крутые фичи в виде операций BULK INSERT из SQL Server в ClickHouse! В прошлой статье "Расширение для SQL Server. Быстро и просто. SQLCLR снова в деле" мы уже рассмотрели процесс создания простого расширения SQLCLR для SQL Server. Так почему бы не пойти тем же путем и не организовать работу с ClickHouse из SQL Server этим же способом?

Задача эта не так проста, как кажется на первый взгляд. Ведь среда CLR, которую использует SQL Server, весьма ограничена в возможностях, а также базируется на устаревшем .NET Framework 4.x. Да что уж тут говорить, если даже не все стандартные сборки доступны для использования из этой среды.

Но, если очень захотеть, то все преграды можно обойти! Займемся же этим!

SQLCLR и мир легаси

Мы работаем с SQLCLR, среда выполнения которого имеет собственные ограничения. Какие? Их много, но нас интересуют следующие:

  • Использование среды выполнения .NET Framework 4.x - в конфигурационном файле sqlservr.exe.config, расположенном в каталоге исполняемых файлов SQL Server (например по пути "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe.config", который зависит от версии SQL Server), указывается версия .NET Framework 4.x:
    <?xml version ="1.0"?>
    <configuration>
        <startup useLegacyV2RuntimeActivationPolicy="true">
            <supportedRuntime version="v4.0" />
        </startup>
        <runtime>
            <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
                <dependentAssembly>
                  <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91"/>
                  <publisherPolicy apply="no"/>
                  <bindingRedirect oldVersion="11.0.0.0-15.0.0.0" newVersion="15.0.0.0"/>
                </dependentAssembly>
            </assemblyBinding>
          <disableCachingBindingFailures enabled="1" />
          <CodeHeapReserveForJumpStubs value="5" />
        </runtime>
    </configuration>
    То есть мы не можем использовать библиотеки и новые возможности современной версии платформы .NET (все, что начинается от .NET Core и до .NET 9+).
  • Нет возможности использовать библиотеки .NET Standard - это вытекает из предыдущего пункта, хоть и не очевидным образом. .NET Standard - это спецификация для всех реализаций .NET. И хотя спецификация версии 2.0 и ниже поддерживают .NET Framework 4.x, среда выполнения SQLCLR работы с .NET Standard не поддерживает. То есть мы не сможем использовать современные библиотеки .NET даже с поддержкой .NET Standard.
  • Ограниченная поддержка стандартных библиотек в среде SQLCLR - не все стандартные библиотеки .NET Framework поддерживаются в среде SQLCLR, а значит мы не можем просто так ссылаться на них. Вот список библиотек, которые по умолчанию нам доступы:
    • mscorlib.dll
    • CustomMarshalers.dll
    • Microsoft.VisualBasic.dll
    • Microsoft.VisualC.dll
    • System.Configuration.dll
    • System.Core.dll
    • System.Data.OracleClient.dll
    • System.Data.SqlXml.dll
    • System.Data.dll
    • System.Deployment.dll
    • System.Security.dll
    • System.Transactions.dll
    • System.Web.Services.dll
    • System.Xml.Linq.dll
    • system.Xml.dll
    • System.dll
    Мы можем подключать сторонние сборки и обойти это ограничение, чем мы далее и займемся. Более подробную информацию Вы можете найти в официальной документации.

Ограничения мы знаем, но как это влияет на нас? Первый важный шаг - это реализация клиентской библиотеки для отправки запросов в ClickHouse из среды .NET. Конечно, можно создать собственное решение на .NET Framework с нуля, но это долго. Очень долго. Поэтому возьмем готовую библиотеку ClickHouse.Client, но вот незадача - библиотека поддерживает работу из .NET Framework через .NET Standard. Но мы уже знаем, что SQLCLR такое не поддерживает.

Выход только один - взять исходный код библиотеки и перевести её полностью на .NET Framework 4.x (например, версию 4.8). Причем важно учитывать и зависимости, на которые ссылается библиотека. Они также не должны содержать ссылок на неподдерживаемые версии .NET.

На момент создания статьи была использована версия 7.9.1. Мы сделаем обзор основных шагов для адаптации библиотеки под .NET Framework, а финальную версию Вы можете сразу посмотреть в готовом проекте расширения SQLCLR для ClickHouse. Вот такие изменения были сделаны:

  • Было выполнено понижение версий библиотек из зависимостей, чтобы избавиться от ссылок на .NET Standard. Изменение версий существенное, но такова цена возврата к legacy:
    • Newtonsoft.Json - установлена версия 8.0.3 взамен System.Text.Json, которого для .NET Framework еще не существовало.
    • NodaTime - с версии 3.2.1 на 1.4.7, в которой имеется поддержка .NET Framework без поддержи современных реализаций .NET.
    Вот содержимое файла настроек зависимостей packages.config:
    <?xml version="1.0" encoding="utf-8"?>
    <packages>
      <package id="Newtonsoft.Json" version="8.0.3" targetFramework="net48" />
      <package id="NodaTime" version="1.4.7" targetFramework="net48" />
    </packages>

    Именно эти версии библиотек выбраны, потому что они самые последние из доступных, где есть только поддержка .NET Framework без ссылок на .NET Standard или более новые версии .NET.

  • Исключено использование новых возможностей платформы .NET:
    • record struct - убрано использование неподдерживаемой конструкции.
    • Переезд на Newtonsoft.Json - мы убрали System.Text.Json и установили Newtonsoft.Json, а значит нужно и изменить алгоритмы работы с JSON. Прмер файла с изменением.
      // Было
      return JsonSerializer.Deserialize<QueryStats>(value, SummarySerializerOptions);
      

      // Стало
      var queryStats = JsonConvert.DeserializeObject<QueryStats>(value);
      return queryStats;
    • ArrayPool - эх, пришлось отказаться от ArrayPool. Это пул переиспользуемых ресурсов для оптимизации потребления памяти. В .NET Framework об этих возможностях ничего неизвестно, а значит пришлось убрать его использование и вернуться к менее оптимальным алгоритмам. Больше всего пострадала пакетная обработка коллекций класса EnumerableExtensions.cs.
      public static IEnumerable<(T[], int)> BatchRented<T>(this IEnumerable<T> enumerable, int batchSize)
      {
          List<T> items = new List<T>();
      

          //var array = ArrayPool<T>.Shared.Rent(batchSize);
          int counter = 0;
      

          foreach (var item in enumerable)
          {
              //array[counter++] = item;
              counter++;
              items.Add(item);
      

              if (counter >= batchSize)
              {
                  yield return (items.ToArray(), counter);
                  //yield return (array, counter);
                  counter = 0;
                  //array = ArrayPool<T>.Shared.Rent(batchSize);
                  items = new List<T>();
              }
          }
          if (counter > 0)
              //yield return (array, counter);
              yield return (items.ToArray(), counter);
      }
      Класс используется при работе с операциями BULK INSERT, так что они станут менее оптимальными в части потребления ресурсов, но в целом все равно рабочими и эффективными.
    • Удалено использование стандартных классов логирования - убрали использование Microsoft.Extensions.Logging. Замену добавлять не стал. Относится к реализации ClickHouseConnection, вот файл этого класса.
    • И другие мелочи.
  • Включена поддержка C# 9.0 - это для поддержки некоторых новых функций из современного .NET, но в контексте .NET Framework. Например, это записи (records) и некоторые другие мелочи. В файле проекта ClickHouseClient.csproj это достигнуто добавлением строки:
    <LangVersion>9.0</LangVersion>
    Здесь Вы можете посмотреть файл полностью.
  • И другие мелкие изменения.

По большей части изменений не так уж и много. Вот она сила обратной совместимости .NET! Теперь у нас есть чистая версия библиотеки для .NET Framework 4 без каких-либо "подозрительных" зависимостей. А самое главное - совместимая со средой выполнения SQLCLR.

Но работа по погружению в мир legacy еще не окончена. Для работы с ClickHouse внутри библиотеки используется класс HttpClient из пространства имен System.Net.Http. Проблема в том, что в среде SQLCLR по умолчанию сборка с реализацией этого класса отсутствует. При попытке использования нашей адаптированной библиотеки мы получим ошибку вида:

Assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.
или
Assembly 'System.Net.Http' could not be installed because existing policy would keep it from being used.
То есть эта сборка просто недоступна по умолчанию!

Это значит, что нам нужно эту сборку зарегистрировать! Но не все так просто. Убедившись, что .NET Framework 4.х установлен на машине вместе со SQL Server, нужно выполнить следующие действия в контексте базы данных (пусть в примере будет база PowerSQLCLR), где потом будет установлено расширение SQLCLR:

-- Этап 1. Включаем поддержку SQLCLR для инстанса SQL Server и доверие для базы данных.
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  
ALTER DATABASE [PowerSQLCLR] SET TRUSTWORTHY ON;
GO


-- Этап 2. Подготавливаем сертификаты Microsoft для сборок .NET Framework.
-- Этот шаг необходим для подключения стандартных сборок .NET
-- к инстансу SQL Server. Для добавленного сертификата создаем
-- служебную учетную запись и разрешаем работать со сборками.
USE [master];


CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';
GO
CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];
GO 
GRANT UNSAFE ASSEMBLY TO [MS.NETcer];
GO
DENY CONNECT SQL TO [MS.NETcer]
GO
ALTER LOGIN [MS.NETcer] DISABLE
GO


-- Этап 3. Добавляем стандартные сборки .NET Framework в служебную базу.
-- Эти сборки необходимы для работы клиента ClickHouse.
USE [PowerSQLCLR];


CREATE ASSEMBLY [System.Net.Http]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;
GO


CREATE ASSEMBLY [System.Web]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
WITH PERMISSION_SET = UNSAFE;
GO
То есть мы выполнили следующие шаги:
  • Включили поддержку SQLCLR, если она еще не была включена.
  • Зарегистрировали сертификат стандартных сборок .NET Framework для последующего импорта этих сборок.
  • Добавили стандартные сборки System.Net.Http и System.Web для корректной работы класса HttpClient

Установленные сборки .NET в SQL Server

Теперь все готово для непосредственного создания расширения SQLCLR. Все, что нужно в мире legacy, мы подготовили.

Как пройти в библиотеку?

Вот мы вплотную подошли к непосредственному созданию библиотеки расширения SQLCLR, ведь все legacy-подготовки уже закончились. Мы не будем рассматривать базовые принципы создания расширений SQLCLR, ведь мы это делали уже в статье Расширение для SQL Server. Быстро и просто. SQLCLR снова в деле. Здесь мы сразу определимся, что собираемся реализовать.

Наше расширение будет содержать следующие методы:

  • ExecuteScalar - это скалярная функция, возвращающее первое значение из первой колонки по результатам выполнения запроса.
  • ExecuteSimple - это табличная функция, возвращающая таблицу с единственной колонкой ResultValue строкового типа, в котором содержатся значения произвольного запроса. Любой возвращаемый тип будет преобразован к строке. Если в первой колонке содержится кортеж ClickHouse с несколькими значениями, то значение будет преобразовано к строке JSON.
  • ExecuteStatement - это хранимая процедура для выполнения любого выражения без возвращаемых значений. Например, для создания таблицы в базе ClickHosue.
  • ExecuteToTempTable - выполнение произвольного запроса SELECT к ClickHouse с сохранением полученного результата во временную таблицу SQL Server.
  • ExecuteToGlobalTempTable - выполнение произвольного запроса SELECT к ClickHouse с сохранением полученного результата во временную ГЛОБАЛЬНУЮ таблицу SQL Server.
  • ExecuteBulkInsertFromTempTable - выполнение операции BULK INSERT в таблицу ClickHouse из подготовленного набора данных, которых хранится во временной таблице SQL Server.

Полную реализацию библиотеки расширения SQLCLR Вы можете посмотреть в проекте ClickHouseClient.Entry, а ниже мы пробежимся по основным моментам её реализации. Верхний уровень реализации находится в файле EntryClickHouseClient.cs.

Функция ExecuteScalar самая простая в реализации. В качестве параметров получаем строку подключения к ClickHouse и текст запроса, а результатом будет срока со значением из запроса.

/// <summary>
/// Функция для выполнения запроса к ClickHouse и получения скалярного значения
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="queryText">SQL-текст запроса для выполнения</param>
/// <returns>Результат запроса, представленный строкой</returns>
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlChars ExecuteScalar(SqlChars connectionString, SqlChars queryText)
{
    string connectionStringValue = new string(connectionString.Value);
    string queryTextValue = new string(queryText.Value);


    string resultAsString = string.Empty;


    using (var connection = new ClickHouseConnection(connectionStringValue))
    {
        var queryResult = connection.ExecuteScalarAsync(queryTextValue)
            .GetAwaiter().GetResult();


        resultAsString = queryResult.ToString();
    }


    return new SqlChars(resultAsString);
}

Мы используем адаптированную ранее библиотеку ClickHouse.Client и ее класс ClickHouseConnection. Все просто, как дважды два!

Функция ExecuteSimple также простая в реализации, как и предыдущая. В качестве параметров получаем строку подключения к ClickHouse и текст запроса, а результатом будет коллекция строк, представляющих значения первой колонки запроса, преобразованные к строке (представление строки для примитивных типов и JSON для сложных типов).

/// <summary>
/// Функция для выполнения простого запроса.
/// 
/// Возвращается только первая колонка из результата запроса в виде строки.
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="queryText">SQL-текст запроса для выполнения</param>
/// <returns>Набор результата запроса (только первая колонка в виде строки)</returns>
[SqlFunction(
    FillRowMethodName = "ExecuteSimpleFillRow",
    SystemDataAccess = SystemDataAccessKind.Read,
    DataAccess = DataAccessKind.Read)]
public static IEnumerable ExecuteSimple(SqlChars connectionString, SqlChars queryText)
{
    List<ExecuteSimpleRowResult> resultRows = new List<ExecuteSimpleRowResult>();


    string connectionStringValue = new string(connectionString.Value);
    string queryTextValue = new string(queryText.Value);


    using (var connection = new ClickHouseConnection(connectionStringValue))
    {
        using (var reader = connection.ExecuteReaderAsync(queryTextValue)
            .GetAwaiter().GetResult())
        {
            while (reader.Read())
            {
                resultRows.Add(new ExecuteSimpleRowResult()
                {
                    ResultValue = ConvertTypeToSqlCommandType(reader.GetValue(0)).ToString()
                });
            }
        }
    }


    return resultRows;
}
public static void ExecuteSimpleFillRow(object resultRow, out SqlChars rowValue)
{
    var resultRowObject = (ExecuteSimpleRowResult)resultRow;
    rowValue = new SqlChars(resultRowObject.ResultValue);
}

Отличие от функции ExecuteScalar в обработке результата запроса, где каждую строку мы преобразуем к объекту класса ExecuteSimpleRowResult:

namespace YPermitin.SQLCLR.ClickHouseClient.Models
{
    public class ExecuteSimpleRowResult
    {
        public string ResultValue { get; set; }
    }
}
Преобразования значений к строке выполняем через метод ConvertTypeToSqlCommandType, суть которого получение представления для примитивных типов и JSON-строки для сложных типов. Приводить здесь листинг не буду, вы можете посмотреть реализацию воооот здесь.

Хранимая процедура ExecuteStatement еще проще в реализации, чем предыдущие примеры. Также принимает строку подключения к ClickHouse и текст запроса, но при выполнении не считывает результат, т.к. предназначена в основном для отправки DDL/DML-команд.

/// <summary>
/// Выполнение команды к ClickHouse без получения результата
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="queryText">SQL-текст команды</param>
[SqlProcedure]
public static void ExecuteStatement(SqlChars connectionString, SqlChars queryText)
{
    string connectionStringValue = new string(connectionString.Value);
    string queryTextValue = new string(queryText.Value);


    using (var connection = new ClickHouseConnection(connectionStringValue))
    {
        var queryResult = connection.ExecuteStatementAsync(queryTextValue)
            .GetAwaiter().GetResult();
    }
}

Без комментариев, ведь нового тут ничего нет.

Две хранимые процедуры ExecuteToTempTable и ExecuteToGlobalTempTable нужны для выполнения запроса SELECT к ClickHouse с целью сохранения результата в указанные и подготовленные заранее временные таблицы. Процедуры принимают строку подключения к ClickHouse, текст запроса к Clickhouse и имя временной таблицы для сохранения результата. Различий между ними немного:

  • ExecuteToTempTable - предназначен для работы с локальными временными таблицами, имена которых начинаются с #, а их жизненный цикл привязан к соединению, которое эту таблицу создало.
  • ExecuteToGlobalTempTable - предназначен для работы с глобальными временными таблицами, имена которых начинаются с ##, а их жизненный цикл не привязан к соединениям. То есть таблицы могут использоваться разными соединениями. Также этот вариант процедуры принимает четвертый параметр со строкой подключения SQL Server. Если указать этот параметр, то для сохранения результата запроса во временную таблицу будут использования операции BULK INSERT на стороне SQL Server, что ускорит работу. Так реализоано, потому что контекстное подключение не позволяет выполнять BULK-операции. Можно передать пустую строку, тогда будет использованы обычные конструкции INSERT.

Вот листинг двух этих процедур.

/// <summary>
/// Выполнение запроса к ClickHouse с сохранением результата во временную локальную таблицу
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="queryText">SQL-текст команды</param>
/// <param name="tempTableName">Имя временной таблицы для сохранения результата</param>
[SqlProcedure]
public static void ExecuteToTempTable(SqlChars connectionString, SqlChars queryText, SqlChars tempTableName)
{
    string tempTableNameValue = new string(tempTableName.Value);


    if (!tempTableNameValue.StartsWith("#", StringComparison.InvariantCultureIgnoreCase))
    {
        throw new Exception("Temp table name should begin with # (local temp table)");
    }
    if (tempTableNameValue.StartsWith("##", StringComparison.InvariantCultureIgnoreCase))
    {
        throw new Exception("Temp table name should begin with # (local temp table). Global temp table with ## not supported by this method.");
    }


    ExecuteToTempTableInternal(connectionString, queryText, tempTableName, _emptyString);
}


/// <summary>
/// Выполнение запроса к ClickHouse с сохранением результата во временную глобальную таблицу
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="queryText">SQL-текст команды</param>
/// <param name="tempTableName">Имя временной таблицы для сохранения результата</param>
/// <param name="sqlServerConnectionString">Строка подключения к SQL Server для выполнения BULK INSERT.
/// Если передана пустая строка, то вставка во временную таблицу будет выполняться обычными инструкциями INSERT.
/// </param>
[SqlProcedure]
public static void ExecuteToGlobalTempTable(SqlChars connectionString, SqlChars queryText, SqlChars tempTableName, SqlChars sqlServerConnectionString)
{
    string tempTableNameValue = new string(tempTableName.Value);


    if (!tempTableNameValue.StartsWith("##", StringComparison.InvariantCultureIgnoreCase))
    {
        throw new Exception("Temp table name should begin with ## (global temp table).");
    }


    ExecuteToTempTableInternal(connectionString, queryText, tempTableName, sqlServerConnectionString);
}

Как вы можете заметить, вся фактическая реализация метода находится в ExecuteToTempTableInternal. Листинг этого метода приводить здесь не будем, Вы можете посмотреть его здесь. Суть его в следующем:

  • Проверяем наличие временной таблицы.
  • Открываем подключение к ClickHouse и выполняем запрос с обработкой результата.
  • Если доступно использование BULK INSERT к SQL Server, то загружаем полученные записи из ClickHouse во временную таблицу таким образом. В противном случае выполняем команды INSERT для заполнения временной таблицы.

После в скрипте T-SQL можно работать с полученными данными, выполняя запросы к этой временной таблице. Примеры работы будут представлены ниже.

И последняя хранимая процедура ExecuteBulkInsertFromTempTable для отправки данных из временной таблицы SQL Server в таблицу ClickHouse через BULK INSERT. Процедура принимает три параметра:

  • Строка подключения ClickHouse.
  • Имя временной таблицы SQL Server.
  • Имя таблицы ClickHouse для загрузки данных.

В библиотеке ClickHouse.Client уже была реализация операции BULK INSERT в таблицу ClickHouse, ее мы и используем!

/// <summary>
/// Операция массовой вставки данных из временной таблицы SQL Server
/// в таблицу ClickHouse
/// </summary>
/// <param name="connectionString">Строка подключения к ClickHouse</param>
/// <param name="sourceTempTableName">Имя временной таблицы с исходными данными</param>
/// <param name="destinationTableName">Имя таблицы ClickHouse для вставки данных</param>
[SqlProcedure]
public static void ExecuteBulkInsertFromTempTable(SqlChars connectionString, SqlChars sourceTempTableName, SqlChars destinationTableName)
{
    string connectionStringValue = new string(connectionString.Value);
    string sourceTempTableNameValue = new string(sourceTempTableName.Value);
    string destinationTableNameValue = new string(destinationTableName.Value);




    using (SqlConnection sqlConnection = GetSqlConnection())
    {
        if (sqlConnection.State != ConnectionState.Open)
        {
            sqlConnection.Open();
        }


        List<object[]> rowsForInsert = new List<object[]>();


        string tempTableSelectQuery =
            @"
SELECT * FROM " + sourceTempTableNameValue + @"
";
        using (SqlCommand tempTableReader = new SqlCommand(tempTableSelectQuery, sqlConnection))
        {
            tempTableReader.CommandType = CommandType.Text;


            using (var reader = tempTableReader.ExecuteReader())
            {
                while (reader.Read())
                {
                    object[] rowValues = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var columnValue = reader.GetValue(i);
                        rowValues[i] = columnValue;
                    }
                    rowsForInsert.Add(rowValues);
                }
            }
        }


        using (var connection = new ClickHouseConnection(connectionStringValue))
        {
            using (var bulkCopy = new ClickHouseBulkCopy(connection)
            {
                DestinationTableName = destinationTableNameValue,
                BatchSize = 100000,
                MaxDegreeOfParallelism = 1
            })
            {
                bulkCopy.InitAsync().GetAwaiter().GetResult();
                bulkCopy.WriteToServerAsync(rowsForInsert).GetAwaiter().GetResult();
            }
        }
    }
}

Весь смысл процедуры в простых шагах:

  • Считываем все записи из указанной временной таблицы.
  • Создаем коллекцию записей для отправки в ClickHouse.
  • Вызываем операцию BULK INSERT в ClickHouse.

И всё!

Все функции и процедуры готовы! Всё наше расширение SQLCLR готово к установке и использованию!

Напомню, что исходный код рабочей версии расширения можно найти здесь.

Установка и настройка

Библиотека готова, пора перейти к установке и настройке. В Visual Studio соберем проект в конфигурации Release и получим следующие необходимые файлы:

  • NodaTime.dll
  • Newtonsoft.Json.dll
  • ClickHouseClient.dll
  • ClickHouseClient.Entry.dll
Все эти файлы скопируем в каталог, например: "C:\Share\SQLCLR".
Файлы сборки расширения

Теперь нужно установить и настроить сборку. Ранее мы показывали скрипты по установке стандартных сборок .NET Framework. Теперь мы рассмотрим скрипт со всеми шагами установки, в том числе по созданию объектов функций и процедур, через которые будет вызов методов расширения SQLCLR.

Вот полный скрипт со всеми шагами установки и настройки расширения:

-- Этап 1. Включаем поддержку SQLCLR для инстанса SQL Server и доверие для базы данных.
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  
ALTER DATABASE [PowerSQLCLR] SET TRUSTWORTHY ON;
GO


-- Этап 2. Подготавливаем сертификаты Microsoft для сборок .NET Framework.
-- Этот шаг необходим для подключения стандартных сборок .NET
-- к инстансу SQL Server. Для добавленного сертификата создаем
-- служебную учетную запись и разрешаем работать со сборками.
USE [master];


CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';
GO
CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];
GO 
GRANT UNSAFE ASSEMBLY TO [MS.NETcer];
GO
DENY CONNECT SQL TO [MS.NETcer]
GO
ALTER LOGIN [MS.NETcer] DISABLE
GO


-- Этап 3. Добавляем стандартные сборки .NET Framework в служебную базу.
-- Эти сборки необходимы для работы клиента ClickHouse.
USE [PowerSQLCLR];


CREATE ASSEMBLY [System.Net.Http]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;
GO


CREATE ASSEMBLY [System.Web]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
WITH PERMISSION_SET = UNSAFE;
GO


-- Этап 4. Удаляем объекты расширения SQLCLR клиента ClickHouse,
-- если они уже существуют. Ниже они будут созданы заново.
USE [PowerSQLCLR];
DROP FUNCTION IF EXISTS [dbo].[fn_CHExecuteScalar];
DROP FUNCTION IF EXISTS [dbo].[fn_CHExecuteSimple];
DROP FUNCTION IF EXISTS [dbo].[fn_CHGetCreateTempDbTableCommand];
DROP PROCEDURE IF EXISTS [dbo].[sp_CHExecuteToTempTable];
DROP PROCEDURE IF EXISTS [dbo].[sp_CHExecuteToGlobalTempTable];
DROP PROCEDURE IF EXISTS [dbo].[sp_CHExecuteStatement];
DROP PROCEDURE IF EXISTS [dbo].[sp_CHExecuteBulkInsertFromTempTable];
DROP ASSEMBLY IF EXISTS [ClickHouseClient.Entry];
DROP ASSEMBLY IF EXISTS [ClickHouseClient];
GO


-- Этап 5. Создаем сборку клиента ClickHouse и расширения SQLCLR в служебной базе,
-- а также все объекты для работы с ней.
-- ВНИМАНИЕ!!! Путь к файлу DLL нужно актуализировать под ваше окружение.
USE [PowerSQLCLR];


CREATE ASSEMBLY [ClickHouseClient]
	FROM 'C:\Share\SQLCLR\ClickHouseClient.dll'
	WITH PERMISSION_SET = UNSAFE;
GO


CREATE ASSEMBLY [ClickHouseClient.Entry]
	FROM 'C:\Share\SQLCLR\ClickHouseClient.Entry.dll'
	WITH PERMISSION_SET = UNSAFE;
GO


CREATE FUNCTION [fn_CHExecuteScalar](
	@connectionString nvarchar(max),
	@queryText nvarchar(max)
) 
RETURNS nvarchar(max)   
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteScalar];   
GO


CREATE FUNCTION [dbo].[fn_CHExecuteSimple](
	@connectionString nvarchar(max),
	@queryText nvarchar(max)
)  
RETURNS TABLE (
	[ResultValue] nvarchar(max)
)
AS   
EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteSimple];
GO


CREATE FUNCTION [fn_CHGetCreateTempDbTableCommand](
	@connectionString nvarchar(max),
	@queryText nvarchar(max),
	@tempTableName nvarchar(max)
) 
RETURNS nvarchar(max)   
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[GetCreateTempDbTableCommand];   
GO


CREATE PROCEDURE [dbo].[sp_CHExecuteStatement]
(
	@connectionString nvarchar(max),
	@queryText nvarchar(max)
)
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteStatement];
GO


CREATE PROCEDURE [dbo].[sp_CHExecuteToTempTable]
(
	@connectionString nvarchar(max),
	@queryText nvarchar(max),
	@tempTableName nvarchar(max)
)
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteToTempTable];
GO


CREATE PROCEDURE [dbo].[sp_CHExecuteToGlobalTempTable]
(
	@connectionString nvarchar(max),
	@queryText nvarchar(max),
	@tempTableName nvarchar(max),
	@sqlServerConnectionString nvarchar(max)
)
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteToGlobalTempTable];
GO


CREATE PROCEDURE [dbo].[sp_CHExecuteBulkInsertFromTempTable]
(
	@connectionString nvarchar(max),
	@sourceTempTableName nvarchar(max),
	@destinationTableName nvarchar(max)
)
AS EXTERNAL NAME [ClickHouseClient.Entry].[YPermitin.SQLCLR.ClickHouseClient.Entry.EntryClickHouseClient].[ExecuteBulkInsertFromTempTable];
GO

Скрипт работает в 5 этапов:

  • Включаем поддержку SQLCLR для инстанса SQL Server и доверие для базы данных.
  • Подготавливаем сертификаты Microsoft для сборок .NET Framework.
  • Добавляем стандартные сборки .NET Framework в служебную базу.
  • Удаляем объекты расширения SQLCLR клиента ClickHouse.
  • Создаем сборку клиента ClickHouse и расширения SQLCLR в служебной базе, а также все объекты для работы с ней.

Теперь мы можем работать с ClickHouse из T-SQL! Время пришло!

Запросы, запросы, запросы

Вот несколько примеров, где мы из T-SQL выполним несколько запросов к ClickHouse. Воспользуемся всеми методами, которые мы реализовали выше.

Получаем версию ClickHouse.

SELECT [PowerSQLCLR].[dbo].[fn_CHExecuteScalar](
		-- Строка подключения
		'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
		-- текст запроса
		'select version()')


-- Пример результата:
-- 25.2.1.2434

Пример выполнения простого запроса, который возвращает одну колонку. Для возврата нескольких колонок используется кортеж, сериализованный в JSON. В T-SQL полученные элементы JSON разбираются конструкциями SQL Server.

select
	JSON_VALUE(d.ResultValue, '$.Item1') AS [DatabaseName],
	JSON_VALUE(d.ResultValue, '$.Item2') [Engine],
	JSON_VALUE(d.ResultValue, '$.Item3') AS [DataPath],
	CAST(JSON_VALUE(d.ResultValue, '$.Item4') AS uniqueidentifier) AS [UUID]
from [PowerSQLCLR].[dbo].fn_CHExecuteSimple(
	-- Строка подключения
	'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
	-- Запрос
	'
SELECT
	tuple(name, engine, data_path,uuid)
FROM `system`.`databases`
'
) d

Создаем временную таблицу и сохраняем в нее результат запроса.

IF(OBJECT_ID('tempdb..#logs') IS NOT NULL)
    DROP TABLE #logs;
CREATE TABLE #logs
(
    [EventTime] datetime2(0),
    [Query] nvarchar(max),
    [Tables] nvarchar(max),
    [QueryId] uniqueidentifier
);


EXECUTE [PowerSQLCLR].[dbo].[sp_CHExecuteToTempTable]
		-- Строка подключения
		'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
		-- Текст запроса
		'
select
	event_time,
	query,
	tables,
	query_id
from `system`.query_log
limit 1000
',
		-- Имя временной таблицы для сохранения результата
		'#logs';


-- Считываем результат
SELECT * FROM #logs

Создаем ГЛОБАЛЬНУЮ временную таблицу и сохраняем в нее результат запроса.

IF(OBJECT_ID('tempdb..##logs') IS NOT NULL)
    DROP TABLE ##logs;
CREATE TABLE ##logs
(
    [EventTime] datetime2(0),
    [Query] nvarchar(max),
    [Tables] nvarchar(max),
    [QueryId] uniqueidentifier
);


EXECUTE [PowerSQLCLR].[dbo].[sp_CHExecuteToGlobalTempTable]
		-- Строка подключения
		'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
		-- Текст запроса
		'
select
	event_time,
	query,
	tables,
	query_id
from `system`.query_log
limit 1000
',
		-- Имя временной таблицы для сохранения результата
		'##logs',
		-- Строка подключения к SQL Server для BULK INSERT.
		-- Строка контекстного подключения для этого не подходит.
		'server=localhost;database=master;trusted_connection=true';


-- Считываем результат
SELECT * FROM ##logs
При использовании глобальной таблицы можно достичь более высокой производительности за счет переноса в нее данных через BULK INSERT (если указана строка подключения к SQL Server).

Запуск произвольной команды без возвращения результата.

EXECUTE [PowerSQLCLR].[dbo].[sp_CHExecuteStatement]
	-- Строка подключения
	'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
	-- Запрос
	'
CREATE TABLE IF NOT EXISTS SimpleTable
(
	Id UInt64,
	Period datetime DEFAULT now(),
	Name String
)
ENGINE = MergeTree
ORDER BY Id;
'

Выполнение операции BULK INSERT в таблицу ClickHouse. Отправляется набор данных из временной таблицы.

SET NOCOUNT ON;


-- Создаем временную таблицу
IF(OBJECT_ID('tempdb..#rowsForInsert') IS NOT NULL)
	DROP TABLE #rowsForInsert;
CREATE TABLE #rowsForInsert
(
    [Id] bigint,
    [Period] datetime2(0),
    [Name] nvarchar(max)
);


-- Заполняем набор данных
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
INSERT INTO #rowsForInsert
SELECT 
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	GETDATE(),
	'Value ' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) as nvarchar(max))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1


-- Отправляем в ClickHouse
EXECUTE [PowerSQLCLR].[dbo].[sp_CHExecuteBulkInsertFromTempTable]
	-- Строка подключения
	'Host=yy-comp;Port=8123;Username=default;password=;Database=default;',
	-- Имя временной таблицы с исходными данными
	'#rowsForInsert',
	-- Имя таблицы в ClickHouse для BULK INSERT
	'SimpleTable'
'

Вот и все примеры! Дело сделано, пути назад нет :)

Немного мыслей

Это было длинное путешествие, спасибо что прошли его вместе со мной!

Мы еще раз стали свидетелями мощи технологии SQLCLR. Несмотря на то, что сама технология уже перешла в статус legacy, т.к. больше не планируется её развития, но это не значит смерти SQLCLR. Ведь на его базе создано так много решений, что поддерживать его для совместимости в SQL Server будут еще многие десятилетия. Ломать - не строить! Microsoft будет беречь совместимость с этой технологией, а в будущем может и реализует ей замену в связке с современными версиями .NET (но это не точно).

Дружба SQL Server + ClickHouse открывают большие возможности по интеграции двух СУБД и не только это! Мы можем открыть новые возможности использования SQL Server в приложениях, которые используют эту СУБД как основное хранилище данных. К этой теме мы еще вернемся в будущем.

Оцените логотип новой СУБД в результате объединения функций SQL Server и ClickHouse :)))

SQL Server + ClickHouse

А на сегодня интернета достаточно! Удачи в делах и до скорых встреч!

Y

YPermitin

.NET, TSQL, DevOps, 1C:Enterprise

Developer, just developer.

Поделиться

Другие статьи

Дружба между SQL Server и ClickHouse. SQLCLR снова с нами
Дружба между SQL Server и ClickHouse. SQLCLR снова с нами
Контроль дубликатов процессов в C# (.NET)
Контроль дубликатов процессов в C# (.NET)
Контроль дочерних процессов с помощью C++ и WinAPI
Контроль дочерних процессов с помощью C++ и WinAPI

Все статьи от автора: YPermitin

Copyright © 2025 Убежище инженера