Ускорение экспорта данных в MS SQL через pandas to_sql

Быстрый ответ

Ускорение экспорта данных из pandas в MS SQL можно достичь, используя функцию fast_executemany из SQLAlchemy. Она оптимизирует процедуру массовых вставок, значительно сокращая количество запросов к базе данных.

# Создаем скоростной движок — прекрасное решение для массовых вставок
engine = create_engine("mssql+pyodbc://user:pass@server/db?driver=SQL+Server", fast_executemany=True)

# Используем созданный движок для отправки DataFrame в MS SQL
df.to_sql('your_table', engine, if_exists='append', index=False)

Примечание: Проверьте, совместимы ли fast_executemany с pyodbc и ODBC-драйвером. При необходимости отредактируйте строку подключения под свои параметры.

Повышаем скорость экспорта с помощью метода 'multi' и опции разбиения на части

Использование метода multi в to_sql позволяет отправлять на сервер несколько строк одним запросом INSERT.

Количество операторов уменьшаем, скорость работы повышаем

df.to_sql('your_table', engine, if_exists='append', method='multi', index=False)

Pandas генерирует меньше операторов INSERT, за счет чего увеличивается производительность при работе с экспортом данных.

Подробнее об этом расскажет наш спикер на видео

Работаем с большими объемами данных? Используем частичную вставку!

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

# Настраиваем эффективную передачу данных
df.to_sql('your_table', engine, if_exists='append', method='multi', chunksize=1000, index=False)

Оптимальный размер каждой части данных определяется экспериментально.

Выполняем полную оптимизацию: работаем с типами данных, CSV и применяем специальные приемы

Эффективность начинаем с выбора типов данных

Выбирайте типы данных, которые не требуют больших ресурсов для обработки: например, INT вместо BIGINT и FLOAT вместо DECIMAL. Меньше не всегда значит хуже.

CSV и BULK INSERT: решение для работы с большими объемами данных

Если процесс обработки данных слишком медленный — попробуйте сначала экспортировать их в CSV, а затем с помощью BULK INSERT загрузить в SQL Server.

d6tstack: решение для тех, кто ценит время

С помощью инструмента d6tstack вы сможете использовать функцию pd_to_mssql, которая позволяет ускорить вставку данных из pandas.

Подготавливаем данные перед вставкой: предугадываем ситуацию

Использование функции apply_after_read из d6tstack поможет вам предварительно обработать данные и ускорить их вставку.

Максимальная производительность с помощью SQLAlchemy и pandas

SQLAlchemy: знания дают скорость

Следите за обновлениями в документации SQLAlchemy и извлекайте максимальную выгоду из функции 'fast_executemany' и других технологических новинок, которые увеличивают производительность.

Pandas: когда отставания нет

Используйте последнюю версию pandas, чтобы не пропустить обновления, повышающие производительность, а также новые возможности, вроде method='multi'.

Визуализация

Давайте визуализируем процесс экспорта данных в базу данных MS SQL:

До оптимизации:
🚰💧.....................(🎈)

Экспорт данных с использованием pandas по умолчанию сравним с капельным поливом — медленный и вялый

После оптимизации:

🚰🔧💦💨💥(🎈)

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

превращают процесс экспорта в дождь, который поливает густой сад — быстрый и эффективный!

В итоге мы получаем сверхбыстрый и высокоэффективный процесс экспорта данных. 🚀

Финальная стадия: настройка для максимальной производительности

Меряем процесс, чтобы его улучшить

Чтобы узнать время экспорта, используйте функцию time.time() или модуль timeit в Python и делайте выводы, исходя из количественных показателей.

Наши подключения максимально оптимизированы: настраиваем параметры под себя

Экспериментируйте с параметрами строки подключения create_engine для оптимальной нагрузки, настройки опций пулинга и таймаутов.

Избавляемся от ненужных данных в таблице

Установите index=False в to_sql, чтобы избежать создания лишнего индексного столбца в таблице SQL. Лишний индекс — это лишние проблемы в ваших запросах!

Полезные материалы

1. pandas.DataFrame.to_sql — документация pandas 2.2.0 — изучите документацию по методу to_sql pandas, предназначенному для экспорта данных в SQL.
2. Дополнительные техники сохранности — Документация SQLAlchemy 1.4 — улучшите свои навыки работы с массовыми операциями в SQLAlchemy.
3. Массовая вставка DataFrame pandas с использованием SQLAlchemy – Stack Overflow — дискуссия о совершенствовании метода to_sql.
4. Работа с большими наборами данных – Python Wiki — стратегии работы с большими объемами данных в Python и pandas.
5. Turbodbc – Ускоренный доступ к базам данных для аналитиков данных — последняя документация turbodbc — узнайте о преимуществах turbodbc для быстрого доступа к базам данных.
6. Обзор типов данных Pandas – Practical Business Python — полезное руководство по типам данных пакета pandas для оптимальной работы с большими данными.