Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Автор статьи: Роман Козлов
Руководитель курса BI-аналитика
Ссылка на автора: @Krv87
В последние годы напряженная экономическая и политическая ситуация в связи с усилением международных санкций привела к уходу многих зарубежных компаний с рынка бизнес-аналитических систем и ограничению доступа к их продуктам и услугам. В частности, рынок бизнес-аналитики столкнулся с серьезными вызовами, так как многие российские предприятия и организации стали ограничены в использовании западных BI-систем. Однако эти трудности послужили стимулом для роста интереса к отечественным разработкам в сфере бизнес-аналитики.
Использование отечественных BI-систем может быть особенно полезно для компаний, работающих в отраслях, связанных с государственными заказами, обороной и безопасностью. Они также могут помочь компаниям, которые сталкиваются с ограничениями в использовании западных BI-систем из-за политических или экономических санкций.
В этой статье мы обратимся к практическому опыту использования отечественной бизнес-аналитической системы Modus BI для решения нетривиальной задачи по миграции данных из СУБД PostgresSQL в СУБД Clickhouse.
Изучение отечественных BI-систем предоставляет ценные практические знания о решениях, доступных на российском рынке, и их способности решать рутинные задачи аналитиков данных (таких, как миграция данных). Однако, для того чтобы полноценно оценить возможности и применимость этих систем в контексте глобального рынка бизнес-аналитики, важно рассмотреть и типовую структуру BI-систем в целом. Это позволит определить общие принципы и архитектурные решения, применяемые как в российских, так и в международных разработках.
Структура современных BI-систем
BI-системы, то есть системы бизнес-аналитики, часто включают в себя несколько ключевых компонентов (рис. 1), таких как:
Системы сбора данных, которые собирают данные из различных источников и сохраняют их в централизованной базе данных.
Системы хранения данных, которые хранят данные в удобном формате и обеспечивают быстрый доступ к ним.
Системы аналитики данных, которые позволяют анализировать данные и извлекать из них ценную информацию.
Инструменты BI-систем — мобильные или десктопные приложения для работы по построению дэшбордов.
Конечные пользователи — это люди или организации, которые используют продукты или услуги в конечной фазе их жизненного цикла. Конечные пользователи могут включать в себя широкий круг людей, таких как конечные потребители, бизнес-клиенты и государственные организации.
Источники данных
Источники данных для BI-систем могут быть внутренними или внешними для компании и включают в себя базы данных, системы управления контентом, системы управления отношениями с клиентами (CRM), системы управления производством, системы управления логистикой и складом, данные из социальных сетей, а также внешние источники, такие как данные о рынке и отчеты о производительности.
При миграции данных из разных источников в единое хранилище данных для анализа важно убедиться, что данные совместимы и могут быть объединены в единый набор данных для анализа. Это может включать проверку совместимости форматов данных, структуры данных, типов данных и т.д. Если данные не совместимы, то могут возникнуть проблемы при их объединении, что может привести к ошибкам в результате анализа. Поэтому важно уделить достаточное внимание этому этапу процесса миграции данных, чтобы обеспечить точность и достоверность результатов анализа.
ETL-процессы
ETL-процессы — это процессы извлечения, преобразования и загрузки данных в хранилище данных BI-системы. Шаги ETL-процессов могут включать очистку, преобразование и объединение данных из разных источников.
ETL-процессы важны для BI-систем и включают в себя процессы извлечения, преобразования и загрузки данных в хранилище данных BI-системы.
Извлечение: первый шаг — извлечение данных из разных источников, таких как базы данных, файлы, приложения и системы управления контентом.
Преобразование: далее данные могут потребовать преобразования, например, очистки, преобразования форматов и объединения данных из разных источников.
Загрузка: завершающий шаг — загрузка данных в хранилище данных BI- системы, которое может быть организовано в виде структурированных таблиц или неструктурированных файлов.
Хранилище и модели данных
Хранилище данных является центральным источником информации для принятия решений в организации, улучшая качество данных путем их объединения из разных источников и проверки на соответствие стандартам. Оно может быть реализовано с использованием различных моделей данных, таких как реляционные базы данных, хранилища данных на основе колонок, документов, файлов или графов, в зависимости от специфических потребностей организации.
Модели данных определяют, как данные будут организованы, храниться и использоваться, обеспечивая эффективное хранение и быстрый доступ к данным. Одним из примеров модели данных может быть иерархическая модель, которая организует данные в виде дерева или графа. Существуют также реляционные модели данных, которые используются для хранения данных в таблицах и связывания их друг с другом по ключам. Важно помнить, что правильный выбор модели данных может значительно повысить производительность и эффективность работы информационной системы.
OLAP кубы
OLAP (Online Analytical Processing) — это технология, которая используется для организации и анализа больших объемов данных. Она позволяет пользователям интерактивно анализировать многомерные данные с разных точек зрения. OLAP-кубы упрощают и ускоряют анализ данных, благодаря предварительной агрегации данных и предоставлению готовых результатов анализа.
OLAP-кубы создаются при помощи специальных инструментов, которые позволяют создавать кубы из разных источников данных, проводить агрегацию и преобразование данных, а также строить многомерные модели для анализа.
Важные части OLAP-кубов — это измерения и факты. Измерения — это атрибуты, по которым можно фильтровать и агрегировать данные. Например, в OLAP-кубе для анализа продаж могут быть измерения: год, квартал, продукт, регион, клиент и т.д. Факты — это числовые данные, которые агрегируются по измерениям. Например, сумма продаж, количество проданных единиц, средняя цена и т.д.
OLAP-кубы могут иметь множество измерений и фактов, а также сложные связи между ними. Правильно построенный OLAP-куб позволяет эффективно анализировать большие объемы данных и выявлять зависимости, которые не могут быть видны на первый взгляд. Кроме того, OLAP-кубы могут быть использованы для построения дашбордов. Например, можно построить дашборд для отслеживания продаж продукта в разных регионах или для отображения показателей производительности компании. Использование OLAP-кубов для построения дашбордов позволяет быстро и удобно анализировать большие объемы данных и получать ценную информацию для принятия важных решений.
Data Mining
Data Mining — это процесс автоматического извлечения интересной и неизвестной информации из больших объемов данных. Это помогает найти скрытые закономерности, анализировать данные и прогнозировать будущие события.
В системах бизнес-аналитики Data Mining используется для анализа данных и выявления новых возможностей для бизнеса. Например, с помощью Data Mining можно выявить скрытые закономерности между продуктами или услугами, которые могут быть использованы для улучшения маркетинговых стратегий. Также Data Mining может быть использован для анализа поведения клиентов, выявления паттернов их покупок и прогнозирования будущих продаж.
Data Mining использует различные методы и алгоритмы машинного обучения, такие как кластерный анализ, деревья решений, нейронные сети и многое другое.
Инструменты BI-систем
Инструменты BI-систем — это программный комплекс, который позволяет анализировать данные, строить отчеты и создавать дэшборды. Существуют как мобильные, так и десктопные приложения, которые обеспечивают работу с BI-системами на различных устройствах и операционных системах.
Инструменты BI включают в себя различные технологии и инструменты, которые позволяют анализировать данные с разных точек зрения, от временных рядов до географических данных, от социальных сетей до машинного обучения. Эти инструменты могут быть организованы в виде отчетов, дашбордов, аналитических приложений и других форматов.
Дашборды, с другой стороны, являются интерактивными панелями, которые предоставляют пользователю доступ к ключевым показателям и метрикам. Они часто используются менеджерами и руководителями для мониторинга работы компании и принятия управленческих решений.
Конечные пользователи
Конечные пользователи — это важная часть архитектуры BI-системы. Они играют ключевую роль в определении и понимании тенденций в работе компании и могут быть менеджерами, аналитиками данных, маркетологами или другими сотрудниками компании. Они могут использовать информацию, собранную из разных источников, для создания отчетов, дашбордов и других инструментов, которые помогают в принятии решений.
Modus BI и Modus ETL
Как мы с вами видим, одним из компонентов современных BI-систем является модуль ETL (Extract, Transform, Load). ETL в BI-системах играет ключевую роль в решении задач по миграции данных, позволяя переносить данные между различными системами хранения, приложениями или платформами.
Реализация ETL в разных BI-системах может варьироваться в зависимости от их архитектуры, возможностей и специфических функций. Например, ведущие BI-системы реализуют следующий подход к внедрению ETL в свою продукты:
В Power BI ETL может быть реализован с использованием Power Query, инструмента для обработки и преобразования данных. Power Query позволяет пользователям подключаться к различным источникам данных, очищать, трансформировать и объединять данные, а также загружать их в модель данных Power BI.
В Tableau для реализации ETL используется Tableau Prep, инструмент для подготовки и обработки данных. Tableau Prep позволяет подключаться к различным источникам данных, очищать и преобразовывать данные с помощью визуального интерфейса и интуитивных функций, после чего данные можно загрузить в Tableau для анализа и визуализации.
В Qlik-платформе для ETL используется Qlik Data Integration (ранее известный как Qlik Data Catalyst). Этот инструмент предоставляет возможности для подключения к различным источникам данных, преобразования данных и загрузки их в Qlik-приложения для анализа.
Однако, зачастую для миграции данных из одной СУБД в другую, в связке с этими инструментами требуется использование сторонних продуктов, не входящих в экосистемы Power BI, Tableau и Qlik соответственно.
Например, Power Query не поддерживает нативную миграцию данных из PostgreSQL в ClickHouse. Для миграции данных из PostgreSQL в ClickHouse можно использовать сторонние инструменты, такие как Apache Kafka, Apache NiFi, Logstash и другие. Эти инструменты могут подключаться к PostgreSQL, получать данные и передавать их в ClickHouse.
Обратимся к платформе Modus BI (рис 2) и её архитектурному решению. Как мы видим, Modus BI состоит из двух основных компонентов:
Modus ETL — решение для управления ETL-процессами и подготовки данных для аналитики:
1) Хранилище данных — на СУБД MSSQL, PostgreSQL, Vertica и ClickHouse;
2) «Адаптер ETL-1C» — HTTP-сервис, встраиваемый в источник для получения данных из 1С-источников с помощью нативного языка платформы;
3) «Агент ETL» — опциональный модуль, разработанный на Golang для обеспечения многопоточного получения данных. Содержит библиотеку готовых драйверов/коннекторов для подключения к БД и иным источникам;
4) Модуль Data Mining — использует интеграцию с библиотеками Python для продвинутой аналитики, реализованы шаблоны (мастера) для настройки моделей регрессии, кластеризации и прогнозирования;
Modus Аналитический портал — веб-приложение интерактивного анализа данных:
1) Конструктор дашбордов — обеспечивает возможность настраивать отчеты (дашборды) без программирования;
2) Подсистема ввода данных — модуль Аналитического портала для настройки и заполнения форм ввода данных;
3) Подсистема отчетных форм — создание форм периодичной отчетности из шаблона xls с многоуровневой шапкой. Функционал позволяет настроить рассылку шаблонов для заполнения по расписанию, дальнейший сбор и консолидацию заполненных файлов xls. Поддерживается статусная модель при проверке качества переданных данных;
4) Подсистема регламентированной отчетности — создание шаблона выгружаемых данных в формате xls или многостраничной презентации в формате pptx;
5) Подсистема RLS — управление ролями пользователей, на уровне данных, строк,объектов;
6) Подсистема администрирования. Поддерживается интеграция с технологиями единого входа: Single‑Sign‑On, SSO по протоколам SAML, OpenID, OAuth2; Active Directory по протоколу LDAP.
Архитектура Modus BI включает в себя все основные компоненты современных BI-решений, включая OLAP и Data-Mining компоненты. Кроме того, собственное ETL-решение позволяет осуществлять автоматизированный сбор данных из различных источников с поддержкой шаблонов и мастер-инструментов настройки ETL-процессов.
Миграция данных из Postgres в Clickhouse при помощи Modus ETL
Изначально в качестве хранилища данных в структуре используемых нашей организацией BI-систем для построения дэшбордов и прочей аналитики использовалась классическая реляционная СУБД PostgreSQL. Однако, по мере роста объема информации в хранилище остро встала проблема быстродействия обновления данных в дэшбордах и скорости отклика на Select — запросы для чтения данных.
В частности, объем сравнительно небольшой по структуре таблицы (рис 3)
стал превышать 40+ ГБ (более 400 млн. строк),а ответ на простой запрос вида:
стал превышать 2 минуты (рис. 4).
В связи с этим, необходимо было качественным образом перестроить модель хранения данных в хранилище. В качестве альтернативной модели для хранения данных была выбрана колоночная СУБД Clickhouse, широко известная и распространенная на отечественном рынке.
Основное преимущество колоночных СУБД заключается в их способности быстро и эффективно выполнять агрегирующие запросы (такие как count, sum и т.д.) над большими объемами данных.
В отличие от реляционных баз данных, где данные хранятся построчно, колоночные СУБД хранят данные по столбцам. Это означает, что при выполнении запроса на агрегирование данных, например, на суммирование значений в столбце, колоночные СУБД могут работать намного быстрее, так как они обращаются только к нужным столбцам, а не к целым строкам. Кроме того, колоночные СУБД обычно имеют более эффективную схему сжатия данных, что позволяет сокращать объем занимаемой памяти на диске.
Modus ETL может подключаться к ClickHouse, используя специальный драйвер, который обеспечивает связь между Modus ETL и ClickHouse (рис 5).
В Modus ETL для этого нужно создать новое соединение с базой данных, выбрав тип соединения, соответствующий ClickHouse. Затем введите необходимые параметры, такие как адрес сервера, порт, имя базы данных, имя пользователя и пароль для доступа к ClickHouse. Делается это в меню “Источник данных” в главном меню интерфейса Modus ETL (рис 6). Следует отменить, что при дальнейшей настройки ETL-пакета, настроенное подключение к базе данных ClickHouse должно быть добавлено к “Источнику данных” в соответствующем меню, а также зарегистрировано в меню “Регистрация набор источников”. Эти меню служат для объединения нескольких источников данных в единый набор в случае, если нам надо было бы забирать данные не только из Postgres, но еще, например, из MySQL или иной реляционной СУБД.
Дальнейшим шагом для миграции данных является настройка сценария извлечения и обработки данных из PostgresSQL в меню “Установка правил выгрузки”. Для этого мы подключаемся к уже зарегистрированному источнику данных PostgresSQL, пишем к нему запрос на языке источника данных (SQL),чтобы выбрать данные для миграции (рис 7).
Данный SQL-запрос выбирает данные из таблицы public.ag_vote в формате для полей vote_id, create_date, user_id, answer_id, question_id, pool_id , и title . Затем он фильтрует эти данные по полю inserted_at, чтобы получить только записи, в которых значение поля inserted_at не старше 3 дней от текущего момента времени в формате timestamptz .
Конструкция используется для приведения значений полей к типу varchar . Это может быть полезно, если в ClickHouse нет соответствующего типа данных, или если типы данных в исходных таблицах несовместимы с типами данных в целевой таблице.
Общий смысл запроса заключается в выборе данных за последние 3 дня из таблицы с приведением типов полей к varchar .
После этого мы создаем пустую таблицу в ClickHouse с помощью кнопки “Создать таблицу в БД” и соединяем атрибуты из таблицы PostgreSQL в “Поле запроса” с атрибутами таблицы ClickHouse в “Поле выгрузки” (рис. 8).
В конечном итоге, данные из исходного набора источников (PostgreSQL) соединяются в единый набор данных в объекте “Состав выгрузки”. Данные извлекаются в соответствии со настроенным с помощью SQL сценарием выгрузки, обрабатываются (также с помощью SQL) в различных сценариях (группировка, объединение, маппинг данных) и выгружаются в СУБД — приемник (ClickHouse) (рис. 9).
Весь этот процесс описывает общий принцип создания ETL-пакета в Modus ETL. Этот пакет, как можно обратить внимание, может запускаться по расписанию, иметь нотификацию по итогам выполнения на электронную почту. Также, шаги выполнения ETL-пакета логируются внутри системы, что позволяет осуществлять контроль за его корректностью.
Если возвращаться к нашему практическому примеру (рис. 10), то наш ETL-пакет имеет следующую структуру, на которой видно, что данные их базы PostgreSQL мигрируют в таблицу ClickHouse. Самим процессом миграции управляет правило ag_vote_leftovers из pg в clickhouse, в котором с помощью SQL настроено извлечение нужных атрибутов из таблицы.
В ClickHouse таблица, которая в PostgreSQL весила, более 40+ ГБ стала весит немногим более 6+ ГБ (рис. 11).
Кроме того, тот же запрос на получение общего количества строк (более 400 млн.) вида:
стал выполняться существенно быстрее (рис. 12).
Таким образом, мы с вами рассмотрели Modus ETL — мощный инструмент для интеграции и обработки данных, который можно использовать для решения разнообразных задач, включая миграцию данных между различными СУБД. Он предоставляет множество гибких и настраиваемых возможностей для работы с данными, таких как преобразование, агрегация, очистка, фильтрация, объединение и другие. Благодаря своей архитектуре и встроенной поддержке популярных СУБД, таких как PostgreSQL и ClickHouse, Modus ETL является прекрасным инструментом для аналитиков данных и разработчиков, работающих с большими объемами данных.
В завершение статьи приглашаем всех новичков в сфере работы с большими данных на открытый урок «Визуализация данных и дашбординг для начинающих».
Знания, полученные в ходе урока, помогут облегчить ежедневную рутину по созданию аналитических отчетов. Также знакомство с основами визуализации данных и Business Intelligence — это возможность узнать больше о профессии BI-аналитика, и о том, какую роль наглядное представление информации играет в современном мире.
Мы поговорим о том, как можно раскрыть свой потенциал в визуальном анализе данных, какие программы для этого подойдут лучше всего, а главное — создадим интерактивный дашборд, используя бесплатные BI решения.
Записаться на урок можно на странице онлайн-курса «Аналитик данных»
