Обсудить
бизнес-задачи

Оптимизация 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 и интегрируется в БД в качестве вызываемой хранимой процедуры, в которую передаются следующие параметры:
  1. Локальный путь к файлу запуска драйвера;
  2. Тип СУБД назначения данных (Oracle/PostgreSQL/MS SQL Sever/Greenplum);
  3. Тип СУБД источника данных (Oracle/PostgreSQL/MS SQL Sever/Greenplum);
  4. Строка подключения к источнику данных;
  5. SQL-запрос для извлечения необходимого датасета;
  6. Строка подключения к целевой БД;
  7. Наименование целевой таблицы для вставки датасета;
  8. Количество строк для вставки в одном пакете интеграции.
Далее, драйвер создает объект подключения к базе данных сервера-источника и выполняет переданный SQL-запрос для получения датасета. Сохраняя данные в оперативной памяти, драйвер пакетно вставляет их в целевую таблицу сервера назначения, используя встроенные высокопроизводительные методы вставки данных в таблицу:
  • BCP для MS SQL Server;
  • SQL*Loader/External Tables для Oracle;
  • COPY для PostgreSQL;
  • gpload/gpfdist для Greenplum.

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

Использование разработанного драйвера позволило сократить время извлечения данных с источника до 10 раз: операции, которые ранее занимали час, теперь выполняются за 4-7 минут.
Однако, для максимальной эффективности драйвера требуется потребление увеличенного объема оперативной памяти. Драйвер сильно зависит от доступных ресурсов, поэтому рекомендуется избегать запуска «прожорливых» процессов параллельно с ним. Важно настроить алгоритм регламентных процедур, чтобы драйвер запускался в отдельном блоке с минимальным количеством параллельных обновлений.
Таким образом, разработка специализированного драйвера интеграции данных значительно улучшила производительность ETL-процессов в хранилище данных, обеспечив более быстрое и эффективное получение актуальной информации для бизнес-аналитиков.