Blog Name
  • Freitag, 25.August 2017

External Tables in Oracle Database 12c

Von: Ulrike Schwinn

Externe Tabellen (auch External Tables) sind aus der Oracle Datenbank nicht mehr wegzudenken. Sie sind seit jeher ein geeignetes Mittel, um auf Daten, die in Flat Files außerhalb der Datenbank gespeichert sind, einfach im Read-only Mode zuzugreifen. Erforderlich ist dazu ein logisches Directory zur Lokalisierung und die Nutzung eines Access Treibers zur Beschreibung der Daten wie zum Beispiel der ORACLE_LOADER Access Treiber für das SQL*Loader Format. Liegen die Dateien nicht im entsprechenden Oracle Access Treiber Format vor, können sie vorab mit anderen Programmen aufbereitet werden. Dies geschieht über das sogenannte Pre-processing.

Der Access Treiber ORACLE_DATAPUMP ermöglicht sogar ein Entladen von Daten, die als Resultat eines SQL Statements vorliegen müssen. Damit wird eine binäre Datei im logischen Directory erzeugt, die auf das entsprechende Zielsystem kopiert und dort wieder verwendet werden kann. Eine Einführung in die Grundlagen der External Table Technologie ist in unserem Artikel Arbeiten mit External Tables nachzulesen. Mit Oracle Database 12c gibt es allerdings noch weitere Neuigkeiten. Folgende Abschnitte geben einen Einblick in einige interessante und nützliche Features. Dabei werden die Funktionen wie immer an Beispielen demonstriert.

 
Weitere Access Treiber

Seit 12.1.0.2 gibt es zwei weitere Access Treiber speziell für Big Data SQL -ORACLE_HIVE und ORACLE_HDFS:

ORACLE_HIVE ist dabei für Daten über Apache Hive Datenquellen gedacht. Dies ist sinnvoll falls Hive Tabellen für die HDFS Datenquellen existieren.

ORACLE_HDFS hingegen eignet sich für Dateien im Hadoop Distributed File System (HDFS). Hier wird Hive Syntax verwendet um die Datenquellen zu beschreiben.

Für Hadoop Anwender aus dem Big Data Umfeld wird damit die Möglichkeit geschaffen, das Daten Processing mit Funktionen der Datenbank zu kombinieren. So kann auf Daten, die in Hadoop gespeichert sind, sehr einfach über External Tables zugegriffen werden. Weitere Informationen finden sich entweder im Big Data Appliance Software User's Guide oder im Database Utilities Guide.

 
Heterogene Datenmengen

Unterschiedlichste Daten mit SQL Statements im Zugriff zu haben, war schon immer ein wichtiges Kennzeichen der Oracle Datenbank. Dies soll natürlich für Daten innerhalb und außerhalb der Datenbank gelten. Welche Unterstützung liefern dabei die External Table Funkionen? Was ist zum Beispiel mit JSON Daten die außerhalb der Datenbank verfügbar sind?

Seit Oracle Database 12c (12.1.0.2) gibt es die Möglichkeit auf JSON Daten mit Standard Datenbankmitteln zuzugreifen. Die Idee dahinter ist, nicht nur einen einfachen Textstring zu speichern und auf diesen zuzugreifen, was schon immer in jedem Release möglich war, sondern auch spezielle JSON Pfad Zugriffe oder JSON Validierungen zu ermöglichen. Natürlich gibt es auch hier die Möglichkeit mit External Table zuzugreifen. Liegen die JSON Daten exportiert zum Beispiel als Text in einer Datei vor, können sie einfach über den Access Treiber ORACLE_LOADER der Datenbank zur Verfügung gestellt werden. Folgendes Beispiel zeigt die Vorgehensweise. 

create table json_contents (json_document CLOB)
organization external 
(type oracle_loader default directory json_dir
 access parameters
    (records delimited by 0x'0A'
     fields (json_document CHAR(5000)))
     location ('PurchaseOrders.dmp')) reject limit unlimited;

Möchte man das Ganze mit den Purchaseorder Daten ausprobieren, kann man Beispieldaten von Oracle verwenden, die auf Github zu finden sind. Weitere Informationen zu JSON in der Oracle Datenbank finden sich auch in unseren JSON Blogeinträgen wie

Auch für extern gespeicherte XML Daten gibt es eine Erweiterung in 12.2. Die neue Klausel XMLTAG in Verbindung mit dem ORACLE_LOADER Treiber, erlaubt es XML Tags anzugeben, um Teile eines XML Dokuments zur Verfügung zu stellen. Nehmen wir als Beispiel das folgende XML Dokument emp.xml: Es besteht aus den Angestellten der Tabelle EMP. Im Screenshot ist ein Ausschnitt der Datei zu sehen.

Folgende External Table Statements zeigen die Verwendung der neuen Syntax. 

CREATE TABLE ext_xml1 (xml_text VARCHAR2(2000) )
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY home
  ACCESS PARAMETERS
  ( 
    RECORDS
    XMLTAG ("DepartmentName" )
    READSIZE 1024 SKIP 0 FIELDS NOTRIM
    MISSING FIELD VALUES ARE NULL
  )
  location ('emp.xml')
)
REJECT LIMIT UNLIMITED
/

CREATE TABLE ext_xml2 (xml_text VARCHAR2(2000) )
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY home
  ACCESS PARAMETERS
  ( 
    RECORDS
    XMLTAG ("DepartmentName", "employeeName" )
    READSIZE 1024 SKIP 0 FIELDS NOTRIM
    MISSING FIELD VALUES ARE NULL
  )
  location ('emp.xml')
)
REJECT LIMIT UNLIMITED
/

Der Treiber sucht dabei den Text, der in der XMLTAG Klausel angegeben ist und gibt die entsprechenden Dokumentfragmente in separaten Zeilen aus. Folgende Abfragen zeigen die Ergebnisse.

 
Große Datenmengen

Zugriffe auf großen Datenmengen können normalerweise von Partitionierung profitieren. In 12.2 ist es jetzt auch möglich External Tables zu partitionieren, die Daten also in mehreren, inhaltlich getrennten, Dateien zu speichern. Eine zusätzliche Partition Klausel erweitert die External Table Syntax. Möglich sind Range, List, Composite Range und Composite List Partitionen - in Verbindung mit den Access Treibern ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HIVE und ORACLE_HDFS. Folgendes Beispiel zeigt eine Verwendung. 

create table ext_part 
(last_name varchar2(25), gender varchar2(1), city varchar2(50), birth_year number)
ORGANIZATION EXTERNAL 
(
type ORACLE_LOADER
DEFAULT DIRECTORY home
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ';' optionally enclosed BY '"' 
MISSING FIELD VALUES ARE NULL
)
)
REJECT LIMIT UNLIMITED 
PARTITION BY LIST (gender)
(
PARTITION part_f VALUES ('F')  
                   LOCATION (home:'cust_f.csv'), 
PARTITION part_m VALUES ('M')
                   LOCATION (home:'cust_m.csv', 'cust_m1.csv')
)
/

Die Tabelle ist nach der Spalte GENDER partitioniert (hier List partitioniert). In der Datei cust_f.csv befinden sich die weiblichen Kunden und in der Datei cust_m.csv die männlichen Kunden. Bei der Auflistung/Seleketieren der männlichen Kunden ist zu erkennen, dass nur der Zugriff auf die Partition 2 PART_M (siehe Pstart und Pstop) für die Ausführung erforderlich ist. 

SQL> select * from ext_part where gender='M';

Execution Plan
----------------------------------------------------------
Plan hash value: 1477800482
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    82 |  4592 |    29   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE      |          |    82 |  4592 |    29   (0)| 00:00:01 |     2 |     2 |
|   2 |   EXTERNAL TABLE ACCESS FULL| EXT_PART |    82 |  4592 |    29   (0)| 00:00:01 |     2 |     2 |
--------------------------------------------------------------------------------------------------------

Da es sich um die External Table Technologie handelt, sind nicht alle Partition Operationen sinnvoll und durchführbar. So kann man ein DROP oder RENAME PARTITION mit der Standard Syntax verwenden; Operationen wie SPLIT oder MODIFY Partition bzw alle Operationen bzgl. Indizes sind hingegen nicht nutzbar.

Es gibt auch einige Features, die die Nutzung von External Table vereinfacht. So ist es beispielsweise seit 12.1 möglich in der LOCATION Klausel Wildcards wie "*" oder "?" anzugeben. Statt LOCATION (home:'cust_m.csv', 'cust_m1.csv') könnte im obigen Beispiel LOCATION (home:'cust_m*.csv') verwendet werden.

Speziell beim Access Treiber ORACLE_DATAPUMP gibt es auch die Möglichkeit die binäre Dump Datei beim Entladen von Daten komprimiert abzulegen. In folgendem Beispiel sollen die Daten mit COMPRESSION HIGH komprimiert werden. 

CREATE TABLE sales_ext
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY HOME
     ACCESS PARAMETERS (COMPRESSION ENABLED HIGH)
     LOCATION ('sales_ext.dmp')
   )
   AS SELECT * FROM sh.sales;

Wichtig zu wissen ist, dass die Verwendung von Parametern wie COMPRESSION ENABLED HIGH (MEDIUM oder LOW) die Advanced Compression Lizenz erfordert.

 
Constraints und External Tables

Häufig stellt sich die Frage ob External Tables mit Constraints verwendet werden können. Dies ist nun ab 12.2 für einige Constraints mit gewissen Einschränkungen möglich. Erlaubt sind die Constraints Not Null, Unique, Primary Key und Foreign Key. Das folgende Beispiel zeigt die Vewendung von NOT NULL in der Spalte LAST_NAME. 

create table ext_cust 
(id number, last_name varchar2(50) not null, city varchar2(50), gender varchar2(1))
ORGANIZATION EXTERNAL 
(
type ORACLE_LOADER
DEFAULT DIRECTORY home
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ';' optionally enclosed BY '"' 
MISSING FIELD VALUES ARE NULL
)
LOCATION ('cust.csv'))
REJECT LIMIT UNLIMITED
/

Nehmen wir an, dass in der ersten Zeile der Datei cust.csv kein Eintrag für den Nachnamen existiert. Beim Zugriff wird eine .log und .bad Datei geschrieben, die den Verstoss gegen das NOT NULL Constraint dokumentiert. Folgender Ausschnitt zeigt das Verhalten. 

 LOG file opened at 07/11/17 14:21:41

Field Definitions for table EXT_CUST
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    ID                              CHAR (255)
      Terminated by ";"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    LAST_NAME                       CHAR (255)
      Terminated by ";"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    CITY                            CHAR (255)
      Terminated by ";"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    GENDER                          CHAR (255)
      Terminated by ";"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
error processing column LAST_NAME in row 1 for datafile /home/oracle/cust.csv
ORA-01400: cannot insert NULL into (LAST_NAME)

Die Verwendung von Unique, Primary Key oder Foreign Key Constraints ist allerdings nur deklarativ möglich und erfordert zusätzlich die Klausel RELY DISABLE. Der Parameter QUERY_REWRITE_INTEGRITY muss auf TRUSTED oder STALE_TOLERATED gesetzt sein. Das Ziel ist dabei die Abfrage Performance zu erhöhen und mehr Optimizertransformationen zu ermöglichen.

 
Flexibilität: Parameteränderungen zur Laufzeit

Erst kürzlich habe ich in Hermann Bärs' Blog vom März 2017 (Quelle siehe unten) eine weitere sehr interessante - allerdings (noch) undokumentierte - Funktionalität entdeckt: Einige External Table Parameter lassen sich in 12.2 zur Laufzeit ändern - ohne dass man das External Table DDL anpassen muss. Es handelt sich dabei um folgende Parameter: DEFAULT DIRECTORY, LOCATION, ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE) und REJECT LIMIT. Diese Funktion erhöht natürlich ungemein die Flexibilität bei der Nutzung von External Tables. Hat man sich auf die Datenstruktur geeinigt, kann man unterschiedliche Dateien in unterschiedlichen Directories zur Verfügung stellen - ohne die Definition zu ändern. Um das Ganze zu verstehen, werde ich die Funktion an einem kleinen Beispiel demonstrieren. 

  CREATE TABLE "SH"."EXT_FLEX" 
   (	"LAST_NAME" VARCHAR2(25 BYTE), 
	"GENDER" VARCHAR2(1 BYTE), 
	"CITY" VARCHAR2(50 BYTE), 
	"BIRTH_YEAR" NUMBER
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE nobadfile nologfile nodiscardfile
        FIELDS TERMINATED BY ';' optionally enclosed BY '"'
       MISSING FIELD VALUES ARE NULL
    )
    )
   REJECT LIMIT UNLIMITED ;

Wie zu erkennen ist, gibt es weder einen Eintrag zu DEFAULT DIRECTORY noch einen Eintrag mit dem Parameter LOCATION. Führen wir unsere erste Abfrage durch: ein einfaches SELECT auf die Tabelle. 

SQL> select count(*) from ext_flex;

  COUNT(*)
----------
         0

Das Ergebnis überrascht nicht, es wird keine Zeile gefunden. Nun stellen wir die zwei Dateien von oben - nämlich cust_m.csv im Directory DIR_M und cust_f.csv im Directory DIR_W - zur Verfügung. Nun fragen wir die Tabelle mit der neuen erweiterten Syntax EXTERNAL MODIFY ab. 

SQL> SELECT count(*) FROM ext_flex
     EXTERNAL MODIFY (LOCATION(dir_m:'cust_m.csv')); 

  COUNT(*)
----------
     37175

SQL> SELECT count(*) FROM ext_flex
     EXTERNAL MODIFY (LOCATION(dir_w:'cust_f.csv')); 

  COUNT(*)
----------
     18325

Und schon erhalten wir je nach Wahl der LOCATION die entsprechenden Ergebnisse.

 
Weitere Informationen

 

Zurück zum Anfang des Artikels

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services