Словари

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

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, но использует меньше памяти в угоду большему использованию ЦП.

Ключ словаря имеет тип 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, значение ячейки не используется, а ключ становится недействительным. Ключ запрашивается снова в следующий раз, когда его нужно использовать. Это поведение можно настроить с помощью настройки 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>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 не могут использоваться одновременно. И одно из полей 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 – Запрос для проверки статуса словаря. Необязательный параметр. Подробнее в разделе Refreshing dictionary data using LIFETIME.
• background_reconnect – Повторное подключение к реплике в фоновом режиме, если подключение не удалось. Необязательный параметр.
• query – Пользовательский запрос. Необязательный параметр.

примечание

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

Null

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

Dictionary Key and Fields

подсказка

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

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

XML описание:

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

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

DDL запрос:

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

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

Key

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

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

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

примечание

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

Numeric Key

Тип: UInt64.

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

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

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

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

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

Composite Key

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

подсказка

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

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

или

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

Attributes

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

или

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

Tag	Description	Required
name	Имя колонки.	Yes
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 не поддерживаются.	Yes
null_value	Значение по умолчанию для несуществующего элемента. В примере это пустая строка. Значение NULL может использоваться только для типов Nullable (см. предыдущую строку с описанием типов).	Yes
expression	Выражение, которое ClickHouse выполняет над значением. Выражение может быть именем колонки в удаленной SQL базе данных. Таким образом, вы можете использовать его для создания псевдонима для удаленной колонки. Значение по умолчанию: без выражения.	No
hierarchical	Если true, атрибут содержит значение родительского ключа для текущего ключа. См. Иерархические словари. Значение по умолчанию: false.	No
injective	Флаг, который показывает, является ли образ id -> attribute инъективным. Если true, ClickHouse может автоматически разместить после оператора GROUP BY запросы к словарям с инъекцией. Обычно это значительно уменьшает количество таких запросов. Значение по умолчанию: false.	No
is_object_id	Флаг, показывающий, выполняется ли запрос для документа MongoDB по ObjectID. Значение по умолчанию: false.

Hierarchical Dictionaries

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-запросе.

Запрос:

Результат:

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

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

Использование словаря дерева регулярных выражений в 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-файла конфигурации мы можем использовать словари дерева регулярных выражений как парсер строк user agent. Мы поддерживаем 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: id узла RegexpTree.
• parent_id UInt64: id родителя узла.
• 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: TabSeparated (без заголовка), колонки:

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

regions_names_*.txt: TabSeparated (без заголовка), колонки:

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

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

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

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

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