В последние годы напряженная экономическая и политическая ситуация в связи с усилением международных санкций привела к уходу многих зарубежных компаний с рынка бизнес-аналитических систем и ограничению доступа к их продуктам и услугам. В частности, рынок бизнес-аналитики столкнулся с серьезными вызовами, так как многие российские предприятия и организации стали ограничены в использовании западных BI-систем. Однако эти трудности послужили стимулом для роста интереса к отечественным разработкам в сфере бизнес-аналитики.
Использование отечественных BI-систем может быть особенно полезно для компаний, работающих в отраслях, связанных с государственными заказами, обороной и безопасностью. Они также могут помочь компаниям, которые сталкиваются с ограничениями в использовании западных BI-систем из-за политических или экономических санкций.
В этой статье мы обратимся к практическому опыту использования отечественной бизнес-аналитической системы Modus BI для решения нетривиальной задачи по миграции данных из СУБД PostgresSQL в СУБД Clickhouse.
Изучение отечественных BI-систем предоставляет ценные практические знания о решениях, доступных на российском рынке, и их способности решать рутинные задачи аналитиков данных (таких, как миграция данных). Однако, для того чтобы полноценно оценить возможности и применимость этих систем в контексте глобального рынка бизнес-аналитики, важно рассмотреть и типовую структуру BI-систем в целом. Это позволит определить общие принципы и архитектурные решения, применяемые как в российских, так и в международных разработках.
Рисунок 1. Типовая архитектура BI решений
При миграции данных из разных источников в единое хранилище данных для анализа важно убедиться, что данные совместимы и могут быть объединены в единый набор данных для анализа. Это может включать проверку совместимости форматов данных, структуры данных, типов данных и т.д. Если данные не совместимы, то могут возникнуть проблемы при их объединении, что может привести к ошибкам в результате анализа. Поэтому важно уделить достаточное внимание этому этапу процесса миграции данных, чтобы обеспечить точность и достоверность результатов анализа.
ETL-процессы важны для BI-систем и включают в себя процессы извлечения, преобразования и загрузки данных в хранилище данных BI-системы.
Извлечение: первый шаг — извлечение данных из разных источников, таких как базы данных, файлы, приложения и системы управления контентом.
Преобразование: далее данные могут потребовать преобразования, например, очистки, преобразования форматов и объединения данных из разных источников.
Загрузка: завершающий шаг — загрузка данных в хранилище данных BI- системы, которое может быть организовано в виде структурированных таблиц или неструктурированных файлов.
Модели данных определяют, как данные будут организованы, храниться и использоваться, обеспечивая эффективное хранение и быстрый доступ к данным. Одним из примеров модели данных может быть иерархическая модель, которая организует данные в виде дерева или графа. Существуют также реляционные модели данных, которые используются для хранения данных в таблицах и связывания их друг с другом по ключам. Важно помнить, что правильный выбор модели данных может значительно повысить производительность и эффективность работы информационной системы.
OLAP-кубы создаются при помощи специальных инструментов, которые позволяют создавать кубы из разных источников данных, проводить агрегацию и преобразование данных, а также строить многомерные модели для анализа.
Важные части OLAP-кубов — это измерения и факты. Измерения — это атрибуты, по которым можно фильтровать и агрегировать данные. Например, в OLAP-кубе для анализа продаж могут быть измерения: год, квартал, продукт, регион, клиент и т.д. Факты — это числовые данные, которые агрегируются по измерениям. Например, сумма продаж, количество проданных единиц, средняя цена и т.д.
OLAP-кубы могут иметь множество измерений и фактов, а также сложные связи между ними. Правильно построенный OLAP-куб позволяет эффективно анализировать большие объемы данных и выявлять зависимости, которые не могут быть видны на первый взгляд. Кроме того, OLAP-кубы могут быть использованы для построения дашбордов. Например, можно построить дашборд для отслеживания продаж продукта в разных регионах или для отображения показателей производительности компании. Использование OLAP-кубов для построения дашбордов позволяет быстро и удобно анализировать большие объемы данных и получать ценную информацию для принятия важных решений.
В системах бизнес-аналитики Data Mining используется для анализа данных и выявления новых возможностей для бизнеса. Например, с помощью Data Mining можно выявить скрытые закономерности между продуктами или услугами, которые могут быть использованы для улучшения маркетинговых стратегий. Также Data Mining может быть использован для анализа поведения клиентов, выявления паттернов их покупок и прогнозирования будущих продаж.
Data Mining использует различные методы и алгоритмы машинного обучения, такие как кластерный анализ, деревья решений, нейронные сети и многое другое.
Инструменты BI включают в себя различные технологии и инструменты, которые позволяют анализировать данные с разных точек зрения, от временных рядов до географических данных, от социальных сетей до машинного обучения. Эти инструменты могут быть организованы в виде отчетов, дашбордов, аналитических приложений и других форматов.
Дашборды, с другой стороны, являются интерактивными панелями, которые предоставляют пользователю доступ к ключевым показателям и метрикам. Они часто используются менеджерами и руководителями для мониторинга работы компании и принятия управленческих решений.
Реализация ETL в разных BI-системах может варьироваться в зависимости от их архитектуры, возможностей и специфических функций. Например, ведущие BI-системы реализуют следующий подход к внедрению ETL в свою продукты:
Например, Power Query не поддерживает нативную миграцию данных из PostgreSQL в ClickHouse. Для миграции данных из PostgreSQL в ClickHouse можно использовать сторонние инструменты, такие как Apache Kafka, Apache NiFi, Logstash и другие. Эти инструменты могут подключаться к PostgreSQL, получать данные и передавать их в ClickHouse.
Обратимся к платформе Modus BI (рис 2) и её архитектурному решению. Как мы видим, Modus BI состоит из двух основных компонентов:
Рисунок 2. Архитектура платформы «Modus BI»
В частности, объем сравнительно небольшой по структуре таблицы (рис 3)
Рисунок 3. Структура таблицы в PostgreSQL
стал превышать 40+ ГБ (более 400 млн. строк),а ответ на простой запрос вида:
стал превышать 2 минуты (рис. 4).
Рисунок 4. Время выполнения запроса в PostgreSQL
В связи с этим, необходимо было качественным образом перестроить модель хранения данных в хранилище. В качестве альтернативной модели для хранения данных была выбрана колоночная СУБД Clickhouse, широко известная и распространенная на отечественном рынке.
Основное преимущество колоночных СУБД заключается в их способности быстро и эффективно выполнять агрегирующие запросы (такие как count, sum и т.д.) над большими объемами данных.
В отличие от реляционных баз данных, где данные хранятся построчно, колоночные СУБД хранят данные по столбцам. Это означает, что при выполнении запроса на агрегирование данных, например, на суммирование значений в столбце, колоночные СУБД могут работать намного быстрее, так как они обращаются только к нужным столбцам, а не к целым строкам. Кроме того, колоночные СУБД обычно имеют более эффективную схему сжатия данных, что позволяет сокращать объем занимаемой памяти на диске.
Modus ETL может подключаться к ClickHouse, используя специальный драйвер, который обеспечивает связь между Modus ETL и ClickHouse (рис 5).
Рисунок 5. Подключение к ClickHouse
В Modus ETL для этого нужно создать новое соединение с базой данных, выбрав тип соединения, соответствующий ClickHouse. Затем введите необходимые параметры, такие как адрес сервера, порт, имя базы данных, имя пользователя и пароль для доступа к ClickHouse. Делается это в меню “Источник данных” в главном меню интерфейса Modus ETL (рис 6). Следует отменить, что при дальнейшей настройки ETL-пакета, настроенное подключение к базе данных ClickHouse должно быть добавлено к “Источнику данных” в соответствующем меню, а также зарегистрировано в меню “Регистрация набор источников”. Эти меню служат для объединения нескольких источников данных в единый набор в случае, если нам надо было бы забирать данные не только из Postgres, но еще, например, из MySQL или иной реляционной СУБД.
Рисунок 6. Интерфейс главного меню Modus ETL
Дальнейшим шагом для миграции данных является настройка сценария извлечения и обработки данных из 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 .
Рисунок 7. Извлечение данных из PostgreSQL для последующей миграции
После этого мы создаем пустую таблицу в ClickHouse с помощью кнопки “Создать таблицу в БД” и соединяем атрибуты из таблицы PostgreSQL в “Поле запроса” с атрибутами таблицы ClickHouse в “Поле выгрузки” (рис. 8).
Рисунок 8. Соединение атрибутов PostgreSQL и ClickHouse
В конечном итоге, данные из исходного набора источников (PostgreSQL) соединяются в единый набор данных в объекте “Состав выгрузки”. Данные извлекаются в соответствии со настроенным с помощью SQL сценарием выгрузки, обрабатываются (также с помощью SQL) в различных сценариях (группировка, объединение, маппинг данных) и выгружаются в СУБД — приемник (ClickHouse) (рис. 9).
Весь этот процесс описывает общий принцип создания ETL-пакета в Modus ETL. Этот пакет, как можно обратить внимание, может запускаться по расписанию, иметь нотификацию по итогам выполнения на электронную почту. Также, шаги выполнения ETL-пакета логируются внутри системы, что позволяет осуществлять контроль за его корректностью.
Рисунок 9. Общий вид создания ETL-пакета
Если возвращаться к нашему практическому примеру (рис. 10), то наш ETL-пакет имеет следующую структуру, на которой видно, что данные их базы PostgreSQL мигрируют в таблицу ClickHouse. Самим процессом миграции управляет правило ag_vote_leftovers из pg в clickhouse, в котором с помощью SQL настроено извлечение нужных атрибутов из таблицы.
Рисунок 10. Диаграмма миграции из PostgreSQL в Clickhouse
В ClickHouse таблица, которая в PostgreSQL весила, более 40+ ГБ стала весит немногим более 6+ ГБ (рис. 11).
Рисунок 11. Объем таблицы в ClickHouse
Кроме того, тот же запрос на получение общего количества строк (более 400 млн.) вида:
стал выполняться существенно быстрее (рис. 12).
Рисунок 12. Скорость выполнения запроса в ClickHouse
Таким образом, мы с вами рассмотрели Modus ETL — мощный инструмент для интеграции и обработки данных, который можно использовать для решения разнообразных задач, включая миграцию данных между различными СУБД. Он предоставляет множество гибких и настраиваемых возможностей для работы с данными, таких как преобразование, агрегация, очистка, фильтрация, объединение и другие. Благодаря своей архитектуре и встроенной поддержке популярных СУБД, таких как PostgreSQL и ClickHouse, Modus ETL является прекрасным инструментом для аналитиков данных и разработчиков, работающих с большими объемами данных.
habr.com
Использование отечественных BI-систем может быть особенно полезно для компаний, работающих в отраслях, связанных с государственными заказами, обороной и безопасностью. Они также могут помочь компаниям, которые сталкиваются с ограничениями в использовании западных BI-систем из-за политических или экономических санкций.
В этой статье мы обратимся к практическому опыту использования отечественной бизнес-аналитической системы Modus BI для решения нетривиальной задачи по миграции данных из СУБД PostgresSQL в СУБД Clickhouse.
Изучение отечественных BI-систем предоставляет ценные практические знания о решениях, доступных на российском рынке, и их способности решать рутинные задачи аналитиков данных (таких, как миграция данных). Однако, для того чтобы полноценно оценить возможности и применимость этих систем в контексте глобального рынка бизнес-аналитики, важно рассмотреть и типовую структуру BI-систем в целом. Это позволит определить общие принципы и архитектурные решения, применяемые как в российских, так и в международных разработках.
Структура современных BI-систем
BI-системы, то есть системы бизнес-аналитики, часто включают в себя несколько ключевых компонентов (рис. 1), таких как:- Системы сбора данных, которые собирают данные из различных источников и сохраняют их в централизованной базе данных.
- Системы хранения данных, которые хранят данные в удобном формате и обеспечивают быстрый доступ к ним.
- Системы аналитики данных, которые позволяют анализировать данные и извлекать из них ценную информацию.
- Инструменты 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 Query не поддерживает нативную миграцию данных из PostgreSQL в ClickHouse. Для миграции данных из PostgreSQL в ClickHouse можно использовать сторонние инструменты, такие как Apache Kafka, Apache NiFi, Logstash и другие. Эти инструменты могут подключаться к PostgreSQL, получать данные и передавать их в ClickHouse.
Обратимся к платформе Modus BI (рис 2) и её архитектурному решению. Как мы видим, 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.
Миграция данных из Postgres в Clickhouse при помощи Modus ETL
Изначально в качестве хранилища данных в структуре используемых нашей организацией BI-систем для построения дэшбордов и прочей аналитики использовалась классическая реляционная СУБД PostgreSQL. Однако, по мере роста объема информации в хранилище остро встала проблема быстродействия обновления данных в дэшбордах и скорости отклика на Select — запросы для чтения данных.В частности, объем сравнительно небольшой по структуре таблицы (рис 3)
Рисунок 3. Структура таблицы в PostgreSQL
стал превышать 40+ ГБ (более 400 млн. строк),а ответ на простой запрос вида:
стал превышать 2 минуты (рис. 4).
Рисунок 4. Время выполнения запроса в PostgreSQL
В связи с этим, необходимо было качественным образом перестроить модель хранения данных в хранилище. В качестве альтернативной модели для хранения данных была выбрана колоночная СУБД Clickhouse, широко известная и распространенная на отечественном рынке.
Основное преимущество колоночных СУБД заключается в их способности быстро и эффективно выполнять агрегирующие запросы (такие как count, sum и т.д.) над большими объемами данных.
В отличие от реляционных баз данных, где данные хранятся построчно, колоночные СУБД хранят данные по столбцам. Это означает, что при выполнении запроса на агрегирование данных, например, на суммирование значений в столбце, колоночные СУБД могут работать намного быстрее, так как они обращаются только к нужным столбцам, а не к целым строкам. Кроме того, колоночные СУБД обычно имеют более эффективную схему сжатия данных, что позволяет сокращать объем занимаемой памяти на диске.
Modus ETL может подключаться к ClickHouse, используя специальный драйвер, который обеспечивает связь между Modus ETL и ClickHouse (рис 5).
Рисунок 5. Подключение к ClickHouse
В Modus ETL для этого нужно создать новое соединение с базой данных, выбрав тип соединения, соответствующий ClickHouse. Затем введите необходимые параметры, такие как адрес сервера, порт, имя базы данных, имя пользователя и пароль для доступа к ClickHouse. Делается это в меню “Источник данных” в главном меню интерфейса Modus ETL (рис 6). Следует отменить, что при дальнейшей настройки ETL-пакета, настроенное подключение к базе данных ClickHouse должно быть добавлено к “Источнику данных” в соответствующем меню, а также зарегистрировано в меню “Регистрация набор источников”. Эти меню служат для объединения нескольких источников данных в единый набор в случае, если нам надо было бы забирать данные не только из Postgres, но еще, например, из MySQL или иной реляционной СУБД.
Рисунок 6. Интерфейс главного меню Modus ETL
Дальнейшим шагом для миграции данных является настройка сценария извлечения и обработки данных из 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 .
Рисунок 7. Извлечение данных из PostgreSQL для последующей миграции
После этого мы создаем пустую таблицу в ClickHouse с помощью кнопки “Создать таблицу в БД” и соединяем атрибуты из таблицы PostgreSQL в “Поле запроса” с атрибутами таблицы ClickHouse в “Поле выгрузки” (рис. 8).
Рисунок 8. Соединение атрибутов PostgreSQL и ClickHouse
В конечном итоге, данные из исходного набора источников (PostgreSQL) соединяются в единый набор данных в объекте “Состав выгрузки”. Данные извлекаются в соответствии со настроенным с помощью SQL сценарием выгрузки, обрабатываются (также с помощью SQL) в различных сценариях (группировка, объединение, маппинг данных) и выгружаются в СУБД — приемник (ClickHouse) (рис. 9).
Весь этот процесс описывает общий принцип создания ETL-пакета в Modus ETL. Этот пакет, как можно обратить внимание, может запускаться по расписанию, иметь нотификацию по итогам выполнения на электронную почту. Также, шаги выполнения ETL-пакета логируются внутри системы, что позволяет осуществлять контроль за его корректностью.
Рисунок 9. Общий вид создания ETL-пакета
Если возвращаться к нашему практическому примеру (рис. 10), то наш ETL-пакет имеет следующую структуру, на которой видно, что данные их базы PostgreSQL мигрируют в таблицу ClickHouse. Самим процессом миграции управляет правило ag_vote_leftovers из pg в clickhouse, в котором с помощью SQL настроено извлечение нужных атрибутов из таблицы.
Рисунок 10. Диаграмма миграции из PostgreSQL в Clickhouse
В ClickHouse таблица, которая в PostgreSQL весила, более 40+ ГБ стала весит немногим более 6+ ГБ (рис. 11).
Рисунок 11. Объем таблицы в ClickHouse
Кроме того, тот же запрос на получение общего количества строк (более 400 млн.) вида:
стал выполняться существенно быстрее (рис. 12).
Рисунок 12. Скорость выполнения запроса в ClickHouse
Таким образом, мы с вами рассмотрели Modus ETL — мощный инструмент для интеграции и обработки данных, который можно использовать для решения разнообразных задач, включая миграцию данных между различными СУБД. Он предоставляет множество гибких и настраиваемых возможностей для работы с данными, таких как преобразование, агрегация, очистка, фильтрация, объединение и другие. Благодаря своей архитектуре и встроенной поддержке популярных СУБД, таких как PostgreSQL и ClickHouse, Modus ETL является прекрасным инструментом для аналитиков данных и разработчиков, работающих с большими объемами данных.
Использование отечественных BI-систем для миграции данных из Postgres в Clickhouse
Автор статьи: Роман Козлов Руководитель курса BI-аналитика Ссылка на автора: @Krv87 В последние годы напряженная экономическая и политическая ситуация в связи с усилением международных санкций привела...
habr.com