=====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:
{{ :dba:oracle_sql_loader_sqlldr_v01.png |Oracle SQL*Loader}}
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=====
Verarbeitung der Daten:
{{ :dba:oracle_sql_loader_sqlldr_v02.png |Oracle SQL*Loader Verarbeitung der Daten}}
====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
-- ob ersetzt, oder angehängt werden soll
-- replace ersetzt die Daten !
replace
-- Wohin
into 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).
----
===== Quellen =====
Oracle:
* http://docs.oracle.com/database/121/SUTIL/GUID-DD843EE2-1FAB-4E72-A115-21D97A501ECC.htm#SUTIL003
Netz:
* [[http://www.orafaq.com/wiki/SQL*Loader_FAQ|http://www.orafaq.com/wiki/SQL*Loader_FAQ]]