Extract, Transform, Load (ETL) або Витяг, Перетворення та Завантаження — процес, який використовується в базах даних та, особливо, у сховищах даних та у засобах Business Intelligence для забезпечення їх роботи для підтримки прийняття рішень. ETL-процес, як концепція, набув поширення у 1970-х рокахGallas, Susan. Kimball vs. Inmon, September 1999, dmDirect [Електронний ресурс] — 12.09.2007 -Режим доступу: http://www.dmreview.com/editorial/dmdirect/ dmdirect_article.cfm?EdID=1400&issue.. Він охоплює наступні етапи обробки даних:
із зовнішніх джерел, Перетворення даних, для зберігання даних у відповідній структурі або форматі, з метою подальшого аналізу. Завантаження даних у кінцеву базу даних. Більш точно, це може бути вітрина даних або сховище даних.
Поняття ETL може стосуватися процесу завантаження будь-якої бази даних. Оскільки виймання даних займає багато часу, то для скорочення загального часу обробки, поширеним є одночасна робота всіх трьох етапів ETL. Поки дані виймаються, процес перетворення отримує інші дані і готує їх для завантаження, щоб уникнути очікування виконання попередніх етапів.
Зазвичай ETL системи об'єднують дані з численних застосунків (систем), які створені та підтримуються різними вендорами та розміщені на різному апаратному забезпеченні. Розрізнені системи, які містять первісні дані, нерідко підтримуються та використовуються різними співробітниками. Для прикладу, система обліку витрат може об'єднувати дані по фонду заробітної платні, продажам та придбанням.
Виймання Перша частина процесу ETL передбачає виймання даних з джерельних (або вихідних) систем. У багатьох випадках це є найважливішим аспектом ETL, оскільки правильне виймання даних необхідне для успішного функціонування наступних процесів. Більшість сховищ даних об'єднують дані з різних джерельних систем. Кожна окрема система може також використовувати іншу організацію даних та/або формат файлів. Загальні джерела даних форматів включають реляційні бази даних, XML, JSON і плоскі бази даних, але можуть також включати структури не реляційної бази даних, такі, як або інші структури даних, такі, як або , або навіть формати, отримані з зовнішніх джерел за допомогою таких засобів, як пошуковий робот або . Потокове відтворення вихідного джерела даних та завантаження на льоту в цільову базу даних є ще одним способом здійснення ETL, коли не вимагається проміжного зберігання даних. Загалом, фаза виймання спрямована на перетворення даних в єдиний формат, який потрібен на наступному кроці перетворення інформації.
Внутрішня частина виймання передбачає перевірку даних, щоб підтвердити, чи дані, отримані з джерел, мають правильні/очікувані значення в певному домені (наприклад, шаблонні/за умовчанням дані або список значень). Якщо дані не відповідають правилам перевірки, вони повністю або частково відхиляються. Відкинуті дані в ідеальному варіанті відправляються назад у джерельну систему для подальшого аналізу для виявлення та виправлення невірних записів. У деяких випадках сам процес виймання може мати правило перевірки даних, щоб прийняти дані та перейти на наступний етап.
Перетворення На етапі перетворення даних застосовується серія правил або функцій до здобутих даних для підготовки цих даних до досягнення кінцевої цілі. Деякі дані взагалі не потребують перетворення; такі дані як «пряме переміщення» або «проходження крізь» дані.
Важливою функцією перетворення є очищення даних, яке має на меті передавати лише «правильні» дані. Виклик при взаємодії різних систем полягає у взаємодії відповідних систем. Набори символів, які можуть бути доступними в одній системі, можуть бути недоступними у інших системах.
В інших випадках, для задоволення бізнес та технічних потреб сервера або сховища даних може знадобитися одне або декілька наступних типів перетворень: Вибір лише певних стовпців для завантаження: (або вибір null (недійсних) стовпчиків для завантаження). Наприклад, якщо вихідні дані мають три стовпці (наприклад, «attributes»), roll_no, вік та зарплата, то вибір може мати лише roll_no та зарплату. Або механізм вибору може ігнорувати всі ті записи, де немає зарплати (salary = null). Переклад закодованих значень: (наприклад, якщо вихідна система кодів чоловіків помічається як «1» та жінок — як «2», але warehouse коди чоловіків як «Ч» і жінок як «Ж») Значення вільної форми кодування: (наприклад, відображення «Чоловік» для «Ч») Виведення нової розрахункової вартості: (наприклад, sale_amount = qty * unit_price) Сортування даних на основі списку стовпців для покращення ефективності пошуку Об'єднання даних із кількох джерел (наприклад, пошуку, злиття) та Агрегація (наприклад, rollup — узагальнення кількох рядків даних — загальний обсяг продажів для кожного магазину, і для кожного регіону тощо) Створення значень сурогатних ключів Транспонування або поворот (перетворення кількох стовпців на кілька рядків або навпаки) Розбиття стовпця на кілька стовпців (наприклад, перетворення CSV списку розділеного комами, вказаного як рядок в одному стовпчику, в окремі значення в різних стовпцях). Розбиття повторюваних стовпців Пошук та перевірка відповідних даних з таблиць або реферованих файлів Застосування будь-якої форми перевірки даних; невдала перевірка може призвести до повного відхилення даних, часткового відхилення або відсутності відмови взагалі, і таким чином ніякі, деякі або всі дані не передаються на наступний крок залежно від розробки правил та обробки винятків; багато з перерахованих вище перетворень можуть призвести до винятків, наприклад, коли кодовий переклад аналізує невідомий код у розширеному вигляді
Завантаження Фаза завантаження завантажує дані в кінцеву ціль, яка може бути простим обмеженим плоским файлом або сховищем даних. Залежно від потреб організації, цей процес дуже різниться. Деякі сховища даних можуть перезаписувати існуючу інформацію з сукупною інформацією; оновлення витягнутих даних часто проводиться щоденно, щотижнево або щомісячно. Інші сховища даних (або навіть інші частини одного і того ж сховища даних) можуть додавати нові дані в історичну форму через регулярні інтервали, наприклад, щогодини. Щоб зрозуміти це, розгляньте сховище даних, необхідне для ведення обліку продажів минулого року. Це сховище даних перезаписує будь-які дані старше року новими даними. Проте введення даних для будь-якого вікна на один рік здійснюється історично. Час і обсяг заміни чи додавання — це вибір стратегічного дизайну залежно від наявного часу та потреб бізнесу. Більш складні системи можуть підтримувати історію та у даних, завантажених у сховище даних.
Оскільки фаза завантаження взаємодіє з базою даних, застосовуються обмеження, визначені в схемі бази даних, також як тригери активовані під час завантаження даних (наприклад, унікальність, посилальна цілісність, обов'язкові поля), що також сприяють загальній якості даних процесу ETL. Наприклад, фінансова установа може мати інформацію про клієнта в декількох відділах, і кожен відділ може мати інформацію про цього клієнта різним чином. Відділ членства може вказати клієнта за ім'ям, тоді як бухгалтерія може вказати клієнта за номером. ETL може поєднувати всі ці елементи даних та об'єднувати їх у єдину презентацію, наприклад, для зберігання в базі даних або сховищі даних. Інший спосіб, коли компанія використовує ETL, — це постійне переміщення інформації до іншої програми. Наприклад, нова програма може використовувати інший постачальник бази даних і, найімовірніше, зовсім іншу схему бази даних. ETL може бути використаний для перетворення даних у формат, придатний для використання новою програмою. Прикладом може бути , яка використовується бухгалтерським обліком, консультантами та юридичними фірмами. Дані, як правило, потрапляють у , хоча деякі підприємства також можуть використовувати вихідні дані для звітів про продуктивність працівників для управління людськими ресурсами (відділом персоналу) або звітів про використання обладнання.
Реальний цикл ETL Типовий цикл реального життя ETL складається з наступних кроків виконання:
Початок циклу Створення Витяг (з джерел) Перевірка Трансформування (, застосування , перевірка цілісності інформації, створення або дезагрегації) Стадія (завантаження в , якщо використовуються) Аудиторські звіти (наприклад, про дотримання ділових правил, а також у випадку несправності, допомагає діагностувати / відновлювати) Опублікування (для цільових таблиць) Архівування
Виклики Процеси ETL можуть задіяти значну складність, і можуть виникнути серйозні проблеми завдяки системам ETL, що було розроблено неналежним чином.
Діапазон значень даних або якість даних в операційній системі може перевищувати очікування дизайнерів у процесі перевірки правильності та виконанні правил перетворення. Профілювання даних джерелом під час аналізу даних може визначати умови даних, які повинні управляти специфікаціями правил перетворення, що призводить до внесення поправок до правил валідації, явно та неявно впроваджених у процесі ETL.
Сховища даних зазвичай збираються з різних джерел даних з різними форматами та цілями. Таким чином, ETL є ключовим процесом для об'єднання всіх даних у стандартному, однорідному середовищі.
Аналіз дизайну повинен визначати масштабованість системи ETL протягом усього терміну її використання, включаючи розуміння обсягів даних, які повинні оброблятися в рамках угод про рівень послуг. Час, доступний для витягування із вихідних систем, може змінюватися, що може означати, що однакова кількість даних може бути оброблена за менший час. Деякі системи ETL повинні масштабувати настільки, щоб обробляти терабайти даних для оновлення сховищ даних з десятками терабайтів даних. Збільшуючи обсяги даних, може знадобитися конструкція, яка може масштабуватись від щоденної партії до багатоденної мікро-партії до інтеграції з черги повідомлень або захопленням змін даних у режимі реального часу для постійного перетворення та оновлення.
Продуктивність Продуктивність ETL-постачальники порівнюють свої реєстрові системи з кількома ТБ (в терабайтах) на годину (або ~ 1 Гб в секунду) за допомогою потужних серверів з декількома процесорами, кількома жорсткими дисками, декількома гігабітними мережевими з'єднаннями та великою кількістю пам'яті.
У реальному житті найнижча частина процесу ETL зазвичай відбувається у фазі завантаження бази даних. Бази даних можуть виконуватися повільно, оскільки вони повинні дбати про паралелі, підтримці цілісності та індексах. Таким чином, для підвищення продуктивності, може бути сенс використовувати: Пряме виймання або метод розвантаження, коли це можливо (замість запитів до бази даних), щоб зменшити навантаження на вихідну систему при отриманні високошвидкісного виймання Більшість обробки перетворень поза межами бази даних Обмеження навантаження, коли це можливо Тим не менше, навіть за допомогою масових операцій, доступ до бази даних зазвичай є вузьким місцем у процесі ETL. Деякі загальні методи, які використовуються для підвищення продуктивності, є: Таблиці розділів (та індекси): спробуйте зберегти подібні розміри розділів (дивіться нульові значення, які можуть перекосити розділи). Проведіть всю перевірку в шасі ETL перед завантаженням: вимкніть перевірку цілісності (вимкніть обмеження) у таблицях цільових баз під час завантаження Відключити тригери (вимкнути тригер) у таблицях цільових баз під час завантаження: імітувати їх ефект як окремий крок Створення ідентифікаторів на рівні ETL (не в базі даних) Видалити індекси (у таблиці або розділі) перед завантаженням — і відтворити їх після завантаження (SQL: drop index; create index) Використовуйте паралельний об'ємне навантаження, коли можливо — добре працює, коли таблиця розділена або немає індексів (Примітка: спроба здійснення паралельних навантажень в одну таблицю (розділ) зазвичай призводить до блокування — якщо не на рядках даних, то на показники)