Драйвер ODBC

Документация по ODBC-драйверу ClickHouse

ODBC-драйвер ClickHouse предоставляет соответствующий стандарту интерфейс для подключения ODBC-совместимых приложений к ClickHouse. Он реализует ODBC API и позволяет приложениям, BI-инструментам и средам сценариев выполнять SQL-запросы, получать результаты и взаимодействовать с ClickHouse с помощью привычных механизмов.

Драйвер взаимодействует с сервером ClickHouse через HTTP-протокол, который является основным протоколом, поддерживаемым во всех развертываниях ClickHouse. Это позволяет драйверу стабильно работать в самых разных средах, включая локальные установки, управляемые облачные сервисы и среды, где доступен только HTTP-доступ.

Исходный код драйвера доступен в репозитории ClickHouse-ODBC на GitHub.

Для лучшей совместимости мы настоятельно рекомендуем обновить сервер ClickHouse до версии 24.11 или более поздней.

Этот драйвер находится в активной разработке. Некоторые возможности ODBC могут быть еще не полностью реализованы. Текущая версия сосредоточена на обеспечении базового подключения и основной функциональности ODBC, а дополнительные возможности запланированы для будущих релизов.

Ваши отзывы очень ценны и помогают определять приоритеты новых возможностей и улучшений. Если вы столкнетесь с ограничениями, отсутствующей функциональностью или неожиданным поведением, пожалуйста, поделитесь своими наблюдениями или запросами на новые возможности через трекер задач по адресу https://github.com/ClickHouse/clickhouse-odbc/issues

​

Установка в Windows

Последнюю версию драйвера можно найти по адресу https://github.com/ClickHouse/clickhouse-odbc/releases/latest. Там можно скачать и запустить MSI-установщик, а затем выполнить простые шаги установки.

​

Тестирование

Проверить драйвер можно с помощью этого простого PowerShell-скрипта. Скопируйте текст ниже, укажите свой URL, имя пользователя и пароль, а затем вставьте его в командную строку PowerShell — после выполнения $reader.GetValue(0) должна отобразиться версия вашего сервера ClickHouse.

$url = "http://127.0.0.1:8123/"
$username = "default"
$password = ""
$conn = New-Object System.Data.Odbc.OdbcConnection("`
    Driver={ClickHouse ODBC Driver (Unicode)};`
    Url=$url;`
    Username=$username;`
    Password=$password")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "select version()"
$reader = $cmd.ExecuteReader()
$reader.Read()
$reader.GetValue(0)
$reader.Close()
$conn.Close()

​

Параметры конфигурации

Ниже перечислены наиболее часто используемые параметры для установления соединения с ODBC-драйвером ClickHouse. Они охватывают основные параметры аутентификации, поведение соединения и параметры обработки данных. Полный список поддерживаемых параметров доступен на GitHub-странице проекта https://github.com/ClickHouse/clickhouse-odbc.

• Url: Указывает полный HTTP(S)-адрес конечной точки сервера ClickHouse. Сюда входят протокол, хост, порт и необязательный путь.
• Username: Имя пользователя, используемое для аутентификации на сервере ClickHouse.
• Password: Пароль, связанный с указанным именем пользователя. Если он не указан, драйвер подключается без аутентификации по паролю.
• Database: База данных, используемая по умолчанию для соединения.
• Timeout: Максимальное время (в секундах), в течение которого драйвер ожидает ответа от сервера, прежде чем прервать запрос.
• ClientName: Пользовательский идентификатор, отправляемый на сервер ClickHouse как часть метаданных клиента. Полезен для трассировки или различения трафика от разных приложений. Этот параметр будет частью заголовка User-Agent в HTTP- запросах, создаваемых драйвером.
• Compression: Включает или отключает HTTP-сжатие для полезной нагрузки запросов и ответов. При включении оно может уменьшить расход пропускной способности и повысить производительность при работе с большими результирующими наборами.
• SqlCompatibilitySettings: Включает настройки запросов, благодаря которым ClickHouse ведет себя больше как традиционная реляционная база данных. Это полезно, когда запросы автоматически генерируются сторонними инструментами, например, Power BI. Такие инструменты обычно не учитывают некоторые специфические особенности ClickHouse и могут создавать запросы, которые приводят к ошибкам или неожиданным результатам. Подробнее см. в разделе Настройки ClickHouse, используемые параметром конфигурации SqlCompatibilitySettings .

Ниже приведено несколько примеров полной строки соединения, передаваемой драйверу для настройки соединения.

• Сервер ClickHouse, установленный локально в экземпляре WSL

Driver={ClickHouse ODBC Driver (Unicode)};Url=http://localhost:8123/;Username=default

• Экземпляр ClickHouse Cloud.

Driver={ClickHouse ODBC Driver (Unicode)};Url=https://you-instance-url.gcp.clickhouse.cloud:8443/;Username=default;Password=your-password

​

Интеграция с Microsoft Power BI

Вы можете использовать драйвер ODBC, чтобы подключить Microsoft Power BI к серверу ClickHouse. Power BI предлагает два варианта подключения: универсальный коннектор ODBC и коннектор ClickHouse; оба входят в стандартную установку Power BI.

Оба коннектора используют ODBC, но различаются по возможностям:

• Коннектор ClickHouse (рекомендуется) Использует ODBC на уровне реализации, но поддерживает режим DirectQuery. В этом режиме Power BI автоматически генерирует SQL-запросы и получает только те данные, которые нужны для каждой визуализации или операции фильтрации.

• Коннектор ODBC Поддерживает только режим Import. Power BI выполняет запрос, заданный пользователем (или выбирает всю таблицу), и импортирует весь результирующий набор в Power BI. При последующих обновлениях весь датасет импортируется заново.

Выбирайте коннектор в зависимости от вашего сценария использования. DirectQuery лучше всего подходит для интерактивных панелей мониторинга с большими датасетами. Выбирайте режим Import, если вам нужны полные локальные копии данных.

Дополнительные сведения об интеграции Microsoft Power BI с ClickHouse см. на странице документации ClickHouse по интеграции с Power BI.

​

Настройки совместимости с SQL

ClickHouse использует собственный диалект SQL и в некоторых случаях ведет себя иначе, чем другие базы данных, такие как MS SQL Server, MySQL или PostgreSQL. Нередко это оказывается преимуществом, поскольку ClickHouse предлагает более удобный синтаксис, упрощающий использование его возможностей.

Однако драйвер ODBC часто используется в средах, где запросы генерируются сторонними инструментами, такими как Power BI, а не пишутся пользователями вручную. Такие запросы обычно опираются на минимальное подмножество стандарта SQL. В таких случаях отличия ClickHouse от стандарта SQL могут приводить к поведению, отличному от ожидаемого, а также к неожиданным результатам или ошибкам. драйвер ODBC предоставляет дополнительный параметр конфигурации SqlCompatibilitySettings, который включает определенные настройки запроса, чтобы поведение ClickHouse в большей степени соответствовало стандарту SQL.

​

Настройки ClickHouse, включаемые параметром конфигурации SqlCompatibilitySettings

В этом разделе описано, какие настройки изменяет драйвер ODBC и почему.

cast_keep_nullable

По умолчанию ClickHouse не позволяет преобразовывать типы Nullable в типы, не допускающие NULL. Однако многие BI-инструменты не различают типы, допускающие и не допускающие NULL, при преобразовании типов. Поэтому нередко можно увидеть запросы, подобные приведённому ниже, сгенерированные BI-инструментами:

SELECT sum(CAST(value, 'Int32'))
FROM values

По умолчанию если столбец value допускает NULL, этот запрос завершится ошибкой с сообщением:

DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(__table1.value :: 2,
'Int32'_String :: 1) -> CAST(__table1.value, 'Int32'_String) Int32 : 0'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN)

Включение cast_keep_nullable изменяет поведение CAST так, что он сохраняет признак Nullable у своих аргументов. Это приближает поведение ClickHouse к поведению других баз данных и стандарту SQL для такого типа преобразований.

prefer_column_name_to_alias

ClickHouse позволяет ссылаться на выражения в том же списке SELECT по их псевдонимам. Например, этот запрос позволяет избежать повторений, и его проще писать:

SELECT
    sum(value) AS S,
    count() AS C,
    S / C
FROM test

Эта возможность широко используется, но в других базах данных псевдонимы в том же списке SELECT обычно так не разрешаются, и такие запросы обычно приводят к ошибке. Проблемы особенно заметны, когда псевдоним совпадает с именем столбца. Например:

SELECT
    sum(value) AS value,
    avg(value)
FROM test

Какое value должна агрегировать функция avg(value)? По умолчанию ClickHouse выбирает псевдоним, фактически превращая это во вложенную агрегацию, а это не то, чего ожидает большинство инструментов.

Само по себе это редко вызывает проблемы, но некоторые BI-инструменты генерируют запросы с подзапросами, которые повторно используют псевдонимы столбцов. Например, Power BI часто генерирует запросы, подобные следующему:

SELECT
    sum(C1) AS C1,
    count(C1) AS C2
FROM
(
    SELECT sum(value) AS C1
    FROM test
    GROUP BY group_index
) AS TBL

При обращении к C1 может возникать следующая ошибка:

Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function sum(C1) AS C1 is found
inside another aggregate function in query. (ILLEGAL_AGGREGATION)

Другие базы данных обычно не разрешают псевдонимы таким образом на том же уровне и вместо этого рассматривают C1 как столбец из подзапроса. Чтобы сохранить схожее поведение в ClickHouse и позволить таким запросам выполняться без ошибок, драйвер ODBC включает prefer_column_name_to_alias.

В большинстве случаев включение этих настроек не должно вызывать проблем. Однако пользователи, у которых параметр readonly установлен в 1, не могут изменять какие-либо настройки даже для запросов SELECT. Для таких пользователей включение SqlCompatibilitySettings приведёт к ошибке. В следующем разделе объясняется, как сделать так, чтобы этот параметр конфигурации работал для пользователей с доступом только для чтения.

​

Как обеспечить работу настроек совместимости SQL для пользователей с доступом только для чтения

При подключении к ClickHouse через драйвер ODBC с включенным параметром SqlCompatibilitySettings пользователь с параметром readonly, установленным в 1, столкнется с ошибкой, поскольку драйвер пытается изменить настройки запроса:

Code: 164. DB::Exception: Cannot modify 'cast_keep_nullable' setting in readonly mode. (READONLY)
Code: 164. DB::Exception: Cannot modify 'prefer_column_name_to_alias' setting in readonly mode. (READONLY)

Это происходит потому, что пользователям в режиме только для чтения нельзя изменять настройки даже для отдельных запросов SELECT. Исправить это можно несколькими способами.

Вариант 1. Установить readonly в 2

Это самый простой вариант. Значение readonly, установленное в 2, позволяет изменять настройки, сохраняя для пользователя режим только для чтения.

ALTER USER your_odbc_user MODIFY SETTING
    readonly = 2

В большинстве случаев самый простой и рекомендуемый способ решить эту проблему — установить readonly в значение 2. Если этот вариант вам не подходит, используйте второй способ.

Вариант 2. Изменение пользовательских настроек в соответствии с настройками, которые задаёт драйвер ODBC.

Это тоже несложно: обновите пользовательские настройки так, чтобы они уже соответствовали параметрам, которые пытается задать драйвер ODBC.

ALTER USER your_odbc_user MODIFY SETTING
    cast_keep_nullable = 1,
    prefer_column_name_to_alias = 1

После этого изменения драйвер ODBC по-прежнему может пытаться применить настройки, но, поскольку значения уже совпадают, фактических изменений не происходит и ошибки удаётся избежать.

Этот вариант тоже прост, но требует сопровождения: в более новых версиях драйвера список настроек может измениться или для совместимости могут добавиться новые. Если вы жёстко задаёте эти настройки для своего ODBC-пользователя, вам может потребоваться обновлять их каждый раз, когда драйвер ODBC начинает применять дополнительные настройки.