CC BY
20
УДК 004.62
Гарина И. О. студент магистратуры 2 курса факультет «Информатика и системы управления»
Ходько А. О. студент магистратуры 2 курса факультет «Информатика и системы управления»
Россия, г. Москва ПОСТРОЕНИЕ УСТОЙЧИВЫХ ETL СИСТЕМ
Аннотация:
В статье рассматриваются системы манипулирования данными - так называемые Extract-Transform-Load (ETL) системы. Выявлены основные недостатки и ошибки, возникающие при проектировании такого рода систем. Представлены шаги для улучшения процесса ETL. Даны рекомендации по управлению данными, способные обеспечивать устойчивые ETL системы.
Ключевые слова: данные, база данных, онлайн-транзакции, оптимизация, агрегация, управление, документирование, распараллеливание
Garina I. О., student of magistracy 2 year, Faculty of Informatics and Control Systems
Russia, Moscow Khodko A. O., student of magistracy 2 year, Faculty of Informatics and Control Systems
Russia, Moscow CONSTRUCTION OF SUSTAINABLE ETL SYSTEMS
Annotation:
Data manipulation systems - the so-called Extract-Transform-Load (ETL) systems are presented in the article.. The main shortcomings and errors that arise in the design of such systems are revealed. The steps presented to improve the ETL process. Recommendations on data management, capable of providing stable ETL systems, are given.
Keywords: data, database, online transactions, optimization, aggregation, management, documentation, parallelization
ВВЕДЕНИЕ
Когда только появились первые базы данных, считалось, что они будут доступны только одному приложению. Но за последние тридцать лет они стали участниками богатой экосистемы, публикуя только те данные, для которых они являются основным источником, и подписываясь на все остальные. Из-за существенной ненадежности сетей данные, как правило, записываются в базу, затем делятся на части и проверяются во время записи. Этот процесс называется Extract-Transform-Load (ETL).
Далеко не все организации при разработке своих систем обработки
онлайн-транзакций (OLTP) думают о жизненном цикле данных вне этой системы. Они не продумывают, как преобразовывать и агрегировать данные из разных источников в центральное хранилище, которое посредством интеллектуального анализа может помочь поддерживать и управлять бизнес-решениями. Даже в тех случаях, когда многие основные процессы, связанные с разработкой и тестированием базы данных, автоматизированы и управляемы, система ETL остается в стороне.
Игнорирование оптимизации БТЬ приводит к тому, что процессы внутри становятся хаотичны, они выполняются долго и часто завершаются неудачно, а это влечет огромные временные потери, а также затраты на восстановление после ошибок. Все это вызвано недостатками в архитектуре системы.
Эта статья поможет понять специфические проблемы, связанные с системами ETL, и шаблоны управления жизненным циклом базы данных (DLM), которые применимы при их разработке, управлении и поддержке. ОБЗОР СИСТЕМ Е^
БТЬ-системы извлекают и преобразуют большие объемы исходных данных в новый набор данных, а затем загружают их в новое хранилище для последующего использования. Этот процесс происходит потому, что данные извлекаются из разных источников (некоторые внутренние, некоторые внешние для организации), и необходимо преобразовать или упорядочить поля данных, прежде чем продолжить обработку.
Может показаться, что ETL состоит из трех различных последовательных процессов, но на самом деле они пересекаются между собой и не обязательно следуют один за другим. Пользователи систем ETL -это всегда другие программные системы или персонал, участвующий в анализе данных, а не интерактивные клиенты или конечные пользователи.
На рисунке 1 представлено упрощенное описание системы ETL. Каждая черная стрелка представляет собой задачу ETL, поскольку данные поступают из разных источников, преобразуются и загружаются в хранилище данных. Процесс ETL часто объединяет данные от нескольких разных поставщиков, и во многих случаях данные ETL также могут быть источниками данных для другого процесса ETL. Например, куб SSAS может быть источником для базы данных OLTP, которая берет данные из него, обеспечивая агрегированные и рассчитанные значения для ключевых бизнес-показателей.
Рис.1 - Упрощенная схема ETL
Сервер получателя обязан проверять и при необходимости преобразовывать данные или делегировать эту задачу отдельному серверу или самому процессу ETL. Если база данных использует промежуточную таблицу, данные преобразуются и загружаются по строкам в целевую базу, в данном примере - реляционную. Для этой задачи используется инструмент ETL, такой как службы интеграции SQL Server (SSIS). Он должен будет выполнить ряд задач преобразования, в зависимости от целей, таких как:
• объединение столбцов;
• разделение столбцов;
• преобразование данных - сортировка, агрегирование;
• преобразование типа данных.
На этом этапе также будет выполняться первый этап проверки данных. Например, должны определяться ограничения для каждого из столбцов в целевой базе данных, чтобы убедиться, что данные соответствуют бизнес-правилам. Если строка не соответствует условиям, она записывается в таблицу журналов для проверки, и процесс продолжается. Также применяются или поддерживаются все соответствующие индексы в целевых таблицах.
ТИПИЧНЫЕ ПРОБЛЕМЫ ПРОЦЕССОВ ETL
В случае долгой обработки данных или ошибки ETL-процесса команде разработчиков требуется много времени, чтобы обнаружить проблему. Когда данные недоступны или неверны, разработчики склонны объяснять это переполнением пакета.
На самом деле, существуют три наиболее распространенные причины, приводящие к хаотичности ETL-процессов. Рассмотри их далее.
1. Отсутствие знаний о восходящих и нисходящих системах.
Как правило, каждая команда практически не знает требований к формату данных, которые устанавливаются командами, которые работают дальше в конвейере данных. Это означает, что восходящие изменения в схеме данных часто выходят за пределы ETL и систем отчетности. Процессы ETL также подвержены сбоям в результате любых изменений формата исходных данных или из-за грязных данных в источнике. Часто эти проблемы не проявляются до тех пор, пока большая часть исходных данных не обработана.
Когда это случается, у команды не остается выбора, кроме как вручную исправлять данные или поток обработки ETL, а затем снова запускать весь процесс. Часто корректировки процесса ETL оказывают влияние на системы Business intelligence, отчетности и оповещения, которые основываются на этих данных.
2. Длительное и непредсказуемое время обработки ETL.
Когда процесс ETL превышает выделенное ему время, выявляются процессы, приводящие к этому, и предпринимается попытка их оптимизации, но часто это приводит к игнорированию основной проблемы, когда другой процесс, работающий в то же время, мешает им.
В рабочей системе, когда несколько процессов пытаются записывать и считывать данные из базы данных, будут происходить блокировки. По мере роста количества источников и объема данных процессы ETL становятся все более продолжительными, пока в один момент их становится невозможно завершить в выделенное время, что означает, что данные становятся недоступны в целевой базе данных, когда бизнес нуждается в них, а процессы обработки вниз по потоку терпят неудачу и требуют повторного запуска.
3. Длительное неудачное тестирование ETL
Многие системы ETL не поддаются тестированию. При тестировании процесса ETL команда может, например, восстановить резервную копию полного набора исходных данных, выполнить сквозной запуск ETL, а затем запустить несколько тестов в промежуточной области, чтобы проверить, что их правила обработки работают, как ожидалось. Это часто означает, что тесты могут длиться долгое время, прежде чем в конечном итоге не выдадут ошибку. Это также приводит к тому, что требуется большое количество времени для усовершенствований и исправлений ETL.
УЛУЧШЕНИЕ СИСТЕМ ETL С ПОМОЩЬЮ DLM
Рассмотрим шаги, которые можно предпринять, чтобы улучшить процессы ETL и установить следующие постулаты DLM:
• Прозрачность - процессы видны всем командам с самого начала проекта;
• Повторяемость - процессы автоматизированы, проверены и поэтому предсказуемы и повторяемы;
• Инкрементальное улучшение - небольшие улучшения происходят беспрерывно;
• Измеримость - процессы обрабатываются и регистрируются таким образом, чтобы ошибки и отклонения от предсказуемого поведения быстро корректировались.
Разделим на логические этапы процесс улучшения процессов ETL.
1. Документирование существующих знаний
Первая задача - собрать и сделать понятными для бизнеса всю существующую информацию о системе. У сотрудников существует статистика того, что происходит внутри системы. Зачастую у них есть отчеты о длительных операциях, которые они пытались оптимизировать. Это
хорошее начало, но необходимо убедиться, что это задокументировано. Как правило, можно найти несколько процессов, являющихся причиной серьезных проблем из-за их долгой работы. Часто эти процессы - не основная причина, но блокируются другими, запущенными параллельно. После нескольких итераций оптимизации система может быть улучшена. Следует обратить внимание на проблемные задачи и продолжительность их выполнения, что будет являться важной информацией.
2. Публикация показателей эффективности
Как можно раньше необходимо начать вводить базовый мониторинг для собранных данных о каждой крупной задачи, особенно когда начинается и заканчивается загрузка или имеются ошибки. Каждая задача должна иметь журнал начала и окончания. Это должно исходить от планировщика - агент SQL Server поддерживает это в таблицах истории, но эту информацию нужно извлечь в таблицу журналов для проверки. Если продолжительность каждой задачи ETL не контролируется, то команда не может обнаружить, что продолжительность работы ETL внезапно или постепенно со временем увеличивается. Разумеется, необходимо учитывать изменения объема данных, но такая мера, как «продолжительность на 1000 записей», - это то, что можно наблюдать с течением времени, чтобы отследить ухудшение производительности.
3. Предотвращение чрезмерной блокировки между задачами
Основной проблемой для систем ETL является то, что задачи
выполняются с разной периодичностью и продолжительностью. Некоторые из этих задач должны выполняться одновременно, чтобы завершить требуемую обработку в выделенном временном окне. Задачи извлечения могут выполняться каждые 5 минут в зависимости от требований к отчетности. Аналогично, задачи, связанные с преобразованием и загрузкой, должны будут выполняться по установленному графику. Часто разные задачи будут конкурировать за данные в одной таблице. Например, одновременно с импортом сегодняшних данных в реляционные таблицы в целевой базе данных другой процесс может считывать данные из этих же таблиц для экспорта в куб SSAS. При минимизации блокировок решающее значение имеет синхронизация и планирование различных задач.
4. Распараллеливание данных
Некоторые системы ETL требуют загрузки всех данных до начала обработки. Иногда это необходимо, но в ситуациях, когда импортируется несколько независимых наборов данных, эта проверка может быть излишне дорогостоящей, если проблема обнаружена на этапе получения данных.
Чтобы как можно быстрее обнаружить проблемы с входящими данными, необходимо распараллелить извлечение данных из независимых источников. При загрузке больших объемов данных из одного источника можно так же разбить этот файл данных на несколько меньших файлов и обрабатывать их параллельно.
Параллельное извлечение данных помогает команде использовать
возможности «быстрого выхода». В случае проблем они могут выбрать повторный запуск загрузки только неудавшегося набора данных или даже продолжить обработку ЕТЬ для остальной части данных, вместо того, чтобы принудительно повторно запускать весь импорт.
5. Предотвращение разрушения системы восходящими данными
Частые изменения формата данных приводят к тому, что ETL система становится непредсказуемой, что, в свою очередь, приводит к увеличению количества ошибок, а это - к тому, что данные становятся недоступны для последующих процессов.
Как правило, система ETL находится между системами, предоставляющими исходные данные, и системами, преобразующими данные в отчеты. Зачастую команда разработки ETL не берет в расчет интересы команд, строящих системы отчетности, чьи данные зависят от ETL. И если нижестоящая команда является сторонней - это нормальная ситуация, но если и ETL, и система отчётности находятся в рамках одной компании - такой подход является ошибочным.
С другой стороны, команда, которая поставляет данные для ETL, может, например, внести изменения в OLTP хранилище, что сразу нарушит процесс ETL. Если данные часто меняются, команде разработки ETL становится сложно отличать реальные ошибки от тех, которые вызваны изменениями данных, что приводит к бесконечному циклу поиска ошибок. Это является большой проблемой, когда поставщик данных - сторонняя компания, и она ожидает результат, не задумываясь о том, как и какие данные она передает.
Это огромная проблема, так как разработчики ЕТЬ теряют возможность улучшать качество процессов, тратя время на адаптацию под изменяющиеся данные и войну с ошибками, которые на самом деле вызваны этими самыми изменениями.
6. Стандартизация данных ETL
Для быстрого обнаружения ошибок в ЕТЬ необходимо наличие четких стандартов, по которым могут быть проверены входящие данные. Это является основным критерием. Большая часть работы по стандартизации должна быть выполнена на ранних этапах создания проекта. Необходимо идентифицировать потребителей данных на этапе планирования и согласовать стандарт на основе модели корпоративных данных.
7. Обеспечение проверки данных во время фазы чтения
Некоторые процессы ЕТЬ предполагают идеальные, чистые входные
данные и, следовательно, могут выдавать ошибки, если на этапах чтения или преобразования поступают некорректные данные. Однако ошибка может произойти через несколько минут, часов или даже дней на этапе обработки, что требует повторного запуска обработки после исправления вручную. Это неудачный подход.
Например, если известно, что в данных от одного поставщика часто приходят некорректные телефонные номера клиентов, тогда необходимо
создать явную проверку этого поля. Выявляя неисправные или проблемные данные на раннем этапе, система снижает время, которое требуется на исправление ошибок, происходит ли это в самой ETL или путем возврата данных к поставщику для корректировки. Если входящие данные содержат ошибки, обнаруженные на раннем этапе, можно шунтировать данные в очередь и выполнять другие задачи до тех пор, пока данные не будут исправлены. ВЫВОД
Главное преимущество внедрения ETL в жизненный цикл базы данных
— уменьшение вероятности упустить малозаметную ошибку. Если ответственно подходить к процессу управления, то можно создавать эффективные системы, основываясь на информации об источнике данных, ограничениях, объеме, скорости и требованиях к безопасности. С помощью ETL можно отслеживать ошибки, не тратя при этом больших сетевых ресурсов, ресурсов памяти и процессора, даже в случае увеличения рабочих нагрузок.
Если масштабировать систему ETL, не контролируя её структуру, то со временем процессы начнут выполняться хаотично, а следовательно начнут возникать непредвиденные ошибки. Однако, если следовать основным правилам построения ETL, можно сделать эффективную систему, которая будет работать предсказуемо до конца срока службы.
Использованные источники:
1. Малыхина, М. П. / Базы данных. Основы, проектирование, использование.
— СПб.: БХВ-Петербург, 2004. — 512 с.
2. David Loshin. ETL (Extract, Transform, Load) // Business Intelligence. — 2nd.
— Morgan Kaufmann, 2012. — 400 с.
3. David Haertzen. ETL Tools // The Analytical Puzzle: Profitable Data Warehousing, Business Intelligence and Analytics. — Technics Publications, 2012.
— 346 с.
4. Ralph Kimball, Joe Caserta. The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data. — John Wiley & Sons, 2004. — 528 с.
