АРХИТЕКТУРА ГИБРИДНОГО ХРАНИЛИЩА ДАННЫХ ДЛЯ АНАЛИТИЧЕСКОЙ ОБРАБОТКИ ТРАНЗАКЦИОННЫХ ЛОГОВ ЭЛЕКТРОННОГО ПРАВИТЕЛЬСТВА

ARCHITECTURE OF A HYBRID DATA WAREHOUSE FOR ANALYTICAL PROCESSING OF E-GOVERNMENT TRANSACTION LOGS

Vlasov Egor Aleksandrovich

4th year full-time student, The North-Western Institute of Management is a branch of the Russian Presidential Academy of National Economy and Public Administration,

Russia, St. Petersburg

АННОТАЦИЯ

В статье рассматривается проблема высокой вычислительной нагрузки на реляционные системы управления базами данных при агрегации и анализе неструктурированных системных логов в инфраструктуре электронного правительства (СМЭВ, ЕПГУ). Автором спроектирована гибридная даталогическая архитектура хранилища данных (DWH) на базе схемы «Звезда», сочетающая колоночную СУБД ClickHouse для хранения миллиардов транзакционных фактов и реляционную СУБД PostgreSQL для управления нормативно-справочной информацией. Продемонстрирован механизм интеграции компонентов через внешние словари в оперативной памяти (RAM), исключающий ресурсные операции соединения таблиц на диске.

ABSTRACT

The article addresses the problem of high computational load on relational database management systems during the aggregation and analysis of unstructured system logs within the e-government infrastructure (SMEV, EPGU). The author designs a hybrid datalogical data warehouse (DWH) architecture based on the star schema, combining the column-oriented DBMS ClickHouse for storing billions of transactional facts and the relational DBMS PostgreSQL for reference data management. A component integration mechanism using external dictionaries in random-access memory (RAM) is presented, which eliminates resource-intensive table join operations on disk.

Ключевые слова: хранилище данных, ClickHouse, PostgreSQL, СМЭВ, схема «Звезда», внешние словари, бизнес-аналитика, электронное правительство.

Keywords: data warehouse, ClickHouse, PostgreSQL, SMEV, star schema, external dictionaries, business intelligence, e-government.

Масштабная цифровая трансформация государственного управления в Российской Федерации и перевод ведомственных сервисов на единую платформу приводит к кратному росту объемов транзакционного трафика через интеграционные шлюзы Системы межведомственного электронного взаимодействия (СМЭВ) и Единого портала государственных услуг (ЕПГУ). Инфраструктурные операторы ежедневно фиксируют миллионы системных событий. Попытки агрегации и вычисления аналитических показателей доступности сервисов (SLA) непосредственно в операционных реляционных СУБД (OLTP-архитектурах) приводят к исчерпанию аппаратных ресурсов серверов и задержкам обработки запросов. Высокая степень нормализации данных и необходимость выполнения ресурсоемких операций JOIN над текстовыми логами требуют перехода к специализированным аналитическим хранилищам данных (DWH).

Для разрешения технологического противоречия между необходимостью непрерывного анализа логов и ограничениями дисковой подсистемы автором разработана гибридная архитектура DWH, основанная на денормализованной модели «Звезда» (Star Schema).

В качестве фундамента аналитического контура выбрана колоночная СУБД ClickHouse (движок MergeTree). Выбор данного решения обусловлен высокой эффективностью алгоритмов сжатия данных и высокой скоростью выполнения агрегационных SQL-запросов (SUM, COUNT, AVG) по плоским таблицам без использования индексов по B-деревьям. Central-компонентом хранилища выступает транзакционная таблица фактов fact_service_requests, фиксирующая отдельные атомарные события в жизненном цикле заявлений граждан.

Для оптимизации использования дискового пространства из таблицы фактов полностью исключены текстовые атрибуты (наименования ведомств, описания ошибок, названия услуг). Они заменены компактными числовыми идентификаторами. Спецификация полей физической модели в ClickHouse представлена в таблице 1.

Таблица 1.

Структура таблицы фактов fact_service_requests в СУБД ClickHouse

Текстовые справочники и иерархические структуры государственных органов характеризуются высокими требованиями к транзакционности (ACID) и ссылочной целостности, но низким объемом данных. В связи с этим нормативно-справочная информация (НСИ) изолирована в реляционной СУБД PostgreSQL. Были спроектированы таблицы измерений: dim_services (реестр услуг), dim_departments (структура ОИВ), dim_errors (классификатор сбоев) и dim_time (календарная аналитика).

Для исключения межсетевых накладных расходов при выполнении аналитических запросов интеграция ClickHouse и PostgreSQL реализована с помощью механизма внешних словарей (External Dictionaries). ClickHouse по расписанию считывает таблицы измерений из PostgreSQL и удерживает их в оперативной памяти (RAM) в виде хэш-таблиц. При генерации BI-дашбордов оперативная подтяжка текстовых наименований ведомств и услуг происходит непосредственно в RAM со скоростью колоночного сканирования, не нагружая PostgreSQL и дисковые массивы.

Особое архитектурное значение имеет измерение времени dim_time, использующее суррогатный целочисленный ключ time_id формата YYYYMMDD. Наличие флага is_holiday позволяет алгоритмам расчета SLA автоматически исключать выходные и праздничные дни из расчетов, обеспечивая корректность оценки регламентных сроков.

Спроектированная гибридная даталогическая архитектура хранилища данных позволяет эффективно разграничить вычислительные нагрузки: ClickHouse обеспечивает масштабируемое хранение и мгновенный анализ миллиардов логов, а PostgreSQL гарантирует строгое ведение нормативно-справочной информации. Использование внешних словарей в RAM полностью устраняет блокировки дисковой подсистемы на операциях JOIN. Разработанное решение создает технологическую основу для внедрения проактивного BI-мониторинга в органах государственной власти.

Список литературы:

1. Дмитриев К. А., Соколова И. В. Сравнительный анализ отечественных BI-систем в рамках реализации стратегии импортозамещения программного обеспечения // Вестник компьютерных и информационных технологий. 2024. Т. 21, № 4. С. 34–45.
2. Таненбаум Э. Распределенные системы. Принципы и парадигмы / Э. Таненбаум, М. ван Стеен. – Санкт-Петербург : Питер, 2003. – 877 с.
3. Турбан Э. Бизнес-аналитика: анализ и управление данными / Э. Турбан, Р. Шарда, Д. Деллен. – Санкт-Петербург : Питер, 2021. – 336 с.
4. Технологический портал СМЭВ [Электронный ресурс] // Министерство цифрового развития, связи и массовых коммуникаций РФ. – URL: https://nsud.gosuslugi.ru/ (дата обращения: 02.05.2026).