Effiziente DWH-Befüllung durch SQL BULK INSERT

Um Daten in unsere MSSQL Server Datenbank zu bekommen werden wir zwangsläufig irgendwann auf Bulk Insert stoßen, hier ein Beispiel für einen BULK INSERT in SQL Server. Der BULK INSERT-Befehl ermöglicht das schnelle Einfügen von großen Datenmengen aus einer Datei in eine SQL Server-Tabelle.

Angenommen, Sie haben eine CSV-Datei namens myData.csv mit folgendem Inhalt:

1,John,Doe
2,Jane,Smith
3,Jim,Brown

Inhalt dieser Seite

Schritt 1: Erstellen Sie eine Zieltabelle

Zuerst müssen Sie sicherstellen, dass Sie eine Zieltabelle haben, in die Sie die Daten einfügen können:

CREATE TABLE dbo.MyTable (
ID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);

Schritt 2: Verwenden Sie BULK INSERT

Stellen Sie sicher, dass Ihre Datei auf einem Pfad liegt, der für den SQL Server zugänglich ist, und verwenden Sie dann den BULK INSERT-Befehl:

BULK INSERT dbo.MyTable
FROM 'C:\Path\To\Your\File\myData.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
);

Erklärung der WITH-Optionen:

FIELDTERMINATOR: Das Zeichen, das Felder trennt, in diesem Fall ein Komma.
ROWTERMINATOR: Das Zeichen, das Zeilen trennt, hier ein Zeilenumbruch.
FIRSTROW: Die Zeile, bei der der Import beginnen soll, nützlich, wenn Ihre Datei Kopfzeilen hat, die Sie überspringen möchten.

Anmerkungen:

Stellen Sie sicher, dass der SQL Server-Dienst Zugriff auf den Dateipfad hat. Wenn Ihre Datei auf einem Laufwerk oder Verzeichnis liegt, das nicht für den SQL Server-Dienst zugänglich ist, müssen Sie sie möglicherweise in einen zugänglichen Pfad verschieben oder die Dienstkontoberechtigungen anpassen.
Achten Sie darauf, dass die Struktur Ihrer Datei (die Reihenfolge und der Typ der Spalten) mit der Tabelle übereinstimmt, in die Sie die Daten einfügen.
Wenn Sie Probleme mit den Textqualifizierern oder bestimmten Zeichen in Ihren Daten haben, müssen Sie diese möglicherweise bereinigen, bevor Sie den Bulk-Insert durchführen.

Vorteile von BULK INSERT:

Schnelligkeit: BULK INSERT ist eine der schnellsten Methoden zum Einlesen von Dateien und Einfügen von Daten in SQL Server. Es ist speziell für Massenkopiervorgänge aus einer Datei in eine Datenbanktabelle optimiert.
Einfachheit: Der Befehl ist recht einfach und erfordert keine komplizierten Konfigurationen oder Setups.
Flexibilität: Mit Optionen wie FIELDTERMINATOR, ROWTERMINATOR und FIRSTROW können Sie den Datenimport an verschiedene Dateiformate anpassen.
Transaction Logging: BULK INSERT ist minimal im Transaktionsprotokoll, was zu einer höheren Geschwindigkeit beim Einlegen von Daten führt, insbesondere im BULK_LOGGED oder SIMPLE Recovery Model.

Nachteile von BULK INSERT:

Sicherheit: Sie müssen sicherstellen, dass der SQL Server-Dienst ausreichenden Zugriff auf den Dateisystempfad hat, was ein potentielles Sicherheitsrisiko darstellen könnte.
Fehlerbehandlung: Die Fehlerbehandlung kann etwas umständlich sein, insbesondere wenn es um das Management von Fehlern in den Daten selbst geht.
Keine Transformationslogik: BULK INSERT bietet keine Möglichkeit, Daten während des Importvorgangs zu transformieren.
Locking: Während der Bulk-Operation kann es zu Sperren kommen, die sich auf die Konkurrenzfähigkeit auswirken.

Stärken:

Große Datenmengen: Besonders stark ist BULK INSERT beim Umgang mit großen Datenmengen, da es weniger Overhead im Vergleich zu anderen Einfügemethoden hat.

Alternativen:

SSIS (SQL Server Integration Services): Ein mächtiges ETL-Tool, das Datenimport/-export, Transformationen und viele weitere Funktionen ermöglicht. SSIS-Pakete können in SQL Server gespeichert und geplant werden und bieten umfangreiche Fehler- und Ausnahmebehandlung.
bcp Utility: Ein Befehlszeilen-Tool, das zum Exportieren und Importieren von Daten in SQL Server verwendet werden kann. bcp ist sehr ähnlich zu BULK INSERT, aber es wird außerhalb von SQL Server ausgeführt und kann daher in Situationen nützlich sein, in denen Sie keine direkte Verbindung zur Datenbank haben.
OPENROWSET: Kann auch zum Importieren von Daten verwendet werden, bietet auch Zugriff auf entfernte Daten und kann in T-SQL-Abfragen verwendet werden.
INSERT…SELECT: Kann für kleinere Datenmengen oder wenn Sie Daten zwischen Tabellen kopieren möchten verwendet werden.
AzCopy: Wenn Sie mit Azure SQL Datenbank arbeiten, ist AzCopy ein nützliches Befehlszeilen-Tool zum Kopieren von Daten in und aus Azure Blob Storage, das dann mit externen Tabellen oder PolyBase in SQL Datenbank verwendet werden kann.

Jede Methode hat ihre eigenen Stärken und Schwächen, je nach den speziellen Anforderungen Ihres Projekts, wie z.B. die Datenmenge, Netzwerk- und Sicherheitsanforderungen, Fehlerbehandlung und Logging-Anforderungen, könnte eine Methode besser geeignet sein als eine andere.