Процесс ETL (извлечение, преобразование и загрузка) в хранилище данных

Что такое ETL?

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

Заманчиво думать, что создание хранилища данных — это просто извлечение данных из нескольких источников и загрузка в базу данных хранилища данных. Это далеко от истины и требует сложного процесса ETL. Процесс ETL требует активного участия различных заинтересованных сторон, включая разработчиков, аналитиков, тестировщиков, топ-менеджеров, и является технически сложным.

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

Зачем вам ЭТЛ?

Существует множество причин для внедрения ETL в организации:

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

Процесс ETL в хранилищах данных

ETL — это трехэтапный процесс

Шаг 1) Извлечение

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

Хранилищу данных необходимо интегрировать системы, имеющие разные

СУБД, Аппаратное обеспечение, Operaтинговые системы и протоколы связи. Источники могут включать в себя устаревшие приложения, такие как мейнфреймы, индивидуальные приложения, устройства точки контакта, такие как банкоматы, коммутаторы вызовов, текстовые файлы, электронные таблицы, ERP, данные от поставщиков, партнеров и другие.

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

Три метода извлечения данных:

Полное извлечение
Частичное извлечение — без уведомления об обновлении.
Частичное извлечение — с уведомлением об обновлении

Независимо от используемого метода извлечение не должно влиять на производительность и время отклика исходных систем. Эти исходные системы представляют собой действующие производственные базы данных. Любое замедление или блокировка могут повлиять на прибыль компании.

Некоторые проверки выполняются во время извлечения:

Согласовать записи с исходными данными
Убедитесь, что не загружен спам/нежелательные данные.
Проверка типа данных
Удалите все типы дубликатов/фрагментированных данных.
Проверьте, все ли ключи на месте или нет

Шаг 2) Трансформация

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

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

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

Ниже приведены данные Integrity Проблемы:

Разное написание одного и того же человека, например Джон, Джон и т. д.
Существует несколько способов обозначения названия компании, например Google, Google Inc.
Использование разных названий, таких как Кливленд, Кливленд.
Возможна ситуация, когда разные номера счетов генерируются разными приложениями для одного и того же клиента.
В некоторых файлах необходимые данные остаются пустыми.
Неверный продукт, полученный в POS, поскольку ввод вручную может привести к ошибкам.

На этом этапе проводятся проверки.

Фильтрация — выберите для загрузки только определенные столбцы.
Использование правил и справочных таблиц для стандартизации данных
Преобразование набора символов и обработка кодировки
Преобразование единиц измерения, такое как преобразование даты и времени, конвертация валют, числовые преобразования и т. д.
Проверка порога данных. Например, возраст не может состоять более чем из двух цифр.
Проверка потока данных из промежуточной области в промежуточные таблицы.
Обязательные поля не должны оставаться пустыми.
Очистка (например, сопоставление NULL с 0 или пола мужского с «M», а женского с «F» и т. д.)
Разделение столбца на несколько частей и объединение нескольких столбцов в один.
Транспонирование строк и столбцов,
Используйте поиск для объединения данных
Использование любой сложной проверки данных (например, если первые два столбца в строке пусты, строка автоматически отклоняется от обработки)

Шаг 3) Загрузка

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

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

Виды загрузки:

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

Проверка загрузки

Убедитесь, что данные ключевого поля не отсутствуют и не имеют значения NULL.
Тестовые представления моделирования на основе целевых таблиц.
Убедитесь, что объединены значения и рассчитаны меры.
Проверка данных в таблице измерений, а также в таблице истории.
Проверьте отчеты BI по загруженной таблице фактов и измерений.

Инструменты ETL

Здесь очень много Инструменты ETL доступны на рынке. Вот некоторые наиболее известные из них:

1. МаркЛогик:

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

https://www.marklogic.com/product/getting-started/

2. Oracle:

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

https://www.oracle.com/index.html

3. Amazon RedShift:

Amazon Redshift — это инструмент хранилища данных. Это простой и экономичный инструмент для анализа всех типов данных с использованием стандартных SQL и существующие инструменты BI. Это также позволяет выполнять сложные запросы к петабайтам структурированных данных.

https://aws.amazon.com/redshift/?nc2=h_m1

Вот полный список полезных Инструменты хранилища данных.

лучшие практики процесса ETL

Ниже приведены лучшие практики для этапов процесса ETL:

Никогда не пытайтесь очистить все данные:

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

Никогда ничего не очищайте:

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

Определим стоимость очистки данных:

Прежде чем очищать все «грязные» данные, вам важно определить стоимость очистки для каждого «грязного» элемента данных.

Чтобы ускорить обработку запросов, используйте вспомогательные представления и индексы:

Чтобы снизить затраты на хранение, храните сводные данные на дисковых лентах. Кроме того, необходим компромисс между объемом хранимых данных и их детальным использованием. Компромисс на уровне детализации данных для снижения затрат на хранение.

Резюме

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

Вы могли бы: