|
18c: Inline External Tables
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 zuzugreifen. Erforderlich ist dazu ein logisches Directory zur Lokalisierung der Daten und ein Access Treiber wie zum Beispiel ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HIVE bzw. ORACLE_HDFS.
In jedem neuen Datenbankrelease gibt es interessante Neuerungen zum Thema External Tables. Die folgende Serie von deutschsprachigen Tipps haben wir in der Vergangenheit veröffentlicht, um die Funktionsweise und die neuen Features zu erklären.
- Arbeiten mit External Tables (Einführung)
- External Tables in Oracle Database 12c
- 12.2 Mehr Flexibilität mit External Table Hidden Feature
Auch in 18c gibt es wieder einige neue Features für External Tables. So gibt es die Möglichkeit Inline External Tables zu nutzen. Zusätzlich steht nur auch die In-Memory Funktionalität für External Tables zur Verfügung. Verwendet man die neue Autonomous Data Warehouse Cloud (ADWC), gibt es darüber hinaus eine interessante Erweiterung: Externe Daten, die im Objekt Storage gespeichert
sind, können über eine External Table zur Verfügung gestellt werden.
In diesem Blog starten wir mit dem Thema Inline External Tables, da diese Funktion sicherlich eine wichtige Rolle für die Anwendungsentwicklung spielen wird.
External Tables jetzt auch Inline
Was sind Inline External Tables? Inline External Tables bieten die Möglichkeit, externe Dateien in SQL als virtuelle View in einer Subquery zu nutzen. Damit kann in SQL-Kommandos auf externe Dateien zugegriffen werden, ohne das vorher eine externe Tabelle erstellt wurde. Es wird also keine externe Tabelle als persistentes Objekt im Data Dictionary angelegt. Dies vereinfacht den Zugriff auf externe Daten und ermöglicht die Entwicklung einfacher und effizienter Datenbankanwendungen. Das SELECT Kommando wird einfach umd eine neue "inline_external_table" Syntax in der FROM Klausel erweitert. Folgendes Template beschreibt die Verwendung.
SELECT columns FROM EXTERNAL ((column definitions) TYPE [ access_driver_type ] external_table_properties [REJECT LIMIT Klausel])
Um die Syntax besser zu verstehen, illustrieren wir die Funktionalität an ein paar Beispielen. Als Grundlage dient im ersten Fall eine CSV Datei mit folgendem Inhalt.
Lex,"De Haan",13.01.01,"Executive","Seattle","2004 Charade Rd" William,"Gietz",07.06.02,"Accounting","Seattle","2004 Charade Rd" Hermann,"Baer",07.06.02,"Public Relations","Munich","Schwanthalerstr. 7031" Susan,"Mavris",07.06.02,"Human Resources","London","8204 Arthur St" Shelley,"Higgins",07.06.02,"Accounting","Seattle","2004 Charade Rd" Daniel,"Faviet",16.08.02,"Finance","Seattle","2004 Charade Rd" Nancy,"Greenberg",17.08.02,"Finance","Seattle","2004 Charade Rd" ..
Zuerst wird das entsprechende logische Directory HOME_DIR erzeugt, in dem die Dateien wie die CSV Datei gespeichert sind. Dann kann man mit den Abfragen starten.
SQL> SELECT * FROM EXTERNAL (
(first_name varchar2(30),
last_name varchar2(30),
hiredate date,
department_name varchar2(30),
city varchar2(30),
street_address varchar2(30))
TYPE ORACLE_LOADER
DEFAULT DIRECTORY home_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE nobadfile nologfile
fields date_format date mask "dd.mm.yy")
LOCATION ('employees.csv') REJECT LIMIT UNLIMITED) employees_external
where first_name='Nancy';
FIRST_NAME LAST_NAME HIREDATE DEPARTMENT CITY STREET_ADDRESS
---------- --------------- --------- ---------- ---------- -----------------
Nancy "Greenberg" 17-AUG-02 "Finance" "Seattle" "2004 Charade Rd"
Es wird keine Tabelle mit dem Namen SALES_EXTERNAL angelegt. Das lässt sich schnell in der View ALL_EXTERNAL_TABLES überprüfen. Die Query liest die externen Daten und liefert die Ergebnisse zurück. Joins mit relationalen Tabellen, ORDER BY, GROUP BY Klauseln und weitere Klauseln können in dieser Art von Abfragen verwendet werden. Unterschiedliche Daten wie JSON und XML auch in Kombination mit relationalen Tabellendaten können mit dieser Technik einfach abgefragt werden.
Als Nächstes soll die Möglichkeit auf JSON Daten zuzugreifen gezeigt werden. Statt der CREATE TABLE Syntax verwenden wir einfach die SELECT Syntax.
Also statt
CREATE TABLE json_dump_file_contents (json_document CLOB)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY 0x'0A'
BADFILE 'JSONDumpFile.bad'
LOGFILE 'JSONDumpFile.log'
FIELDS (json_document CHAR(5000)))
LOCATION ('PurchaseOrders.dmp'))
REJECT LIMIT UNLIMITED;
kann man jetzt einfach folgende Abfrage verwenden.
select * from external ((json_document clob)
type oracle_loader
default directory home_dir
access parameters (
RECORDS DELIMITED BY 0x'0A' FIELDS (json_document CHAR(5000)) )
location ('PurchaseOrders.dmp') reject limit unlimited) json_tab;
Das Ganze kann natürlich auch dazu verwendet werden, um mit einem INSERT Statement JSON Daten in die Datenbank zu laden. Folgendes Beispiel zeigt die Vorgehensweise.
create sequence seq_j start with 1 increment by 1;
create table json_tab (id number generated as identity,
json_document clob constraint
ensure_json CHECK (json_document IS JSON));
insert into json_tab (json_document)
select json_data from external ((json_data clob)
type oracle_loader
default directory home_dir
access parameters (
RECORDS DELIMITED BY 0x'0A'
BADFILE 'JSONDumpFile.bad' LOGFILE 'JSONDumpFile.log' FIELDS (json_data CHAR(5000)))
location ('PurchaseOrders.dmp') reject limit unlimited) json_result;
Einzige Einschränkung, die man auch im Database Administrator's Guide nachlesen kann, ist die Möglichkeit die External Table zu partitionieren. Übrigens ist diese Inline External Table Funktion in allen Editionen der Datenbank verfügbar.
Weitere Informationen
- SQL Language Reference Guide
- Database Administrator's Guide
- Autonomous Data Warehouse Cloud
- Database In-Memory Guide
Zurück zur Community-Seite