Oracle etl что это

Обновлено: 03.07.2024

ETL-разработчик — это одно из направлений работы в IT - сфере. Данный специалист занимается управлением хранилищами данных: он отвечает за консолидацию, федерализацию и обмен данными.

ETL происходит из английских слов : «to Extract», «to Transform», «to Load», то есть « И звлекать», « П реобразо вы вать», « З агружать».

ETL - разработчик в действии

  1. Нужно извлекать данные из внешних источников.
  2. Преобразовывать эти данные согласно имеющейся бизнес-модели.
  3. Загружать преобразованные данные в собственное хранилище данных.
  1. Часто источниками данных являются очень разносторонние системы хранилищ данных с разными форматами самих данных. А это влечет за собой знание различных процедур извлечения этих данных. Иногда бывает, что внутри одной информационной системы данные извлекаются разными путями.
  2. Данные с разных источников и разных форматов должны быть приведены в единый вид, который регламентируется собственными бизнес-правилами, единством применяемых систем кодирования, используемыми классификаторами и справочниками.
  3. При извлечении и преобразовании данных всегда нужно учитывать особенности компании, где работает ETL - разработчик, зачастую это добавляет дополнительные сложности в и т ак непростую работу.

ETL - разработка в действии

  1. Облачные хранилища. В данном случае ETL - разработчик работает с облачными хранилищами, которые удешевляют процесс хранения данных и позволяют легко масштабировать и защищать облачный продукт. Часто такой процесс завязан с каким-нибудь приложением.
  2. Базы данных. Работа с обычными базами данных, где ETL - developer — это тот , кто отвечает за перемещение данных в хранилища.
  3. Машинное обучение. При машинном обучении приходится часто работать с большими объемами данных. ETL применяется для миграции данных из разных источников в одно хранилище, которым пользуются для машинного обучения.
  4. Маркетинговые данные. Часто крупным компаниям необходимо провести маркетинговое исследование , и поэтому им необходимо переместить в одно хранилище данные из разных источников: веб-аналитика, со цс ети, данные о клиентах, о продажах и т. д.
  5. Данные от IoT. Если в системе IoT подключено очень много датчиков, собирающих данные, то часто для их анализа такие данные собираются в одном месте. За этот «сбор» данных отвечает ETL - разработчик.
  6. Синхронизация данных. К примеру , данные из обычных баз копируются в облачные хранилища. При обновлении данных в обычных хранилищах их нужно обновлять и в облаке. За этот процесс синхронизации тоже может отвечать ETL - разработчик.
  7. Аналитика бизнеса. Часто топ -менеджменту больших компаний нужно проводить анализ показателей собственного бизнеса. В больших бизнес-организациях очень много источников таких данных. За сбор информации в одном месте из разносторонних областей бизнеса отвечает ETL - разработка.

Системный инструмент ETL

  1. Cloud BigData.
  2. IBM InfoSphere.
  3. Power Center.
  4. MicrosoftSQLServer.
  5. Open Text.
  6. OracleGoldenGate.
  7. PervasiveDataIntegrator.
  8. PitneyBowes.
  9. SAPBusinessObjects.
  10. Sybase.
  11. И др.

Мы будем очень благодарны

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

ETL – аббревиатура от Extract, Transform, Load. Это системы корпоративного класса, которые применяются, чтобы привести к одним справочникам и загрузить в DWH и EPM данные из нескольких разных учетных систем.

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

Хочу оговориться, что описание архитектуры отражает мой личный опыт работы с ETL-инструментами и мое личное понимание «нормального» применения ETL – промежуточным слоем между OLTP системами и OLAP системой или корпоративным хранилищем.
Хотя в принципе существуют ETL, который можно поставить между любыми системами, лучше интеграцию между учетными системами решать связкой MDM и ESB. Если же вам для интеграции двух зависимых учетных систем необходим функционал ETL, то это ошибка проектирования, которую надо исправлять доработкой этих систем.

Зачем нужна ETL система

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

  1. Как случайные ошибки, возникшие на уровне ввода, переноса данных, или из-за багов;
  2. Как различия в справочниках и детализации данных между смежными ИТ-системами.
  1. Привести все данные к единой системе значений и детализации, попутно обеспечив их качество и надежность;
  2. Обеспечить аудиторский след при преобразовании (Transform) данных, чтобы после преобразования можно было понять, из каких именно исходных данных и сумм собралась каждая строчка преобразованных данных.

Как работает ETL система

Все основные функции ETL системы умещаются в следующий процесс:


В разрезе потока данных это несколько систем-источников (обычно OLTP) и система приемник (обычно OLAP), а так же пять стадий преобразования между ними:


  1. Процесс загрузки – Его задача затянуть в ETL данные произвольного качества для дальнейшей обработки, на этом этапе важно сверить суммы пришедших строк, если в исходной системе больше строк, чем в RawData то значит — загрузка прошла с ошибкой;
  2. Процесс валидации данных – на этом этапе данные последовательно проверяются на корректность и полноту, составляется отчет об ошибках для исправления;
  3. Процесс мэппинга данных с целевой моделью – на этом этапе к валидированной таблице пристраивается еще n-столбцов по количеству справочников целевой модели данных, а потом по таблицам мэппингов в каждой пристроенной ячейке, в каждой строке проставляются значения целевых справочников. Значения могут проставляться как 1:1, так и *:1, так и 1:* и *:*, для настройки последних двух вариантов используют формулы и скрипты мэппинга, реализованные в ETL-инструменте;
  4. Процесс агрегации данных – этот процесс нужен из-за разности детализации данных в OLTP и OLAP системах. OLAP-системы — это, по сути, полностью денормализованная таблица фактов и окружающие ее таблицы справочников (звездочка/снежинка), максимальная детализация сумм OLAP – это количество перестановок всех элементов всех справочников. А OLTP система может содержать несколько сумм для одного и того же набора элементов справочников. Можно было-бы убивать OLTP-детализацию еще на входе в ETL, но тогда мы потеряли бы «аудиторский след». Этот след нужен для построения Drill-down отчета, который показывает — из каких строк OLTP, сформировалась сумма в ячейке OLAP-системы. Поэтому сначала делается мэппинг на детализации OLTP, а потом в отдельной таблице данные «схлопывают» для загрузки в OLAP;
  5. Выгрузка в целевую систему — это технический процесс использования коннектора и передачи данных в целевую систему.

Особенности архитектуры

Реализация процессов 4 и 5 с точки зрения архитектуры тривиальна, все сложности имеют технический характер, а вот реализация процессов 1, 2 и 3 требует дополнительного пояснения.

Процесс загрузки

При проектировании процесса загрузки данных необходимо помнить о том что:

  1. Надо учитывать требования бизнеса по длительности всего процесса. Например: Если данные должны быть загружены в течение недели с момента готовности в исходных системах, и происходит 40 итераций загрузки до получения нормального качества, то длительность загрузки пакета не может быть больше 1-го часа. (При этом если в среднем происходит не более 40 загрузок, то процесс загрузки не может быть больше 30 минут, потому что в половине случаев будет больше 40 итераций, ну или точнее надо считать вероятности:) ) Главное если вы не укладываетесь в свой расчет, то не надейтесь на чудо — сносите и все, делать заново т.к. вы не впишитесь;
  2. Данные могут загружаться набегающей волной – с последовательным обновлением данных одного и того-же периода в будущем в течение нескольких последовательных периодов. (например: обновление прогноза окончания года каждый месяц). Поэтому кроме справочника «Период», должен быть предусмотрен технический справочник «Период загрузки», который позволит изолировать процессы загрузки данных в разных периодах и не потерять историю изменения цифр;
  3. Данные имеют обыкновение быть перегружаемыми много раз, и хорошо если будет технический справочник «Версия» как минимум с двумя элементами «Рабочая» и «Финальная», для отделения вычищенных данных. Кроме-того создание персональных версий, одной суммарной и одной финальной позволяет хорошо контролировать загрузку в несколько потоков;
  4. Данные всегда содержат ошибки: Перезагружать весь пакет в [50GB -> +8] это очень не экономно по ресурсам и вы, скорее всего, не впишитесь в регламент, следовательно, надо грамотно делить загружаемый пакет файлов и так проектировать систему, чтобы она позволяла обновлять пакет по маленьким частям. По моему опыту лучший способ – техническая аналитика «файл-источник», и интерфейс, который позволяет снести все данные только из одного файла, и вставить вместо него обновленные. А сам пакет разумно делить на файлы по количеству исполнителей, ответственных за их заполнение (либо админы систем готовящие выгрузки, либо пользователи заполняющие вручную);
  5. При проектировании разделения пакета на части надо еще учитывать возможность так-называемого «обогащения» данных (например: Когда 12 января считают налоги прошлого года по правилам управленческого учета, а в марте-апреле перегружают суммы на посчитанные по бухгалтерскому), это решается с одной стороны правильным проектированием деления пакета данных на части так, чтобы для обогащения надо было перегрузить целое количество файлов (не 2,345 файла), а с другой стороны введением еще одного технического справочника с периодами обогащения, чтобы не потерять историю изменений по этим причинам).
Процесс валидации

Ближе к практике в каждом из передаваемых типов данных в 95% случаев возможны следующие ошибки:

  1. Не из списка разрешенных значений
  2. Отсутствие обязательных значений
  3. Не соответствие формату (Все договора должны нумероваться «ДГВxxxx..»)
  1. Не из списка разрешенных значений для связанного элемента
  2. Отсутствие обязательных элементов для связанного элемента
  3. Не соответствие формату для связанного элемента(например: для продукта «АИС» все договора должны нумероваться «АИСxxxx..»)
  1. Символы допустимые в одном формате, недопустимы в другом
  2. Кодировка
  3. Обратная совместимость (Элемент справочника был изменен в целевой системе без добавления мэппинга)
  4. Новые значения (нет мэппинга)
  5. Устаревшие значения (не из списка разрешенных в целевой системе)
  1. Не число
  2. Не в границах разрешенного интервала значений
  3. Пропущено порядковое значение (например: данные не дошли)
  1. Не выполняется отношение y=ax+b (например: НДС и Выручка, или Встречные суммы равны)
  2. Элементу «А» присвоен неправильный порядковый номер
  3. Разницы за счет разных правил округления значений (например: в 1С и SAP никогда не сходится рассчитанный НДС)
  1. Переполнение
  2. Потеря точности и знаков
  3. Несовместимость форматов при конвертации в не число
  1. День недели не соответствует дате
  2. Сумма единиц времени не соответствует из-за разницы рабочие/не рабочие/праздничные/сокращенные дни
  1. Несовместимость формата даты при передаче текстом (например: ISO 8601 в UnixTime, или разные форматы в ISO 8601)
  2. Ошибка точки отсчета и точности при передаче числом (например: TimeStamp в DateTime)

Соответственно проверки на ошибки реализуются либо формулами, либо скриптами в редакторе конкретного ETL-инструмента.
А если вообще по большому счету, то большая часть ваших валидаций будет на соответствие справочников, а это [select * from a where a.field not in (select…) ]
При этом для сохранения аудиторского следа разумно сохранять в системе две отдельные таблицы – rawdata и cleandata с поддержкой связи 1:1 между строками.

Процесс мэппинга

Процесс мэппинга так же реализуется с помощью соответствующих формул и скриптов, есть три хороших правила при его проектировании:

    Таблица замэпленных данных должна включать одновременно два набора полей – старых и новых аналитик, чтобы можно был сделать select по исходным аналитикам и посмотреть, какие целевые аналитики им присвоены, и наоборот:


Заключение

В принципе это все архитектурные приемы, которые мне понравились в тех ETL инструментах, которыми я пользовался.

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

Ниже приведен отобранный список инструментов ETL с их популярными функциями и ссылками на веб-сайты. Список содержит как открытое (бесплатное), так и коммерческое (платное) программное обеспечение.

1) Клей AWS


Особенности:

  • Автоматическое обнаружение схемы
  • Этот инструмент ETL автоматически генерирует код для извлечения, преобразования и загрузки ваших данных.
  • Задания AWS Glue позволяют запускать по расписанию, по запросу или на основе определенного события.

2) Алуома


Особенности:

  • Обеспечить современный подход к миграции данных
  • Инфраструктура Alooma соответствует вашим потребностям.
  • Это поможет вам решить ваши проблемы с конвейером данных.
  • Создавайте коллажи для анализа транзакционных или пользовательских данных с любым другим источником данных.
  • Объедините хранилища данных в одном месте, независимо от того, находятся они в облаке или локально.
  • Легко помогает захватить все взаимодействия.

3) Стежка


Особенности:

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

4) Fivetran


Особенности:

  • Помогает создавать надежные автоматизированные конвейеры со стандартизированными схемами.
  • Добавление новых источников данных так быстро, как вам нужно
  • Не требуется обучение или пользовательское кодирование
  • Поддержка BigQuery, Snowflake, Azure, Redshift и т. Д.
  • Доступ ко всем вашим данным в SQL
  • Полная репликация по умолчанию

5) Матиллион


Особенности:

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

6) Streamsets

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


Особенности:

  • Превратите большие данные в идеи всей организации с помощью Apache Spark.
  • Позволяет выполнять массовую обработку ETL и машинного обучения без необходимости использования языка Scala или Python.
  • Работайте быстро с единым интерфейсом, который позволяет проектировать, тестировать и развертывать приложения Spark
  • Он предлагает лучшую видимость исполнения Spark с помощью дрейфа и обработки ошибок.

7) Таленд


Особенности:

  • Поддерживает обширные преобразования интеграции данных и сложные рабочие процессы
  • Обеспечивает бесперебойную связь для более чем 900 различных баз данных, файлов и приложений.
  • Он может управлять проектированием, созданием, тестированием, развертыванием и т. Д. Интеграционных процессов.
  • Синхронизировать метаданные между платформами баз данных
  • Инструменты управления и мониторинга для развертывания и контроля работ

8) Информатика PowerCenter


Особенности:

  • Он имеет централизованную систему регистрации ошибок, которая облегчает регистрацию ошибок и отклонение данных в реляционные таблицы.
  • Встроенный интеллект для повышения производительности
  • Ограничить журнал сеанса
  • Возможность расширения интеграции данных
  • Фонд модернизации архитектуры данных
  • Лучшие проекты с применением передовых методов разработки кода
  • Интеграция кода с внешними инструментами настройки программного обеспечения
  • Синхронизация среди географически распределенных членов команды.

9) Блендо

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

Oracle Data Integration, Cloud, Spatial and Analytics (GoldenGate, ODI, Cloud, Spatial, Exadata)

Поскольку многие компании вложились в Oracle Warehouse Builder, было принято решение не бросать его разработку, а произвести постепенное слияние продуктов. Об этом достаточно подробно написано в блоге Андрея Пивоварова.

E-LT и ETL. Меняется ли смысл от перестановки букв?

Об этих двух подходах написано много. Каждый из них имеет свои плюсы и минусы.

Подход ETL подразумевает порядок выгрузку данных в stage область, преобразование там этих данных, а затем загрузку данных в хранилище. Этот подход стар как мир и имеет большое количество инструментов, которые его поддерживают. Разработка концепции ETL подразумевала, что ни источники данных, ни хранилище данных неспособные самостоятельно преобразовать данные к нужному виду.

Подход E-LT также основан на том, что данные сначала загружаются stage область, но в отличие от предыдущего подхода хранилище данных и stage область совмещены. Т.е. мы имеем в хранилище сырые данные, которые затем могут быть трансформированы в любой нужный нам вид и сохранены в том же хранилище.

E-LT подход хорош тем, что в хранилище данных мы можем отследить, откуда появились данные, т.е. имеем как преобразованные (агрегированные, отобранные, трансформированные) данные, так и исходные. Кроме того, мы можем допускать пользователя к исходным данные с минимальной агрегацией, что дает нам возможность строить операционные витрины данных и операционный BI. Делается это за счет применения CDC технологий (GoldenGate, Streams, triggers). Подробнее о построении операционных BI можно посмотреть в моей презентации.

Если краткое, то отличие операционного BI от стратегического можно выразить следующей таблицей:

Если подвести итог, то у E-LT подхода есть следующие плюсы:

  • совмещение промежуточной stage области и хранилища не замедляет передачу данных для анализа, давая проводить анализ в реальном времени
  • для получения доступа к нижнему уровню детализации не требует подключения к исходным системам
  • E-LT системы позволяют использовать знания администраторов, т.е. не требуется изучать отдельный сложный продукт, который управляет stage областью.

Наличие плюсов, подразумевает наличие минусов:

  • E-LT подход относительно нов и не все ETL инструменты поддерживают его;
  • E-LT подход опирается на функциональность СУБД, поэтому не является независимым и самодостаточным.

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

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

Архитектура ODI

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

image

image

Комбинация этих модулей и позволяет задать процесс переноса данных в хранилище.

Заключение

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

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

В следующих постах я более подробно расскажу об использовании Oracle Data Inetegrator для построения ETL-решений.


Сложные ETL-процессы, как правило, разбиваются на цепочку более простых.

Рассмотрим частный случай импорта данных из внешнего файла. Например, из Excel или csv.

Образец файла с исходными данными (протокол проведенных соревнований по плаванию):


Модель данных в целевой БД:


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

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

Архитектура ETL (мы концентрируемся на ETL 1):


Для определения возможной схемы стейджинговой таблицы проведем первичный data profiling, определив:

  • метрики исходных данных (список атрибутов, их типы, длину строковых полей, null/not null, потенциальный ключ, степень соответствия целевым атрибутам и пр);
  • аномалии (грязные или отсутствующие данные, множественные значения и пр);
  • возможный способ осуществления парсинга.

Требования к Staging-ETL (v1.0):


Глядя на исходные данные, приходим к выводу что в них присутствуют множественные значения. Мы не будем готовы загрузить такие данные в целевые таблицы (например, строки Фамилия+Имя, Клуб+Город, Группа+Длина дистанции+Стиль плавания).

Нужно детальное сравнение исходных данных с целевыми атрибутами и уточнение требований.

Уточненные требования:


Создадим рабочую таблицу для загрузки “сырых” исходных данных.

Фактически, мы будем создавать ELT (Extract-Load-Transform) а не ETL (Extract-Transform-Load) код. Другими словами, все трансформации и очистку данных мы будем делать ПОСЛЕ загрузки сырых данных в БД.

Будем полагать исходные данные находятся в текстовом файле формата csv.
Если, изначально данные находятся в Excel, конверсию в csv можно предварительно сделать программно или средствами самого Excel.

Грузим данные в рабочую таблицу:

Commands completed successfully.
(1470 rows affected)
(5 rows affected)


Альтернативно, ту же задачу можно было сделать с помощью мастера импорта данных или с помощью

Попробуем решить задачу одним запросом SELECT, разбив его на ряд CTE-модулей

(30 rows affected)


Внимательно проанализировав полученный результат, мы находим признак грязных данных (Будник Виктория в названии группы).


Нужна доработка кода выше под эту особенность и строго продуманный подход к тому, как мы будем парсить исходные данные. Попытка выработать этот самый строгий подход дана в размеченном скриншоте ниже.


(30 rows affected)


Определение списка соревновательных дней:

(2 rows affected)


Определение места проведения соревнования:


(100 rows affected)


Мы приблизились к выполнению требований.

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

Попробуем разбить Фамилию Имя:


Проверим корректно ли происходит разбиение на всем множестве записей (у всех таких записей значение в колонке F1 не пусто):

(324 rows affected)

(106 rows affected)



Время на data profiling было потрачено не зря! Порой имя спортсмена содержит символы (в/к), что, вероятнее всего, означает что он участвовал в соревновании вне конкурса.


Для того чтобы загрузить лишь имя спортсмена, в поле first_name нужно взять первое слово (в случае если их несколько):

(99 rows affected)


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

Заметим, помимо прочего, что у всех атрибутов могут быть пробелы как До, так и После их значения. Для их удаления будем применять ltrim(rtrim(value)).

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

Еще одна проблема – результаты заплывов (время). Строки не находятся в едином формате и, соответственно, не конвертируются в тип time(2).

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

Будем разбивать значения этого исходного поля на два новых: результат-время и результат-дисквалификация. При этом само поле результат-время разложим на компоненты hh-mm-ss-ms с намерением впоследствии применить функцию timefromparts(hh, mm, ss, ms, precision) и тем самым сконвертировать результат из типа varchar к типу time(2).

(12 rows affected)


Теперь соберем все вместе.

Окончательный вариант:

(2 rows affected)


Используя язык SQL, мы создали основу ELT-процесса парсинга полуструктурированных данных.

Заметим, что код выше не является идеальным. Это скорее R&D-решение, полученное дата-аналитиком в процессе исследования исходных данных. Вопрос оптимизации будет решен дата-инженером во время создания полноценного ETL-процесса.

* Материалы, использованные в статье (код, исходный Excel-файл, результаты работы парсера, выгруженные в csv-файл) можно получить по этой ссылке.

Автор материала – Тимофей Гавриленко, преподаватель Тренинг-центра ISsoft.


Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.

Microsoft Certified Professional (70-464, 70-465).

Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.

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

Читайте также: