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

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

Современные DWH агрегируют десятки и сотни миллионов строк, а количество интеграций постоянно увеличивается. ETL конкурирует за ресурсы с другими задачами, упирается в сетевую пропускную способность и I/O, и поэтому становится основным ограничителем скорости обновления отчетности.

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

Заметный эффект дает уменьшение объема передаваемых данных. Инкрементальные загрузки и CDC позволяют забирать только изменения, а грамотная фильтрация и выбор нужных колонок сокращают сетевой трафик и нагрузку на источники.

Параллелизация ускоряет загрузку без потери контроля над качеством данных. Разделение по диапазонам, партициям или бизнес-ключам и запуск нескольких потоков лучше использует ресурсы DWH.

Отдельно стоит выделить высокопроизводительные способы вставки. Пакетные операции и bulk-методы значительно эффективнее построчной загрузки и лучше используют возможности СУБД.

Также имеет смысл переносить часть преобразований ближе к источнику, а сам ETL-контур изолировать по ресурсам, чтобы он не конкурировал с другими регламентными задачами.

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

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

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

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

Оптимизация ETL дает бизнесу предсказуемость: регламентные окна становятся устойчивыми, а аналитические отчеты обновляются вовремя. Это снижает операционные риски, когда задержка загрузки приводит к устаревшим метрикам и ошибочным решениям.

Технически оптимизация уменьшает нагрузку на источники и DWH, освобождает ресурсы для параллельных процессов и повышает стабильность процедур. В результате система легче масштабируется при росте объемов и числа интеграций.

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

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

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