ODBC-драйвер

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

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

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

Последняя версия драйвера доступна по адресу 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 к серверу ClickHouse можно использовать ODBC-драйвер. Power BI предлагает два варианта подключения: универсальный коннектор ODBC и коннектор ClickHouse; оба входят в стандартную установку Power BI.

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

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

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

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

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

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

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

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

cast_keep_nullable

По умолчанию ClickHouse не позволяет преобразовывать типы Nullable в типы, не допускающие NULL. Однако многие инструменты BI не различают такие типы при преобразовании типов. Поэтому инструменты 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 приведёт к ошибке. В следующем разделе объясняется, как обеспечить работу этого параметра конфигурации для пользователей с доступом только для чтения.

При подключении к 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-драйвер начнет применять дополнительные настройки.