Insert: wie am besten viele Datensätze einfügen?

[Sharky]

Hallo zusammen,

ich möchte aus einer Textdatei viele Datensätze importieren. Es können pro Importvorgang auch schon mal bis zu 50.000 Werte sein.

Wenn ich nun das INSERT-Statement formulieren, sehe ich mindestens zwei Möglichkeiten. Welche ist die bessere?

1.
INSERT INTO messwerte (id, momentanwert) VALUES (1, 120);
INSERT INTO messwerte (id, momentanwert) VALUES (2, 145);

oder

2.
INSERT INTO messwerte (id, momentanwert), VALUES (1, 120), (2, 145);

Grundsätzlich würde ich Variante zwei favorisieren. Allerdings weiß ich nicht genau, wieviele Werte pro Statement zusammenkommen .

Wie lang darf ein solches INSERT-Statement (PostgreSQL) sein?

Danke und Grüße

Sharky

 

[castorp]

50.000 mit der zweiten Variante dürften machbar sein.

Noch schneller wäre allerdings ein COPY, oder wenn die Datei nicht auf dem Postgres Server direkt liegt, ein \copy via psql.

 

[akretschmer]

ack. Mit COPY kannst Du direct CSV-Dateien etc. einspielen, schnellste aller Varianten.

 

[Sharky]

COPY ist interessant...
Allerdings kann ich es nicht anwenden, da ich beim Datum das Format anpassen muss.

Eine Zeile in meiner csv-Datei sieht z.B. so aus:
17.03.2020 12:54:00;9,400000;

Da ist noch Handarbeit gefragt.

 

[castorp]

Das müsste sich eigentlich importieren lassen, wenn man vorher DateStyle entsprechend anpasst

 

[akretschmer]

das Datum ist schon okay:

test=*# select '17.03.2020 12:54:00'::timestamp;
      timestamp     
---------------------
 2020-03-17 12:54:00
(1 row)

Das sollte auch mit COPY laufen. Blöder ist das 9,4, das soll wohl 9.4 sein. Wenn alle Stricke reißen kann man es immer noch erst mal als TEXT in eine Zwischentabelle holen und dann weiterverarbeiten.

 

[dabadepdu]

Wenn alle Stricke reißen kann man es immer noch erst mal als TEXT in eine Zwischentabelle holen und dann weiterverarbeiten.

Bei "unsicheren" Datenquellen, also Menschhand im Spiel, ungemanaged, .. mache ich es immer so. Alle Felder als Text laden mit COPY. Die nächste Ebene ist dann ein View, der die Typisierung liefert. So hat man den Vorteil, dass die ganze Power von Postgres zur Nachbearbeitung, Zerlegung, Adaptierun zur Verfügung steht für die Konvertierung. Notfalls werden wirklich wie akretschmer schreibt ganze Zeilen als Text geladen. Auch das Filtern und Aggregieren, Dublettencheck usw. sind damit kein Problem, wenn nötig. Das Ganze in Schnell und ohne eine Zeile Client Code, vor allem ohne "die Schleife", alles blitzschnell.

Das Verfahren lässt sich je nach Lust und Laune auch Stück für Stück standardisieren, so dass ein Import nur noch die Definition eines Typisierungsviews ist und der Rest inkl. Validierung automatisch läuft.

Man spricht im Normalfall (klassisch) von ETL, Extract, Transform, Load. Das artet aber oft beliebig aus, denn E und T werden außerhalb von SQL mit unnötiger und vor allem oft unvollständiger Bastellei bewerkstelligt, besonders, wenn bei der Daten-"Pflege" der Faktor Mensch ungebremst im Spiel war. Viele Entwickler sind sich ja leider nicht bewusst, wie scharf man Eingaben bereits auf DB Ebene überwachen kann und sollte. Viele wissen nicht, dass genau dafür ein Datenmodell und Constraints verwendet werden können und sollen. Viele geht es vor allem deshalb so, weil sie mit irgendeiner Lauch Datenbank arbeiten, die das nicht mal richtig kann. Solche Daten machen dann eigentlich immer Probleme.

Ich mache also eher sowas wie ITEL Import, Transform, Extract, Load (final). Heute kann man sich das meist erlauben, weil DB Ressourcen nicht mehr so langsam und teuer sind, wie sie es mal waren. Erst bei sehr großen Datenmengen lohnt sich eher ein ETL Verfahren.

 

[castorp]

Man kann natürlich auch spezialisierte Import Tools verwenden bei denen man den Dezimaltrenner und das Datumsformat explizit einstellen kann.

Bei nur 50000 Datensätze sollte das von der Geschwindigkeit keinen großen Unterschied machen (bzw. Tools wie pgloader könnten u.U sogar schneller sein)

 

[dabadepdu]

Die nächste Ebene ist dann ein View, der die Typisierung liefert.

Kleiner Nachtrag/Anmerkung:
Wer keine Views mag, möchte oder kann: Die Validierung kann natürlich auch mit einer Reihe von Select Statements erfolgen, die finiale Typisierung dann beim Insert in die eigentliche Zieltabelle.