Словари
Словарь — хранилище данных типа ключ/значение, которое полностью или частично хранится в оперативной памяти сервера 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>']
где:
-
PRIMARY KEY
— ключ словаря; -
SOURCE
— источник данных для словаря; -
LAYOUT
— способ хранения словаря в памяти; -
LIFETIME
— частота обновления словаря; -
SETTINGS
— дополнительные настройки, которые можно указать для словаря, источником данных которого является локальный файл, ресурс HTTP(s) или таблица ClickHouse; -
COMMENT
— комментарий к словарю.
Ключ и атрибуты словаря
Структуру словаря описывают ключ и атрибуты, которые указываются как параметры в запросе 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>
— настройки источника.
Значение | Источник данных |
---|---|
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>
— способ хранения словаря в памяти.
Значение | Способ хранения словаря |
---|---|
FLAT |
Словарь полностью хранится в оперативной памяти в виде плоских массивов |
HASHED, COMPLEX_KEY_HASHED |
Словарь полностью хранится в оперативной памяти в виде хеш-таблиц |
SPARSE_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 |
Тип размещения словаря для получения метаданных (например, номер |
Значения с префиксом 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.
Пример
-
Создайте таблицу
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 │ ------------------
-
Создайте на кластере словарь, который будет считывать данные из таблицы
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>
, если 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 │ └───────────────────────────────────────┘
Пример
-
Создайте таблицу
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 │ ------------------------------
-
Выполните следующий запрос, чтобы считать имена пользователей из таблицы
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>];