В вычислительной технике извлечение , преобразование, загрузка ( ETL ) — это трехфазный процесс, в котором данные извлекаются из входного источника, преобразуются (включая очистку ) и загружаются в выходной контейнер данных. Данные могут быть собраны из одного или нескольких источников, а также могут быть выведены в одно или несколько мест назначения. Обработка ETL обычно выполняется с помощью программных приложений , но также может выполняться вручную системными операторами. Программное обеспечение ETL обычно автоматизирует весь процесс и может запускаться вручную или по повторяющимся графикам либо как отдельные задания, либо как пакет заданий.
Правильно спроектированная система ETL извлекает данные из исходных систем и обеспечивает соблюдение стандартов типа данных и валидности данных, а также гарантирует, что они структурно соответствуют требованиям вывода. Некоторые системы ETL также могут предоставлять данные в формате, готовом к представлению, чтобы разработчики приложений могли создавать приложения, а конечные пользователи могли принимать решения. [1]
Процесс ETL часто используется в хранилищах данных . [2] Системы ETL обычно интегрируют данные из нескольких приложений (систем), обычно разработанных и поддерживаемых разными поставщиками или размещенных на отдельном компьютерном оборудовании. Отдельные системы, содержащие исходные данные, часто управляются и эксплуатируются разными заинтересованными сторонами . Например, система учета затрат может объединять данные из заработной платы, продаж и закупок.
Извлечение данных включает в себя извлечение данных из однородных или неоднородных источников; преобразование данных обрабатывает данные путем очистки данных и преобразования их в надлежащий формат/структуру хранения для целей запросов и анализа; наконец, загрузка данных описывает вставку данных в конечную целевую базу данных, такую как оперативное хранилище данных , витрина данных , озеро данных или хранилище данных. [3] [4]
Обработка ETL включает в себя извлечение данных из исходной системы(систем). Во многих случаях это представляет собой наиболее важный аспект ETL, поскольку правильное извлечение данных закладывает основу для успеха последующих процессов. Большинство проектов по хранению данных объединяют данные из разных исходных систем. Каждая отдельная система может также использовать различную организацию данных и/или формат . Распространенные форматы источников данных включают реляционные базы данных , базы данных с плоскими файлами , XML и JSON , но могут также включать нереляционные структуры баз данных, такие как IBM Information Management System или другие структуры данных, такие как Virtual Storage Access Method (VSAM) или Indexed Sequential Access Method (ISAM) , или даже форматы, извлеченные из внешних источников с помощью таких средств, как веб-краулер или сбор данных . Потоковая передача извлеченного источника данных и загрузка «на лету» в целевую базу данных — это еще один способ выполнения ETL, когда не требуется промежуточное хранение данных.
Неотъемлемой частью извлечения является проверка данных для подтверждения того, имеют ли данные, извлеченные из источников, правильные/ожидаемые значения в заданном домене (например, шаблон/значение по умолчанию или список значений). Если данные не соответствуют правилам проверки, они полностью или частично отклоняются. В идеале отклоненные данные возвращаются в исходную систему для дальнейшего анализа с целью выявления и исправления неверных записей или выполнения обработки данных .
На этапе преобразования данных к извлеченным данным применяется ряд правил или функций, чтобы подготовить их к загрузке в конечный объект.
Важной функцией преобразования является очистка данных , которая направлена на передачу только «правильных» данных в цель. Проблема при взаимодействии различных систем заключается в интерфейсе и коммуникации соответствующих систем. Наборы символов, которые могут быть доступны в одной системе, могут отсутствовать в других.
В других случаях для удовлетворения деловых и технических потребностей сервера или хранилища данных может потребоваться один или несколько из следующих типов преобразований:
Фаза загрузки загружает данные в конечный целевой объект, которым может быть любое хранилище данных, включая простой разграниченный плоский файл или хранилище данных . В зависимости от требований организации этот процесс сильно различается. Некоторые хранилища данных могут перезаписывать существующую информацию кумулятивной информацией; обновление извлеченных данных часто выполняется ежедневно, еженедельно или ежемесячно. Другие хранилища данных (или даже другие части того же хранилища данных) могут добавлять новые данные в исторической форме через регулярные интервалы времени, например, ежечасно. Чтобы понять это, рассмотрим хранилище данных, которое требуется для ведения записей о продажах за последний год. Это хранилище данных перезаписывает любые данные старше года более новыми данными. Однако ввод данных для любого годового окна выполняется историческим образом. Сроки и объем замены или добавления являются стратегическими решениями по проектированию, зависящими от доступного времени и потребностей бизнеса . Более сложные системы могут поддерживать историю и аудиторский след всех изменений данных, загруженных в хранилище данных. Поскольку фаза загрузки взаимодействует с базой данных, применяются ограничения, определенные в схеме базы данных, а также в триггерах, активируемых при загрузке данных (например, уникальность, ссылочная целостность , обязательные поля), которые также вносят вклад в общую производительность качества данных процесса ETL.
Реальный цикл ETL может состоять из дополнительных этапов выполнения, например:
Процессы ETL могут быть весьма сложными, а при неправильном проектировании систем ETL могут возникнуть серьезные эксплуатационные проблемы.
Диапазон значений данных или качество данных в операционной системе могут превышать ожидания проектировщиков на момент указания правил проверки и преобразования. Профилирование данных источника во время анализа данных может определить условия данных, которые должны управляться спецификациями правил преобразования, что приводит к изменению правил проверки, явно и неявно реализованных в процессе ETL.
Хранилища данных обычно собираются из различных источников данных с различными форматами и целями. Таким образом, ETL является ключевым процессом для объединения всех данных в стандартной однородной среде.
Анализ дизайна [5] должен установить масштабируемость системы ETL на протяжении всего срока ее использования, включая понимание объемов данных, которые должны быть обработаны в рамках соглашений об уровне обслуживания . Время, доступное для извлечения из исходных систем, может измениться, что может означать, что тот же объем данных может быть обработан за меньшее время. Некоторые системы ETL должны масштабироваться для обработки терабайт данных для обновления хранилищ данных десятками терабайт данных. Увеличение объемов данных может потребовать конструкций, которые могут масштабироваться от ежедневной партии до многодневной микропартии для интеграции с очередями сообщений или сбора изменений данных в реальном времени для непрерывного преобразования и обновления.
Уникальные ключи играют важную роль во всех реляционных базах данных, поскольку они связывают все вместе. Уникальный ключ — это столбец, который идентифицирует данную сущность, тогда как внешний ключ — это столбец в другой таблице, который ссылается на первичный ключ. Ключи могут состоять из нескольких столбцов, в этом случае они являются составными ключами. Во многих случаях первичный ключ — это автоматически сгенерированное целое число, которое не имеет значения для представляемой бизнес-сущности , но существует исключительно для целей реляционной базы данных — обычно называемое суррогатным ключом .
Поскольку в хранилище обычно загружается более одного источника данных, ключи являются важной проблемой, которую необходимо решить. Например: клиенты могут быть представлены в нескольких источниках данных, с их номером социального страхования в качестве первичного ключа в одном источнике, их номером телефона в другом и суррогатом в третьем. Однако хранилище данных может потребовать консолидации всей информации о клиентах в одно измерение .
Рекомендуемый способ решения этой проблемы заключается в добавлении суррогатного ключа хранилища, который используется в качестве внешнего ключа из таблицы фактов. [6]
Обычно обновления происходят в исходных данных измерения, что, очевидно, должно быть отражено в хранилище данных.
Если первичный ключ исходных данных требуется для отчетности, измерение уже содержит эту часть информации для каждой строки. Если исходные данные используют суррогатный ключ, хранилище должно отслеживать его, даже если он никогда не используется в запросах или отчетах; это делается путем создания таблицы поиска , содержащей суррогатный ключ хранилища и исходный ключ. [7] Таким образом, измерение не загрязняется суррогатами из различных исходных систем, в то время как возможность обновления сохраняется.
Таблица поиска используется по-разному в зависимости от характера исходных данных. Существует 5 типов, которые следует рассмотреть; [7] три из них включены здесь:
Поставщики ETL оценивают свои системы записи на уровне нескольких ТБ (терабайт) в час (или ~1 ГБ в секунду), используя мощные серверы с несколькими ЦП, несколькими жесткими дисками, несколькими гигабитными сетевыми подключениями и большим объемом памяти.
В реальной жизни самая медленная часть процесса ETL обычно происходит на этапе загрузки базы данных. Базы данных могут работать медленно, поскольку им приходится заботиться о параллелизме, поддержании целостности и индексах. Таким образом, для лучшей производительности может иметь смысл использовать:
Тем не менее, даже при использовании массовых операций, доступ к базе данных обычно является узким местом в процессе ETL. Некоторые распространенные методы, используемые для повышения производительности:
nullзначениями, которые могут исказить разбиение)disable constraint ( ...) в таблицах целевой базы данных во время загрузкиdisable trigger...) в таблицах целевой базы данных во время загрузки: смоделировать их действие как отдельный шагdrop index... ; create index...)Выполнять ли определенные операции в базе данных или за ее пределами, может быть компромиссом. Например, удаление дубликатов с использованием distinctможет быть медленным в базе данных; таким образом, имеет смысл делать это за ее пределами. С другой стороны, если использование distinctзначительно (x100) уменьшает количество извлекаемых строк, то имеет смысл удалять дубликаты в базе данных как можно раньше, перед выгрузкой данных.
Распространенным источником проблем в ETL является большое количество зависимостей между заданиями ETL. Например, задание «B» не может начаться, пока задание «A» не завершено. Обычно можно добиться лучшей производительности, визуализируя все процессы на графике и пытаясь уменьшить график, максимально используя параллелизм и делая «цепочки» последовательной обработки как можно короче. Опять же, разбиение больших таблиц и их индексов может действительно помочь.
Другая распространенная проблема возникает, когда данные распределены по нескольким базам данных, и обработка выполняется в этих базах данных последовательно. Иногда репликация базы данных может быть задействована как метод копирования данных между базами данных – это может значительно замедлить весь процесс. Распространенным решением является сокращение графа обработки до трех слоев:
Такой подход позволяет обработке максимально использовать преимущества параллелизма. Например, если вам нужно загрузить данные в две базы данных, вы можете запустить загрузки параллельно (вместо загрузки в первую — и затем репликации во вторую).
Иногда обработка должна происходить последовательно. Например, размерные (справочные) данные необходимы до того, как можно будет получить и проверить строки для основных таблиц «фактов» .
Некоторые реализации программного обеспечения ETL включают параллельную обработку . Это позволяет использовать ряд методов для повышения общей производительности ETL при работе с большими объемами данных.
Приложения ETL реализуют три основных типа параллелизма:
Все три типа параллелизма обычно применяются совместно в одной работе или задаче.
Дополнительная сложность возникает с обеспечением относительной согласованности загружаемых данных. Поскольку несколько исходных баз данных могут иметь разные циклы обновления (некоторые могут обновляться каждые несколько минут, в то время как другие могут занимать дни или недели), может потребоваться система ETL для удержания определенных данных до тех пор, пока все источники не будут синхронизированы. Аналогично, когда склад может быть сверен с содержимым исходной системы или с главной бухгалтерской книгой, становится необходимым установление точек синхронизации и сверки.
Процедуры хранилищ данных обычно подразделяют большой процесс ETL на более мелкие части, работающие последовательно или параллельно. Чтобы отслеживать потоки данных, имеет смысл помечать каждую строку данных как "row_id", а каждую часть процесса как "run_id". В случае сбоя наличие этих идентификаторов помогает откатить и перезапустить сбойную часть.
Лучшая практика также требует контрольных точек , которые являются состояниями, когда определенные фазы процесса завершены. По достижении контрольной точки хорошей идеей будет записать все на диск, очистить некоторые временные файлы, зарегистрировать состояние и т. д.
Установленная структура ETL может улучшить связность и масштабируемость . [ требуется цитата ] Хороший инструмент ETL должен иметь возможность взаимодействовать со многими различными реляционными базами данных и читать различные форматы файлов, используемые в организации. Инструменты ETL начали мигрировать в интеграцию корпоративных приложений или даже в корпоративную сервисную шину , системы, которые теперь охватывают гораздо больше, чем просто извлечение, преобразование и загрузку данных. Многие поставщики ETL теперь имеют возможности профилирования данных , качества данных и метаданных . Обычный вариант использования инструментов ETL включает преобразование файлов CSV в форматы, читаемые реляционными базами данных. Типичный перевод миллионов записей облегчается инструментами ETL, которые позволяют пользователям вводить потоки/файлы данных в формате CSV и импортировать их в базу данных с минимальным количеством кода.
Инструменты ETL обычно используются широким кругом профессионалов — от студентов, изучающих компьютерные науки, которые хотят быстро импортировать большие наборы данных, до архитекторов баз данных, отвечающих за управление учетными записями компаний. Инструменты ETL стали удобным инструментом, на который можно положиться для достижения максимальной производительности. Инструменты ETL в большинстве случаев содержат графический интерфейс, который помогает пользователям удобно преобразовывать данные, используя визуальный картограф данных, в отличие от написания больших программ для анализа файлов и изменения типов данных.
Хотя инструменты ETL традиционно предназначались для разработчиков и ИТ-персонала, исследовательская фирма Gartner пишет, что новая тенденция заключается в предоставлении этих возможностей бизнес-пользователям, чтобы они могли самостоятельно создавать соединения и интеграцию данных при необходимости, а не обращаться к ИТ-персоналу. [8] Gartner называет таких нетехнических пользователей гражданскими интеграторами. [9]
В приложениях обработки онлайн-транзакций (OLTP) изменения из отдельных экземпляров OLTP обнаруживаются и регистрируются в моментальном снимке или пакете обновлений. Экземпляр ETL может использоваться для периодического сбора всех этих пакетов, преобразования их в общий формат и загрузки в озеро данных или хранилище. [1]
Виртуализация данных может использоваться для усовершенствования обработки ETL. Применение виртуализации данных к ETL позволило решить наиболее распространенные задачи ETL по миграции данных и интеграции приложений для нескольких распределенных источников данных. Виртуальный ETL работает с абстрактным представлением объектов или сущностей, собранных из различных реляционных, полуструктурированных и неструктурированных источников данных . Инструменты ETL могут использовать объектно-ориентированное моделирование и работать с представлениями сущностей, постоянно хранящимися в централизованной архитектуре типа «ступица и спица» . Такая коллекция, которая содержит представления сущностей или объектов, собранных из источников данных для обработки ETL, называется репозиторием метаданных, и она может находиться в памяти или быть сделана постоянной. Используя постоянный репозиторий метаданных, инструменты ETL могут переходить от одноразовых проектов к постоянному промежуточному программному обеспечению, выполняя согласование данных и профилирование данных последовательно и в режиме, близком к реальному времени.
Извлечение, загрузка, преобразование (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]