В вычислениях извлечение , преобразование, загрузка ( ETL ) — это трехэтапный процесс, в котором данные извлекаются из входного источника, преобразуются (включая очистку ) и загружаются в контейнер выходных данных. Данные могут быть сопоставлены из одного или нескольких источников, а также могут быть выведены в одно или несколько мест назначения. Обработка ETL обычно выполняется с помощью программных приложений , но системные операторы также могут выполнять ее вручную . Программное обеспечение ETL обычно автоматизирует весь процесс и может запускаться вручную или по повторяющимся расписаниям как в виде отдельных заданий, так и в виде пакета заданий.
Правильно спроектированная система ETL извлекает данные из исходных систем, обеспечивает соблюдение стандартов типа данных и достоверности данных, а также обеспечивает их структурное соответствие требованиям выходных данных. Некоторые системы ETL также могут доставлять данные в формате, готовом к представлению, чтобы разработчики приложений могли создавать приложения, а конечные пользователи могли принимать решения. [1]
Процесс ETL часто используется в хранилищах данных . [2] Системы ETL обычно интегрируют данные из нескольких приложений (систем), обычно разработанных и поддерживаемых разными поставщиками или размещенных на отдельном компьютерном оборудовании. Отдельные системы, содержащие исходные данные, часто управляются и эксплуатируются разными заинтересованными сторонами . Например, система учета затрат может объединять данные расчета заработной платы, продаж и закупок.
Извлечение данных включает извлечение данных из однородных или гетерогенных источников; преобразование данных обрабатывает данные путем очистки данных и преобразования их в правильный формат/структуру хранения для целей запроса и анализа; наконец, загрузка данных описывает вставку данных в конечную целевую базу данных, такую как хранилище операционных данных , витрина данных , озеро данных или хранилище данных. [3] [4]
Обработка ETL включает извлечение данных из исходной системы (систем). Во многих случаях это представляет собой наиболее важный аспект ETL, поскольку правильное извлечение данных создает основу для успеха последующих процессов. Большинство проектов хранилищ данных объединяют данные из разных исходных систем. Каждая отдельная система может также использовать различную организацию и/или формат данных . Общие форматы источников данных включают реляционные базы данных , базы данных с плоскими файлами , XML и JSON , но могут также включать нереляционные структуры баз данных, такие как IBM Information Management System или другие структуры данных, такие как метод доступа к виртуальному хранилищу (VSAM) или индексированный последовательный формат . Метод доступа (ISAM) или даже форматы, полученные из внешних источников с помощью таких средств, как веб-сканер или сбор данных . Потоковая передача извлеченного источника данных и оперативная загрузка в целевую базу данных — это еще один способ выполнения ETL, когда промежуточное хранилище данных не требуется.
Неотъемлемая часть извлечения включает проверку данных, чтобы подтвердить, имеют ли данные, извлеченные из источников, правильные/ожидаемые значения в данном домене (например, шаблон/по умолчанию или список значений). Если данные не соответствуют правилам проверки, они отклоняются полностью или частично. Отклоненные данные в идеале передаются обратно в исходную систему для дальнейшего анализа с целью выявления и исправления неверных записей или обработки данных .
На этапе преобразования данных к извлеченным данным применяется ряд правил или функций, чтобы подготовить их к загрузке в конечную цель.
Важной функцией преобразования является очистка данных , целью которой является передача цели только «правильных» данных. Проблема при взаимодействии различных систем заключается во взаимодействии и взаимодействии соответствующих систем. Наборы символов, которые могут быть доступны в одной системе, могут быть недоступны в других.
В других случаях для удовлетворения бизнес- и технических потребностей сервера или хранилища данных может потребоваться один или несколько из следующих типов преобразования:
На этапе загрузки данные загружаются в конечный целевой объект, которым может быть любое хранилище данных, включая простой плоский файл с разделителями или хранилище данных . В зависимости от требований организации этот процесс широко варьируется. Некоторые хранилища данных могут перезаписывать существующую информацию накопительной информацией; обновление извлеченных данных часто производится ежедневно, еженедельно или ежемесячно. Другие хранилища данных (или даже другие части того же хранилища данных) могут добавлять новые данные в исторической форме через определенные промежутки времени — например, ежечасно. Чтобы понять это, рассмотрим хранилище данных, необходимое для ведения учета продаж за последний год. Это хранилище данных перезаписывает любые данные старше года более новыми данными. Однако ввод данных за любой год производится историческим способом. Сроки и объем замены или добавления являются стратегическими решениями, зависящими от имеющегося времени и потребностей бизнеса . Более сложные системы могут вести историю и контрольный журнал всех изменений данных, загруженных в хранилище данных. Когда фаза загрузки взаимодействует с базой данных, применяются ограничения, определенные в схеме базы данных, а также в триггерах, активируемых при загрузке данных (например, уникальность, ссылочная целостность , обязательные поля), которые также способствуют повышению общего качества данных. процесса ETL.
Типичный реальный цикл ETL состоит из следующих этапов выполнения:
Процессы ETL могут быть весьма сложными, а при неправильно спроектированных системах ETL могут возникнуть серьезные эксплуатационные проблемы.
Диапазон значений данных или качество данных в операционной системе может превысить ожидания разработчиков во время указания правил проверки и преобразования. Профилирование источника данных во время анализа данных может выявить условия данных, которыми необходимо управлять с помощью спецификаций правил преобразования, что приводит к изменению правил проверки, явно или неявно реализованных в процессе ETL.
Хранилища данных обычно собираются из множества источников данных разного формата и назначения. Таким образом, ETL является ключевым процессом, позволяющим объединить все данные в стандартной однородной среде.
Анализ проектирования [5] должен установить масштабируемость системы ETL на протяжении всего срока ее использования, включая понимание объемов данных, которые должны быть обработаны в рамках соглашений об уровне обслуживания . Время, доступное для извлечения из исходных систем, может измениться, что может означать, что тот же объем данных придется обработать за меньшее время. Некоторым системам ETL приходится масштабироваться для обработки терабайтов данных для обновления хранилищ данных с десятками терабайт данных. Увеличение объемов данных может потребовать разработки, которая может масштабироваться от ежедневных пакетов до микропакетов на несколько дней, а также интеграции с очередями сообщений или сбора измененных данных в реальном времени для непрерывного преобразования и обновления.
Поставщики ETL тестируют свои системы записи со скоростью несколько ТБ (терабайт) в час (или ~ 1 ГБ в секунду), используя мощные серверы с несколькими процессорами, несколькими жесткими дисками, несколькими гигабитными сетевыми подключениями и большим объемом памяти.
В реальной жизни самая медленная часть процесса ETL обычно происходит на этапе загрузки базы данных. Базы данных могут работать медленно, поскольку им приходится заботиться о параллелизме, поддержании целостности и индексах. Таким образом, для повышения производительности может иметь смысл использовать:
Тем не менее, даже при использовании массовых операций доступ к базе данных обычно является узким местом в процессе ETL. Некоторые распространенные методы, используемые для повышения производительности:
nullзначениями, которые могут исказить разделение).disable constraint...) в таблицах целевой базы данных во время загрузки.disable trigger...) в таблицах целевой базы данных во время загрузки: смоделируйте их действие как отдельный шаг.drop index…… ; create index)Выполнение определенных операций в базе данных или за ее пределами может потребовать компромисса. Например, удаление дубликатов с помощью distinctбазы данных может быть медленным; таким образом, имеет смысл сделать это снаружи. С другой стороны, если использование distinctзначительно (x100) уменьшает количество извлекаемых строк, то имеет смысл как можно раньше удалить дубликаты в базе данных перед выгрузкой данных.
Распространенным источником проблем в ETL является большое количество зависимостей между заданиями ETL. Например, задание «Б» не может начаться, пока задание «А» не завершено. Обычно можно добиться большей производительности, визуализируя все процессы на графе и пытаясь уменьшить граф, максимально используя параллелизм и делая «цепочки» последовательной обработки как можно короче. Опять же, секционирование больших таблиц и их индексов может действительно помочь.
Другая распространенная проблема возникает, когда данные распределяются по нескольким базам данных и обработка выполняется в этих базах данных последовательно. Иногда репликация базы данных может использоваться как метод копирования данных между базами данных — это может существенно замедлить весь процесс. Общее решение — сократить граф обработки до трех слоев:
Такой подход позволяет при обработке максимально использовать преимущества параллелизма. Например, если вам нужно загрузить данные в две базы данных, вы можете запустить загрузку параллельно (вместо загрузки в первую — и последующей репликации во вторую).
Иногда обработка должна происходить последовательно. Например, размерные (справочные) данные необходимы, прежде чем можно будет получить и проверить строки для основных таблиц «фактов» .
Недавней [update]разработкой в программном обеспечении ETL является реализация параллельной обработки . Это позволило использовать ряд методов повышения общей производительности ETL при работе с большими объемами данных.
Приложения ETL реализуют три основных типа параллелизма:
Все три типа параллелизма обычно работают вместе в одном задании или задаче.
Дополнительная трудность связана с обеспечением относительной согласованности загружаемых данных. Поскольку несколько исходных баз данных могут иметь разные циклы обновления (некоторые могут обновляться каждые несколько минут, а другие могут занимать дни или недели), системе ETL может потребоваться удерживать определенные данные до тех пор, пока все источники не будут синхронизированы. Аналогичным образом, если склад необходимо сверить с содержимым в исходной системе или с главной книгой, становится необходимым создание точек синхронизации и сверки.
Процедуры хранения данных обычно разделяют большой процесс ETL на более мелкие части, выполняемые последовательно или параллельно. Чтобы отслеживать потоки данных, имеет смысл пометить каждую строку данных «row_id» и пометить каждую часть процесса «run_id». В случае сбоя наличие этих идентификаторов поможет выполнить откат и повторно запустить неудавшуюся часть.
Передовая практика также требует создания контрольных точек , которые представляют собой состояния, когда определенные этапы процесса завершены. На контрольной точке неплохо записать все на диск, очистить некоторые временные файлы, записать состояние и т. д.
С 2010 года [update]виртуализация данных начала продвигать обработку ETL. Применение виртуализации данных к ETL позволило решить наиболее распространенные ETL-задачи по миграции данных и интеграции приложений для нескольких рассредоточенных источников данных. Виртуальный ETL работает с абстрактным представлением объектов или сущностей, собранных из множества реляционных, полуструктурированных и неструктурированных источников данных. Инструменты ETL могут использовать объектно-ориентированное моделирование и работать с представлениями сущностей, постоянно хранящимися в централизованной звездообразной архитектуре. Такая коллекция, содержащая представления сущностей или объектов, собранных из источников данных для обработки ETL, называется репозиторием метаданных и может находиться в памяти или быть постоянной. Используя постоянный репозиторий метаданных, инструменты ETL могут переходить от одноразовых проектов к постоянному промежуточному программному обеспечению, выполняя гармонизацию и профилирование данных последовательно и практически в реальном времени.
Уникальные ключи играют важную роль во всех реляционных базах данных, поскольку они связывают все вместе. Уникальный ключ — это столбец, идентифицирующий данную сущность, тогда как внешний ключ — это столбец в другой таблице, который ссылается на первичный ключ. Ключи могут состоять из нескольких столбцов и в этом случае являются составными ключами. Во многих случаях первичный ключ представляет собой автоматически сгенерированное целое число, которое не имеет значения для представляемого бизнес-объекта , а существует исключительно для целей реляционной базы данных, обычно называемое суррогатным ключом .
Поскольку в хранилище обычно загружается более одного источника данных, ключи являются важной проблемой, которую необходимо решить. Например: клиенты могут быть представлены в нескольких источниках данных, причем их номер социального страхования является первичным ключом в одном источнике, номером телефона — в другом и суррогатным ключом — в третьем. Однако хранилище данных может потребовать консолидации всей информации о клиентах в одном измерении .
Рекомендуемый способ решения этой проблемы включает добавление суррогатного ключа хранилища, который используется в качестве внешнего ключа из таблицы фактов. [6]
Обычно происходят обновления исходных данных измерения, которые, очевидно, должны быть отражены в хранилище данных.
Если для создания отчетов требуется первичный ключ исходных данных, измерение уже содержит эту часть информации для каждой строки. Если исходные данные используют суррогатный ключ, хранилище должно отслеживать его, даже если он никогда не используется в запросах или отчетах; это делается путем создания таблицы поиска , содержащей суррогатный ключ хранилища и исходный ключ. [7] Таким образом, измерение не загрязняется суррогатами из различных исходных систем, при этом сохраняется возможность обновления.
Таблица поиска используется по-разному в зависимости от характера исходных данных. Есть 5 типов, которые следует учитывать; [7] сюда включены три:
Установленная структура ETL может улучшить возможности подключения и масштабируемости . [ нужна цитация ] Хороший инструмент ETL должен иметь возможность взаимодействовать со многими различными реляционными базами данных и читать различные форматы файлов, используемые в организации. Инструменты ETL начали мигрировать в системы интеграции корпоративных приложений или даже в системы корпоративной сервисной шины , которые теперь охватывают гораздо больше, чем просто извлечение, преобразование и загрузка данных. Многие поставщики ETL теперь имеют возможности профилирования данных , качества данных и метаданных . Распространенный вариант использования инструментов ETL включает преобразование файлов CSV в форматы, читаемые реляционными базами данных. Типичный перевод миллионов записей облегчается инструментами ETL, которые позволяют пользователям вводить потоки/файлы данных в формате CSV и импортировать их в базу данных с минимальным количеством кода.
Инструменты ETL обычно используются широким кругом специалистов — от студентов, изучающих информатику, стремящихся быстро импортировать большие наборы данных, до архитекторов баз данных, отвечающих за управление учетными записями компании. Инструменты ETL стали удобным инструментом, на который можно положиться для достижения максимальной производительности. . Инструменты ETL в большинстве случаев содержат графический интерфейс, который помогает пользователям удобно преобразовывать данные с помощью визуального преобразователя данных, а не писать большие программы для анализа файлов и изменения типов данных.
Хотя инструменты ETL традиционно предназначались для разработчиков и ИТ-персонала, исследовательская фирма Gartner пишет, что новая тенденция заключается в предоставлении этих возможностей бизнес-пользователям, чтобы они могли сами создавать соединения и интегрировать данные, когда это необходимо, вместо того, чтобы обращаться к ИТ-персоналу. [8] Gartner называет этих нетехнических пользователей гражданскими интеграторами. [9]
Извлечение, загрузка, преобразование (ELT) — это вариант ETL, при котором извлеченные данные сначала загружаются в целевую систему. [10] Архитектура аналитического конвейера также должна учитывать, где очищать и обогащать данные [10], а также как согласовывать измерения. [1] Некоторые из преимуществ процесса ELT включают скорость и возможность более простой обработки как неструктурированных, так и структурированных данных. [11]
Книга Ральфа Кимбалла и Джо Казерты «Инструментарий ETL для хранилища данных» (Wiley, 2004), которая используется в качестве учебника для курсов, обучающих процессам ETL в хранилищах данных, посвящена этой проблеме. [12]
Облачные хранилища данных, такие как Amazon Redshift , Google BigQuery , Microsoft Azure Synapse Analytics и Snowflake Inc., смогли обеспечить высокомасштабируемую вычислительную мощность. Это позволяет компаниям отказаться от преобразований предварительной загрузки и реплицировать необработанные данные в свои хранилища данных, где они могут преобразовывать их по мере необходимости с помощью SQL .
После использования ELT данные могут быть обработаны дальше и сохранены в витрине данных. [13]
Большинство инструментов интеграции данных ориентированы на ETL, тогда как ELT популярен в устройствах баз данных и хранилищ данных. Аналогичным образом можно выполнить TEL (преобразование, извлечение, загрузку), при котором данные сначала преобразуются в блокчейн (как способ записи изменений данных, например, сжигание токенов), а затем извлекаются и загружаются в другое хранилище данных. [14]