PowerShell, SQL und BCP

BCP (Bulk CoPy) ist ein Tool zum Import von Datenmengen in SQL-Tabellen. BCP ist aus meiner Erfahrung deutlich! schneller als alle anderen Weg per PowerShell Daten in SQL-Tabellen zu füllen. Aber BCP ist etwas bockig, wenn es die Daten nicht im erwarteten Format bekommt. Davon möchte ich auf dieser Seite berichten. Eine Seite zu PowerShell und SQL selbst ist noch in Arbeit.

Was ist BCP?

Microsoft kürzt das SQL Datenimport-Tool "Bulk CoPy" als BCP ab. Es ist eine EXE, die Sie mittels Kommandozeilen steuern, um Dateien in einem passenden Format in eine SQL-Tabelle zu importieren. Das Programm ist im SQL-Installationspaket enthalten, aber kann wohl auch einzeln im Rahmen der " Microsoft Command Line Utilities for SQL Server" herunter geladen werden

64bit https://go.microsoft.com/fwlink/?linkid=2043518
32bit https://go.microsoft.com/fwlink/?linkid=2043622

Performance mit BCP

Gerade wenn es darum geht viele Daten in einen SQL-Server zu bringen, dann macht es keinen Sinn dies "zeilenweise" zu machen. Das könnten z.B. IISLogs sein oder auch Auszüge aus dem Messagetracking oder viele anderen Daten, die Sie in SQL besser auswerten können. Dafür gibt es mehrere Optionen eines "Massenimports":

  • SSIS - SQL Server Integration Services (vormals Data Transformation Services (DTS))
    Über diesen Weg kann der SQL-Server von verschiedenen Quellen holen. Der Prozess läuft meines Wissens auf dem SQL-Server und die zu importierenden Daten muss der SQL-Server entsprechend zugreifen können.
  • BCP - Bulk CopyProgram
    Die Kommandozeile ist schon lange ein Weg, eine vorliegende Datei in SQL zu importieren. Eine Transformation der Daten ist meines Wissens beim Import nicht möglich
    https://docs.microsoft.com/de-de/sql/tools/bcp-utility

Ich habe versucht per PowerShell einen Import über die verschiedenen Schnittstellen zu machen aber bis auch nicht mal ansatzweise an die Performance von BCP gekommen. Da liegen Welten dazwischen wie das folgende Beispiel belegen soll.

I diesem Fall habe ich per "Get-Messagetrackinglog" aus einer größeren Exchange Umgebung verschiedene Daten extrahiert und erst einmal in einer CSV-Datei geschrieben. Diese habe ich dann per PowerShell in die SQL-Datenbank importiert. Der Import war schon mit einem BulkCopy optimiert, bei dem ich mit einer Blicksize für 1000 Zeilen gearbeitet habe, um die Datatable-Struktur nicht zu stark aufzublähen. bei 100.000 Zeilen waren das also 100 "Bulk Imports".

130MB
169959 Rows
Totaltime ca 2h
= 1,3 rows/Sek

Das ist natürlich nicht tolerierbar. Aber es war nicht alleine der SQL-Import. Schon der einfache "„Import-CSV | out-null" hat mit 10 Sekunden schon lange gedauert und das Übertragen der CSV-Daten in eine DataTable, die dann per SQL-Statement übertragen werden konnte, dauert noch länger. Ich bin natürlich nun nicht der geniale Programmierer und vielleicht könnte man den Code noch optimieren aber ich habe schon verschiedene Dinge versucht. Mit größeren Datenmengen sollten Sie die Grenzen von PowerShell kennen und in einer "richtigen Sprache" Programmieren, die .NET-Klassen nutzen oder andere Module, z.B. LogParser zu Hilfe nehmen.

Nachdem die Daten aber schon mal in der SQL-Datenbank waren, habe ich diese per BCP einfach mal exportiert.

Export
169959 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1813   Average : (93744.63 rows per sec.)

Es hat gerade mal 1,8Sek gedauert, um die Tabelle mit 120 Megabyte Daten und 170.000 Zeilen in eine lokale Datei TSV zu kopieren.

Danach habe ich die Datenbank gelöscht (DROP and Recreate) und die 120 MB große TSV-Datei wieder importiert.

169959 rows copied.
Network packet ze (bytes): 4096
Clock Time (ms.) Total     : 5204   Average : (32659.30 rows per sec.)

Natürlich war das noch etwas langsamer aber mit 5,2 Sekunden immer noch viel viel schneller als jeder PowerShell-Versuch davor. Da ich auf der Datenbank eingestellt habe, dass über verschiedene Felder ein Index erstellt wird und basi8erend darauf Dubletten ignoriert werden, habe ich die gleiche Daten noch einmal importiert:

0 ws copied.
NeNetwork packet size (bytes): 4096
Clock Time (ms.) Total     : 3890

Es wurde natürlich nichts importiert aber auch das war in weniger als 4 Sekunden vorbei. BCP ist aber für Massenexport/Import ab sofort mein erstes Mittel der Wahl. Allerdings ist BCP aus meiner Sicht schon ziemlich speziell. Es ist eine interessante Option, wenn Sie die Quelldateien im richtigen Format vorliegen haben und eine Import-Beschreibung erstellen. Dennoch sollten Sie einen Blick auf meine Erkenntnisse werfen, die ich beim Einsatz von BCP so erlangt habe. Sie sparen sich den ein oder anderen Fehlversuch.

Eckpunkte zu BCP

BCP ist nach meinem Verständnis nämlich sehr schwach beim Interpretieren von CSV-Dateien und viele Einschränkungen sind einfach nicht mehr Zeitgemäß, Sie sind aber bei BCP aus dem SQL 2012-Paket immer noch vorhanden:

  • BCP ist eine Kommandozeilenapplikation (EXE)
    d.h. mit Parametern aufrufen und den Exit-Code auswerten. Es gibt keine richtige "Pipeline" o.ä. Eine schöne Einbindung in ein PowerShell-Skript ist nicht elegant möglich. Letztlich können Sie nur den Errrolevel auswerten und eine Error-Datei mit angeben.
  • Parameter sind "Case-Sensibel"
    Das ist in der Windows-Welt schon einmal ungewöhnlich. Einige Parameter wie z.B. die Angabe eines Komma als Trennzeichen mit " -t , " gehen in der Powershell nur mit Umcodierung, da ein Komma hier ein Array-Trenner ist.
  • Keine Feldeinfassung
    Viele CSV-Dateien fassen die Datenfeldinhalte in Anführungszeichen ein. Diese Datei beschreibt, welche Felder es gibt und welche Spalten in der Quelle auf welches Feld mit welchem Format zugeordnet werden. Eine Vorlage kann man anhand einer bestehenden SQL-Datenbank erstellen lassen. Zur Wahl steht ein TXT-Format (Endung fmt) oder eine XML-Datei (empfohlen). Wenn sich die Struktur der Zieldatenbank ändert,  müssen Sie meist eine neue Steuerdatei anlegen. Ich habe mir daher angewöhnt die Steuerdatei ggfls. erst zu exportieren.
  • BCP braucht eine Steuerdatei
    Diese Datei beschreibt, welche Felder es gibt und welche Spalten in der Quelle auf welches Feld mit welchem Format zugeordnet werden. Eine Vorlage kann man anhand einer bestehenden SQL-Datenbank erstellen lassen. Zur Wahl steht ein TXT-Format (Endung fmt) oder eine XML-Datei (empfohlen). Wenn sich die Struktur der Zieldatenbank ändert,  müssen Sie meist eine neue Steuerdatei anlegen.
  • Standard-Trennzeichen sind "Tab" und "CRLF"
    Die Datensätze erwartet BCP zeilenweise mit einem Tabulator als Trennzeichen. Das Trennzeichen darf NICHT in den Daten selbst vorkommen Wer also ein "Komma" als Trennzeichen hat und in den Daten ein Komma vorkommt, kann diese Zeile nicht importieren. Fatalerweise exportiert BCP ein SQL-Feld mit einem Tabulator in den Daten einfach, so dass ein späterer Import der gleiche Datei fehl schlägt.
  • Keine Feldeinfassung
    Viele CSV-Dateien fassen die Datenfeldinhalte in Anführungszeichen ein. Damit kann BCP nicht umgehen. nur gut, dass der "TAB" als Separator üblicherweise nicht innerhalb eines Datenfelds vorkommen sollte.
  • Keine Feldnamen aus der CSV-Datei (?)
    Ich habe noch keinen Weg gefunden, wie BCP selbst z.B. die erste Zeile als Feldnamen interpretiert. Wenn die erste Zeile die Feldnamen enthält, dann können Sie BCP mit dem Parameter "-F 2" anweisen, erst bei Zeile 2 zu beginnen. Ihre Felder in der CSV-Datei müssen aber genau in der Reihenfolge vorliegen wie diese durch die Steuerdatei vorgegeben wird. BCP kann übrigens beim Export auch keine Spaltenbezeichnungen mit schreiben.
  • BCP legt selbst Tabellen an.
    Wenn Sie DB-Owner sind und sich bei dem Namen der Zieldatenbank vertun, dann legt BCP direkt eine neue Tabelle an. Den Fehler muss man dann erst mal finden, wenn Sie die Daten nicht finden und sie nicht zufällig am SQL Management Studio dann die Datenbanken durchschauen.

Wenn Sie nun immer noch BCP nutzen wollen oder sollen, dann ist es in der Regel einfach die Quelldaten gleich so aufzubereiten, dass diese in BCP direkt eingelesen werden können.

  • Die CSV-Datei sollen den passenden Zeichensatz haben, der bei BCP auch angegeben wird
  • Die CSV-Datei sollte die gleiche Feldreihenfolge wie die Tabelle im SQL-Server haben
    Das macht die Fehlersuche und die Definition der Dateien einfacher
  • Aus meiner Sicht ist der "Tabulator" der am besten geeignete Trenner, solange dieser nicht in den Nutzdaten enthalten ist
    Wobei ich beim Import des Betreffs aus einem Messagetracking tatsächlich einige TAB gefunden habe. Die Zeile wurde dann mit einem Fehler verworfen.
  • Als Datensatztrenner (Zeilenende) eignet sich CRLF

Die BCP Steuerdatei

BCP unterstützt zwei Formate einer Steuerdatei, eine alte "Text"-Version und eine neuere XML-Version. Welche Version Sie nutzen bleibt ihnen überlassen. Für die Zuordnung bietet es sich an zuerst einmal die aktuelle SQL-Tabelle zu exportieren, z.B. mit

REM Erzeugen einer FMT/XML Datei aus einer bestehenden SQL-Datenbank

bcp datenbankname.dbo.tabelle format nul -c -x -f bcpdefinition.xml -T -S sql01.msxfaq.net\INSTANZ

Das Ergebnis könnte so aussehen:

Die Datei muss ggfls. angepasst werden, wenn ihre Quelldaten nicht den Defaults entsprechen.

Export/Import-Test

Um dann die grundlegende Funktion zu prüfen, habe ich in der SQL-Tabelle manuell per SQL Management Studio ein paar Datensätze addiert und diese erst mal exportiert: So haben Sie die fast perfekte TSV-Datei zum Testen eines Import

bcp Datenbankename.dbo.tabelle OUT C:\temp\daten.csv -T  -S sql01.msxfaq.net\instanz -v

Ich sage absichtlich nur "fast perfekt", denn wen in den Feldern selbst ein "Tab" enthalten sein sollte, dann wird diese Zeile beim Import falsche getrennt. BCP konvertiert oder fasst diesen String nicht ein """-Zeichen ein. Beim Export ist keine Formatdatei erforderlich. In der Gegenrichtung kann dann der Import erfolgen:

Import einer CSV in SQL
bcp Datenbankname.dbo.tabelle IN C:\temp\daten.csv -f bcpdefinition.xml -T -S sql01.msxfaq.net\INSTANZ -w

Sie merken aber schon, dass ich kein Freund von BCP bin aber eines muss man BCP lassen. Es ist "sauschnell".

Datenkonvertierung

Nun entspricht eine TSV-Datei nicht gerade dem Format, welches die PowerShell-Commandlets "Export-CSV" und "Import-CSV" direkt schreiben. Vor allem konnte ich den PowerShell-Commandlets es nicht abgewöhnen die Strings in Anführungszeichen zu setzen. Ich wollte aber auch nicht auf die Performance von BCP.EXE verzichten, so dass ich mir ein PowerShell-Skript geschrieben habe, welches Tabellen per Pipeline annimmt und in eine geeignete TSV-Datei konvertiert.

convertto-bcpcsv.ps1
Nach dem Download bitte die Erweiterung auf PS1 ändern.

Achtung:
Das einfache Skript ist nicht "speicheroptimiert". Wer damit eine 2 GB große "CSV-Datei" konvertieren will, muss bis zu 16 GB Ram einplanen. Das geht sicher noch besser. Aber da ich bislang immer zeitnah kleine CSV-Dateien importiere, hatte ich noch keinen Bedarf das genauer zu untersuchen und zu optimieren.

Beispiele für Aufrufe sind:

# Einfache CSV-Datei konvertieren
import-csv .\daten.csv | convertto-bcpcsv.ps1 -formatxmlpath .\daten.fmt.xml | out-file .\daten.tsv

get-process | convertto-bcpcsv.ps1 -formatxmlpath .\process.fmt.xml| out-file .\processes.tsv

get-childitem | convertto-bcpcsv.ps1 -formatxmlpath .\file.fmt.xml| out-file .\daten.tsv

Diese Beispiele zeigen, wie ich ein Objekt mit Properties über die Pipeline einlade und anhand der per Parameter "-formatxmlpath" spezifizierten Import-Beschreibung in die richtige TSC-Reihenfolge bringe. Diese TSV-Datei kann ich dann per BCP sehr zügig einlesen.

Wenn Sei größere Datenmengen zu importieren haben, dann würde ich prüfen, ob Sie die Datei nicht direkt in einem "BCP-freundlichen Format ablegen können.

Weitere Links