Oracle Cloud Infrastructure-Dokumentation

Externe Tabellen mit Partitionierung der Quelldatei

In Autonomous Database können Sie partitionierte externe Tabellen aus im Hive-Stil partitionierten Daten oder aus im einfachen Ordner partitionierten Daten in Ihrem Cloud-Objektspeicher erstellen.

Anstatt eine vollständige Partitionsspezifikation anzugeben, leitet die Prozedur Partitionierungsinformationen aus dem Dateipfad für bestimmte Dateimuster ab. Beispiel: Ziehen Sie die folgenden Datenspezifikationen in Betracht:

  • Beispiel für Hive-Stil: sales/country=USA/year=2020/month=01/file1.csv

  • Beispiel für einfache Ordnerpartitionierung: sales/USA/2020/01/file1.parquet

Die Verwendung eines dieser gängigen Partitionierungsformate vereinfacht sowohl die Erstellung als auch die Verwaltung von partitionierten externen Tabellen erheblich. Auch wenn die Partitionsspalten nicht in der Datendatei erscheinen, können sie dennoch mit SQL abgefragt werden. Durch die Partitionierung von Daten wird auch die Abfrageperformance verbessert, indem die Menge der gescannten Daten deutlich reduziert wird. In diesem Beispiel kann bei der Abfrage von "USA"-Daten das Scannen der Dateien für andere Länder übersprungen werden.

Partitionierte Daten im Hive-Format im Cloud-Objektspeicher

Hive bietet ein Standard-Metadatenformat für Big-Data-Prozessoren. Partitionierte Daten im Cloud-Objektspeicher, die im Hive-Format generiert werden, werden im Format folder/subfolder dargestellt. Beispiel: Im Cloud-Objektspeicher wird eine Datendatei im Hive-Format wie folgt gespeichert: 

table/partition1=partition1_value/partition2=partition2_value/data_file.csv

Im partitionierten Hive-Format gespeicherte Dateien stellen Partitionsinformationen im Pfadnamen der Datendatei bereit. Der Datendatei-Pfadname enthält Informationen über den Objektinhalt, einschließlich Partitionsspaltennamen und Partitionsspaltenwerten (die Datei enthält nicht die Partitionsspalten und die zugehörigen Werte).

Beispiel: Eine externe partitionierte Tabelle SALES, die aus Daten im Hive-Format im Cloud Object Store erstellt wurde: 

.../sales/country=USA/year=2020/month=01/file1.csv
.../sales/country=USA/year=2020/month=01/file2.csv
.../sales/country=USA/year=2020/month=02/file3.csv
.../sales/country=USA/year=2020/month=03/file1.csv
.../sales/country=FRA/year=2020/month=03/file1.csv

Die Informationen zur Partitionierung im Hive-Format zeigen, dass die Datendateien im Cloud-Objektspeicher nach country, year und month partitioniert sind, und die Werte für diese Partitionsspalten werden auch im Pfadnamen im Hive-Format für jede Datendatei angegeben (der Pfadname enthält Werte für die partitionierten Spalten: country, year und month).

Die Spaltennamen im Pfad werden von der API verwendet, um die Tabellendefinition zu vereinfachen.

Im einfachen Ordnerformat partitionierte Daten im Cloud-Objektspeicher

Partitionierte Daten im Cloud-Objektspeicher, die im Ordnerformat generiert werden, werden ähnlich wie partitionierte Daten im Hive-Format in einem folder/subfolder-Format dargestellt. Die Informationen im Pfad enthalten jedoch die Spaltenwerte ohne die Spaltennamen. Bei partitionierten Daten im Ordnerformat ist die im Objektnamen angegebene Partitionsreihenfolge signifikant und muss mit der Reihenfolge in den Spalten der Tabelle übereinstimmen.

Beispiel: Im Cloud-Objektspeicher wird eine Datendatei im Ordnerformat gespeichert:

table/partition1_value/partition2_value/*.parquet

Der Pfad enthält sowohl Partitionsspaltenwerte in der Reihenfolge der Partitionsspalten und die Datendateien. Mit Autonomous Database können Sie eine externe partitionierte Tabelle aus Ordnerformatdaten erstellen und eine Abfrage mit den angegebenen Partitionen ausführen.

Im nach Ordnern partitionierten Format gespeicherte Dateien geben die Spaltenwerte der Datenpartition im Dateinamen an. Im Gegensatz zu Hive enthalten die Pfade den Spaltennamen nicht. Daher müssen die Spaltennamen angegeben werden. Die Reihenfolge der Partitionsspalten ist wichtig, und die Reihenfolge im Dateinamen für Spaltenpartitionsnamen muss mit der Reihenfolge im Parameter partition_columns übereinstimmen.

Partitionierte Daten im Cloud-Objektspeicher abfragen

Wenn Sie externe partitionierte Daten im Hive-Format abfragen, versteht und verwendet die Abfrage-Engine die Partitionierungsinformationen aus dem Dateipfadnamen. Beispiel: Eine externe partitionierte SALES-Tabelle, in der die Quelldatei sales/country=USA/year=2020/month=02/file3.csv im Objektspeicher die folgenden Umsatzdaten enthält: 

tents, 291
canoes, 22
backpacks, 378

Die country-Werte im Pfadnamen und die Zeitraumwerte für month und year werden nicht als Spalten in der Datei angegeben. Die Partitionsspaltenwerte werden nur im Pfadnamen mit den angezeigten Werten angegeben: USA, 2020 und 02. Nach dem Erstellen einer externen partitionierten Tabelle mit dieser Datendatei können Sie die Partitionsspalten und deren Werte verwenden, wenn Sie eine Abfrage für die externe partitionierte Tabelle ausführen.

Beispiele:

SELECT year, month, product, units 
     FROM SALES WHERE year='2020' AND month='02' AND country='USA'

Der Vorteil der Erstellung einer externen partitionierten Tabelle mit Daten, die als im Hive-Format partitionierte Daten generiert werden, ist, dass die Abfrage-Engine so optimiert ist, dass die Daten zur Auswahl der richtigen Partition bereinigt werden. Die Abfrage wählt nur Daten aus einer Partition und muss nur eine Datendatei durchsuchen. Daher erfordert die Abfrage nur einen Scan der Datei file3.csv (/sales/country=USA/year=2020/month=02/file3.csv). Bei großen Datenmengen kann ein solches Partitions-Pruning erhebliche Performanceverbesserungen bewirken.

Bei externen Standardtabellen von Oracle Database muss die Partitionsspalte als Spalte in der Datei verfügbar sein, um sie für Abfragen oder Partitionsdefinitionen zu verwenden. Ohne die spezielle Verarbeitung, die mit externen partitionierten Tabellen in Autonomous Database verfügbar ist, wäre dies ein Problem, wenn Sie Daten im Hive-Format im Cloud-Objektspeicher verwenden möchten. Sie müssten die Datendateien neu generieren, um die Partition als Spalte in die Datendatei aufzunehmen.

Partitionierte externe Tabellen erstellen

Wenn Sie unstrukturierte Daten im Hive-Format im Cloud-Objektspeicher verwenden und eine externe partitionierte Tabelle erstellen, können die Spalten und deren Typen nicht aus der Quelldatei abgeleitet werden. Daher müssen die Spalten und ihre Datentypen mit dem Parameter column_list angegeben werden. Um die partitionierten externen Tabellen zu erstellen, geben Sie die Partitionsspalten und ihre Typen wie folgt mit der Prozedur DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE an:

  • Die Root für die Liste ist im Pfadnamen mit dem Parameter file_uri_list angegeben. Beispiel: http://.../sales/*

  • Die Spaltennamen und Datentypen werden mit dem Parameter column_list angegeben.

  • Die Option partition_columns im Parameter format gibt die Partitionsspalten an.

  • Die generierte DLL enthält die im Pfadnamen angegebenen Spalten.

In diesem Beispiel werden beim Erstellen der externen Tabelle die Spalten country, year und month im Parameter column_list hinzugefügt. Die externe Tabelle wird mit den Spalten country, year und month erstellt, die nicht in den Datendateien enthalten sind, und List-Partitionen werden erstellt, um das Partition Pruning zu aktivieren.

Wenn Sie strukturierte Daten wie Parquet-, Avro- oder ORC-Dateien verwenden, die im Ordnerformat im Cloud-Objektspeicher gespeichert sind, sind die Spalten und ihre Datentypen bekannt. Sie müssen die Spaltenliste nicht wie bei unstrukturierten Daten angeben. Um die partitionierten externen Tabellen zu erstellen, geben Sie die Partitionsspalten und ihre Typen wie folgt mit der Prozedur DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE an:

  • Die Root für die Liste ist im Pfadnamen mit dem Parameter file_uri_list angegeben. Beispiel: http://.../sales/*
  • Der Parameter column_list ist für strukturierte Dateien nicht erforderlich. Wenn Sie die Spaltenliste nicht angeben, müssen Sie beim Erstellen der externen partitionierten Tabelle die Partitionsspalten und deren Datentypen definieren. Verwenden Sie die Option partition_columns im Parameter format, um die Partitionsspalten und ihre Datentypen anzugeben.
  • Die generierte DLL enthält die im Pfadnamen angegebenen Spalten.

Vollständige Beispiele finden Sie unter Externe partitionierte Daten mit Quelldatei im Hive-Format abfragen und Externe partitionierte Daten mit Quelldatei im Ordnerformat abfragen.

Übergeordnetes Thema: Externe Tabellen mit in Quelldateien angegebener Partitionierung abfragen

Externe Partitionierung - CSV-Quelldateien mit Ordnern im Hive-Stil

Zeigt, wie externe partitionierte Tabellen mit CSV-Quelldateien erstellt werden, die im Cloud-Objektspeicher in Hive-Ordnern gespeichert sind.

Quelldatei-Liste:

.../sales/country=USA/year=2020/month=01/file1.csv
.../sales/country=USA/year=2020/month=01/file2.csv
.../sales/country=USA/year=2020/month=02/file3.csv
.../sales/country=USA/year=2020/month=03/file1.csv
.../sales/country=FRA/year=2020/month=03/file1.csv

API: 

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential', 
   file_uri_list     => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.csv', 
   column_list       => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)', 
   field_list        => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
   format            => '{"type":"csv","partition_columns":["country", "year", "month"]}');
Hinweis

Die partition_columns im Parameter format müssen mit den im Pfad enthaltenen Spaltennamen übereinstimmen (Beispiel: Die Spalte country entspricht "country=…").

Externe Partitionierung - CSV-Quelldateien mit einfachen Ordnern

Zeigt, wie externe partitionierte Tabellen mit CSV-Quelldateien erstellt werden, die im Cloud-Objektspeicher im einfachen Ordnerformat gespeichert sind.

Quelldatei-Liste:

.../sales/USA/2020/01/file1.csv
.../sales/USA/2020/01/file2.csv
.../sales/USA/2020/02/file3.csv
.../sales/USA/2020/03/file1.csv
.../sales/FRA/2020/03/file1.csv

API: 

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
   table_name        => 'mysales',
   credential_name   => 'mycredential', 
   file_uri_list     =>  'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.csv', 
   column_list       => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)', 
   field_list        => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
   format            => '{"type":"csv","partition_columns":["country", "year", "month"]}');
Hinweis

Der API-Aufruf ist mit dem im vorherigen Beispiel identisch, die Reihenfolge der partition_columns im Parameter format ist jedoch wichtig, da der Spaltenname nicht im Dateipfad enthalten ist.

Externe Partitionierung: Parquet-Quelldateien mit Ordnern im Hive-Stil

Zeigt, wie externe partitionierte Tabellen mit Parquet-Quelldateien erstellt werden, die im Cloud-Objektspeicher in Hive-Ordnern gespeichert sind.

Quelldatei-Liste:

.../sales/USA/2020/01/file1.parquet
.../sales/USA/2020/01/file2.parquet
.../sales/USA/2020/02/file3.parquet
.../sales/USA/2020/03/file1.parquet
.../sales/FRA/2020/03/file1.parquet

API: 

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
   table_name            => 'mysales',
     credential_name     => 'mycredential', 
     file_uri_list       => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.parquet',
     format              => 
         json_object(    'type' value 'parquet', 
               'schema' value 'first',
               'partition_columns' value 
                  json_array(
                      json_object('name' value 'country', 'type' value 'varchar2(100)'),
                      json_object('name' value 'year', 'type' value 'number'),
                      json_object('name' value 'month', 'type' value 'varchar2(2)')
            )
        )

);
Hinweis

Der Parameter column_list ist nicht angegeben. Wie dargestellt, geben Sie für jede Partitionsspalte den Namen und Datentyp im format-Parameter partition_columns an.

Externe Partitionierung - Parquet mit einfachen Ordnern

Zeigt, wie externe partitionierte Tabellen mit Parquet-Quelldateien erstellt werden, die im Cloud-Objektspeicher im einfachen Ordnerformat gespeichert sind.

Quelldatei-Liste:

.../sales/USA/2020/01/file1.parquet
.../sales/USA/2020/01/file2.parquet
.../sales/USA/2020/02/file3.parquet
.../sales/USA/2020/03/file1.parquet
.../sales/FRA/2020/03/file1.parquet

API: 

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential', 
   file_uri_list     => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.parquet', 
   format            => 
     json_object(    'type' value 'parquet', 
                'schema' value 'first',
         'partition_columns' value 
           json_array(
              json_object('name' value 'country', 'type' value 'varchar2(100)'),
              json_object('name' value 'year', 'type' value 'number'),
              json_object('name' value 'month', 'type' value 'varchar2(2)')
            )
        )

);
Hinweis

Der Parameter column_list ist nicht angegeben. Sie müssen sowohl den Namen als auch den Datentyp für die Partitionsspalten angeben. Außerdem ist die Reihenfolge der partition_columns in der Formatklausel wichtig, weil der Spaltenname nicht im Dateipfad enthalten ist.