CDC и инкрементальные пакеты SSIS: сокращаем окно ночной загрузки DWH

Есть такой тип задач, которые откладываешь до последнего, потому что «в принципе работает». Ночная загрузка витрин у одного из наших клиентов работала - но занимала почти четыре часа, и за эти четыре часа источники нельзя было трогать, отчёты тормозили, а ночное окно обслуживания всё теснее. Когда к нему добавились задачи резервного копирования и регламентного обслуживания индексов - совместить всё в одну ночь перестало получаться.

Пришло время разобраться с этим как следует.

Что было до

Источниковая операционная база - MS SQL Server 2012, хранилище данных на нём же, отдельная база. SSIS-пакеты написаны несколько лет назад, работали по классической схеме: полная выгрузка каждой таблицы-источника, усечение staging-таблицы, загрузка, затем логика обновления dimension и fact таблиц в хранилище.

Это работало, пока объёмы были небольшими. Когда в операционной базе накопились сотни миллионов строк в нескольких ключевых таблицах - полная выгрузка стала занимать само по себе значительную часть ночи. Добавь сюда перестройку индексов в конце - и вот тебе четыре часа.

Проблема очевидная: мы каждую ночь перекачиваем весь объём данных, хотя за сутки меняется процент от него. Нужна инкрементальная загрузка.

Два пути к инкременту

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

Первый - по timestamp-колонке. Простейший подход: в каждой таблице есть поле updated_at, берёшь строки где updated_at > last_load_time. Работает, пока разработчики не забывают обновлять это поле, пока нет физических удалений строк (которые ты не увидишь вообще), пока часовые пояса не начинают вытворять что-то неожиданное. У клиента часть таблиц была без updated_at, часть - с, но не везде надёжно заполняемой.

Второй - Change Data Capture. Фича SQL Server, появившаяся в 2008 версии. Читает транзакционный лог и складывает изменения (insert, update, delete) в системные CDC-таблицы с указанием типа операции и значений до/после. Подходит для всех операций включая удаления, не требует изменений в прикладной схеме, работает на уровне СУБД.

Мы выбрали CDC. Для таблиц без updated_at это был единственный вменяемый вариант, для остальных - единственный, который ловит удаления.

Включение CDC и что из этого следует

Включить CDC на базе и таблицах - несколько строк T-SQL, ничего сложного:

-- На базе данных
EXEC sys.sp_cdc_enable_db;

-- На каждой таблице
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL,
    @supports_net_changes = 1;

После этого SQL Server начинает писать изменения в таблицы вида cdc.dbo_Orders_CT. Параметр @supports_net_changes = 1 включает «чистые изменения» - когда строка менялась несколько раз за период, CDC покажет только итоговое состояние, не каждую промежуточную версию. Для нашей задачи (загрузить актуальное состояние в хранилище) это то что нужно.

Важный момент, который стоит проговорить сразу: CDC читает транзакционный лог, и если лог не успевает за объёмом транзакций или ротируется слишком быстро - CDC может пропустить изменения. Для клиента мы пересмотрели настройки retention лога и настроили агентский джоб очистки CDC с нужным горизонтом.

Переписываем пакеты SSIS

Это было большей частью работы. SSIS умеет работать с CDC из коробки - в нём есть специальные компоненты: CDC Source, CDC Splitter, CDC Control Task. В теории это должно было сильно упростить написание.

На практике CDC Source оказался привередливее, чем хотелось бы. Нам пришлось явно управлять LSN-границами (Log Sequence Number - метка позиции в логе): в начале пакета читаем «с какого LSN последний раз загрузились», в конце фиксируем «до какого LSN загрузили сейчас». CDC Control Task это умеет, но требует переменных и конкретного порядка вызовов - если пакет падает на середине, нужно убедиться что LSN не обновился, иначе при следующем запуске пропустишь кусок данных.

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

CDC Splitter делит поток изменений на три потока: вставки, обновления, удаления. Дальше каждый поток идёт своим путём в destination.

Удаления - отдельная история

В хранилище данных «удаление» строки из источника - не всегда физическое удаление в fact-таблице. Зависит от логики. Для части таблиц клиента мы реализовали soft delete: в dimension ставим флаг is_deleted = 1 и дату удаления, в fact-таблицу ничего не трогаем - исторические факты остаются. Для другой части (справочники, которые реально перестают существовать) - физическое удаление.

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

Результат

После переключения на инкрементальные пакеты ночная загрузка на следующее утро показала около 35 минут. Это на объёме суточных изменений, который составляет несколько процентов от общего. Четыре часа до этого - это и была стоимость «гонять всё заново каждый раз».

Окно обслуживания освободилось, бэкапы и обслуживание индексов перестали конфликтовать с загрузкой.

Что мы придерживаем как задачу на следующий шаг - мониторинг задержки CDC. Сейчас мы видим успешность пакетов и их продолжительность, но нет метрики «насколько хранилище отстаёт от источника прямо сейчас» в режиме близком к реальному времени. Это отдельная работа, которую стоит сделать - особенно если клиент когда-нибудь захочет сократить цикл загрузки с ночного до более частого.

Проект веду в рамках DWH и аналитики. Если у вас похожая история с многочасовыми окнами загрузки - обсудим.