Delphi-PRAXiS - Bulk-Import

Delphi-PRAXiS (https://www.delphipraxis.net/forum.php)

- Datenbanken (https://www.delphipraxis.net/15-datenbanken/)

- - Bulk-Import (https://www.delphipraxis.net/203171-bulk-import.html)

Hallo, ich habe in einer Quelldatenbank (ORACLE) ca. 4-5 Millionen Datensätze einer Tabelle von folgendem Aufbau:
CREATE TABLE QAMV (
ID INTEGER,
PRUEFLOS VARCHAR(12),
VORGLFNR INTEGER,
MERKNR INTEGER,
SATZSTATUS VARCHAR(4),
QPMK_WERKS VARCHAR(16),
VERWMERKM VARCHAR(32),
MKVERSION VARCHAR(24),
KURZTEXT VARCHAR(160),
DUMMY20 VARCHAR(80),
MASSEINHSW VARCHAR(12),
SOLLWERT DOUBLE PRECISION,
TOLERANZOB DOUBLE PRECISION,
TOLERANZUN DOUBLE PRECISION);

Diese sollen in eine Firebirddatenbank eingelesen werden.
Versucht habe ich es bisher zeilenweise mit parametrisierten INSERTS und auch mit Stored-Procedures. Dauert aber immer einige Stunden. Da es nicht die einzige Tabelle ist, die ich nächtlich zu transferieren habe, suche ich nach einer Variante, die deutlich schneller ist. Nun bin ich auch über externe Tabelle bei Firebird gestoßen. Wie werden die Importdateien genau erzeugt? Bis jetzt weiß ich, dass es die Felder ohne Delimiter mit fester Anzahl von Zeichen gespeichert werden müssen. Gibt es eine Funktion, die das Erzeugen von Strings aus z.B. Realzahlen, Datum oder auch Timesstamps ermöglicht? Strings sind kein Problem.
Oder geht das anders schneller?

Du kannst es damit mal versuchen und Deine CSV Files als externe Tabelle definieren:

http://www.firebirdfaq.org/faq209/

https://www.wisdomjobs.com/e-univers...bles-7629.html

(Mir ist nicht ganz klar, ob das eine Server (lokale) Funktion ist oder auch über isql im client geht.)

ja, das hatte ich auch probiert. Es erzeugt eine Datei, in der man sich anschauen kann, wie die Import-Datei dann aussehen sollte. So jedenfalls mein Verständnis. In dieser Datei werden aber die nummerischen Werte codiert und nicht als Klartext gespeichert. Ich muss aber wohl die Datei selber erzeugen und dann der Datenbank zum Lesen geben.

Vielleicht wird auf dem Weg eine Defaultdatei erzeugt, wenn Du das create statement aufrufst. Aber gemäß 2. Link muss das nicht unbedingt kodiert sein.
P.S.: Falls es so sein sollte, würde ich wahrscheinlich die Zahlen auch als char definieren und im Zielsystem konvertieren.
Bei großen Tabellen und ständiger Wiederholung dieses Verfahrens ist das natürlich resourcen hungrig, aber einmalig ist es sicher vertretbar.
Gennerell ist die Datenübernahme auf reiner Textbasis viel leichter zu handhaben, als vortypisierte Daten zu importieren. Wenn alles importiert ist, kann man schnell und effizient validieren, viel einfacher als bei einem Importfile. Natürlich sollte bei einem sauberen Export aus einem anderen RDBMS auch nicht allzuviel überraschendes passieren.

Zitat:

zeilenweise mit parametrisierten INSERTS

Ein INSERT pro Zeile oder ein INSERT mit vielen Datenzeilen?

Musst du sowas nur einmal oder öfters/regelmäßig machen?
Wenn ja, dann wäre ein direkter Weg bestimmt angenehmer, als der Umweg über SQL-ImportScripte, CSV, XML oder Dergleichen.

Eventuell gibt es in Firebird auch eine direkte Schnittstelle zu Oracle?

Beispiel in PostgreSQL

https://sql-info.de/postgresql/notes...-firebird.html
* hier wird in Postgres eine CSV eingelesen und als Tabelle an Firebird übertragen

* ein Data-Wrapper (oder wie das dann in Firebird heißen würde) zu Oracle und dann via SELECT+INSERT die Daten direkt von dort ziehen.

* es gibt ForeignDataWrapper für Oracle und Firebird, womit über Postgres zwischen Oracle und Firefird Daten ausgetauscht werden können
* und Postgres muß nicht installiert werden (es gibt eine ZIP zum Entpacken und da kann man den Server auch direkt starten)

Je nachdem, was möglich ist:
- Indices deaktivieren
- Trigger + SP deaktivieren
- RefInts/FK Checks deaktivieren

Dann würde ich noch schauen, wo die Zeit liegen bleibt. Was ist der Flaschenhals? Plattenzugriffe? Prozessor? Speicher?

Vielen Dank an jobo: der zweite Link hat mir geholfen und nun lese ich 4,5 Mio Datensätze in unter 5 Minuten ein :-)

Nur zur Sicherheit:
- du definierst die Daten als externe Tabelle
- und liest sie von dort in die Firebird-Tabelle ein?

Zitat:

Zitat von hoefi (Beitrag 1455609)

nun lese ich 4,5 Mio Datensätze in unter 5 Minuten ein

Das klingt brauchbar, so soll es sein!

Cool wäre, wenn Du für andere schreibst, was das Problem gelöst hat.

FireDAC beherrscht bei Firebird ArrayDML, damit kann ein Insert viele Datensätze verarbeiten.

Hallo jobo,

hier mein Ansatz.
1. Erzeugen der CSV-Datei (hier aus einer HANA-DB) in Delphi:

Delphi-Quellcode:

			    laenge[0]  := 8;     // Laengenvorgabe der Ausgabefelder

    laenge[1]  := 12;

    laenge[2]  := 8;

    laenge[3]  := 8;

    laenge[4]  := 4;

    laenge[5]  := 16;

    laenge[6]  := 32;

    laenge[7]  := 24;

    laenge[8]  := 160;

    laenge[9]  := 80;

    laenge[10] := 12;

    laenge[11] := 8;

    laenge[12] := 8;

    laenge[13] := 8;

    datei := 'C:\temp\qamv1.csv'; // Ausgabedatei

    AssignFile(dd,datei);

    REWRITE(dd);

    WITH H2R_Query DO

      BEGIN

       CLOSE;

       SQL.CLEAR;

       SQL.Add('SELECT C.PRUEFLOS, C.VORGLFNR, C.MERKNR, C.SATZSTATUS, C.QPMK_WERKS, C.VERWMERKM, C.MKVERSION, C.KURZTEXT, C.DUMMY20, C.MASSEINHSW, C.SOLLWERT, C.TOLERANZOB, C.TOLERANZUN');

       SQL.Add('FROM QALS Q, QAMV" C');

       SQL.ADD('WHERE Q.WERK = ''XXXX'' AND');

       SQL.ADD(' C.PRUEFLOS = Q.PRUEFLOS AND');

       SQL.ADD(' Q.ERSTELDAT >= TO_DATE(''01.01.2010'',''dd.mm.yyyy'')');

       OPEN;                     // holen der Daten

       i := 0;

       WHILE NOT EOF DO

         BEGIN

           s  := IntToStr(i);

           WHILE LENGTH(s) < 8 DO s := '0' + s;      // ID als Integer darstellen

           st := '';

           FOR j := 0 TO Fields.Count - 1 DO                            // Felder anpassen

             BEGIN

               st := Fields[j].AsString;

               WHILE LENGTH(st) < lae[j+1]  DO st := st + ' ';

               IF Fields[i].DataType IN [ftFloat, ftCurrency, ftSingle]

                 THEN WHILE POS(',',st) > 0 DO st[POS(',',st)] := '.'; // numerische Felder einen Punkt als Dezimaltrenner geben

               s := s +  st;

             END;

             WRITELN(dd,s);

           INC(i);

           IF i = max_anz THEN BREAK;

           NEXT;

         END;

      END;

    CloseFile(dd);

2: Definition der externen Tabelle in FB:

SQL-Code:

			CREATE TABLE QAMV_TEXT EXTERNAL 'C:\TEMP\QAMV1.CSV' (

    ID          CHAR(8),

    PRUEFLOS    CHAR(12),

    VORGLFNR    CHAR(8),

    MERKNR      CHAR(8),

    SATZSTATUS  CHAR(4),

    QPMK_WERKS  CHAR(16),

    VERWMERKM   CHAR(32),

    MKVERSION   CHAR(24),

    KURZTEXT    CHAR(160),

    DUMMY20     CHAR(80),

    MASSEINHSW  CHAR(12),

    SOLLWERT    CHAR(8),

    TOLERANZOB  CHAR(8),

    TOLERANZUN  CHAR(8),

    LFCR        CHAR(2)

);

3: Definition der Zieltabelle in FB:

CREATE TABLE QAMV (

    ID          INTEGER,

    PRUEFLOS    CHAR(12),

    VORGLFNR    INTEGER,

    MERKNR      INTEGER,

    SATZSTATUS  VARCHAR(4),

    QPMK_WERKS  VARCHAR(16),

    VERWMERKM   VARCHAR(32),

    MKVERSION   VARCHAR(24),

    KURZTEXT    VARCHAR(160),

    DUMMY20     VARCHAR(80),

    MASSEINHSW  VARCHAR(12),

    SOLLWERT    DOUBLE PRECISION,

    TOLERANZOB  DOUBLE PRECISION,

    TOLERANZUN  DOUBLE PRECISION,

    LFCR        CHAR(2)

);

4: Stored Procedure für das Übertragen der externen Tabelle in die Zieltabelle:

SQL-Code:

			create or alter procedure QAMV_WANDELN

as

begin

  insert into qamv

    select * from QAMV_TEXT;

end

Was ich nicht berechnet habe, ist das anschließende Erzeugen des Indexes.
Werde heute mal probieren, weitere Tabellen ebenso zu übertragen.

Brauchst Du die CRLF Spalte wirklich im Ziel? Das ist doch eine Import Krücke oder?

Die ID kannst Du doch auch innerhalb der DB erzeugen, das spart extra Programmcode und macht es flexibler.

Und was die Konvertierung angeht, auch die würde ich bereits im Select Statement machen, bei Zahlen gleich mit der Längenformatierung, bei Text mit Padding, spart noch mehr Code und macht das Codestück universeller.

Hallo jobo,

in der Zieltabelle ist das LFCR-Feld nicht nötig, sondern praktisch, wenn man die StoredProcedure mit "SELECT *" verwenden will. Falls man die Felder für den Select und Insert ausschreibt, kann man darauf verzichten.

Die ID habe ich absichtlich nicht per Trigger und Generator automatisch erzeugen lassen, da ich die Geschwindigkeit erreichen will und erstmal auf alles Reduzierende verzichte.

Zitat:

Zitat von hoefi (Beitrag 1455687)

in der Zieltabelle ist das LFCR-Feld ..praktisch, wenn man die StoredProcedure mit "SELECT *" verwenden will. ..

Die ID habe ich absichtlich nicht per Trigger und Generator automatisch erzeugen lassen, da ich die Geschwindigkeit erreichen will und erstmal auf alles Reduzierende verzichte.

Ok, praktisch find ich gut. :)
Das kann ja für jeden anders ausfallen.

Zur Performance:
Ich kann mir nicht vorstellen, dass die Nutzung einer Sequenz sich negativ auswirkt. Z.B. so (ohne ID as dem Textfile, sondern aus der Sequenz):

Code:

			insert into qamv 

  select NEXT VALUE FOR <SequenceName> ,qt.* from QAMV_TEXT qt;

Ein Trigger ist für den reinen Import unnötig. Das zu importierende Textfile dürfte ohne ID sogar ein gute Stück kleiner sein.

Zitat:

Zur Performance:
Ich kann mir nicht vorstellen, dass die Nutzung einer Sequenz sich negativ auswirkt. Z.B. so (ohne ID as dem Textfile, sondern aus der Sequenz):

Da es wohl weitere Tabellen gibt u.U. auch FK verwendet werden würde ich auch die vorhandenen mitnehmen und die Sequenzen anschliessend händisch setzen.

Zitat:

Werde heute mal probieren, weitere Tabellen ebenso zu übertragen.

Ich meine, im Export-Code hätte er eine ID erzeugt. Das ginge halt auch billiger.
Wenn Refconstraints da sind, müssen si enatürlich so wie sie vorliegen importiert werden.

Bei
[SQL]INSERT INTO tabelle
SELECT ...[/SQL]
Müssen natürlich auch die Felder der Quelle und des Ziels übereinstimmen, vor allem in Anzahl und Reihenfolge.
Die Zielfelder mit anzugeben kann da auch nie schaden.

Zitat:

Zitat von himitsu (Beitrag 1455720)

Ist gegeben Siehe #11

Aber auch nur wenn beide Tabellen gleichzeitig erstellt werden, bzw. die neue Tabelle beim Erstellen den Aufbau der Anderen erbt.
Es wurde ja nirgends gesagt, ob das Ziel nicht eine bereits bestehende Tabelle/Datenbank sein könnte, wo über die Jahrzehnte fleißig Felder ein-/ausgebaut wurden (ALTER TABLE). :angle:

Es geht ja offensichtlich um einen adhoc Prozess und anhand der Antworten des TE darf man annehmen, dass die Problematik geläufig ist.