Оптимизация ETL-процессов DWH в десятки раз

блог о bi, №1 в рунете

При разработке хранилища данных для бизнес-аналитики (DWH) ключевым аспектом является эффективное извлечение данных из различных источников, таких как другие базы данных, CSV, HTML и JSON-файлы. В условиях больших объемов данных, поступающих ежедневно, критически важно оптимизировать время выполнения ETL-процессов (практика показывает, что бизнес-пользователям необходимо иметь актуальные данные в начале рабочего дня).

Проблематика извлечения данных

В процессе организации DWH на платформах MS SQL Server/Postgresql/Greenplum возникали вопросы, связанные с увеличением времени, затрачиваемого на извлечение данных из внешних СУБД, с которыми DWH взаимодействуют путем создания соединений «сервер-сервер»: если подобные соединения используются для получения справочных, «легких» данных (таблицы, наборы данных до 5 млн строк), то проблем с производительностью нет, однако при выгрузке данных о фактах деятельности бизнеса (более 50 миллионов строк) производительность интеграции данных ощутимо снижается.

Случаи из практики

DWH MS SQL Server – источник данных PostgreSQL

DWH на платформе MS SQL Server в рамках политики регламентного обновления ежедневно (в период с 00:00 по 03:00) подключается к серверу-источнику на PostgreSQL, при этом получая объем данных по фактам чеков более 80 миллионов строк за одну сессию, что занимает 1,5-2 часа от всего времени интеграции.
DWH Greenplum – источник данных Oracle

В случае DWH на платформе Greenplum – происходит ежедневное получение более 150 миллионов строк данных товародвижений с сервера источника Oracle, что занимает около 3 часов.

Разработка драйвера интеграции данных

Понимая, что стандартных средств MS SQL Server/PostgreSQL (Airflow, Spark, Nifi)/Greenplum (Airflow, Spark, Nifi) недостаточно для эффективной интеграции данных, было решено разработать универсальное решение.

На языке C# был создан драйвер, обеспечивающий интеграцию данных из источников (Oracle/PostgreSQL/MS SQL Sever/Greenplum) в целевые таблицы БД назначений. В основу драйвера были вложены 4 ключевые концепции для оптимизации интеграции:

Многопоточность;
Асинхронность;
Im-memory обработка;
Пакетная вставка.

Механизм работы драйвера

Драйвер локально разворачивается на сервере, на котором находится DWH и интегрируется в БД в качестве вызываемой хранимой процедуры, в которую передаются следующие параметры:

Локальный путь к файлу запуска драйвера;
Тип СУБД назначения данных (Oracle/PostgreSQL/MS SQL Sever/Greenplum);
Тип СУБД источника данных (Oracle/PostgreSQL/MS SQL Sever/Greenplum);
Строка подключения к источнику данных;
SQL-запрос для извлечения необходимого датасета;
Строка подключения к целевой БД;
Наименование целевой таблицы для вставки датасета;
Количество строк для вставки в одном пакете интеграции.

Далее, драйвер создает объект подключения к базе данных сервера-источника и выполняет переданный SQL-запрос для получения датасета. Сохраняя данные в оперативной памяти, драйвер пакетно вставляет их в целевую таблицу сервера назначения, используя встроенные высокопроизводительные методы вставки данных в таблицу:

BCP для MS SQL Server;
SQL*Loader/External Tables для Oracle;
COPY для PostgreSQL;
gpload/gpfdist для Greenplum.

Результаты и выводы

Использование разработанного драйвера позволило сократить время извлечения данных с источника до 10 раз: операции, которые ранее занимали час, теперь выполняются за 4-7 минут.

Однако, для максимальной эффективности драйвера требуется потребление увеличенного объема оперативной памяти. Драйвер сильно зависит от доступных ресурсов, поэтому рекомендуется избегать запуска «прожорливых» процессов параллельно с ним. Важно настроить алгоритм регламентных процедур, чтобы драйвер запускался в отдельном блоке с минимальным количеством параллельных обновлений.

Таким образом, разработка специализированного драйвера интеграции данных значительно улучшила производительность ETL-процессов в хранилище данных, обеспечив более быстрое и эффективное получение актуальной информации для бизнес-аналитиков.

ВЕРНУТЬСЯ К БЛОГУ