SQL-Loader 2005
Werbung
SQL-Loader Prof. Dr. Waldemar Rohde Dipl.-Ing. Jörg Höppner 05.05.2006 1 *SQL-Loader Beschreibung Definition transferiert Daten aus einer oder mehreren externen Dateien in eine oder mehrere Tabellen einer Oracle-Datenbank. ist ein eigenständiges Programm, was zum Lieferumfang von Oracle gehört Einlese-Formate Binäre Formate Character Formate Bedingung Zieltabellen müssen in der Datenbank existieren Zweck Laden von großen Datenmengen (05.05.2006) 2 *SQL-Loader Funktion Kontroll-Datei Daten-Dateien Parameter-Datei (optional) SQL*Loader abgewiesen Feld-Bearbeitung Bad-Datei akzeptiert Nicht zutreffend Datensatz-Auswahl selektiert Discard Datei (optional) abgewiesen Oracle-Server eingefügt Log-Datei DatenbankDateien (05.05.2006) 3 *SQL-Loader Aufruf Syntax sqlldr [Schlüssel = Wert], [Schlüssel = Wert], . . . Beispiel sqlldr userid = scott/tiger@student, control=insert.ctl, log=report.log, bad=error.dat oder sqlldr scott/tiger@student, control=insert.ctl, log=report.log, bad=error.dat Erklärung Dieser Programmaufruf bewirkt den Ladevorgang in die Datenbank student unter der Benutzung der UserID scott mit dem Passwort tiger. Die Definition der zu ladenden Daten befindet sich in der Datei insert.ctl, alle Meldungen des Vorgangs werden in die Datei report.log geschrieben, während sich am Ende des Ladevorgangs alle abgelehnten Datensätze in der Datei error.dat befinden werden. (05.05.2006) 4 *SQL-Loader Schaubild 10;ACCOUNTING;NEW YORK 20;RESEARCH;DALLAS 30;SALES;CHICAGO 40;OPERATIONS;BOSTON Dateisystem Programm SQL*Loader deptno Oracle DB 10 20 30 40 dname ACCOUNTING RESEARCH SALES OPERATIONS data.dat Kontroll-Datei => loc NEW YORK DALLAS CHICAGO BOSTON LOAD DATA INFILE `data.dat` INSERT INTO TABLE dept FIELDS TERMINATED by `;` OPTIONALLY ENCLOSED BY `“` (deptno integer external, dname char(14), loc char(13) ) Tabelle dept (05.05.2006) 5 *SQL-Loader Ladedaten Variante 1 Definition der Import Daten mit Felddaten variabler Länge und entspr. Feldtrennzeichen ¾ 10;ACCOUNTING;NEW YORK 20;RESEARCH;DALLAS 30;SALES;CHICAGO 40;OPERATIONS;BOSTON Variante 2 Definition der Import Daten mit Felddaten fester Länge ohne Feldtrennzeichen ¾ 10 20 30 40 ¾ Das 1. Feld ist 4 Zeichen, das 2.Feld ist 12 Zeichen und das 3.Feld ist 8 Zeichen lang. ACCOUNTING RESEARCH SALES OPERATIONS NEW YORK DALLAS CHICAGO BOSTON (05.05.2006) 6 *SQL-Loader Datentypen gebräuchliche Typen alphanumerisch CHAR [(length)] DATE [(length)] [,`date format`] DECIMAL EXTERNAL [(length)] INTEGER EXTERNAL [(length)] binär DOUBLE FLOAT DECIMAL [(digits [,precision])] INTEGER SMALLINT BLOB, CLOB (05.05.2006) 7 *SQL-Loader Datentypen Loader-DDL-Mapping DECIMAL EXTERNAL INTEGER EXTERNAL Attribut: Gleitkommazahl DDL: NUMBER(7,2) (z.B.) Attribut: Ganzzahl DDL: NUMBER(7) (z.B.) CHAR(7) Attribut: Zeichenfolge DDL: CHAR(7) oder VARCHAR2(7) (z.B.) (05.05.2006) 8 *SQL-Loader Kontrolldatei Bestandteile (Forts.) Felddefinitionen bzgl. Positionen bei Feldern fester Länge oder Trennzeichen bei Feldern variabler Länge, Datentypen und Filterkriterien (Schlüsselwort FIELDS) Filterbedingungen für die Definition einer Untermenge der zu ladenden Daten (Schlüsselwort WHEN) z.B. WHEN (8:20) != ‘ 12345‘ and (60:70) = ‘was ist das‘ Kommentare (eingeleitet durch --) (05.05.2006) 9 *SQL-Loader Kontrolldatei Bestandteile Name der zu ladenden Daten-Datei (Schlüsselwort INFILE) Name der Tabelle in die die Daten geladen werden sollen, sowie die Methode wie Daten zu laden sind. INSERT Zieltabelle muß leer sein, bevor mit der Befüllung begonnen werden kann. REPLACE Bevor geladen werden kann wird die Tabelle geleert. Aus den verknüpften Childtabellen werden durch DELETE CASCADE zugehörige DS entfernt. APPEND Die zu ladenden Daten werden an die Tabelle angehängt. (Alter Datenbestand bleibt erhalten) TRUNCATE entspricht dem REPLACE mit dem Unterschied, dass die Childtabelle unverändert bleibt. Hierbei wird unterschieden, ob Daten in eine leere Tabelle geschrieben werden oder an die Inhalte einer bereits gefüllten Tabelle angehängt werden. (Schlüsselwort INTO TABLE) (05.05.2006) 10 *SQL-Loader Kontrolldatei (Beispiel 1 alphanumerisch) Hier sind aus einer Textdatei mit Datensätzen, bestehend aus Datenfeldern fester Länge, Daten in eine Tabelle mit dem Namen dept zu überführen. Die Felder dieser Zieltabelle sind wie folgt definiert: Name NULL? Type DEPTNO DNAME LOC NOT NULL NUMBER(2) VARCHAR2(14) VARCHAR2(13) LOAD DATA INFILE `data.dat` INSERT INTO TABLE dept ( deptno dname loc ) position(1:4) integer external, position(5:17) char, position(18:25 char (05.05.2006) 11 *SQL-Loader Kontrolldatei (Beispiel 2 alphanumerisch) Hier sind aus einer Textdatei mit Datensätzen, bestehend aus Datenfeldern variabler Länge, Daten in eine Tabelle mit dem Namen dept zu überführen. Die Felder dieser Zieltabelle entsprechen dem Beispiel 1: Name NULL? Type DEPTNO DNAME HIREDATE LOC NOT NULL NUMBER(2) VARCHAR2(14) DATE(10) VARCHAR2(13) LOAD DATA INFILE `data.dat` INSERT INTO TABLE dept Fields terminated by `;` optionally enclosed by `“` ( deptno integer external, dname char(14), hiredate date(10) “DD.MM.YYYY“, loc char(13) ) Heiweis: Bei einen Datum ist es der sicherste Weg die Formatierung des Datums zu beschreiben. Nach dem Beispiel wird ein Datum in dem Format 2stellig Tag, 2stellig Monat und 4stellig Jahr erwartet. Diese Komponenten sind durch einen Punkt von einander getrennt. (05.05.2006) 12 *SQL-Loader Kontrolldatei (Beispiel 3 alphanumerisch + binär) Hier sind aus einer Textdatei mit Datensätzen, bestehend aus Datenfeldern variabler Länge, zzgl. der Dateinamen, hinter denen sich die binären Bildinformationen verbergen, Daten in eine Tabelle mit dem Namen autodaten zu überführen. Anders als bei den anderen Beispielen sollen hier Bilder, die binär codiert sind, in die Zieltabelle gespeichert werden. Name ID Hersteller Typ Baujahr Bild Null ? NOT NULL Type NUMBER(3) VARCHAR2(20) VARCHAR2(20) VARCHAR2(4) BLOB <autodaten.txt> 2;Ferrari;F40;1998;ferrari.jpg 3;Lotus;Elise;2000;lotus.jpg 4;Maybach;57;2003;maybach.jpg (05.05.2006) 13 *SQL-Loader Kontrolldatei (Beispiel 3 Forts.) Für die zuvor beschriebene Anforderung wird das folgende Kontoll-File erforderlich LOAD DATA INFILE `autodaten.txt` INSERT INTO TABLE autodaten Fields terminated by `;` optionally enclosed by `“` (ID integer external, HerstellerCHAR(20), Typ CHAR(20), Baujahr CHAR(4), Dummy FILLER CHAR(40), Bild LOBFILE (Dummy) TERMINATED BY EOF ) Länge des Dateinamens Inklusive Dateipfad Beide Deklarationen sind für den Zugriff auf Bilddateien notwendig! Ein Filler (hier „Dummy“ genannt) wird nur als Beschreibungsunterstützung des Ablageortes der Bildinformationen benötigt. Er stellt den Bezug zu der Datei her, die das Bild gespeichert vorhält. Der Fillerinhalt (i.R. der Dateiname) wird dann beim Zugriff des Loaders auf die Datei verwendet. Der Zugriff auf Binärdateien ist nur über diesen Weg möglich. (05.05.2006) 14 *SQL-Loader Kontrolldatei Hinweise Nach Abschluß der Kontrolldatei-Datei erfolgt immer ein carriage return/ line feed kurz: CR/LF (Entertastendruck)! Jeder Datensatz innerhalb der Ladedatei wird ebenfalls mit CR/LF abgeschlossen. Dieses gilt auch für den letzten Eintrag! Der Ladedatei (festgelegt durch INFILE) wird immer in dem Verzeichnis gesucht, in dem das Programm sqlldr gestartet wurde! Sind in einer Ladedatei Sätze vorhanden, deren letztes Feld bzw. deren letzte Felder NULL sind, so ist in dem Kontroll-File die Option TRAILING NULLCOLS einzustellen! LOAD DATA INFILE `data.dat` INSERT INTO TABLE dept Fields terminated by `;` TRAILING NULLCOLS trailing NULLCOLS optionally enclosed by `“` ( deptno integer external, … (05.05.2006) 15
