Datei als Rohdatensatz in SQL ablegen und nachträglich importieren

ukulele

ukulele

Datenbank-Guru
Beiträge
5.306
Ich tue mich ein bisschen schwer mit dem Thema. Im Prinzip baue ich eine Art Data Warehouse für CSV-Dateien (mit verschiedenen Formaten). Grundsätzlich sind das alles Tabellen, allerdings kündigen sich hier verschiedene Formate und Besonderheiten an. Daher möchte ich eigentlich Schritt für Schritt folgendes tun können:

1) Liste von Dateien im Zielverzeichnis holen
2) Dateien als Ganzes importieren
3) ggf. einzelne Datensätze in der Datei manipulieren
4) Daten aus der Datei in eine Tabelle importieren
5) Die Datei auf dem Dateisystem löschen
6) Die Datei in der DB irgendwann löschen

Eins ist soweit kein Problem. Bei Zweitens wird es komplizierter, ich hab schon kleine Dateien mit max ~100kb als VARCHAR(MAX) importiert und dann im Text irgendwelche Manipulationen vorgenommen. Jetzt sind das aber csv-Dateien mit bestimmt mal 100 MB. Wenn ich das jetzt als VARBINARY(MAX) speichere könnte das vielleicht gehen aber dann kann ich natürlich keine Zeichenketten finden und verändern.

Ist es überhaupt sinnvoll möglich diese Datei für einen kurzen Zeitraum als ganzes in der DB zu halten oder macht es einfach keinen Sinn und die Dateien sollten auf dem Dateisystem verbleiben?

Es handelt sich leider um Daten die nur mit ; getrennt sind und Zeichenketten nicht vernünftig kennzeichnen. Ich werde also bei einem direkten Import auch ständige Probleme mit Sonderzeichen kriegen. Auch gibt es teilweise Zeilen die verworfen werden müssen (2te Überschrift).
 
Werbung:
Ich mache es so, dass ich beliebige Dateien als BLOB in einer Tabelle lade. Eine Kategorisierung (Typ/Format) erfolgt beim Upload durch spezifische Vorgaben oder User. Freigabe, Verarbeitung, Fehlerstatus sind abgesichert für einmaligen Durchlauf. Der Teil ist generisch.
Mit diesen Vorgaben habe ich für jeden Upload (konkrete Datei) Format, Vorgehensweise, Business Prozess, Datum, Transaktion.
Damit stoße ich einen spezifischen Prozess an, der bspw. CSV in eine Stage Table lädt (genug und genug große Spalten, Textformat).
Darauf kommt ein View, der typisiert und ggf. kleine / günstige Vorformatierung und Selektion macht.
Auf den View kommt ein Plausibilisierung.
Dann kommt jenachdem darauf der Massen-Import oder bei sensiblen Daten Zeilenweise mit Fehlerbehandlung (also wenn ein Constraint zuschlägt oder sowas, egal, bei jedem Fehler)
Das Drumrum ist relativ aufwändig. Die Anpassung für neue Dateien / Arbeitsschritte beschränkt sich auf eine Klassifizierung des BP, Formatangaben für die Datei und die genannten Views. (Was innerhalb der Views passiert / passieren muss, ob Filterung, Aggregation, Abgleich, ist beliebig und durch die Views gekapselt)

Jedenfalls:
M.E. kann jede Art von Verarbeitung, Filterung, Transformation am besten nach dem (Pre)Import erfolgen, weil SQL das einfach gut und komfortabel kann.
Im Zweifel habe ich insgesamt auch den Nachweis, wann, was hochgeladen und verarbeitet wurde und geknallt hat. Oder auch ein Archiv an Fehlerdatensätzen, anhand dessen der Import angepasst werden kann.

Welche aktuellen Beschränkungen bei MS durch Varchar(max) in Richtung BLOB, Spaltengröße existieren, weiß ich nicht.
 
Also ein BLOB pro Zeile in der Datei und dann als VARCHAR in einen Datensatz in die DB oder wie genau lädst du das rein?
 
Das ganze File als Blob in einen Datensatz.

Es ist so, der Upload (per Browser oder Fatclient) weiß überhaupt nicht, was das ist. Es ist konfigurierbar, ob der Upload tatsächlich in einer Tabelle landet (als Zeile, eine Spalte BLOB mit der Datei) oder ob er im Filesystem landet.
Daraus ergeben sich verschiedene Möglichkeiten. Im Filesystem kann ich diverse OS Funktionen nutzen, z.b. entpacken eines ZIP Files, gestückelte Dateien zusammensetzen usw. Auch große CSV sind als ZIP damit relativ sparsam zu speichern.
Aus dem Filesystem heraus, wo die Datei in jedem Fall zwischengespeichert wird, gibt es m.E. die besten / schnellsten Möglichkeiten zum Massenimport. IdR ist das irgendeine Form von Textdatei, Trenner variabel, die temporär in eine (immer gleiche und vorhandene) Rohimporttabelle gehen, alles Textfelder.
Also Datei an DB
>DB an FS und oder BLOB
>Datei aus FS importiert in Puffertabelle, alle Spalten, zeilenweise
>von Puffertabelle über Views in Production Table

Das Ganze Drumrum ist aber auch erstmal egal.
Im Kern wird eine CSV zeilenweise in eine Stagingtabelle geladen ohne jegliche Form von Interpretation, Prüfung, Typisierung, Filterung.
Das geschieht alles danach in Views.
 
Okay dann werde ich glaube ich die Dateien auf dem FS belassen und von dort erstmal in eine Staging-Tabelle laden, die Datei bleibt dann wo sie ist. Fehler bei der Verarbeitung werde ich dann in eine andere Tabelle ausleiten oder so, die Datei auf dem FS bleibt zu Debugging-Zwecken eine Weile bestehen. Sollte ein Fehler auftreten ist das für mich (zumindest derzeit) ziemlich gut reproduzierbar, auch im Betrieb. Da lohnt das Nachhalten der Dateien nicht so richtig.

Das hilft mir auf jeden Fall, ich glaube ich muss erstmal irgendwas zum laufen bringen und mache mir zu viele Gedanken im Voraus.
 
ukulele schrieb:
Eins ist soweit kein Problem. Bei Zweitens wird es komplizierter, ich hab schon kleine Dateien mit max ~100kb als VARCHAR(MAX) importiert und dann im Text irgendwelche Manipulationen vorgenommen. Jetzt sind das aber csv-Dateien mit bestimmt mal 100 MB. Wenn ich das jetzt als VARBINARY(MAX) speichere könnte das vielleicht gehen aber dann kann ich natürlich keine Zeichenketten finden und verändern.
Zum Vergrößern anklicken....
Also varchar(max) geht bis 2GB. Damit kannst Du denke ich fast jede Textdatei reinladen. Natürlich haben Dateien erstmal nix in der Datenbank verloren. Dazu gibt es auch eine Empfehlung von Microsoft, ich meine ab 256 kb Filegröße nimmst dazu Filestream. Am besten ist aber in deinem Fall die Filetable geeignet. Da kannst von beiden Seiten auf die Daten zugreifen. Sprich bei der Filetable kopierst die Dateien auf das Filestream Share (Filetable setzt auf Filestream auf) und schwupps! sind sie in der Datenbank.
 
Dukel schrieb:
Evtl. wäre ein ETL Tool (z.B. SQL Server Integration Services) eher dazu geeignet.
Zum Vergrößern anklicken....
Eigentlich wollte ich möglichst viel mit SQL Basics lösen um mich dem Thema Data Warehouse langsam zu nähern. Ich möchte da ungern von weiteren Tools abhängig sein. Nichts desto trotz werden wir, wenn die neue SQL Version endlich da ist, eine Standard Core Lizenz kaufen. Dann habe ich deutlich mehr Mittel als mit SQL Express wie jetzt noch.
 
ukulele schrieb:
Eigentlich wollte ich möglichst viel mit SQL Basics lösen um mich dem Thema Data Warehouse langsam zu nähern. Ich möchte da ungern von weiteren Tools abhängig sein. Nichts desto trotz werden wir, wenn die neue SQL Version endlich da ist, eine Standard Core Lizenz kaufen. Dann habe ich deutlich mehr Mittel als mit SQL Express wie jetzt noch.
Zum Vergrößern anklicken....
Wenns erstmal nur um Entwicklung geht kannst Du die Developer Edition nehmen. Die kostet nix und ist hat alle Features der Enterprise Version
 
Ich habe jetzt mal eine Datei als ganzes in ein VARCHAR(MAX) importiert (in einer Tabelle/Spalte).

Normalerweise würde ich die Datei mit BULK einlesen a la
SQL: 
    BULK
    INSERT    [tabelle]
    FROM    'datei.txt'
    WITH    ( FIRSTROW = 2,CODEPAGE = '65001',FIELDTERMINATOR = ';',ROWTERMINATOR = '\n' )
    GO

Wie würde ich den einen VARCHAR(MAX) jetzt elegant zerlegen? Am liebsten erstmal ohne Spalten-Trenner einfach eine Zeile in eine Spalte einer neuen Tabelle. Bastel ich mir da was mit CTE oder gibts da irgend einen Ansatz BULK INSERT FROM ( SELECT spalte FROM tabelle )?
 
Schau Dir mal die Funktion STRING_SPLIT an. Als Delimiter würde ich es mal mit char(10) probieren, müßte NEWLINE (\n) sein.
 
Also ich habe gedacht ich könnte es mit CTE in Zeilen zerlegen, scheidet aber aus weil max 32767 Rekursionen. Das würde in diesem Fall gehen aber ich habe nachher CSV Dateien mit mind. 200k Zeilen. STRING_SPLIT habe ich auch gefunden, wird aber erst ab SQL 2016 unterstützt und ich vermute es gibt hier eventuell auch ein Limit?

Jetzt versuche ich es grade mit XML, also Zeilenumbrüche durch XML-Tags ersetzen und damit aus jeder Zeile einen Node machen aber das wirft auch Fehler, ich vermute aufgrund der Länge. Eventuell ist eine Schleife zwar langsam und wenig elegant aber derzeit die robustere Lösung.

Ich habe jetzt erst eine Datei in einer VARCHAR(MAX) gespeichert. Das scheint deutlich schwerer zu verarbeiten zu sein als ich dachte. Wenn ich @dabadepdu richtig verstanden habe haut er alles vom Filesystem in eine Tabelle für Rohdaten. Vielleicht ist das einfach der bessere Ansatz.
 
Aus dem Dateisystem auf dem Server hat man m.E. die besten Möglichkeiten. Einerseits wegen Performance, dann wegen der Loader Tools der Hersteller, per SQL (was idr irgendwie Wrapper der Loadertools sind), aber auch -zumindest unter Linux- zahlreichen Tools zur Vorverarbeitung.
Was bei MSSQL aktuell alles geht weiß ich nicht, bei Dir sieht es so aus, als wäre wenigstens eine Version später, mit 2016 z.B. openrowset sehr hilfreich. Allerdings hab ich da wirklich keinen Überblick.
Geht sowas bei Dir? Oder ist dein System zu alt dafür?
Code: 
BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
von hier: Import CSV file into SQL Server

Bei dem Import, wie ich es beschrieben habe, ist der Ausgangspunkt die Upload Funktion (SP), die für Webclients oder Fatclients identisch von allen genutzt wird. Die Ablage im BLOB ist dabei nicht das Ziel, sondern eine Art Archiv, bei Bedarf. Für den CSV oder XML Import kleinerer Datenmengen ist es aber auch einfach praktisch. Oder wie gesagt, für eine Art "Replay" oder sogar Debug bei Fehlern. (was aber eigentlich nur bei menschengemachten Daten auftritt)
 
Ja SQL 2014 wird abgelöst von SQL 2022 wenn er denn verfügbar ist. Ich habe auch einen neuen SQL Developer aber will mir jetzt nicht die Mühe machen und warte :)

Ich werde wohl auf BULK INSERT umstellen, aber das geht eben nur aus Datei in Tabelle, ich wolle erst aus Datei in VARCHAR(MAX) und dann von dort weiter in eine Tabelle, aber das scheint nicht so geil zu funktionieren. Ich werde dann erstmal alles in eine Spalte laden und dann den FIELDTERMINATOR prüfen. Da kann es nämlich knallen dank des Export-Tools das keine "" setzt.
 
Werbung:
ukulele schrieb:
Ja SQL 2014 wird abgelöst von SQL 2022 wenn er denn verfügbar ist. Ich habe auch einen neuen SQL Developer aber will mir jetzt nicht die Mühe machen und warte :)
Zum Vergrößern anklicken....
Sry, aber jetzt dein Problem mit einem veralteten SQL Server 2014 anzugehen anstatt mit der verfügbaren neuesten Version, die mehr Funktionalität bietet macht keinerlei Sinn. Quälst dich nur. Aber seis drum hier Function STRING_SPLIT in SQL Server 2014 gibts ne Implemtierung für String_plit
 
Zurück
Oben