10 принципов производительного ETL от Ральфа Кимбала.

Приветствую.

Перевод статьи "Increasing ETL Throughput" от Ezequiel Gallardo.

---

Ключевые концепции, которые
должен знать разработчик перед тем,
как создавать "ETL процесс"

  От ETL команды ожидают создания максимально производительного набора ETL процессов. Мы рекомендуем эти 10 правил, которые применимы как для самописных решений так и при использовании некоторых ETL инструментов, для поднятия производительности на максимальный уровень.
Ральф Кимбал.

10. Уменьшайте операции ввода - вывода.

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

Я настаиваю на том, что ввод-вывод должен быть сведен к абсолютному минимуму. Безусловно, вам может понадобиться сохранить данные источников по многим причинам. Одна из допустимых причин этого может заключаться в том, что вам необходимо минимизировать доступ к источнику, или ваш источник позволяет сделать только одно чтение данных.

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

Если вы вдруг обнаружите, что занимаетесь созданием стейджинговых таблиц и кучи процедур по записи и чтению из них - остановитесь! Отступите от текущей работы и посмотрите на весь проект со стороны. Убирая стейджинговые таблицы вы не только уменьшаете ввод-вывод - главное препятствие быстрой загрузки, - но и уменьшаете количество ETL процедур, которые необходимо поддерживать, а также упрощаете создание расписаний загрузок и огранизацию их пакетного запуска.

9. Избегайте процессов чтения из или записи в БД.

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

ETL процессы часто требуют записи данных на диск для обработки. Это может быть сортировка, агрегация, сохранение промежуточных расчетов или просто запись на диск для сохранности. У ETL разработчика есть выбор сделать это в таблице БД или в плоском файле. База данных требует значительно большего объема операций, чем простое сохранение данных в файле. А ETL средство может манипулировать данными из файла так же просто, как и данными из БД. Таким образом, лучшей практикой является использование плоских файлов для работы со стейджинговыми данными.

8. Фильтруйте как можно раньше.

Уменьшайте количество обрабатываемых строк как можно ближе к источнику. Избегайте преобразований тех данных, которые никогда не попадут в целевые таблицы хранилища данных.

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

7. Партиционирование и параллелизм.

Лучший способ увеличения производительности - множественность процессов преобразования данных.

• Читайте источники данных при помощи запросов с параллелизмом (parallel DML).
• Преобразовывайте данные с помощью конвеерной обработки и с использованием области стейджа.
• Партиционируйте целевые таблицы и загружайте их в параллельном режиме.

6. Обновляйте агрегаты инкрементально.

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

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

5. Брать только нужное (столбцы и строки).

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

Не имеет большого смысла загружать сотни тысяч или миллионы (а то и миллиарды) строк, если новых или измененных среди них - всего лишь несколько сотен. Новые или измененные строки с момента последней инкрементальной загрузки, конечно. Вы должны выбрать подходящий механизм, чтобы получать дельту из системы источника. Обычно существует несколько механизмов установки процессов отслеживания изменений данных в системах источниках в зависимости от типа их БД.

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

4. Утилиты выгрузки-загрузки/логирование.

• Используйте родные утилиты массовой выгрузки данных для БД.
• Используйте родные утилиты массовой загрузки данных для БД.
• Выключите логирование.
• Set DIRECT=TRUE.

3. Удалите ограничения целостности и индексы БД.

Внешние ключи (foreign keys) слишком тяжелая функциональность для БД, они должны быть удалены. В случае же, если внешние ключи все же нужны, запретите их перед началом ETL, и разрешите обратно уже после окончания загрузки, в рамках пост-обработки. Оставьте только те индексы, которые помогают фильтровать данные при обновлениях и для ускорения выборок по условию определенному в where. Удалите все остальные индексы (индексы для ускорения вставки и т.п.). Выполняйте пересоздание индексов во время пост-обработки.

2. Снижайте сетевой трафик.

Храните рабочие ETL файлы на локальных дисках. Установите ETL движок в тоже место, где находится ХД.

1. Дайте ETL инструменту делать свою работу.

Минимизируйте использование любой функциональности из СУБД. Не пользуйтесь механизмом сохраненных процедур или функций, генерации ключей средствами БД, триггеров или определения дубликатов.

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

---

Вот такие 10 рекомендаций применяются для стандартных ETL средств, типа Informatica, но практически все из них (кроме совета номер 9), при определенной адаптации, вполне подходят и для разработки в Oracle Data Integrator. Я, в данный момент, как раз занимаюсь реализацией пункта номер 5.

Стабильной загрузки!