Порядок разработки ETL-процессов (часть 4)

Автор: Евгений Островский

Преобразование данных

Не секрет, что физическая модель ХД зачастую не совпадает со структурой оперативных источников данных. Это вызвано повышенными требованиями к качеству структурирования информации и особенностями моделирования ХД. Поэтому возникает потребность в преобразовании данных, которые поступают из оперативных источников в структуры, соответствующие таблицам ХД.

Преобразование данных сводится к нескольким элементарным операциям:

• Вычисление;
• Агрегация;
• Получение статистических данных (статистика);
• Согласование ключей;
• Генерация суррогатных ключей.

Вычисление – это операция, результат которой функционально зависит от её параметров, и сама операция может быть реализована на уровне скалярных функций.

Агрегация – это операция, которая реализуется как функциональная зависимость на уровне агрегатных функций (функции ряда).

Статистика – операция получения данных на основе количественной и/или исторической информации; результат статистической функции напрямую не зависит от конкретных значений полей в таблице.

Генерация суррогатных ключей – операция сопоставления естественному ключу (чаще всего – составному) уникального суррогатного ключа - идентификатора набора данных ХД. Применяется для обновляемых RDS. Решение о способах реализации принимается в каждом случае отдельно. Чаще всего применяются следующие способы: последовательная нумерация (суррогатный ключ каждого нового естественного ключа получается увеличением существующего максимального из известных суррогатных ключей на единицу)и кодирование естественного ключа (суррогатный ключ вычисляется из естественного с помощью функциональной зависимости).

Согласование ключей – операция приведения идентификаторов набора данных источника к виду, конформному идентификаторам ХД. Согласование ключей выполняется чаще всего с помощью карт соответствия идентификаторов (IDMAP).

Результат всех преобразований поступает в таблицы области STCF, структура которых повторяет структуру целевых таблиц ХД, за исключением служебных полей, существование которых оправдано только в ХД. Ограничения (constraints) в таблицах области STCF не создаются, так как качество информации должно быть проверено вполне на этапе очистки данных.

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

Распределение данных

Распределение данных на несколько потоков перед вставкой в ХД требуется для того, чтобы разделить новые данные и записи, которые должны обновить или дополнить ранее поступившую информацию в ХД.
Записи могут распределяться из STCF на «новые» (область STIN) и «повторные» (область STUP) разными методами.

Самый простой, но, в то же время, и самый ресурсоёмкий способ – полное сравнение записей из STCF с данными, которые уже находятся в ХД, при этом в качестве параметров сравнения используется имеющаяся ключевая информация (первичные и альтернативные ключи).

Альтернативой является использование признаков модифицированных данных, или полей даты-времени последней модификации записи, если, конечно, такие поля есть в источнике данных и содержат информацию высокого качества.

Физическая структура таблиц областей STIN и STUP, также как и STCF, полностью повторяет структуру целевых таблиц ХД.

Вставка и обновление данных

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

Вставка данных производится простым копированием записей из таблицы STIN в таблицу ХД.

Обновление данных ХД из таблицы области STUP производится в соответствии с требованиями к ведению истории данных. Непосредственно, сама операция обновления может выполняться запросом UPDATE, или парой запросов DELETE и INSERT.

Оптимизация перегрузки данных

Процесс перегрузки данных источников в хранилище данных, с технической точки зрения, является последовательностью SQL-запросов к СУБД над довольно большими объёмами данных (от 1 до 100 мегабайт за один сеанс). Поэтому, выполнение неоптимизированных процессов перегрузки может на порядки увеличить время выполнения за счёт излишних или повторных обработок или пересылок данных.

Замечание: оптимизация ETL должна выполняться строго после отладки.

Общие приёмы оптимизации

Основная особенность загрузки хранилищ данных с точки зрения СУБД – наличие таблиц с большим количеством записей. Следующая таблица описывает соответствующие особенности (неудобства) и методы их устранения.

Помимо основных правил оптимизации выполнения SQL-запросов, описанных в литературе по РСУБД (использование индексов, отказ от коррелированных подзапросов в пользу derived query, и т.п.), в процессах перегрузки данных есть несколько узких мест, которые можно оптимизировать. Эти узкие места – фаза очистки данных, этап генерации суррогатных ключей и фаза вставки данных в ХД.

Оптимизация очистки данных

При очистке данных производится проверка каждой записи на соответствие ряду заранее выбранных критериев и правил.

Способ 1

Так как проверка одних критериев может зависеть от результатов проверки других (например, проверка ограничения на значение числа, содержащегося в поле с типом данных char(10), зависит от проверки является ли содержимое этого текстового поля числом), то рекомендуется по результатам проверки критериев с более высоким приоритетом (в приведённом примере проверка является ли значение поля числом имеет более высокий приоритет) формировать промежуточные (временные) таблицы, которые будут потом проверяться на соответствие другим критериям. В результате, запрос на проверку каждого последующего критерия будет обрабатывать всё меньший объём данных.

Этот способ имеет и недостатки: отсутствие выигрыша при поступлении качественных данных, и даже проигрыш по скорости за счёт потребности в очистке промежуточных таблиц; увеличение числа объектов промежуточной области; увеличение числа шагов.

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

Способ 2

Для анализа критериев, время проверки которых невелико, можно использовать логические файлы (представления). Это позволит уменьшить время выполнения фазы за счёт отсутствия излишних пересылок данных в промежуточные таблицы и упразднить некоторые шаги.

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

Способ 3

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

Оптимизация процедуры формирования суррогатных ключей

Формирование суррогатных ключей по принципу последовательной нумерации, в случае использования SQL-запроса для этой операции - чрезвычайно ресурсоёмкая процедура, так как она создаёт и обрабатывает временную таблицу, объём которой равен половине квадрата от количества записей исходной таблицы. Поэтому в некоторых случаях, при планируемом поступлении большого количества записей в каждой загрузке (более 1E4), необходимо рассмотреть альтернативные варианты процедуры генерации суррогатных ключей. Например: кодированные ключи, хранимая процедура, автонумерация.

Оптимизация фазы вставки данных в ХД

Поток вставки (STIN)

Вставка данных в ХД происходит через таблицу STIN, данные в которую попадают из таблицы области STCF. Однако, зачастую перегрузка записей из STCF в STIN представляет из себя SQL-запрос с объединением с целевой таблицей, а перегрузка из STIN в ХД – простое копирование.
Можно объединить эти шаги и заполнять ХД непосредственно из таблицы области STCF.

Поток обновления (STUP)

Операция обновления UPDATE – одна из самых медленных в РСУБД. Один из методов обойти использование UPDATE – замена её на операции удаления и вставки.

Возможны два варианта замены:

1. Стандартный

a. Удаление из ХД строк, которые имеются в STUP
b. Вставка всей таблицы STUP в ХД

2. Оптимизированный

a. Вставка в STUP строк, которых нет в STUP, но есть в ХД
b. Очистка всей таблицы ХД
c. Вставка в ХД всей таблицы STUP

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

Подобная замена будет эффективна при большом количестве обновляемых полей таблицы ХД (более 10). Однако, эта замена невозможна для случаев, когда ограничения ссылочной целостности (reference constraints) созданы физически в базе данных. Для таких случаев UPDATE – единственный способ обновления данных.

Оптимизация фазы преобразования

В описании фазы преобразования данных говорилось о том, что уже на этой фазе производится выделение данных из одной таблицы «на входе» в таблицы, аналогичные сущностям хранилища данных, «на выходе». Однако, легко заметить, что этот метод, отвечая традициям улучшения сопровождаемости кода, является неоптимальным с точки зрения количества таблиц, запросов и, как следствие, времени загрузки.
Более рациональным подходом будет заполнение одной таблицы STCF с уже преобразованными ключами, а разделение данных на разные сущности оставить на этап распределения данных (STIN и STUP). Потенциально, это может дать ускорение как разработки, так и выполнения процедур загрузки (до нескольких раз) без ухудшения сопровождаемости кода.

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

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

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

• Часть 3

По этой теме можно также почитать:

• Суррогатные ключи. Контролируйте идентификаторы строк формированием суррогатных ключей в хранилищах данных
• Суррогатные ключи. Конвейерная обработка суррогатных ключей
• Статьи из раздела Использование суррогатных ключей
• Статьи из раздела ETL

Для удобства отслеживания новых публикаций на сайте рекомендую подписаться на рассылку или подписаться на канал RSS.

Если вы нашли в сети интересные ссылки на ресурсы по технологиям хранилищ данных, OLAP, CRM или data mining, и хотите поделиться ими с другими, присылайте их. Я с удовольствием размещу их на этом сайте.