Извлечь, преобразовать, загрузить

Обычная диаграмма ETL

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

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

Процесс ETL часто используется в хранилищах данных . Системы ETL обычно интегрируют данные из нескольких приложений (систем), обычно разработанных и поддерживаемых разными поставщиками или размещенных на отдельном компьютерном оборудовании. Отдельные системы, содержащие исходные данные, часто управляются и эксплуатируются разными заинтересованными сторонами . Например, система учета затрат может объединять данные расчета заработной платы, продаж и закупок.

Извлечение данных включает извлечение данных из однородных или гетерогенных источников; преобразование данных обрабатывает данные путем очистки данных и преобразования их в правильный формат/структуру хранения для целей запроса и анализа; наконец, загрузка данных описывает вставку данных в конечную целевую базу данных, такую ​​как хранилище операционных данных , витрина данных , озеро данных или хранилище данных.

Извлекать

Обработка ETL включает извлечение данных из исходной системы (систем). Во многих случаях это представляет собой наиболее важный аспект ETL, поскольку правильное извлечение данных создает основу для успеха последующих процессов. Большинство проектов хранилищ данных объединяют данные из разных исходных систем. Каждая отдельная система может также использовать различную организацию и/или формат данных . Общие форматы источников данных включают реляционные базы данных , базы данных с плоскими файлами , XML и JSON , но могут также включать нереляционные структуры баз данных, такие как IBM Information Management System или другие структуры данных, такие как метод доступа к виртуальному хранилищу (VSAM) или индексированный последовательный формат . Метод доступа (ISAM) или даже форматы, полученные из внешних источников с помощью таких средств, как веб-сканер или сбор данных . Потоковая передача извлеченного источника данных и оперативная загрузка в целевую базу данных — это еще один способ выполнения ETL, когда промежуточное хранилище данных не требуется.

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

Трансформировать

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

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

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

• Выбор только определенных столбцов для загрузки: (или выбор нулевых столбцов для не загрузки). Например, если исходные данные имеют три столбца (так называемые «атрибуты»),roll_no, age и оклад, то в выборке могут быть толькоroll_no и оклад. Или механизм выбора может игнорировать все записи, в которых зарплата отсутствует (зарплата = ноль).
• Перевод кодированных значений: ( например , если исходная система кодирует мужчину как «1» и женщину как «2», но коды склада мужского пола как «M», а женщину как «F»)
• Кодирование значений в свободной форме: ( например , сопоставление «Мужчина» с «М»)
• Получение нового расчетного значения: ( например , sale_amount = qty * unit_price)
• Сортировка или упорядочивание данных на основе списка столбцов для повышения производительности поиска.
• Объединение данных из нескольких источников ( например , поиск, слияние) и дедупликация данных .
• Агрегирование (например, роллап — суммирование нескольких строк данных — общий объем продаж по каждому магазину, по каждому региону и т. д.)
• Генерация суррогатного ключа значений
• Транспонирование или поворот (преобразование нескольких столбцов в несколько строк или наоборот)
• Разделение столбца на несколько столбцов ( например , преобразование списка, разделенного запятыми , заданного в виде строки в одном столбце, в отдельные значения в разных столбцах)
• Дезагрегирование повторяющихся столбцов
• Поиск и проверка соответствующих данных из таблиц или ссылочных файлов.
• Применение любой формы проверки данных; неудачная проверка может привести к полному отклонению данных, частичному отклонению или отсутствию отклонения вообще, и, таким образом, никакие, некоторые или все данные не передаются на следующий шаг в зависимости от конструкции правила и обработки исключений; многие из вышеперечисленных преобразований могут привести к исключениям, например, когда трансляция кода анализирует неизвестный код в извлеченных данных.

Нагрузка

На этапе загрузки данные загружаются в конечный целевой объект, которым может быть любое хранилище данных, включая простой плоский файл с разделителями или хранилище данных . В зависимости от требований организации этот процесс широко варьируется. Некоторые хранилища данных могут перезаписывать существующую информацию накопительной информацией; обновление извлеченных данных часто производится ежедневно, еженедельно или ежемесячно. Другие хранилища данных (или даже другие части того же хранилища данных) могут добавлять новые данные в исторической форме через определенные промежутки времени — например, ежечасно. Чтобы понять это, рассмотрим хранилище данных, необходимое для ведения учета продаж за последний год. Это хранилище данных перезаписывает любые данные старше года более новыми данными. Однако ввод данных за любой год производится историческим способом. Сроки и объем замены или добавления являются стратегическими решениями, зависящими от имеющегося времени и потребностей бизнеса . Более сложные системы могут вести историю и контрольный журнал всех изменений данных, загруженных в хранилище данных. Когда фаза загрузки взаимодействует с базой данных, применяются ограничения, определенные в схеме базы данных, а также в триггерах, активируемых при загрузке данных (например, уникальность, ссылочная целостность , обязательные поля), которые также способствуют повышению общего качества данных процесса ETL.

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

Реальный цикл ETL

Типичный реальный цикл ETL состоит из следующих этапов выполнения:

1. Начало цикла
2. Создание справочных данных
3. Извлечение (из источников)
4. Подтвердить
5. Преобразование ( очистка , применение бизнес-правил , проверка целостности данных , создание агрегатов или дезагрегаций)
6. Стадия (загрузка в промежуточные таблицы, если они используются)
7. Отчеты аудита (например, о соблюдении бизнес-правил. Также в случае сбоя помогает провести диагностику/ремонт)
8. Публикация (для целевых таблиц)
9. Архив

Проблемы

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

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

Хранилища данных обычно собираются из множества источников данных разного формата и назначения. Таким образом, ETL является ключевым процессом, позволяющим объединить все данные в стандартной однородной среде.

Анализ дизайна следует обеспечить масштабируемость системы ETL на протяжении всего срока ее использования, включая понимание объемов данных, которые необходимо обрабатывать в рамках соглашений об уровне обслуживания . Время, доступное для извлечения из исходных систем, может измениться, что может означать, что тот же объем данных придется обработать за меньшее время. Некоторым системам ETL приходится масштабироваться для обработки терабайтов данных для обновления хранилищ данных с десятками терабайт данных. Увеличение объемов данных может потребовать разработки, которая может масштабироваться от ежедневных пакетов до микропакетов на несколько дней, а также интеграции с очередями сообщений или сбора измененных данных в реальном времени для непрерывного преобразования и обновления.

Производительность

Поставщики ETL тестируют свои системы записи со скоростью несколько ТБ (терабайт) в час (или ~ 1 ГБ в секунду), используя мощные серверы с несколькими процессорами, несколькими жесткими дисками, несколькими гигабитными сетевыми подключениями и большим объемом памяти.

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

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

Тем не менее, даже при использовании массовых операций доступ к базе данных обычно является узким местом в процессе ETL. Некоторые распространенные методы, используемые для повышения производительности:

• Таблицы разделов (и индексы): старайтесь, чтобы разделы были одинаковыми по размеру (следите за null значения, которые могут исказить разделение)
• Выполните всю проверку на уровне ETL перед загрузкой: отключите проверку целостности ( disable constraint ...) в таблицах целевой базы данных во время загрузки
• Отключить триггеры ( disable trigger ...) в таблицах целевой базы данных во время загрузки: смоделируйте их эффект как отдельный шаг
• Генерируйте идентификаторы на уровне ETL (не в базе данных).
• Удалите индексы (в таблице или разделе) перед загрузкой и воссоздайте их после загрузки (SQL: drop index ... ; create index ...)
• По возможности используйте параллельную массовую загрузку — хорошо работает, когда таблица секционирована или отсутствуют индексы (Примечание: попытка выполнить параллельную загрузку в одну и ту же таблицу (раздел) обычно приводит к блокировкам — если не строк данных, то индексов).
• Если существует потребность в вставке, обновлении или удалении, выясните, какие строки и каким образом следует обрабатывать на уровне ETL, а затем обработайте эти три операции в базе данных отдельно; вы часто можете выполнять массовую загрузку для вставок, но обновления и удаления обычно выполняются через API (с использованием SQL ).

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

Распространенным источником проблем в ETL является большое количество зависимостей между заданиями ETL. Например, задание «Б» не может начаться, пока задание «А» не завершено. Обычно можно добиться большей производительности, визуализируя все процессы на графе и пытаясь уменьшить граф, максимально используя параллелизм и делая «цепочки» последовательной обработки как можно короче. Опять же, секционирование больших таблиц и их индексов может действительно помочь.

Другая распространенная проблема возникает, когда данные распределяются по нескольким базам данных и обработка выполняется в этих базах данных последовательно. Иногда репликация базы данных может использоваться как метод копирования данных между базами данных — это может существенно замедлить весь процесс. Общее решение — сократить граф обработки до трех слоев:

• Источники
• Центральный уровень ETL
• Цели

Такой подход позволяет при обработке максимально использовать преимущества параллелизма. Например, если вам нужно загрузить данные в две базы данных, вы можете запустить загрузку параллельно (вместо загрузки в первую — и последующей репликации во вторую).

Иногда обработка должна происходить последовательно. Например, размерные (справочные) данные необходимы, прежде чем можно будет получить и проверить строки для основных таблиц «фактов» .

Параллельная обработка

Недавний Развитием программного обеспечения ETL является реализация параллельной обработки . Это позволило использовать ряд методов повышения общей производительности ETL при работе с большими объемами данных.

Приложения ETL реализуют три основных типа параллелизма:

• Данные: путем разделения одного последовательного файла на более мелкие файлы данных для обеспечения параллельного доступа.
• Конвейер : позволяет одновременно запускать несколько компонентов в одном потоке данных , например, поиск значения в записи 1 одновременно с добавлением двух полей в запись 2.
• Компонент: одновременный запуск нескольких процессов с разными потоками данных в одном задании, например, сортировка одного входного файла при удалении дубликатов в другом файле.

Все три типа параллелизма обычно работают вместе в одном задании или задаче.

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

Возможность повторного запуска, возможность восстановления

Процедуры хранения данных обычно разделяют большой процесс ETL на более мелкие части, выполняемые последовательно или параллельно. Чтобы отслеживать потоки данных, имеет смысл пометить каждую строку данных «row_id» и пометить каждую часть процесса «run_id». В случае сбоя наличие этих идентификаторов поможет выполнить откат и повторно запустить неудавшуюся часть.

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

Виртуальный ЭТЛ

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

Работа с ключами

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

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

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

Обычно происходят обновления исходных данных измерения, которые, очевидно, должны быть отражены в хранилище данных.

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

Таблица поиска используется по-разному в зависимости от характера исходных данных. Есть 5 типов, которые следует учитывать; сюда включены три:

Тип 1

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

Тип 2

Добавляется новая строка измерения с новым состоянием исходной системы; назначается новый суррогатный ключ; исходный ключ больше не уникален в справочной таблице

Полностью авторизован

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

Инструменты

Установленная структура ETL может улучшить возможности подключения и масштабируемости . Хороший инструмент ETL должен иметь возможность взаимодействовать со многими различными реляционными базами данных и читать различные форматы файлов, используемые в организации. Инструменты ETL начали мигрировать в системы интеграции корпоративных приложений или даже в системы корпоративной сервисной шины , которые теперь охватывают гораздо больше, чем просто извлечение, преобразование и загрузка данных. Многие поставщики ETL теперь имеют профилирования данных , качества данных и метаданных возможности . Распространенный вариант использования инструментов ETL включает преобразование файлов CSV в форматы, читаемые реляционными базами данных. Типичный перевод миллионов записей облегчается инструментами ETL, которые позволяют пользователям вводить потоки/файлы данных в формате CSV и импортировать их в базу данных с минимальным количеством кода.

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

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

ETL против ELT

Основная статья: Извлечение, загрузка, преобразование

Извлечение, загрузка, преобразование (ELT) — это вариант ETL, при котором извлеченные данные сначала загружаются в целевую систему. Архитектура аналитического конвейера также должна учитывать места очистки и обогащения данных. а также как согласовать размеры. Некоторые из преимуществ процесса ELT включают скорость и возможность более простой обработки как неструктурированных, так и структурированных данных.

Книга Ральфа Кимбалла и Джо Казерты «Инструментарий ETL для хранилища данных» (Wiley, 2004), которая используется в качестве учебника для курсов, обучающих процессам ETL в хранилищах данных, посвящена этой проблеме.

Облачные хранилища данных, такие как Amazon Redshift , Google BigQuery , Microsoft Azure Synapse Analytics и Snowflake Inc., смогли обеспечить высокомасштабируемую вычислительную мощность. Это позволяет компаниям отказаться от преобразований предварительной загрузки и реплицировать необработанные данные в свои хранилища данных, где они могут преобразовывать их по мере необходимости с помощью SQL .

После использования ELT данные могут быть обработаны дальше и сохранены в витрине данных.

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

См. также

• Архитектурный шаблон (эталовая архитектура EA)
• Конвейеры CMS
• Создание, чтение, обновление и удаление (CRUD)
• Очистка данных
• Интеграция данных
• Витрина данных
• Сетка данных — предметно-ориентированная архитектура данных.
• Миграция данных
• Преобразование данных (вычисления)
• Электронный обмен данными (EDI)
• Архитектура предприятия
• Юридический стандарт электронного обмена данными (LEDES)
• Обнаружение метаданных
• Онлайн-аналитическая обработка (OLAP)
• Онлайн-обработка транзакций (OLTP)
• Пространственный ETL