Что такое словарь в ClickHouse, какие бывают словари, как их создать и каким командами к ним обращаться. Пара примеров со словарями в самой популярной колоночной аналитической СУБД.
Что такое словарь в ClickHouse
Как колоночная база данных, ClickHouse предназначена для аналитической обработки огромных объемов данных в реальном времени. Аналитические сценарии предполагают изучение метрик и создание отчетов по сотням атрибутов. Если некоторые значения используются очень часто и повторяются во множестве сценариев, их целесообразно хранить в области ближнего доступа, аналогично кэшу памяти или жесткого диска. В реляционных СУБД для этого используются материализованные представления – заранее вычисленные результаты, которые можно переиспользовать при выполнении SQL-запросов. Например, это может сократить количество операций соединения, которые считаются ресурсоемкими и не очень производительными. А, поскольку ClickHouse не очень хорошо поддерживает операторы JOIN, о чем мы недавно писали здесь, подобная идея для этой колоночной СУБД весь актуальна.
ClickHouse позволяет создавать представления, как обычные, которые не хранят данных, а выполняют чтение данных из другой таблицы при каждом доступе, так и материализованные, которые хранят данные, преобразованные запросом с оператором SELECT. Про работу с представлениями и триггерами в ClickHouse мы поговорим в следующий раз, а пока вернемся к замечанию, что вместо ресурсоемких операций соединения с JOIN в этой колоночной СУБД рекомендуется использовать словари. Это примитивная структура данных по принципу ключ-значение, полностью или частично сохраняемая в оперативной памяти сервера ClickHouse.
Словари работают намного быстрее запросов с JOIN-операторами, что заметно в сценариях, когда надо часто обращаться к какому-то справочнику, чтобы получить набор значений по ключу. Для этого используются специализированные функций, которые выполняются намного быстрее обычных SQL-запросов. Однако, данные из словаря можно только прочитать с помощью SQL-запроса.
Посмотрим, как это сделать, на примере данных в открытой онлайн-песочнице, которая доступна по URL-адресу https://play.clickhouse.com/play. Чтобы получить список доступных словарей, выполним команду отображения разрешений для пользователя play:
show grants
В этой ClickHouse-песочнице доступные пользователю play для просмотра словари хранятся в таблицах country_iso_codes, country_polygons, stations_dict в базе данных blogs. Даже по названию таблиц можно сделать выводы о словарях, которые в них хранятся: это коды стран, их граничные координаты для вычисления площади, сведения о городах. Например, посмотрим, содержимое таблицы country_iso_codes с помощью запроса
select * from blogs.country_iso_codes
Данные, которые хранятся в словарях, меняются очень редко, поэтому их можно лишь читать. Например, прочитаем данные из словаря с кодами стран, задав запрос:
SELECT
dictGet('blogs.country_iso_codes', 'name', tuple('RS')) AS country,
toTypeName(country) AS type
FROM
blogs.country_iso_codes
Group BY country
Этот запрос извлекает имя страны для кода ‘RS’ из словаря country_iso_codes. Если в словаре есть запись с ключом ‘RS’, возвращается название этой страны и тип данных этого столбца. Для лаконичного вывода я сгруппировала строки по названию страны.
Какие бывают словари и как создать свой словарь
ClickHouse имеет встроенные (внутренние) словари. Например, полностью управляемый сервис ClickHouse на платформе Яндекса (Managed Service for ClickHouse) содержит встроенный словарь-геобазу и набор функций для работы с ним, позволяющие получить имя региона на нужном языке по его идентификатору, а также узнать id города, области, округа, страны или континента по идентификатору региона. Также можно проверить, входит ли один регион в другой и получить цепочку родительских регионов.
ClickHouse хранит словари в оперативной памяти полностью или частично, периодически обновляет их и динамически подгружает отсутствующие значения. Помимо встроенных словарей, можно подключать собственные словари из различных источников данных, загрузив xml-файл, HTTP-ресурс или другую СУБД. Также можно создать словарь, используя DDL-запросы. Конфигурация внешних словарей может находиться в одном или нескольких xml-файлах. Путь к конфигурации указывается в параметре dictionaries_config. Загружать словарь можно при старте сервера или при первом использовании, в зависимости от настройки dictionaries_lazy_load.
Например, следующий xml-файл описывает данные словаря в CSV-файле под названием ext-dict-example. Его можно использовать для оптимизации запросов к базе данных, например, для замены кодов состояний на их строковые представления без необходимости делать дополнительные запросы к основным таблицам.
<clickhouse>
<dictionary>
<name>ext-dict-example</name>
<source>
<file>
<path>/path-to/ext-dict-example.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat />
</layout>
<structure>
<id>
<name>code</name>
</id>
<attribute>
<name>state</name>
<type>String</type>
<null_value>new</null_value>
</attribute>
</structure>
<lifetime>10</lifetime>
</dictionary>
</clickhouse>
Тег <layout> определяет способ хранения данных в памяти, а <flat> говорит о плоской структуре словаря, без вложенных объектов. Тегом <id> помечено поле, используемое в качестве уникального идентификатора элементов словаря, а тегом <name> — его название, в данном примере code. Тег <attribute> описывает атрибутивные, дополнительные данные. С помощью <name> задается название, в этом примере state, а с помощью <type> — тип данных, в этом примере string. Тегом <null_value> задается значение по умолчанию для отсутствующих данных, в примере это new. С помощью тега <lifetime> можно задать время жизни словаря в кэше перед обновлением, например, 10 секунд.
К сожалению, из-за отсутствия разрешений на создание своего словаря в онлайн-песочнице ClickHouse мне не удалось создать свой словарь с помощью DDL-запросов. Также не удалось загрузить словарь с помощью xml-файла или подключить внешнюю СУБД, например, MongoDB, MySQL или PostgreSQL. Однако, вы можете сделать это самостоятельно на тестовом аккаунте Managed Service for ClickHouse или на нашем курсе по этой колоночной СУБД.
Читайте в новой статье про виртуальные таблицы (представления) в ClickHouse и особенности работы с ними.
Освойте администрирование и эксплуатацию ClickHouse для аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
