X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

DBMS_CLOUD: Zugriff auf Object Storage aus der Oracle Datenbank - Praktische Anwendungsfälle

Marcus Schroeder
Master Principal Sales Consultant

Vor kurzer Zeit hat meine Kollegin Ulrike Schwinn in einem Blog-Eintrag die Installation des Oracle Datenbank Package DBMS_CLOUD beschrieben. Das hat mich auf die Idee gebracht die gängigsten Operationen des Package DBMS_CLOUD vorzustellen und die Funktionsweise in einfachen Skripten auszuführen. Natürlich ist das alles größtenteils in der Doku beschrieben aber um sich mit dem Thema DBMS_CLOUD schnell auseinandersetzen zu können, hilft es praktische Anwendungsfälle mit dem entsprechenden Skripten anzubieten. Jeder der Anwendungsfälle ist leicht nachzuvollziehen und einfach auszuprobieren. Die einzelnen Skripte und die verwendeten Beispiel-Daten finden Sie am Ende der jeweiligen Abschnitte. Alle verwendeten Skripte/Beispiel-Daten können Sie sich auch hier zusammen herunterladen.

Voraussetzungen

Um die Anwendungsfälle auszuprobieren, müssen folgende Voraussetzungen erfüllt sein:

  1. Eine Installation von DBMS_CLOUD & DBMS_SODA in einer Oracle Datenbank. Diese kann man On-Premises oder in der Cloud (inklusive in Autonomous Database) durchführen.
  2. Zugriff auf einen Object Storage. Die URI auf einen Object Storage z.B. Oracle Cloud Infrastructure, AWS S3, Azure Blob Storage oder S3 compatible Object Storage.
  3. Credentials. Um auf den Object Storage zuzugreifen, benötigen Sie den Benutzername und das Passwort.
  4. Einen Oracle-Benutzer in dem die DBMS_CLOUD und DBMS_SODA Packages ausführbar sind. Testen kann man das mit dem SQL Befehl desc DBMS_CLOUD bzw. desc DBMS_SODA. Bekommen Sie nach dem Ausführen des Befehls eine Auflistung der Funktionen/Prozeduren, sind die Packages ausführbar. 

Anwendungsfälle

Folgende Anwendungsfälle werden in den Skripten ausgeführt:

Die folgenden Anwendungsfälle wurden in einem Oracle Object Storage in der Oracle Cloud durchgeführt, sind aber in vollem Umfang in jeder im Abschnitt Voraussetzung gelisteten Object Storage Varianten identisch lauffähig.

DBMS_CLOUD.CREATE_CREDENTIAL

Die Zugriffs-Voraussetzung für den Object-Storage ist das Anlegen von Credentials. Diese werden verschlüsselt innerhalb der Datenbank gespeichert und können für den spezifischen Datenbank-Benutzer verwendet werden. Die Credentials werden für diese Anwendungsfälle im Benutzer-Schema SCOTT angelegt. Der username/password ist die Kombination, mit der sich am Object Storage angemeldet wird. Die Fett gedruckten Parameter müssen in allen weiteren Skripten angepasst werden!

/* Drop Credentials TESTCRED*/
BEGIN
  DBMS_CLOUD.DROP_CREDENTIAL(credential_name => 'TESTCRED');
END;
/

/* CREATE CREDENTIALS TESTCRED */
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'TESTCRED',
username        => 'oracleidentitycloudservice/max.mustermann@object-storage.com', 
password        => 'IhrP4ssw0rt'
);
END;
/

-> Download Script create_credentials.sql

Die Art der Credentials ist abhängig vom verwendeten Cloud Object Storage. Eine detaillierte Beschreibung welche Werte bei den verschiedenen Cloud-Anbietern zu verwenden sind finden Sie hier. Die Credentials werden verschlüsselt unter einer Credential-Bezeichnung abgelegt und können jederzeit verwendet werden. Sollten Sie diesen Zugriff längere Zeit nicht benötigen können sie mit den Prozeduren DBMS_CLOUD.DISABLE_CREDENTIAL bzw. DBMS_CLOUD.ENABLE_CREDENTIAL diesen Zugriff vorübergehend deaktivieren bzw. aktiveren.

Wichtig: Einige Prozeduren sind in der Dokumentation nicht beschrieben, Sie können jedoch mit dem Befehl 

desc DBMS_CLOUD;

alle verfügbaren Funktionen und Prozeduren inklusive benötigten Parameter auflisten, selbst wenn diese nicht in der Doku beschrieben werden.

DBMS_CLOUD.LIST_OBJECTS

Mit dieser Funktion können alle Objekte unter der Object Storage URI aufgelistet werden. Die URI weist ein vom Cloud-Anbieter abhängiges Format auf:

Oracle Cloud Infrastructure - Object Storage

https://objectstorage.region.oraclecloud.com/n/<namespace-string>/b/<bucket>/o/<filename>

Beispiel:

https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/<beispieldatei.csv>

AWS S3

https://s3-us-west-2.amazonaws.com/adb/channels.txt 

Azure Blob Storage

https://db_user.blob.core.windows.net/adb/channels.txt

Haben Sie die korrekte URI ermittelt, können wir mit dem Auflisten der Dateien im Object Storage beginnen. Der Aufruf ist simple: 

/* List Objects in Bucket */
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('TESTCRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/');

Angezeigt werden alle unter der URI vorhandenen Dateien mit den dazugehörigen Metadaten.

Bild 1: Auflistung der Dateien im Object Storage

-> Download Script list_objects.sql

Man kann sich auch die Inhalte einer Datei anzeigen lassen, in diesem Beispiel speichere ich den Inhalt der Datei in einen CLOB:

/* List object to clob */
SELECT TO_CLOB(
     DBMS_CLOUD.GET_OBJECT(
       credential_name => 'TESTCRED',
       object_uri      => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/chan_v3.dat'))
FROM DUAL;

Diese Funktion ist nicht dazu gedacht den Inhalt einer Datei zu verarbeiten, sondern soll einen Überblick über die Inhalte geben.

-> Download Script list_objects_into_clob.sql

-> Download Testfile chan_v3.dat

DBMS_CLOUD.CREATE_EXTERNAL_TABLE

Mit External Tables können Oracle Tabellen Daten ausserhalb der Datenbank in Dateien gespeichert werden. Dabei werden die Metadaten in der Datenbank gespeichert und die Daten verbleiben in externen Dateien. Es werden nicht nur CSV-Dateien unterstützt, sondern auch Parquet und Arvo Formate. Um eine CSV-Datei als Grundlage für einen External Table zu verwenden wird folgender Aufruf verwendet: 

/* Create external table called Channels */
DROP TABLE CHANNELS;
BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(   
      table_name =>'CHANNELS',   
      credential_name =>'TESTCRED',   
      file_uri_list =>'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/chan_v3.dat',
      format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true'),   
      column_list => 'CHANNEL_ID NUMBER,
                      CHANNEL_DESC VARCHAR2(100),
                      CHANNEL_CLASS VARCHAR2(100),
                      CHANNEL_CLASS_ID NUMBER,
                      CHANNEL_TOTAL VARCHAR2(100),
                      CHANNEL_TOTAL_ID NUMBER'
                     );
   END;
/ 
/* Show data '/
SELECT * FROM CHANNELS; 

Bei einem External Table müssen alle Spaltennamen und Datentypen im Parameter "column_list" definiert werden. Ein weiterer wichtiger Parameter ist "format", dieser beschreibt verschiedene Möglichkeiten mit den Daten aus der Datei umzugehen. In diesem Fall werden nicht vorhandene Einträge in Spalten ignoriert und die Doppelten-Anführungszeichen im Datensatz beim Lesen entfernt. Es gibt weiterer Parameter wie zum Beispiel verschiedene CSV-Trennzeichen. Eine Auflistung aller Parameter bekommen Sie unter dem folgenden Link . Lassen sie sich bitte nicht von der json_object-Funktion für den "format" Parameter verwirren, die Schlüssel-Werte-Paare werden an die Prozedur EXTERNAL_TABLE im JSON-Format übergeben. Es bedeutet nicht, dass die Quell-Datei im JSON-Format vorliegen muss. 

Ein weiterer Hinweis: Alle Operationen können nur auf einem Object Storage/URI durchgeführt werden, da es nur möglich ist ein Credential-Parameter zu verwenden. Das bedeutet, dass ein External Table die Daten nicht über verschiedenen Object Storage Ablagen/URIs Daten auslesen kann.

-> Download Script external_table.sql

Liegen mehrere Dateien im Object Storage, die Sie in einem External Table darstellen wollen, können Sie diese mit Wildcards einbeziehen. Möchten Sie in dem obigen Beispiel nicht nur die Datei "chan_v3.dat", sondern auch "chan_v4.dat" darstellen, können Sie dies mit der Wildcard "?" angeben. In diesem Fall würde die URI folgendermaßen aussehen:

...
file_uri_list =>'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/chan_v?.dat',...
...

Neben dem Fragezeichen ist es auch möglich durch einen * mehrere Wildcards zu verwenden. Wenn z.B. alle Dateien mit dem Anfang chan_*.dat verwendet werden sollen.

-> Download Script external_table_wildcard.sql

-> Download Testfile chan_v4.dat

Wenn die Quell-Dateien JSON-Formate verwenden, sind keine großen Änderungen bei der Erstellung von External Tables nötig. Einzig die Format-Angaben müssen geändert und die Spalten-Bezeichnungen werden weggelassen, da JSON-Formate die Spaltennamen beinhalten. 

/* enable DBMS_OUTPUT */
SET SERVEROUTPUT ON;

/* Drop JSON Table*/
DROP TABLE j_purchaseorder;

/* load json document into table */
BEGIN  
 DBMS_CLOUD.CREATE_EXTERNAL_TABLE(    
    table_name      => 'J_PURCHASEORDER',
    credential_name => 'TESTCRED',
    file_uri_list   => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/PurchaseOrders.dmp',
    column_list     => 'json_document blob',
    field_list      => 'json_document CHAR(5000)'
    );
END;
/

/* Output from Table */
SELECT JSON_VALUE(json_document,'$.Requestor'),    
       JSON_VALUE(json_document,'$.ShippingInstructions.Address.city') 
FROM J_PURCHASEORDER PO where rownum < 50; 

-> Download Script external_table_json.sql

-> Download Testfile purchaseorders.dmp

Wenn die Quell-Dateien Parquet- oder ARVO-Formate verwenden, sind keine großen Änderungen bei der Erstellung von External Tables nötig. Einzig die Format-Angaben müssen geändert und die Spalten-Bezeichnungen werden weggelassen, da Parquet und ARVO-Formate die Spaltennamen beinhalten. 

/* Create external table called Channels */
DROP TABLE CHANNELS;
BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(   
      table_name      =>'CHANNELS',   
      credential_name =>'TESTCRED',   
      file_uri_list   =>'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/sales_extended.parquet',
      format          => json_object('type' value 'parquet', 'schema' value 'first') 
      );
   END;
/ 
SELECT * FROM CHANNELS; 

-> Download Scrip external_table_parquet.sql

-> Download Parquet-Test-Datei sales_extended.parquet

DBMS_CLOUD.COPY_DATA

Anders als bei den External Tables werden die Daten physisch aus der Datei im Object Storage in eine Datenbank-Tabelle kopiert. Bei dieser Prozedur gibt es keine großen Änderungen bezüglich des Aufbaus und der Parameter. Die Tabelle ist in der Oracle Datenbank im korrekten Format vorhanden, dadurch wird keine Spalten-Beschreibung benötigt.

/* Copy Data to Channels */
DROP TABLE CHANNELS;
CREATE TABLE CHANNELS (
    CHANNEL_ID                  NUMBER          NOT NULL,
    CHANNEL_DESC                VARCHAR2(20)    NOT NULL,
    CHANNEL_CLASS               VARCHAR2(20)    NOT NULL,
    CHANNEL_CLASS_ID            NUMBER          NOT NULL,
    CHANNEL_TOTAL               VARCHAR2(13)    NOT NULL,
    CHANNEL_TOTAL_ID            NUMBER          NOT NULL);

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'CHANNELS',
    credential_name => 'TESTCRED',
    file_uri_list   => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/chan_v3.dat',
    format          => JSON_OBJECT('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
END;
/
SELECT * FROM CHANNELS; 

-> Download Script copy_data.sql

Für das Kopieren eines JSON-Files benötigt man wenig Änderungen des Skripts. Eine Zuordnung der Felder zu Tabellenspalten ist nicht nötig, da das Format aus der Datei vorgegeben ist und die Daten direkt in den BLOB geschrieben werden.

/* delete table J_PURCHASEORDER */
DROP TABLE J_PURCHASEORDER;

/* Create table J_PURCHASEORDER*/
CREATE TABLE J_PURCHASEORDER (JSON_DOCUMENT blob);

/* Copy data to table J_PURCHASEORDER*/
BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'J_PURCHASEORDER',
    credential_name => 'TESTCRED',
    file_uri_list   => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/PurchaseOrders.dmp',
    field_list      => 'JSON_DOCUMENT CHAR(5000)'
 );
END;
/

/* Select table J_PURCHASEORDER */
SELECT JSON_VALUE(json_document,'$.Requestor'),
       JSON_VALUE(json_document,'$.ShippingInstructions.Address.city') 
FROM J_PURCHASEORDER PO where rownum < 50; 

-> Download Script copy_data_json.sql

DBMS_CLOUD.CREATE_HYBRID_PART_TABLE

Um die External Tables mit Oracle Tabellen Inhalten zu verknüpfen, gibt es die Prozedur CREATE_HYBRID_PART_TABLE. Ein typischer Anwendungsfall ist das Speichern von oft benötigten Daten innerhalb der Datenbank und das Auslagern von weniger benötigten Daten in externe Dateien. Neben den bekannten Parametern wird in der partition_clause die verwendeten Partitionen beschrieben. In unserem Fall verwenden wir drei Partitionen, die aufgrund des Spalte deptno nach den Werten 10, 20 und 30 partitioniert werden. Da es momentan nicht möglich ist, mittels DBMS_CLOUD.COPY_DATA Daten in einen Partition Table zu laden, müssen wir einen kleinen Umweg gehen und ein temporäre Tabelle für die 10er Partition anlegen, mittels COPY_DATA befüllen und diese Daten in die Partition der Hybrid Partition Table laden.

/* create hybrid partitioned table */
DROP TABLE EXT_EMP_DEPT_HYBRID;
BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name      => 'EXT_EMP_DEPT_HYBRID',
    credential_name => 'TESTCRED',
    format          => JSON_OBJECT('type' VALUE 'CSV'),
    column_list     => 'ENAME VARCHAR2(10), DNAME VARCHAR2(14), DEPTNO NUMBER',
    partitioning_clause => 'PARTITION BY LIST (deptno)
      ( PARTITION deptno_30 VALUES (30) EXTERNAL LOCATION
         (''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/emp_dept_30.csv''),
        PARTITION deptno_20 VALUES (20) EXTERNAL LOCATION
         (''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/emp_dept_20.csv''),
        PARTITION deptno_10 VALUES (10) )'   -- intern
     );
END;
/
/* Copy Data to temporary table PART10 */
DROP TABLE PART10;
CREATE TABLE PART10(
    ENAME VARCHAR2(10),
    DNAME VARCHAR2(14), 
    DEPTNO NUMBER);

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'PART10',
    credential_name => 'TESTCRED',
    file_uri_list   => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/emp_dept_10.csv',
    format          => JSON_OBJECT('type' VALUE 'CSV')
 );
END;
/

/* fill the table with partition 10 values */
INSERT INTO EXT_EMP_DEPT_HYBRID SELECT ENAME, DNAME, DEPTNO FROM PART10;

DROP TABLE PART10;

/* Read the whole table */
SELECT * FROM EXT_EMP_DEPT_HYBRID;

-> Download Script create_hybrid_part_table.sql

-> Download Testfile 1 emp_dept_10.csv

-> Download Testfile 2 emp_dept_20.csv

-> Download Testfile 3 emp_dept_30.csv

DBMS_CLOUD.PUT_OBJECT

Das DBMS_CLOUD Package ermöglicht nicht nur das Lesen von Dateien aus einem Object Storage, sondern auch das Erzeugen von neuen Dateien. Dadurch kann man z.B. weniger häufig benötigte Daten aus einer Tabelle in eine Datei in den Object Storage schreiben und diese später in einem Hybrid Partition Tables angeben. Um dieses Beispiel einfach zu halten lese ich aus einer Datei im Object Storage und schreibe die Inhalte wieder in eine neue Datei. Natürlich kann die Variable my_blob_data mit beliebigen Daten gefüllt werden.

/* List Objects in Bucket */
select * from DBMS_CLOUD.LIST_OBJECTS('TESTCRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/');

/*  Sending BLOB Data to Object Storage */
DECLARE
      my_blob_data BLOB;
BEGIN 
 my_blob_data :=  TO_BLOB(DBMS_CLOUD.GET_OBJECT(credential_name => 'TESTCRED',object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/chan_v3.dat'));
 
DBMS_CLOUD.PUT_OBJECT(
     credential_name => 'TESTCRED',
     object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/export.dat',
     contents => my_blob_data
     ); 
END;
/

/* List Objects in Bucket */
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('TESTCRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/');

/* drop object in object storage */
BEGIN
   DBMS_CLOUD.DELETE_OBJECT(
       credential_name => 'TESTCRED',
       object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/export.dat');
   END;
/ 

/* List Objects in Bucket */
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('TESTCRED','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<ihr_tenant_name>/b/<bucket_name>/o/');

-> Download Script export_data.sql

Fazit

Das DBMS_CLOUD Package ist einfach zu bedienen und bietet eine Fülle von Möglichkeiten mit externen Daten aus einem Object Storage umzugehen. Das übliche Verfahren, die Daten aus der Cloud in einen lokalen File-Storage zu laden und dann in einem zweiten Schritt in die Datenbank zu laden, entfällt. Dieses reduziert die Anzahl möglicher Fehler die bei dem mehrstufigen Verfahren auftauchen können. Diese Auflistung von Skripten soll Ihnen helfen möglichst schnell die verschiedenen Anwendungsfälle ausprobieren zu können. Basierend darauf können Sie schnell die vielen Möglichkeiten und auch Einschränkungen des DBMS_CLOUD Package einschätzen.

Weiterführende Links

19c DBMS_CLOUD Dokumentation

19c DBMS_SODA Dokumentation

Anwendungsbeispiel in Englisch auf Amazon S3 Storage

Installation/Konfiguration von DBMS_CLOUD on-Premises

 

 

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.