Что означает этап преобразование данных в etl процессе
Извлечение, преобразование и загрузка (ETL)
Распространенной проблемой, с которой сталкиваются организации, является сбор данных из нескольких источников в нескольких форматах. Затем необходимо переместить его в одно или несколько хранилищ данных. Назначение может отличаться от типа хранилища данных, в котором находится источник. Часто формат отличается, или данные должны быть преобразованы в форму или очищены перед их загрузкой в конечное место назначения.
За несколько лет для решения этих проблем было разработано много средств, служб и процессов. Независимо от используемого процесса, существует общая необходимость координировать работу и применить некоторый уровень преобразования данных в конвейере данных. В следующих разделах описываются распространенные методы, используемые для выполнения этих задач.
Извлечение, преобразование и загрузка (ETL)
Извлечение, преобразование и загрузка (ETL) — это конвейер данных, используемый для получения данных из различных источников. Затем данные преобразуются в соответствии с бизнес-правилами и загружаются в целевое хранилище данных. Преобразование, выполняемое в ETL, выполняется в специализированном подсистеме и часто включает использование промежуточных таблиц для временного хранения данных при их преобразовании и окончательной загрузке в назначение.
Обычно в процессе преобразования данных применяются различные операции (например, фильтрация, сортировка, агрегирование, объединение, очистка, дедупликация и проверка данных).
Часто три этапа ETL выполняются параллельно, чтобы сэкономить время. Например, при извлечении данных процесс преобразования может уже обрабатывать полученные данные и подготавливать их для загрузки, а процесс загрузки может начать обрабатывать подготовленные данные, не дожидаясь полного завершения извлечения.
Соответствующие службы Azure:
Извлечение, загрузка и преобразование (ELT)
Конвейер извлечения, загрузки и преобразования (ELT) отличается от ETL исключительно средой выполнения преобразования. В конвейере ELT преобразование происходит в целевом хранилище данных. В этом случае для преобразования данных вместо специальной подсистемы используются средства обработки целевого хранилища данных. Это упрощает архитектуру за счет удаления механизма преобразования из конвейера. Еще одним преимуществом этого подхода является то, что масштабирование целевого хранилища данных также улучшает производительность конвейера ELT. Тем не менее ELT работает надлежащим образом, только если целевая система имеет достаточную производительность для эффективного преобразования данных.
Обычно конвейер ELT применяется для обработки больших объемов данных. Например, вы можете начать с извлечения всех исходных данных в неструктурированные файлы в масштабируемом хранилище, например в распределенной файловой системе Hadoop, хранилище больших двоичных объектов Azure или Azure Data Lake Gen 2 (или сочетании). Такие технологии, как Spark, Hive или polybase, затем можно использовать для запроса исходных данных. Ключевой особенностью ELT является то, что хранилище данных, используемое для выполнения преобразования, — это то же хранилище, в котором данные в конечном счете потребляются. Это хранилище данных считывает данные непосредственно из масштабируемого хранилища, вместо того чтобы загружать их в собственное защищаемое хранилище. Этот подход пропускает шаг копирования данных, представленный в ETL, который часто может быть трудоемкой операцией для больших наборов данных.
Хранилище данных управляет только схемой данных и применяет ее при чтении. Например, кластер Hadoop, использующий Hive, описывает таблицу Hive, где источником данных является фактический путь к набору файлов в HDFS. В Azure синапсе Polybase может добиться того же результата — создав таблицу для данных, хранимых извне, в саму базу данных. Когда исходные данные загружены, данные, имеющиеся во внешних таблицах, можно обрабатывать, используя возможности хранилища данных. В сценариях с большими данными это означает, что хранилище данных должно иметь возможность массовой параллельной обработки (MPP), которая разбивает данные на небольшие фрагменты и распределяет обработку блоков на нескольких узлах параллельно.
Последний этап конвейера ELT обычно заключается в преобразовании исходных данных в окончательный формат, более эффективный для тех типов запросов, которые необходимо поддерживать. Например, данные могут быть секционированы. Кроме того, ELT может использовать оптимизированные форматы хранения, такие как Parquet, которые хранят данные, ориентированные на строки, в один столбец и обеспечивают оптимизированное индексирование.
Соответствующие службы Azure:
Поток данных и поток управления
В контексте конвейеров данных поток управления обеспечивает упорядоченную обработку набора задач. Для этого используется управление очередностью. Эти ограничения можно рассматривать как соединители на схеме рабочего процесса, показанной ниже. Каждая задача имеет результат (успешное завершение, сбой или завершение). Все последующие задачи начинают обработку данных, только когда предыдущая задача завершена с одним из этих результатов.
Потоки управления выполняют потоки данных в качестве задачи. В рамках задачи потока данных данные извлекаются из источника, преобразовываются и загружаются в хранилище данных. Выходные данные одной задачи потока данных могут быть входными данными для следующей задачи потока данных, а потоки данных могут выполняться параллельно. В отличие от потоков управления, вы не можете добавить ограничения между задачами в потоке данных. Однако вы можете добавить средство просмотра данных для наблюдения за данными по мере их обрабатывания каждой задачей.
На приведенной выше схеме показано несколько задач в потоке управления, одна из которых является задачей потока данных. Одна из задач вложена в контейнер. Контейнеры можно использовать для обеспечения структуры задач, тем самым формируя единицу работы. Одним из примеров является повторение элементов в коллекции (например, файлы в папке или инструкции базы данных).
Соответствующие службы Azure:
Выбор технологий
Дальнейшие действия
На следующей схеме эталонной архитектуры представлены сквозные конвейеры ELT в Azure:
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 компании любого размера могут извлечь выгоду из современных технологий. Анализируя большие пулы данных с большей гибкостью и меньшими затратами на обслуживание, компании получают ключевые идеи для создания реальных конкурентных преимуществ в своем бизнесе.
Преобразование данных в ETL
Этап ETL-процесса, следующий за извлечением, — преобразование данных. Его цель — подготовка данных к размещению в ХД и приведение их к виду, наиболее удобному для последующего анализа. При этом должны учитываться некоторые выдвигаемые аналитиком требования, в частности, к уровню качества данных. Поэтому в процессе преобразования может быть задействован самый разнообразный инструментарий, начиная от простейших средств ручного редактирования данных до систем, реализующих весьма сложные методы обработки и очистки данных.
Рис. 26. Процесс преобразования данных в ETL Рассмотрим каждую из этих операций более детально.
Преобразование структуры данных
Во многих случаях данные поступают в хранилище, интегрируясь из множества источников, которые создавались с помощью различных программных средств, методологий, соглашений, стандартов и т.д. Данные из таких источников могут отличаться своей структурной организацией: соглашениями о назначении имен полей и таблиц, порядком их описания, форматами, типами и кодировкой данных, например точностью представления числовых данных, используемыми разделителями целой и дробной частей, разделителями групп разрядов и т.д. Следовательно, во многих случаях извлеченные данные непригодны для непосредственной загрузки в ХД из-за отличия их структуры от структуры соответствующих целевых таблиц ХД.
При этом если таблицы фактов чаще всего соответствуют требованиям ХД, то таблицы измерений нуждаются в дополнительной обработке и, может быть, объединении.
Так, если в источнике, полученном из одного филиала, информация о покупателях хранится в поле Customer_Id, а в источнике, полученном из другого филиала, — в поле Clients_Name, то для создания одного измерения Покупатель придется решать задачу их объединения.
Дополнительная обработка структуры данных также требуется в ситуации, когда одно подразделение фирмы представляет информацию о цене и количестве проданных товаров, а другое — о количестве товаров и общей сумме продаж. В таком случае потребуется привести информацию о продажах, полученную из обоих источников, к общему виду.
Агрегирование данных
Как правило, в качестве источников данных для хранилищ выступают системы оперативной обработки данных (OLTP-системы), учетные системы, файлы различных СУБД, локальные файлы отдельных пользователей и т.д. Общим свойством всех этих источников является то, что они содержат данные с максимальной степенью детализации — сведения о ежедневных продажах или даже о каждом факте продажи в отдельности, об обслуживании каждого клиента и т.д. Распространено мнение, что такое детальное воспроизведение событий в исследуемом бизнес-процессе только пойдет на пользу, поскольку данные никогда не бывают лишними и чем больше их будет собрано, тем точнее окажутся результаты анализа.
Это не совсем так. Элементарные события, из которых состоит бизнес-процесс, например обслуживание одного клиента, выполнение одного заказа и т.д., которые также называют атомарными (то есть неделимыми), по своей сути являются случайными величинами, подверженными влиянию множества различных случайных факторов — от погоды до настроения клиента. Следовательно, информация о каждом отдельном событии в бизнес-процессе практически не имеет ценности. Действительно, на основании информации о продажах за один день нельзя сделать вывод обо всех особенностях торговли. Точно так же нельзя выработать стратегию работы с клиентами на основе исследования поведения одного клиента.
Иными словами, для достоверного описания предметной области использование данных с максимальным уровнем детализации не всегда целесообразно, поэтому наибольший интерес для анализа представляют данные, обобщенные по некоторому интервалу времени, по группе клиентов, товаров и т.д. Такие обобщенные данные называются агрегированными (иногда агрегатами), а сам процесс их вычисления — агрегированием.
В результате агрегирования большое количество записей о каждом событии в бизнес-процессе заменяется относительно небольшим количеством записей, содержащих агрегированные значения. Например, вместо информации о каждой из 365 ежедневных продаж в году в результате агрегирования будут храниться 52 записи с обобщением по неделям, 12 — по месяцам или 1 — за год. Если цель анализа — разработка прогноза продаж, то для краткосрочного оперативного прогноза достаточно использовать данные по неделям, а для долгосрочного стратегического прогноза — по месяцам или даже по годам.
Фактически при агрегировании производится объединение нескольких записей в одну с вычислением агрегированного значения на основе значений каждой записи. При вычислении агрегатов может быть использовано несколько способов. □ Среднее — для данных, расположенных в пределах интервала, в котором они обобщаются, вычисляется среднее значение. Затем все записи из данного интервала заменяются одной, содержащей их среднее значение (рис. 27).
Закономерен вопрос: нужно ли агрегировать все данные без разбору по всем возможным уровням обобщения или к этому следует подходить внимательно? Для ответа необходимо изучить наиболее вероятные направления использования данных в ХД. Однако если хранилище находится на стадии разработки и внедрения и методика его использования еще не до конца проработана, то сделать это трудно. Тем не менее, если опросить потенциальных пользователей ХД, что именно они хотят получить, возможно, некоторые сведения на этот счет удастся разыскать.
Из всех возможных вариантов агрегирования следует выбрать наиболее значимые с точки зрения планируемых направлений анализа, а от остальных отказаться. Очевидно, можно отказаться от агрегатов, которые имеют малое число подчиненных агрегированных значений (например, агрегирование ежемесячных продаж за квартал), поскольку их легко вычислить в процессе анализа. Или, наоборот, можно отказаться от агрегатов с максимальной степенью детализации (например, агрегирование ежедневных продаж). Второй вариант наиболее предпочтителен, поскольку на первый взгляд сулит существенную экономию, так как число дней в году умножается на число товаров и продавцов. Однако если данные о продажах являются разреженными, то есть не каждый товар продается ежедневно, то экономия может оказаться весьма незначительной.
Выбор нужных агрегатов всегда определяется особенностями бизнеса. При этом следует помнить, что агрегаты, требуемые для анализа, могут быть вычислены и непосредственно при выполнении аналитического запроса к ХД, хотя тем самым время его выполнения несколько увеличится. Подобный подход позволяет, например, отказаться от агрегирования редко используемых данных.
Таким образом, выбор правильной стратегии агрегирования данных в ETL — сложная и противоречивая задача. Увеличение числа агрегатов в ХД приводит к увеличению его размеров и сложности структуры данных. Снижение числа агрегатов в ХД может привести к необходимости их вычисления в процессе выполнения аналитических запросов, что увеличит время ожидания пользователя. Следовательно, необходимо обеспечить разумный компромисс между этими факторами. Существует и более простое правило, определяющее стратегию агрегирования: создавайте только те агрегаты, которые с большой долей вероятности понадобятся при анализе данных.
Перевод значений
Часто данные в источниках хранятся с использованием специальных кодировок, которые позволяют сократить избыточность данных и тем самым уменьшить объем памяти, требуемой для их хранения. Так, наименования объектов, их свойств и признаков могут храниться в сокращенном виде. В этом случае перед загрузкой данных в хранилище требуется выполнить перевод таких сокращенных значений в более полные и, соответственно, понятные.
Например, согласно заведенному в организации порядку идентификационный номер операции может быть закодирован в виде 06–04–12–62, где 06–04 — число и месяц, 12 — код товара, 62 — код региона. Такое представление позволяет хранить данные очень компактно. Однако для заполнения соответствующих измерений в многомерной модели запись необходимо декодировать.
Кроме того, часто возникает необходимость конвертировать числовые данные, например преобразовать вещественные в целые, уменьшить избыточную точность представления чисел, использовать экспоненциальный формат и т.д.
Создание новых данных
В процессе загрузки в ХД может понадобиться вычисление некоторых новых данных на основе существующих, что обычно сопровождается созданием новых полей. Например, OLTP-система содержит информацию только о количестве и цене проданного товара, а в целевой таблице ХД есть поле Сумма. Тогда в процессе преобразования необходимо вычислить сумму как произведение цены на количество проданных единиц товара. Таким образом, будет создано поле, содержащее новую информацию.
Еще одним возможным примером новых данных, создаваемых в процессе обработки, являются экономические, финансовые и другие показатели, которые могут быть вычислены на основе имеющихся данных. Так, на основе данных о продажах можно рассчитать рейтинг популярности товаров и создать новое поле, в котором для каждого товара будет указано соответствующее рейтинговое значение (например, по пятибалльной системе).
Создание новой информации на основе имеющихся данных тесно связано с таким важным процессом, как обогащение данных, которое может производиться (частично или полностью) на этапе преобразования данных в ETL. Агрегирование также может рассматриваться как создание новых данных.
Очистка данных
Сбор данных в процессе ETL производится из большого числа источников, многие из которых не содержат автоматических средств поддержки целостности, непротиворечивости и корректного представления данных. В связи с этим при переносе информации в ХД приходится сталкиваться с потоками «грязных» данных, которые могут стать причиной неправильных результатов анализа и даже сделать невозможным применение некоторых аналитических алгоритмов и методов. По этой причине в процессе ETL применяется очистка — процедура корректировки данных, которые в каком-либо смысле не удовлетворяют определенным критериям качества, то есть содержат нарушения структуры данных, противоречия, пропуски, дубликаты, неправильные форматы и т.д.
Некоторые проблемы в данных являются критичными и даже не позволяют выполнить загрузку данных в ХД (как правило, это нарушения структуры и некорректные форматы данных). Другие проблемы менее критичны, не мешают переносу данных в ХД, но не позволяют их корректно анализировать (пропуски, противоречия и дубликаты). Критичные проблемы в данных должны устраняться непосредственно в процессе ETL. Некритичные факторы, снижающие качество данных, могут устраняться как в процессе ETL, так и при подготовке их к анализу в аналитической системе.
Очистка данных — одна из наиболее важных и в то же время наиболее сложных и трудно поддающихся формализации задач ETL-процесса, поскольку набор факторов, снижающих качество данных, весьма разнообразен и может постоянно меняться. Поэтому очистке данных при разработке ETL-процессов уделяют большое внимание.
Выбор места для выполнения преобразований данных
Таким образом, все операции преобразования, которые могут потребоваться при переносе данных в ХД, обычно не сосредотачиваются на одном шаге ETL-процесса, а распределяются по различным этапам в зависимости от того, где выполнение преобразования более эффективно.