X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

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.

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 -- extern
         (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_30.csv''), 
        partition deptno_20 values (20) external location -- extern
         (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_20.csv''), 
        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 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 deutschsprachigen 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.

Handbucheinträge und weitere Informationen

Kundenerfahrung

 

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.