Словари

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

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

Настроить ADQM для подключения словарей из внешних источников можно с помощью конфигурационных XML-файлов или DDL-запросов. В этой статье описывается второй подход (рекомендуемый) — приводится базовый синтаксис и примеры запросов для создания/конфигурирования/удаления словарей в ADQM, а также для чтения данных из словарей с помощью специальных функций.

Создание и конфигурирование словаря

Чтобы создать и настроить словарь в ADQM, используйте запрос CREATE DICTIONARY. Общий синтаксис:

CREATE DICTIONARY [OR REPLACE][IF NOT EXISTS] [<database_name>.]<dictionary_name> [ON CLUSTER <cluster_name>]
(   <key1_name> <key1_data_type>,
    <key2_name> <key2_data_type>,
    <attr1_name> <attr1_data_type> [DEFAULT <default_value1>] [EXPRESSION <expr1>] [HIERARCHICAL] [INJECTIVE] [IS_OBJECT_ID],
    <attr2_name> <attr2_data_type> [DEFAULT <default_value2>] [EXPRESSION <expr2>] [HIERARCHICAL] [INJECTIVE] [IS_OBJECT_ID])
PRIMARY KEY <key1_name>, <key2_name>
SOURCE(<SOURCE_TYPE>([<param1_name> <param1_value> ... <paramN_name> <paramN_value>]))
LAYOUT(<LAYOUT_NAME>([<param_name> <param_value>]))
LIFETIME({MIN <min_value> MAX <max_value> | <max_value>})
[SETTINGS(<setting_name> = <setting_value>, <setting_name> = <setting_value>, ...)]
[COMMENT '<comment_text>']

где:

Ключ и атрибуты словаря

Структуру словаря описывают ключ и атрибуты, которые указываются как параметры в запросе CREATE DICTIONARY.

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

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

    • Числовой ключ — столбец типа UInt64.

      CREATE DICTIONARY <dictionary_name> (
          <key_name> UInt64,
          ...
          )
      PRIMARY KEY <key_name>
      ...

      где <key_name> — имя столбца с ключами.

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

      CREATE DICTIONARY <dictionary_name> (
          <key1_name> <key1_data_type>,
          <key2_name> <key2_data_type>
          ...
          )
      PRIMARY KEY <key1_name>, <key2_name>
      ...

      где <key1_name>, <key2_name> — имена столбцов с ключами, <key1_data_type>, <key2_data_type> — типы ключей.

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

    CREATE DICTIONARY <dictionary_name> (
        ...
        <attribute_name> <attribute_data_type> [DEFAULT <default_value>] [EXPRESSION <expr>] [HIERARCHICAL] [INJECTIVE] [IS_OBJECT_ID]
        )
    ...
    Параметры и основные выражения

    <attribute_name>

    Имя столбца в источнике данных

    <attribute_data_type>

    Тип данных, к которому будет приводиться значение из таблицы источника словаря.

    Поддерживаются типы: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, UUID, Decimal32, Decimal64, Decimal128, Decimal256, String, Array.

    Возможность использовать типы Nullable зависит от способа размещения словаря в памяти (например, для атрибутов словарей IP_TRIE в настоящее время типы Nullable не поддерживаются)

    DEFAULT

    Значение по умолчанию для элемента, не найденного по ключу. Значение NULL можно указывать, только если тип атрибута Nullable

    EXPRESSION

    Выражение, которое выполняется для значения

    HIERARCHICAL

    Указывает, что атрибут содержит значение родительского ключа для текущего ключа (используется для конфигурирования иерархического словаря)

    INJECTIVE

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

    IS_OBJECT_ID

    Указывает, что запрос выполняется для документа MongoDB по ObjectID

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

Источник данных для словаря в запросе CREATE DICTIONARY указывается с помощью выражения SOURCE:

CREATE DICTIONARY <dictionary_name> (...)
...
SOURCE(<SOURCE_TYPE>([<param1_name> <param1_value> ... <paramN_name> <paramN_value>]))
...

где <SOURCE_TYPE> — тип источника данных, <paramX_name> <paramX_value> — настройки источника.

Возможные значения <SOURCE_TYPE>
Значение Источник данных

FILE

Локальный файл (файл должен быть расположен в папке user_files)

HTTP

HTTP-ресурс

MYSQL

Таблица из базы данных MySQL

CLICKHOUSE

Таблица из базы данных ClickHouse

MONGODB

Коллекция из базы данных MongoDB

REDIS

База данных Redis

CASSANDRA

Семейство столбцов (таблица) из пространства ключей (базы данных) Cassandra

POSTGRESQL

Таблица из базы данных PostgreSQL

ODBC

Таблица из любой базы данных, которая имеет драйвер ODBC

Примеры подключения словарей к различным типам источников приведены в разделе Dictionary Sources документации ClickHouse.

Размещение словаря в памяти

Способ хранения словаря в памяти указывается с помощью выражения LAYOUT в запросе CREATE DICTIONARY:

CREATE DICTIONARY <dictionary_name> (...)
...
LAYOUT(<LAYOUT_TYPE>([<param_name> <param_value>]))
...

где <LAYOUT_TYPE> — способ хранения словаря в памяти.

Возможные значения <LAYOUT_TYPE>
Значение Способ хранения словаря

FLAT

Словарь полностью хранится в оперативной памяти в виде плоских массивов

HASHED, COMPLEX_KEY_HASHED

Словарь полностью хранится в оперативной памяти в виде хеш-таблиц

SPARSE_HASHED, COMPLEX_KEY_SPARSE_HASHED

Словарь полностью хранится в оперативной памяти в виде хеш-таблиц аналогично HASHED/COMPLEX_KEY_HASHED, но занимает меньше места в памяти и генерирует более высокую загрузку CPU

HASHED_ARRAY, COMPLEX_KEY_HASHED_ARRAY

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

RANGE_HASHED, COMPLEX_KEY_RANGE_HASHED

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

CACHE, COMPLEX_KEY_CACHE

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

SSD_CACHE, COMPLEX_KEY_SSD_CACHE

Словарь хранится в кеше аналогично SSD_CACHE/COMPLEX_KEY_SSD_CACHE, но данные хранятся на SSD, а индекс в оперативной памяти

DIRECT, COMPLEX_KEY_DIRECT

Словарь не хранит данные локально и взаимодействует с источником непосредственно в момент запроса

IP_TRIE

Тип размещения словаря для получения метаданных (например, номер AS или код страны) по IP-адресам. У словаря должен быть составной ключ, который содержит один элемент типа String — IP-адрес

Значения с префиксом COMPLEX_KEY используются для словарей с составным ключом.

Особенности всех вариантов размещения словарей в памяти описаны в разделе Storing Dictionaries in Memory документации ClickHouse.

Обновление словаря

С помощью выражения LIFETIME в запросе CREATE DICTIONARY можно указать интервал автоматического обновления словаря в секундах.

CREATE DICTIONARY <dictionary_name> (...)
...
LIFETIME(<max_value> | MIN <min_value> MAX <max_value>)
...

где:

  • <max_value> — интервал обновления словаря. При LIFETIME(0) обновление словаря отключено.

  • MIN <min_value> MAX <max_value> — интервал, внутри которого случайным образом выбирается время для обновления словаря. Такая настройка может быть полезна для распределения нагрузки на источник данных при обновлении словаря на большом количестве серверов. При MIN 0 MAX 0 словарь не перезагружается по истечении времени. В этом случае данные словаря могут быть перезагружены командой SYSTEM RELOAD DICTIONARY.

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

Логика обновления словаря зависит от типа источника и способа хранения словаря в памяти — подробнее об этом в разделе Dictionary Updates документации ClickHouse.

Пример

  1. Создайте таблицу roles_dict, которая будет источником данных для словаря:

    CREATE TABLE roles_dict (`id` Int32, `role` String) ENGINE = MergeTree() ORDER BY id;
    INSERT INTO roles_dict VALUES (10, 'admin'), (20, 'owner'), (30, 'author'), (40, 'reviewer'), (50, 'editor'), (60, 'view only');
    --id─┬─role-------
    │ 10 │ admin     │
    │ 20 │ owner     │
    │ 30 │ author    │
    │ 40 │ reviewer  │
    │ 50 │ editor    │
    │ 60 │ view only │
    ------------------
  2. Создайте на кластере словарь, который будет считывать данные из таблицы roles_dict:

    CREATE DICTIONARY roles_dict_clickhouse (`id` Int32, `role` String DEFAULT 'no role assigned')
    PRIMARY KEY id
    SOURCE(CLICKHOUSE(HOST 'localhost' USER 'default' PASSWORD '' DATABASE 'default' TABLE 'roles_dict'))
    LIFETIME(MIN 10 MAX 20)
    LAYOUT(FLAT());

Просмотр информации о словарях

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

SELECT * FROM system.dictionaries WHERE name = '<dictionary_name>' [FORMAT <format_name>];
Пример

 
Запрос:

SELECT * FROM system.dictionaries WHERE name = 'roles_dict_clickhouse' FORMAT Vertical;

Пример вывода на экран (словарь сконфигурирован, но не загружен):

Row 1:
──────
database:                           default
name:                               roles_dict_clickhouse
uuid:                               4e28febc-b472-423c-bc14-bd29f195390f
status:                             NOT_LOADED
origin:                             4e28febc-b472-423c-bc14-bd29f195390f
type:
key.names:                          ['id']
key.types:                          ['UInt64']
attribute.names:                    ['role']
attribute.types:                    ['String']
bytes_allocated:                    0
hierarchical_index_bytes_allocated: 0
query_count:                        0
hit_rate:                           0
found_rate:                         0
element_count:                      0
load_factor:                        0
source:
lifetime_min:                       0
lifetime_max:                       0
loading_start_time:                 1970-01-01 00:00:00
last_successful_update_time:        1970-01-01 00:00:00
loading_duration:                   0
last_exception:
comment:

Чтобы получить имена всех словарей, существующих в базе данных, можно использовать запрос SHOW DICTIONARIES:

SHOW DICTIONARIES FROM <database_name>;

Загрузка словарей

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

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

SYSTEM RELOAD DICTIONARIES;
SYSTEM RELOAD DICTIONARY <dictionary_name>;

После загрузки словаря в таблице system.dictionaries меняется статус словаря на LOADED, а также записываются значения конфигурационных параметров словаря и различные метрики (такие как количество занятой словарем RAM или количество запросов к словарю с момента его успешной загрузки). Например:

SELECT * FROM system.dictionaries WHERE name = 'roles_dict_clickhouse' FORMAT Vertical;
Row 1:
──────
database:                           default
name:                               roles_dict_clickhouse
uuid:                               4e28febc-b472-423c-bc14-bd29f195390f
status:                             LOADED
origin:                             4e28febc-b472-423c-bc14-bd29f195390f
type:                               Flat
key.names:                          ['id']
key.types:                          ['UInt64']
attribute.names:                    ['role']
attribute.types:                    ['String']
bytes_allocated:                    21048
hierarchical_index_bytes_allocated: 0
query_count:                        0
hit_rate:                           1
found_rate:                         0
element_count:                      6
load_factor:                        0.005859375
source:                             ClickHouse: default.roles_dict
lifetime_min:                       10
lifetime_max:                       20
loading_start_time:                 2023-03-14 11:05:13
last_successful_update_time:        2023-03-14 11:05:13
loading_duration:                   0.001
last_exception:
comment:

Использование словаря

Для работы со словарями ADQM поддерживает набор специальных функций.

dictGet, dictGetOrDefault, dictGetOrNull

Функции dictGet, dictGetOrDefault и dictGetOrNull извлекают значения из словаря.

dictGet('<dictionary_name>', <attr_name>, <id_expr>)
dictGetOrDefault('<dictionary_name>', <attr_name>, <id_expr>, <default_value_expr>)
dictGetOrNull('<dictionary_name>', <attr_name>, <id_expr>)
Аргументы

<dictionary_name>

Имя словаря. Указывается как строковый литерал

<attr_name>

Имя столбца в источнике словаря (cтроковый литерал) или кортеж имен столбцов (tuple)

<id_expr>

Значение ключа словаря. Указывается как выражение, возвращающее значение типа UInt64, или кортеж выражений (tuple), в зависимости от того, числовой или составной ключ определен в конфигурации словаря

<default_value_expr>

Значение, возвращаемое, если в словаре не найдена строка с заданным ключом <id_expr>. Указывается как выражение, возвращающее значение типа данных, сконфигурированного для атрибута <attr_name>, или кортеж выражений

Возвращаемое значение:

  • значение атрибута, соответствующее ключу <id_expr>, если ADQM смог привести это значение к типу данных, указанному для атрибута в конфигурации словаря;

  • если ключ <id_expr> в словаре не найден:

    • dictGet возвращает значение, указанное через выражение DEFAULT для атрибута при конфигурировании словаря;

    • dictGetOrDefault возвращает значение <default_value_expr>;

    • dictGetOrNull возвращает NULL.

Например, следующий запрос возвращает из словаря roles_dict_clickhouse роль с идентификатором 20:

SELECT dictGet('roles_dict_clickhouse', 'role', 20);
┌─dictGet('roles_dict_clickhouse', 'role', 20)─┐
│ owner                                        │
└──────────────────────────────────────────────┘

ADQM поддерживает также специализированные функции, которые приводят значения атрибутов словаря к определённому типу данных независимо от конфигурации словаря:

  • dictGetInt8, dictGetInt16, dictGetInt32, dictGetInt64;

  • dictGetUInt8, dictGetUInt16, dictGetUInt32, dictGetUInt64;

  • dictGetFloat32, dictGetFloat64;

  • dictGetDate;

  • dictGetDateTime;

  • dictGetUUID;

  • dictGetString.

Синтаксис этих функций аналогичен синтаксису dictGet, который приведен выше. Любую из этих функций можно использовать с суффиксом OrDefault (например, dictGetInt8OrDefault) и указать в аргументах значение <default_value_expr>, которое будет возвращаться, если ключ в словаре не найден.

dictHas

Функция dictHas проверяет, присутствует ли запись с указанным ключом в словаре.

dictHas('<dictionary_name>', <id_expr>)
Аргументы

<dictionary_name>

Имя словаря. Указывается как строковый литерал

<id_expr>

Значение ключа словаря. Указывается как выражение, возвращающее значение типа UInt64, или кортеж выражений (tuple), в зависимости от того, числовой или составной ключ определен в конфигурации словаря

Функция возвращает:

  • 0 — если ключ в словаре не найден;

  • 1 — если ключ есть.

Например, следующий запрос проверяет, есть ли в словаре roles_dict_clickhouse роль с идентификатором 100:

SELECT dictHas('roles_dict_clickhouse', 100);
┌─dictHas('roles_dict_clickhouse', 100)─┐
│                                     0 │
└───────────────────────────────────────┘

Пример

  1. Создайте таблицу users, которая содержит имена пользователей и идентификаторы назначенных пользователям ролей:

    CREATE TABLE users (user_id Int32, name String, role_id Int32) ENGINE = MergeTree ORDER BY user_id;
    INSERT INTO users VALUES (1, 'john', 10), (2, 'mary', 30), (3, 'andrew', 40), (4, 'harry', 70), (5, 'ann', 50);
    --user_id---name-----role_id--
    │       1 │ john   │      10 │
    │       2 │ mary   │      30 │
    │       3 │ andrew │      40 │
    │       4 │ harry  │      70 │
    │       5 │ ann    │      50 │
    ------------------------------
  2. Выполните следующий запрос, чтобы считать имена пользователей из таблицы users и найти названия их ролей в словаре roles_dict_clickhouse по идентификаторам role_id:

    SELECT  name AS user, dictGet('roles_dict_clickhouse', 'role', toUInt64(role_id)) AS role FROM users;

    Результат:

    --user-----role--------------
    │ john   │ admin            │
    │ mary   │ author           │
    │ andrew │ reviewer         │
    │ harry  │ no role assigned │
    │ ann    │ editor           │
    -----------------------------

Отключение и подключение словаря

Словарь можно отключить с помощью запроса DETACH DICTIONARY:

DETACH DICTIONARY [IF EXISTS] [<database_name.]<dictionary_name> [ON CLUSTER <cluster_name>] [PERMANENTLY];

При выполнении этого запроса словарь становится "невидимым" для сервера, но его метаданные не удаляются. Если отключение не было перманентным (ключевое слово PERMANENTLY не использовалось в запросе), при следующем запуске сервер прочитает метаданные и снова будет "видеть" словарь. Если словарь был отключен перманентно, то сервер не подключит его обратно автоматически.

Независимо от того, каким способом словарь был отключен, его можно подключить обратно с помощью запроса ATTACH:

ATTACH DICTIONARY  [IF NOT EXISTS] [<database_name.]<dictionary_name> [ON CLUSTER <cluster_name>];

Удаление словаря

Удалить словарь можно с помощью запроса DROP DICTIONARY:

DROP DICTIONARY [IF EXISTS] [<database_name>.]<dictionary_name> [ON CLUSTER <cluster_name>];
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней