|
Hybrid Partitioned Tables: Lifecycle Management leicht gemacht
Mit Oracle External Table kann man seit jeher auf Datenmengen, die in Dateien ausserhalb der Datenbank gespeichert sind, lesend zugreifen.
In der Datenbank werden dabei nur die erforderlichen Metadaten der External Tables gespeichert.
Voraussetzung ist immer das Vorhandensein eines logischen Directories und die Verwendung eines entsprechenden Access Treibers wie z.B. ORACLE_LOADER oder
ORACLE_DATAPUMP.
Die Dateien können in unterschiedlichen Formaten im Dateisystem, im HDFS oder aber auch mit Autonomous Database im Oracle Cloud Object Storage
vorliegen. Bei Letzterem ist die Verwendung des Package DBMS_CLOUD erforderlich.
Seit geraumer Zeit gibt es zusätzlich die Möglichkeit External Tables auch partitioniert abzulegen. Damit war es nur noch eine Frage der Zeit, dass
man auch eine gemischte - also eine hybride Speicherung - mit der External Table Technik erlaubt.
Mit Oracle Database 19c ist es soweit: nun besteht die Möglichkeit hybride partitionierte Tabellen (engl. hybrid partitioned tables) anzulegen.
Die Partitionen von hybriden, partitionierten Tabellen können dann sowohl in der Oracle Datenbank in Oracle Tablespaces als auch in
externen Quellen vorliegen, wie z.B. Linux-Dateien mit CSV-Einträgen oder Dateien auf dem Hadoop Distributed File System (HDFS).
So lassen sich beispielsweise Partitionen, die nicht mehr so häufig abgefragt werden, einfach in externe Dateien verschieben,
um so eine kostengünstigere Speicherlösung zu verwenden.
Folgende Grafik zeigt die Architektur einer Hybrid Partitioned Table.
Hybrid Partitioned Tables unterstützen dabei alle existierenden Tabellentyptreiber für externe Partitionen wie
ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS und ORACLE_HIVE.
Um hybrid partitioned Tables anzulegen, wird eine erweiterte External Table Syntax verwendet. Hybrid Partitioned Tables können mit
CREATE TABLE oder auch mit ALTER TABLE erzeugt werden. Mit dem ALTER TABLE Kommando können dann beispielsweise "normal" partitionierte Tabellen
nachträglich mit externen Partitionen angereichert werden. Eine gute Einführung findet sich im Handbuch "VLDB and Partitioning Guide" im Kapitel Hybrid Partitioned Tables.
Hier werden u.a. auch die Einschränkungen beschrieben. So sind die Operationen wie SPLIT, MERGE und MOVE nicht erlaubt und als Partitionierungstyp
bisher nur Single Level Partitionierung mit LIST und RANGE möglich. Darüberhinaus gibt es im Moment auch keine Unterstützung für LOB, LONG und ADT Datentypen.
Hybrid Partitioned Tables mit CREATE TABLE erzeugen
In Szenario 1 wird eine hybrid partitioned Table mit CREATE TABLE angelegt. Die Partition DEPTNO_10 ist eine interne Partition, DEPTNO_20 eine externe Partition.
Die Klausel EXTERNAL PARTITION ATTRIBUTES gibt den Treiber (hier: ORACLE_LOADER) und das Directory (hier: HOME) für die externe(n) Partition(en) an.
Die CSV Datei emp_dept_20.csv im Directory HOME speichert die Informationen zur Partition DEPTNO_20.
Tipp: Mit dem SQL*Plus Kommando SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}] lassen sich die Daten aus Tabellen einfach
im CSV Format ausgeben und speichern. Ein Beispiel dazu findet sich im Tipp
12.2: Ausgewählte Basisfeatures in SQL und PL/SQL .
Das Directory mit Namen HOME ist vorhanden, wie wir zuerst überprüfen.
SQL> SELECT directory_name, directory_path
FROM all_directories;
DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------------------------------------
LOBHOME /home/oracle/LOB
TEST /home/oracle/test
HOME /home/oracle
...
Nun wird eine hybrid partitioned Table EXT_EMP_DEPT_HYBRID_1 erzeugt. Sie ist LIST partitioniert nach der Spalte DEPTNO.
DROP TABLE scott.ext_emp_dept_hybrid_1;
CREATE TABLE scott.ext_emp_dept_hybrid_1
( "ENAME" varchar2(10),
"DNAME" varchar2(14),
DEPTNO number)
EXTERNAL PARTITION ATTRIBUTES
(
TYPE oracle_loader
DEFAULT DIRECTORY home
REJECT LIMIT UNLIMITED
)
PARTITION BY LIST (deptno)
( PARTITION deptno_10 VALUES (10) , -- intern
PARTITION deptno_20 VALUES (20) EXTERNAL LOCATION ('emp_dept_20.csv')) -- extern
/
-- Befüllen der internen Partition
INSERT INTO "SCOTT".EXT_EMP_DEPT_HYBRID_1
SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10;
-- Das Resultat abfragen
SQL> SELECT * FROM scott.ext_emp_dept_hybrid_1;
ENAME DNAME DEPTNO
---------- -------------- ----------
CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
"SMITH" "RESEARCH" 20
"JONES" "RESEARCH" 20
"SCOTT" "RESEARCH" 20
"ADAMS" "RESEARCH" 20
"FORD" "RESEARCH" 20
8 rows selected.
Weitere Partitionen lassen sich dann mit dem ALTER TABLE Kommando wie folgt hinzufügen.
SQL> ALTER TABLE scott.ext_emp_dept_hybrid_1
ADD PARTITION deptno_30 VALUES (30)
EXTERNAL DEFAULT DIRECTORY home LOCATION ('emp_dept_30.csv');
Das Ganze kann auch mit dem Treiber ORACLE_DATAPUMP verwendet werden. Dazu ist es zuerst notwendig die externen Dateien, die später als externe Partitionen verwendet werden sollen, mit einer Hilfstabelle zu erzeugen. Die Dateien haben in unserem Beispiel dann den Namen emp_dept_20.exp bzw. emp_dept_30.exp. Folgendes Beispiel erzeugt die Datei emp_dept_20.exp.
DROP TABLE ext_emp_dept_help;
CREATE TABLE ext_emp_dept_help
ORGANIZATION EXTERNAL
(
TYPE oracle_datapump
DEFAULT DIRECTORY home
LOCATION ('emp_dept_20.exp')
)
REJECT LIMIT UNLIMITED
AS SELECT e.ename, d.dname, deptno FROM scott.dept d JOIN scott.emp e USING (deptno) WHERE deptno=20
/
--DROP TABLE ext_emp_dept_help;
Danach wird die Datei emp_dept_30.exp nach dem gleichen Verfahren erstellt.
Nun sind wir in der Lage im nächsten Schritt die hybrid partitioned Tabelle anzulegen.
Die externe Partition DEPTNO_20_30 greift dann auf die externen Dateien emp_dept_20.exp und emp_dept_30.exp zu,
die wir vorher mit der Hilfstabelle erzeugt und im Directory HOME zur Verfügung gestellt haben.
drop table "scott.ext_emp_dept_hybrid_2;
CREATE TABLE scott.ext_emp_dept_hybrid_2
( "ENAME" varchar2(10) ,
"DNAME" varchar2(14),
DEPTNO number)
EXTERNAL PARTITION ATTRIBUTES
(
TYPE oracle_datapump
DEFAULT DIRECTORY home
)
PARTITION BY LIST (deptno)
( PARTITION deptno_10 VALUES (10), -- intern
PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.exp','emp_dept_30.exp') -- extern
)
/
Partitioned Tables in Hybrid Partitioned Tables umwandeln
In Szenario 2 wird eine list partitioned Table in eine hybrid partitioned Table umgewandelt. Wir verwenden dazu eine "normale" list partitioned Table mit der internen Partition DEPTNO_10.
DROP TABLE scott.ext_emp_dept_hybrid_3;
CREATE TABLE scott.ext_emp_dept_hybrid_3
( "ENAME" varchar2(10),
"DNAME" varchar2(14),
DEPTNO number)
PARTITION BY LIST (deptno)
( PARTITION deptno_10 values (10));
-- Befüllen der Partition
INSERT INTO scott.ext_emp_dept_hybrid_3
SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10;
Um externe Partitionen einbinden zu können, muss zuerst die Klausel EXTERNAL PARTITION ATTRIBUTES hinzugefügt werden. Damit wird dann der Treiber (hier ORACLE_LOADER) und das Default Directory (hier HOME) bekanntgegeben.
ALTER TABLE scott.ext_emp_dept_hybrid_3
ADD EXTERNAL PARTITION ATTRIBUTES
( TYPE oracle_loader
DEFAULT DIRECTORY home
REJECT LIMIT UNLIMITED );
Im nächsten Schritt können dann externe Partitionen hinzugefügt werden. Hier wird die externe Partition DEPTNO_20_30 angefügt, die ihre Informationen aus den Dateien emp_dept_20.csv und emp_dept_30.csv erhält.
ALTER TABLE scott.ext_emp_dept_hybrid_3
ADD PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.csv', 'emp_dept_30.csv');
Hybrid Partitioned Tables und Oracle Object Storage
In Autonomous Database mit der Version 19c können ebenfalls hybrid partitioned Tables verwendet werden. Wie im Falle von External Tables oder Partitioned External Tables wird dazu das Package DBMS_CLOUD verwendet. Ein Beispiel zum Anlegen einer External Table mit DBMS_CLOUD findet sich im Community Tipp Autonomous Database: External Tables. Im Fall von hybrid partitioned Tables sieht die Syntax dann folgendermassen aus:
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE ( table_name IN VARCHAR2, credential_name IN VARCHAR2, partitioning_clause IN CLOB, column_list IN CLOB, field_list IN CLOB DEFAULT, format IN CLOB DEFAULT);
Im Handbuch "Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure" im Appendix A "Autonomous Database
Supplied Package Reference" findet sich dazu die Beschreibung der einzelnen Argumente.
Im Szenario 3 liegen die externen Informationen im Oracle Object Storage in den Dateien emp_dept_20.csv und emp_dept_30.csv, die zu Beginn schon erzeugt worden sind.
Wie beim Laden von Daten mit Data Pump muss auch in diesem Fall gewährleistet sein, dass die Kommunikation mit der Autonomous Database abgesichert
ist und nur autorisierte Daten geladen werden. Dazu sind die Datenbank Credentials mit dem Package DBMS_CLOUD zuvor angelegt und als Default festgelegt worden.
Wir überprüfen die Datenbank Credentials. Falls noch nicht vohanden, werden sie mit ALTER DATABASE PROPERTY festgelegt.
SQL> SELECT credential_name, username, enabled, comments FROM dba_credentials;
CREDENTIAL_NAME USERNAME ENABL COMMENTS
-------------------- ---------------------------------------- ----- ----------------------------------------
CREDENTIAL_US1 oracleidentitycloudservice/ulrike.schwin TRUE {"comments":"Created via DBMS_CLOUD.crea
n@oracle.com te_credential"}
-- falls nicht enabled
-- alter database property set default_credential = 'ADMIN.CREDENTIAL_US1';
-- falls nicht vorhanden, dann zuerst mit dbms_cloud.create_credential erzeugen
Das Vorhandensein der Dateien im Object Storage lässt sich vorab auch mit DBMS_CLOUD übeprüfen.
Hinweis: DBMS_CLOUD.CREATE_HYBRID_PART_TABLE unterstützt nicht nur Oracle Cloud Infrastructure Object Storage,
sondern auch Microsoft Azure und AWS S3.
SQL> SELECT object_name, bytes
FROM dbms_cloud.list_objects('CREDENTIAL_US1','https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/')
WHERE object_name LIKE 'emp%';
OBJECT_NAME BYTES
-------------------------------------------------- ----------
emp_dept_20.csv 114
emp_dept_20.exp 12288
emp_dept_30.csv 121
emp_dept_30.exp 12288
Wir erzeugen nun eine hybrid partitioned Table mit der Prozedur DBMS_CLOUD.CREATE_HYBRID_PART_TABLE. Für eine erfolgreiche Anwendung ist dabei die Angabe von Tabellenname, der Credential Name, das Argument FORMAT, das Argument COLUMN_LIST und die Klausel für die externen und internen Partitionen erforderlich. Zur Erinnerung: der Parameter FORMAT muss als ein JSON Objekt angegeben. Dazu gibt es zwei Möglichkeiten.
format => '{"format_option" : "format_value" }'
oder
format => json_object('format_option' value 'format_value'))
Folgendes Beispiel zeigt die Verwendung. Im Argument FORMAT können wir den Typ CSV nutzen.
DROP TABLE ext_emp_dept_hybrid_auto;
BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
table_name => 'EXT_EMP_DEPT_HYBRID_AUTO',
credential_name => 'CREDENTIAL_US1',
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/oraseemeadesandbox/b/USBUCKET/o/emp_dept_30.csv''), --extern
partition deptno_20 values (20) external location
( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_20.csv''), --extern
partition deptno_10 values (10) --intern
)'
);
END;
/
Um sicher zu gehen, dass der Zugriff mit External Table erfolgreich sein wird, empfiehlt es sich vorab mit VALIDATE_EXTERNAL_TABLE eine Prüfung durchzuführen. Gibt es eine Fehlermeldung, sollte man noch einmal das Setup prüfen. In folgendem Beispiel ist offensichtlich die URI nicht korrekt. Die Fehlermeldung gibt dabei den richtigen Hinweis darauf.
execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO'); Error starting at line : 24 in command - BEGIN DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO'); END; Error report - ORA-20011: Invalid object uri - https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/emp_dept_30.csv ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 844 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1105 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1389 ORA-06512: at line 1
Ist das Problem behoben, kann man erfolgreich auf die Daten zugreifen.
SQL> SELECT * FROM ext_emp_dept_hybrid_auto; ENAME DNAME DEPTNO ---------- -------------- ---------- ALLEN SALES 30 WARD SALES 30 MARTIN SALES 30 BLAKE SALES 30 TURNER SALES 30 JAMES SALES 30 SMITH RESEARCH 20 JONES RESEARCH 20 SCOTT RESEARCH 20 ADAMS RESEARCH 20 FORD RESEARCH 20 11 rows selected.
Möchte man den Treiber ORACLE_DATAPUMP verwenden, muss im Argument FORMAT der Treiber mit der Konstante DBMS_CLOUD.FORMAT_TYPE_DATAPUMP verwendet werden. Die beiden externen Dateien emp_dept_30.exp und emp_dept_20.exp aus dem ersten Beispiel sind dazu vorab in den Object Storage geladen worden.
DROP TABLE ext_emp_dept_hybrid_dp;
BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE (
table_name => 'EXT_EMP_DEPT_HYBRID_DP',
credential_name => 'CREDENTIAL_US1',
format => json_object('type' VALUE DBMS_CLOUD.FORMAT_TYPE_DATAPUMP),
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/oraseemeadesandbox/b/USBUCKET/o/emp_dept_30.exp''),
partition deptno_20 values (20) external location
( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_20.exp''),
partition deptno_10 values (10)
)'
);
END;
/
Nach erfolgreicher Validierung, kann man auch hier wie gewohnt auf die Informationen mit SQL Kommandos zugreifen.
SQL> execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO_DP'); PL/SQL procedure successfully completed.
Monitoring
Möchte man herausfinden, ob eine Tabelle hybrid partitioned ist, kann man dazu im Data Dictionary nachsehen. External Tables - egal ob hybrid oder nicht - lassen sich generell mit der View USER/ALL/DBA_EXTERNAL_TABLES auflisten.
SQL> SELECT table_name, owner, type_name
FROM all_external_tables;
TABLE_NAME OWNER TYPE_NAME
------------------------------ ---------- --------------------
OPATCH_XML_INV SYS ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_LIST SCOTT ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_3 SCOTT ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_2 SCOTT ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_RANGE SCOTT ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_2 SCOTT ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_1 SCOTT ORACLE_LOADER
ORDERS_H OT ORACLE_LOADER
HYPT_TO_INT_TABLE US ORACLE_LOADER
EXT_EMP_DEPT_HELP US ORACLE_DATAPUMP
9 rows selected.
Sucht man speziell nach der Eigenschaft "hybrid", sollte man die View USER/ALL/DBA_TABLES mit der neuen Spalte HYBRID verwenden.
SQL> SELECT table_name, owner, hybrid
FROM all_tables where hybrid='YES';
TABLE_NAME OWNER HYB
------------------------------ ---------- ---
EXT_EMP_DEPT_HYBRID_RANGE SCOTT YES
EXT_EMP_DEPT_HYBRID_2 SCOTT YES
EXT_EMP_DEPT_HYBRID_LIST SCOTT YES
EXT_EMP_DEPT_HYBRID_1 SCOTT YES
EXT_EMP_DEPT_HYBRID_2 SCOTT YES
EXT_EMP_DEPT_HYBRID_3 SCOTT YES
ORDERS_H OT YES
HYPT_TO_INT_TABLE US YES
8 rows selected.
Hybrid partitioned Tables werden natürlich auch in den Views, die mit external partitioned Tables eingeführt wurden, gelistet wie zum Beispiel:
- USER/ALL/DBA_XTERNAL_PART_TABLES
- USER/ALL/DBA_XTERNAL_TAB_PARTITIONS
- USER/ALL/DBA_XTERNAL_TAB_SUBPARTITIONS
- USER/ALL/DBA_XTERNAL_LOC_PARTITIONS
Folgende Abfrage mit USER_XTERNAL_PART_TABLES listet alle partitionierten external Tables auf.
SQL> SELECT table_name, type_name, default_directory_name Directory, reject_limit, access_parameters
FROM user_xternal_part_tables;
TABLE_NAME TYPE_NAME DIRECTORY
------------------------------ -------------------- --------------------
REJECT_LIMIT
----------------------------------------
ACCESS_PARAMETERS
--------------------------------------------------------------------------------
HYPT_TO_INT_TABLE ORACLE_LOADER HOME
UNLIMITED
FIELDS TERMINATED BY ','
(prod_id,cust_id,time_id DATE 'dd-mm-yyyy',chan
...
Fazit
Ab Oracle Database 19c gibt es die Möglichkeit partitionierte Tabellen mit Partitionen auszustatten, deren Daten innerhalb und ausserhalb der Datenbank liegen. So kann Speicherplatz für Daten nicht nur in der Datenbank selbst sondern auch außerhalb verwendet werden. Die Daten können dabei als Dateien im Dateisystem, im Hadoop Distributed File System (HDFS) oder wie im Fall vom Autonomous Database auch im Cloud Object Storage liegen. Ein aktuelles Beispiel liefert CERN, die damit die Möglichkeit nutzten 1 PB Daten mit Oracle Autonomous Database zu nutzen. Wer mehr dazu erfahren möchte, kann sich den Webcast Oracle Global Leaders Webcast: Managing 1 PB of data with Oracle Autonomous DW ansehen.
Weitere Informationen und Artikel zum Thema External Tables
Folgende Links listen unsere Blogeinträge zum Thema External Tables, die wir über die Jahre veröffentlicht haben - angefangen von den Grundlagen zu External Tables bis zum aktuellen Tipp in 19c.
- Grundlagen zu External Tables
- External Table in 12c: Partitionierung, Constraints, XML, Parameter zur Laufzeit usw.
- Inline External Table mit 18c
- In-Memory External Tables
- Autonomous Database: External Tables
- Hybrid Partitioned Tables: Lifecycle Management leicht gemacht
- VLDB and Partitioning Guide: Hybrid Partitioned Tables
- Appendix A "Autonomous Database Supplied Package Reference": DBMS_CLOUD
Handbucheinträge
Zurück zur Community-Seite