Что такое ETL?
ETL — это процесс, который извлекает данные из нескольких исходных систем, изменяет их (посредством вычислений, конкатенации и т. д.), а затем помещает в систему хранилища данных. ETL означает «Извлечение», «Преобразование» и «Загрузка».
Легко поверить, что создать хранилище данных так же просто, как получить данные из многочисленных источников и передать их в базу данных хранилища данных. Это далеко не так, и требуется сложная процедура ETL. Процесс ETL, который является технически сложным, предполагает активное участие различных заинтересованных сторон, включая разработчиков, аналитиков, тестировщиков и руководителей высшего звена.
Чтобы сохранить свою ценность как инструмента принятия решений, система хранилища данных должна развиваться синхронно с развитием бизнеса. ETL — это регулярный (ежедневный, еженедельный, ежемесячный) процесс системы хранилища данных, который должен быть гибким, автоматизированным и должным образом документированным.
Как работает ETL?
Здесь мы шаг за шагом узнаем, как работает процесс ETL:
Шаг 1) Извлечение
Данные извлекаются из исходной системы и помещаются в промежуточную область во время извлечения. Если требуются какие-либо преобразования, они выполняются в промежуточной области, чтобы не навредить производительности исходной системы. Откат будет затруднен, если поврежденные данные передаются напрямую из источника в базу данных Хранилища данных. Прежде чем перемещать извлеченные данные в хранилище данных, их можно проверить в промежуточной области.
Хранилища данных могут объединять системы с различным оборудованием, системами управления базами данных, операционными системами и протоколами связи. Хранилища данных должны объединять системы с разрозненными СУБД, аппаратным обеспечением, операционными системами и протоколами связи. Источники могут включать устаревшие программы, такие как мэйнфреймы, специализированные приложения, устройства точки контакта, такие как банкоматы и коммутаторы вызовов, текстовые файлы, электронные таблицы, ERP, данные от поставщиков и партнеров и т. д.
Таким образом, перед извлечением данных и их физической загрузкой требуется логическая карта данных. Связь между источниками и целевыми данными показана на этой карте данных.
Три метода извлечения данных:
- Частичное извлечение – Если исходная система предупреждает вас об изменении записи, это самый простой способ получить данные.
- Частичное извлечение (без уведомления об обновлении) – Не все системы могут доставлять уведомление при появлении обновления; но они могут указывать на записи, которые были изменены, и обеспечивать извлечение этих записей.
- Полный экстракт – Некоторые системы вообще не способны определить, какие данные были изменены. В этом случае единственный способ получить данные из системы — выполнить полное извлечение. Этот подход требует наличия резервной копии предыдущего экстракта в
того же формата, чтобы идентифицировать внесенные изменения.
Независимо от принятого метода извлечение не должно влиять на производительность или время отклика исходных систем. Это производственные базы данных реального времени. Любое замедление или блокировка могут повлиять на прибыль компании.
Шаг 2) Трансформация
Данные, полученные с исходного сервера, являются необработанными и непригодными для использования в исходном состоянии. В результате его необходимо очистить, нанести на карту и преобразовать. На самом деле это ключевой шаг, на котором процесс ETL повышает ценность и преобразует данные для создания содержательных отчетов BI.
Это ключевая концепция ETL, в которой вы применяете набор функций к извлеченным данным. Прямой ход or пройти через данныеa — тип данных, не требующий какого-либо преобразования.
Вы можете выполнять индивидуальные операции с данными на этапе преобразования. Например, предположим, что клиент хочет получить сумму дохода от продаж, которой нет в базе данных. или если имя и фамилия в таблице находятся в отдельных столбцах. Перед загрузкой их можно объединить.
Ниже приведены некоторые примеры проблем с целостностью данных:
- Разные варианты написания одного и того же человека, например Прашант, Паршант и т. д.
- Существует множество способов представления названия компании, например Google, Google Inc.
- Используются различные названия, такие как Кливленд и Кливленд.
- Возможно, что несколько номеров счетов создаются разными приложениями для одного и того же клиента.
- Некоторые файлы с необходимыми данными остаются пустыми.
Шаг 3) Загрузка
Заключительным этапом процесса ETL является загрузка данных в базу данных целевого хранилища данных. В типичное хранилище данных за относительно короткий период времени загружается большой объем данных. В результате процесс загрузки должен быть оптимизирован по производительности.
В случае сбоя загрузки должны быть предусмотрены процедуры восстановления, чтобы операции можно было возобновить с точки сбоя без ущерба для целостности данных. Администраторы хранилища данных должны отслеживать, продолжать и останавливать нагрузки в зависимости от производительности сервера.
Виды загрузки:
- Начальная нагрузка — заполнение всего
таблицы хранилища данных - Инкрементальная нагрузка — реализация текущих
модификации по мере необходимости на регулярной основе - Полное обновление — очистка содержимого
одной или нескольких таблиц и перезагрузка их свежими данными
Проверка загрузки
- Убедитесь, что данные ключевого поля не отсутствуют и не имеют значения NULL.
- Необходимо протестировать представления моделирования на основе целевых таблиц.
- Изучите объединенные значения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: Подключитесь к базе данных и выполните необходимые запросы, выполнив все необходимые процедуры.
- основной.py: Отвечает за управление потоком операций и выполнение основных операций в указанном порядке.
В этом разделе sql_queries.py это место, где мы собираемся хранить все наши sql-запросы для извлечения из исходных баз данных и импорта в нашу целевую базу данных (хранилище данных).
Настройка учетных данных и переменных базы данных
В файлеvariable.py создайте переменную для записи имени базы.
datawarehouse_name = 'ваше_имя_datawarehouse_name'
Настройте все строки подключения и учетные данные исходной и целевой базы данных в db_credentials.py, как показано ниже. Сохраните конфигурацию в виде списка, чтобы мы могли позже при необходимости повторять ее во многих базах данных.
из переменных import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (target db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' база данных': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'password': 'your_db_password', 'autocommit': True, } # sql-server (исходная база данных) sqlserver_db_config = [ { 'Trusted_Connection ': 'да', 'драйвер': '{SQL Server}', 'сервер': 'your_sql_server', 'база данных': 'db1', 'пользователь': 'your_db_username', 'пароль': '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 (source db) fdb_db_config = [ { 'dsn' : "/ваш/путь/к/source.db", 'user': "ваше_имя_пользователя", 'пароль': "ваш_пароль", } ]
SQL запросы
В этом разделе sql_queries.py это место, где мы собираемся хранить все наши sql-запросы для извлечения из исходных баз данных и импорта в нашу целевую базу данных (хранилище данных).
Нам приходится реализовывать различные синтаксисы для каждой базы данных, поскольку мы работаем с несколькими платформами данных. Мы можем сделать это, разделив запросы по типу базы данных.
# примеры запросов будут различаться на разных платформах БД firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO table (column_1,column_2,column_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' ВЫБЕРИТЕ fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1,column_2,column_3) ЗНАЧЕНИЯ (?, ?, ? ) ''') sqlserver_extract = (''' ВЫБЕРИТЕ sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1,column_2,column_3) VALUES (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1,column_2,column_3) VALUES (?, ?, ?) ''') # экспорт запросов class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # создаем экземпляры для SqlQuery class 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 # переменные из переменных import 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() # загружаем данные в базу данных хранилища if данные: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('данные загружены в базу данных хранилища') target_cursor.close() else : print('данные пусты') def etl_process(queries, target_cnx, source_db_config, db_platform): # устанавливаем соединение с исходной базой данных if 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 'Error! нераспознанная платформа базы данных' # циклически перебирать запросы sql для запроса в запросах: etl(query, source_cnx, target_cnx) # закрывать соединение с исходной базой данных source_cnx.close()
Собираем все вместе
Теперь, на следующем шаге, мы можем перебрать все учетные данные в main.py и выполнить etl для всех баз данных.
Для этого нам нужно импортировать все необходимые переменные и методы:
# переменные из db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config из sql_queries import fbd_queries, sqlserver_queries, mysql_queries из import * # методы из etl import 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 for {} имеет ошибку".format(config['database'])) print ('сообщение об ошибке: {}'.format(error)) continue # sql-server для конфигурации в sqlserver_db_config: попробуйте: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') кроме Исключения как ошибки: print("etl for {} имеет ошибку".format(config['database'])) print('сообщение об ошибке: {}'.format(error)) continue # firebird для конфигурации в fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') кроме исключения как ошибки: print("etl for {} имеет ошибку".format(config ['база данных'])) print('сообщение об ошибке: {}'.format(error)) continue target_cnx.close() if __name__ == "__main__": main()
В своем терминале введите python main.py, и вы только что создали ETL, используя чистый скрипт Python.
Инструменты ETL
На рынке существует несколько инструментов хранения данных. Вот некоторые из наиболее известных примеров:
1. МаркЛогик:
MarkLogic — это система хранения данных, которая использует ряд бизнес-возможностей для упрощения и ускорения интеграции данных. Он может запрашивать многие виды данных, такие как документы, связи и метаданные.
https://www.marklogic.com/product/getting-started/
2. Оракул:
Oracle — самая популярная база данных в отрасли. Он предлагает широкий спектр решений для хранилищ данных как для локальных, так и для облачных сервисов. Это помогает улучшить качество обслуживания клиентов за счет повышения операционной эффективности.
https://www.oracle.com/index.html
3. Амазон Редшифт:
Redshift — решение для хранения данных от Amazon. Это простое и экономичное решение для анализа различных видов данных с помощью стандартного SQL и существующих инструментов бизнес-аналитики. Это также позволяет выполнять сложные запросы к петабайтам структурированных данных.
https://aws.amazon.com/redshift/?nc2=h_m1
Заключение
Эта статья дала вам глубокое понимание того, что такое ETL, а также пошаговое руководство по настройке ETL в Python. Он также предоставил вам список лучших инструментов, которые большинство организаций в настоящее время используют для построения своих конвейеров данных ETL.
С другой стороны, большинство современных организаций имеют огромные объемы данных с очень динамичной структурой. Создание конвейера ETL с нуля для таких данных — сложная процедура, поскольку организациям придется использовать большое количество ресурсов для создания этого конвейера, а затем гарантировать, что он сможет справиться с большим объемом данных и изменениями схемы.
Об авторе
Прашант Шарма
В настоящее время я получаю степень бакалавра технологий (B.Tech) в Технологическом институте Веллора. Я с большим энтузиазмом отношусь к программированию и его реальным применениям, включая разработку программного обеспечения, машинное обучение, глубокое обучение и науку о данных.
Надеюсь, вам понравится статья. Если вы хотите связаться со мной, то вы можете подключиться:
или при любых других сомнениях вы можете отправить письмо мне тоже
Похожие страницы:
- '
- "
- Учетная запись
- активный
- Все
- Amazon
- аналитика
- Приложения
- ПЛОЩАДЬ
- гайд
- Автоматизированный
- Восстановление
- стимулирование
- строить
- Строительство
- бизнес
- бизнес-аналитика
- призывают
- Проверки
- Кливленд
- облако
- облачные сервисы
- код
- Связь
- Компания
- связи
- продолжать
- Создающий
- Полномочия
- данным
- Интеграция данных
- наука о данных
- информационное хранилище
- хранилища данных
- База данных
- базы данных
- глубокое обучение
- развивать
- застройщиков
- Разработка
- Устройства
- Размеры
- Документация
- водитель
- затрат
- и т.д
- выполнение
- руководителей высшего звена.
- Впечатления
- добыча
- Экстракты
- Ошибка
- First
- поток
- формат
- свежий
- полный
- инструкция
- Аппаратные средства
- здесь
- High
- история
- Как
- How To
- HTTPS
- определения
- Влияние
- импортирующий
- В том числе
- информация
- интеграции.
- Интеллекта
- вопросы
- IT
- Основные
- большой
- УЧИТЬСЯ
- изучение
- линия
- Список
- загрузка
- обучение с помощью машины
- управление
- карта
- рынок
- Медиа
- Самые популярные
- двигаться
- имена
- уведомление
- номера
- Предложения
- операционный
- операционные системы
- Операционный отдел
- оракул
- заказ
- организации
- Другие контрактные услуги
- партнеры
- Пароль
- производительность
- Платформа
- Платформы
- Популярное
- Произведенный
- Производство
- Программирование
- Программы
- Проект
- тянущий
- Питон
- Сырье
- реального времени
- Реальность
- учет
- выздоровление
- Отношения
- Отчеты
- Полезные ресурсы
- ответ
- доходы
- Наука
- Услуги
- набор
- Короткое
- просто
- So
- Software
- разработка программного обеспечения
- Решения
- SQL
- Этап
- Область
- магазин
- магазины
- система
- системы
- цель
- технологии
- снижения вреда
- Технологии
- Источник
- время
- трансформация
- учебник
- Обновление ПО
- ценностное
- поставщики
- объем
- Склады
- Складирование
- еженедельно
- Работа
- работает