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.
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.
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.
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.
Zurück zum Anfang des Artikels
Zurück zur Community-Seite