oracle etl что это
What is ETL?
Extract, transform, and load (ETL) is the process data-driven organizations use to gather data from multiple sources and then bring it together to support discovery, reporting, analysis, and decision-making.
The data sources can be very diverse in type, format, volume, and reliability, so the data needs to be processed to be useful when brought together. The target data stores may be databases, data warehouses, or data lakes, depending on the goals and technical implementation.
The three distinct steps of ETL
Extract
During extraction, ETL identifies the data and copies it from its sources, so it can transport the data to the target datastore. The data can come from structured and unstructured sources, including documents, emails, business applications, databases, equipment, sensors, third parties, and more.
Transform
Because the extracted data is raw in its original form, it needs to be mapped and transformed to prepare it for the eventual datastore. In the transformation process, ETL validates, authenticates, deduplicates, and/or aggregates the data in ways that make the resulting data reliable and queryable.
Load
ETL moves the transformed data into the target datastore. This step can entail the initial loading of all the source data, or it can be the loading of incremental changes in the source data. You can load the data in real time or in scheduled batches.
ELT or ETL: What’s the difference?
The transformation step is by far the most complex in the ETL process. ETL and ELT, therefore, differ on two main points:
In a traditional data warehouse, data is first extracted from «source systems» (ERP systems, CRM systems, etc.). OLAP tools and SQL queries depend on standardizing the dimensions of datasets to obtain aggregated results. This means that the data must undergo a series of transformations.
Traditionally, these transformations have been done before the data was loaded into the target system, typically a relational data warehouse.
However, as the underlying data storage and processing technologies that underpin data warehousing evolve, it has become possible to effect transformations within the target system. Both ETL and ELT processes involve staging areas. In ETL, these areas are found in the tool, whether it is proprietary or custom. They sit between the source system (for example, a CRM system) and the target system (the data warehouse).
In contrast, with ELTs, the staging area is in the data warehouse, and the database engine that powers the DBMS does the transformations, as opposed to an ETL tool. Therefore, one of the immediate consequences of ELTs is that you lose the data preparation and cleansing functions that ETL tools provide to aid in the data transformation process.
ETL and enterprise data warehouses
Traditionally, tools for ETL primarily were used to deliver data to enterprise data warehouses supporting business intelligence (BI) applications. Such data warehouses are designed to represent a reliable source of truth about all that is happening in an enterprise across all activities. The data in these warehouses is carefully structured with strict schemas, metadata, and rules that govern the data validation.
The ETL tools for enterprise data warehouses must meet data integration requirements, such as high-volume, high-performance batch loads; event-driven, trickle-feed integration processes; programmable transformations; and orchestrations so they can deal with the most demanding transformations and workflows and have connectors for the most diverse data sources.
After loading the data, you have multiple strategies for keeping it synchronized between the source and target datastores. You can reload the full dataset periodically, schedule periodic updates of the latest data, or commit to maintain full synchronicity between the source and the target data warehouse. Such real-time integration is referred to as change data capture (CDC). For this advanced process, the ETL tools need to understand the transaction semantics of the source databases and correctly transmit these transactions to the target data warehouse.
ETL and data marts
Data marts are smaller and more focused target datastores than enterprise data warehouses. They can, for instance, focus on information about a single department or a single product line. Because of that, the users of ETL tools for data marts are often line-of-business (LOB) specialists, data analysts, and/or data scientists.
ETL tools for data marts must be usable by business personnel and data managers, rather than by programmers and IT staff. Therefore, these tools should have a visual workflow to make it easy to set up ETL pipelines.
ETL or ELT and data lakes
Data lakes follow a different pattern than data warehouses and data marts. Data lakes generally store their data in object storage or Hadoop Distributed File Systems (HDFS), and therefore they can store less-structured data without schema; and they support multiple tools for querying that unstructured data.
One additional pattern this allows is extract, load, and transform (ELT), in which data is stored “as-is” first, and will be transformed, analyzed, and processed after the data is captured in the data lake. This pattern offers several benefits.
ETL tools for data lakes include visual data integration tools, because they are effective for data scientists and data engineers. Additional tools that are often used in data lake architecture include the following:
ETL use cases
The ETL process is fundamental for many industries because of its ability to ingest data quickly and reliably into data lakes for data science and analytics, while creating high-quality models. ETL solutions also can load and transform transactional data at scale to create an organized view from large data volumes. This enables businesses to visualize and forecast industry trends. Several industries rely on ETL to enable actionable insights, quick decision-making, and greater efficiency.
Financial services
Financial services institutions gather large amounts of structured and unstructured data to glean insights into consumer behavior. These insights can analyze risk, optimize banks’ financial services, improve online platforms, and even supply ATMs with cash.
Oil and gas
Oil and gas industries use ETL solutions to generate predictions about usage, storage, and trends in specific geographical areas. ETL works to gather as much information as possible from all the sensors of an extraction site and process that information to make it easy to read.
Automotive
ETL solutions can enable dealerships and manufacturers to understand sales patterns, calibrate their marketing campaigns, replenish inventory, and follow up on customer leads.
Telecommunications
With the unprecedented volume and variety of data being produced today, telecommunications providers rely on ETL solutions to better manage and understand that data. Once this data is processed and analyzed, businesses can use it to improve advertising, social media, SEO, customer satisfaction, profitability, and more.
Healthcare
With the need to reduce costs while also improving care, the healthcare industry employs ETL solutions to manage patient records, gather insurance information, and meet evolving regulatory requirements.
Life sciences
Clinical labs rely on ETL solutions and artificial intelligence (AI) to process various types of data being produced by research institutions. For example, collaborating on vaccine development requires huge amounts of data to be collected, processed, and analyzed.
Public sector
With Internet of Things (IoT) capabilities emerging so quickly, smart cities are using ETL and the power of AI to optimize traffic, monitor water quality, improve parking, and more.
ETL Products and Solutions
Service Oriented Architecture (SOA) Suite
How can you decrease the complexity of application integration? With simplified cloud, mobile, on-premises, and IoT integration capabilities—all within a single platform—this solution can deliver faster time to integration and increased productivity, along with a lower total cost of ownership (TCO). Many enterprise applications, including, Oracle E-Business Suite, heavily use this product to orchestrate dataflows.
GoldenGate
Digital transformation often demands moving data from where it’s captured to where it’s needed, and GoldenGate is designed to simplify this process. Oracle GoldenGate is a high-speed data replication solution for real-time integration between heterogeneous databases located on-premises, in the cloud, or in an autonomous database. GoldenGate improves data availability without affecting system performance, providing real-time data access and operational reporting.
Cloud Streaming
Our Cloud Streaming solution provides a fully managed, scalable, and durable solution for ingesting and consuming high-volume data streams in real time. Use this service for messaging, application logs, operational telemetry, web clickstream data, or any other instance in which data is produced and processed continually and sequentially in a publish-subscribe messaging model. It is fully compatible with Spark and Kafka.
Чернобровов Алексей Аналитик
ETL: что такое, зачем и для кого
В статье рассмотрено одно из ключевых BI-понятий (Business Intelligence) – ETL-технологии: определение, история возникновения, основные принципы работы, примеры реализации и типовые варианты использования (use cases). Также отмечены некоторые проблемы применения ETL и способы их решения с помощью программных инструментов обработки больших данных (Big Data).
Что такое ETL и зачем это нужно
Начнем с определения: ETL (Extract, Transform, Load) – это совокупность процессов управления хранилищами данных, включая [1]:
Понятие ETL возникло в результате появления множества корпоративных информационных систем, которые необходимо интегрировать друг с другом с целью унификации и анализа хранимых в них данных. Реляционная модель представления данных, подходящая для потребностей транзакционных систем, оказалась неэффективной для комплексной обработки и анализа информации. Поиск унифицированного решения привел к развитию хранилищ и витрин данных – самостоятельных систем хранения консолидированной информации в виде измерений и показателей, что считается оптимальным для формирования аналитических запросов [2].
Прикладное назначение ETL состоит в том, чтобы организовать такую структуру данных с помощью интеграции различных информационных систем. Учитывая, что BI-технологии позиционируются как «концепции и методы для улучшения принятия бизнес-решений с использованием систем на основе бизнес-данных» [3], можно сделать вывод о прямой принадлежность ETL к этому технологическому стеку.
Как устроена ETL-система: архитектура и принцип работы
Независимо от особенностей построения и функционирования ETL-система должна обеспечивать выполнение трех основных этапов процесса ETL-процесса (рис.1) [4]:
Рис. 1. Обобщенная структура процесса ETL
Таким образом, ETL-процесс представляет собой перемещение информации (потока данных) от источника к получателю через промежуточную область, содержащую вспомогательные таблицы, которые создаются временно и исключительно для организации процесса выгрузки (рис. 2) [1]. Требования к организации потока данных описывает аналитик. Поэтому ETL – это не только процесс переноса данных из одного приложения в другое, но и инструмент подготовки данных к анализу.
Рис. 2. Потоки данных между компонентами ETL
Для подобных запросов предназначены OLAP-системы. OLAP (Online Analytical Processing) – это интерактивная аналитическая обработка, подготовка суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу. При этом строится сложная структура данных – OLAP-куб, включающий таблицу фактов, по которым делаются ключевые запросы и таблицы агрегатов (измерений), показывающие, как могут анализироваться агрегированные данные. Например, группировка продуктов по городам, производителям, потребителям и другие сложные запросы, которые могут понадобиться аналитику. Куб потенциально содержит всю информацию, нужную для ответов на любые количественные и пространственно-временные вопросы. При огромном количестве агрегатов зачастую полный расчёт происходит только для некоторых измерений, для остальных же производится «по требованию» [6].
Таким образом, основные функции ETL-системы можно представить в виде последовательности операций по передаче данных из OLTP в OLAP (рис. 3) [7]:
Рис. 3. ETL-процесс по передаче данных от OLTP в OLAP
Немного про хранилища и витрины данных
ETL часто рассматривают как средство переноса данных из различных источников в централизованное КХД. Однако КХД не связано с решением какой-то конкретной аналитической задачи, его цель — обеспечивать надежный и быстрый доступ к данным, поддерживая их хронологию, целостность и непротиворечивость. Чтобы понять, каким образом КХД связаны с аналитическими задачами и ETL, для начала обратимся к определению.
Корпоративное хранилище данных (КХД, DWH – Data Warehouse) – это предметно-ориентированная информационная база данных, специально разработанная и предназначенная для подготовки отчётов и бизнес-анализа с целью поддержки принятия решений в организации. Информация в КХД, как правило, доступна только для чтения. Данные из OLTP-системы копируются в КХД таким образом, чтобы при построении отчётов и OLAP-анализе не использовались ресурсы транзакционной системы и не нарушалась её стабильность. Есть два варианта обновления данных в хранилище [8]:
ETL-процесс позволяет реализовать оба этих способа. Отметим основные принципы организации КХД [8]:
Витрина данных (Data Mart) представляет собой срез КХД в виде массива тематической, узконаправленной информации, ориентированного, например, на пользователей одной рабочей группы или департамента. Витрина данных, аналогично дэшборд-панели, позволяет аналитику увидеть агрегированную информацию в определенном временном или тематическом разрезе, а также сформировать и распечатать отчетные данные в виде шаблонизированного документа [9].
При проектировании хранилищ и витрин данных аналитику следует ориентироваться на возможности их прикладного использования и с учетом этого разрабатывать ETL-процессы. Например, если известно, что информация, поступающая из определенных подразделений, является самой важной и полезной, а также наиболее часто анализируется, то в регламент переноса данных в хранилище стоит внести соответствующие приоритеты. Это позволит ускорить работу с информацией, что особенно важно для data-driven организаций со сложной многоуровневой филиальной структурой и большим количеством подразделений [4].
Прикладные кейсы использования ETL-технологий
Рассмотрим пару типовых примеров использования ETL-систем [10].
Кейс 1. Прием нового сотрудника на работу, когда требуется завести учетную карточку во множестве корпоративных систем. В реальности в средних и крупных организациях этим занимаются специалисты разных подразделений, не скоординировав задачу между собой. Поэтому на практике часто возникают ситуации, когда принятый на работу сотрудник подолгу не может получить банковскую карту, потому что его учетная запись не была вовремя заведёна в бухгалтерии, а уже уволенные сотрудники имеют доступ к корпоративной почте и приложениям, т.к. их аккаунты не заблокированы в домене. ETL поможет быстро наладить взаимодействие между всеми корпоративными информационными системами.
Аналогичным образом ETL-технологии помогут автоматизировать удаление аккаунтов сотрудника из всех корпоративных систем в случае увольнения. В частности, как только в HR-систему попадут данные о дате окончания карьеры сотрудника на этом месте работы, информация о необходимости блокировки его записи поступит контроллеру домена, его корпоративная почта автоматически архивируется, а почтовый ящик блокируется. Также возможен полуавтоматический режим с созданием заявки на блокировку в службу технической поддержки, например, Help Desk.
Кейс 2. Разноска платежей, когда при взаимодействии со множеством контрагентов необходимо сопоставить информацию в виде платёжных документов, с деньгами, поступившими на расчетный счёт. В реальности это два независимых потока данных, которые сотрудники бухгалтерии или операционисты связывают вручную. Далеко не все корпоративные финансовые системы имеют функцию автоматической привязки платежей.
Итак, информация о платежах поступает от платежной сети в зашифрованном виде, т.к. содержит персональные данные. Вторым потоком данных являются файлы в формате DBF, содержащие информацию о банках-контрагентах, которая требуется для геолокации платежа. Наконец, с минимальной задержкой в три банковских дня, приходят деньги и выписка с платежами, проведёнными через банк-партнёр. Отметим, что в реальности прямой связи между всеми этими данными нет: номера документов, указанные в реестрах от платёжной системы и банка, не совпадают, а из-за особенностей работы банка дата платежа, которая значится в выписке, может не соответствовать дате реальной оплаты, которая содержится в зашифрованном файле реестра.
Расшифровку данных можно включить в ETL-процесс, в результате чего получится текстовый файл сложной структуры, содержащий ФИО, телефон, паспортные данные плательщика, сумму и дату платежа, а также дополнительные технические данных, идентифицирующие транзакцию. Это как раз позволит связать платёж с данными из банковской выписки. Данные из реестра обогащаются информацией о банках-контрагентах (филиалах, подразделениях, городах и адресах отделений), после этого осуществляются их соответствие (мэппинг) к конкретным полям таблиц корпоративных информационных систем и загрузка в КХД. Обогащение уже очищенных данных происходит в рамках реляционной модели с использованием внешних ключей.
После прихода банковской выписки запускается ещё один ETL-процесс, задача которого состоит в сопоставлении ранее полученной информации о платежах с реально пришедшими деньгами. Поскольку выписки приходят из банка в текстовом формате, первым шагом трансформации является разбор файла, затем идет процесс автоматической привязки платежей с использованием информации, ранее загруженной в корпоративную систему из реестров платежей и банков. В процессе привязки происходит сравнение не только ключей, идентифицирующих транзакцию, но и суммы и ФИО плательщика, а также отделения банка. Также решается задача исправления неверной даты платежа, указанной в банковской выписке, на реальную дату его совершения.
В результате нескольких ETL-процессов получилась система автоматической привязки платежей, при этом основные затраты были связаны с не с разработкой программного обеспечения, а с проектированием и изучением форматов файлов. В редких случаях ручной привязки обогащение данных с помощью ETL-технологии существенно облегчает эту процедуру. В частности, наличие телефонного номера плательщика позволяет уточнить данные о платеже лично у него, а геолокация платежа даёт информацию для аналитических отчётов и позволяет более эффективно отслеживать переводы от партнёров-брокеров (рис. 4).
Рис. 4. Организация разноски платежей с помощью ETL
Современный рынок ETL-систем и особенности выбора
Существует множество готовых ETL-систем, реализующих функции загрузки данных в КХД. Среди коммерческих решений наиболее популярными считаются следующие [11]:
К категории условно бесплатных можно отнести [11]:
При выборе готовой ETL-системы необходимо, в первую очередь, руководствоваться не бюджетом ее покупки или стоимостью использования, а следующими функциональными критериями:
Многие из современных промышленных решений представляют собой технологические платформы, позволяющие масштабировать ETL-процессы с поддержкой параллелизма выполнения операций, перераспределением нагрузки по обработке информации между источниками и самой системой, а также другими функциями в области интеграции данных. Поэтому выбор ETL-средства – это своего рода компромисс между конкретным проектным решением, текущими и будущими перспективами использования ETL-инструментария, а также стоимостью разработки и поддержания в актуальном состоянии всех используемых функций ETL-процесса [2].
Некоторые проблемы ETL-технологий и способы их решения
Как правило, ETL-системы самостоятельно справляются с проблемами подготовки данных к агрегированию и анализу, выполняя операции очистки данных. При этом устраняются проблемы качества данных: проверка на корректность форматов и типов, приведение значений к нужному диапазону, заполнение пропусков, удаление дубликатов, противоречий и нарушений структуры. Однако, кроме очистки данных, можно выделить еще пару трудоемких задач, которые не решаются автоматически:
значимость данных с точки зрения анализа; сложность получения данных из источников; возможное нарушение целостности и достоверности данных; объем данных в источнике.
На практике часто приходится искать компромисс между этими факторами. Например, данные могут представлять несомненную ценность для анализа, но сложность их извлечения или очистки может свести на нет все преимущества от использования [4].
Таким образом, Big Data инструменты пакетной и потоковой обработки позволяют дополнить типовые ETL-системы, предоставляя бизнес-пользователям более широкие возможности по работе с корпоративной информацией. Однако, в этом случае временные, трудовые и финансовые затраты на аналитику данных существенно возрастут, т.к. понадобятся дорогие специалисты: Data Engineer, который выстроит конвейер данных (pipeline), а также Data Scientist, который разработает программное приложение для онлайн-аналитики, включая оригинальные ML-алгоритмы. Впрочем, такие инвестиции будут оправданы, если предприятие достигло хотя бы 3-го уровня управленческой зрелости по модели CMMI, обладает большим количеством разных данных с высоким потенциалом для аналитики и стремится стать настоящей data-driven компанией. Однако, чтобы эти вложения принесли выгоду, а не превратились в пустые траты, стоит адекватно оценить свои потребности и возможности, возможно, с привлечением внешнего консультанта по аналитике данных.
Стоит отметить, что разработчики многих ETL-систем учитывают потребность аналитики больших данных с помощью своих продуктов и потому включают в их возможности работы с Apache Hadoop и Spark, как, например, Pentaho Business Analytics Platform [14]. В этом случае не придется самостоятельно разрабатывать средства интеграции ETL-системы с распределенными решениями сбора и обработки больших данных, а можно воспользоваться готовыми коннекторами и API-интерфейсами. Впрочем, это не отменяет необходимость предварительной аналитической работы по проектированию и реализации ETL-процесса. Организация сбора информации в хранилище данных может достигать до 80% трудозатрат по проекту. Учет различных аспектов ETL-процессов с прицелом на будущее позволит тщательно спланировать необходимые работы, избежать увеличения общего времени реализации и стоимости проекта, а также обеспечить BI-систему надежными и актуальными данными для анализа [2].
Oracle Data Integration, Cloud, Spatial and Analytics
Oracle Data Integration, Cloud, Spatial and Analytics (GoldenGate, ODI, Cloud, Spatial, Exadata)
Введение в Oracle Data Integrator
Oracle занимается вопросами интеграции данных достаточно давно, но долгое время такая интеграция обеспечивалась только в однородной среде, т.е. среде баз данных Oracle. Однако, реальность такова, что в компаниях обычно существуют не только Oracle, но и другие конкурирующие базы данных: MSSQL, DB2 и т.д. Даже если это не так, то и сама компания Oracle имеет не одну базу данных — например, MySQL, Berkley, TimesTen.
Ориентируясь на свою базу данных, Oracle разработал отличный инструмент — Oracle Warehouse Builder. Ключевым элементом использования этого инструмента было формирование PL/SQL процедур, которые осуществляли преобразование. Процедуры управлялись через графический интерфейс. Инструмент был бесплатным. Все бы хорошо, но увлекшись Oracle Warehouse Builder, Streams и гетерогенными сервисами, Oracle проспал рынок выделенных инструментов ETL инструментов. Вот почему в 2007 была приобретена компания Sunopsis, один из лидеров ETL рынка.
Oracle Data Integrator, продукт компании Sunopsis, был выбран в качестве основного продукта для интеграции данных. Он обладает всеми необходимыми качествами — высокоскоростной, гибкий, гетерогенный. Его идеология совпадает с идеологией ранее разработанных продуктов Oracle, да и сам продукт отлично укладывается в линейку ETL-инструментов.
Поскольку многие компании вложились в Oracle Warehouse Builder, было принято решение не бросать его разработку, а произвести постепенное слияние продуктов. Об этом достаточно подробно написано в блоге Андрея Пивоварова.
Обобщая, можно сказать, что Oracle Data Integrator — основной и приоритетный продукт. Именно он будет развиваться быстрыми темпами, именно он будет использовать в других продуктах Oracle для интеграции данных.
E-LT и ETL. Меняется ли смысл от перестановки букв?
Об этих двух подходах написано много. Каждый из них имеет свои плюсы и минусы.
Подход ETL подразумевает порядок выгрузку данных в stage область, преобразование там этих данных, а затем загрузку данных в хранилище. Этот подход стар как мир и имеет большое количество инструментов, которые его поддерживают. Разработка концепции ETL подразумевала, что ни источники данных, ни хранилище данных неспособные самостоятельно преобразовать данные к нужному виду.
Подход E-LT также основан на том, что данные сначала загружаются stage область, но в отличие от предыдущего подхода хранилище данных и stage область совмещены. Т.е. мы имеем в хранилище сырые данные, которые затем могут быть трансформированы в любой нужный нам вид и сохранены в том же хранилище.
E-LT подход хорош тем, что в хранилище данных мы можем отследить, откуда появились данные, т.е. имеем как преобразованные (агрегированные, отобранные, трансформированные) данные, так и исходные. Кроме того, мы можем допускать пользователя к исходным данные с минимальной агрегацией, что дает нам возможность строить операционные витрины данных и операционный BI. Делается это за счет применения CDC технологий (GoldenGate, Streams, triggers). Подробнее о построении операционных BI можно посмотреть в моей презентации.
Если краткое, то отличие операционного BI от стратегического можно выразить следующей таблицей:
Если подвести итог, то у E-LT подхода есть следующие плюсы:
Наличие плюсов, подразумевает наличие минусов:
Однако, если вдуматься в эти подходы можно увидеть и много общего. Более того, эти подходы в чистом виде в реальности встречаются не так часто.
Приведем пример. Допустим, нам нужно в хранилище данных получать таблицу агрегатов из 100 строк — по 1 строке для каждого офиса. Имеет ли смысл тащить таблицу с исходными данными объемом 100 млн. строк в stage область? Я думаю, что нет.
Вот почему, многие разработчики ETL средств пытаются научить свои средства работать с базой данных более эффективно нежели просто вытаскивать сырые данные.
Подход компании Oracle был изначально направлен именно на использование возможностей базы данных. По сути с приобретением компании Sunopsis у Oracle появился инструмент TETLT, т.е. инструмент, который трансформирует данные в том месте, где это наиболее эффективно.
Архитектура ODI
Для того, чтобы эффективно использовать ODI необходимо понимать архитектуру комплекса.
При репликации данных в Oracle Data Integrator используются так называемые Knowledge Module — набор компонентов. Каждый компонент отвечает за свою задачу.
Комбинация этих модулей и позволяет задать процесс переноса данных в хранилище.
Заключение
Итак, инструмент Oracle Data Integrator — это ETL-инструмент, позволяющий декларативно определять процесс выгрузки, преобразования и загрузки данных в хранилище.
Продукт изначально был разработан так, чтобы максимально задействовать средства базы данных, поэтому, в частности, он дает возможность обойтись без выделенного центра для ETL-преобразования.
Для ODI разработан целый ряд knowledge-модулей, которые позволяют извлекать и загружать данные практически в любую базу данных и информационную систему.
В следующих постах я более подробно расскажу об использовании Oracle Data Inetegrator для построения ETL-решений.