Що таке ETL?
ETL — це процес, який витягує дані з кількох вихідних систем, змінює їх (через обчислення, конкатенації тощо), а потім поміщає в систему Data Warehouse. ETL означає Extract, Transform і Load.
Легко повірити, що створити сховище даних так само просто, як отримати дані з багатьох джерел і внести їх у базу даних сховища даних. Це далеко не так, і потрібна складна процедура ETL. Процес ETL, який є технічно складним, передбачає активну участь різноманітних зацікавлених сторін, включаючи розробників, аналітиків, тестувальників і керівників вищої ланки.
Щоб зберегти свою цінність як інструменту прийняття рішень, система сховища даних повинна розвиватися синхронно з розвитком бізнесу. ETL — це регулярний (щоденний, щотижневий, щомісячний) процес системи сховища даних, який має бути гнучким, автоматизованим і належним чином задокументованим.
Як працює ETL?
Тут ми крок за кроком дізнаємося, як працює процес ETL:
Крок 1) Екстракція
Дані витягуються з вихідної системи та поміщаються в проміжну область під час вилучення. Якщо потрібні будь-які перетворення, вони виконуються в проміжній області, щоб продуктивність вихідної системи не постраждала. Відкат буде складним, якщо пошкоджені дані передаються безпосередньо з джерела в базу даних сховища даних. Перш ніж перемістити витягнуті дані до сховища даних, їх можна перевірити в проміжній області.
Сховища даних можуть поєднувати системи з різним апаратним забезпеченням, системами керування базами даних, операційними системами та протоколами зв’язку. Сховища даних повинні поєднувати системи з різнорідними СУБД, обладнанням, операційними системами та протоколами зв’язку. Джерела можуть включати застарілі програми, такі як мейнфрейми, спеціальні програми, пристрої точки контакту, такі як банкомати та комутатори викликів, текстові файли, електронні таблиці, ERP, дані від постачальників і партнерів тощо.
Таким чином, перед витягуванням даних і їх фізичним завантаженням потрібна логічна карта даних. Зв’язок між джерелами та цільовими даними показано на цій карті даних.
Три методи вилучення даних:
- Часткове вилучення – Якщо вихідна система сповіщає вас про зміну запису, це найпростіший спосіб отримати дані.
- Часткове вилучення (без сповіщення про оновлення) – Не всі системи можуть надсилати сповіщення про оновлення; але вони можуть вказувати на записи, які були змінені, і забезпечувати вилучення цих записів.
- Повний витяг – Деякі системи нездатні визначити, які дані взагалі були змінені. У цьому сценарії єдиний спосіб отримати дані з системи — виконати повне вилучення. Цей підхід вимагає наявності резервної копії попереднього витягу в
той самий формат, щоб визначити внесені зміни.
Незалежно від обраного методу вилучення не повинно впливати на продуктивність або час відгуку вихідних систем. Це виробничі бази даних реального часу. Будь-яке уповільнення або блокування може вплинути на прибутки компанії.
Крок 2) Трансформація
Дані, отримані з вихідного сервера, є необробленими та непридатними для використання у вихідному стані. У результаті його потрібно очистити, відобразити та трансформувати. Насправді це ключовий крок, на якому процес ETL додає цінність і перетворює дані для створення значущих звітів BI.
Це ключова концепція ETL, у якій ви застосовуєте набір функцій до витягнутих даних. Прямий хід or проходити через датa — тип даних, який не потребує жодного перетворення.
Ви можете виконувати налаштовані операції над даними на етапі трансформації. Наприклад, припустімо, що клієнту потрібна сума доходу від продажів, якої немає в базі даних. або якщо ім’я та прізвище в таблиці знаходяться в окремих стовпцях. Перед завантаженням їх можна об’єднати.
Нижче наведено кілька прикладів проблем із цілісністю даних.
- Різні варіанти написання однієї особи, як-от Prashant, Parshant тощо.
- Є багато способів представити назву компанії, наприклад Google, Google Inc.
- Використовуються різні назви, такі як Клівленд і Клівленд.
- Можливо, різні програми створюють кілька номерів рахунків для одного клієнта.
- Деякі необхідні файли даних залишаються порожніми.
Крок 3) Завантаження
Останнім етапом процесу ETL є завантаження даних у цільову базу даних сховища даних. У типове сховище даних за відносно короткий проміжок часу завантажується великий обсяг даних. У результаті процес завантаження має бути оптимізовано для підвищення продуктивності.
У разі збою завантаження слід запровадити процедури відновлення, щоб операції можна було перезапустити з точки збою без шкоди для цілісності даних. Адміністратори сховища даних повинні відстежувати, продовжувати та зупиняти завантаження на основі продуктивності сервера.
Види навантаження:
- Початкове навантаження — заповнення всього
таблиці сховища даних - Інкрементне навантаження — триває реалізація
регулярні зміни за потреби - Повне оновлення — очищення вмісту
однієї або кількох таблиць і перезавантаження їх свіжими даними
Перевірка навантаження
- Перевірте, чи дані ключового поля не відсутні або не є нульовими.
- Необхідно протестувати представлення моделювання на основі цільових таблиць.
- Вивчіть комбіновані значення3 та обчислені показники.
- Перевірки даних у таблицях розмірності та історії.
- Перегляньте звіти BI у завантаженій таблиці фактів і розмірів.
Налаштування ETL за допомогою PythonScript
Як наслідок, ви повинні виконати базове Extract Transform Load (ETL) із кількох баз даних до сховища даних, щоб агрегувати дані для бізнес-аналітики. Є кілька доступних пакетів ETL, які, на вашу думку, були надмірними для вашого базового випадку використання.
У цій статті я покажу вам, як отримати дані з MySQL, SQL-сервера та firebird. Використовуючи Python 3.6, трансформуйте дані та завантажте їх на SQL-сервер (сховище даних).
Перш за все, ми повинні створити каталог для нашого проекту:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Щоб налаштувати ETL за допомогою Python, вам потрібно створити такі файли в каталозі проекту.
- db_credentials.py: Повинен мати всю інформацію, необхідну для підключення до всіх баз даних. наприклад пароль бази даних, номер порту тощо.
- sql_queries.py: Мають бути доступні всі типові запити до бази даних для отримання та завантаження даних у форматі String.
- etl.py: Підключіться до бази даних і зробіть необхідні запити, виконавши всі необхідні процедури.
- main.py: Відповідає за управління потоком операцій і виконання основних операцій у встановленому порядку.
У цьому розділі sql_queries.py це місце, де ми збираємося зберігати всі наші запити sql для вилучення з вихідних баз даних та імпорту в нашу цільову базу даних (сховище даних)
Налаштуйте облікові дані та змінні бази даних
У variables.py створіть змінну для запису назви бази.
datawarehouse_name = 'ваше_datawarehouse_name'
Налаштуйте всі рядки підключення вихідної та цільової бази даних і облікові дані в db_credentials.py, як показано нижче. Збережіть конфігурацію як список, щоб ми могли згодом повторити її, коли буде потрібно, через багато баз даних.
зі змінних імпортувати datawarehouse_name datawarehouse_name = 'ваше_datawarehouse_name' # sql-сервер (цільова база даних, сховище даних) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', 'database': '{}'.format(datawarehouse_name), 'user': 'your_db_username ', 'password': 'your_db_password', 'autocommit': True, } # sql-сервер (вихідна база даних) sqlserver_db_config = [ { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password' : 'your_db_password', 'autocommit': True, } ] # mysql (вихідна база даних) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', }, { 'user': 'your_user_2', 'password' : 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (джерело db) fdb_db_config = [ { 'dsn': "/your/path/to/source.db", 'user': "your_username", 'password': "your_password", } ]
Запити SQL
У цьому розділі sql_queries.py це місце, де ми збираємося зберігати всі наші sql-запити для вилучення з вихідних баз даних та імпорту в нашу цільову базу даних (сховище даних).
Ми маємо реалізувати різні синтаксиси для кожної бази даних, оскільки ми працюємо з кількома платформами даних. Ми можемо зробити це, розділивши запити на основі типу бази даних.
# приклади запитів відрізнятимуться на різних платформах бази даних firebird_extract = (''' ВИБРАТИ fbd_column_1, fbd_column_2, fbd_column_3 З fbd_table; ''') firebird_insert = (''' ВСТАВИТИ В таблицю (стовпець_1, стовпець_2, стовпець_3) ЗНАЧЕННЯ (?, ?, ?) ''') firebird_extract_2 = (''' ВИБРАТИ fbd_column_1, fbd_column_2, fbd_column_3 З fbd_table_2; ''') firebird_insert_2 = ('''' ВСТАВИТИ В таблицю_2 (стовпець_1, стовпець_2, стовпець_3) ЗНАЧЕННЯ (?, ?, ?) ''') sqlserver_extract = (''' ВИБРАТИ sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 З sqlserver_table ''') sqlserver_insert = (''' ВСТАВИТИ В таблицю (стовпець_1, стовпець_2, стовпець_3) ЗНАЧЕННЯ (?, ?, ?) ''') mysql_extract = (''' ВИБРАТИ mysql_column_1, mysql_column_2, mysql_column_3 З mysql_table ''') mysql_insert = (''' ВСТАВИТИ В таблицю (стовпець_1, стовпець_2, стовпець_3) ЗНАЧЕННЯ (?, ?, ?) ''') # експорт запитів class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # створити екземпляри для класу SqlQuery fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query = SqlQuery(sqlserver_extract, sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # зберегти як список для ітерації fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Витягти трансформаційне навантаження
Щоб налаштувати ETL за допомогою Python для вищезгаданих джерел даних, вам знадобляться наступні модулі:
# модулі python import mysql.connector import pyodbc import fdb # змінні з імпорту змінних datawarehouse_name
Для цього ми можемо використовувати дві техніки: etl() і etl_process().
etl_process() — це процедура для встановлення з’єднання з джерелом бази даних і виклику методу etl() на основі платформи бази даних.
А в другому методі, який є методом etl(), він спочатку виконує запит на вилучення, потім зберігає дані SQL у змінних даних і вставляє їх у цільову базу даних, яка є нашим сховищем даних. Перетворення даних може бути здійснене шляхом зміни змінної даних кортежу типу.
def etl(query, source_cnx, target_cnx): # отримати дані з вихідної бази даних source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # завантажити дані в базу даних сховища, якщо data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('data loaded to warehouse db') target_cursor.close() else : print('дані порожні') def etl_process(queries, target_cnx, source_db_config, db_platform): # встановити з’єднання з джерельною базою даних, якщо db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver': source_cnx = pyodbc.connect (**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: return 'Помилка! unrecognized db platform' # цикл через sql-запити для запиту в запитах: etl(query, source_cnx, target_cnx) # закрийте з'єднання з вихідною базою даних source_cnx.close()
З’єднати все разом
Тепер, на наступному кроці, ми можемо перейти до всіх облікових даних у main.py та виконати etl для всіх баз даних.
Для цього нам потрібно імпортувати всі необхідні змінні та методи:
# змінні з db_credentials імпорт datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config з sql_queries імпорт fbd_queries, sqlserver_queries, mysql_queries з імпорт змінних * # методи з etl імпорт etl_process
Код у цьому файлі відповідає за перебір облікових даних для підключення до бази даних і виконання необхідних операцій ETL за допомогою Python.
def main(): print('starting etl') # встановити з'єднання для цільової бази даних (sql-сервер) target_cnx = pyodbc.connect(**datawarehouse_db_config) # цикл через облікові дані # mysql для конфігурації в mysql_db_config: try: print("loading db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql')крім винятків як помилка: print("etl для {} має помилку".format(config['database'])) друку ('error message: {}'.format(error)) continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') крім винятку як помилка: print("etl для {} має помилку".format(config['database'])) print('повідомлення про помилку: {}'.format(error)) continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird')крім винятків як помилка: print("etl для {} має помилку".format(config ['database'])) print('повідомлення про помилку: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
У вашому терміналі введіть python main.py, і ви щойно створили ETL за допомогою чистого сценарію python.
Інструменти ETL
На ринку є кілька інструментів для зберігання даних. Ось кілька найвідоміших прикладів:
1. MarkLogic:
MarkLogic — це система сховища даних, яка використовує низку бізнес-можливостей для полегшення та прискорення інтеграції даних. Він може запитувати багато видів даних, таких як документи, зв’язки та метадані.
https://www.marklogic.com/product/getting-started/
2. Оракул:
Oracle є найпопулярнішою базою даних у галузі. Він пропонує широкий спектр рішень для сховищ даних як для локальних, так і для хмарних служб. Це допомагає покращити взаємодію з клієнтами, підвищуючи ефективність роботи.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift — це рішення для зберігання даних від Amazon. Це просте та економічно ефективне рішення для аналізу різних типів даних за допомогою стандартного SQL та існуючих інструментів бізнес-аналітики. Це також дозволяє виконувати складні запити до петабайтів структурованих даних.
https://aws.amazon.com/redshift/?nc2=h_m1
Висновок
Ця стаття дала вам глибоке розуміння того, що таке ETL, а також покроковий підручник, як налаштувати ETL у Python. Він також надав вам список найкращих інструментів, які сьогодні використовує більшість організацій для побудови своїх каналів даних ETL.
З іншого боку, більшість сучасних організацій мають величезну кількість даних із високодинамічною структурою. Створення конвеєра ETL з нуля для таких даних є складною процедурою, оскільки організаціям доведеться використовувати велику кількість ресурсів, щоб створити цей конвеєр, а потім забезпечити, щоб він міг йти в ногу з великим обсягом даних і змінами схеми.
про автора
Прашант Шарма
Зараз я здобуваю ступінь бакалавра технологій (B.Tech) у Веллорському технологічному інституті. Я з великим ентузіазмом ставлюся до програмування та його реальних застосувань, включаючи розробку програмного забезпечення, машинне навчання, глибоке навчання та науку про дані.
Сподіваюся, вам сподобалася стаття. Якщо ви хочете зв'язатися зі мною, ви можете зв'язатися на:
або для будь-яких інших сумнівів, ви можете відправити лист мені також
споріднений
- '
- "
- рахунки
- активний
- ВСІ
- Amazon
- аналітика
- застосування
- ПЛОЩА
- стаття
- Автоматизований
- резервна копія
- підвищення
- будувати
- Створюємо
- бізнес
- бізнес-аналітика
- call
- Перевірки
- Клівленд
- хмара
- хмарні сервіси
- код
- Комунікація
- компанія
- зв'язку
- продовжувати
- створення
- Повноваження
- дані
- інтеграція даних
- наука про дані
- сховище даних
- сховища даних
- Database
- базами даних
- глибоке навчання
- розвивати
- розробників
- розробка
- прилади
- Розмір
- документація
- водій
- ефективність
- і т.д.
- виконання
- керівництво
- Досліди
- видобуток
- Виписки
- Провал
- Перший
- потік
- формат
- свіжий
- Повний
- керівництво
- апаратні засоби
- тут
- Високий
- історія
- Як
- How To
- HTTPS
- ідентифікувати
- Impact
- імпорт
- У тому числі
- інформація
- інтеграція
- Інтелект
- питання
- IT
- ключ
- великий
- УЧИТЬСЯ
- вивчення
- Лінія
- список
- загрузка
- навчання за допомогою машини
- управління
- карта
- ринок
- Медіа
- Найбільш популярний
- рухатися
- Імена
- сповіщення
- номера
- Пропозиції
- операційний
- операційні системи
- операції
- оракул
- порядок
- організації
- Інше
- партнери
- Пароль
- продуктивність
- платформа
- Платформи
- популярний
- Вироблений
- Production
- Програмування
- програми
- проект
- тягне
- Python
- Сировина
- реального часу
- Реальність
- облік
- відновлення
- Відносини
- Звіти
- ресурси
- відповідь
- revenue
- наука
- Послуги
- комплект
- Короткий
- простий
- So
- Софтвер
- розробка програмного забезпечення
- Рішення
- SQL
- Стажування
- стан
- зберігати
- магазинів
- система
- Systems
- Мета
- технології
- методи
- Технологія
- Джерело
- час
- Перетворення
- підручник
- Оновити
- значення
- постачальники
- обсяг
- Склад
- Складування
- тижні
- Work
- працює