ETL расшифровывается как Extract, Transform и Load. Это важная концепция в системах хранения данных. Извлечение означает извлечение данных из различных источников данных, таких как транзакционные системы или приложения. Преобразование означает применение правил преобразования данных, чтобы они стали пригодными для аналитической отчетности. Процесс загрузки включает перемещение данных в целевую систему, обычно в хранилище данных.
Три уровня, вовлеченные в цикл ETL:
Промежуточный уровень — промежуточный уровень используется для хранения данных, извлеченных из различных систем исходных данных.
Уровень интеграции данных — уровень интеграции преобразует данные из промежуточного уровня и перемещает данные в базу данных, где данные объединяются в иерархические группы, часто называемые измерениями , в факты и агрегируют факты. Комбинация таблиц фактов и измерений в системе DW называется схемой .
Уровень доступа. Уровень доступа используется конечными пользователями для получения данных для аналитической отчетности.
Инструмент ETL используется для извлечения данных из разных источников данных, преобразования данных и загрузки их в систему DW. В отличие от этого, инструмент BI используется для создания интерактивных и специальных отчетов для конечных пользователей, информационной панели для высшего руководства, визуализации данных для ежемесячных, ежеквартальных и ежегодных заседаний совета директоров.
К наиболее распространенным инструментам ETL относятся — SAP BO Data Services (BODS), Informatica, Microsoft — SSIS, Oracle Data Integrator ODI, Talend Open Studio, Clover ETL с открытым исходным кодом и т. Д.
Наиболее распространенные инструменты BI включают — SAP Business Objects, SAP Lumira, IBM Cognos, JasperSoft, Microsoft BI Platform, Tableau, Oracle Business Intelligence Enterprise Edition и т. Д.
Популярные инструменты ETL, доступные на рынке, —
Промежуточная область — это промежуточная область, которая находится между источниками данных и системами хранилищ данных / витрин данных. Промежуточные области могут быть спроектированы для обеспечения многих преимуществ, но основными мотивами их использования являются повышение эффективности процессов ETL, обеспечение целостности данных и поддержка операций с качеством данных.
Хранилище данных представляет собой более широкую концепцию по сравнению с интеллектуальным анализом данных. Интеллектуальный анализ данных включает извлечение скрытой информации из данных и интерпретацию ее для будущих прогнозов. В отличие от этого, хранилище данных включает в себя такие операции, как аналитическая отчетность для генерации подробных отчетов и специальных отчетов, обработка информации для создания интерактивных информационных панелей и диаграмм.
OLTP означает систему онлайн-обработки транзакций, которая обычно является реляционной базой данных и используется для управления ежедневными транзакциями.
OLAP означает систему онлайн-аналитической обработки, которая обычно является многомерной системой и также называется хранилищем данных.
Предположим, что компания продает свою продукцию клиентам. Каждая продажа — это факт, который происходит внутри компании, и таблица фактов используется для записи этих фактов. В каждой таблице фактов хранятся первичные ключи для соединения таблицы фактов с таблицами измерений и показателями / фактами.
Пример — Fact_Units
Таблица измерений хранит атрибуты или измерения, которые описывают объекты в таблице фактов. Это набор сопутствующих таблиц к таблице фактов.
Пример — Dim_Customer
Витрина данных представляет собой простую форму хранилища данных и ориентирована на одну функциональную область. Обычно он получает данные только из нескольких источников.
Пример. В организации могут существовать витрины данных для финансов, маркетинга, отдела кадров и других отдельных отделов, в которых хранятся данные, относящиеся к их конкретным функциям.
Агрегатные функции используются для группировки нескольких строк одного столбца для формирования более значимого измерения. Они также используются для оптимизации производительности, когда мы сохраняем агрегированные таблицы в хранилище данных.
Общие агрегатные функции —
пример
Операторы языка определения данных (DDL) используются для определения структуры или схемы базы данных.
Примеры —
CREATE — для создания объектов в базе данных
ALTER — изменяет структуру базы данных
Операторы языка манипулирования данными (DML) используются для манипулирования данными в базе данных.
Примеры —
SELECT — извлекает данные из базы данных
INSERT — вставляет данные в таблицу
ОБНОВЛЕНИЕ — обновляет существующие данные в таблице
DELETE — удаляет все записи из таблицы, место для записей остается
Операторы языка управления данными (DCL) используются для управления доступом к объектам базы данных.
Примеры —
GRANT — дает пользователю права доступа к базе данных
REVOKE — отзывает права доступа, предоставленные командой GRANT
Операторы используются для указания условий в операторе SQL и в качестве союзов для нескольких условий в операторе. Общие типы операторов:
Операторы общего набора в SQL:
Операция пересечения используется для объединения двух операторов SELECT, но она возвращает только те записи, которые являются общими для обоих операторов SELECT. В случае Intersect количество столбцов и тип данных должны быть одинаковыми. MySQL не поддерживает оператор INTERSECT. Запрос Intersect выглядит следующим образом:
Операция минус объединяет результат двух операторов Select и возвращает только тот результат, который принадлежит первому набору результатов. Минус-запрос выглядит следующим образом —
Если вы выполняете источник минус цель и цель минус источник, и если запрос минус возвращает значение, это следует рассматривать как случай несовпадения строк.
Если минус-запрос возвращает значение, а пересечение счетчика меньше, чем счетчик источника или таблица назначения, то исходная и целевая таблицы содержат повторяющиеся строки.
Предложение group-by используется с оператором select для сбора данных подобного типа. HAVING очень похож на WHERE, за исключением того, что утверждения внутри него носят совокупный характер.
Синтаксис —
Пример — таблица сотрудников
Группировать по стране
Тестирование ETL выполняется до того, как данные перемещаются в производственную систему хранилища данных. Иногда это также называется балансировкой таблицы или сверкой производства.
Основной целью тестирования ETL является выявление и устранение дефектов данных и общих ошибок, которые возникают до обработки данных для аналитической отчетности.
В следующей таблице представлены ключевые функции тестирования баз данных и ETL и их сравнение.
ETL-тестирование можно разделить на следующие категории в зависимости от их функции —
Тестирование количества от источника к цели — включает в себя сопоставление количества записей в исходной и целевой системах.
Тестирование данных от источника к цели — включает проверку данных между исходной и целевой системами. Он также включает интеграцию данных и проверку пороговых значений, а также проверку дубликатов данных в целевой системе.
Data Mapping или Transformation Testing — подтверждает сопоставление объектов в исходной и целевой системах. Это также включает проверку функциональности данных в целевой системе.
Тестирование конечного пользователя — включает в себя создание отчетов для конечных пользователей, чтобы проверить, соответствуют ли данные в отчетах ожиданиям. Это включает в себя поиск отклонений в отчетах и перекрестную проверку данных в целевой системе для проверки отчетов.
Повторное тестирование — включает исправление ошибок и дефектов в данных в целевой системе и повторный запуск отчетов для проверки данных.
Тестирование системной интеграции — включает в себя тестирование всех отдельных систем, а затем объединение результатов, чтобы определить, есть ли отклонения.
Потеря данных в процессе ETL.
Неверные, неполные или дублирующие данные.
Система DW содержит исторические данные, поэтому объем данных слишком велик и действительно сложен для проведения тестирования ETL в целевой системе.
Тестерам ETL обычно не предоставляется доступ к просмотру графиков работы в инструменте ETL. У них практически нет доступа к инструментам отчетности BI, чтобы увидеть окончательный макет отчетов и данных внутри отчетов.
Трудно генерировать и создавать тестовые случаи, поскольку объем данных слишком велик и сложен.
Тестеры ETL обычно не имеют представления о требованиях к отчетам конечного пользователя и бизнес-потоке информации.
ETL-тестирование включает в себя различные сложные концепции SQL для проверки данных в целевой системе.
Иногда тестировщикам не предоставляется источник информации о целевом отображении.
Нестабильная среда тестирования приводит к задержке разработки и тестирования процесса.
Основные обязанности тестера ETL включают в себя:
Проверка таблиц в исходной системе — проверка количества, проверка типа данных, ключи не пропущены, дубликаты данных.
Применение логики преобразования перед загрузкой данных: проверка порога данных, проверка суррогатного ключа и т. Д.
Загрузка данных из промежуточной области в целевую систему: агрегированные значения и вычисленные показатели, ключевые поля не пропущены, проверка количества в целевой таблице, проверка отчета BI и т. Д.
Тестирование инструмента ETL и его компонентов, контрольных примеров — создание, разработка и выполнение планов тестирования, контрольных примеров, инструмента тестирования ETL и его функций, системы Test DW и т. Д.
Преобразование — это набор правил, который генерирует, изменяет или передает данные. Преобразование может быть двух типов — активное и пассивное.
В активном преобразовании число строк, созданных в качестве выходных данных, может быть изменено после выполнения преобразования. Этого не происходит во время пассивного преобразования. Информация проходит через тот же номер, что и входные данные.
Разделение — это когда вы разделяете область хранилища данных на части. Обычно это делается для улучшения производительности транзакций.
Если ваша система DW огромна по размеру, для поиска данных потребуется время. Разделение пространства хранения позволяет вам находить и анализировать данные проще и быстрее.
Разделение может быть двух типов — циклическое разбиение и разбиение по хэшу.
При циклическом разбиении данные равномерно распределяются по всем разделам, поэтому количество строк в каждом разделе относительно одинаково. Хэширование — это когда сервер использует хеш-функцию для создания ключей секционирования для группировки данных.
Маплет определяет правила Преобразования.
Сеансы определяются для указания данных при их перемещении из исходной системы в целевую.
Рабочий процесс — это набор инструкций, которые инструктируют сервер о выполнении задачи.
Отображение — это перемещение данных из источника в пункт назначения.
Преобразование «Уточняющий запрос» позволяет получить доступ к данным из реляционных таблиц, которые не определены в документах сопоставления. Это позволяет обновлять медленно изменяющиеся таблицы измерений, чтобы определить, существуют ли записи в целевом объекте или нет.
Суррогатный ключ — это что-то, имеющее числа, сгенерированные последовательностью, не имеющие значения, и просто для уникальной идентификации строки. Это не видно пользователям или приложению. Он также называется ключом-кандидатом.
У суррогатного ключа есть сгенерированные последовательности числа без значения. Он предназначен для уникальной идентификации строк.
Первичный ключ используется для уникальной идентификации строк. Он виден пользователям и может быть изменен согласно требованию.
В таких случаях вы можете применить метод контрольной суммы. Вы можете начать с проверки количества записей в исходной и целевой системах. Выберите суммы и сравните информацию.
В этом тестировании тестер проверяет диапазон данных. Все пороговые значения в целевой системе необходимо проверить, чтобы убедиться, что они соответствуют ожидаемому результату.
Пример. Атрибут Age не должен иметь значения больше 100. В столбце Date DD / MM / YY поле месяца не должно иметь значения больше 12.
Если первичный ключ не определен, могут появиться повторяющиеся значения.
Дублирование данных может также возникнуть из-за неправильного отображения и ошибок вручную при передаче данных из источника в целевую систему.
Регрессионное тестирование — это когда мы вносим изменения в правила преобразования данных и агрегирования, чтобы добавить новую функциональность и помочь тестировщику найти новые ошибки. Ошибки, которые появляются в данных, поступающих в регрессионное тестирование, называются регрессионными.
Три подхода — сверху вниз, снизу вверх и гибрид.
Наиболее распространенные сценарии тестирования ETL —
Очистка данных — это процесс удаления данных из хранилища данных. Он удаляет ненужные данные, такие как строки с нулевыми значениями или лишние пробелы.
Косметическая ошибка связана с графическим интерфейсом приложения. Это может быть связано со стилем шрифта, размером шрифта, цветами, выравниванием, орфографическими ошибками, навигацией и т. Д.
Это называется ошибка, связанная с анализом граничных значений.
Вы можете сделать это, создав переменную сопоставления и отфильтрованное преобразование. Возможно, вам потребуется сгенерировать последовательность, чтобы получить нужную вам специально отсортированную запись.
Сравнение значений — включает в себя сравнение данных в исходной и целевой системах с минимальным преобразованием или без него. Это можно сделать с помощью различных инструментов тестирования ETL, таких как преобразование квалификатора источника в Informatica.
Столбцы критических данных можно проверить, сравнив различные значения в исходной и целевой системах.
Вы можете использовать операторы Минус и Пересечение, чтобы выполнить проверку полноты данных. Когда вы выполняете исходный минус цель и целевой минус источник, а запрос минус возвращает значение, это является признаком несовпадения строк.
Если минус-запрос возвращает значение, а пересечение счетчика меньше, чем счетчик источника или таблица назначения, существуют повторяющиеся строки.
Преобразование ярлыков — это ссылка на объект, который доступен в общей папке. Эти ссылки обычно используются для различных источников и целей, которые должны совместно использоваться различными проектами или средами.
В Диспетчере репозитория ярлык создается путем присвоения статуса «Общий». Позже объекты можно перетаскивать из этой папки в другую папку. Этот процесс обеспечивает единую точку контроля для объекта, и у нескольких проектов нет всех источников и целей импорта в их локальных папках.
Многоразовое Преобразование является локальным для папки. Пример — Генератор последовательностей многократного использования для распределения складских идентификаторов клиентов. Полезно загружать информацию о клиенте из нескольких исходных систем и выделять уникальные идентификаторы для каждого нового ключа источника.
Когда вы присоединяете одну таблицу к себе, она называется Self-Join.
Нормализация базы данных — это процесс организации атрибутов и таблиц реляционной базы данных для минимизации избыточности данных.
Нормализация включает в себя разбиение таблицы на менее избыточные (и меньшие) таблицы, но без потери информации.
Таблица фактов без фактов — это таблица фактов, которая не имеет никаких мер. По сути, это пересечение измерений. Существует два типа таблиц без фактов: одна для захвата события, а другая для описания условий.
Медленно меняющиеся размеры относятся к изменяющемуся значению атрибута с течением времени. SCD бывают трех типов — Тип 1, Тип 2 и Тип 3.