Esegui query sui dati esterni con Autonomous Database sull'infrastruttura Exadata dedicata

Descrive package e strumenti per eseguire query e convalidare i dati con Autonomous Database sull'infrastruttura Exadata dedicata.

I dati esterni non sono gestiti dal database; tuttavia, è possibile utilizzare le procedure DBMS_CLOUD per eseguire query sui dati esterni. Sebbene le query su dati esterni non saranno così veloci come le query sulle tabelle di database, è possibile utilizzare questo approccio per avviare rapidamente l'esecuzione di query sui file di origine esterni e sui dati esterni.

È possibile utilizzare le procedure DBMS_CLOUD per convalidare i dati nei file di origine esterni per una tabella esterna in modo da poter identificare i problemi e correggere i dati nella tabella esterna oppure escludere i dati non validi prima di utilizzare i dati.

Nota

Se non si utilizza l'utente ADMIN, assicurarsi che l'utente disponga dei privilegi necessari per le operazioni che l'utente deve eseguire. Per ulteriori informazioni, vedere Gestisci privilegi utente database.

Esegui query su dati esterni

Per eseguire query sui dati nei file nel cloud, è necessario prima memorizzare le credenziali di storage degli oggetti in Autonomous Database, quindi creare una tabella esterna utilizzando la procedura PL/SQL DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Il file di origine in questo esempio, channels.txt, contiene i seguenti dati: 

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others
  1. Memorizzare le credenziali dello storage degli oggetti cloud utilizzando la procedura DBMS_CREDENTIAL.CREATE_CREDENTIAL. Ad esempio:
    BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@oracle.com',
    password => 'password'
  );
END;
/

    I valori forniti per username e password dipendono dal servizio di storage degli oggetti cloud che si sta utilizzando:

    • Oracle Cloud Infrastructure Object Storage: username è il nome utente di Oracle Cloud Infrastructure e password è il token di autenticazione di Oracle Cloud Infrastructure. Vedere Utilizzo dei token di autenticazione.

    • Oracle Cloud Infrastructure Object Storage Classic: username è il tuo nome utente Oracle Cloud Infrastructure Classic e password è la tua password Oracle Cloud Infrastructure Classic.

    Questa operazione memorizza le credenziali nel database in formato cifrato. È possibile utilizzare qualsiasi nome per il nome della credenziale. Tenere presente che questo passo è necessario una sola volta a meno che le credenziali dell'area di memorizzazione degli oggetti non vengano modificate. Dopo aver memorizzato le credenziali, è possibile utilizzare lo stesso nome di credenziale per tutti i caricamenti dati.

  2. Creare una tabella esterna sopra i file di origine utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

    La procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE supporta i file esterni nei servizi di storage degli oggetti cloud supportati. La credenziale è una proprietà a livello di tabella, pertanto i file esterni devono trovarsi nella stessa area di memorizzazione degli oggetti.

    Ad esempio:

    BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'CHANNELS_EXT',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/idthydc0kinr/mybucket/channels.txt',
    format => json_object('delimiter' value ','),
    column_list => 'CHANNEL_ID NUMBER, 
	CHANNEL_DESC VARCHAR2(20), 
	CHANNEL_CLASS VARCHAR2(20)'
 );
END;
/

    Di seguito sono riportati i parametri disponibili.

    • table_name: è il nome della tabella esterna.

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

    • file_uri_list: è una lista delimitata da virgole dei file di origine che si desidera interrogare.

      In questo esempio, file_uri_list è un URI Swift di Oracle Cloud Infrastructure che specifica il file channels.txt nel bucket mybucket nell'area us-phoenix-1. (idthydc0kinr è lo spazio di nomi dello storage degli oggetti in cui risiede il bucket). Per informazioni sui formati URI supportati, vedere Formati URI di storage degli oggetti cloud.

    • format: definisce le opzioni che è possibile specificare per descrivere il formato del file di origine. Per informazioni sulle opzioni di formato che è possibile specificare, vedere Parametro formato.

    • column_list: è una lista delimitata da virgole delle definizioni di colonna nei file di origine.

    È ora possibile eseguire query sulla tabella esterna creata nel passo precedente. Ad esempio:

    SELECT count(*) FROM channels_ext;

    Per impostazione predefinita, il database prevede che tutte le righe del file di dati esterno siano valide e corrispondano sia alle definizioni del tipo di dati di destinazione che alla definizione del formato dei file. Se nei file di origine sono presenti righe che non corrispondono alle opzioni di formato specificate, la query segnala un errore. È possibile utilizzare le opzioni dei parametri format, ad esempio rejectlimit, per eliminare questi errori. In alternativa, è inoltre possibile convalidare la tabella esterna creata per visualizzare i messaggi di errore e le righe rifiutate in modo da poter modificare le opzioni di formato di conseguenza. Per ulteriori informazioni, vedere Convalida dati esterni.

    Per informazioni dettagliate sui parametri, vedere CREATE_EXTERNAL_TABLE Procedura.

Convalida dati esterni

Per convalidare una tabella esterna, utilizzare la procedura DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Prima di convalidare una tabella esterna, è necessario creare la tabella esterna utilizzando la procedura DBMS_CLOUD.CREATE_EXTERNAL_TABLE. Utilizzare quindi la procedura DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE per convalidarla. Ad esempio: 

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS_EXT' );
END;
/

Questa procedura analizza i file di origine e li convalida utilizzando le opzioni di formato specificate al momento della creazione della tabella esterna.

Per impostazione predefinita, l'operazione di convalida esegue la scansione di tutte le righe nei file di origine e si interrompe quando una riga viene rifiutata. Se si desidera convalidare solo un subset delle righe, utilizzare il parametro rowcount. Quando il parametro rowcount è impostato, l'operazione di convalida esegue la scansione delle righe e si interrompe quando una riga viene rifiutata o quando il numero di righe specificato viene convalidato senza errori.

Ad esempio, l'operazione di convalida seguente esegue la scansione di 100 righe e si interrompe quando una riga viene rifiutata o quando vengono convalidate 100 righe senza errori:

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100 ); 
END; 
/

Se non si desidera interrompere la convalida quando una riga viene rifiutata e si desidera visualizzare tutte le righe rifiutate, impostare il parametro stop_on_error su FALSE. In questo caso VALIDATE_EXTERNAL_TABLE analizza tutte le righe e segnala tutte le righe rifiutate.

Se si desidera convalidare solo un subset di righe, utilizzare il parametro rowcount. Quando rowcount è impostato e stop_on_error è impostato su FALSE, l'operazione di convalida esegue la scansione delle righe e si interrompe quando il numero di righe specificato viene rifiutato o quando il numero di righe specificato viene convalidato senza errori. Ad esempio, l'esempio seguente esegue la scansione di 100 righe e si interrompe quando vengono rifiutate 100 righe o quando vengono convalidate 100 righe senza errori: 

BEGIN 
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
     table_name => 'CHANNELS_EXT',
     rowcount => 100, 
     stop_on_error => FALSE );
END; 
/

Per informazioni dettagliate sui parametri di VALIDATE_EXTERNAL_TABLE, vedere DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Per visualizzare i risultati delle operazioni di convalida nelle tabelle dba_load_operations e user_load_operations, vedere Visualizza log per convalida dati.

Visualizza log per convalida dati

Dopo aver convalidato una tabella esterna, è possibile visualizzare il risultato dell'operazione di convalida eseguendo una query su una tabella delle operazioni di caricamento.

  • dba_load_operations: mostra tutte le operazioni di convalida.

  • user_load_operations: mostra le operazioni di convalida nello schema.

È possibile utilizzare queste tabelle per visualizzare le informazioni di convalida del caricamento. Ad esempio, utilizzare questa istruzione SELECT per eseguire una query su user_load_operations: 

SELECT table_name,owner_name,type,status,start_time,update_time,logfile_table,badfile_table
FROM user_load_operations
WHERE type = 'VALIDATE';


TABLE_NAME    OWNER_NAME  TYPE       STATUS     START_TIME     UPDATE_TIME    LOGFILE_TABLE    BADFILE_TABLE
------------- ----------  ---------- ---------- -------------- -------------  ---------------  ---------------
CHANNELS_EXT  SH          VALIDATE   COMPLETED  13-NOV-17...   13-NOV-17...   VALIDATE$21_LOG  VALIDATE$21_BAD

Utilizzando questa istruzione SQL con la clausola WHERE nella colonna TYPE vengono visualizzate tutte le operazioni di caricamento di tipo VALIDATE.

La colonna LOGFILE_TABLE mostra il nome della tabella su cui è possibile eseguire una query per esaminare il log di un'operazione di convalida. Ad esempio, la query seguente mostra il log per questa operazione di convalida: 

SELECT * FROM VALIDATE$21_LOG;

La colonna BADFILE_TABLE mostra il nome della tabella su cui è possibile eseguire una query per esaminare le righe in cui si sono verificati errori durante la convalida. Ad esempio, la seguente query mostra i record rifiutati per l'operazione di convalida precedente: 

SELECT * FROM VALIDATE$21_BAD;

A seconda degli errori visualizzati nel log e delle righe mostrate nel file BADFILE_TABLE, è possibile correggere l'errore eliminando la tabella esterna utilizzando il comando DROP TABLE e ricreandola specificando le opzioni di formato corrette in DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Nota

Le tabelle LOGFILE_TABLE e BADFILE_TABLE vengono memorizzate per due giorni per ogni operazione di convalida e quindi rimosse automaticamente.