Documentazione dell'infrastruttura Oracle Cloud

Informazioni sulle tabelle esterne con partizionamento dei file di origine

In Autonomous Database è possibile creare tabelle esterne partizionate da dati partizionati in stile Hive o da semplici dati partizionati in cartelle memorizzati nell'area di memorizzazione degli oggetti cloud.

Utilizzando il partizionamento del file di origine, invece di fornire una specifica di partizione completa, la procedura deriva le informazioni di partizionamento dal percorso del file per determinati modelli di file. Si consideri, ad esempio, le seguenti specifiche di file di dati:

  • Stile Hive: ad esempio: sales/country=USA/year=2020/month=01/file1.csv

  • Stile semplice di partizionamento delle cartelle: ad esempio: sales/USA/2020/01/file1.parquet

L'utilizzo di uno di questi formati di partizionamento comuni semplifica notevolmente la creazione e la gestione di tabelle esterne partizionate. Inoltre, anche se le colonne delle partizioni potrebbero non essere visualizzate nel file di dati, è comunque possibile eseguirne la query utilizzando SQL. Il partizionamento dei dati migliora anche le prestazioni delle query riducendo drasticamente la quantità di dati scansionati. In questo esempio, quando si esegue una query sui dati 'USA', la query può saltare la scansione dei file per altri paesi.

Formato Hive - Dati partizionati nell'area di memorizzazione degli oggetti cloud

Hive offre un formato di metadati standard per i motori di elaborazione dei big data. I dati partizionati nell'area di memorizzazione degli oggetti cloud generati in formato Hive sono rappresentati in formato folder/subfolder. Ad esempio, nell'area di memorizzazione degli oggetti cloud viene memorizzato un file di dati in formato Hive come indicato di seguito. 

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

I file salvati in formato partizionato Hive forniscono informazioni sulla partizione nel nome del percorso del file di dati. Il nome del percorso del file di dati include informazioni sul contenuto dell'oggetto, inclusi i nomi delle colonne di partizione e i valori delle colonne di partizione (il file di dati non include le colonne di partizione e i valori associati).

Ad esempio, si consideri una tabella SALES partizionata esterna creata dai dati del formato Hive nell'area di memorizzazione degli oggetti cloud: 

.../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

Le informazioni sulla partizione in formato Hive mostrano che i file di dati nell'area di memorizzazione degli oggetti cloud sono partizionati da country, year e month e che i valori per queste colonne di partizione vengono specificati anche all'interno del nome del percorso in formato Hive per ogni file di dati (il nome del percorso include valori per le colonne partizionate: country, year e month).

I nomi delle colonne nel percorso verranno utilizzati dall'API per semplificare la definizione della tabella.

Formato cartella semplice - Dati partizionati nell'area di memorizzazione degli oggetti cloud

I dati partizionati nell'area di memorizzazione degli oggetti cloud generati in formato cartella sono rappresentati in un formato folder/subfolder, simile ai dati partizionati in formato Hive, ma le informazioni nel percorso mostrano i valori delle colonne e non includono i nomi delle colonne. Inoltre, con i dati partizionati in formato cartella, l'ordine di partizione specificato nel nome dell'oggetto è significativo e deve corrispondere all'ordine nelle colonne della tabella.

Ad esempio, nell'area di memorizzazione degli oggetti cloud viene memorizzato un file di dati formato cartella come indicato di seguito.

table/partition1_value/partition2_value/*.parquet

Il percorso include sia i valori delle colonne di partizione, nell'ordine delle colonne di partizione, sia i file di dati. Autonomous Database consente di creare una tabella partizionata esterna dai dati del formato delle cartelle e di eseguire una query utilizzando le partizioni specificate.

I file salvati nel formato partizionato della cartella forniscono i valori della colonna della partizione dati nel nome del file. A differenza di Hive, i percorsi non includono il nome della colonna, pertanto è necessario fornire i nomi delle colonne. L'ordine delle colonne della partizione è importante e l'ordine nel nome del file per i nomi delle partizioni delle colonne deve corrispondere all'ordine nel parametro partition_columns.

Informazioni sulla query dei dati partizionati nell'area di memorizzazione degli oggetti cloud

Quando si esegue una query sui dati partizionati esterni in formato Hive, il motore di query comprende e utilizza le informazioni di partizionamento dal nome del percorso del file. Ad esempio, si consideri una tabella SALES partizionata esterna in cui il file di origine, sales/country=USA/year=2020/month=02/file3.csv nell'area di memorizzazione degli oggetti, include i dati di vendita seguenti: 

tents, 291
canoes, 22
backpacks, 378

I valori country nel nome del percorso e i valori del periodo di tempo per month e year non vengono specificati come colonne nel file di dati. I valori della colonna della partizione vengono specificati solo nel nome del percorso con i valori visualizzati: USA, 2020 e 02. Dopo aver creato una tabella partizionata esterna con questo file di dati, è possibile utilizzare le colonne della partizione e i relativi valori quando si esegue una query sulla tabella partizionata esterna.

Ad esempio:

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

Il vantaggio della creazione di una tabella partizionata esterna con dati generati come dati partizionati in formato Hive è che il motore di query è ottimizzato per eseguire il partizionamento eliminando i dati per selezionare la partizione corretta e la query seleziona solo i dati da una partizione e deve solo cercare un singolo file di dati. Pertanto, la query richiederebbe solo una scansione del file file3.csv (/sales/country=USA/year=2020/month=02/file3.csv). Per grandi quantità di dati, tale eliminazione delle partizioni può fornire miglioramenti significativi delle prestazioni.

Utilizzando le tabelle esterne standard di Oracle Database, la colonna della partizione deve essere disponibile come colonna nel file di dati per utilizzarla per le query o le definizioni della partizione. Senza la gestione speciale disponibile con le tabelle partizionate esterne in Autonomous Database, questo sarebbe un problema se si desidera utilizzare i dati memorizzati in formato Hive nell'area di memorizzazione degli oggetti cloud, poiché sarebbe necessario rigenerare i file di dati per includere la partizione come colonna nel file di dati.

Informazioni sulla creazione di tabelle esterne partizionate

Quando si utilizzano dati non strutturati memorizzati in formato Hive nell'area di memorizzazione degli oggetti cloud e si crea una tabella partizionata esterna, le colonne e i relativi tipi non possono essere derivati dal file di origine. Pertanto, le colonne e i relativi tipi di dati devono essere specificati con il parametro column_list. Per creare tabelle esterne partizionate, utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE per specificare le colonne della partizione e i relativi tipi come indicato di seguito.

  • La radice per l'elenco di file viene specificata nel nome del percorso con il parametro file_uri_list. Ad esempio, http://.../sales/*

  • I nomi delle colonne e i tipi di dati vengono specificati con il parametro column_list.

  • L'opzione partition_columns nel parametro format specifica le colonne della partizione.

  • La DLL generata include le colonne specificate nel nome del percorso.

In questo esempio, quando viene creata la tabella esterna, le colonne country, year e month vengono aggiunte al parametro column_list. La tabella esterna viene creata con le colonne country, year e month, che non si trovano nei file di dati, e le partizioni di lista vengono create abilitando l'eliminazione delle partizioni.

Quando si utilizzano dati strutturati, ad esempio file Parquet, Avro o ORC memorizzati in formato cartella nell'area di memorizzazione degli oggetti cloud, le colonne e i relativi tipi di dati sono noti e non è necessario specificare l'elenco di colonne come richiesto con i dati non strutturati. Per creare tabelle esterne partizionate, utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE per specificare le colonne della partizione e i relativi tipi come indicato di seguito.

  • La radice per l'elenco di file viene specificata nel nome del percorso con il parametro file_uri_list. Ad esempio, http://.../sales/*
  • Il parametro column_list non è obbligatorio per i file strutturati. Se non si specifica l'elenco di colonne, è necessario definire le colonne di partizione e i relativi tipi di dati quando si crea la tabella partizionata esterna. Utilizzare l'opzione partition_columns nel parametro format per specificare le colonne della partizione e i relativi tipi di dati.
  • La DLL generata include le colonne specificate nel nome del percorso.

Per esempi completi, vedere Query di dati partizionati esterni con organizzazione file di origine formato Hive e Query di dati partizionati esterni con organizzazione file di origine formato cartella.

Argomento padre: Query di tabelle esterne con partizionamento specificato nei file di origine

Partizionamento esterno: file di origine CSV con cartelle in stile Hive

Mostra come creare tabelle partizionate esterne con file di origine CSV memorizzati nell'area di memorizzazione degli oggetti cloud nelle cartelle in stile Hive.

Elenco file di origine:

.../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"]}');
Nota

Il parametro partition_columns nel parametro format deve corrispondere ai nomi di colonna trovati nel percorso (ad esempio, la colonna country corrisponde a "country=…")

Partizionamento esterno: file di origine CSV con cartelle semplici

Mostra come creare tabelle partizionate esterne con file di origine CSV memorizzati nell'area di memorizzazione degli oggetti cloud in formato cartella semplice.

Elenco file di origine:

.../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"]}');
Nota

La chiamata API è uguale a quella dell'esempio precedente, ma l'ordine di partition_columns nel parametro format è significativo perché il nome della colonna non si trova nel percorso del file.

Partizionamento esterno: file di origine Parquet con cartelle in stile Hive

Mostra come creare tabelle partizionate esterne con file di origine Parquet memorizzati nell'area di memorizzazione degli oggetti cloud nelle cartelle in stile Hive.

Elenco file di origine:

.../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)')
            )
        )

);
Nota

Il parametro column_list non è specificato. Come mostrato, per ogni colonna di partizione specificare sia il nome che il tipo di dati nel parametro format partition_columns.

Partizionamento esterno: Parquet con cartelle semplici

Mostra come creare tabelle partizionate esterne con file di origine Parquet memorizzati nell'area di memorizzazione degli oggetti cloud in formato cartella semplice.

Elenco file di origine:

.../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)')
            )
        )

);
Nota

Il parametro column_list non è specificato. È necessario includere sia il nome che il tipo di dati per le colonne della partizione. Inoltre, l'ordine di partition_columns nella clausola format è importante perché il nome della colonna non si trova nel percorso del file.