ETL на SSIS 2008: как мы сократили ночную загрузку с 6 часов до 90 минут

Клиент - средний ритейл, несколько десятков магазинов по региону. Хранилище данных у них появилось года четыре назад, строилось в спешке на DTS под SQL Server 2000 и с тех пор в основном не трогалось - работает и ладно. Ночная загрузка стартовала в 23:00 и к утру должна была закончить. В теории. На практике она всё чаще заезжала за пять утра, а однажды - за семь, и операционисты открыли рабочий день с вчерашними остатками.

Позвонили нам.

Что нашли внутри

Первый час аудита ничего неожиданного не принёс: DTS-пакеты, написанные в 2006-м, последовательная цепочка - источник за источником, таблица за таблицей. Никакого параллелизма. Один поток данных, который тащил записи из восьми магазинных баз по очереди, потом агрегировал, потом грузил в хранилище. Типичная история.

Хуже другое: за прошедшие годы количество магазинов выросло примерно втрое, номенклатура разрослась, появились новые отчётные измерения. А архитектура загрузки осталась той же. Линейный ETL плохо масштабируется - это не открытие, это просто математика.

Вдобавок обнаружили несколько мест, где пакет делал TRUNCATE + полную перезаливку там, где вполне можно было обойтись инкрементальной загрузкой по дате изменения записи. Каждую ночь заново гнался весь справочник товаров - несколько сотен тысяч строк - хотя менялось за день от силы несколько десятков позиций.

Почему именно SSIS 2008, а не 2005

У клиента на серверах стоял SQL Server 2008, так что выбор SSIS-версии был предопределён. Но нас это не расстроило: восьмая версия по сравнению с 2005-й заметно прибавила в части управления буферами и параллельными потоками. Конкретно нас интересовали две вещи.

Первое - Data Flow буферизация. SSIS 2008 позволяет тонко настраивать DefaultBufferSize и DefaultBufferMaxRows на уровне каждого Data Flow Task. В 2005-й это тоже было, но в 2008-й движок стал аккуратнее распределять память между потоками и реже устраивать swap на диск при объёмных трансформациях. На нашем объёме данных это ощутимо.

Второе - Control Flow с реальным параллелизмом. Несколько Data Flow Task можно запускать одновременно, и движок сам разруливает зависимости через Precedence Constraints. Мы выстроили граф: источники, которые не зависят друг от друга, идут параллельно; те, которым нужны промежуточные результаты - ждут в очереди. Получился не плоский список задач, а нормальный DAG.

Что переделали

Работа разбилась на три части.

Параллелизация по источникам. Восемь магазинных баз теперь загружаются одновременно, каждая в своём потоке. Ограничение - пропускная способность сети и дисковая подсистема хранилища. Мы запустили нагрузочный тест и выяснили, что четыре параллельных потока - оптимум: при восьми параллельных дисковый ввод-вывод начинал проседать и суммарное время не улучшалось.

Инкрементальная загрузка там, где это возможно. Для справочников и измерений перешли на сравнение по полю LastModified - загружаем только изменённые записи. Для таблицы фактов (транзакции по чекам) оставили подход скользящего окна: берём данные за последние N дней, потому что кассовое ПО клиента иногда дописывает корректировки задним числом.

Обработка ошибок и логирование. Старые DTS-пакеты при ошибке молча падали или продолжали работу с неполными данными. В SSIS настроили Error Output для критичных преобразований: строки с ошибками идут в отдельную таблицу-карантин, пакет пишет в Event Log, DBA получает письмо. Мелочь, но теперь утренние разборки «почему цифры не сошлись» занимают минуты, а не часы.

Результат

Первый прогон новой цепочки занял около двух часов - часть времени ушла на то, что мы поначалу перестраховались и поставили слишком маленький размер буфера. После настройки буферов и тестирования оптимального числа параллельных потоков вышли на стабильные 85-95 минут на полный цикл. При том что объём данных с момента написания старых DTS-пакетов вырос втрое.

Клиент доволен - загрузка теперь заканчивается к часу ночи с запасом.

Что здесь работает, а что нет

Параллельные потоки в SSIS - не волшебная кнопка. Если узкое место - один перегруженный источник или одна база-назначение с медленными дисками, параллелизм не поможет или даже ухудшит ситуацию за счёт конкуренции за ресурс. Диагностику надо начинать с профилировщика, а не с архитектурных решений.

Ещё один момент: SSIS-пакеты при всех достоинствах неудобно версионировать. XML-формат .dtsx нечеловеческий, диффы между версиями в git читать больно. Мы завели соглашение - каждое существенное изменение описывать в текстовом changelog рядом с пакетом. Костыль, но без него потом не вспомнишь, зачем была добавлена вот эта ветка в Control Flow.

Подробнее о том, как мы вообще строим хранилища данных и аналитику, можно почитать на соответствующей странице - там в общих чертах описан наш подход.