Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_sqlldr_faq

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:

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:

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 <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).


Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_sqlldr_faq.txt · Zuletzt geändert: 2016/10/18 15:37 von Gunther Pippèrr