CSV do SQL Server: koszmar importu zbiorczego (T-SQL i / lub Pandy)

Question

Próbuję zbiorczo wstawić .CSVplik do SQL Server bez większego sukcesu.

Trochę tła:

1. Musiałem wstawić 16 milionów rekordów do bazy danych SQL Server (2017). Każdy rekord ma 130 kolumn. Mam pole w .CSVwyniku wywołania API od jednego z naszych dostawców, o którym nie wolno mi wspominać. Miałem typy danych całkowite, zmiennoprzecinkowe i ciągi.

2. Próbowałem zwykle: BULK INSERTale nie mogłem przekazać błędów typu danych. Wysłałem tutaj pytanie , ale nie udało mi się.

3. Próbowałem eksperymentować z Pythonem i wypróbowałem każdą możliwą metodę, ale pandas.to_sqlwszyscy ostrzegali, że jest to bardzo powolne. Utknąłem z błędami związanymi z typem danych i obcięciem ciągu. Inaczej niż te z BULK INSERT.

4. Próbowałem bez wielu opcji pd.to_sqli chociaż nie powodowało to żadnych błędów typu danych ani obcinania, kończyło się niepowodzeniem z powodu braku miejsca w mojej bazie danych tmp SQL. Nie mogłem również przekazać tego błędu, chociaż miałem dużo miejsca, a wszystkie moje pliki danych (i pliki dziennika) były ustawione na autogrowth bez ograniczeń.

W tym momencie utknąłem. Mój kod (dla pd.to_sqlutworu) był prosty:

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)

Nie bardzo wiem, czego jeszcze spróbować, każda rada jest mile widziana. Wszystkie kody i przykłady, które widziałem, dotyczą małych zbiorów danych (niewiele kolumn). Chętnie spróbuję innej metody. Byłbym wdzięczny za wszelkie wskazówki.

Dzięki!

Answer 1

Chciałem tylko udostępnić ten brudny fragment kodu, na wypadek gdyby komuś to pomogło. Zauważ, że zdaję sobie sprawę, że to wcale nie jest optymalne, jest powolne, ale udało mi się wstawić około 16 milionów rekordów w dziesięć minut bez przeciążania mojej maszyny.

Próbowałem robić to w małych partiach z:

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

Brzydki jak diabli, ale działał dla mnie.

Jestem otwarty na wszystkich krytyków i porady. Jak wspomniałem, zamieszczam to na wypadek, gdyby komuś pomogło, ale nie mogę się doczekać, aby otrzymać konstruktywną opinię.

Answer 2

Ładowanie danych z ramki danych pandy do bazy danych SQL jest bardzo powolne, aw przypadku dużych zbiorów danych zwykle kończy się brak pamięci. Potrzebujesz czegoś, co jest znacznie wydajniejsze niż w przypadku dużych zbiorów danych.

d6tstack to coś, co może rozwiązać twoje problemy. Ponieważ działa z natywnymi poleceniami importu bazy danych. Jest to biblioteka niestandardowa stworzona specjalnie do rozwiązywania problemów ze schematem i wydajnością. Działa dla XLS, CSV, TXT, które można eksportować do CSV, Parquet, SQL i Pandas.

Answer 3

Myślę, że df.to_sqljest niesamowity! Ostatnio często go używam. Jest trochę powolny, gdy zbiory danych są naprawdę ogromne. Jeśli potrzebujesz szybkości, myślę, że wkładanie zbiorcze będzie najszybszą opcją. Możesz nawet wykonywać tę pracę partiami, dzięki czemu nie zabraknie Ci pamięci i być może przytłoczysz swój komputer.

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