Vereinfachter csv-Import mit SQL Loader Express in 12c

Ich will nicht behaupten, dass es mich tief erschüttert hätte, dass Tom Kyte vor einigen Tagen eine Auszeit angekündigt und seine AskTom-Seite an Chris Saxon und Connor McDonald übergeben hat - aber völlig unberührt gelassen, hat es mich jedenfalls nicht. Meine Beschäftigung mit Oracle-Datenbanken hat im Jahr 2000 begonnen und AskTom war gerade in den ersten Jahren der Ort, an dem ich nach Erklärungen suchte für alle Verhaltensweisen des RDBMS, die ich nicht verstanden hatte - und das waren zunächst ziemlich viele. Die Bücher, die der Herr Kyte im Lauf der Jahre veröffentlicht hat, stehen immer in Griffweite im Regal und obwohl ich AskTom in den letzten Jahren nur noch selten besucht habe - unter anderem, weil die interessanten Threads irgendwann unendlich lang wurden - bedauere ich Toms Abschied; und freue mich andererseits darüber, dass er plant, weitere Bücher zu schreiben, und dass er so kompetente Nachfolger für die Weiterführung von AskTom gefunden hat.

Was mich zum eigentlichen Thema bringt: in seinem eigenen Blog (den ich auch seit vielen Jahren verfolge) hat Connor McDonald (von dem hier auch noch irgendwo ein Buch steht) vor kurzem einen Artikel zu einem interessanten neuen Feature in 12c veröffentlicht: dem SQL Loader Express. Dieser leistet etwas ganz Simples: er erlaubt ein vereinfachtes Einlesen von csv-Dateien - ein Feature, das mir bei Oracle seit vielen Jahren gefehlt hat, weil dieses Einlesen in anderen RDBMS schon seit langer Zeit sehr viel einfacher durchzuführen war. Dazu ein minimales Beispiel (das noch deutlich minimaler ist als das des Herrn McDonald und die entsprechenden Erläuterungen des von ihm verlinkten White Paper):

-- csv-Datei emp.txt mit folgenden Daten
7902,FORD,ANALYST
7844,TURNER,SALESMAN
7788,SCOTT,ANALYST
7654,MARTIN,SALESMAN
7566,JONES,MANAGER
7369,SMITH,CLERK
7876,ADAMS,CLERK
7900,JAMES,CLERK
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7521,WARD,SALESMAN
7934,MILLER,CLERK
7499,ALLEN,SALESMAN
7839,KING,PRESIDENT

-- Anlage einer entsprechenden Tabelle emp im Schema test
 create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9)
);

-- Aufruf des sqlldr im Verzeichnis, in dem sich die csv-Datei befindet
sqlldr userid=test/test data=emp.txt table=emp

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Oct 3 00:57:18 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP:
  14 Rows successfully loaded.

Check the log files:
  emp.log
  emp_%p.log_xt
for more information about the load.

Das zugehörige Log zeigt sehr detailliert, was im Hintergrund geschieht:

Express Mode Load, Table: EMP
Data File:      emp.txt
  Bad File:     emp_%p.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table EMP, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                               FIRST     *   ,       CHARACTER
ENAME                                NEXT     *   ,       CHARACTER
JOB                                  NEXT     *   ,       CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'emp.txt'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ","
(
  EMPNO,
  ENAME,
  JOB
)
End of generated control file for possible reuse.

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_EMP"

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
(
  "EMPNO" NUMBER(4),
  "ENAME" VARCHAR2(10),
  "JOB" VARCHAR2(9)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp_%p.bad'
    LOGFILE 'emp_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "EMPNO" CHAR(255),
      "ENAME" CHAR(255),
      "JOB" CHAR(255)
    )
  )
  location
  (
    'emp.txt'
  )
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table EMP

INSERT /*+ append parallel(auto) */ INTO EMP
(
  EMPNO,
  ENAME,
  JOB
)
SELECT
  "EMPNO",
  "ENAME",
  "JOB"
FROM "SYS_SQLLDR_X_EXT_EMP"

dropping external table "SYS_SQLLDR_X_EXT_EMP"

Table EMP:
  14 Rows successfully loaded.

Demnach umfasst die Operation folgende Schritte:

• Generierung eines wiederverwendbaren sqlldr control files.
• Erzeugung eines temporären directories für das Verzeichnis, in dem die csv-Datei liegt und in dem der sqlldr-Aufruf erfolgte.
• Aktivierung von parallel dml für die Session.
• Anlage einer external table basierend auf der Struktur der csv-Datei.
• parallelisiertes Insert Append zur Übertragung der Daten der External Table in die Zieltabelle.
• Drop der External Table.

Das Verfahren erspart eine Menge Definitionsaufwand und kann als External Table Generator verwendet werden (wie David Aldridge in seinem Kommentar bemerkt).

Nachtrag 14.09.2015: offenbar auch wieder ein Thema, das aktuell ein gewisses Interessae hervorruft - jedenfalls hat auch Oren Nakdimon ein schönes entsprechendes Beispiel veröffentlicht.