Словари

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

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

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

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

Учебник

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

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

ClickHouse:

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

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

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

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

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

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

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

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

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

Not supported in ClickHouse Cloud

примечание

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

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

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

примечание

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

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

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

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

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

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

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

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

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

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

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

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

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

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

Соответствующий 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, но использует меньше памяти в ущерб большему использованию CPU.

Ключ словаря имеет тип 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. Этот метод хранения работает так же, как и хеш, и позволяет использовать диапазоны по дате/времени (произвольный числовой тип) в дополнение к ключу.

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

Чтобы использовать образец для диапазонов по датам, определите элементы 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, значение ячейки не используется, а ключ становится устаревшим. Ключ будет заново запрашиваться в следующий раз, когда потребуется использовать его. Это поведение можно настроить с помощью настройки 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 — это интервал обновления для полностью загруженных словарей и интервал недействительности для кэшированных словарей.

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

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

или

Установка <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 вычитается из предыдущего времени обновления перед запросом обновленных данных.

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

или

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

Словарь может быть подключен к 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 запрашивает необходимые ключи, отправляя запрос на STDIN исполняемого файла. В противном случае 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 схем.

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

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

Поля настройки:

• 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 не могут использоваться вместе. Необходимо объявить либо одно из этих полей, либо другое.

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

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

Известная уязвимость функциональности 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. И должно быть объявлено либо одно из этих полей, либо другое.

примечание

Не существует явного параметра 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. И должно быть объявлено либо одно из этих полей, либо другое.

MongoDB

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

или

или

Поля настройки:

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

или

Поля настройки:

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

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

или

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

• 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

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

Словарь ключ и поля

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

Клаузула 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>. Поля ключа задаются в таком же формате, как и атрибуты словаря attributes. Пример:

или

Для запроса к функции 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 позволяет вам получить цепочку родителей элемента.

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

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

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

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

подсказка

Если вы используете словарь с ClickHouse Cloud, пожалуйста, используйте опцию DDL запроса для создания ваших словарей и создайте ваш словарь как пользователь default. Также проверьте список поддерживаемых источников словарей в руководстве по совместимости с облаком.

Соответствующий 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

примечание

Эта страница не применяется к ClickHouse Cloud. Функция, описанная здесь, недоступна в сервисах ClickHouse Cloud. Смотрите руководство по Cloud Compatibility для получения дополнительной информации.

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

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

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

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

Внутренние словари отключены в стандартной сборке. Чтобы включить их, раскомментируйте параметры 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: Табличный (без заголовка), столбцы:

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

regions_names_*.txt: Табличный (без заголовка), столбцы:

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

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

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

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

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