Documentazione dell'infrastruttura Oracle Cloud

Esegui query su tabelle esterne con partizionamento implicito

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

Argomento padre: Esegui query sui dati esterni con Autonomous Database

Informazioni sulle tabelle esterne con partizionamento implicito

In Autonomous Database utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE per creare tabelle esterne partizionate implicite da dati partizionati in stile Hive o da semplici dati partizionati in cartelle memorizzati nell'area di memorizzazione degli oggetti cloud.

Utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE per creare tabelle esterne partizionate implicite. Passando le opzioni appropriate a questa procedura, le partizioni vengono derivate dai dati di origine. Le tabelle esterne partizionate supportano la ricerca automatica in runtime delle colonne di partizione e dei relativi valori. La ricerca automatica in runtime delle modifiche nella struttura dell'area di memorizzazione degli oggetti di base, ad esempio l'aggiunta o la rimozione di oggetti, semplifica il processo di manutenzione eliminando la necessità di ulteriori procedure di sincronizzazione richieste da DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE. Ciò garantisce che i dati siano aggiornati al runtime delle query.

Con il partizionamento implicito, Autonomous Database determina automaticamente le colonne su cui una tabella viene "partizionata" in base alla struttura di file gerarchica dell'origine dello storage degli oggetti. Non è necessario dichiarare esplicitamente uno schema di partizionamento. Il partizionamento implicito offre vantaggi in termini di prestazioni simili a tabelle di partizione senza la necessità di definire in modo esplicito una tabella esterna partizionata utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

Le tabelle esterne partizionate implicite supportano i seguenti stili di denominazione per gli oggetti partizionati nell'area di memorizzazione degli oggetti:

Per ulteriori informazioni sui formati di denominazione, vedere Informazioni sulle tabelle esterne con il partizionamento dei file di origine.

Utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE, le tabelle esterne partizionate implicite possono essere create nei modi seguenti:
  • Impostare il tipo di partizione su hive con un determinato elenco di colonne di partizione

    In questo caso, implicit_partition_type è impostato su hive e implicit_partition_columns fornisce una lista di colonne di partizione.

    Ad esempio:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_type":"hive",
                          "implicit_partition_columns":["country","year","month"]}');
  • Impostare il tipo di partizione su hive senza fornire un elenco di colonne di partizione

    In questo caso, implicit_partition_type è impostato su hive e implicit_partition_columns non viene fornito. Le colonne della partizione vengono rilevate automaticamente cercando '=' nel percorso specificato da file_uri_list. column_name si trova a sinistra di '=' e il valore si trova sul lato destro. Se nel percorso non viene trovato un column_name, viene restituito un errore.

    Ad esempio:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_type":"hive"');
  • Fornire una lista di colonne di partizione senza specificare il tipo

    In questo caso, implicit_partition_type non è impostato e implicit_partition_columns fornisce una lista di colonne.

    Ad esempio:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_columns":["country","year","month"]}');

Per ulteriori informazioni, vedere CREATE_EXTERNAL_TABLE Procedure.

Esegui query su dati partizionati impliciti esterni con organizzazione file di origine formato Hive

Utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE per creare una tabella esterna partizionata implicita dai dati nell'area di memorizzazione degli oggetti generati dai dati Hive.

I file di origine di esempio nell'esempio seguente utilizzano il seguente formato di denominazione:

OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet

Considerare i seguenti file di origine di esempio:

OBJBUCKET/sales/country=USA/year=2024/month=01/sales-2024-01.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-02.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-03.parquet

Per creare una tabella esterna partizionata implicita con i dati memorizzati in questo formato Hive di esempio, effettuare le operazioni riportate di seguito.

  1. Memorizzare le credenziali dell'area di memorizzazione degli oggetti utilizzando la procedura DBMS_CLOUD.CREATE_CREDENTIAL.

    Ad esempio:

    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/

    La creazione di una credenziale per accedere all'area di memorizzazione degli oggetti di Oracle Cloud Infrastructure non è necessaria se si abilitano le credenziali del principal risorsa. Per ulteriori informazioni, consulta la sezione Usa principal risorsa per accedere alle risorse di Oracle Cloud Infrastructure.

    Questa operazione memorizza le credenziali nel database in un formato cifrato. È possibile utilizzare qualsiasi nome per il nome della credenziale. Tenere presente che questo passo è obbligatorio una sola volta a meno che le credenziali dell'area di memorizzazione degli oggetti non vengano modificate. Una volta memorizzate le credenziali, è possibile utilizzare lo stesso nome credenziale per creare tabelle esterne.

    Per informazioni sui parametri username e password per i diversi servizi di storage degli oggetti, vedere CREATE_CREDENTIAL Procedure.

  2. Creare una tabella esterna partizionata implicita sopra i file di origine utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    1. Caso 1: abilita il partizionamento implicito.
      In questo esempio, poiché l'opzione di formato implicit_partition_columns non viene fornita, le colonne della partizione vengono rilevate automaticamente e il partizionamento implicito viene abilitato impostando l'opzione di formato implicit_partition_type su hive.

      Tuttavia, se viene fornita una lista di colonne, tali colonne vengono utilizzate come colonne di partizione e Autonomous Database non tenta di trovare le colonne.

      Per rilevare le colonne di partizione, Autonomous Database avvia la ricerca dall'inizio del percorso, specificato da file_uri_list, per '='. Se trovata, la parte sinistra di '=' fino all'ultimo '/' viene presa come colonna (ad esempio "paese") e la parte destra di '=', fino al primo '/' come valore (ad esempio "USA"). La ricerca continua per '=' fino a dopo '/' che segue il valore della prima partizione, rilevando il secondo '=' nel percorso e così via. 

      BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_type":"hive"');
END;
/

      Di seguito sono riportati i parametri disponibili.

      • table_name: è il nome della tabella esterna.

      • credential_name: indica il nome della credenziale creata nel passo precedente.

      • file_uri_list: è una lista delimitata da virgole di URI di file di origine. Per questo elenco sono disponibili due opzioni:

        • Specificare una lista delimitata da virgole di singoli URI di file senza caratteri jolly.

        • Specificare un singolo URI file con caratteri jolly, in cui i caratteri jolly possono essere solo successivi all'ultima barra "/". Il carattere "*" può essere utilizzato come caratteri jolly per più caratteri, il carattere "?" può essere utilizzato come caratteri jolly per un singolo carattere.

        In questo esempio, namespace-string è lo spazio di nomi dello storage degli oggetti Oracle Cloud Infrastructure e bucketname è il nome del bucket. Per ulteriori informazioni, vedere Informazioni sugli spazi di nomi dello storage degli oggetti.

      • column_list: è una lista delimitata da virgole di nomi di colonna e tipi di dati per la tabella esterna. L'elenco include le colonne all'interno del file di dati e quelle derivate dal nome dell'oggetto (dai nomi nel percorso del file specificato da file_uri_list).

        column_list non è richiesto quando i file di dati sono file strutturati (Parquet, Avro o ORC).

      • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine. L'opzione implicit_partition_type specifica il tipo di formato dei dati come hive.

        Se i dati nel file di origine sono cifrati, decifrare i dati specificando l'opzione di formato encryption. Per ulteriori informazioni sulla decifrazione dei dati, vedere Decifrare i dati durante l'importazione dallo storage degli oggetti.

        Per ulteriori informazioni, vedere DBMS_CLOUD Package Format Options.

    1. Caso 2: partizionamento implicito ottimizzato
      In questo esempio, il partizionamento implicito viene abilitato specificando l'opzione del formato implicit_partition_config e il partizionamento implicito ottimizzato viene abilitato impostando strict_column_order su true.

      Poiché partition_columns specifica una lista di colonne partizionate, tali colonne vengono utilizzate come colonne di partizione e Autonomous Database non tenta di trovare le colonne. 

      BEGIN
dbms_cloud.create_external_table (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["country","year","month","day"]
                          }
                          }');
END;
/

      I parametri di formato sono:

      • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine. L'opzione implicit_partition_config dispone delle seguenti opzioni secondarie:
        • partition_type

          L'unico valore accettato è "hive". L'impostazione predefinita è un percorso di nome file non Beehive. Se partition_type non viene specificato, viene restituito un errore del client.

        • partition_columns

          Lista di colonne partizionate.

        • strict_column_order

          Impostare su true per abilitare il partizionamento implicito ottimizzato. Impostare su true solo per i casi in cui si è certi che il percorso dell'area di memorizzazione degli oggetti non cambierà e non sono presenti prefissi mancanti. Impostare su false o omettere per disabilitare.

        Se i dati nel file di origine sono cifrati, decifrare i dati specificando l'opzione di formato encryption. Per ulteriori informazioni sulla decifrazione dei dati, vedere Decifrare i dati durante l'importazione dallo storage degli oggetti.

        Per ulteriori informazioni, vedere DBMS_CLOUD Package Format Options.

    Per informazioni dettagliate sui parametri, vedere CREATE_EXTERNAL_TABLE Procedure. Per ulteriori informazioni sui servizi di storage degli oggetti cloud supportati, vedere DBMS_CLOUD Formati URI.

  3. È ora possibile eseguire query sulla tabella partizionata esterna creata nel passo precedente.

    Autonomous Database sfrutta le informazioni di partizionamento della tabella partizionata esterna, assicurando che la query acceda solo ai file di dati pertinenti nell'area di memorizzazione degli oggetti.

    Ad esempio:

    SELECT product, units FROM mysales WHERE year='2024' and month='02'

    Questa istruzione SQL esegue query solo sui dati nella partizione per il mese 02 dell'anno 2024.

  • Ottimizza pianificazione query area di memorizzazione oggetti con l'opzione strict_column_order
    Quando una query si rivolge a una cartella dell'area di memorizzazione degli oggetti con un numero molto elevato di file e sottocartelle, la fase di pianificazione ed elenco può diventare il costo principale prima che i dati vengano effettivamente scansionati. Questo è comune con i layout delle cartelle in stile Hive in cui i valori delle partizioni sono incorporati nel percorso.

Argomento padre: Query di tabelle esterne con partizionamento implicito

Ottimizza la pianificazione delle query dell'area di memorizzazione degli oggetti con l'opzione strict_column_order

Quando una query si rivolge a una cartella object-store con un numero molto elevato di file e sottocartelle, la fase di pianificazione e quotazione può diventare il costo principale prima che i dati vengano effettivamente scansionati. Questo è comune con i layout delle cartelle in stile Hive in cui i valori delle partizioni sono incorporati nel percorso.

Nota

  • L'impostazione strict_column_order è disabilitata per impostazione predefinita. Abilita solo se il layout del percorso è coerente.

  • Se le convenzioni delle cartelle cambiano, ad esempio aggiungendo, rimuovendo o riordinando le colonne delle partizioni, è necessario aggiornare l'opzione partition_columns e potrebbe essere necessario disabilitare questa opzione.

Abilitare l'opzione strict_column_order come indicato di seguito.

Se i dati utilizzano percorsi partizionati in stile Hive e le colonne di partizione vengono sempre visualizzate in un ordine fisso e coerente senza segmenti mancanti, abilitare l'ottimizzazione in base alla seguente impostazione: 

strict_column_order = true  (with partition_type = "hive")

Il database può saltare le directory non necessarie seguendo l'ordine delle partizioni già definite. Ciò significa che non è necessario elencare ogni singolo oggetto, il che può accelerare notevolmente i tempi di pianificazione per set di dati di grandi dimensioni.

Note per l'utilizzo dell'opzione strict_column_order:

  • I percorsi seguono la denominazione e l'ordine Hive, ad esempio:

    .../country=US/year=2025/month=09/...

  • Le colonne di partizione mantengono un set e una sequenza fissi, senza riordini o prefissi saltati.

  • È necessario ottimizzare l'elenco plan-time per le cartelle contenenti un numero molto elevato di oggetti.
  • Impossibile utilizzare questa opzione nei set di dati in cui non sono presenti tutti i prefissi. Ad esempio, alcune cartelle includono year=, mentre altre iniziano con month=).

Esempi: utilizzare l'opzione strict_column_order per le partizioni Hive-Style con DBMS_CLOUD.CREATE_EXTERNAL_TABLE

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name        => 'sales_xt',
    credential_name   => 'MY_CREDENTIAL',
    file_uri_list     => 'https://objectstorage.example.com/n/tenant/b/lake/o/sales/',
    -- Data is stored as: .../country=US/year=2025/month=09/*.parquet
    column_list       => 'product       VARCHAR2(100),
                          units         NUMBER,
                          amount        NUMBER,
                          country       VARCHAR2(30),
                          year          NUMBER,
                          month         NUMBER',
    format            => '{
      "type": "parquet",
      "implicit_partition_config": {
        "partition_type": "hive",
        "partition_columns": ["country","year","month"],
        "strict_column_order": true
      }
    }'
  );
END;
/

Verificare che la logica di pianificazione evita di elencare percorsi di livello superiore non correlati (ad esempio percorsi appartenenti ad altri paesi o anni):

  • Eseguire una query selettiva che vincola le partizioni iniziali, ad esempio:
    SELECT COUNT(*) FROM sales_xt WHERE country = ''US'' AND year = 2025;
    con
    strict_column_order = true
    .
  • Se le ipotesi di layout non vengono soddisfatte, disabilitare l'opzione e riprovare.

Interroga dati stile non Hive partizionati impliciti esterni

Utilizzare la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE per creare una tabella esterna partizionata implicita dai dati nello storage degli oggetti generati da dati non Hive.

I file di origine di esempio nell'esempio seguente utilizzano il seguente formato di denominazione:

OBJBUCKET/<table>/<value1>/<value2>/file.parquet

Considerare i seguenti file di origine di esempio:

OBJBUCKET/sales/USA/2024/01/sales-2024-01.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-02.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-03.parquet

Per creare una tabella esterna partizionata implicita con i dati memorizzati in questo formato Hive di esempio, effettuare le operazioni riportate di seguito.

  1. Memorizzare le credenziali dell'area di memorizzazione degli oggetti utilizzando la procedura DBMS_CLOUD.CREATE_CREDENTIAL.

    Ad esempio:

    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/

    La creazione di una credenziale per accedere all'area di memorizzazione degli oggetti di Oracle Cloud Infrastructure non è necessaria se si abilitano le credenziali del principal risorsa. Per ulteriori informazioni, consulta la sezione Usa principal risorsa per accedere alle risorse di Oracle Cloud Infrastructure.

    Questa operazione memorizza le credenziali nel database in un formato cifrato. È possibile utilizzare qualsiasi nome per il nome della credenziale. Tenere presente che questo passo è obbligatorio una sola volta a meno che le credenziali dell'area di memorizzazione degli oggetti non vengano modificate. Una volta memorizzate le credenziali, è possibile utilizzare lo stesso nome credenziale per creare tabelle esterne.

    Per informazioni sui parametri username e password per i diversi servizi di storage degli oggetti, vedere CREATE_CREDENTIAL Procedure.

  2. Creare una tabella esterna partizionata implicita sopra i file di origine utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    In questo esempio, il partizionamento implicito viene abilitato fornendo alle colonne di partizione l'opzione di formato implicit_partition_columns. Poiché il nome della cartella non include le colonne della partizione, i valori della partizione nel percorso, specificati da file_uri_list, possono essere trovati solo se viene fornita una lista esplicita di colonne con l'opzione di formato implicit_partition_columns. Per rilevare i rispettivi valori di colonna nel percorso, l'ordine delle colonne deve essere uguale all'ordine dei valori.

    Le colonne della partizione vengono rilevate automaticamente. 
    BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_columns":["country","year","month"]}');
END;
/

    Di seguito sono riportati i parametri disponibili.

    • table_name: è il nome della tabella esterna.

    • credential_name: indica il nome della credenziale creata nel passo precedente.

    • file_uri_list: è una lista delimitata da virgole di URI di file di origine. Per questo elenco sono disponibili due opzioni:

      • Specificare una lista delimitata da virgole di singoli URI di file senza caratteri jolly.

      • Specificare un singolo URI file con caratteri jolly, in cui i caratteri jolly possono essere solo successivi all'ultima barra "/". Il carattere "*" può essere utilizzato come caratteri jolly per più caratteri, il carattere "?" può essere utilizzato come caratteri jolly per un singolo carattere.

    • column_list: è una lista delimitata da virgole di nomi di colonna e tipi di dati per la tabella esterna. L'elenco include le colonne all'interno del file di dati e quelle derivate dal nome dell'oggetto (dai nomi nel percorso del file specificato da file_uri_list).

      column_list non è richiesto quando i file di dati sono file strutturati (Parquet, Avro o ORC).

    • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine. L'opzione implicit_partition_type non è impostata. Poiché viene specificato implicit_partition_columns, il tipo viene rilevato automaticamente come non Beehive.

      Se i dati nel file di origine sono cifrati, decifrare i dati specificando l'opzione di formato encryption. Per ulteriori informazioni sulla decifrazione dei dati, vedere Decifrare i dati durante l'importazione dallo storage degli oggetti.

      Per ulteriori informazioni, vedere DBMS_CLOUD Package Format Options.

    In questo esempio, namespace-string è lo spazio di nomi dello storage degli oggetti Oracle Cloud Infrastructure e bucketname è il nome del bucket. Per ulteriori informazioni, vedere Informazioni sugli spazi di nomi dello storage degli oggetti.

    Per informazioni dettagliate sui parametri, vedere CREATE_EXTERNAL_TABLE Procedure.

    Per ulteriori informazioni sui servizi di storage degli oggetti cloud supportati, vedere DBMS_CLOUD Formati URI.

  3. È ora possibile eseguire query sulla tabella partizionata esterna creata nel passo precedente.

    Autonomous Database sfrutta le informazioni di partizionamento della tabella partizionata esterna, assicurando che la query acceda solo ai file di dati pertinenti nell'area di memorizzazione degli oggetti.

    Ad esempio:

    SELECT product, units FROM mysales WHERE year='2024'

    Questa istruzione SQL esegue query solo sui dati nella partizione per l'anno 2024.