”Load” af mange datafiler med betydende filnavne i Oracle

Vi støder med jævne mellemrum på opgaven at loade indholdet af en masse datafiler med betydende filnavne ind i Oracle. Det er typisk dumps af større felttyper (10K+) fra et andet system, hvor det har været besværligt at flytte data via alm. databasedrivere, så vi i stedet får en zip-fil fuld af tekstfiler, og en beskrivelse af, at navnet på filerne fortæller f.eks., hvilket kundenummer data skal påhæftes i Oracle databasen.

Fremfor rent faktisk at loade disse data ind, er det ofte nemmere at tilgå dem direkte fra Oracle, og i ét trin at flytte data hen til deres slut destination med almindelige insert update kolonner. Dette kan f.eks. gøres med external tables.

En external table i Oracle er i virkeligheden blot en pegepind, der peger ud af databasen og hen på en tekstfil, og en beskrivelse af, hvordan tekstfilen skal læses. Dette er en super effektiv måde at tilgå en datafil i en kort periode mens man henter og bearbejder dens indhold.

Men der er også mulighed for intelligent at tilgå mange datafiler på én gang. Dette kan hjælpe i situationer som den der er beskrevet ovenfor, hvor f.eks. 10.000 filer indeholder én lang kommentar hver, hvor filnavnet er det tilhørende kundenummer, og hvor man gerne vil have opdateret et CLOB felt i databasen ud fra dette kundenummer. Tricket er at bruge COLUMN TRANSFORMS og FROM LOBFILE mulighederne i external table. I det følgende eksempel er dette gjort på en Windows server, men tricket kan også bruges på Linux.

Lad os sige at vi har 10.000 små datafiler i et bibliotek på databaseserveren. Det første vi skal gøre at give adgang til dem fra databasen. Dette gøres med et directory:

create directory ext_files as ‘d:\lots_of_files_dir’;

Det næste vi skal gøre er at sørge for er, at vi har én fil vi kan tage udgangspunkt i. Det har vi ikke til at starte med, da de 10.000 filer ikke kender noget til hinanden, men det kan heldigvis nemt klares med en alm DIR kommando, hvor outputtet kanaliseres over i en output fil:

dir /b >file_names.txt

Det giver et indhold i output-filen der svarer til dette:

Kunde00001.txt
Kunde00013.txt
Kunde00017.txt
…
Kunde18212.txt
Kunde19231.txt
file_names.txt

Da batch kommandoer ikke kender noget til transaktioner eller ACID-tankegang, vil navnet på den nye fil vi skaber også komme med i outputtet, så den skal vi huske at slette i filen.

Den nye fil benytter vi så som LOCATION fil i vores external table, og henter dens data ind i den første kolonne i vores external table.

Men samtidigt definerer vi en CLOB kolonne, som skal gives os indholdet af den tilhørende fil.

Så toppen af vores external table definition ser således ud:

CREATE TABLE lob_tab (
filename  VARCHAR2(100),
clob_content      CLOB
)
ORGANIZATION EXTERNAL

Dernæst angiver vi type og, hvilket directory, der skal benyttes for at nå filerne:

TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_files

Så kommer mapningen af, hvordan data skal forstås, inkl. angivelse af en log fil, hvor evt. ulæselig data skal logges, en fake field terminator, der aldrig skal bruges da hver record kun har én værdi, og en NULL angivelse:

ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE ext_files:'lob_tab_%a_%p.bad'
LOGFILE ext_files:'lob_tab_%a_%p.log'
FIELDS TERMINATED BY '¤'
MISSING FIELD VALUES ARE NULL
(
filename  CHAR(100)
)

Dernæst følger selve tricket, hvor vi fortæller clob_content kolonnen skal “transformers” ud fra en selvstændig fil i directoriet med det navn der står filename kolonnen:

COLUMN TRANSFORMS (clob_content FROM LOBFILE (filename) FROM (ext_files) CLOB)
)

Endelig fortæller vi hovedfilens navn og, at der blot skal køres videre ligegyldig, hvor mange fejl vi evt. støder på:

LOCATION ('file_names.txt')
)
REJECT LIMIT UNLIMITED

Alt I alt kommer CREATE kommandoen så til at se således ud:

CREATE TABLE lob_tab (
filename  VARCHAR2(100),
clob_content      CLOB
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_files
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE ext_files:'lob_tab_%a_%p.bad'
LOGFILE ext_files:'lob_tab_%a_%p.log'
FIELDS TERMINATED BY '¤'
MISSING FIELD VALUES ARE NULL
(
filename  CHAR(100)
)
COLUMN TRANSFORMS (clob_content FROM LOBFILE (filename) FROM (ext_files) CLOB)
)
LOCATION ('file_names.txt')
)
REJECT LIMIT UNLIMITED
/

Efter denne kommando er eksekveret er både filnavne og indhold så umiddelbart tilgængelige med en almindelig SELECT, og vi kan flytte på data som vi ønsker det:

SELECT * FROM lob_tab

FILENAME            CLOB_CONTENT:
Kunde00001.txt      Her starter en lang kommentar om…
Kunde00013.txt      Dette er en beskrivelse af hvordan kunden…
Kunde00017.txt      Denne kunde bør altid…
…

10000 rows returned
I have been working as a system developer, data modeler and DBA on the Oracle platform using SQL, Pl/SQL, Forms, Reports, JSP, HTML and Javascript since 1996, and have been involved in both client/server and web-based projects for 25+ different customers. I'm normally known as a problem solver