Основные функции ETL-систем
ETL – аббревиатура от Extract, Transform, Load. Это системы корпоративного класса, которые применяются, чтобы привести к одним справочникам и загрузить в DWH и EPM данные из нескольких разных учетных систем.
Вероятно, большинству интересующихся хорошо знакомы принципы работы ETL, но как таковой статьи, описывающей концепцию ETL без привязки к конкретному продукту, на я Хабре не нашел. Это и послужило поводом написать отдельный текст.
Хочу оговориться, что описание архитектуры отражает мой личный опыт работы с ETL-инструментами и мое личное понимание «нормального» применения ETL – промежуточным слоем между OLTP системами и OLAP системой или корпоративным хранилищем.
Хотя в принципе существуют ETL, который можно поставить между любыми системами, лучше интеграцию между учетными системами решать связкой MDM и ESB. Если же вам для интеграции двух зависимых учетных систем необходим функционал ETL, то это ошибка проектирования, которую надо исправлять доработкой этих систем.
Зачем нужна ETL система
Проблема, из-за которой в принципе родилась необходимость использовать решения ETL, заключается в потребностях бизнеса в получении достоверной отчетности из того бардака, который творится в данных любой ERP-системы.
- Как случайные ошибки, возникшие на уровне ввода, переноса данных, или из-за багов;
- Как различия в справочниках и детализации данных между смежными ИТ-системами.
- Привести все данные к единой системе значений и детализации, попутно обеспечив их качество и надежность;
- Обеспечить аудиторский след при преобразовании (Transform) данных, чтобы после преобразования можно было понять, из каких именно исходных данных и сумм собралась каждая строчка преобразованных данных.
Как работает ETL система
Все основные функции ETL системы умещаются в следующий процесс:
В разрезе потока данных это несколько систем-источников (обычно OLTP) и система приемник (обычно OLAP), а так же пять стадий преобразования между ними:
- Процесс загрузки – Его задача затянуть в ETL данные произвольного качества для дальнейшей обработки, на этом этапе важно сверить суммы пришедших строк, если в исходной системе больше строк, чем в RawData то значит — загрузка прошла с ошибкой;
- Процесс валидации данных – на этом этапе данные последовательно проверяются на корректность и полноту, составляется отчет об ошибках для исправления;
- Процесс мэппинга данных с целевой моделью – на этом этапе к валидированной таблице пристраивается еще n-столбцов по количеству справочников целевой модели данных, а потом по таблицам мэппингов в каждой пристроенной ячейке, в каждой строке проставляются значения целевых справочников. Значения могут проставляться как 1:1, так и *:1, так и 1:* и *:*, для настройки последних двух вариантов используют формулы и скрипты мэппинга, реализованные в ETL-инструменте;
- Процесс агрегации данных – этот процесс нужен из-за разности детализации данных в OLTP и OLAP системах. OLAP-системы — это, по сути, полностью денормализованная таблица фактов и окружающие ее таблицы справочников (звездочка/снежинка), максимальная детализация сумм OLAP – это количество перестановок всех элементов всех справочников. А OLTP система может содержать несколько сумм для одного и того же набора элементов справочников. Можно было-бы убивать OLTP-детализацию еще на входе в ETL, но тогда мы потеряли бы «аудиторский след». Этот след нужен для построения Drill-down отчета, который показывает — из каких строк OLTP, сформировалась сумма в ячейке OLAP-системы. Поэтому сначала делается мэппинг на детализации OLTP, а потом в отдельной таблице данные «схлопывают» для загрузки в OLAP;
- Выгрузка в целевую систему — это технический процесс использования коннектора и передачи данных в целевую систему.
Особенности архитектуры
Реализация процессов 4 и 5 с точки зрения архитектуры тривиальна, все сложности имеют технический характер, а вот реализация процессов 1, 2 и 3 требует дополнительного пояснения.
Процесс загрузки
При проектировании процесса загрузки данных необходимо помнить о том что:
- Надо учитывать требования бизнеса по длительности всего процесса. Например: Если данные должны быть загружены в течение недели с момента готовности в исходных системах, и происходит 40 итераций загрузки до получения нормального качества, то длительность загрузки пакета не может быть больше 1-го часа. (При этом если в среднем происходит не более 40 загрузок, то процесс загрузки не может быть больше 30 минут, потому что в половине случаев будет больше 40 итераций, ну или точнее надо считать вероятности:) ) Главное если вы не укладываетесь в свой расчет, то не надейтесь на чудо — сносите и все, делать заново т.к. вы не впишитесь;
- Данные могут загружаться набегающей волной – с последовательным обновлением данных одного и того-же периода в будущем в течение нескольких последовательных периодов. (например: обновление прогноза окончания года каждый месяц). Поэтому кроме справочника «Период», должен быть предусмотрен технический справочник «Период загрузки», который позволит изолировать процессы загрузки данных в разных периодах и не потерять историю изменения цифр;
- Данные имеют обыкновение быть перегружаемыми много раз, и хорошо если будет технический справочник «Версия» как минимум с двумя элементами «Рабочая» и «Финальная», для отделения вычищенных данных. Кроме-того создание персональных версий, одной суммарной и одной финальной позволяет хорошо контролировать загрузку в несколько потоков;
- Данные всегда содержат ошибки: Перезагружать весь пакет в [50GB -> +8] это очень не экономно по ресурсам и вы, скорее всего, не впишитесь в регламент, следовательно, надо грамотно делить загружаемый пакет файлов и так проектировать систему, чтобы она позволяла обновлять пакет по маленьким частям. По моему опыту лучший способ – техническая аналитика «файл-источник», и интерфейс, который позволяет снести все данные только из одного файла, и вставить вместо него обновленные. А сам пакет разумно делить на файлы по количеству исполнителей, ответственных за их заполнение (либо админы систем готовящие выгрузки, либо пользователи заполняющие вручную);
- При проектировании разделения пакета на части надо еще учитывать возможность так-называемого «обогащения» данных (например: Когда 12 января считают налоги прошлого года по правилам управленческого учета, а в марте-апреле перегружают суммы на посчитанные по бухгалтерскому), это решается с одной стороны правильным проектированием деления пакета данных на части так, чтобы для обогащения надо было перегрузить целое количество файлов (не 2,345 файла), а с другой стороны введением еще одного технического справочника с периодами обогащения, чтобы не потерять историю изменений по этим причинам).
Процесс валидации
Данный процесс отвечает за выявление ошибок и пробелов в данных, переданных в ETL.
Само программирование или настройка формул проверки не вызывает вопросов, главный вопрос – как вычислить возможные виды ошибок в данных, и по каким признакам их идентифицировать?
Возможные виды ошибок в данных зависят от того какого рода шкалы применимы для этих данных. (Ссылка на прекрасный пост, объясняющий, какие существуют виды шкал — http://habrahabr.ru/post/246983/).
Ближе к практике в каждом из передаваемых типов данных в 95% случаев возможны следующие ошибки:
- Не из списка разрешенных значений
- Отсутствие обязательных значений
- Не соответствие формату (Все договора должны нумероваться «ДГВxxxx..»)
- Не из списка разрешенных значений для связанного элемента
- Отсутствие обязательных элементов для связанного элемента
- Не соответствие формату для связанного элемента(например: для продукта «АИС» все договора должны нумероваться «АИСxxxx..»)
- Символы допустимые в одном формате, недопустимы в другом
- Кодировка
- Обратная совместимость (Элемент справочника был изменен в целевой системе без добавления мэппинга)
- Новые значения (нет мэппинга)
- Устаревшие значения (не из списка разрешенных в целевой системе)
- Не число
- Не в границах разрешенного интервала значений
- Пропущено порядковое значение (например: данные не дошли)
- Не выполняется отношение y=ax+b (например: НДС и Выручка, или Встречные суммы равны)
- Элементу «А» присвоен неправильный порядковый номер
- Разницы за счет разных правил округления значений (например: в 1С и SAP никогда не сходится рассчитанный НДС)
- Переполнение
- Потеря точности и знаков
- Несовместимость форматов при конвертации в не число
- День недели не соответствует дате
- Сумма единиц времени не соответствует из-за разницы рабочие/не рабочие/праздничные/сокращенные дни
- Несовместимость формата даты при передаче текстом (например: ISO 8601 в UnixTime, или разные форматы в ISO 8601)
- Ошибка точки отсчета и точности при передаче числом (например: TimeStamp в DateTime)
Соответственно проверки на ошибки реализуются либо формулами, либо скриптами в редакторе конкретного ETL-инструмента.
А если вообще по большому счету, то большая часть ваших валидаций будет на соответствие справочников, а это [select * from a where a.field not in (select…) ]
При этом для сохранения аудиторского следа разумно сохранять в системе две отдельные таблицы – rawdata и cleandata с поддержкой связи 1:1 между строками.
Процесс мэппинга
Процесс мэппинга так же реализуется с помощью соответствующих формул и скриптов, есть три хороших правила при его проектировании:
-
Таблица замэпленных данных должна включать одновременно два набора полей – старых и новых аналитик, чтобы можно был сделать select по исходным аналитикам и посмотреть, какие целевые аналитики им присвоены, и наоборот:
Заключение
В принципе это все архитектурные приемы, которые мне понравились в тех ETL инструментах, которыми я пользовался.
Кроме этого конечно в реальных системах есть еще сервисные процессы — авторизации, разграничения доступа к данным, автоматизированного согласования изменений, и все решения конечно являются компромиссом с требованиями производительности и предельным объемом данных.
Надеюсь, эта информация будет вам полезна.
- Веб-разработка
- Анализ и проектирование систем
Что такое ETL?
Извлечение, преобразование и загрузка (ETL) — это процесс, используемый организациями, управляющими данными, для сбора данных из различных источников и последующего их объединения для исследования, отчетности, анализа и принятия решений.
Источники данных могут быть очень разными по типу, формату, объему и надежности, поэтому данные необходимо обработать, чтобы они были полезны при объединении. В зависимости от целей и технической реализации целевыми хранилищами данных могут быть базы данных, хранилища данных или озера данных.
Три отдельных этапа ETL
Извлечение
В процессе извлечения ETL идентифицирует данные и копирует их из источников, чтобы перенести их в целевое хранилище данных. Данные могут поступать из структурированных и неструктурированных источников, включая документы, электронную почту, бизнес-приложения, базы данных, оборудование, датчики, третьи лица и многие другие.
Преобразование
Поскольку извлеченные данные в исходном виде являются необработанными, их необходимо отобразить и преобразовать, чтобы подготовить их для конечного хранилища данных. В процессе преобразования ETL выполняет проверку достоверности, аутентификацию, дедупликацию и (или) агрегирует данные таким образом, чтобы полученные в результате данные были надежными и доступными для запроса.
Загрузка
ETL перемещает преобразованные данные в целевое хранилище данных. Этот этап может включать в себя первоначальную загрузку всех исходных данных, или это может быть загрузка постепенных изменений в исходных данных. Данные можно загружать в режиме реального времени или партиями по расписанию.
ELT или ETL: в чем разница?
Этап преобразования, безусловно, является самым сложным в процессе ETL. Таким образом, ETL и ELT различаются по двум основным вещам.
- Когда происходит преобразование
- Где происходит преобразование
В традиционном хранилище данных данные сначала извлекаются из исходных систем (ERP-систем, CRM-систем и т. д.). Инструменты OLAP и запросы SQL зависят от стандартизации измерений наборов данных для получения агрегированных результатов. Это означает, что данные должны пройти ряд преобразований.
Традиционно эти преобразования выполнялись до загрузки данных в целевую систему, как правило в хранилище реляционных данных.
Однако по мере развития технологий хранения и обработки данных, лежащих в основе хранилищ данных, стало возможным проводить преобразования внутри целевой системы. Процессы ETL и ELT включают в себя области технологической подготовки. В ETL эти области находятся в инструменте, независимо от того, является он собственным или специализированным. Они находятся между исходной системой (например, CRM-системой) и целевой системой (хранилищем данных).
В отличие от инструмента ETL, в ELT область технологической подготовки находится в хранилище данных, а преобразования выполняет движок базы данных, на котором работает СУБД. Поэтому одним из первых результатов использования ELT является потеря функций подготовки и очистки данных, которые предоставляют инструменты ETL для помощи в процессе преобразования данных.
ETL и корпоративные хранилища данных
Традиционно инструменты для ETL в основном использовались для доставки данных в корпоративные хранилища данных, поддерживающие приложения бизнес-аналитики (BI). Такие хранилища данных разрабатывались для использования в качестве надежного источника истины обо всем, что происходит на предприятии по всем видам деятельности. Данные в этих хранилищах тщательно структурированы с помощью тщательных схем, метаданных и правил, регулирующих проверку данных.
Инструменты ETL для корпоративных хранилищ данных должны отвечать требованиям интеграции данных, таким как пакетная загрузка с высоким объемом и производительностью, интеграционные процессы, управляемые событиями и потоками данных, программируемые преобразования и оркестрации, чтобы они могли справляться с самыми сложными преобразованиями и рабочими процессами и иметь средства сопряжения для самых разных источников данных.
После загрузки данных у Вас есть несколько стратегий для обеспечения их синхронизации между исходным и целевым хранилищами данных. Вы можете периодически перезагружать полный набор данных, планировать периодические обновления последних данных или поддерживать полную синхронность между источником и целевым хранилищем данных. Такая интеграция в реальном времени называется регистрацией измененных данных (CDC). Для этого продвинутого процесса инструменты ETL должны понимать семантику транзакций исходных баз данных и правильно передавать эти транзакции в целевое хранилище данных.
ETL и витрины данных
Витрины данных — это меньшие по размеру и более сфокусированные по сравнению с корпоративными хранилищами данных целевые хранилища данных. Например, они могут быть сфокусированы на информации об одном отделе или одном продуктовом направлении. В связи с этим пользователями инструментов ETL для витрин данных часто являются специалисты одной предметной области (LOB), аналитики данных и (или) исследователи данных.
Инструменты ETL для работы с витринами данных должны быть удобны для использования скорее бизнес-сотрудниками компании и менеджерами данных, нежели программистами и ИТ-персоналом. Поэтому в этих инструментах должен иметься визуальный рабочий процесс, чтобы облегчить настройку конвейеров ETL.
ETL или ELT и озера данных
В озерах данных используется другая модель, чем в хранилищах данных и витринах данных. Озера данных обычно хранят свои данные в объектных хранилищах или распределенных файловых системах Hadoop (HDFS), поэтому они могут хранить менее структурированные данные без схемы; кроме того, они поддерживают множество инструментов для запросов к этим неструктурированным данным.
Одной из дополнительных моделей, которая позволяет это делать, является извлечение, загрузка и преобразование (ELT), при котором данные сначала хранятся как есть, а после сбора данных в озеро данных они преобразуются, анализируются и обрабатываются. В такой модели есть ряд преимуществ.
- Все данные записываются; сигнал не теряется из-за агрегации или фильтрации
- Данные могут поступать очень быстро, что полезно для Интернета вещей (IoT) потоковой передачи, аналитики журналов, метрик веб-сайта и т. д.
- Таким образом можно обнаружить тенденции, которых не ожидалось в момент сбора данных.
- Это позволяет внедрять новые методы искусственного интеллекта (AI), которые отлично справляются с обнаружением закономерностей в больших неструктурированных массивах данных.
Инструменты ETL для озер данных включают визуальные инструменты интеграции данных, поскольку они эффективны для исследователей данных и инженеров по работе с данными. В архитектуре озера данных часто используются следующие дополнительные инструменты:
- Облачные потоковые сервисы, которые могут передавать большие потоки данных в реальном времени в озера данных для обмена сообщениями, журналов приложений, оперативной телеметрии, отслеживания маршрутов переходов по веб-сайтам, обработки событий и анализа безопасности. Совместимость с Kafka гарантирует, что эти сервисы могут получать данные практически из бесконечного числа источников данных.
- Облачные сервисы на базе технологии Spark, способные быстро выполнять задачи по обработке и преобразованию данных на очень больших наборах данных. Сервисы Spark могут загружать наборы данных из объектного хранилища или HDFS, обрабатывать и преобразовывать их в памяти на масштабируемых кластерах вычислительных экземпляров и записывать выходные данные обратно в озеро данных или в витрины данных и (или) хранилища данных.
Сценарии применения ETL
Процесс ETL является основополагающим для многих отраслей благодаря тому, что он позволяет быстро и надежно вводить данные в озера данных для обработки и анализа данных, создавая при этом высококачественные модели. Решения ETL также могут загружать и преобразовывать транзакционные данные в требуемом масштабе для создания упорядоченного представления из больших объемов данных. Это позволяет предприятиям визуализировать и прогнозировать отраслевые тенденции. Решения ETL используются в разных отраслях для получения действенной информации, быстрого принятия решений и повышения эффективности.
Финансовые услуги
Финансовые учреждения собирают большие объемы структурированных и неструктурированных данных, чтобы получить представление о поведении потребителей. Благодаря этим данным можно анализировать риски, оптимизировать финансовые услуги банков, совершенствовать онлайн-платформы и даже снабжать банкоматы наличными.
Нефтегазовая промышленность
В нефтегазовой промышленности решения ETL используются для создания прогнозов об использовании, хранении и тенденциях в конкретных географических районах. ETL работает над тем, чтобы собрать как можно больше информации со всех сенсоров на месте извлечения и обработать эту информацию, чтобы сделать ее легко читаемой.
Автомобильная отрасль
Решения ETL позволяют дилерским центрам и производителям понять структуру продаж, скорректировать свои маркетинговые кампании, пополнить запасы и следить за привлечением клиентов.
Телекоммуникации
Учитывая беспрецедентный объем и разнообразие данных, генерируемых сегодня, поставщики телекоммуникационных услуг используют решения ETL для лучшего управления этими данными и их понимания. После обработки и анализа этих данных предприятия могут использовать их для оптимизации рекламы, социальных сетей, SEO, повышения удовлетворенности клиентов, прибыльности и многого другого.
Здравоохранение
В связи с необходимостью снижения затрат и одновременного повышения качества обслуживания, решения ETL используются в сфере здравоохранения для управления медицинскими картами пациентов, сбора страховой информации и удовлетворения меняющихся нормативных требований.
Естественные науки
Клинические лаборатории используют решения ETL и искусственный интеллект (ИИ) для обработки различных типов данных, создаваемых исследовательскими учреждениями. Например, для совместной работы по разработке вакцин требуется собрать, обработать и проанализировать огромный объем данных.
Государственный сектор
Благодаря быстрому развитию возможностей Интернета вещей (IoT) умные города используют ETL и возможности искусственного интеллекта для оптимизации дорожного движения, мониторинга качества воды, улучшения парковок и многого другого.
Продукты и решения ETL
Комплекс Service Oriented Architecture (SOA)
Как упростить интеграцию приложений? Благодаря упрощенным возможностям интеграции облачных, мобильных, локальных и IoT-систем (все в рамках единой платформы) это решение позволяет ускорить время интеграции и повысить производительность, а также снизить совокупную стоимость владения (TCO). Многие корпоративные приложения, включая Oracle E-Business Suite, активно используют этот продукт для оркестровки потоков данных.
GoldenGate
Цифровая трансформация часто требует перемещения данных из мест их сбора в места, где они необходимы. Для упрощения этого процесса разработано решение GoldenGate. Oracle GoldenGate — это решение для высокоскоростной репликации данных с целью интеграции в режиме реального времени между гетерогенными базами данных, расположенными локально, в облаке или в автономной базе данных. GoldenGate повышает доступность данных без ущерба для производительности системы, обеспечивая доступ к данным в режиме реального времени и оперативную отчетность.
Cloud Streaming
Наше решение Cloud Streaming предоставляет полностью управляемое, масштабируемое и надежное решение для приема и потребления потоков данных большого объема в режиме реального времени. Используйте этот сервис для обмена сообщениями, получения журналов приложений, операционной телеметрии, данных о посещении или в любых других случаях, когда данные создаются и обрабатываются непрерывно и последовательно по модели обмена сообщениями «публикация-подписка». Решение полностью совместимо со Spark и Kafka.
7 ошибок ETL-разработчика
Проекты хранилищ данных уже давно являются частью IT-инфраструктуры большинства крупных предприятий. Процессы ETL являются частью этих проектов, однако разработчики иногда совершают одни и те же ошибки при проектировании и сопровождении этих процессов. Некоторые из этих ошибок описаны в этом посте.
Я хотел бы сразу сузить рамки обсуждения и договориться о терминологии:
- Хранилище данных (Datawarehouse, DWH) подразумевается традиционное SQL DWH (Oracle Database, MS SQL Server и т.д.);
- При моделировании DWH обычно подразумеваются концепции single version of truth (единая версия правды) и historical truth (историческая правда);
- Под ETL-процессом (Extraction-Transformation-Loading) подразумевается процесс загрузки данных из одной или нескольких source systems (исходных систем ) в DWH.
- DWH создано не вчера и в данный момент над ним независимо работает несколько команд разработчиков со своими проектами.
- Загружать данные в максимальном удобном виде для аналитических приложений;
- В процессе загрузки данных обогащать их дополнительной информацией;
- Фиксировать и документировать lineage (происхождение) данных.
В целом, суть большинства ошибок ETL-разработчика можно объяснить игнорированием жизненного правила с этой картинки.
В дальнейшем будут использованы примеры для DWH на базе Oracle 11g. Итак приступим.
1. Использование системной даты (или аналогичной функции) в бизнес-логике
Одна из самых простых и частых ошибок, особенно у неопытных разработчиков. Допустим есть бизнес-правило: во время «ночного окна для загрузки» выгружать заказы, которые были закрыты за этот день (по полю close_date). Результатом иногда бывает примерно такой sql statement:
insert into target_table
select from orders
where close_date >= sysdate() — 1
Даже если забыть про то, что sysdate() может содержать не только дату, но и время, то у нас с этим скриптом возникают проблемы в тот момент, когда регулярная работа ETL процесса нарушается по вполне банальным причинам (исходная система апгрейдится на новую версию, пропала связь с исходной системой, из-за нового процесса ETL закончилось место во временном tablespace и т.д.). Т.е. в тот момент когда наш ETL процесс нужно по каким-то причинам перезапустить или же приостановить на время и потом снова запустить. Также может произойти нечто интересное, если по какой-то причине этот процесс запустят дважды за день.
Решение у этой ошибки обычно простое: параметризовать вызов данного процесса, и если нужно, то использовать sysdate() как дефолтное значение с возможностью переопределения. Хотя использование поля типа datetime для обработки дельты с точки зрения сопровождения ХД не очень оптимально, и вместо него лучше применить дельту по некоему дискретному полю (например целочисленный id сессии загрузки или нечто подобное)
2. Профилирование данных не было сделано перед началом разработки
Даже самая документированная и разработанная по всем правилам и методикам исходная система обычно содержит в себе некорректные или неконсистентные данные, несмотря на многочисленные уверения ее разработчиков или команды поддержки. И полагаться на уверения в правильности с той стороны баррикад, обычно чревато проблемами в конце разработки. Любой источник данных (таблица, файл, xml, json и т.д.) должен быть проверен на соответствие логической модели DWH. Существуют различные инструменты для профилирования данных, как встроенные в ETL инструменты, так и независимые от них. Перечислю наиболее востребованные проверки:
Проверка #1: Уникальность идентификаторов и натуральных ключей исходных данных
Различие между идентификатором и натуральным ключом состоит в том, что идентификатор — это обычно некое суррогатное значение, которое технически идентифицирует строку, а натуральный ключ — это значение или комбинация значений, которые имеют бизнес-смысл.
Таблица order_details:
| order_details_id | document_position | order_id |
| 35346346 | 10 | 1224114 |
| 35346365 | 20 | 1224114 |
| …. | …. | …. |
| 35345464 | 10 | 1224438 |
В данном примере order_details_id — это идентификатор, а комбинация document_position+order_id — это натуральный ключ.
Пример: я участвовал в проекте по загрузке данных в DWH из распределенной системы (instance-based), в которой велся учет объектов сетевой инфраструктуры. Разработчики этой системы на голубом глазу уверяли, что id этого объекта является уникальным и даже показывали в исходной системе уникальный индекс на таблице в подтверждение своих слов. Подвох выявился не сразу: оказывается уникальность этих id существовала только в рамках одного инстанса системы, и когда попробовали загрузить все данные со всех инстансов, то получилась проблема с уникальностью. В результате пришлось менять модель данных и расширять натуральный ключ сущности «сетевой объект» дополнительным полем «инстанс», чтобы обеспечить уникальность.
Проверка #2: Типы данных
Если поле называется Order_nr, то в нем необязательно содержатся только числовые значения — там вполне могут быть буквенно-цифровые последовательности. Также всегда стоит проверять длину полей. Эта проблема обычно характерна для файловых источников данных — таблицы БД обычно хорошо типизированы.
Проверка #3: Ссылочная целостность (проверка FK)
То, что разработчик показывает ER-диаграммы своей исходной системы, показывает у себя на DEV-окружении существующие FK между таблицами, и вообще мамой клянется, что у него все под контролем, не является поводом не проверить существование «повисших» записей. Т.к. он может быть не в курсе, что на продуктивном окружении DBA уже отключил эту проверку для улучшения производительности (конечно, согласовав это с менеджером разработчика, т.е. никто не виноват). Также проблемы со ссылочной целостностью очень часто встречается для файловых источников данных. Также не стоит забывать о применении сценария late-arriving-data (например, если данные приходят согласовано сегодня, далеко не факт, что так будет и через полгода).
Проверка #4: NULL значения
Основная проблема NULL значений состоит в том, что NULL<>NULL, поэтому любые запросы с джойнами по полю, которое может содержать NULL, будут возвращать непредсказуемые результаты. Поэтому все важные поля стоит обернуть конструкцией nvl(). Существует отдельный холивар по поводу грузить NULL в неключевые поля или заменять на некие значения по умолчанию. Мне ближе идея о всеобщей замене NULLов для более стандартизированного подхода к использованию DWH, но я не берусь настаивать, что так нужно делать всегда.
Проверка #5: Даты
Проверки полей с датами обычно являются самыми усложненными, т.к. помимо стандартных проверок приходится учитывать то, что не все даты, которые являются допустимыми с точки зрения БД, являются таковыми с точки зрения DWH: дата «21-07-1007» вряд ли является допустимой для даты заключения договора на оказание услуг сотовой связи. При моделировании DWH обычно существуют т.н. даты «начала времен» и «конца времен» (возможны другие названия), и любая дата, не попадающая в этот диапазон времени должна заменяться на некое значение по умолчанию.
Отдельного упоминания заслуживают случаи использования типов данных вроде varchar(8) для хранения дат (в формате например ‘20151201’), т.к. количество проверок здесь должно быть еще больше.
3. Удаление дубликатов через GROUP BY или DISTINCT
Несмотря на то, что в DWH обычно грузятся все данные, которые приходят из источника, существуют сценарии, когда на вход приходят заведомо дублирующиеся данные. Но уникальность натурального ключа требует только одну запись из дубликатов. Существует два неправильных способа удаления дубликатов:
Неправильный способ #1: GROUP BY
Допустим, мы грузим адреса клиентов и знаем, что теоретически для одного клиента может прийти несколько записей с адресной информацией (обычно они являются полными дубликатами из-за проблем, например, с синхронизацией). Поддавшись желанию решить задачу «в лоб», разработчик может написать такой запрос:
insert into customer_address
select customer_id, max(street_name), max(house_nr)
from source_table
group by customer_id
Проблемы начнутся, если на вход придут две реально отличающиеся записи для одного клиента (например, была ошибка ввода оператора, которую он исправил, но в источник данных попали оба варианта записи):
| customer_id | street_name | house_nr |
| 1321 | Moskovskaya str | 127 |
| 1321 | Pushkinskaya str | 34 |
Запрос может вернуть такой результат (в зависимости от локали):
| customer_id | street_name | house_nr |
| 1321 | Pushkinskaya str | 127 |
Такой записи в исходных данных не было, и у пользователей DWH может возникнуть резонный вопрос, что вообще это такое? На самом деле здесь нарушено 3-е требование к ETL процессу: в DWH была загружена запись, которая не может быть отслежена до исходной системы, проще говоря, которой там нет. И это однозначная ошибка ETL разработчика.
Неправильный способ #2: DISTINCT
Второй вариант «решения в лоб» в описанном выше сценарии — это использовать для удаления дублирующихся записей DISTINCT
insert into customer_address
select distinct customer_id, street_name, house_nr
from source_table
В данном случае пара дублирующихся записей с разными атрибутами будет идентифицирована раньше, поскольку вместо одной получится две записи, и будет нарушена уникальность натурального ключа и ETL-процесс упадет с ошибкой.
Один из правильных способов
Как же стоит решать проблему наличия двух записей с одинаковым натуральным ключом, но разными атрибутами? Очевидно, что если в модель данных данных изменений не внести, то из всех записей должна быть выбрана одна единственная правильная. Выбирать ее нужно согласно заранее определенному критерию: если информация является довольно критичной, то можно реализовывать различные сценарии Data Quality, если же нет, то в качестве корректной записи брать последнюю загруженную.
insert into customer_address
select customer_id, street_name, house_nr from (
select customer_id, street_name, house_nr,
row_number() over (partition by customer_id order by change_datetime desc) row_num
from source_table)
where row_num = 1
В общем следует не забывать, что любая запись в DWH должна иметь возможность быть отслеженной до источника(ов) данных в зависимости от бизнес-правила и не создавать «необъяснимые» записи.
4. Использование «статичных» скриптов из исходных систем
Очень часто бизнес-логика для сущностей DWH приходит от разработчиков или аналитиков исходных систем в виде SQL скриптов. И это большое подспорье для ETL-разработчика, но, как говорится, «бойтесь данайцев, дары приносящих»: как правило эти скрипты фиксируют некое условно «статичное» состояние исходное системы в некоторый момент времени, а ETL-разработчик обычно занимается отслеживанием динамики в данных и загрузкой только изменений («дельта»). Что же должно настораживать в этих «статичных» SQL скриптах? Вот некоторые из:
- агрегатные функции (SUM, AVG, COUNT и т.д.)
- операторы IN и EXISTS
- оконные функции ( OVER(PARTITION BY …))
insert order_id into orders_from_calls
select order_id from orders
where order_id IN (select order_id from calls where order_id <> -1)
and changed_date > $last_loaded_date
Вроде бы все логично: грузить в нашу таблицу order_from_calls все заказы, на которые есть ссылка в таблице звонков, и для которых дата последнего изменения больше даты последней загрузки. А теперь представим, что обновление таблицы calls в DWH не произошло (например, она грузится из другой исходной системы и связь с ней по какой-то причине нарушена), и этот запрос не загрузил некоторые id заказов. После этого таблица calls была дозагружена правильно, и там эти пропущенные id заказов появились, но мы их уже не загрузим в таблицу order_from_calls, т.к. в таблице orders ничего не поменялось и новые запуски этого запроса ничего не дадут. Поэтому в данном случае отслеживать дельту нужно не только по таблице orders, но и по таблице calls.
5. Разработка на небольшом объеме данных для разработки
Как правило, ETL-разработчику для разработки на DEV-окружении выгружается небольшая часть данных из продуктивной системы, на которой и предлагается вести разработку и отладку работы ETL-процессов. К сожалению, разработанные на таком малом объеме данных решения обычно приводят к различным проблемам на продуктивной системе, таким как недостаточная производительность, нехватка места для промежуточных таблиц (например, разработчик решил красиво разнести шаги бизнес-логики по набору промежуточных таблиц, последовательно перегружая из одной в другую — а вот в продуктивной системе данных оказалось слишком много, и tablespace для временных таблиц скоропостижно закончился).
К сожалению, эту ошибку ETL-разработчик не всегда может решить самостоятельно, из-за различных регламентов и инструкций, отсутствия бюджета на полноценное DEV-окружение с тем же объемом данных как на продуктиве и т.д. Таким образом, это стоит рассматривать как проектный риск.
Одним из выходов является дробление этапов проекта на более мелкие и делать релизы более часто, чтобы идентифицировать такие проблемы не в конце проекта, а хотя бы посередине.
6. Неправильное использование технических и бизнес дат
В DWH существует 2 типа дат: бизнес-даты и технические даты. Разница у них в происхождении: бизнес-дата — это та дата, которая пришла из источника данных или была создана по бизнес-правилам; техническая дата — это дата, которая была сгенерирована ETL процессом либо самим DWH. И очень часто их используют неправильно:
#1 Бизнес-даты используются как технические даты
Если сущность историзируется как SCD2 (Slowly Changing Dimension type 2) и в источнике данных есть поля «_from» и «_to», которые ETL разработчику предлагается использовать в качестве диапазонов валидности данных, то у него должны быть просто железобетонные гарантии того, все диапазоны валидности для каждого натурального ключа будут: 1) непересекающимися, 2) между диапазонами не будет разрывов, 3) объединение этих диапазонов дат будет совпадать с диапазоном даты «от начала времен» до «конца времен» установленных для вашего DWH (это могут быть например пары дат «01.01.1000» и «31.12.9999», или «11.11.1111» и «09.09.9999»). Как правило, разработчики исходных систем мало заморачиваются, и если правило «непересекающихся диапазонов дат» обычно соблюдается, то со 2-м и 3-м пунктом обычно возникают проблемы. В любом случае, общей рекомендацией является не использовать бизнес-даты для SCD2, а генерировать свои технические даты.
#2 Технические даты используются как бизнес-даты
Очень часто источники данных не поставляют поля для отслеживания каких-либо контрольных дат: например, документ имеет только статус закрытия, но не метку времени, когда это событие произошло, и в качестве решения предлагается использовать технические даты «_from» и «_to», которые были сгенерированы ETL процессом. Однако это решение работает до первого сбоя ETL процесса (например, остановки ETL процессов на пару дней): сбой произошел в понедельник, восстановление наступило в среду, и т.к. исходная система вполне себе работала все это время, все созданные документы будут загружены как созданные в среду. В общем случае, сценарий «историческая правда» не реализуем, если источник данных не поставляет всех нужных пользователям дат и может быть лишь сэмулирован (с помощью технических дат), но в таком случае этот сценарий должен быть проговорен и описан в документации, чтобы через год пользователи не удивлялись нулевому количеству закрытых документов в понедельник и вторник, а также тройному количеству их в среду.
7. «Механическая» реализация
Это одна из самых сложных для идентификации ошибок и, по правде говоря, не является ошибкой именно ETL разработчика, а скорее архитектора DWH. Но над проектом работает же команда, и коллег выручать тоже надо.
Иногда так случается, что целевая сущность в DWH была неправильно смоделирована исходя из-за расхождений в терминологии для разработчика исходной системы и архитектора. Разработчик исходной системы мыслит категориями своей исходной системы, архитектору DWH же необходимо продумывать различные интеграционные схемы, как связать в едином DWH множество объектов из разнородных исходных систем.
Опишу на примере сущности «клиент» как одной из типичных для такого рода проблем: в источнике данных есть таблица «customer», имеющая уникальный натуральный ключ, ссылочная целостность в порядке. На основе этой таблицы в DWH была создана сущность «customer». Исходя из названия, логично предположить, что одна запись в этой таблице должна соответствовать одному клиенту, но фактически выяснилось, что на самом деле один и тот же реальный клиент мог иметь несколько записей с одними и теми же атрибутами, но разными натуральными ключами. И это привело бы к неприятной коллизии для пользователей DWH, которые использовали эту сущность, например, для подсчета общего количества клиентов компании. В результате было принято решение разделить эту сущность на две: «customer_record» и «customer», связанные через FK отношением M:1.
А если бы ETL разработчик «механически» реализовал все по спецификации, то он бы конечно был бы не виноват, но у него была возможность заметить это, т.к. в любом случае он по сравнению с архитектором работает условно говоря «на земле», в отличие от «витающего в облаках» архитектора.
В целом можно упомянуть некоторые симптомы «механической» реализации:
- «Наследовать» имена таблиц из исходной системы
- Копировать бизнес-логику из существующих потоков данных в новые
- Использовать джобы «пилотного» проекта в основном
- Внимательно анализировать бизнес-правила с любыми джойнами, которые потенциально могут «отрезать» часть данных (left outer join обычно предпочтительнее inner join)
- C другой стороны проверять «сомнительные» джойны, которые могут «размножить» данные из-за некорректного или неполного условия
Заключение
Безусловно, этот список не полон, но я надеюсь, что эта статья может навести некий порядок в головах, которые и так заморочены дедлайнами, майлстоунами, релизами и багфиксами.
ETL: что такое, зачем и для кого
ETL (Extract, Transform, and Load) — это основа современных data-driven бизнесов, которая включает в себя три процесса:
- Extraction или извлечение
Необработанные данные получают из разнородных источников, таких как база данных или приложение.
- Transformation или преобразование
Полученные данные изменяют, очищают и синхронизируют, чтобы конечному пользователю было легче читать.
- Loading или загрузка
После преобразования данные загружаются в целевую систему, которая в основном представляет собой инструмент бизнес-аналитики (BI) или хранилище данных.
Все об ETL: от ручного программирования к автоматизации
ETL стал популярным в 1970-х годах, когда компании начали работать с мэйнфреймами для хранения транзакционных данных по всем своим операциям. В результате возникла необходимость в эффективной интеграции всех этих данных. Вот где компаниям помогала ETL-система.
Хранилища данных появились в 1980-х годах и предлагали интегрированный доступ к данным из нескольких разнородных систем. Но проблема заключалась в том, что для многих баз данных требовались инструменты ETL, ориентированные на конкретного поставщика. Поэтому компании выбрали разные инструменты ETL для использования с разными хранилищами данных.
Однако эти скорейшие решения требовали физических усилий, таких как написание скриптов. А их также приходилось часто корректировать для различных источников данных.
Увеличение объема и сложности данных привело к появлению автоматизированного процесса ETL, исключающего ручное кодирование и предлагающего автоматизированный процесс для наблюдения за потоками данных.
Аналогия с пивом и подгузниками: почему важен процесс ETL?
Когда говорят о силе данных, часто упоминается интересная история. И это называется аналогией пива и подгузников.
В сети магазинов WalMart с помощью data mining обнаружили, что продажи подгузников и пива коррелировали по вечерам в пятницу. Таким образом, они поместили оба этих предмета ближе друг к другу и увидели заметный рост продаж.
Сейчас мы не уверены в правдивости этой истории, но точно знаем, что компания может использовать свои данные для получения ценной информации и принятия прибыльных решений.
Зачем вам нужна ETL-система:
- Процесс ETL экономит время и усилия при ручной обработке данных
Самым большим преимуществом процесса ETL является то, что он помогает вам автоматически собирать, преобразовывать и консолидировать данные. Это означает, что вы можете сэкономить время и силы, импортируя строки и строки данных вручную.
- ETL упрощает работу со сложными данными
Со временем вашему бизнесу приходится работать с большим объемом сложных и разнообразных данных. Например, могут быть разные часовые пояса, имена клиентов, идентификаторы устройств и местоположение.
Добавьте к этому еще несколько атрибутов, и вы сможете круглосуточно форматировать данные. Кроме того, файлы входящих данных могут быть разных форматов, макетов и типов. Вот где ETL может упростить вам жизнь.
- ETL снижает риски, связанные с человеческим фактором
Независимо от того, насколько осторожны вы со своими данными, вы не застрахованы от ошибок. Например, данные могут быть случайно дублированы в целевой системе, или ручной ввод может быть введен неправильно. Устраняя вмешательство человека, инструмент ETL может помочь вам избежать такого сценария.
- ETL помогает улучшить процесс принятия решений
Автоматизируя работу с критически важными данными и уменьшая вероятность ошибок, ETL помогает гарантировать, что данные, которые вы получаете для анализа, имеют наилучшее возможное качество. А качественные данные имеют основополагающее значение для принятия более эффективных корпоративных решений.
- ETL увеличивает рентабельность инвестиций (ROI)
Поскольку вы экономите время, усилия и ресурсы, ETL-процесс в конечном итоге помогает вам повысить рентабельность инвестиций. Кроме того, улучшая бизнес-аналитику, это помогает увеличить вашу прибыль.
Это связано с тем, что предприятия полагаются на ETL-процесс для представления консолидированных данных для принятия более эффективных бизнес-решений.
Пять шагов для успешного внедрения ETL
Если вы желаете реализовать успешный ETL-процесс, то выполните следующие 5 шагов:
Первый шаг — четко определить источники данных, которые вы хотите включить в свое хранилище данных. Этими источниками могут быть реляционные базы данных SQL, нереляционные базы данных NoSQL, платформы программного обеспечения как услуги (SaaS) или другие приложения. Как только источники данных установлены, определите конкретные поля данных, которые вы хотите извлечь. Затем принимайте или вводите эти данные из разнородных источников в самом необработанном виде.
Шаг 2. Преобразование
Следующим шагом является преобразование этих данных в унифицированные с помощью набора бизнес-правил (таких как агрегирование, присоединение, сортировка, функции объединения и т.д.).
После преобразования данные необходимо загрузить в хранилище. На этом этапе вам нужно будет установить скорость, которая относится к частоте загрузки данных. Укажите, будете ли вы вставлять новые данные или необходимо обновить существующие.
Важно выполнить проверку количества записей до и после передачи данных в хранилище данных. Это стоит выполнить для исключения недопустимых и избыточных данных.
Шаг 5. Автоматизация
Последний шаг — автоматизировать процесс ETL с помощью инструментов. Это поможет вам сэкономить время, повысить точность и уменьшить усилия, связанные с повторным запуском процесса вручную.
С помощью средств автоматизации ETL вы можете спроектировать рабочий процесс ETL и контролировать его через простой в использовании графический интерфейс. Кроме того, эти инструменты обладают сложными возможностями, такими как профилирование и очистка данных.
Примеры использования ETL-систем
Вот два наиболее распространенных варианта использования ETL-процессов для повышения эффективности на предприятиях:
- Синхронизация данных из нескольких источников
Компании часто хранят данные в нескольких независимых системах.
Например, если два розничных продавца объединяют свои предприятия, у них может быть несколько общих поставщиков, партнеров и потребителей. Кроме того, они могут иметь данные обо всех этих объектах в своих соответствующих хранилищах. Однако обе стороны могут использовать разные базы данных, и данные в них не всегда могут совпадать.
В таком сценарии две компании могут объединить свои базы данных в одну с помощью ETL-системы. Она, в свою очередь, удаляет дубликаты, стандартизирует форматы и синхронизирует данные.
- Перенос данных из устаревших систем
Другой вариант использования инструментов ETL — это когда компании переносят данные из устаревших систем в обновленную систему.
Во время миграции данных ETL-система помогает извлекать данные из разных источников, преобразовывать их в формат, совместимый с новой инфраструктурой, а затем загружать их в новую систему.
Например, в одной системе номера телефонов могут храниться в круглых скобках (в формате (111) 111-1111). В то время как в другой могут быть с дефисами (т.е. 111-111-1111). В этом случае ETL-система поможет убедиться, что все эти телефонные номера из обеих исходных систем имеют одинаковый формат, прежде чем сохранять их в целевой системе.
Лучшие инструменты ETL для интеграции данных
Мы составили список из четырех основных инструментов интеграции ETL, доступных на рынке, чтобы помочь вам выбрать тот, который соответствует потребностям вашего бизнеса.
- Astera Centerprise
Astera Centerprise — это мощный инструмент ETL, который консолидирует данные из множества систем. Он поддерживает управление данными с помощью ряда встроенных преобразований и помогает передавать данные в хранилище, причем полностью без кода, методом перетаскивания.
Hevo Data помогает предприятиям извлекать данные из многочисленных источников (таких как базы данных, поток событий и облачные приложения) в хранилище данных. Все происходит в режиме реального времени без какого-либо кодирования. Его легко настроить, и он изначально интегрируется с широким спектром источников данных.
Improvado — это надежный маркетинговый инструмент ETL, который позволяет вам подключить маркетинговый API к любой платформе визуализации, даже если у вас нет технических навыков. Он может соединяться к более чем 100 источникам данных, которые вы можете подключать и управлять через единую платформу в облаке или на месте.
Skyvia — это облачный инструмент, который не требует программирования для интеграции, резервного копирования, управления и доступа к данным. Он предлагает решение ETL для нескольких сценариев интеграции данных, поддерживая файлы CSV, базы данных, облачные хранилища данных и облачные приложения.
Заключение: процессы и примеры ETL
Чтобы получить значимую информацию, поддерживающую рост вашей компании, вам необходимо объединить все данные из нескольких разнородных источников в удобном формате. Здесь вам может помочь ETL-система.
ETL упрощает и расширяет процесс извлечения необработанных данных, рассредоточенных по многочисленным системам, в хранилище данных. Таким образом, выбор правильного инструмента ETL — очень важная часть аналитики данных любой компании.
Выбранный вами инструмент ETL должен интегрировать все источники данных, используемые вашим бизнесом. Он должен предлагать пользовательский интерфейс без ошибок и обеспечивать последовательную, точную и безопасную загрузку данных.