Центр управления данными нашей компании занимается построением хранилищ, Data Lake, платформ данных и BI-систем. ETL — неотъемлемая часть нашей работы. Сегодня мы рассмотрим актуальные подходы к созданию подобных решений и расскажем о двух проектах, где они были реализованы нестандартными способами.

ETL vs EL-T

Extract Transformation Load — это извлечение данных из источника (E), их трансформация/обогащение (T) и загрузка (L) в хранилище или озеро. Изначально ETL-системы так и строились — в виде отдельного сервера (либо группы серверов), который извлекал в себя данные, трансформировал и загружал в хранилище. Т. е. данные прокачивались через внешнюю систему-посредник.

Однако за последние 10–15 лет ситуация изменилась. Классические ETL-системы все еще успешно применяются на рынке, но возникли нюансы:

• Широкое применение массивно-параллельных систем (MPP) и Hadoop. Если у вас параллельная и распределенная СУБД хранилища, ETL-сервер в качестве коммуникационной трубы между источником и хранилищем становится слабым звеном системы (в силу ограничений канала передачи данных). При этом MPP-системы предлагают параллельные средства загрузки данных. Например, PXF в Greenplum или Apache Sqoop для Hadoop.

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

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

Как результат — порядок букв изменился на «EL-T». Мы загружаем данные в СУБД как есть (EL), а затем трансформируем их внутри БД ее же инструментами (T). Безусловно, при желании этот подход можно реализовать и в традиционных ETL-решениях. Например, в Informatica PowerCenter есть функционал PushDown, к тому же почти во всех ETL можно использовать SQL-команды.

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

Особняком стоит Oracle Data Integrator (ODI), который изначально проектировался как EL-T инструмент. Решение вообще не прокачивает через себя данные, а является оркестратором и настраиваемым кодогенератором, который гибко подстраивается под возможности источников и хранилища. Отдельный плюс — возможность создания своих Knowledge Module под новые системы. Нет поддержки PXF и Greenplum? Без проблем — напишите Knowledge Module, и она будет. При этом само проектирование EL-T ведется как в традиционных решениях: маппинги таблиц, колонок и их трансформация настраиваются примерно как в уже упомянутой Informatica PowerCenter.

Не можешь купить, сделай сам

Большинство вендоров покинули Россию, поэтому тем же Oracle Data Integrator в новых проектах уже не воспользоваться. Что касается отечественных аналогов и Open Source продуктов, к сожалению, в основном они идут по пути ETL, а не EL-T (так, среди них уже практически нет решений с кодогенерацией). Отдельно отметим только FormIT Plus7 от DIS Group: решение содержит лицензированное ядро Informatica PowerCenter и может эффективно заменить оригинальную систему.

С одной стороны, после ухода вендоров на рынке образовался вакуум. С другой, в современных СУБД есть все для EL-T, кроме буквы O. Все это приводит нас к DIY-решениям («сделай сам»): EL-T, реализованное средствами СУБД, плюс внешний оркестратор. Отдельно оговоримся, что в этой статье мы не рассматриваем Change Data Capture (CDC) системы, только традиционные ETL/EL-T.

У DIY есть свои плюсы:

• Цена — по сути, вам не придется покупать сторонний продукт для реализации ETL

• Зачастую разработчикам легче читать SQL, чем диаграммы маппингов

• Гибкость и повторное использование кода за счет процедурного расширения SQL

• Скорость работы

Но есть и недостатки:

• Отсутствие поддержки от единого вендора

• Каждое решение уникально, это своего рода яйца Фаберже. Новые сотрудники будут долго погружаться в проект

• Некоторым специалистам, например, бизнес-аналитикам, проще читать маппинги, а не SQL

• Для реализации нужны более квалифицированные разработчики

• Традиционные решения обычно поддерживают Data Lineage. На голом SQL для этого потребуются сторонние продукты

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

В качестве внешнего оркестратора мы обычно используем решения Apache — Airflow или NiFi. У Airflow более широкие возможности в части оркестрации и мониторинга. NiFi же, помимо оркестрации, может выполнять традиционные ETL-задачи. Однако его все равно нельзя считать полноценным ETL/EL-T продуктом, поскольку в нем нельзя выстраивать маппинги на уровне полей таблиц. Выбор между ними — вопрос, скорее, «религиозный»: Python в Airflow против Java в NiFi. Ответ обычно зависит от уже имеющегося в компании технологического стека.

EL-T и современные фреймворки

Один из главных вызовов, возникающих при разработке современных хранилищ данных, — сокращение Time to Market. Зачастую процесс добавления нескольких атрибутов (даже не сущностей!) занимает много времени, и, что особенно печально, эти сроки сложно предсказать и обосновать перед бизнесом.

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

Нам нужно было обеспечить:

• Предсказуемое время внесения изменений

• Полное версионирование информации (возможность посмотреть вчерашнюю версию отчета)

• Контроль качества данных

Мы предложили сформировать детальный слой хранилища заново — на базе архитектуры Data Vault 2.0. В Data Vault данные представляются в виде трех сущностей, каждая из которых проецируется в отдельную таблицу:

• HUB — неизменяемая часть сущности (бизнес-ключ)

• LINK — связь между HUB (все связи хранятся отдельно)

• SATELLITE (SAT) — изменяемая часть атрибутов сущностей (историчность SCD2). SAT хранит атрибуты одного HUB или LINK, при этом для каждого HUB и LINK может быть несколько SAT.

По сути, Data Vault разделяет объекты (HUB) и их связи (LINK), а также обеспечивает отдельное хранение истории изменения атрибутов (SAT). Более детально архитектура описана в книге Daniel Linstedt, Michael Olschimke «Building a Scalable Data Warehouse with Data Vault 2.0».

Такая архитектура позволяет безболезненно добавлять новые атрибуты, сущности и связи в хранилище, при этом сохраняя всю историю изменений атрибутов. Из хранилища ничего не удаляется, просто добавляется отдельный атрибут — признак, показывающий, действует ли определенный экземпляр сущности или нет.

Применение Data Vault сводится к созданию трех видов таблиц (HUB, LINK, SAT) и стандартной обработки EL-T при загрузке данных в таблицы. Реализовывать эту логику руками для каждого HUB/LINK/SAT очень трудозатратно, плюс вероятность ошибок будет довольно велика. Поэтому мы создали фреймворк для работы с Data Vault 2.0. Он состоит из метаслоя, генератора структур таблиц и EL-T процедур, а также стандартного EL-T процесса, который читает настройки из метаслоя и запускает в нужном порядке сгенерированные EL-T процедуры.

В основе фреймворка лежит генератор, который получает на входе описание структуры Data Vault:

• Список HUB/LINK/SAT и их описание

• Список атрибутов и их описание

• Маппинг атрибутов представления (View) над объектами из Stage-области (об этом чуть ниже)

Генератор выполняет проверки, сравнивая текущую структуру Data Vault в БД с новой (всего более 20 проверок). Он определяет, можно ли привести ее к требуемой структуре, какие команды SQL можно выполнить (например, создать новые SAT), а какие — нельзя (поменять бизнес-ключ). Также генератор проверяет корректность и полноту описанного EL-T (например, что типы полей в Stage и Data Vault совместимы и EL-T описан для всех NOT NULL атрибутов), а затем генерирует код хранимых процедур для EL-T HUB/LINK/SAT. Плюс регистрирует новые объекты в метаслое. Отметим, что в нашем решении запрещено вручную вносить изменения в код создания таблиц и процедур загрузки.

ET-L процедуры грузят данные, выполняя всю сложную логику заполнения Data Vault. Например, для SAT считается хэш строки атрибутов и сравнивается с хэшем в БД. Если он поменялся, создается новая версия строки, а старая версия строки закрывается временем загрузки. Если хэш не менялся, ничего не происходит. Логика для LINK (в случае связи один ко многим) отслеживает, что произошла замена связи с одного объекта на другой, и создает новую, а старую помечает (запись в SAT этого LINK) как недействительную. Один раз отладив кодогенератор, мы получили достоверно работающие процедуры для всего EL-T. На самом деле генерация происходит из шаблонов, и часть кода можно настроить, просто меняя шаблон, без программирования.

Но где же здесь трансформация? Будь мы Google, мы бы, конечно, придумали свой язык :) Но ни мы, ни заказчик не хотели тратить лишнее время и деньги, поэтому использовали для трансформации язык SQL. Попросту создаем представления (View) над объектами Stage-областей, в которых и проводим все необходимые трансформации и обогащение данных. А затем ссылаемся на эти View из описания, которое подается на вход генератора. При этом одно View может использоваться для загрузки нескольких объектов. Например, из View факта продажи может грузиться соответствующий HUB, все его SAT, LINK и «многие к одному» (связи со справочниками). Этот подход позволяет эффективно разделять рутину по загрузке (кодогенератор все делает сам) и творческие задачи по трансформации данных.

Кстати, аналогичный подход реализован в популярном инструменте трансформации DBT. Описываете SELECT как источник данных, и DBT материализует его по вашему запросу. При этом в системе есть зависимости и встроенные средства оркестрации (порядок формирования). Сложную логику материализации (например, SCD2) придется делать руками, но если вам нужно просто формировать витрины по слоям, присмотритесь к DBT.

С точки зрения архитектуры все просто:

1. Данные трансформируются во View.

2. Переносятся в Data Vault сгенерированными EL-T процедурами под управлением данных из метаслоя.

3. Созданные вручную, но зарегистрированные в метаслое процедуры рассчитывают агрегаты, используемые в BI.

На схеме (см. рис. 1) также есть аббревиатура DQ. Одним из требований заказчика было обеспечение контроля качества данных в системе, и Data Quality (DQ) — как раз об этом.

Когда вы грузите данные не построчно через ETL-инструмент, а групповыми операторами типа INSERT SELECT или MERGE, ошибка в любой строке (например, NULL в NOT NULL колонке или дубликат первичного ключа) приведет к тому, что все данные не будут загружены. Чтобы этого избежать, перед загрузкой данные проходят фильтр строчного DQ. По сути, подлежащие загрузке данные из View помещаются во временную таблицу, которая проверяется механизмом DQ. Сюда входят: обязательная проверка на дубликаты ключей, автогенерируемые проверки на NULL в NOT NULL полях и произвольные проверки, описанные дата-инженером на языке SQL. При этом в случае обязательных проверок строки удаляются из временной таблицы, и создаются инциденты DQ. Для произвольных есть выбор — удалять данные или просто создать инцидент. После этого данные из временной таблицы загружаются в Data Vault. При этом для одного View проверка происходит один раз — независимо от того, сколько объектов Data Vault из него грузится.

Кроме строчного DQ в системе есть групповое, которое отрабатывает уже после загрузки данных в Data Vault. Это написанные вручную, но параметризируемые процедуры (что позволяет использовать их несколько раз). К примеру, с их помощью можно проверить, нет ли существенных отклонений в загруженных данных относительно предыдущих дней (с учетом календаря). Или узнать, поступили ли определенные данные (например, те, которые должны загружаться раз в месяц или квартал) в систему в указанную дату. Если проверка заканчивается неудачей, в DQ создается соответствующий инцидент.

Падение/ошибки EL-T процесса и ошибки при загрузке Stage-областей тоже автоматически создают инциденты DQ.

Все инциденты DQ маркируются группой в зависимости от правила, по которому они созданы. Например, системные инциденты, продажи, командировки и др. Инженер контроля качества данных описывает потребителей информации (например, конкретного BI-отчета). Далее для потребителя могут быть сопоставлены группы и вес одного инцидента группы. Например, потребитель отчета о продажах будет заинтересован в группе продаж (малый вес) и системной группе (огромный вес). А потребитель отчета о прибылях и убытках — в продажах, командировочных расходах и, конечно, в системной группе. Если сложить все открытые инциденты (в которых заинтересован потребитель), предварительно умноженные на их веса, и сравнить с пороговыми значениями, можно оценить качество данных. Мы реализовали трехуровневую систему: зеленый, желтый, красный. «Светофор» выведен и в систему мониторинга заказчика, и в отчеты. При этом пользователь может самостоятельно оценить достоверность данных. После закрытия инцидента «светофор» автоматически меняет цвет.

Благодаря автоматической генерации процессов EL-T мы реализовали механизм контроля качества данных как часть самого процесса EL-T (для всех объектов). При этом дата-инженер может донастраивать механизм в зависимости от ситуации.

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

Наше решение помогло компании сократить Time to Market в 2–3 раза, а главное, эти сроки стали предсказуемыми.

ETL/EL-T: а можно легче или вообще без них?

Мы привыкли перемещать данные, чтобы ими воспользоваться. В ИТ-ландшафте крупного современного предприятия зачастую есть несколько хранилищ и/или Data Lake. Можно ли в этом случае делать к ним запросы, не объединяя содержащиеся в них данные? А если требуется построить профиль Customer 360 для кол-центра (желательно онлайн)? Для это нужно перемещать данные? Или, предположим, дата-сайнтисты хотят получать все данные из единого окна. Придется строить еще одно хранилище?

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

Подключаться к нескольким источникам умеют многие BI-системы. Ключевое отличие виртуальных БД —наличие встроенного оптимизатора запросов, ориентированного на распределенную обработку. Он может получить статистику от подключенных источников (путем сбора или получения статистики реальной СУБД) и переписать запрос так, чтобы минимизировать объем результата и делегировать большую часть работы источникам. Например, таким образом можно «заставить» источник провести предагрегацию данных. В наличии и продвинутые техники оптимизации: например, можно передать часть работы кластеру Hadoop или закэшировать часть данных в специальной, выделенной под кэш БД, чтобы не нагружать источники. Там же можно и хранить предпросчитанные агрегаты.

Это не полноценная замена ETL/EL-T, хотя в некоторых случаях виртуальная БД все же может заменить или существенно облегчить эти процессы.

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

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

Мы выделили в системе три слоя:

1. Base Views — представления Denodo, которые соответствуют объектам в источниках.

2. Business Views — слой трансформации и обогащения.

3. User Views — слой, где данные представляются в удобном для пользователя виде (подстановка значений справочников, скрытие служебных полей и др.). Также на этом слое реализован контроль прав доступа по строкам (не всем сотрудникам нужно видеть VIP-клиентов).

Взаимодействие между слоями — это, по сути, View над View нижнего слоя. Данные первого слоя прозрачно кэшируются по расписанию, а при выполнении запроса к View он на самом деле идет к кэшу, а не к источнику. На втором уровне реализован механизм формирования сложных витрин путем INSERT SELECT. При этом витрины также доступны через Denodo, чтобы не повторять ресурсоемкие расчеты при каждом обращении к данным. Весь SQL-код проекта — это тексты View и INSERT SELECT плюс две процедуры в Denodo.

Для оркестрации мы использовали Denodo Scheduler. Он позволяет отслеживать зависимости, выполнять кэширование данных и формирование витрин в нужном порядке по ночам. В течение дня (раз в час) выполняется обновление кэша View c актуальной информацией.

В качестве бонуса (встроенный функционал системы) заказчик получил:

• Продвинутую систему прав доступа (ограничение по строкам и столбцам).

• Data Lineage. Можно отследить путь данных в колонку конечного представления или узнать, кто использует данные из выбранного представления.

• Data Catalog — витрину самообслуживания, с помощью которой можно получить описание каждой витрины и примеры данных (Sample) с учетом прав доступа.

Мы завершили проект за три месяца: развернули среды Denodo, спроектировали и реализовали бизнес-логику витрин, подготовили документацию, провели обучение специалистов заказчика и приемо-сдаточные испытания. Причем непосредственно реализация решения заняла буквально несколько недель. Достаточно было написать несколько SELECT для View, причем даже не написать, а нарисовать, потому что Denodo поддерживает графический дизайн представлений.  Мы как будто делали проект в одной БД, а не собирали данные из разных источников.

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

Выводы и рекомендации

• Самое быстрое EL — средства самой платформы (особенно в случае MPP).

• Самое быстрое T — сама СУБД (SQL).

• Выбор между ETL и EL-T — за вами. На наш взгляд, ETL лучше подходит для малых (до 100 тыс. строк перемещаемых данных ежедневно) и средних (до 1 млн строк) проектов. А EL-T — для больших (свыше 1 млн строк).

• В нынешних реалиях EL-T — это СУБД плюс внешний оркестратор.

• В качестве внешнего оркестратора можно использовать Airflow, NiFi или имеющийся у вас ETL-продукт.

• Использование типовых архитектур хранилища (Data Vault) требует создания фреймворка и генерации кода EL-T.

• Самодельное EL-T требует контроля качества данных на входе. При этом использование фреймворка для кодогенерации EL-T позволяет легко интегрировать контроль качества данных в процесс.

• Если вам не нужна сложная логика материализации данных, присмотритесь к DBT для трансформации информации внутри хранилища.

• Виртуализация данных может помочь в ETL/EL-T, а ИНОГДА даже полностью их заменить.

  Автор: Виктор Езерский, руководитель технологического отдела центра управления данными «Инфосистемы Джет».

Статья опубликована в JETINFO