Порядок разработки ETL-процессов

Е.В. Островский, специалист производственного центра Datagy компании "Диасофт"

Введение

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

Данная статья описывает общие правила разработки ETL-процессов и определяет последовательность операций при загрузке хранилища данных (ХД) из источников данных.

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

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

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

Структура процесса перегрузки данных

Процесс

В общем случае, программист ETL может представлять себе архитектуру ХД в виде совокупности трёх областей: источник данных( совокупность таблиц оперативной системы и дополнительных справочников (классификаторов, таблиц согласования), позволяющую создать многомерную модель данных с требуемыми измерениями), промежуточная область (совокупность таблиц, использующихся исключительно как промежуточные при загрузке ХД) и приёмник данных. Движение данных от источника к приёмнику называют потоком данных. Необходимые потоки данных формирует и описывает аналитик (см. рис. 1).

Рисунок 1

Процесс перегрузки данных – это реализация потока данных от единственного набора данных источника до одного или нескольких наборов данных ХД.
Различают следующие классы процессов:

1. По характеру загрузки:

• Процесс начальной загрузки (Initial load)
• Процесс обновляющей загрузки (Refreshing load)

2. По виду источника данных:

• SCF (источник данных – стандартный классификатор Datagy, чаще всего – структурированный текстовый файл)
• UCF (источник данных – стандартный классификатор оперативной системы, пользовательский классификатор)
• MLR (источник данных – RDS или таблица фактов оперативной системы)
• DWH (источник данных – хранилище данных)

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

Фаза

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

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

Шаг

Шаги представляют собой отдельные SQL-запросы, которые выполняют единичные действия по перегрузке, преобразованию и выборке данных.

Каждый запрос (равно как и скрипты фаз и процессов) оформляется в отдельном файле в соответствии со «Стандартом на оформление технологических документов».

Группа процессов

Группы процессов введены для организации периодических перегрузок данных и указывают чёткую последовательность выполнения процессов внутри группы.

Стадии загрузки источника данных

В процессе загрузки, данные проходят следующие основные стадии, каждая из которых реализуется в виде отдельной фазы процесса перегрузки данных:

Таблица 1: Основные стадии процесса загрузки данных

* Операция резервирования BACKUP в основной массе проектов по созданию двухуровневых ХД не применяется.Требуется дальнейшее рассмотрение целесообразности введения этой операции вообще, так как она может быть заменена применением SCD уровня 2 и выше.

** Операция удаления записей из ХД в общем случае не реализуется из-за отсутствия потребности в ней. Решение о её создании принимается каждый раз, если того требует техническое задание.

Рассмотрим основные стадии процесса загрузки данных подробнее.

Извлечение данных (IDC)

Результатом работы, на этой стадии, является перегрузка данных из источника в реляционную СУБД, в промежуточную область. Для этой цели, под каждый источник данных, в промежуточной области создаётся своя таблица. Все таблицы имеют префикс “sttm” (сокр. от “STaging Table for Middle-layer data”), либо “stti” (сокр. от “STaging Table for Initial load”) (также вошла в обиход фраза: «Таблица принадлежит области STTM (STTI)»).

Таблицы, участвующие в загрузке справочников SCF и UCF чаще всего относятся к области STTI, так как не требуют написания процедур обновляющей загрузки; а те, что участвуют в загрузке таблиц фактов и обновляемых RDS – к STTM, так как для них обновляющая загрузка (загрузка данных в ХД с учётом наличия данных в ХД) почти всегда разрабатывается.

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

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

Таблица 2

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

Структурирование данных (Structuring)

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

• DBF
• TXT (TSV, CSV, или файл с выровненными полями)
• XML

Как уже сказано выше, структурированию подвергаются только данные, которые выгружаются из неструктурированных источников данных.

Обработка данных (Refinement)

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

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

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

Пересылка данных (Transfer)

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

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

И наоборот, данные источника не всегда могут быть обработаны (подпроцесс Refinement) на сервере поставщика данных. Тогда их необходимо переслать для обработки на сервер консолидации данных, что также требует наличия защищённых каналов связи и административных ресурсов. В этом случае, этап пересылки выполняется прежде структурирования данных.

Импорт данных в СУБД (Upload)

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

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

Обработка ошибок в стадии IDC

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

Примеры фатальных ошибок:

• отсутствие файлов источника данных;
• ошибка доступа к данным;
• возникновение системной ошибки ОС.

Типовые сценарии извлечения данных

Таблица 3

* В случае извлечения данных из ODBC-, JDBC-источника данных, или СУБД, применяется функция Pump программы SQLExecutor. Она логически разделяет пересылку данных на две части – извлечение и вставка данных – и, между этими частями, может применить программную обработку данных через механизм трансформаторов. Подробнее см. документацию по SQLExecutor.

Очистка данных

Очистка данных заключается в фильтрации тех данных, которые, в каком-либо смысле, не удовлетворяют существующим физическим ограничениям или бизнес-правилам.
При этом данные из таблицы области STTM полностью разделяются на 2 части (потока), которые впоследствии обрабатываются отдельно: данные, которые не прошли проверку, поступают в таблицу с префиксом “ster”, а данные, пригодные к дальнейшей обработке – в таблицу с префиксом “stac”.

Рисунок 2

STER

В поток STER, как уже сказано, попадают данные, не пригодные к загрузке в ХД по какому-либо заранее заданному критерию. Эти критерии определяются на этапе информационного исследования и преобразуются в SQL-запросы разработчиком ETL.

Категории критериев оценки качества данных можно свести к следующим классам:

A. По критичности:

1. Критичные ошибки в данных (данные, которые не соответствуют этому критерию, не могут быть загружены в ХД). Пример: числовое выражение, содержащее букву.
2. Некритичные ошибки в данных (данные, которые могут быть загружены в ХД, но не являются качественными). Пример: пустое (NULL) значение в поле имени.
3. Качественные данные.

B. По проверяемым объектам:

1. Корректность форматов и представлений данных.
2. Уникальность первичных и альтернативных ключей.
3. Полнота данных.
4. Полнота связей.
5. Соответствие данных аналитическим ограничениям.

Возможны несколько вариантов реализации процедур фильтрации потока STER:

1. Записи таблицы области STTM обрабатываются по принципу приоритета: если запись не удовлетворяет критерию с более высоким приоритетом, то она не будет проверяться на соответствие остальным критериям с более низким приоритетом, и заносится в набор данных области STER (это может быть как таблица, так и логический файл-представление(view), но его физическая структура полностью включает в себя все поля исходной таблицы STTM).
   Пример:

2. Записи таблицы области STTM получают уникальный идентификатор, и таблица STER формируется по принципу «тестового листа» - как карта соответствия критерия проверки качества данных и идентификатора записи.
   Пример:

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

Для проектов, где качество данных не является составляющей основных требований, поток STER просто не выводится, и процедуры проверки данных не разрабатываются.

STAC

Таблица области STAC содержит данные, повторяющие входные по составу полей, но уже прошедшие все фильтры на этапе заполнения STER, и, таким образом, признанные качественными. Разработка дальнейших процедур производится с учётом того, что данные области STAC не «пропадут» и не «задвоятся» при объединении, будут удовлетворять бизнес-правилам и ограничениям на формат данных.
Формирование таблицы STAC может производиться по принципу «STTM минус STER», или наложением всех фильтров одновременно. Первый способ предпочтительнее, из-за очевидного выигрыша в скорости.

Рисунок 3. Способ 1

Рисунок 4. Способ 2

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

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

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

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

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

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

Генерация суррогатных ключей – операция сопоставления естественному ключу (чаще всего – составному) уникального суррогатного ключа - идентификатора набора данных ХД. Применяется для обновляемых 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 позволят существенно оптимизировать затраты при построении современного аналитического информационного комплекса и повысить его эффективность.