Какой оптимальный вариант переливки миллионов строк из одной БД в другую, используя Pandas?

Question

[kukarekuu]

Есть задача - перелив нескольких таблиц (~10 млн. строк каждая / ~ 20 столбцов) из MSSQL в Postgres. Сейчас я переливаю их с помощью python+pandas.
Переливаю стандартно: читаю из MSSQL (pd.read_sql()), и получившийся DataFrame лью в другую базу(pd.to_sql()). Способ рабочий, но работает, на мой взгляд, довольно долго - около полутора часов на перелив 10 млн. записей (сервера MSSQL и Postgres находятся на разных машинах, но в одной сети).
Думал в сторону многопоточности, и даже реализовал многопоточность в том случае, если требуется перелив из нескольких CSV в базу.
Но в случае перелива из БД в БД никак не выходит применить многопоточность.
Есть какие-нибудь библиотеки или методы в pandas, с помощью которых можно было бы ускорить это дело?
Либо, быть может, кто-то успешно реализовал многопоточность в такой задаче?

Comments

[MechanicZelenyy]

У вас как мне кажется плохо с понимаем где находится узкое место вашей задачи: многопоточность вам никак не поможет потому что у вас основное время это операции ввода/вывода.

Answer 1

[Константин Цветков]

Оптимально использовать "Импорт и экспорт данных SQL Server". Документация.

Comments

[kukarekuu]

Хочется пользоваться всеми возможностями pandas + python.

[sim3x]

kukarekuu, Зачем?

[kukarekuu]

sim3x, pandas+python - штука универсальная, не хочется сильно уходить в сторону от этой экосистемы, много наработок сделано уже именно в этой связке
Если она позволит быстро грузить данные в этой задаче, то хотелось бы не тратить время на изучение ещё одного стека технологий

[sim3x]

kukarekuu,

Нет, данная связка не позволит грузить данные быстрее, чем спец решения для СУБД

[bl]

pandas - в общем, это надстройка-обёртка над некоторыми стандартными модулями (в данном случае - коннекторами к базам данных, транслирующим запросы на чистый SQL-код), которая при этом конвертирует все данные в свой pandas-формат.
Поэтому все расходы - это именно промежуточное создание объектов в pandas. Уберите pandas и пишите на обычном питоне с коннекторами к базам данных. - скорость возрастёт в разы. Или уберите питон и делайте дампы из командной строки, вся операция может сократится до минуты.
Если задача одноразовая - проще подождать.

[kukarekuu]

bl, не согласен
pandas, как и используемый им numpy, написан на С, то бишь работает он быстрее
а на чистом питоне будет медленнее, потому как это интерпретируемый язык высокого уровня
вопрос в том, может ли библиотека, написанная на С, быть быстрее, чем стандартные модули СУБД?

[bl]

kukarekuu, pandas и numpy далеко не полностью написаны на Си. Посмотрите модуль pandas, объекты создаются как обычно в питоне. Замерьте, сравните.

[bl]

kukarekuu, вот обычное подключение БД к pandas, используется стандартный модуль sqlite3. Разве нет?

import pandas as pd
import sqlite3
con = sqlite3.connect("data/weather_2012.sqlite")
df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con)

[bl]

kukarekuu, кстати, ядро Питона и стандартные модули тоже скомпилированы. Например, посмотрите что в `import sqlite3`.

[kukarekuu]

bl, да, многое в pandas - это чисто питон, но ключевые вещи (в первую очередь векторная обработка массивов данных) написана на С, что и дает возможности по быстрой обработке данных.

"вот обычное подключение БД к pandas, используется стандартный модуль sqlite3. Разве нет?"

sqlite - в том числе, так же можно пользовать sqlalchemy
Я думаю, что pandas по скорости примерно равен SQL-механизмам (что и подтверждают тесты), но в нем лично мне намного удобнее делать in-memory вычисления , и, что немаловажно, есть питоновские методы распараллеливания процессов, которыми, насколько я знаю, нельзя пользоваться средствами СУБД. А распараллеливание может принести неплохой выигрыш на мощных ПК
В общем, вопрос опять сводится к multiprocessing при SQL коннектах в pandas :)

[d-stream]

kukarekuu,

pandas+python - штука универсальная, не хочется сильно уходить в сторону от этой экосистемы, много наработок сделано уже именно в этой связке

при варианте MS SQL->Postgre vs MS SQL->нечто->Postgre - второй вариант по-любому проигрывает в силу появления еще одной сущности...