Запросы к ClickHouse из MS SQL / Хабр

Иногда может возникнуть необходимость запрашивать данные из ClickHouse в реальном времени при выполнении запросов или хранимых процедур на MS SQL. Последний "из коробки" представляет для этого средства только через Linked Server, OLE DB for ODBC и ClickHouse ODBC.

Если запрос возвращает немного данных - это вполне рабочий путь. Но если запрос к ClickHouse возвращает десятки тысяч строк, то производительность связки OLE DB for ODBC и ClickHouse ODBC не выдерживает никакой критики.

С этой проблемой мы столкнулись в хранимой процедуре отчета на запросе, возвращающем ~70 тыс. строк с ~40 полями. Напрямую на ClickHouse из DBEaver этот запрос выполнялся за 0.7 секунд. На PostgreSQL через pg_clickhouse (clickhouse_fdw) - 1.5 секунды. А на MS SQL через OLE DB for ODBC и ClickHouse ODBC - почти 40 секунд!

В очень упрощенном виде, запрос выглядел, примерно, так:

SELECT * INTO #temp_table
  FROM OPENQUERY(ClickHouse, 
    'SELECT id, date, [...] -- еще 37 полей
     FROM some_schema.some_table
     WHERE date = ( 
       SELECT TOP 1 date 
       FROM some_schema.some_table 
       WHERE date <= '2026-01-01' 
     ORDER BY date DESC );'
  );

Для того, чтобы максимально исключить влияние ODBC, мы попытались сделать запрос с одним полем таким образом:

SELECT * INTO #rows
FROM OPENQUERY(ClickHouse,
  'SELECT formatRow(''Values'', id, date, [...] -- еще 37 полей
     ) AS Val
   WHERE date = ( 
     SELECT TOP 1 date 
     FROM some_schema.some_table 
     WHERE date <= '2026-01-01' 
   ORDER BY date DESC );');

Такой запрос выполнялся чуть дольше, чем секунду. Вот только все попытки в дальнейшем превратить эти строки в поля показали, что быстрее чем за 40 секунд результат всё равно не получается. Одна из попыток:

SELECT * INTO #t
FROM OPENQUERY(ClickHouse, 
  'SELECT id, date, [...] -- еще 37 полей
   FROM some_schema.some_table
   LIMIT 0;');

DECLARE @sql_str nvarchar(max);
;WITH Numbered AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Val)/20 AS rn, Val FROM #rows ),
Grouped AS (
  SELECT 'INSERT INTO #t VALUES '
    +STRING_AGG(CONVERT(nvarchar(max),Val),',')+';' AS Ins
  FROM Numbered
  GROUP BY rn )
SELECT @sql_str='BEGIN ТRANSACTION;'
  +STRING_AGG(CONVERT(nvarchar(max),Ins),'')+'COMMIT;'
FROM Grouped;
EXECUTE (@sql_str);

Формирование динамического запроса в @sql_str происходило практически мгновенно, а вот его исполнение EXECUTE занимало больше одной минуты.

Варианты решения

В первую очередь в голову приходит наиболее прямой, но и наиболее сложный путь. Воспользоваться родным драйвером ClickHouse через CLR. Кроме сложности, это ещё и опасный путь, так как ошибки в CLR могут приводить к нарушениям работоспособности самого MS SQL. Если же проблема требует незамедлительного решения, как было у нас, то этот вариант или отвергается или откладывается до лучших времён в качестве технического долга.

Следующий пришедший в голову вариант попроще и понадёжней. Можно написать внешний сервис, к которому обращаться запросом, например, по REST из хранимой процедуры. Кроме очевидных минусов обращения из хранимой процедуры к внешнему сервису, по срокам этот подход тоже не подходил, так как такой отчёт, да ещё и не один, нужен был заказчику, как обычно, ещё вчера.

Поэтому был выбран третий, наиболее простой в реализации вариант: пусть ClickHouse сам заливает результат запроса в MS SQL.

Проектирование выбранного варианта

К сожалению, ClickHouse не умеет напрямую работать с MS SQL, а вставка больших объемов данных через ODBC и free_tds тоже не балует производительностью. Если точнее, то вставка происходит почти таким же образом, как в последнем запросе и занимает не намного меньше времени.

Какой самый быстрый способ вставки в MS SQL? Естественно BULK INSERT или INSERT BULK. Первым способом можно воспользоваться непосредственно вызывая утилиту bcp. Для второго нужно писать код, например, на C#. Причем, так как требуется поддержка не одного запроса, а любых запросов, возвращающих произвольный набор полей, то в сжатые сроки написать этот код нам показалось мало реальным.

Поэтому остановились не на самом лучшем или самом оптимальном, а на самом быстро и просто реализуемом варианте - с утилитой bcp.

Реализация

Для начала на сервере ClickHouse необходимо установить mssql-tools. Описывать его установку тут не буду, так как это уже сделано Microsoft

Затем, для поддержки встроенной (доменной) авторизации на MS SQL Server нам потребовалось установить клиент Kerberos. В нашем случае ClickHouse был на сервере с Rocky Linux 10. Для Debian-based дистрибутивов установка будет несколько отличаться.

sudo dnf install krb5-workstation krb5-libs

Затем надо было выполнить конфигурацию Kerberos в файле /etc/krb5.conf. Например:

includedir /etc/krb5.conf.d/

[logging]
  default = FILE:/var/log/krb5libs.log
  kdc = FILE:/var/log/krb5kdc.log
  admin_server = FILE:/var/log/kadmind.log

[libdefaults]
  default_realm = MY-DOMAIN.RU
  rdns = false
  dns_lookup_kdc = true
  dns_lookup_realm = true
  ticket_lifetime = 1d
  dns_lookup_realm = false
  renew_lifetime = 7d
  pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
  default_ccache_name = KEYRING:persistent:%{uid}

[realms]
  CORP.SUEK.RU = {
    kdc = my-domain-controller.my-domain.ru:88
    default_domain = my-domain.ru
    kpasswd_server = my-domain-controller.my-domain.ru
  }

[domain_realm]
  .my-domain.ru = MY-DOMAIN.RU
  my-domain.ru = MY-DOMAIN.RU

Далее был использован скрипт /home/clickhouse/run/create_keytab.sh для создания и обновления keytab файла:

#!/bin/bash 
read -p "Enter username for MY-DOMAIN.RU (case sensitive!): " CRED 
echo -n "Password: " 
read -s NEW_PASS 
cp $CRED.keytab $CRED.keytab.bak 
printf "%b" "addent -password -p $CRED@MY-DOMAIN.RU -k 1 -e aes256-cts-hmac-sha1-96\n$NEW_PASS\nwrite_kt $CRED.keytab\nexit" | ktutil 
exit

Не забываем о

sudo chmod 700 /home/clickhouse/run/create_keytab.sh
sudo chown clickhouse:clickhouse /home/clickhouse/run/create_keytab.sh

Предположим, что этим скриптом мы создали файл /home/clickhouse/run/my-mssql-domain-account.keytab под аккаунтом clickhouse, под которым у нас запущен ClickHouse

sudo -u clickhouse /home/clickhouse/run/create_keytab.sh

указав в ответах на запросы скрипта доменный аккаунт, под которым мы будем обращаться к MS SQL сервер, например, my-mssql-domain-account, и его пароль.

Теперь нам нужен скрипт для получение билетика Kerberos. Создадим его в файле /home/clickhouse/run/kinit_ticket.sh

#!/bin/sh 
sudo -u clickhouse /bin/kinit -F -p -r 7d -k -t /home/clickhouse/run/my-mssql-domain-account.keytab my-mssql-domain-account@MY-DOMAIN.RU

Затем дадим права на него только clickhouse и создадим символическую ссылку, чтобы этот скрипт запускался раз в сутки

sudo chmod 700 /home/clickhouse/run/kinit_ticket.sh
sudo chown clickhouse:clickhouse /home/clickhouse/run/kinit_ticket.sh
ln -s /home/clickhouse/run/kinit_ticket.sh /etc/cron.daily

Рекомендую запустить вручную скрипт /home/clickhouse/run/kinit_ticket.sh и убедиться, что билетик получен

sudo -u clickhouse /bin/klist

Так же нам потребуется скрипт /home/clickhouse/run/renew_ticket.sh для продления билетика

#!/bin/sh 
sudo -u clickhouse /bin/kinit -R

Аналогично даём права на него и создаем символическую ссылку, но уже для запуска скрипта каждый час

sudo chmod 700 /home/clickhouse/run/renew_ticket.sh
sudo chown clickhouse:clickhouse /home/clickhouse/run/renew_ticket.sh
ln -s /home/clickhouse/run/renew_ticket.sh /etc/cron.hourly

Рекомендую запустить вручную скрипт /home/clickhouse/run/renew_ticket.sh и так же, как было описано выше, убедиться, что билетик был обновлён и его время начала действия обновилось после предыдущего запуска klist

На MS SQL следует добавить доменный аккаунт my-mssql-domain-account с правами public. Больше никаких прав ему не потребуется.

А вот аккаунт, который был прописан в настройках ClickHouse ODBC на MS SQL Server, потребует в ClickHouse дополнительных прав CREATE TEMPORARY TABLE. Например, для аккаунта db_link_mssql:

GRANT CREATE TEMPORARY TABLE ON *.* TO db_link_mssql;

Далее будем считать, что конфигурацию ClickHouse по-умолчанию не меняли и пользовательские скрипты находятся в директории /var/lib/clickhouse/user_scripts/

В этой директории мы создаем скрипт bcp_in.sh для запуска ранее установленного bcp в составе пакета mssql-tools

#!/bin/bash 
EXIT_STATUS=0 
tmp_file=$(mktemp /tmp/clickhouse_bcp_to_mssql.XXXXXXXXX); 
sed 's/\t\\N/\t/g' > $tmp_file 
/opt/mssql-tools18/bin/bcp $2 in $tmp_file -c -b 10000000 -a 16384 -u -T -S $1 || EXIT_STATUS=$? 
rm $tmp_file 
echo $EXIT_STATUS 
exit $EXIT_STATUS

Опять не забываем дать права на этот скрипт только clickhouse

sudo chmod 700 /var/lib/clickhouse/user_scripts/bcp_in.sh
sudo chown clickhouse:clickhouse /var/lib/clickhouse/user_scripts/bcp_in.sh

Использование скрипт�� подразумевается через табличную функцию executable(). Скрипт bcp_in.sh принимает два параметра. Первый — доменное имя MS SQL сервер, второй — имя таблицы, в которую следует загрузить данные.

Функция executable() принимает пять параметров. Первый — имя скрипта с указанием обеих параметров. Второй — формат возвращаемого рекордсета. Третий — структура возвращаемого рекордсета. Четвертый — запрос, результат которого поступит на стандартный ввод вызываемого скрипта. Пятый — необязательные опции.

Так как clickhouse формирует поток для executable(), в котором NULL значения заменяются на \N, а bcp такого не воспринимает, то пришлось заменять сочетание табуляции и \N на табуляцию, чтобы получить вместо \N пустые строки. Такой подход накладывает ограничение на результат запроса: в первом поле недопустимы NULL. Если же так сформировать запрос не удается, то первое поле следует сделать из фиктивной константы.

Теперь на MS SQL можем проверить, как работает наша конструкция. Создаём глобальную временную таблицу с уникальным именем и структурой, точно соответствующей нужному запросу:

DECLARE @sql_str nvarchar(max); 
SELECT @sql_str = 'DROP TABLE IF EXISTS ##temp_table_'
  +CONVERT(nvarchar(max),@@SPID)+'; 
  SELECT * INTO ##temp_table_'
  +CONVERT(nvarchar(max),@@SPID)+'
  FROM OPENQUERY(ClickHouse, 
    ''SELECT id, date, [...] -- еще 37 полей
      FROM some_schema.some_table
      LIMIT 0;'');';
EXEC (@sql_str);

А затем выполним запрос, который заполнит созданную глобальную временную таблицу:

SELECT @sql_str = 'SELECT * 
  FROM executable(''bcp_in.sh '+REPLACE(@@SERVERNAME,'\','\\') 
    +' ##temp_table_'+CONVERT(nvarchar(max),@@SPID) 
    +''', TabSeparatedRaw, ''Status String'', (
      SELECT id, date, [...] -- еще 37 полей
      FROM some_schema.some_table
      WHERE date = ( 
        SELECT TOP 1 date 
        FROM some_schema.some_table 
        WHERE date <= ''2026-01-01'' 
        ORDER BY date DESC )
    ));'
EXEC (@sql_str);

В нашем случае этот запрос выполняется чуть более 2 секунд, что, конечно, хуже, чем запрос напрямую с ClickHouse и даже чем запрос через clickhouse_fdw на PostgreSQL, но на порядок лучше, чем запрос через ODBC.

Еще раз хочу обратить внимание, что это наверняка не самый лучший способ выполнения запросов к ClickHouse из MS SQL в реальном времени, в том числе, и из хранимой процедуры. Но этот способ реализуем буквально за минуты и даёт неплохой результат.

Спасибо за прочтение и конструктивную критику.