Словари

Словарь — это отображение (ключ -> атрибуты), удобное для различных видов справочных списков.

ClickHouse поддерживает специальные функции для работы со словарями, которые можно использовать в запросах. Использовать словари с функциями проще и эффективнее, чем JOIN с таблицами справочников.

ClickHouse поддерживает:

• Словари с набором функций.
• Встроенные словари с конкретным набором функций.

Учебник

Если вы только начинаете работу со Словарями в ClickHouse, у нас есть учебник, который охватывает эту тему. Посмотрите здесь.

Вы можете добавлять свои собственные словари из различных источников данных. Источником словаря может быть таблица ClickHouse, локальный текстовый или исполняемый файл, ресурс HTTP(s), или другая СУБД. Для получения дополнительной информации смотрите раздел "Источники словарей".

ClickHouse:

• Полностью или частично хранит словари в ОЗУ.
• Периодически обновляет словари и динамически подгружает недостающие значения. Другими словами, словари могут загружаться динамически.
• Позволяет создавать словари с помощью xml-файлов или DDL-запросов.

Конфигурация словарей может располагаться в одном или нескольких xml-файлах. Путь к конфигурации задаётся в параметре dictionaries_config.

Словари могут загружаться при запуске сервера или при первом использовании, в зависимости от настройки dictionaries_lazy_load.

Системная таблица dictionaries содержит информацию о словарях, настроенных на сервере. Для каждого словаря вы можете найти там:

• Статус словаря.
• Параметры конфигурации.
• Метрики, такие как объем ОЗУ, выделенный для словаря, или количество запросов с тех пор, как словарь был успешно загружен.

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Создание словаря с помощью DDL-запроса

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

• Не требуют добавления дополнительных записей в файлы конфигурации сервера
• Словарями можно управлять как самостоятельными объектами, такими как таблицы или представления
• Данные можно считывать напрямую, используя привычный SELECT, а не функции таблицы словаря
• Словари можно легко переименовывать

Создание словаря с помощью конфигурационного файла

Not supported in ClickHouse Cloud

примечание

Создание словаря с помощью конфигурационного файла не применимо к ClickHouse Cloud. Пожалуйста, используйте DDL (см. выше) и создавайте ваш словарь как пользователь default.

Файл конфигурации словаря имеет следующий формат:

Вы можете настраивать любое количество словарей в одном и том же файле.

примечание

Вы можете преобразовать значения для небольшого словаря, описывая его в запросе SELECT (см. функцию transform). Эта функциональность не связана со словарями.

Настройка Словаря

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Если словарь настраивается с помощью xml-файла, то структура конфигурации словаря имеет следующий вид:

Соответствующий DDL-запрос имеет следующую структуру:

Хранение словарей в памяти

Существует множество способов хранения словарей в памяти.

Мы рекомендуем flat, hashed и complex_key_hashed, которые обеспечивают оптимальную скорость обработки.

Кэширование не рекомендуется из-за потенциально низкой производительности и трудностей в выборе оптимальных параметров. Читайте об этом в разделе cache.

Существует несколько способов улучшить производительность словаря:

• Вызывайте функцию для работы со словарем после GROUP BY.
• Отметьте атрибуты, которые нужно извлекать, как инъективные. Атрибут называется инъективным, если разным ключам соответствуют разные значения атрибутов. Таким образом, когда GROUP BY использует функцию, которая извлекает значение атрибута по ключу, эта функция автоматически исключается из GROUP BY.

ClickHouse генерирует исключение для ошибок, связанных со словарями. Примеры ошибок:

• Словарь, к которому осуществляется доступ, не удалось загрузить.
• Ошибка при выполнении запроса к cached словарю.

Вы можете просмотреть список словарей и их статусы в таблице system.dictionaries.

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Конфигурация выглядит следующим образом:

Соответствующий DDL-запрос:

Словари без слова complex-key* в размещении имеют ключ с типом UInt64, словари с complex-key* имеют составной ключ (комплексный, с произвольными типами).

Ключи UInt64 в xml-словарях определяются с помощью тега <id>.

Пример конфигурации (колонка key_column имеет тип UInt64):

Составные complex ключи в xml-словах определяются тегом <key>.

Пример конфигурации составного ключа (ключ имеет один элемент с типом String):

Способы хранения словарей в памяти

• flat
• hashed
• sparse_hashed
• complex_key_hashed
• complex_key_sparse_hashed
• hashed_array
• complex_key_hashed_array
• range_hashed
• complex_key_range_hashed
• cache
• complex_key_cache
• ssd_cache
• complex_key_ssd_cache
• direct
• complex_key_direct
• ip_trie

flat

Словарь полностью хранится в памяти в виде плоских массивов. Сколько памяти использует словарь? Объем пропорционален размеру самого большого ключа (в занимаемом пространстве).

Ключ словаря имеет тип UInt64, а значение ограничивается max_array_size (по умолчанию — 500,000). Если при создании словаря будет обнаружен больший ключ, ClickHouse выбросит исключение и не создаст словарь. Начальный размер плоских массивов словаря контролируется настройкой initial_array_size (по умолчанию — 1024).

Поддерживаются все типы источников. При обновлении данные (из файла или из таблицы) считываются полностью.

Этот метод обеспечивает наилучшую производительность среди всех доступных способов хранения словаря.

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

или

hashed

Словарь полностью хранится в памяти в виде хеш-таблицы. Словарь может содержать любое количество элементов с любыми идентификаторами. На практике количество ключей может достигать десятков миллионов элементов.

Ключ словаря имеет тип UInt64.

Поддерживаются все типы источников. При обновлении данные (из файла или из таблицы) считываются полностью.

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

или

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

или

sparse_hashed

Аналогично hashed, но использует меньше памяти в ущерб большему использованию ЦП.

Ключ словаря имеет тип UInt64.

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

или

Также возможно использовать shards для этого типа словаря, и вновь это более важно для sparse_hashed, чем для hashed, так как sparse_hashed медленнее.

complex_key_hashed

Этот тип хранения предназначен для использования с составными ключами. Аналогично hashed.

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

или

complex_key_sparse_hashed

Этот тип хранения предназначен для использования с составными ключами. Аналогично sparse_hashed.

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

или

hashed_array

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

Ключ словаря имеет тип UInt64.

Поддерживаются все типы источников. При обновлении данные (из файла или из таблицы) считываются полностью.

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

или

complex_key_hashed_array

Этот тип хранения предназначен для использования с составными ключами. Аналогично hashed_array.

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

или

range_hashed

Словарь хранится в памяти в виде хеш-таблицы с упорядоченным массивом диапазонов и их соответствующих значений.

Ключ словаря имеет тип UInt64 типов. Этот метод хранения работает аналогично hashed и позволяет использовать диапазоны дат/времени (произвольный числовой тип) в дополнение к ключу.

Пример: Таблица содержит скидки для каждого рекламодателя в формате:

Чтобы использовать пример для диапазонов дат, задайте элементы range_min и range_max в структуре. Эти элементы должны содержать элементы name и type (если type не указан, будет использоваться тип по умолчанию - Date). type может быть любым числовым типом (Date / DateTime / UInt64 / Int32 / другие).

примечание

Значения range_min и range_max должны уместиться в типе Int64.

Пример:

или

Чтобы работать с этими словарями, необходимо передать дополнительный аргумент функции dictGet, для которого выбирается диапазон:

Пример запроса:

Эта функция возвращает значение для заданных id и диапазона дат, который включает указанную дату.

Подробности алгоритма:

• Если id не найден или диапазон не найден для id, возвращается значение по умолчанию для типа атрибута.
• Если имеются перекрывающиеся диапазоны и range_lookup_strategy=min, возвращается совпадающий диапазон с минимальным range_min, если найдено несколько диапазонов, возвращается диапазон с минимальным range_max, если снова найдено несколько диапазонов (несколько диапазонов имели одно и то же range_min и range_max) возвращается случайный диапазон из них.
• Если имеются перекрывающиеся диапазоны и range_lookup_strategy=max, возвращается совпадающий диапазон с максимальным range_min, если найдено несколько диапазонов, возвращается диапазон с максимальным range_max, если снова найдено несколько диапазонов (несколько диапазонов имели одно и то же range_min и range_max) возвращается случайный диапазон из них.
• Если range_max равен NULL, диапазон открыт. NULL рассматривается как максимально возможное значение. Для range_min могут быть использованы 1970-01-01 или 0 (-MAX_INT) как открытое значение.

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

или

Пример конфигурации с перекрывающимися диапазонами и открытыми диапазонами:

complex_key_range_hashed

Словарь хранится в памяти в виде хеш-таблицы с упорядоченным массивом диапазонов и их соответствующих значений (см. range_hashed). Этот тип хранения предназначен для использования с составными ключами.

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

cache

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

Ключ словаря имеет тип UInt64.

При поиске словаря сначала ищется в кэше. Для каждого блока данных все ключи, которые не найденные в кэше или устарели, запрашиваются из источника с использованием SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...). Полученные данные затем записываются в кэш.

Если ключи не найдены в словаре, то создается задача обновления кэша и добавляется в очередь обновлений. Свойства очереди обновлений можно контролировать с помощью настроек max_update_queue_size, update_queue_push_timeout_milliseconds, query_wait_timeout_milliseconds, max_threads_for_updates.

Для кэшированных словарей период lifetime данных в кэше может быть установлен. Если больше времени прошло с момента загрузки данных в ячейку, чем lifetime, значение ячейки не используется, и ключ становится устаревшим. Этот ключ запрашивается снова в следующий раз, когда он будет использоваться. Это поведение можно настроить с помощью настройки allow_read_expired_keys.

Это наименее эффективный из всех способов хранения словарей. Скорость кэша сильно зависит от правильных настроек и сценария использования. Словарь кэша работает хорошо только тогда, когда коэффициенты попаданий достаточно высоки (рекомендуется 99% и выше). Вы можете просмотреть средний коэффициент попадания в таблице system.dictionaries.

Если настройка allow_read_expired_keys установлена в 1, по умолчанию 0. Затем словарь может поддерживать асинхронные обновления. Если клиент запрашивает ключи и все они находятся в кэше, но некоторые из них устарели, тогда словарь вернет устаревшие ключи клиенту и запросит их асинхронно от источника.

Чтобы улучшить производительность кэша, используйте подзапрос с LIMIT, и вызывайте функцию с словарем извне.

Поддерживаются все типы источников.

Пример настроек:

или

Установите достаточно большой размер кэша. Вам нужно провести эксперимент для выбора количества ячеек:

1. Установите некоторое значение.
2. Запустите запросы, пока кэш не заполнится полностью.
3. Оцените потребление памяти, используя таблицу system.dictionaries.
4. Увеличьте или уменьшите количество ячеек, пока не будет достигнуто необходимое потребление памяти.

примечание

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

complex_key_cache

Этот тип хранения предназначен для использования с составными ключами. Аналогично cache.

ssd_cache

Аналогично cache, но хранит данные на SSD и индекс в ОЗУ. Все настройки словаря кэша, связанные с очередью обновлений, могут также применяться к словарям кэша SSD.

Ключ словаря имеет тип UInt64.

или

complex_key_ssd_cache

Этот тип хранения предназначен для использования с составными ключами. Аналогично ssd_cache.

direct

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

Ключ словаря имеет тип UInt64.

Поддерживаются все типы источников, кроме локальных файлов.

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

или

complex_key_direct

Этот тип хранения предназначен для использования с составными ключами. Аналогично direct.

ip_trie

Этот тип хранения предназначен для отображения сетевых префиксов (IP-адресов) на метаданные, такие как ASN.

Пример

Предположим, у нас есть таблица в ClickHouse, которая содержит наши IP-префиксы и соответствия:

Давайте определим ip_trie словарь для этой таблицы. Размещение ip_trie требует составного ключа:

или

Ключ должен иметь только один атрибут типа String, который содержит разрешенный IP-префикс. Другие типы не поддерживаются пока.

Синтаксис:

Функция принимает либо UInt32 для IPv4, либо FixedString(16) для IPv6. Например:

Другие типы пока не поддерживаются. Функция возвращает атрибут для префикса, который соответствует этому IP-адресу. Если есть перекрывающиеся префиксы, возвращается наиболее специфичный.

Данные должны полностью помещаться в ОЗУ.

Обновление данных словаря с использованием LIFETIME

ClickHouse периодически обновляет словари на основе тега LIFETIME (определенного в секундах). LIFETIME - это интервал обновления для полностью загруженных словарей и интервал недействительности для кэшированных словарей.

Во время обновлений старая версия словаря все еще может быть запрошена. Обновления словарей (за исключением загрузки словаря для первого использования) не блокируют запросы. Если во время обновления возникает ошибка, ошибка записывается в журнал сервера, и запросы могут продолжать использовать старую версию словаря. Если обновление словаря прошло успешно, старая версия словаря атомарно заменяется.

Пример настроек:

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

или

Установка <lifetime>0</lifetime> (LIFETIME(0)) предотвращает обновление словарей.

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

Пример настроек:

или

Если <min>0</min> и <max>0</max>, ClickHouse не перезагружает словарь по таймауту. В этом случае ClickHouse может перезагрузить словарь раньше, если файл конфигурации словаря был изменен или была выполнена команда SYSTEM RELOAD DICTIONARY.

При обновлении словарей сервер ClickHouse применяет различную логику в зависимости от типа источника:

• Для текстового файла он проверяет время модификации. Если время отличается от ранее зафиксированного времени, словарь обновляется.
• Словари из других источников обновляются каждый раз по умолчанию.

Для других источников (ODBC, PostgreSQL, ClickHouse и т. д.) вы можете настроить запрос, который обновит словари только в случае, если они действительно изменились, а не каждый раз. Для этого выполните следующие шаги:

• Таблица словаря должна иметь поле, которое всегда изменяется, когда исходные данные обновляются.
• Настройки источника должны указывать запрос, который извлекает изменяющееся поле. Сервер ClickHouse интерпретирует результат запроса как строку, и если эта строка изменилась по отношению к своему предыдущему состоянию, словарь обновляется. Укажите запрос в поле <invalidate_query> в настройках для источника.

Пример настроек:

или

Для словарей Cache, ComplexKeyCache, SSDCache и SSDComplexKeyCache поддерживаются как синхронные, так и асинхронные обновления.

Также возможно для словарей Flat, Hashed, ComplexKeyHashed запрашивать только данные, которые изменились после предыдущего обновления. Если update_field указан как часть конфигурации источника словаря, значение времени предыдущего обновления в секундах будет добавлено к запросу данных. В зависимости от типа источника (Executable, HTTP, MySQL, PostgreSQL, ClickHouse или ODBC) будет применена различная логика к update_field перед запросом данных из внешнего источника.

• Если источник - HTTP, то update_field будет добавлен в качестве параметра запроса с временем последнего обновления в качестве значения параметра.
• Если источник - Executable, то update_field будет добавлен как аргумент исполняемого скрипта с временем последнего обновления в качестве значения аргумента.
• Если источник - ClickHouse, MySQL, PostgreSQL, ODBC, то будет добавлена дополнительная часть WHERE, где update_field сравнивается как больше или равно времени последнего обновления.
  • По умолчанию это условие WHERE проверяется на самом высоком уровне SQL-запроса. В качестве альтернативы условие может быть проверено в любом другом WHERE-условии в запросе с использованием ключевого слова {condition}. Пример:

Если установлена опция update_field, можно установить дополнительную опцию update_lag. Значение параметра update_lag вычитается из времени предыдущего обновления перед запросом обновленных данных.

Пример настроек:

или

Источники словарей

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Словарь может быть подключен к ClickHouse из множества различных источников.

Если словарь настроен с использованием xml-файла, конфигурация выглядит следующим образом:

В случае DDL-запроса вышеописанная конфигурация будет выглядеть так:

Источник настраивается в секции source.

Для типов источников Локальный файл, Исполняемый файл, HTTP(S), ClickHouse доступны дополнительные настройки:

или

Типы источников (source_type):

• Локальный файл
• Исполняемый файл
• Исполняемый пул
• HTTP(S)
• СУБД
  • ODBC
  • MySQL
  • ClickHouse
  • MongoDB
  • Redis
  • Cassandra
  • PostgreSQL

Локальный файл

Пример настроек:

или

Поля настроек:

• path – Абсолютный путь к файлу.
• format – Формат файла. Поддерживаются все форматы, описанные в Форматах.

Когда словарь с источником FILE создается с помощью команды DDL (CREATE DICTIONARY ...), файл источника должен находиться в директории user_files, чтобы предотвратить доступ пользователей БД к произвольным файлам на узле ClickHouse.

Смотрите также

• Функция словаря

Исполняемый файл

Работа с исполняемыми файлами зависит от того, как словарь хранится в памяти. Если словарь хранится с использованием cache и complex_key_cache, ClickHouse запрашивает необходимые ключи, отправляя запрос на стандартный ввод исполняемого файла. В противном случае ClickHouse запускает исполняемый файл и обрабатывает его вывод как данные словаря.

Пример настроек:

Поля настроек:

• command — Абсолютный путь к исполняемому файлу или имя файла (если директория команды находится в PATH).
• format — Формат файла. Поддерживаются все форматы, описанные в Форматах.
• command_termination_timeout — Исполняемый скрипт должен содержать основной цикл чтения и записи. После разрушения словаря труба закрывается, и исполняемому файлу будет предоставлено command_termination_timeout секунд на завершение, прежде чем ClickHouse отправит сигнал SIGTERM дочернему процессу. command_termination_timeout указывается в секундах. Значение по умолчанию 10. Необязательный параметр.
• command_read_timeout - Таймаут для чтения данных из стандартного вывода команды в миллисекундах. Значение по умолчанию 10000. Необязательный параметр.
• command_write_timeout - Таймаут для записи данных в стандартный ввод команды в миллисекундах. Значение по умолчанию 10000. Необязательный параметр.
• implicit_key — Исполняемый файл источника может возвращать только значения, а соответствие запрашиваемым ключам определяется неявно — по порядку строк в результате. Значение по умолчанию - false.
• execute_direct - Если execute_direct = 1, то command будет искаться внутри директории user_scripts, указанной переменной user_scripts_path. Дополнительные аргументы скрипта могут быть указаны с разделителем пробелом. Пример: script_name arg1 arg2. Если execute_direct = 0, command передается как аргумент для bin/sh -c. Значение по умолчанию - 0. Необязательный параметр.
• send_chunk_header - управляет тем, следует ли отправлять количество строк перед отправкой блока данных на обработку. Необязательный параметр. Значение по умолчанию - false.

Этот источник словаря можно настроить только через XML-конфигурацию. Создание словарей с исполняемым источником через DDL отключено, иначе пользователь БД мог бы выполнять произвольные бинарные файлы на узле ClickHouse.

Исполняемый пул

Исполняемый пул позволяет загружать данные из пула процессов. Этот источник не работает с макетами словаря, которые требуют загрузки всех данных из источника. Исполняемый пул работает, если словарь хранится с использованием cache, complex_key_cache, ssd_cache, complex_key_ssd_cache, direct или complex_key_direct.

Исполняемый пул создаст пул процессов с заданной командой и будет поддерживать их в работающем состоянии до тех пор, пока они не завершатся. Программа должна читать данные из стандартного ввода, пока они доступны, и выводить результат в стандартный вывод. Она может ожидать следующий блок данных на стандартном вводе. ClickHouse не закроет стандартный ввод после обработки блока данных, но будет передавать другой кусок данных по мере необходимости. Исполняемый скрипт должен быть готов к такому способу обработки данных — он должен опрашивать стандартный ввод и сбрасывать данные на стандартный вывод заранее.

Пример настроек:

Поля настроек:

• command — Абсолютный путь к исполняемому файлу или имя файла (если директория программы записана в PATH).
• format — Формат файла. Поддерживаются все форматы, описанные в "Форматах".
• pool_size — Размер пула. Если для pool_size указано значение 0, то нет ограничений на размер пула. Значение по умолчанию - 16.
• command_termination_timeout — Исполняемый скрипт должен содержать основной цикл чтения и записи. После разрушения словаря труба закрывается, и исполняемому файлу будет предоставлено command_termination_timeout секунд на завершение, прежде чем ClickHouse отправит сигнал SIGTERM дочернему процессу. Указано в секундах. Значение по умолчанию - 10. Необязательный параметр.
• max_command_execution_time — Максимальное время выполнения исполняемой команды скрипта для обработки блока данных. Указано в секундах. Значение по умолчанию – 10. Необязательный параметр.
• command_read_timeout - таймаут для чтения данных из стандартного вывода команды в миллисекундах. Значение по умолчанию 10000. Необязательный параметр.
• command_write_timeout - таймаут для записи данных в стандартный ввод команды в миллисекундах. Значение по умолчанию 10000. Необязательный параметр.
• implicit_key — Исполняемый файл источника может возвращать только значения, а соответствие запрашиваемым ключам определяется неявно — по порядку строк в результате. Значение по умолчанию - false. Необязательный параметр.
• execute_direct - Если execute_direct = 1, тогда command будет искать внутри директории user_scripts, указанной переменной user_scripts_path. Дополнительные аргументы скрипта могут быть указаны с разделителем пробелом. Пример: script_name arg1 arg2. Если execute_direct = 0, command передается как аргумент для bin/sh -c. Значение по умолчанию - 1. Необязательный параметр.
• send_chunk_header - управляет тем, следует ли отправлять количество строк перед отправкой блока данных на обработку. Необязательный параметр. Значение по умолчанию - false.

Этот источник словаря можно настроить только через XML-конфигурацию. Создание словарей с исполняемым источником через DDL отключено, иначе пользователь БД мог бы выполнять произвольные бинарные файлы на узле ClickHouse.

HTTP(S)

Работа с HTTP(S) сервером зависит от того, как словарь хранится в памяти. Если словарь хранится с использованием cache и complex_key_cache, ClickHouse запрашивает необходимые ключи, отправляя запрос через метод POST.

Пример настроек:

или

Для того чтобы ClickHouse смог получить доступ к HTTPS-ресурсу, необходимо настроить openSSL в конфигурации сервера.

Поля настроек:

• url – URL источника.
• format – Формат файла. Поддерживаются все форматы, описанные в "Форматах".
• credentials – Базовая HTTP-аутентификация. Необязательный параметр.
• user – Имя пользователя, необходимое для аутентификации.
• password – Пароль, необходимый для аутентификации.
• headers – Все пользовательские HTTP-заголовки, используемые для HTTP-запроса. Необязательный параметр.
• header – Один HTTP-заголовок.
• name – Идентификатор, используемый для отправки заголовка в запросе.
• value – Значение, установленное для конкретного идентификатора.

При создании словаря с использованием команды DDL (CREATE DICTIONARY ...) удаленные хосты для HTTP-словарей проверяются на соответствие содержимому раздела remote_url_allow_hosts из конфигурации, чтобы предотвратить доступ пользователей БД к произвольным HTTP-серверам.

СУБД

ODBC

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

Пример настроек:

или

Поля настроек:

• db – Имя базы данных. Укажите его, если имя базы данных не задано в параметрах <connection_string>.
• table – Имя таблицы и схемы, если это возможно.
• connection_string – Строка подключения.
• invalidate_query – Запрос для проверки состояния словаря. Необязательный параметр. Подробнее в разделе Обновление данных словаря с использованием LIFETIME.
• background_reconnect – Повторное подключение к реплике в фоновом режиме, если подключение не удалось. Необязательный параметр.
• query – Пользовательский запрос. Необязательный параметр.

примечание

Поля table и query не могут использоваться вместе. И одно из полей table или query должно быть объявлено.

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

Если у вас возникли проблемы с кодировками при использовании Oracle, смотрите соответствующий пункт Часто задаваемых вопросов.

Известная уязвимость функциональности ODBC словаря

примечание

Когда вы подключаетесь к базе данных через драйвер ODBC параметр соединения Servername может быть подменен. В этом случае значения USERNAME и PASSWORD из odbc.ini отправляются на удаленный сервер и могут быть скомпрометированы.

Пример небезопасного использования

Настроим unixODBC для PostgreSQL. Содержимое /etc/odbc.ini:

Если вы затем выполните такой запрос

ODBC-драйвер отправит значения USERNAME и PASSWORD из odbc.ini на some-server.com.

Пример подключения к PostgreSQL

Операционная система Ubuntu.

Установка unixODBC и ODBC-драйвера для PostgreSQL:

Настройка /etc/odbc.ini (или ~/.odbc.ini, если вы вошли под пользователем, который запускает ClickHouse):

Конфигурация словаря в ClickHouse:

или

Вам может потребоваться отредактировать odbc.ini, чтобы указать полный путь к библиотеке с драйвером DRIVER=/usr/local/lib/psqlodbcw.so.

Пример подключения к MS SQL Server

Операционная система Ubuntu.

Установка ODBC-драйвера для подключения к MS SQL:

Настройка драйвера:

Замечания:

• Чтобы определить самую раннюю версию TDS, которая поддерживается определенной версией SQL Server, обратитесь к документации продукта или ознакомьтесь с MS-TDS Product Behavior.

Настройка словаря в ClickHouse:

или

Mysql

Пример настроек:

или

Поля настроек:

• port – Порт на сервере MySQL. Вы можете указать его для всех реплик или для каждой из них индивидуально (внутри <replica>).

• user – Имя пользователя MySQL. Вы можете указать его для всех реплик или для каждой из них индивидуально (внутри <replica>).

• password – Пароль пользователя MySQL. Вы можете указать его для всех реплик или для каждой из них индивидуально (внутри <replica>).

• replica – Раздел конфигурации реплик. Может быть несколько этих разделов.

  • replica/host – Хост MySQL.
  • replica/priority – Приоритет реплики. При попытке подключения ClickHouse проходит по репликам в порядке приоритета. Чем меньше число, тем выше приоритет.

• db – Имя базы данных.

• table – Имя таблицы.

• where – Критерии выбора. Синтаксис для условий идентичен синтаксису WHERE в MySQL, например, id > 10 AND id < 20. Необязательный параметр.

• invalidate_query – Запрос для проверки состояния словаря. Необязательный параметр. Читайте больше в разделе Обновление данных словаря с использованием LIFETIME.

• fail_on_connection_loss – Параметр конфигурации, который контролирует поведение сервера при потере соединения. Если true, исключение выбрасывается немедленно, если соединение между клиентом и сервером было потеряно. Если false, сервер ClickHouse повторит попытку выполнения запроса три раза перед выбрасыванием исключения. Обратите внимание, что повторные попытки ведут к увеличению времени отклика. Значение по умолчанию: false.

• query – Пользовательский запрос. Необязательный параметр.

примечание

Поля table или where не могут использоваться вместе с полем query. И одно из полей table или query должно быть объявлено.

примечание

Явного параметра secure нет. При установлении SSL-соединения безопасность обязательна.

MySQL можно подключить на локальном хосте через сокеты. Для этого установите host и socket.

Пример настроек:

или

ClickHouse

Пример настроек:

или

Поля настроек:

• host – Хост ClickHouse. Если это локальный хост, запрос обрабатывается без сетевой активности. Для улучшения отказоустойчивости вы можете создать Распределенную таблицу и ввести её в последующих конфигурациях.
• port – Порт на сервере ClickHouse.
• user – Имя пользователя ClickHouse.
• password – Пароль пользователя ClickHouse.
• db – Имя базы данных.
• table – Имя таблицы.
• where – Критерии выбора. Могут быть опущены.
• invalidate_query – Запрос для проверки состояния словаря. Необязательный параметр. Читайте больше в разделе Обновление данных словаря с использованием LIFETIME.
• secure - Используйте SSL для соединения.
• query – Пользовательский запрос. Необязательный параметр.

примечание

Поля table или where не могут использоваться вместе с полем query. И одно из полей table или query должно быть объявлено.

MongoDB

Пример настроек:

или

или

Поля настроек:

• host – Хост MongoDB.
• port – Порт на сервере MongoDB.
• user – Имя пользователя MongoDB.
• password – Пароль пользователя MongoDB.
• db – Имя базы данных.
• collection – Имя коллекции.
• options - Опции строки подключения MongoDB (необязательный параметр).

или

Поля настроек:

• uri - URI для установки соединения.
• collection – Имя коллекции.

Дополнительная информация об этом движке

Redis

Пример настроек:

или

Поля настроек:

• host – Хост Redis.
• port – Порт на сервере Redis.
• storage_type – Структура внутреннего хранилища Redis, используемая для работы с ключами. simple предназначен для простых источников и для хешированных источников с одним ключом, hash_map - для хешированных источников с двумя ключами. Диапазон источников и кэшированные источники с комплексными ключами не поддерживаются. Может быть опущено, значение по умолчанию simple.
• db_index – Конкретный числовой индекс логической базы данных Redis. Может быть опущено, значение по умолчанию 0.

Cassandra

Пример настроек:

Поля настроек:

• host – Хост Cassandra или запятая-разделенный список хостов.
• port – Порт на серверах Cassandra. Если не указан, используется порт по умолчанию 9042.
• user – Имя пользователя Cassandra.
• password – Пароль пользователя Cassandra.
• keyspace – Имя ключевого пространства (базы данных).
• column_family – Имя семейства колонок (таблицы).
• allow_filtering – Флаг для разрешения или запрета потенциально дорогих условий на колонках ключа кластеризации. Значение по умолчанию 1.
• partition_key_prefix – Количество колонок ключа партиции в первичном ключе таблицы Cassandra. Обязательно для составных словарей. Порядок колонок ключа в определении словаря должен совпадать с Cassandra. Значение по умолчанию 1 (первая колонка ключа является ключом партиции, а другие колонки ключа - ключами кластеризации).
• consistency – Уровень консистентности. Возможные значения: One, Two, Three, All, EachQuorum, Quorum, LocalQuorum, LocalOne, Serial, LocalSerial. Значение по умолчанию One.
• where – Дополнительные критерии выбора.
• max_threads – Максимальное количество потоков для загрузки данных из нескольких партиций в составных словарях.
• query – Пользовательский запрос. Дополнительный параметр.

примечание

Поля column_family или where не могут использоваться вместе с полем query. И одно из полей column_family или query должно быть объявлено.

PostgreSQL

Пример настроек:

или

Поля настроек:

• host – Хост на сервере PostgreSQL. Вы можете указать его для всех реплик или для каждой по отдельности (внутри <replica>).
• port – Порт на сервере PostgreSQL. Вы можете указать его для всех реплик или для каждой по отдельности (внутри <replica>).
• user – Имя пользователя PostgreSQL. Вы можете указать его для всех реплик или для каждой по отдельности (внутри <replica>).
• password – Пароль пользователя PostgreSQL. Вы можете указать его для всех реплик или для каждой по отдельности (внутри <replica>).
• replica – Раздел конфигураций реплик. Может быть несколько разделов:
  • replica/host – Хост PostgreSQL.
  • replica/port – Порт PostgreSQL.
  • replica/priority – Приоритет реплики. При попытке подключения ClickHouse проходит по репликам в порядке приоритета. Чем меньше число, тем выше приоритет.
• db – Имя базы данных.
• table – Имя таблицы.
• where – Критерии выбора. Синтаксис условий такой же, как для клаузулы WHERE в PostgreSQL. Например, id > 10 AND id < 20. Дополнительный параметр.
• invalidate_query – Запрос для проверки состояния словаря. Дополнительный параметр. Читайте далее в разделе Обновление данных словаря с использованием LIFETIME.
• background_reconnect – Переподключение к реплике в фоновом режиме, если соединение не удалось. Дополнительный параметр.
• query – Пользовательский запрос. Дополнительный параметр.

примечание

Поля table или where не могут использоваться вместе с полем query. И одно из полей table или query должно быть объявлено.

Null

Специальный источник, который можно использовать для создания ненастоящих (пустых) словарей. Такие словари могут быть полезны для тестов или в настройках с разделенными данными и узлами запросов на узлах с распределенными таблицами.

Ключ и поля словаря

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Клаузула structure описывает ключ словаря и поля, доступные для запросов.

XML описание:

Атрибуты описаны в элементах:

• <id> — Ключевая колонка
• <attribute> — Столбец данных: может существовать несколько атрибутов.

DDL запрос:

Атрибуты описаны в теле запроса:

• PRIMARY KEY — Ключевая колонка
• AttrName AttrType — Столбец данных. Может существовать несколько атрибутов.

Ключ

ClickHouse поддерживает следующие типы ключей:

• Числовой ключ. UInt64. Определяется в теге <id> или с использованием ключевого слова PRIMARY KEY.
• Составной ключ. Набор значений различных типов. Определяется в теге <key> или с использованием ключевого слова PRIMARY KEY.

XML структура может содержать либо <id>, либо <key>. DDL-запрос должен содержать единственный PRIMARY KEY.

примечание

Вы не должны описывать ключ как атрибут.

Числовой ключ

Тип: UInt64.

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

Поля конфигурации:

• name – Имя колонки с ключами.

Для DDL-запроса:

• PRIMARY KEY – Имя колонки с ключами.

Составной ключ

Ключ может быть tuple из любых типов полей. Макет в этом случае должен быть complex_key_hashed или complex_key_cache.

подсказка

Составной ключ может состоять из одного элемента. Это позволяет использовать строку в качестве ключа, например.

Структура ключа устанавливается в элементе <key>. Поля ключа указываются в том же формате, что и словарные атрибуты. Пример:

или

Для запроса к функции dictGet* в качестве ключа передается кортеж. Пример: dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2)).

Атрибуты

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

или

Поля конфигурации:

Тег	Описание	Обязательно
name	Имя колонки.	Да
type	Тип данных ClickHouse: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, UUID, Decimal32, Decimal64, Decimal128, Decimal256,Date, Date32, DateTime, DateTime64, String, Array. ClickHouse пытается привести значение из словаря к указанному типу данных. Например, для MySQL поле может быть TEXT, VARCHAR или BLOB в таблице источника MySQL, но его можно загружать как String в ClickHouse. Nullable в настоящее время поддерживается для словарей Flat, Hashed, ComplexKeyHashed, Direct, ComplexKeyDirect, RangeHashed, Polygon, Cache, ComplexKeyCache, SSDCache, SSDComplexKeyCache. В словарях IPTrie типы Nullable не поддерживаются.	Да
null_value	Значение по умолчанию для несуществующего элемента. В примере это пустая строка. Значение NULL может использоваться только для типов Nullable (см. предыдущую строку описания типов).	Да
expression	Выражение, которое ClickHouse выполняет над значением. Выражение может быть именем колонки в удаленной SQL базе данных. Таким образом, вы можете использовать его для создания псевдонима для удаленной колонки. Значение по умолчанию: нет выражения.	Нет
hierarchical	Если true, атрибут содержит значение родительского ключа для текущего ключа. См. Иерархические словари. Значение по умолчанию: false.	Нет
injective	Флаг, указывающий, является ли изображение id -> attribute инъективным. Если true, ClickHouse может автоматически помещать после клаузулы GROUP BY запросы к словарям с инъекцией. Обычно это значительно снижает количество таких запросов. Значение по умолчанию: false.	Нет
is_object_id	Флаг, показывающий, выполняется ли запрос для документа MongoDB по ObjectID. Значение по умолчанию: false.

Иерархические словари

ClickHouse поддерживает иерархические словари с числовым ключом.

Посмотрите на следующую иерархическую структуру:

Эта иерархия может быть выражена в следующей таблице словаря.

region_id	parent_region	region_name
1	0	Россия
2	1	Москва
3	2	Центр
4	0	Великобритания
5	4	Лондон

Эта таблица содержит колонку parent_region, которая содержит ключ ближайшего родителя для элемента.

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

Функция dictGetHierarchy позволяет вам получить родительскую цепочку элемента.

Для нашего примера структура словаря может быть следующей:

Полигональные словари

Полигональные словари позволяют эффективно искать полигон, содержащий указанные точки. Например: определение зоны города по географическим координатам.

Пример конфигурации полигонального словаря:

подсказка

If you are using a dictionary with ClickHouse Cloud please use the DDL query option to create your dictionaries, and create your dictionary as user default. Also, verify the list of supported dictionary sources in the Cloud Compatibility guide.

Соответствующий DDL-запрос:

При настройке полигонального словаря ключ должен иметь один из двух типов:

• Простой полигон. Это массив точек.
• MultiPolygon. Это массив полигонов. Каждый полигон представляет собой двумерный массив точек. Первый элемент этого массива является внешней границей полигона, а последующие элементы указывают области, которые должны быть исключены из него.

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

Пользователь может загружать свои данные в любых форматах, поддерживаемых ClickHouse.

Доступно 3 типа хранения в памяти:

• POLYGON_SIMPLE. Это наивная реализация, при которой для каждого запроса осуществляется линейный проход по всем полигонам, и проверка принадлежности выполняется для каждого из них без использования дополнительных индексов.

• POLYGON_INDEX_EACH. Для каждого полигона строится отдельный индекс, который позволяет быстро проверять, принадлежит ли точка ему в большинстве случаев (оптимизировано для географических регионов). Также на рассматриваемую область накладывается сетка, что значительно уменьшает количество рассматриваемых полигонов. Сетка создается рекурсивным делением ячейки на 16 равных частей и настраивается с помощью двух параметров. Деление останавливается, когда глубина рекурсии достигает MAX_DEPTH или когда ячейка пересекает не более чем MIN_INTERSECTIONS полигонов. Для ответа на запрос есть соответствующая ячейка, и индекс для полигонов, хранящихся в ней, запрашивается поочередно.

• POLYGON_INDEX_CELL. Эта реализация также создает описанную выше сетку. Доступны те же параметры. Для каждой ячейки сетки строится индекс по всем частям полигонов, которые попадают в нее, что позволяет быстро отвечать на запрос.

• POLYGON. Синоним POLYGON_INDEX_CELL.

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

Пример

Пример работы со словарем, определенным выше:

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

Пример

Вы можете читать колонки из полигональных словарей с помощью запроса SELECT, просто включив store_polygon_key_column = 1 в конфигурации словаря или соответствующем DDL-запросе.

Запрос:

Результат:

Словарь регулярных выражений

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

Использование словаря регулярных выражений в ClickHouse Open-Source

Словари регулярных выражений определяются в ClickHouse open-source с использованием источника YAMLRegExpTree, для которого указан путь к YAML-файлу, содержащему дерево регулярных выражений.

Источник словаря YAMLRegExpTree представляет структуру дерева регулярных выражений. Например:

Эта конфигурация состоит из списка узлов дерева регулярных выражений. Каждый узел имеет следующую структуру:

• regexp: регулярное выражение узла.
• attributes: список пользовательских атрибутов словаря. В этом примере имеются два атрибута: name и version. Первый узел определяет оба атрибута. Второй узел определяет только атрибут name. Атрибут version предоставляется дочерними узлами второго узла.
  • Значение атрибута может содержать ссылки на захваты, ссылаясь на группы захвата соответствующего регулярного выражения. В примере значение атрибута version в первом узле состоит из ссылки \1 на группу захвата (\d+[\.\d]*) в регулярном выражении. Номера ссылок варьируются от 1 до 9 и записываются как $1 или \1 (для номера 1). Ссылка заменяется соответствующей группой захвата во время выполнения запроса.
• дочерние узлы: список детей узла дерева регулярных выражений, каждый из которых имеет свои атрибуты и (возможно) дочерние узлы. Сравнение строк происходит в глубину. Если строка соответствует узлу регулярного выражения, словарь проверяет также совпадение с дочерними узлами. Если это так, атрибуты самого глубокого совпадающего узла назначаются. Атрибуты дочернего узла перезаписывают атрибуты родительских узлов с одинаковыми именами. Имена дочерних узлов в YAML-файлах могут быть произвольными, например, versions в приведенном выше примере.

Словари деревьев регулярных выражений позволяют получить доступ только с помощью функций dictGet, dictGetOrDefault и dictGetAll.

Пример:

Результат:

В этом случае мы сначала совпадаем с регулярным выражением \d+/tclwebkit(?:\d+[\.\d]*) во втором узле верхнего уровня. Затем словарь продолжает смотреть в дочерние узлы и находит, что строка также соответствует 3[12]/tclwebkit. В результате значение атрибута name соответствует Android (определенному в первом слое), а значение атрибута version соответствует 12 (определенному в дочернем узле).

С мощным файлом конфигурации YAML мы можем использовать словари деревьев регулярных выражений в качестве парсера строк пользовательского агента. Мы поддерживаем uap-core и демонстрируем, как использовать его в функциональном тесте 02504_regexp_dictionary_ua_parser

Сбор значений атрибутов

Иногда полезно возвращать значения из нескольких регулярных выражений, которые совпали, а не только значение листового узла. В этих случаях может быть использована специализированная функция dictGetAll. Если у узла есть значение атрибута типа T, dictGetAll вернет Array(T), содержащий ноль или несколько значений.

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

Пример:

Результат:

Режимы совпадения

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

• regexp_dict_flag_case_insensitive: Использовать нечувствительное к регистру совпадение (по умолчанию false). Может быть переопределено в отдельных выражениях с помощью (?i) и (?-i).
• regexp_dict_flag_dotall: Позволить '.' совпадать с символами новой строки (по умолчанию false).

Использование словаря регулярных выражений в ClickHouse Cloud

Вышеприведенный источник YAMLRegExpTree работает в ClickHouse Open Source, но не в ClickHouse Cloud. Чтобы использовать словари деревьев регулярных выражений в ClickHouse Cloud, сначала создайте словарь дерева регулярных выражений из YAML-файла на локальном экземпляре ClickHouse Open Source, затем выгрузите этот словарь в CSV-файл с помощью функции таблицы dictionary и клаузулы INTO OUTFILE.

Содержимое CSV-файла:

Схема выгруженного файла:

• id UInt64: идентификатор узла RegexpTree.
• parent_id UInt64: идентификатор родителя узла.
• regexp String: строка регулярного выражения.
• keys Array(String): имена пользовательских атрибутов.
• values Array(String): значения пользовательских атрибутов.

Чтобы создать словарь в ClickHouse Cloud, сначала создайте таблицу regexp_dictionary_source_table со следующей структурой:

Затем обновите локальный CSV с помощью

Вы можете ознакомиться с тем, как Вставить локальные файлы для получения дополнительных деталей. После инициализации исходной таблицы мы можем создать RegexpTree с помощью источника таблицы:

Встроенные словари

Not supported in ClickHouse Cloud

примечание

This page is not applicable to ClickHouse Cloud. The feature documented here is not available in ClickHouse Cloud services. See the ClickHouse Cloud Compatibility guide for more information.

ClickHouse содержит встроенную функцию для работы с геобазой.

Это позволяет вам:

• Использовать идентификатор региона, чтобы получить его название на желаемом языке.
• Использовать идентификатор региона, чтобы получить идентификатор города, области, федерального округа, страны или континента.
• Проверять, является ли регион частью другого региона.
• Получать цепочку родительских регионов.

Все функции поддерживают "транслокальность", способность одновременно использовать разные перспективы владения регионами. Дополнительную информацию см. в разделе "Функции для работы с словарями веб-аналитики".

Внутренние словари отключены в стандартном пакете. Чтобы включить их, раскомментируйте параметры path_to_regions_hierarchy_file и path_to_regions_names_files в файле конфигурации сервера.

Геобаза загружается из текстовых файлов.

Поместите файлы regions_hierarchy*.txt в директорию, указанную параметром path_to_regions_hierarchy_file. Этот параметр конфигурации должен содержать путь к файлу regions_hierarchy.txt (стандартная региональная иерархия), а остальные файлы (regions_hierarchy_ua.txt) должны находиться в той же директории.

Поместите файлы regions_names_*.txt в директорию, указанную параметром path_to_regions_names_files.

Вы также можете создать эти файлы самостоятельно. Формат файлов следующий:

regions_hierarchy*.txt: Таблица с разделителями (без заголовка), колонки:

• идентификатор региона (UInt32)
• идентификатор родительского региона (UInt32)
• тип региона (UInt8): 1 - континент, 3 - страна, 4 - федеральный округ, 5 - регион, 6 - город; другие типы не имеют значений
• население (UInt32) — необязательный столбец

regions_names_*.txt: Таблица с разделителями (без заголовка), колонки:

• идентификатор региона (UInt32)
• название региона (String) — Не может содержать табуляцию или символы новой строки, даже экранированные.

Для хранения в оперативной памяти используется плоский массив. По этой причине идентификаторы не должны превышать миллиона.

Словари могут быть обновлены без перезапуска сервера. Однако набор доступных словарей не обновляется. Для обновлений проверяются времена модификации файлов. Если файл изменен, словарь обновляется. Интервал проверки изменений настраивается в параметре builtin_dictionaries_reload_interval. Обновления словарей (кроме загрузки при первом использовании) не блокируют запросы. Во время обновления запросы используют старые версии словарей. Если возникает ошибка во время обновления, ошибка записывается в журнал сервера, и запросы продолжают использовать старую версию словарей.

Мы рекомендуем периодически обновлять словари с геобазой. Во время обновления создавайте новые файлы и записывайте их в отдельное место. Когда все будет готово, переименуйте их в файлы, используемые сервером.

Существуют также функции для работы с идентификаторами ОС и поисковыми системами, но их не следует использовать.