Превратите хаос в прибыль: понимание процесса ETL

Опубликовано: 2022-03-11

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

Представьте себе такой сценарий: вы — ИТ-менеджер компании по производству чехлов для мобильных телефонов с розничными точками по всей территории Соединенных Штатов. Ваш совет директоров изо всех сил пытается принимать решения на основе продаж, потому что информация доступна, но находится в разных местах и ​​в разных форматах. Один из директоров просит вас создать информационную панель с информацией о продажах из всех точек продаж, чтобы представить ее на следующем заседании совета директоров.

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

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

Основы ETL

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

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

Давайте углубимся в три шага ETL, чтобы подробно описать процесс.

Добыча

Извлечение — это процесс получения данных из одного или нескольких источников. Источники могут иметь различные форматы и структуры, такие как документы, электронные таблицы, файлы CSV, плоские файлы, реляционные базы данных, такие как Oracle, MySQL, SQL Server, нереляционные базы данных и т. д.

Существует два основных типа извлечения: полное и частичное .

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

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

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

Трансформация

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

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

В некоторых проектах, обычно с небольшим объемом данных, нет необходимости использовать промежуточные области, но в большинстве проектов они используются.

На этапе трансформации решается ряд задач:

  • Выбор: критерии выбора извлеченных данных. Выбор может быть сделан на этапе экстракции, на этапе трансформации или на обоих этапах.
  • Интеграция: объединение данных из фазы извлечения в промежуточную область. Эта комбинация означает добавление, удаление и обновление данных в промежуточной области на основе извлеченных данных.
  • Соединения: используются для объединения извлеченных данных, аналогично соединениям SQL (внутреннее соединение, левое соединение, правое соединение, полное соединение и т. д.).
  • Очистка или очистка: удаляются несогласованные или недопустимые данные или данные с ошибками для улучшения качества данных. Работа с несколькими источниками данных увеличивает вероятность возникновения проблем с данными, требующих очистки, таких как:
    • Ссылочная целостность (клиент с несуществующей категорией)
    • Отсутствующие значения (клиент без идентификатора)
    • Уникальность (более одного человека с одинаковым SSN)
    • Орфографические ошибки (Сан-Диего, Каннада, Л.Анжелес)
    • Противоречивые ценности (Алексей 27.04.1974 г.р., Алексей 14.04.2000 г.р.)
    • и много других
  • Суммирование: суммирование наборов данных для последующего использования.
  • Агрегации: данные собраны и обобщены в группы
  • Консолидация: данные из нескольких источников или структур, объединенные в единый набор данных.

Вот некоторые распространенные типы трансформации:

  • Удалить повторяющиеся данные
  • Разделение и слияние
  • Преобразования (дата, время, числовые маски, измерения)
  • Кодирование (от мужчины к M)
  • Расчеты (item_value = unit_Price * количество)
  • Генерация ключей

Загрузка

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

Существует три основных типа загрузки данных: полная или начальная, добавочная и обновление.

  • Полная или начальная означает полную загрузку извлеченных и преобразованных данных. Все данные в промежуточной области будут загружены в конечный пункт назначения, чтобы быть готовыми для бизнес-пользователей.
  • Инкрементная загрузка — это процесс сравнения преобразованных данных с данными в конечном пункте назначения и загрузки только новых данных. Добавочная загрузка может использоваться в сочетании с загрузкой обновления, как описано ниже.
  • Обновляющая загрузка — это процесс обновления данных в конечном месте назначения для отражения изменений, сделанных в исходном источнике. Обновление может быть полным или добавочным.

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

Тестирование

Тестирование — один из самых важных этапов ETL, но при этом один из самых недооцененных.

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

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

То же самое нужно делать после любого преобразования. Например, при суммировании данных на этапе преобразования данные должны быть проверены, чтобы гарантировать, что данные не были потеряны, а суммы верны.

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

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

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

Если обнаружены ошибки, они должны быть исправлены, и есть несколько решений, которые необходимо принять, если вам нужно их исправить: Должны ли быть изменены исходные данные? Возможно ли это сделать? Если ошибки не могут быть исправлены в исходном коде, могут ли они быть исправлены каким-либо преобразованием?

В некоторых случаях данные с ошибками должны быть устранены, а для информирования ответственных лиц инициировано оповещение.

Некоторые примеры тестирования:

  • Данные требуют проверки
  • Качество данных
  • Представление
  • Правила данных
  • Моделирование данных

логирование

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

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

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

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

Журналы улучшают автоматизацию. Процессы ETL с большим объемом данных, которые запускаются автоматически, нуждаются в системах журналирования. Если они хорошо спланированы и выполнены, все усилия, затраченные на создание системы ведения журналов, окупятся в виде более быстрой идентификации ошибок, более надежных данных и точек улучшения, обнаруженных в файлах журналов.

Существует три основных этапа создания системы журналов: создание, архивирование и анализ .

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

Представление

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

Сколько усилий нужно затратить на разработку системы регистрации?

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

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

Для более очевидного подхода, независимо от размера компании, объема данных и частоты интеграции, процесс ETL должен быть прибыльным . Инвестиции времени, денег и технических ресурсов должны приносить прибыль — как сказал экономист Милтон Фридман: «Бесплатных обедов не бывает».

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