Etl сервис что это
Чернобровов Алексей Аналитик
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].
ETL и ELT: разница в том, как…
В течение последних нескольких десятилетий ETL (извлечение, преобразование, загрузка) был традиционным подходом, который использовался в хранилищах данных и аналитике. Подход ELT (извлечение, загрузка, преобразование) меняет старую парадигму. Но что на самом деле происходит, когда меняются местами буквы «T» и «L»?
ETL и ELT решают одну и ту же задачу:
Компаниям необходимо собирать, обрабатывать и анализировать гигабайты данных и событий. Данные должны быть чистыми, управляемыми и готовыми к анализу. Их нужно обогатить, формировать и трансформировать, прежде чем они станут значимыми.
Но то «как» это сделано в этих подходах отличается. Новый подход открывает новые возможности во многих современных проектах обработки данных. Есть определенные различия в том, как обрабатываются необработанные данные, когда выполняется обработка и как анализ.
В этой статье мы покажем технологические различия ETL и ELT, покажем примеры инженерии данных и анализа двух подходов и рассмотрим 10 плюсов и минусов ETL и ELT.
Технологические различия: давайте сначала разберем три ключевых этапа E, T, L:
ETL и ELT: что такое ETL?
ETL требует управления необработанными данными, включая извлечение необходимой информации и выполнение правильных преобразований, чтобы в конечном итоге удовлетворить потребности бизнеса. Каждый этап: извлечение, преобразование и загрузка, требует взаимодействия инженеров и разработчиков данных и работы с ограничениями емкости традиционных хранилищ данных. Используя ETL, аналитики и другие пользователи BI привыкли ждать, поскольку простой доступ к информации невозможен до тех пор, пока не будет завершен весь процесс ETL.
Что такое ELT?
При ELT после завершения извлечения данных вы сразу же начинаете этап загрузки – перемещение всех источников данных в единое централизованное хранилище данных. Благодаря сегодняшним инфраструктурным технологиям, в которых используются облака, системы могут поддерживать большие хранилища и масштабируемые вычисления. Следовательно, большой, расширяющийся пул данных и быстрая обработка практически бесконечны для сохранения всех извлеченных необработанных данных.
Таким образом, подход ELT представляет собой современную альтернативу ETL. Однако он все еще на этапе развития. Поэтому структуры и инструменты для поддержки процесса ELT не всегда подходят для облегчения загрузки и обработки больших объемов данных. Потенциал роста очень многообещающий – предоставление неограниченного доступа ко всем вашим данным в любое время и экономия усилий и времени разработчиков для пользователей и аналитиков BI.
Практический пример
Вот пример, который показывает технологические различия между ETL и ELT, он поможет нам вникнуть в детали.
В нашей демо-версии будут использоваться две таблицы данных: одна для покупок, а другая для валют, как показано ниже:
ТАБЛИЦА ПОКУПОК
Сумма
валюта
ТАБЛИЦА ВАЛЮТ
валюта
Курс
Чтобы разобрать основы, мы рассмотрим, как эти данные обрабатывается в ETL и ELT. Для каждого из них мы покажем, как рассчитать единую сводную таблицу с использованием этих двух таблиц, включая среднюю покупку в каждой стране (на основе предоставленного IP-адреса).
Преобразование ETL в извлеченных данных
В процессе ETL к ряду правил или функций для извлеченных данных и создания таблицы, которая будет загружена применяется этап преобразования.
Вот код, который показывает процесс предварительного преобразования данных для ETL:
Используя этот скрипт, мы сопоставляем IP-адреса с соответствующей страной. Мы выводим новое рассчитанное значение «сумма», умножая значения обеих исходных таблиц в группе на атрибут валюты. Затем мы сортируем данные по столбцу страны, объединяем данные из таблиц покупок и валют и суммируем средние значения по странам.
Это преобразование данных приводит к новой таблице со средней суммой по стране:
СРЕДНЯЯ СУММА ПО СТРАНЕ
страна
сумма
Преобразование данных ELT во время выполнения запроса
В отличие от ETL, в ELT все данные уже загружены и могут использоваться в любой момент времени.
Следовательно, преобразование выполняется во время выполнения запроса:
В запросе мы выбираем IP-адрес по стране, умножая сумму из таблицы покупок на курс из таблицы валют, чтобы вычислить среднюю сумму. Затем объединение обеих таблиц на основе общих столбцов обеих таблиц и группировка по странам.
Это приведет к той же самой выходной таблице, что и в описанном выше процессе ETL. Однако в этом случае, поскольку все необработанные данные уже загружены, нам будет проще продолжить выполнение других запросов в той же среде для тестирования и определения лучших возможных преобразований данных, соответствующих бизнес-требованиям.
Итог этого практического примера
В разработке кода ELT более эффективен, чем ETL. Кроме того, ELT более гибок, чем ETL. С помощью ELT пользователи могут запускать новые преобразования, тестировать и улучшать запросы непосредственно на необработанных данных по мере необходимости – без лишних времени и сложности, к которым мы привыкли с ETL.
Управление хранилищами данных и озерами данных
Согласно Gartner, потребности компаний в управлении данными и интеграции данных сегодня требуют как малых, так и больших, неструктурированных и структурированных объемов данных. Вот что они предлагают изменить в способе работы:
«Традиционная команда бизнес-аналитики должна продолжать разрабатывать четкие передовые практики с хорошо понятными бизнес-целями… существует второй режим бизнес-аналитики, который является более гибким и. очень итеративным, с непредвиденным обнаружением данных, допускающим быстрый сбой».
Такие мысли вызвали много разговоров о хранилищах и озерах данных. Концепция озера данных – это новый взгляд на большие объемы неструктурированных данных, предназначенный для бесконечного масштабирования с использованием таких инструментов, как Hadoop, для реализации второго режима работы бизнес-аналитики, описанного Gartner. Хотя компании по-прежнему используют хранилища данных для поддержки традиционной парадигмы, такой как ETL, масштабируемые современные хранилища данных, такие как Redshift и BigQuery, могут использоваться для реализации современной парадигмы ELT со всеми присущими ей преимуществами, упомянутыми выше.
IBM рассказывает о 5 вещах, которые требуются для современных проектов на основе больших данных, о необходимости новых концепций данных, таких как озеро данных. Это «5 V»:
ETL по-прежнему хорошо подходит для работы с устаревшими хранилищами данных, при рассмотрении более мелких подмножеств и их перемещении в хранилище данных. Но трудно предоставить решение с ETL для «5 V», когда вы идете вниз по списку – как работать с объемами? Неструктурированными данными? Скорость? и т.п.
Подход ELT открывает возможности для работы в более гибкой итеративной среде бизнес-аналитики благодаря своей эффективности и гибкости. ELT позволяет реализовать множество концепций хранилищ данных и распространяется на концепции озера данных, что позволяет включать неструктурированные данные в свое решение бизнес-аналитики.
Подводя итоги: 10 плюсов и минусов ETL и ELT
Обобщая эти два подхода, мы сгруппировали различия по 10 критериям:
1. Время – Загрузка
ETL: использует промежуточную область и систему, дополнительное время для загрузки данных
ELT: все в одной системе, загрузка только один раз
2. Время – Преобразование
ETL: нужно подождать, особенно для больших объемов данных – по мере роста данных время преобразования увеличивается
ELT: все в одной системе, скорость не зависит от размера данных
3. Время – Обслуживание
ETL: высокий уровень обслуживания – выбор данных для загрузки и преобразования; необходимо сделать все снова, если данные удалены или вы хотите улучшить основное хранилище данных.
ELT: низкие эксплуатационные расходы – все данные всегда доступны
4. Сложность реализации
ETL: на ранней стадии требует меньше места, и результат будет чистый
ELT: требует глубоких знаний инструментов и экспертного проектирования основного большого хранилища.
5. Анализ и стиль обработки
ETL: основан на нескольких сценариях для создания представлений – удаление представления означает удаление данных
ELT: создание специальных представлений – низкие затраты на создание и обслуживание
6. Ограничение данных или ограничение на поставку
ETL: предполагая и выбирая данные априори
ELT: По HW (нет) и политике хранения данных
7. Поддержка хранилищ данных
ETL: преобладающая устаревшая модель, используемая для локальных и реляционных структурированных данных.
ELT: адаптировано для использования в масштабируемой облачной инфраструктуре для поддержки структурированных и неструктурированных источников больших данных.
8. Поддержка озера данных
ETL: не является частью подхода
ELT: позволяет использовать озеро с поддержкой неструктурированных данных
9. Удобство использования
ETL: фиксированные таблицы, фиксированная временная шкала, используется в основном ИТ
ELT: ситуативность, гибкость, доступность для всех, от разработчика до гражданского интегратора
10. Рентабельность
ETL: нерентабельно для малого и среднего бизнеса
ELT: масштабируемость и доступность для бизнеса любого размера с использованием онлайн-решений SaaS
Заключительные мысли об ETL и ELT
ETL устарел. Он помог справиться с ограничениями традиционных жестких инфраструктур центров обработки данных, но сегодня это больше не является проблемой. В организациях с большими наборами данных, в масштабе нескольких терабайт, время загрузки может занять часы, в зависимости от сложности правил преобразования.
ELT – важная часть будущего хранилищ данных. С ELT компании любого размера могут извлечь выгоду из современных технологий. Анализируя большие пулы данных с большей гибкостью и меньшими затратами на обслуживание, компании получают ключевые идеи для создания реальных конкурентных преимуществ в своем бизнесе.