External Tables um Daten zu laden

Ein einfacher Weg um Daten in die Datenbank zu laden, geht über external Tables. Seit kurzen veröffentlicht die Deutsche Bahn AG Daten zum Thema Infrastruktur. Ich stelle hier kurz vor, wie diese Daten in eine Oracle DB geladen werden können. Im allgemeinen werden Daten mit sqlldr in die DB geladen. Dabei verwendet sqlldr external tables, die fuer den Ladevorgang temporär erstellt werden. Nützlich ist die sqlldr express Funktion. sqlldr express lädt den Inhalt einer Datei in eine Tabelle gleichen Namens. Zusätzlich werden im Logfile die Kommandos zum Erstellen der external Tables sowie die Insert DMLs protokolliert. Die create table statemnts koennen benutzt werden, um permanente external tables zu erstellen.

Als erstes wird in der DB ein neuer Tablespace und Account angelegt um die Daten zu laden

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set echo on
--
create tablespace stage datafile size 100m;
--
create user loader identified by Welcome01
default tablespace stage
temporary tablespace temp;
--
alter user loader quota unlimited on stage;
--
grant connect to loader;
grant create table to loader;
grant create any directory to loader;
grant drop any directory to loader;
--
/*
connect loader/Welcome01
--
select user from dual;
--
connect / as sysdba
--
drop tablespace stage including contents and datafiles;
--
drop user loader cascade;
*/

Die Daten können direkt von der DB Webseite geholt werden.

1
2
3
4
5
6
#!/bin/bash
#
wget -q -O bahnsteig.dat http://data.deutschebahn.com/datasets/bahnsteig/DBSuS-Bahnsteigdaten-Stand2015-10.csv
wget -q -O station.dat http://data.deutschebahn.com/datasets/stationsdaten/DBSuS-Uebersicht_Bahnhoefe-Stand2015-10.csv
# wget -q -O bahnsteig_rni.dat http://data.deutschebahn.com/datasets/bahnsteig_regio/DBRNI-Bahnsteigdaten-Stand2016-01.csv
# wget -q -O station_rni.dat http://data.deutschebahn.com/datasets/stationsdaten_regio/DBRNI-Uebersicht_Bahnhoefe-Stand2016-01.csv

Auf der DB Webseite ist auch jeweils die Struktur der Daten beschrieben. In der Regel erkennt man diese aber auch, wenn man einen kurzen Blick in die Datei wirft. Mit diesen Angaben erstellt man dann die Tabellen in die man die Daten lädt.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table bahnsteig (
  bhfnr  number
  , bahnsteig varchar2(10)
  , bahnsteigkante number
  , bezeichner_lokal varchar2(30)
  , laenge number
  , hoehe number
);

create table station (
  bundesland varchar2(30)
  , bm varchar2(30)
  , bhfnr number
  , station varchar2(50)
  , bhf_kurz varchar2(20)
  , vst number
  , strasse varchar2(50)
  , plz number
  , ort varchar2(50)
  , aufgtraeger varchar2(50)
  , verbund varchar2(50)
  , fern varchar2(5)
  , nah varchar2(5)
)
;

Sind die Tabellen erstellt, werden mit dem SQL*Load die Daten geladen.

1
2
sqlldr userid=loader/Welcome01 table=bahnsteig terminated_by=';'
sqlldr userid=loader/Welcome01 table=station terminated_by=';'

Beim Laden wird im Logfile auch das DDL Command zum Erstellen der external Table geschrieben.

 
…
creating external table "SYS_SQLLDR_X_EXT_STATION"

CREATE TABLE "SYS_SQLLDR_X_EXT_STATION"
(
  "BUNDESLAND" VARCHAR2(30),
  "BM" VARCHAR2(30),
  "BHFNR" NUMBER,
  "STATION" VARCHAR2(50),
  "BHF_KURZ" VARCHAR2(20),
  "VST" NUMBER,
  "STRASSE" VARCHAR2(50),
  "PLZ" NUMBER,
  "ORT" VARCHAR2(50),
  "AUFGTRAEGER" VARCHAR2(50),
  "VERBUND" VARCHAR2(50),
  "FERN" VARCHAR2(5),
  "NAH" VARCHAR2(5)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'station_%p.bad'
    LOGFILE 'station_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY ";" LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "BUNDESLAND" CHAR(255),
      "BM" CHAR(255),
      "BHFNR" CHAR(255),
      "STATION" CHAR(255),
      "BHF_KURZ" CHAR(255),
      "VST" CHAR(255),
      "STRASSE" CHAR(255),
      "PLZ" CHAR(255),
      "ORT" CHAR(255),
      "AUFGTRAEGER" CHAR(255),
      "VERBUND" CHAR(255),
      "FERN" CHAR(255),
      "NAH" CHAR(255)
    )
  )
  location
  (
    'station.dat'
  )
)REJECT LIMIT UNLIMITED
…

Mit dieser Steilvorlage ist es recht einfach die external Tables zu erstellen.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

create or replace directory LOAD_STAGE as '/home/oracle/project/load';

CREATE TABLE "EXT_BAHNSTEIG"
(
  "BHFNR" NUMBER,
  "BAHNSTEIG" VARCHAR2(10),
  "BAHNSTEIGKANTE" NUMBER,
  "BEZEICHNER_LOKAL" VARCHAR2(30),
  "LAENGE" NUMBER,
  "HOEHE" NUMBER
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY LOAD_STAGE
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P15
    BADFILE 'LOAD_STAGE':'bahnsteig_%p.bad'
    LOGFILE 'bahnsteig_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY ";" LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "BHFNR" CHAR(255),
      "BAHNSTEIG" CHAR(255),
      "BAHNSTEIGKANTE" CHAR(255),
      "BEZEICHNER_LOKAL" CHAR(255),
      "LAENGE" CHAR(255),
      "HOEHE" CHAR(255)
    )
  )
  location
  (
    'bahnsteig.dat'
  )
)REJECT LIMIT UNLIMITED
;

CREATE TABLE "EXT_STATION"
(
  "BUNDESLAND" VARCHAR2(30),
  "BM" VARCHAR2(30),
  "BHFNR" NUMBER,
  "STATION" VARCHAR2(50),
  "BHF_KURZ" VARCHAR2(20),
  "VST" NUMBER,
  "STRASSE" VARCHAR2(50),
  "PLZ" NUMBER,
  "ORT" VARCHAR2(50),
  "AUFGTRAEGER" VARCHAR2(50),
  "VERBUND" VARCHAR2(50),
  "FERN" VARCHAR2(5),
  "NAH" VARCHAR2(5)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY LOAD_STAGE
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
    BADFILE 'LOAD_STAGE':'station_%p.bad'
    LOGFILE 'station_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY ";" LRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "BUNDESLAND" CHAR(255),
      "BM" CHAR(255),
      "BHFNR" CHAR(255),
      "STATION" CHAR(255),
      "BHF_KURZ" CHAR(255),
      "VST" CHAR(255),
      "STRASSE" CHAR(255),
      "PLZ" CHAR(255),
      "ORT" CHAR(255),
      "AUFGTRAEGER" CHAR(255),
      "VERBUND" CHAR(255),
      "FERN" CHAR(255),
      "NAH" CHAR(255)
    )
  )
  location
  (
    'station.dat'
  )
)REJECT LIMIT UNLIMITED
;

Jetzt haben wir einen direkten Pointer auf die CSF Dateien und können die Inhalte selektieren.

 

SQL> connect loader/Welcome01
Connected.
SQL> @s1
SQL> set echo on
SQL>  select STATION,BAHNSTEIG,LAENGE,HOEHE from ext_station join ext_bahnsteig using ( bhfnr ) where PLZ = 79585; 

STATION                                            BAHNSTEIG      LAENGE      HOEHE
-------------------------------------------------- ---------- ---------- ----------
Steinen                                            B01               150         55
Steinen                                            B02               150         55

Mit den external Tables haben wir einen einfachen und flexiblen Weg Daten in die DB zu laden. Mit SQL bzw PL/SQL stehen vielfältige Möglichkeiten zur Verfügung die Daten beim Lafen zu transofmieren.

 
SQL> truncate table bahnsteig;

Table truncated.

SQL> truncate table station;

Table truncated.

SQL> insert into station select * from EXT_STATION; 

4475 rows created.

SQL> insert into bahnsteig select * from EXT_BAHNSTEIG;

11222 rows created.

SQL>  commit; 

Commit complete.

SQL>

Liegt das gleiche File mit unterschiedlichem Inhalt in verschiedenen Verzeichnissen, muss lediglich das DIRECTORY Object angepasst werden.

 
SQL> create or replace directory LOAD_STAGE as '/home/oracle/project/load';

Directory created.

SQL> select count(*) from ext_station;

  COUNT(*)
----------
      4475

SQL> create or replace directory LOAD_STAGE as '/home/oracle/project/load/tab2';

Directory created.

SQL> select count(*) from ext_station;

  COUNT(*)
----------
       582

SQL>

Was noch jetzt noch fehlt ist ein Weg die Files bereitszustellen und gegebenenfalls auf die Logfiles zugreifen zu können.

Referenzen

Express Mode Loading with SQL*Loader in Oracle Database 12c (pdf)