CSV в SQL Server: кошмар массового импорта (T-SQL и / или Pandas)

Question

Я .CSVбезуспешно пытаюсь массово вставить файл в SQL Server.

Немного предыстории:

1. Мне нужно было вставить 16 миллионов записей в БД SQL Server (2017). Каждая запись имеет 130 столбцов. У меня есть поле в .CSVрезультате вызова API от одного из наших поставщиков, о котором я не могу упоминать. У меня были типы данных целые, с плавающей запятой и строки.

2. Я попробовал как обычно: BULK INSERTно мне не удалось передать ошибки типа данных. Я отправил вопрос здесь , но не мог заставить его работать.

3. Я пробовал экспериментировать с python и пробовал все методы, которые мог найти, но pandas.to_sqlвсе предупреждали, что это очень медленно. Я застрял с ошибками типа данных и усечения строки. В отличие от тех, что были из BULK INSERT.

4. Без особых опций я попробовал, pd.to_sqlи хотя он не вызывал ошибок типа данных или усечения, он терпел неудачу из-за нехватки места в моей базе данных tmp SQL. Я также не мог передать эту ошибку, хотя у меня было много места, и все мои файлы данных (и файлы журналов) были настроены на автоматический рост без ограничений.

На этом я застрял. Мой код (для pd.to_sqlкуска) был прост:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

df.to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)

Я не совсем уверен, что еще попробовать, любой совет приветствуется. Все коды и примеры, которые я видел, относятся к небольшим наборам данных (не многим столбцам). Я готов попробовать любой другой метод. Буду признателен за любые указатели.

Благодаря!

Answer 1

Я просто хотел поделиться этим грязным фрагментом кода на случай, если он кому-то поможет. Обратите внимание, что я прекрасно понимаю, что это совсем не оптимально, это медленно, но я смог вставить около 16 миллионов записей за десять минут, не перегружая мою машину.

Я пробовал делать это небольшими партиями:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

a = 1
b = 1001

while b <= len(df):
    try:
        df[a:b].to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)
        a = b + 1
        b = b + 1000
    except:
        print(f'Error between {a} and {b}')
        continue

Урод, черт возьми, но работал на меня.

Я открыт для всех критиков и советов. Как я уже упоминал, я публикую это на случай, если это поможет кому-то еще, но также с нетерпением жду конструктивных отзывов.

Answer 2

Загрузка данных из фрейма данных pandas в базу данных SQL происходит очень медленно, и при работе с большими наборами данных нехватка памяти является обычным случаем. Вам нужно что-то более эффективное при работе с большими наборами данных.

d6tstack - это то, что может решить ваши проблемы. Потому что он работает с собственными командами импорта БД. Это настраиваемая библиотека, специально созданная для работы со схемой, а также с проблемами производительности. Работает с XLS, CSV, TXT, которые можно экспортировать в CSV, Parquet, SQL и Pandas.

Answer 3

Я считаю, df.to_sqlэто круто! В последнее время я его много использую. Это немного медленно, когда наборы данных действительно огромны. Если вам нужна скорость, я думаю, что Bulk Insert будет самым быстрым вариантом. Вы даже можете выполнять работу партиями, чтобы не исчерпать память и, возможно, перегружать вашу машину.

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT  OurTable 
FROM 'c:\OurTable.txt' 
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', 
   ROWS_PER_BATCH = 10000, TABLOCK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH