|
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:
- Oracle Autonomous Database: External Tables
- Oracle Autonomous Database: Komprimierte Dateien einlesen
- Oracle Autonomous Datawarehouse Cloud So geht's
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
- Oracle Autonomous Database Website
- Dokumentation Autonomous Data Warehouse
- Dokumentation Autonomous Transaction Processing
- Dokumentation DBMS_CLOUD
- Alle Tipps zum Thema Autonomous Database
- Autonomous Data Warehouse Tools and Application Test Matrix
- Oracle Autonomous Data Warehouse - Access Parquet Files in Object Stores
- What's New in Autonomous Data Warehouse
Zurück zur Community-Seite