Datenladen: Vom Objectstore in die Autonomous Database

Mit Autonomous Database gibt es die Möglichkeit auf den Oracle Objectstore zuzugreifen. Das Interface hierfür wird über das Package DBMS_CLOUD zur Verfügung gestellt. In "älteren" Tipps haben wir schon einige Möglichkeiten dazu skizziert. Wer mehr darüber erfahren möchte, kann beispielsweise folgende Blogpostings verwenden:

Darüberhinaus stellt sich die Frage, wie können Daten direkt in Datenbanktabellen geladen werden. Ganz offensichtlich kann man hierfür External Table Zugriffe verwenden, um zum Beispiel Daten mithilfe eines INSERT, SELECT oder MERGE Statements in die entsprechenden Tabellen zu transferieren. Aber es geht auch direkt mit der COPY_DATA Funktion von DBMS_CLOUD. Darüberhinaus lässt sich das Ganze natürlich auch mit Database Jobs und dem Package DBMS_SCHEDULER planen. Wie das funktioniert, wird an folgenden Beispielen demonstriert.

Die erste Aufgabenstellung besteht darin, bestimmte Reports über den Objectstore zur Verfügung zu stellen und direkt zur späteren Weiterverarbeitung in eine Datenbanktabelle zu laden. In unserem Fall handelt es sich um das Einlesen von Billing-Daten aus einer OCI Tenancy in die Autonomous Database um analytische Auswertungen zu ermöglichen. Die Namen dieser Reports setzen sich aus einem speziellen Namenspräfix zusammen und sind im Format CSV.
Die Syntax von COPY_DATA sieht folgendermassen aus:

  
DBMS_CLOUD.COPY_DATA ( table_name        IN VARCHAR2,
	               credential_name   IN VARCHAR2,	
	               file_uri_list     IN CLOB,	
	               schema_name       IN VARCHAR2 DEFAULT,
	               field_list        IN CLOB DEFAULT,
	               format            IN CLOB DEFAULT);

Die Syntax ist dabei ganz ähnlich der External Table Syntax von DBMS_CLOUD: Wir benötigen den Namen der Datenbank Tabelle, die Credential Information, die FileURI, die Felddefinitionen und die spezielle Einstellungen im Format Argument.

Stellen wir zuerst die Tabelle zur Verfügung: sie soll REPORT_USAGE genannt werden. Um die Vorgehensweise kurz zu demonstrieren, legen wir eine Tabelle mit 6 Spalten vom Datentyp VARCHAR2 an.

   
DROP TABLE report_usage;
CREATE TABLE report_usage (col1 VARCHAR2(100), 
                           col2 VARCHAR2(100), 
                           col3 VARCHAR2(100),
                           col4 VARCHAR2(100), 
                           col5 VARCHAR2(100), 
                           col6 VARCHAR2(100));

Der Inhalt des Objectstores sieht im Beispiel folgendermassen aus.

Um Informationen über die Dateien im Objectstore zu erhalten, kann man die Tabellenfunktion LIST_OBJECTS verwenden. Sie listet die Dateinamen und die Grösse der Dateien auf.

 
SQL> SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('CREDENTIAL_US1', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens.../')
     WHERE object_name LIKE 'reports_usage-csv%';

OBJECT_NAME                                        BYTES
--------------------------------------------- ----------
reports_usage-csv_0001000000026753.csv.gz        1317568
reports_usage-csv_0001000000026754.csv.gz        1317568

Es gibt offensichtlich im Moment 2 Dateien mit dem entsprechenden Präfix. Sie sind allerdings komprimiert und im CSV Format. Kennt man die vollständigen Namen und somit die vollständige FileURI kann die Aufgabenstellung ganz einfach mit den entsprechenden COPY_DATA Aufrufen gelöst werden. In folgendem Beispiel wird die Information aus der Datei mit dem Namen reports_usage-csv_0001000000026753.csv.gz kopiert.

SQL> execute DBMS_CLOUD.COPY_DATA(table_name =>'report_usage', -
                                  credential_name => 'CREDENTIAL_US1',- 
                                  file_uri_list => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens.../reports_usage-csv_0001000000026753.csv.gz',-
                                  format => json_object('compression' value 'auto','type' value 'csv', 'skipheaders' value '1', 'rejectlimit' value 10), -
                                  field_list => 'col1, col2, col3,col4, col5, col6');
PL/SQL procedure successfully completed.

Im Argument FORMAT können über vordefinierte JSON Key:Value Paare folgende Informationen mitgegeben werden: COMPRESSION und der Wert AUTO gibt die Möglichkeit die Dateien im GZ Format zu verarbeiten. Mit TYPE und dem Wert CSV wird ein einfaches Einlesen von CSV Dateien möglich. Mit SKIPHEADERS wird im Beispiel die erste Zeile in der Datei ignoriert. Der Hinweis auf REJECTLIMIT gibt an, bei wie vielen fehlerhaften Einträgen die Verarbeitung abbricht. Das Argument FIELD_LIST gibt die Spalten an - vergleichbar mit der Verwendung im Falle von regulären externen Oracle Tables. Alle Informationen zur FORMAT Option erhält man im Link unter DBMS_CLOUD Format Options. Eine einfache Abfrage auf die Tabelle bestätigt dann das erfolgreiche Einfügen der Daten.

 
SQL> SELECT count(*) FROM report_usage;

  COUNT(*)
----------
     22590

Die Tabelle hat nun folgende Inhalte.

Für den Fall dass noch weitere Usage Dateien vorhanden sind, wird sicher ein kleines PL/SQL Programm hilfreich sein. Folgendes Programm könnte ein guter Startpunkt sein:

 
DECLARE 
  CURSOR all_files IS 
      SELECT object_name 
      FROM DBMS_CLOUD.LIST_OBJECTS('CREDENTIAL_US1', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens...')
      WHERE object_name LIKE 'reports_usage-csv%';
     
      TYPE filestab IS TABLE OF VARCHAR2(2000);
      file_tab filestab;
      idx PLS_INTEGER;
      uri VARCHAR2(400);

BEGIN
 OPEN all_files;
 FETCH all_files bulk collect INTO file_tab;
 CLOSE all_files;

 FOR idx IN 1..file_tab.count LOOP 
       uri:= 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens...'||file_tab(idx); 
       DBMS_CLOUD.COPY_DATA(table_name => 'report_usage', 
                            credential_name => 'CREDENTIAL_US1', 
                            file_uri_list => uri,
                            format => json_object('compression' value 'auto','type' value 'csv', 'skipheaders' value '1', 'rejectlimit' value 10),
                            field_list => 'col1, col2, col3, col4, col5, col6');
       COMMIT; 
 END LOOP;
END;
/

Erweitern wir die Aufgabenstellung ein wenig und befüllen die Usage Tabelle REPORT_USAGE täglich mit den Informationen aus dem Objectstore. Dort wird täglich eine neue Datei mit neuen Usage Daten hochgeladen. Der Name der Datei enthält dabei eine Zahl, die jedes Mal um 1 erhöht wird. Folgende Abfrage enthält dann den Namen der aktuellsten Datei.

 
SQL> SELECT max(object_name) recentfile from DBMS_CLOUD.LIST_OBJECTS('CREDENTIAL_US1', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens...') 
     WHERE object_name LIKE 'reports_usage-csv%';

RECENTFILE                                   
---------------------------------------------
reports_usage-csv_0001000000026754.csv.gz

Die folgende Stored Procedure soll dann täglich ausgeführt werden.

 
CREATE OR REPLACE PROCEDURE insertrecentfile
AS
 recent_file VARCHAR2(200);

 uri VARCHAR2(400);

BEGIN

  SELECT max(object_name) INTO recent_file FROM DBMS_CLOUD.LIST_OBJECTS('CREDENTIAL_US1', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens.../') 
  WHERE object_name LIKE 'reports_usage-csv%';

  uri:= 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/...Fortsetzung des Linknamens.../'||recent_file;
  DBMS_CLOUD.COPY_DATA(table_name      => 'report_usage', 
                       credential_name => 'CREDENTIAL_US1', 
                       file_uri_list   =>  uri, 
                       format          =>  json_object('compression' value 'auto','type' value 'csv', 'skipheaders' value '1', 'rejectlimit' value 10),
                       field_list      =>  'col1, col2, col3, col4');
  COMMIT;
END;

/

Der Scheduler Job soll diese Prozedur jeden Tag einmal ausführen. Jobklassen bieten dabei die Möglichkeit, Jobs für die Ressourcenzuweisung und Priorisierung zu gruppieren. Die Jobklassen und damit verbundene Ressourcegruppen können vorab wie folgt selektiert werden.

 
SQL> SELECT job_class_name, resource_consumer_group, comments 
     FROM dba_scheduler_job_classes order by 2;


JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP             COMMENTS                                                    
------------------------------ ----------------------------------- ------------------------------------------------------------
HIGH                           HIGH                                High priority jobs                                          
LOW                            LOW                                 Low priority jobs                                           
MEDIUM                         MEDIUM                              Medium priority jobs                                        
ORA$AT_JCNRM_SA                ORA$AUTOTASK                        auto space advisor                                          
ORA$AT_JCNRM_SQ                ORA$AUTOTASK                        sql tuning advisor                      

Wie man leicht erkennen kann, heißen die Ressourcegruppen in Autonomous Database wie die vordefinierten Services - nämlich LOW, MEDIUM und HIGH. Der Scheduler Job sieht in unserem Fall dann folgendermassen aus.

 
SQL> execute DBMS_SCHEDULER.DROP_JOB('listobject');

SQL> execute DBMS_SCHEDULER.CREATE_JOB(job_name        => 'listobject', -
                                       job_type        => 'STORED_PROCEDURE', -
                                       job_action      => 'ADMIN.INSERTRECENTFILE',- 
                                       start_date      => SYSDATE, -
                                       repeat_interval => 'FREQ = DAILY; INTERVAL = 1',-
                                       auto_drop       => FALSE, -
                                       job_class       => 'MEDIUM', -
                                       enabled         => TRUE); 

Zur Überprüfung können wir die üblichen Data Dictionary Views zu rate ziehen.

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='dd.mm.yyyy hh24:mi';
Session altered

SQL> SELECT job_name, next_run_date, enabled, state, job_class, comments
     FROM user_scheduler_jobs; 

JOB_NAME   NEXT_RUN_DATE    ENABL STATE                JOB_CLASS COMMENTS                      
---------- ---------------- ----- -------------------- --------- --------------------
LISTOBJECT 18.05.2019 15:18 TRUE  SCHEDULED            MEDIUM
                                        
SQL> SELECT job_name, status, actuaL_start_date, errors 
     FROM user_scheduler_job_run_details;

JOB_NAME   STATUS                         ACTUAL_START_DAT ERRORS    
---------- ------------------------------ ---------------- ----------
LISTOBJECT SUCCEEDED                      17.05.2019 17:01     

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services