Menu

Autonomous Database: External Tables

Von: Ulrike Schwinn

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 - sei es in der Verwendung mit dem ORACLE_LOADER Access Treiber oder zum Entladen mit dem ORACLE_DATAPUMP Access Treiber. Auch ein Preprocessing ist möglich, falls die Daten nicht im passenden SQL*Loader Format verfügbar sind. Wie kann man nun die External Table Funktionalität im Zusammenhang mit Autonomous Database verwenden? Man muss bedenken, dass es in Autonomous Database keine Möglichkeit gibt auf das Betriebssystem zuzugreifen. Die Antwort ist ganz einfach: Die Schnittstelle wird über das Package DBMS_CLOUD bereitgestellt. Was ist damit möglich? An einem Beispiel wird die Funktionsweise erklärt.

Die Idee ist wie bei On-Premise Datenbanken, die Daten einfach außerhalb der Datenbank zu belassen und die Spaltenbeschreibung in Form der External Table im Data Dictionary zur Verfügung zu stellen. Für Autonomous Database bedeutet dies, die Dateien im Object Storage zur Verfügung zu stellen. In unserem Fall lade ich die Datei products_ext.txt in den Oracle Object Storage. Dazu erzeugen wir im entsprechenden Compartment ein Bucket beispielsweise USBUCKET. Ein Bucket ist dabei ein logischer Container, in dem Objekte gespeichert werden können. Im nächsten Schritt wird garantiert, dass die Kommunikation mit Autonomous Database gesichert ist und somit nur autorisierte Daten geladen werden. Dazu werden die Datenbank Credentials mit dem Package DBMS_CLOUD angelegt und als Default festgelegt. Das verwendete Passwort lässt sich in der OCI Konsole oben rechts unter "User Settings" im Bereich Auth Tokens mit Generate Token erzeugen. Die Database Credentials lauten in meinem Fall CREDENTIAL_US1. Verbinden wir uns einfach mit dem SQL*Plus Instant Client und setzen dazu folgende Befehle ab. 

execute dbms_cloud.drop_credential('CREDENTIAL_US1');
begin DBMS_CLOUD.create_credential(credential_name => 'CREDENTIAL_US1',
                                          username => 'oracleidentitycloudservice/ulrike.schwinn@oracle.com',
                                          password => 'AUTH Token passwort');

end;
/
alter database property set default_credential = 'ADMIN.CREDENTIAL_US1';

Im Beispiel verwende ich einen Federated User, der vollqualifiziert angegeben werden muss inklusive Identity Provider (IdP)- also zum Beispiel oracleidentitycloudservice/ulrike.schwinn@oracle.com. Das Ganze funktioniert natürlich ebenso mit einem lokalen User.

Nun fehlt nur noch das Datenbankobjekt External Table. Dies wird über das Package DBMS_CLOUD erzeugt. Um das Ganze zu demonstrieren, verwenden wir die Datei products_ext.txt. Diese besteht aus einigen Zeilen, die durch das Zeichen '|' getrennt sind. Folgender Abschnitt zeigt einen kleinen Ausschnitt. 

5MP Telephoto Digital Camera|                       Photo|                                                       899,99
17" LCD w/built-in HDTV Tuner|                      Peripherals and Accessories|                                 999,99
Envoy 256MB - 40GB|                                 Hardware|                                                    999,99
...

Wer dies selbst nachvollziehen möchte, kann die Datei auch hier herunterladen und danach in den eigenen Object Storage laden.

Nun kommt die Funktion CREATE_EXTERNAL_TABLE aus DBMS_CLOUD zum Einsatz. Für eine erfolgreiche Anwendung ist dabei mindestens die Angabe von Tabellenname, der Credential Name, die FILE_URI_LIST, das Argument FORMAT und das Argument COLUMN_LIST, das die Liste der Spaltennamen und Datentypen, durch Kommata getrennt, für die externe Tabelle angibt, notwendig. Folgendes Beispiel zeigt die Verwendung. 

drop table products_EXT;

begin DBMS_CLOUD.create_external_table(table_name=>'products_ext', 
                      credential_name => 'CREDENTIAL_US1',
                      file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/products_ext.txt',
                      format => json_object('delimiter' value '|', 'trimspaces' value 'ltrim', 'rejectlimit' value 10),
                      column_list=>'prodname Varchar2(100),
                      category varchar2(200),
                      price varchar2(20)');
                      end;
/

Der Parameter FORMAT kann dabei in zwei verschiedenen Varianten angegeben werden: 


format => '{"format_option" : "format_value" }'  

oder

format => json_object('format_option' value 'format_value'))

In meinem Beispiel habe ich die wohlbekannten Optionen DELIMITER für die Feldbegrenzung, TRIMSPACES für den Umgang mit führenden oder abschließenden Leerzeichen und REJECTLIMIT verwendet. Weitere Format Optionen kann man im Handbuch nachlesen.

Zum Abschluß wird natürlich noch das Ergebnis angezeigt.



Was passiert nun im Fehlerfall? Wo kann man die Informationen aus den Logdateien finden? Auch hier stellt das Package DBMS_CLOUD eine Funktionalität zur Verfügung - nämlich VALIDATE_EXTERNAL_TABLE. Diese Prozedur validiert die Quelldateien für eine External Table, erzeugt Protokollinformationen und speichert die Zeilen, die nicht mit den für die External Table angegebenen Formatoptionen übereinstimmen, in einer Badfile-Tabelle. Demonstrieren wir das Vorgehen an einem Beispiel. Dazu verkleinern wir den Datentyp der Spalte Category auf VARCHAR2(10). 

drop table products_EXT;

begin DBMS_CLOUD.create_external_table(table_name=>'products_ext', 
                      credential_name => 'CREDENTIAL_US1',
                      file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/products_ext.txt',
                      format => json_object('delimiter' value '|', 'trimspaces' value 'ltrim', 'rejectlimit' value 10),
                      column_list=>'prodname Varchar2(100),
                      category varchar2(10),
                      price varchar2(20)');
                      end;
/

Das Anlegen ist erfolgreich verlaufen. Auch der Eintrag im Data Dictionary ist erfolgt. 

select table_name from USER_EXTERNAL_TABLES;
TABLE_NAME                                                                                                                      
------------------------------------------------------------------
CHANNELS_EXT
PRODUCTS_EXT

Allerdings gibt es beim Zugriff folgenden Fehler: 

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

Um weitere Informationen zu erhalten, verwenden wir jetzt die VALIDATE_EXTERNAL_TABLE. 

execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'products_ext');
Error starting at line : 15 in command -
BEGIN DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'products_ext'); END;
Error report -
ORA-20003: Reject limit reached, query table "ADMIN"."VALIDATE$49_LOG" for error details
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 535
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 851
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 872
ORA-06512: at line 1

Die Fehlermeldung weist auf die Einstellung rejectlimit hin. Die von Oracle neuangelegte Logtabelle VALIDATE$49_LOG enthält einen ausführlichen Fehlerbericht. Eine weitere Tabelle, die Badfile Tabelle, enthält dabei die fehlerhafte Zeile, bei der die Verarbeitung abgebrochen ist. Abhängig von der Einstellung des Arguments STOP_ON_ERROR lassen sich natürlich auch alle Zeilen ausgeben. Folgender Screenshot zeigt die Informationen aus der Logdatei.

Weitere Formate


Die gespeicherten Dateien müssen nicht unbedingt im Textformat vorliegen. Wie in On-premise Installationen ist auch das CSV Format mit der Formatoption TYPE möglich. Auch vorkomprimierte Dateien lassen sich auslesen. Die Formatoption COMPRESSION für komprimierte Dateien ermöglicht den Zugriff auf Dateien des Komprimierungstyps gzip, zlib und bzip2. Mehr dazu findet sich auch im Posting zu Autonomous Database Services: Komprimierte Dateien einbinden.

Übrigens lassen sich auch Parquet Files verwenden. Bei der Formatoption muss dazu die Option TYPE und der Wert 'parquet' verwendet werden. Die zu verwendenden Metadaten werden dann über die Option SCHEMA mit Wert FIRST oder ALL ausgelesen. Eine umfassende Beschreibung und gute Anleitung zum Thema Parquet und ADW findet sich in folgendem Blogeintrag Oracle Autonomous Data Warehouse - Access Parquet Files in Object Stores. Zum besseren Verständnis ist hier noch einmal das Beispiel in Kurzfassung zu finden. Der verwendete Parquet File lässt sich hier laden. Die Credentials sind wie im obigen Beispiel zu nutzen. Der External Table Aufruf sieht dann folgendermaßen aus: 

begin
    dbms_cloud.create_external_table (
       table_name =>'sales_extended_ext',
       credential_name =>'CREDENTIAL_US1',
       file_uri_list =>'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/sales_extended.parquet',
       format =>  '{"type":"parquet",  "schema": "first"}'
    );
end;
/

Eine einfache Abfrage auf die External Table SALES_EXTENDED_EXT bestätigt den Erfolg.

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services