Inhaltsverzeichnis
Der Oracle SQL*Loader - Tips und Tricks im Einsatz
Mit dem SQL*Loader (sqlldr) (High speed, bulk data Loader) lassen sich Text Daten in die Datenbank laden.
Siehe ⇒ https://docs.oracle.com/database/121/SUTIL/GUID-8D037494-07FA-4226-B507-E1B2ED10C144.htm
Der SQL*Loader ist die schnellste Möglichkeit Daten in die Datenbank zu landen und kann neben dem klassischen CSV Formaten fast jedes noch so exotische Format verarbeiten.
Allerdings ist meist etwas Geduld gefragt, dem Werkzeug beizubringen, wie die Daten zu interpretieren sind.
Zwei Arbeitsweisen:
- Convential Path
- SQL Inserts werden mit den Daten durchgeführt
- Direct Path
- SQL wird nicht verwendet, die Datenblöcke werden direkt geschrieben
Der SQL*Loader (sqlldr) findet sich in der Client und in der Server Installation der Oracle Datenbank.
Aufruf vom SQL Loader
Übersicht SQL*Loader Funktion:
Aufruf zum Beispiel über ein Dos Script:
set ORACLE_HOME=c:\oracle\products\12.1.0.2\dbhome_1 set LOGIN_DATA=scott/tiger@gpidb %ORACLE_HOME%/bin/sqlldr %LOGIN_DATA% control=PARAM_IMPORTS.ctl bad=bad.log log=log.log discard=discard.log
Den SQL Loader steuern
Die Kontrol Datei für den SQL*Loader
In der Control Datei wird definiert, wie die zu ladende Datei aufgebaut ist und welche Daten wie in eine oder mehrere Zieltabellen geladen werden sollen.
Aufbau:
- Load Data Anweisung
- Ort und Art der Import Datei vorgeben
- Tabellenstruktur der Import Tabelle
- Aufbau der Import Daten
- Position
- Datentyp
- Trennzeichen
Funktionen
- Daten in der Import-Tabelle anfügen oder ersetzen.
- SQL Funktionen können auf einzelne Daten durchführt werden.
- Steuert das Importverhalten in Zusammenhang mit den Daten ( z.B. Import in 2 Tabellen je nach Daten).
- Large Binary Objects (Dateityp BLOB) können über die File-Anweisung in die DB importiert werden.
Einfüge-Optionen:
- Insert (default)
- Tabelle muss leer sein
- Replace
- Daten in der Tabelle werden gelöscht und neu eingefügt
- evtl. Delete Trigger und delete cascade wird ausgeführt
- Truncate
- Tabelle mit SQL Truncate löschen → zuvor Constraints ausschalten!
- Append
- Daten werden angehängt
Grundaufbau der Datei (Kommentarzeichen ist „–“):
-- Aufrufoptionen vom SQLLDR hinterlegen Options ( ERRORS= 200000 ,SKIP=1 ) LOAD DATA -- Char set CHARACTERSET UTF8 --Quelldaten INFILE <textfile.dat> -- ob ersetzt, oder angehängt werden soll -- replace ersetzt die Daten ! REPLACE -- Wohin INTO TABLE <MY_DB_TABLE> -- Aufbau der Datei FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' TRAILING nullcols -- Spalten der Tabelle + Logik beim Einlesen der Daten ( PARAM_TABLE_NAME CHAR(32) , PARAM_01 CHAR(255) , PARAM_02 CHAR(255) , PARAM_03 CHAR(255) )
Die ersten Zeile einer CSV Daten NICHT importieren
Option SKIP verwenden
Options ( SKIP=1 )
Eine Sequence für das füllen der ID Spalte einer Tabelle verwenden
Soll beim Laden die ID Spalte einer Tabelle mit einer fortlaufenden Nummer gefüllt werden, kann bei der Spaltendefinition eine Oracle Sequence angegeben werden:
Mit EXPRESSION wird nun eine Funktion verwandt, in dem Fall der Aufruf des nächsten Wertes der Sequence
( ID_COLUMN EXPRESSION "parameter_id_seq.nextval" ...
Einen festen Wert hinterlegen
Mit EXPRESSION wird nun eine Funktion verwandt die einen Wert lieft, in diesem Fall immer 1:
OUE_UMPID EXPRESSION "to_char(1)"
Oracle NULL Wert für leere Felder einfügen
Ist ein Wert in der CSV leer, dann soll ein NULL Value in der Datenbank Tabelle eingefügt werden:
PARAMETER_30 CHAR(255) NULLIF PARAM_30=BLANKS
Datumsformat hinterlegen
Die Spalte in der Datenbank Tabelle ist vom Format DATE, mit dem richtigen Format String in Daten zuvor wandeln;
-- Datumsformat in der Textdatei DD.MM.YYYY , OUE_DATUM_KONGRESS DATE "DD.MM.YYYY" NULLIF OUE_DATUM_KONGRESS=BLANKS -- Datumsformat in der Textdatei YYYYMMDD"T"HH24MISS , LUM_CREATEDATE DATE 'YYYYMMDD"T"HH24MISS',
SQL Funktionen zum Filtern der Daten verwenden
Mit „:„ wird der Wert in der CSV Datei als Bind Variable referenziert und kann dann in einer normalen SQL Funktion verwendet werden.
Beispiele:
--EMail Adresse immer klein , OUE_E_MAIL_ADRESSE CHAR(255) "LOWER(:OUE_E_MAIL_ADRESSE)" -- Falls null den Wert 0 verwenden , OUE_GASTORINTESTINAL CHAR(1) "NVL(:OUE_GASTORINTESTINAL,0)" -- Passwörter beim laden maskieren , LUM_PASSWORD "translate(upper(substr(:LUM_PASSWORD,1,8)),'ABCDEFGIHUJKLMNOPQRSTUVWXYZ0123456789ÄÜÖß[].','***********************************************')", -- Nur einen Teil der Daten laden , LUM_ADDRESSINFO CHAR(4000) NULLIF LUM_ADDRESSINFO=BLANKS "substr(:LUM_ADDRESSINFO,1,4000)"
UTF8 Text Dateien laden
Zuvor den Zeichensatz der Datei mit in der Steuerdatei angeben:
..
LOAD DATA
CHARACTERSET UTF8
INFILE *
..
Einzelne Spalten NICHT laden
Keyword FILLER und BOUNDFILLER (wenn doch referenziert werden soll! (ab 9i)).
Beispiel:
..
PARAM1 FILLER
,PARAM2 BOUNDFILLER
,PARAM3 BOUNDFILLER,
PARAM4 ":PARAM2 + :PARAM3"
..
Geladen wird nur der Wert von PARAM2 und PARAM3 in die Spalte PARAM4.
Verschiedene Text Formate laden
Fixed Record Format
Beispiel:
load data infile 'example1.dat' "fix 25" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 10010, "DDRAM 256MB" , 200103 , Mousepad, 3010 ,Papier "Din A4", 89999,SDRAM 64 MB,
Variable Record Format
load data infile 'example2.dat' "var 3" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 02010010,"DDRAM 256MB",016200103,Mousepad, 0213010,Papier "Din A4",01889999,SDRAM 64 MB,
Stream Record Format
load data infile 'example.dat' "str X'7c0d0a'" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 10010,"DDRAM 256MB",| 200103,Mousepad,| 13010,Papier "Din A4",| 89999,SDRAM 64 MB,|
Stream Record Format mit Positionsangabe
Load data infile 'example.dat' "str X'7c0d0a'" into table import ( artNr position(1:6) , artName position(*+1:21) "replace(:artName,chr(34),'')" ) example.dat: 10010 "DDRAM 256MB"| 200103Mousepad| 13010 Papier "Din A4"| 89999 SDRAM 64 MB|
Laden von Binären Daten
LOAD DATA INFILE * INTO TABLE MMS_DIRLOAD_FILES APPEND FIELDS TERMINATED BY '?' (ID SEQUENCE(COUNT), FILENAME, FILESIZE, FILE_LOADER, RES_FILE FILLER CHAR, FILE_DATA LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE' ) BEGINDATA D:\data\sort.dat?59392?1?gpipperr?D:\data\sort.dat?
In 2 Tabellen laden
load data infile 'example5.dat' "str X'7c0d0a'" replace into table import (artNr position(1:7), artName position(8:22)) into Table lager ( artNrFK position(1:7), Laden position(23:30), anzahl position(31:33) "nvl(:anzahl,0)") into Table lager ( artNrFK position(1:7), Laden position(34:44), anzahl position(45:49) "nvl(:anzahl,0)") Example.dat: 10010 DDRAM 256MB Hamburg 10 Frankfurt 20 | 200103 Mousepad Hamburg 6 Frankfurt 20 | 13010 Papier Din A4 Hamburg 9 Frankfurt 2000| 89999 SDRAM 64 MB Hamburg 56 Frankfurt |
2-zeilige Einträge landen
load data infile 'example6.dat' replace continueif this(1)='*' into table import (artNr position(1:6), artName position(7:21) ) Example6.dat: *12345 "DDRAM 256MB" *200103 Mousepad *13010 Papier "Din A4" *89999 SDRAM 64 MB
Direct Path Load
Die Daten direkt mit der Option DIRECT=TRUE in die Datenblöcke laden.
Einschränkungen
- keine SQL Funktionen verwendbar
- keine aktiven Transaktionen auf der Import-Tabelle
- keine geclusterten Tabellen
Kann parallel ausgeführt werden.
Mit der Option UNRECOVERABLE werden für die eigentlichen Datendateien keine Redos geschrieben (nur für einen Index).