CSV zu SQL Server: Massenimport-Albtraum (T-SQL und / oder Pandas)

Question

Ich versuche, eine .CSVDatei ohne großen Erfolg in SQL Server einzufügen .

Ein bisschen Hintergrund:

1. Ich musste 16 Millionen Datensätze in eine SQL Server-Datenbank (2017) einfügen. Jeder Datensatz hat 130 Spalten. Ich habe ein Feld im .CSVErgebnis eines API-Aufrufs eines unserer Anbieter, das ich nicht erwähnen darf. Ich hatte Datentypen für Ganzzahlen, Gleitkommazahlen und Zeichenfolgen.

2. Ich habe das Übliche versucht: BULK INSERTaber ich konnte die Datentypfehler nicht übergeben bekommen. Ich habe hier eine Frage gestellt , konnte sie aber nicht zum Laufen bringen.

3. Ich habe versucht, mit Python zu experimentieren und jede Methode ausprobiert, die ich finden konnte, aber pandas.to_sqlfür alle warnte es, dass es sehr langsam war. Ich habe mich mit Fehlern beim Abschneiden von Datentypen und Zeichenfolgen beschäftigt. Anders als die von BULK INSERT.

4. Ohne viele Optionen habe ich es versucht pd.to_sqlund obwohl es keine Datentyp- oder Kürzungsfehler verursachte, schlug es fehl, weil nicht genügend Speicherplatz in meiner tmp SQL-Datenbank vorhanden war. Ich konnte diesen Fehler auch nicht bestehen, obwohl ich viel Speicherplatz hatte und alle meine Datendateien (und Protokolldateien) auf unbegrenztes automatisches Wachstum eingestellt waren.

Ich blieb an diesem Punkt stecken. Mein Code (für das pd.to_sqlStück) war einfach:

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)

Ich bin mir nicht sicher, was ich sonst noch versuchen soll. Ratschläge sind willkommen. Alle Codes und Beispiele, die ich gesehen habe, befassen sich mit kleinen Datensätzen (nicht viele Spalten). Ich bin bereit, jede andere Methode auszuprobieren. Ich würde mich über Hinweise freuen.

Vielen Dank!

Answer 1

Ich wollte nur diesen schmutzigen Code teilen, nur für den Fall, dass er jemand anderem hilft. Beachten Sie, dass ich mir sehr bewusst bin, dass dies überhaupt nicht optimal ist, es ist langsam, aber ich konnte in zehn Minuten etwa 16 Millionen Datensätze einfügen, ohne meinen Computer zu überlasten.

Ich habe es in kleinen Mengen versucht mit:

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

Hässlich wie die Hölle, aber für mich gearbeitet.

Ich bin offen für alle Kritiker und berate. Wie bereits erwähnt, poste ich dies für den Fall, dass es anderen hilft, freue mich aber auch auf konstruktives Feedback.

Answer 2

Das Laden von Daten aus dem Pandas-Datenrahmen in die SQL-Datenbank ist sehr langsam, und bei großen Datenmengen ist ein Speichermangel üblich. Sie möchten etwas, das viel effizienter ist, wenn Sie mit großen Datenmengen arbeiten.

d6tstack ist etwas, das Ihre Probleme lösen könnte. Weil es mit nativen DB-Importbefehlen funktioniert. Es handelt sich um eine benutzerdefinierte Bibliothek, die speziell für die Behandlung von Schema- und Leistungsproblemen entwickelt wurde. Funktioniert für XLS, CSV, TXT, die nach CSV, Parkett, SQL und Pandas exportiert werden können.

Answer 3

Ich finde df.to_sqldas ziemlich genial! Ich habe es in letzter Zeit viel benutzt. Es ist ein bisschen langsam, wenn die Datenmengen sehr groß sind. Wenn Sie Geschwindigkeit brauchen, ist Bulk Insert meiner Meinung nach die schnellste Option. Sie können die Arbeit sogar stapelweise erledigen, damit Ihnen nicht der Arbeitsspeicher ausgeht und Sie Ihren Computer möglicherweise überfordern.

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