Войти
В вычислениях, извлечь, преобразовать, загрузить (ETL ) - это общая процедура копирования данных из одного или нескольких источников в целевую систему, которая представляет данные не так, как источник (и), или в другом контексте, чем источник (и). Процесс ETL стал популярной концепцией в 1970-х годах и часто используется в хранилищах данных.
Извлечение данных включает извлечение данных из однородных или разнородных источников; преобразование данных обрабатывает данные путем очистки данных и преобразования их в соответствующий формат / структуру хранения для целей запросов и анализа; наконец, загрузка данных описывает вставку данных в конечную целевую базу данных, такую как хранилище операционных данных, витрина данных, озеро данных или хранилище данных.
Правильно спроектированная система ETL извлекает данные из исходных систем, обеспечивает соблюдение стандартов качества и согласованности данных, согласовывает данные, чтобы отдельные источники могли использоваться вместе, и, наконец, доставляет данные в формате, готовом к презентации, чтобы разработчики приложений могут создавать приложения, а конечные пользователи могут принимать решения.
Поскольку извлечение данных требует времени, обычно три этапа выполняются в конвейере. Пока данные извлекаются, другой процесс преобразования выполняется при обработке уже полученных данных и подготавливает их к загрузке, в то время как загрузка данных начинается, не дожидаясь завершения предыдущих фаз.
Системы ETL обычно объединяют данные из нескольких приложений (систем), которые обычно разрабатываются и поддерживаются разными поставщиками или размещаются на отдельном компьютерном оборудовании. Отдельные системы, содержащие исходные данные, часто управляются разными сотрудниками. Например, система учета затрат может объединять данные из расчета заработной платы, продаж и закупок.
Стандартная диаграмма ETL Первая часть процесса ETL включает извлечение данных из исходной системы (систем). Во многих случаях это является наиболее важным аспектом ETL, поскольку извлечение данных правильно создает основу для успеха последующих процессов. Большинство проектов хранилищ данных объединяют данные из разных исходных систем. Каждая отдельная система также может использовать другую организацию данных и / или формат. Общие форматы источников данных включают реляционные базы данных, XML, JSON и плоские файлы, но могут также включать нереляционные структуры баз данных, такие как как Система управления информацией (IMS) или другие структуры данных, такие как Метод доступа к виртуальному хранилищу (VSAM) или Метод индексированного последовательного доступа (ISAM), или даже форматы получено из внешних источников с помощью таких средств, как поиск в Интернете или сканирование экрана. Потоковая передача извлеченного источника данных и загрузка «на лету» в целевую базу данных - это еще один способ выполнения ETL, когда промежуточное хранилище данных не требуется. В общем, этап извлечения направлен на преобразование данных в единый формат, подходящий для обработки преобразования.
Внутренняя часть извлечения включает проверку данных, чтобы подтвердить, имеют ли данные, извлеченные из источников, правильные / ожидаемые значения в данном домене (например, шаблон / значение по умолчанию или список значений). Если данные не соответствуют правилам проверки, они отклоняются полностью или частично. В идеале отклоненные данные возвращаются в исходную систему для дальнейшего анализа с целью выявления и исправления неправильных записей.
На этапе преобразование данных к извлеченным данным применяется ряд правил или функций, чтобы подготовить их для загрузки в конечную цель.
Важной функцией преобразования является очистка данных, цель которой - передать целевому объекту только «правильные» данные. Проблема при взаимодействии различных систем заключается во взаимодействии и взаимодействии соответствующих систем. Наборы символов, которые могут быть доступны в одной системе, могут быть недоступны в других.
В других случаях может потребоваться один или несколько из следующих типов преобразования для удовлетворения деловых и технических потребностей сервера или хранилища данных:
Фаза загрузки загружает данные в конечную цель, которая может быть любыми данными store, включая простой плоский файл с разделителями или хранилище данных . В зависимости от требований организации этот процесс сильно различается. Некоторые хранилища данных могут заменять существующую информацию совокупной информацией; обновление извлеченных данных часто выполняется ежедневно, еженедельно или ежемесячно. Другие хранилища данных (или даже другие части того же хранилища данных) могут добавлять новые данные в исторической форме через определенные промежутки времени, например, ежечасно. Чтобы понять это, рассмотрим хранилище данных, которое требуется для ведения записей о продажах за последний год. Это хранилище данных заменяет любые данные старше одного года более новыми данными. Однако ввод данных для любого окна на один год осуществляется в историческом порядке. Сроки и объем замены или добавления - это стратегический выбор дизайна, зависящий от имеющегося времени и бизнес потребностей. Более сложные системы могут поддерживать историю и контрольный журнал всех изменений данных, загруженных в хранилище данных.
Поскольку этап загрузки взаимодействует с базой данных, ограничения, определенные в схеме базы данных - а также в триггерах, активируемых при загрузке данных, - применяются (например, уникальность, ссылочная целостность, обязательные поля), которые также влияют на общую производительность качества данных процесса ETL.
Типичный реальный цикл ETL состоит из следующих шагов выполнения:
Процессы ETL могут иметь значительную сложность, и при неправильно спроектированных системах ETL могут возникать значительные эксплуатационные проблемы.
Диапазон значений данных или качество данных в операционной системе может превосходить ожидания проектировщиков на момент определения правил проверки и преобразования. Профилирование данных источника во время анализа данных может идентифицировать условия данных, которые должны управляться спецификациями правил преобразования, что приводит к изменению правил проверки, явно и неявно реализованному в процессе ETL.
Хранилища данных обычно собираются из множества источников данных с разными форматами и назначениями. Таким образом, ETL является ключевым процессом для объединения всех данных в стандартной однородной среде.
Анализ проекта должен установить масштабируемость системы ETL на протяжении всего срока ее использования, включая понимание объемов данных, которые должны обрабатываться в рамках соглашений об уровне обслуживания. Время, доступное для извлечения из исходных систем, может измениться, что может означать, что такой же объем данных, возможно, придется обработать за меньшее время. Некоторые системы ETL должны масштабироваться, чтобы обрабатывать терабайты данных, чтобы обновлять хранилища данных десятками терабайт данных. Для увеличения объемов данных могут потребоваться проекты, которые могут масштабироваться от ежедневного пакета до многодневного микропакета до интеграции с очередями сообщений или сбора данных об изменениях в реальном времени для непрерывного преобразования и обновления..
Поставщики ETL сравнивают свои рекордные системы со скоростью несколько ТБ (терабайт) в час (или ~ 1 ГБ в секунду), используя мощные серверы с несколькими процессорами, несколькими жесткими дисками, несколькими гигабитами. сетевые подключения и много памяти.
В реальной жизни самая медленная часть процесса ETL обычно происходит на этапе загрузки базы данных. Базы данных могут работать медленно, потому что им приходится заботиться о параллелизме, поддержании целостности и индексах. Таким образом, для повышения производительности может иметь смысл использовать:
Тем не менее, даже при использовании массовых операций доступ к базе данных обычно является узким местом в процессе ETL. Некоторые распространенные методы, используемые для повышения производительности:
Выполнение определенных операций в базе данных или вне ее может потребовать компромисса. Например, удаление дубликатов с использованием independentможет происходить медленно в базе данных; таким образом, имеет смысл делать это вне. С другой стороны, если использование uniqueзначительно (x100) уменьшает количество строк, которые необходимо извлечь, то имеет смысл как можно раньше удалить дубликаты в базе данных перед выгрузкой данных.
Распространенным источником проблем в ETL является большое количество зависимостей между заданиями ETL. Например,, задание «B» не может начаться, пока задание «A» не завершено. Обычно можно добиться большей производительности, визуализируя все процессы на графике и пытаясь уменьшить граф, максимально используя параллелизм и выполняя "цепочки" последовательной обработки как можно короче. Опять же, действительно может помочь разделение больших таблиц и их индексов.
Другая распространенная проблема возникает, когда данные распределены между несколькими базами данных, и обработка выполняется в этих базах данных последовательно. Иногда репликация базы данных может использоваться как метод копирования данных между базами данных - это может значительно замедлить весь процесс. Распространенным решением является сокращение графа обработки до трех уровней:
Этот подход позволяет обработке максимально использовать преимущества параллелизма. Например, если вам нужно загрузить данные в две базы данных, вы можете запускать загрузки параллельно (вместо загрузки в первую, а затем репликации во вторую).
Иногда обработка должна происходить последовательно. Например, размерные (справочные) данные необходимы до того, как можно будет получить и проверить строки для основных таблиц фактов.
Недавняя разработка в программном обеспечении ETL - это реализация параллельная обработка. Он позволил ряду методов улучшить общую производительность ETL при работе с большими объемами данных.
Приложения ETL реализуют три основных типа параллелизма:
Все три типа параллелизма обычно работают вместе в одном задании или задаче.
Дополнительная трудность связана с обеспечением относительной согласованности загружаемых данных. Поскольку несколько исходных баз данных могут иметь разные циклы обновления (некоторые могут обновляться каждые несколько минут, а другие могут занимать дни или недели), от системы ETL может потребоваться удерживать определенные данные до тех пор, пока все источники не будут синхронизированы. Аналогичным образом, если склад может потребоваться согласовать содержимое в исходной системе или с главной бухгалтерской книгой, возникает необходимость в установлении точек синхронизации и согласования.
Процедуры хранилища данных обычно подразделяют большой процесс ETL на более мелкие части, выполняющиеся последовательно или параллельно. Чтобы отслеживать потоки данных, имеет смысл пометить каждую строку данных «row_id» и пометить каждую часть процесса «run_id». В случае сбоя наличие этих идентификаторов поможет откатить и перезапустить отказавший кусок.
Лучшая практика также требует контрольных точек, которые представляют собой состояния, когда определенные этапы процесса завершены. Оказавшись на контрольной точке, рекомендуется записать все на диск, очистить некоторые временные файлы, зарегистрировать состояние и т. Д.
По состоянию на 2010 год данные виртуализация начала продвигать обработку ETL. Применение виртуализации данных к ETL позволило решить наиболее распространенные задачи ETL миграции данных и интеграции приложений для нескольких рассредоточенных источников данных. Virtual ETL работает с абстрактным представлением объектов или сущностей, собранных из различных источников реляционных, полуструктурированных и неструктурированных данных. Инструменты ETL могут использовать объектно-ориентированное моделирование и работать с представлениями сущностей, постоянно хранящимися в централизованной архитектуре звездообразный узел. Такая коллекция, которая содержит представления сущностей или объектов, собранных из источников данных для обработки ETL, называется репозиторием метаданных, и она может находиться в памяти или быть постоянной. Используя постоянный репозиторий метаданных, инструменты ETL могут переходить от одноразовых проектов к постоянному промежуточному программному обеспечению, выполняя согласование данных и профилирование данных последовательно и почти в реальном времени.
Уникальные ключи играют важную роль во всех реляционных базах данных, поскольку они связывают все вместе. Уникальный ключ - это столбец, который идентифицирует данную сущность, тогда как внешний ключ - это столбец в другой таблице, который ссылается на первичный ключ. Ключи могут состоять из нескольких столбцов, в этом случае они являются составными ключами. Во многих случаях первичный ключ представляет собой автоматически сгенерированное целое число, которое не имеет значения для представляемого бизнес-объекта , но существует исключительно для целей реляционной базы данных, обычно называемой суррогатом . key.
Поскольку обычно в хранилище загружается более одного источника данных, ключи представляют собой важную проблему, которую необходимо решить. Например: клиенты могут быть представлены в нескольких источниках данных с их номером социального страхования в качестве первичного ключа в одном источнике, их номером телефона в другом и суррогатом в третьем. Тем не менее, хранилище данных может потребовать консолидации всей информации о клиентах в одно измерение.
. Рекомендуемый способ решения проблемы включает добавление суррогатного ключа хранилища, который используется в качестве внешнего ключа из таблицы фактов.
Обычно обновления происходят в исходных данных измерения, которые, очевидно, должны быть отражены в хранилище данных.
Если для создания отчетов требуется первичный ключ исходных данных, измерение уже содержит эту информацию для каждой строки. Если в исходных данных используется суррогатный ключ, хранилище должно отслеживать его, даже если он никогда не используется в запросах или отчетах; это делается путем создания таблицы поиска, которая содержит суррогатный ключ хранилища и исходный ключ. Таким образом, измерение не загрязняется суррогатами из различных исходных систем, а возможность обновления сохраняется.
Таблица поиска используется по-разному в зависимости от характера исходных данных. Следует рассмотреть 5 типов; сюда включены три:
Используя установленную среду ETL, можно повысить свои шансы на улучшение связи и масштабируемость. Хороший инструмент ETL должен иметь возможность связываться с множеством различных реляционных баз данных и читать различные форматы файлов, используемые в организации. Инструменты ETL начали мигрировать в системы Enterprise Application Integration или даже Enterprise Service Bus, которые теперь охватывают гораздо больше, чем просто извлечение, преобразование и загрузку данных. Многие поставщики ETL теперь имеют возможности профилирования данных, качества данных и метаданных. Обычный вариант использования инструментов ETL включает преобразование файлов CSV в форматы, читаемые реляционными базами данных. Типичный перевод миллионов записей облегчается с помощью инструментов ETL, которые позволяют пользователям вводить потоки / файлы данных в формате csv и импортировать их в базу данных с минимальным количеством кода.
Инструменты ETL обычно используются широким кругом профессионалов - от студентов-информатиков, которые хотят быстро импортировать большие наборы данных, до архитекторов баз данных, отвечающих за управление учетными записями компании, инструменты ETL стали удобным инструментом, который можно полагался на получение максимальной производительности. Инструменты ETL в большинстве случаев содержат графический интерфейс, который помогает пользователям удобно преобразовывать данные с помощью визуального средства отображения данных, в отличие от написания больших программ для анализа файлов и изменения типов данных.
Хотя инструменты ETL традиционно предназначались для разработчиков и ИТ-персонала, новая тенденция заключается в предоставлении этих возможностей бизнес-пользователям, чтобы они сами могли создавать соединения и интеграции данных при необходимости, а не обращаться к ИТ-персоналу. Gartner называет этих нетехнических пользователей Citizen Integrators.
Извлечь, загрузить, преобразовать (ELT) - это вариант ETL, при котором извлеченные данные сначала загружаются в целевую систему. Архитектура конвейера аналитики также должна учитывать, где очищать и дополнять данные, а также как согласовывать измерения.
Облачные хранилища данных, такие как Amazon Redshift, Google BigQuery и Snowflake Computing смогли обеспечить высокую масштабируемость вычислительной мощности. Это позволяет предприятиям отказаться от преобразований с предварительной загрузкой и реплицировать необработанные данные в свои хранилища данных, где они могут преобразовывать их по мере необходимости с помощью SQL.
После использования ELT данные могут обрабатываться дальше и сохраняться в киоске данных.
У каждого подхода есть свои плюсы и минусы. Большинство инструментов интеграции данных склоняются к ETL, в то время как ELT популярен в устройствах баз данных и хранилищ данных. Точно так же можно выполнить TEL (преобразование, извлечение, загрузка), когда данные сначала преобразуются в цепочке блоков (как способ записи изменений в данных, например, сжигания токена) перед извлечением и загрузкой в другое хранилище данных.
