Esegui query su dati esterni con file di origine ORC, Parquet o Avro

Quando accedi AI dati ORC, Parquet o Avro memorizzati nell'area di memorizzazione degli oggetti utilizzando tabelle esterne in Autonomous AI Database, puoi impostare in modo automatico o esplicito la dimensione massima delle colonne di testo.

1. Memorizzare le credenziali dell'area di memorizzazione degli oggetti per accedere all'area di memorizzazione degli oggetti, utilizzando la procedura DBMS_CLOUD.CREATE_CREDENTIAL:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DEF_CRED_NAME',
       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 per ORC, Parquet o Avro sopra i file di origine utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE e specificare il parametro di formato maxvarchar.

   La procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE supporta file esterni nei servizi di storage degli oggetti cloud supportati, tra cui: Oracle Cloud Infrastructure Object Storage, Azure Blob Storage o Azure Data Lake Storage, Amazon S3 e Amazon S3-Compatible, tra cui: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage e Wasabi Hot Cloud Storage. La credenziale è una proprietà a livello di tabella, pertanto i file esterni devono trovarsi nella stessa area di memorizzazione degli oggetti.

   Per impostazione predefinita, le colonne create nella tabella esterna mappano automaticamente i tipi di dati ai tipi di dati Oracle per i campi trovati nei file di origine e i nomi delle colonne della tabella esterna corrispondono ai nomi dei campi di origine.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"extended"}'
    );
   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. Il parametro credential_name deve essere conforme alle convenzioni di denominazione degli oggetti Oracle. Per ulteriori informazioni, vedere Regole di denominazione degli oggetti database.

   • file_uri_list: è una lista delimitata da virgole dei file di origine che si desidera interrogare. Il formato URI per gli endpoint dedicati è supportato nei realm commerciali (OC1). Per ulteriori informazioni, vedere Endpoint dedicati dello storage degli oggetti e Formati URI DBMS_CLOUD.

   • format: definisce le opzioni per descrivere il formato del file di origine. Per un file Parquet, utilizzare il parametro format per specificare type parquet. Per un file Avro utilizzare il parametro format per specificare type avro. Per un file ORC utilizzare il parametro format per specificare type orc.

     L'opzione maxvarchar con valore extended specifica che le colonne di testo vengono create come varchar(32767) in un'istanza di Autonomous AI Database con dimensione stringa estesa. I valori possibili sono standard con varchar(4000), extended con varchar(32767) e auto. Il valore predefinito maxvarchar è auto. Con questo valore, la dimensione massima del testo si basa sul valore di MAX_STRING_SIZE.

   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 impostazione predefinita, il parametro format schema è impostato e le colonne e i tipi di dati vengono derivati automaticamente e i campi nell'origine corrispondono alle colonne della tabella esterna per nome. I tipi di dati di origine vengono convertiti nei tipi di dati Oracle della colonna della tabella esterna in base al mapping DBMS_CLOUD per i tipi di dati ORC, Parquet o Avro. I valori validi per il parametro schema sono:

   • first: analizza lo schema del primo file ORC, Parquet o Avro trovato da DBMS_CLOUD nel file file_uri_list specificato (first è il valore predefinito per schema).

   • all: analizza tutti gli schemi per tutti i file ORC, Parquet o Avro trovati in file_uri_list. Poiché si tratta semplicemente di file acquisiti in un'area di memorizzazione degli oggetti, non vi è alcuna garanzia che i metadati di ciascun file siano uguali. Ad esempio, File1 può contenere un campo denominato "indirizzo", mentre File2 potrebbe mancare tale campo. L'esame di ciascun file per derivare le colonne è un po' più costoso, ma potrebbe essere necessario se il primo file non contiene tutti i campi obbligatori.

   Nota
   
   Se si specifica il parametro column_list, fornire i nomi delle colonne e i tipi di dati per la tabella esterna e il valore schema, se specificato viene ignorato. Utilizzando column_list è possibile limitare le colonne nella tabella esterna. Se column_list non viene specificato, il valore predefinito di schema è first.
3. È ora possibile eseguire query sulla tabella esterna creata nel passo precedente:

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(32767) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(32767) 
   CITY                 VARCHAR2(32767) 
   STATE_PROVINCE       VARCHAR2(32767) 
   INCOME_LEVEL         VARCHAR2(32767)
   

   SELECT prod_id, quantity_sold, gender, city, income_level 
        FROM sales_extended_ext 
        WHERE ROWNUM < 8;
   
        PROD_ID     QUANTITY_SOLD     GENDER    CITY              INCOME_LEVEL
   1    13          1                 M         Adelaide          K: 250,000 – 299,999
   2    13          1                 M         Dolores           L: 300,000 and above
   3    13          1                 M         Cayuga            F: 110,000 – 129,999
   4    13          1                 F         Bergen op Zoom    C: 50,000 – 69,999
   5    13          1                 F         Neuss             J: 190,000 – 249,999
   6    13          1                 F         Darwin            F: 110,000 – 129,999
   7    13          1                 M         Sabadell          K:250,000 – 299,999
   

   Questa query mostra i valori per le righe nella tabella esterna. Se si desidera eseguire una query frequente su questi dati, dopo aver esaminato i dati è possibile caricarli in una tabella con DBMS_CLOUD.COPY_DATA.

   Se si specifica l'opzione format maxvarchar come standard, le colonne di testo varchar2() vengono create con la dimensione 4000. Ad esempio:

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"standard"}'
    );
   END;
   /     
   

   Il parametro format con l'opzione maxvarchar impostata sul valore standard specifica che le colonne di testo vengono create come varchar(4000).

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(4000) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(4000) 
   CITY                 VARCHAR2(4000) 
   STATE_PROVINCE       VARCHAR2(4000) 
   INCOME_LEVEL         VARCHAR2(4000)