Consultar Dados Externos com o Autonomous AI Database na Infraestrutura Dedicada do Exadata

Descreve pacotes e ferramentas para consultar e validar dados com o Autonomous AI Database na Infraestrutura Dedicada do Exadata.

Os dados externos não são gerenciados pelo banco de dados; no entanto, você pode usar procedimentos DBMS_CLOUD para consultar seus dados externos. Embora as consultas em dados externos não sejam tão rápidas quanto as consultas em tabelas de banco de dados, você pode usar essa abordagem para começar rapidamente a executar consultas em seus arquivos de origem externa e dados externos.

Você pode usar os procedimentos DBMS_CLOUD para validar os dados nos arquivos de origem externa de uma tabela externa para que possa identificar problemas e corrigir os dados na tabela externa ou excluir dados inválidos antes de usar os dados.

Observação: Se você não estiver usando um usuário ADMIN, certifique-se de que o usuário tenha os privilégios necessários para as operações que o usuário precisa executar. Consulte Gerenciar Privilégios de Usuário do Banco de Dados para obter mais informações.

Consultar dados externos

Para consultar dados em arquivos na Nuvem, primeiro você precisa armazenar suas credenciais de armazenamento de objeto em seu Autonomous AI Database e, em seguida, criar uma tabela externa usando o procedimento PL/SQL DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

O arquivo de origem neste exemplo, channels.txt, tem os seguintes dados:

S,Direct Sales,Direct
T,Tele Sales,Direct
C,Catalog,Indirect
I,Internet,Indirect
P,Partners,Others

Armazene sua credencial do Cloud Object Storage usando o procedimento DBMS_CREDENTIAL.CREATE_CREDENTIAL. Por exemplo:
```
 BEGIN
   DBMS_CREDENTIAL.CREATE_CREDENTIAL(
     credential_name => 'DEF_CRED_NAME',
     username => 'adb_user@oracle.com',
     password => 'password'
   );
 END;
 /
```
Os valores fornecidos para username e password dependem do serviço de Cloud Object Storage que você está usando:
- Oracle Cloud Infrastructure Object Storage: username é o nome do usuário da Oracle Cloud Infrastructure e password é o seu token de autenticação da Oracle Cloud Infrastructure. Consulte Como Trabalhar com Tokens de Autenticação.
- Oracle Cloud Infrastructure Object Storage Classic: username é o nome do usuário do seu Oracle Cloud Infrastructure Classic e password é a senha do seus Oracle Cloud Infrastructure Classic.
Esta operação armazena as credenciais no banco de dados em um formato criptografado. Você pode usar qualquer nome para o nome da credencial. Observe que essa etapa só é necessária uma vez, a menos que suas credenciais do armazenamento de objetos sejam alteradas. Depois de armazenar as credenciais, você poderá usar o mesmo nome de credencial para todos os carregamentos de dados.
Crie uma tabela externa na parte superior dos arquivos de origem usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

O procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE suporta arquivos externos em serviços suportados de armazenamento de objetos na nuvem. A credencial é uma propriedade em nível de tabela; portanto, os arquivos externos devem estar no mesmo armazenamento de objetos.

Por exemplo:
```
 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;
 /
```
Os parâmetros são:
- table_name: é o nome da tabela externa.
- credential_name: é o nome da credencial criada na etapa anterior.
- file_uri_list: é uma lista delimitada por vírgulas dos arquivos de origem que você deseja consultar.
  
  Neste exemplo, file_uri_list é um URI Swift do Oracle Cloud Infrastructure que especifica o arquivo channels.txt no bucket mybucket na região us-phoenix-1. (idthydc0kinr é o namespace dp armazenamento de objetos no qual o bucket reside.) Para obter informações sobre os formatos de URI suportados, consulte Formatos de URI do Cloud Object Storage.
- format: define as opções que você pode especificar para descrever o formato do arquivo de origem. Para obter informações sobre as opções de formato que você pode especificar, consulte Parâmetro de Formato.
- column_list: é uma lista delimitada por vírgulas das definições de coluna nos arquivos de origem.
Agora você pode executar consultas na tabela externa criada na etapa anterior. Por exemplo:
```
 SELECT count(*) FROM channels_ext;
```
Por padrão, o banco de dados espera que todas as linhas no arquivo de dados externo sejam válidas e correspondam às definições do tipo de dados de destino, bem como à definição de formato do(s) arquivo(s). Se houver linhas nos arquivos de origem que não correspondam às opções de formato especificadas, a consulta reportará um erro. Você pode usar opções de parâmetro format, como rejectlimit, para suprimir esses erros. Como alternativa, você também pode validar a tabela externa criada para ver as mensagens de erro e as linhas rejeitadas para que possa alterar suas opções de formato adequadamente. Consulte Validar Dados Externos para obter mais informações.

Para obter informações detalhadas sobre os parâmetros, consulte CREATE_EXTERNAL_TABLE Procedure.

Colunas de Metadados da Tabela Externa

Os metadados da tabela externa ajudam a determinar de onde vêm os dados quando você executa uma consulta.

As tabelas externas que você cria com DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE ou DBMS_CLOUD.CREATE_HYBRID_PART_TABLE incluem duas colunas invisíveis file$path e file$name. Essas colunas ajudam a identificar de qual arquivo um registro está vindo.

file$path: Especifica o texto do caminho do arquivo até o início do nome do objeto.
file$name: Especifica o nome do objeto, incluindo todo o texto que se segue ao "/" final.

Por exemplo:

SELECT genre_id, name, file$name, file$path FROM ext_genre
     WHERE rownum <= 2;

genre_id      name        file$name     file$path
--------      ---------   -----------   ----------------------
1             Action      genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
2             Adventure   genre.csv     https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre

Consulte Colunas Invisíveis para obter mais informações sobre colunas invisíveis.

Validar Dados Externos

Para validar uma tabela externa, use o procedimento DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Antes de validar uma tabela externa, você precisa criar a tabela externa usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE. Em seguida, use o procedimento DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE para validá-la. Por exemplo:

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

Este procedimento verifica os arquivos de origem e os valida usando as opções de formato especificadas quando você criou a tabela externa.

A operação de validação, por padrão, verifica todas as linhas nos arquivos de origem e para quando uma linha é rejeitada. Se quiser validar somente um subconjunto de linhas, use o parâmetro rowcount. Quando o parâmetro rowcount é definido, a operação de validação verifica as linhas e é interrompida quando uma linha é rejeitada ou quando o número especificado de linhas é validado sem erros.

Por exemplo, a seguinte operação de validação verifica 100 linhas e para quando uma linha é rejeitada ou quando 100 linhas são validadas sem erros:

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

Se você não quiser que a validação seja interrompida quando uma linha for rejeitada e quiser ver todas as linhas rejeitadas, defina o parâmetro stop_on_error como FALSE. Nesse caso, VALIDATE_EXTERNAL_TABLE verifica todas as linhas e relata todas as linhas rejeitadas.

Se quiser validar somente um subconjunto de linhas, use o parâmetro rowcount. Quando rowcount é definido e stop_on_error é definido como FALSE, a operação de validação verifica as linhas e é interrompida quando o número especificado de linhas é rejeitado ou quando o número especificado de linhas é validado sem erros. Por exemplo, o exemplo a seguir verifica 100 linhas e para quando 100 linhas são rejeitadas ou quando 100 linhas são validadas sem erros:

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

Consulte Procedimento VALIDATE_EXTERNAL_TABLE para obter informações detalhadas sobre parâmetros DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.

Consulte Exibir Logs para Validação de Dados para ver os resultados de operações de validade nas tabelas dba_load_operations e user_load_operations.

Exibir Logs para Validação de Dados

Depois de validar uma tabela externa, você poderá ver o resultado da operação de validação consultando uma tabela de operações de carga:

dba_load_operations: mostra todas as operações de validação.
user_load_operations: mostra as operações de validação no seu esquema.

Você pode usar essas tabelas para exibir informações de validação de carga. Por exemplo, use esta instrução SELECT para consultar 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

O uso dessa instrução SQL com a cláusula WHERE na coluna TYPE exibe todas as operações de carregamento com o tipo VALIDATE.

A coluna LOGFILE_TABLE mostra o nome da tabela que você pode consultar para verificar o log de uma operação de validação. Por exemplo, a consulta a seguir mostra o log desta operação de validação:

SELECT * FROM VALIDATE$21_LOG;

A coluna BADFILE_TABLE mostra o nome da tabela que você pode consultar para verificar as linhas nas quais houve erros durante a validação. Por exemplo, a consulta a seguir mostra os registros rejeitados para a operação de validação acima:

SELECT * FROM VALIDATE$21_BAD;

Dependendo dos erros mostrados no log e das linhas mostradas em BADFILE_TABLE, você pode corrigir o erro eliminando a tabela externa usando o comando DROP TABLE e recriando-a especificando as opções de formato corretas em DBMS_CLOUD.CREATE_EXTERNAL_TABLE.

Observação: As tabelas LOGFILE_TABLE e BADFILE_TABLE são armazenadas durante dois dias para cada operação de validar e, em seguida, removidas automaticamente.