Usar Pesquisa JSON em Documentos no Object Storage

Você pode criar um índice de pesquisa JSON em documentos que residem em arquivos armazenados no Object Storage, o que permite pesquisar campos em seus documentos JSON usando JSON_TEXTCONTAINS, por exemplo, pesquisando com curingas.

Usar Pesquisa JSON em Documentos no Object Storage

Você pode criar um índice de pesquisa JSON em documentos em arquivos armazenados no Object Storage. Um índice de pesquisa JSON foi projetado especificamente para documentos JSON e permite que você faça pesquisas regulares e de texto completo em seus documentos ou fragmentos de seus documentos usando JSON_TEXTCONTAINS.

Consulte JSON_TEXTCONTAINS Condição para obter mais informações.

Você usa DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX para criar um índice de pesquisa JSON em arquivos no Object Storage. Você pode configurar uma taxa de atualização que indique a frequência em minutos na qual o índice é atualizado para qualquer novo upload ou exclusão. Consulte Criar um Índice de Pesquisa JSON em Documentos JSON em Arquivos para obter mais informações.

Os seguintes objetos são criados quando você cria um índice de pesquisa JSON:

  • Um índice de pesquisa JSON index_name nos arquivos JSON do Object Storage.

  • Uma tabela local INDEX_NAME$TXTIDX. Esta tabela é a tabela de mapeamento entre seus documentos nos arquivos do Object Storage e o índice de pesquisa JSON que é criado no banco de dados. Consulte Tabela de Referência do Índice de Pesquisa JSON para obter mais informações.

  • Uma view com o nome padrão INDEX_NAME. Você pode utilizar a view para executar uma pesquisa usando JSON_TEXTCONTAINS. Essa view é criada na parte superior da tabela INDEX_NAME$TXTIDX. Consulte View de Referência do Índice de Pesquisa JSON para obter mais informações.

  • Uma tabela de log de erros com o sufixo padrão index_name$txtidx_err. Consulte Tabela de Log de Erros para obter mais informações.

A operação de criação de índice de pesquisa JSON é registrada na view ALL_SCHEDULER_JOB_RUN_DETAILS. Consulte Monitorar Criação de Índice de Texto para obter mais informações.

O Autonomous Database suporta a criação de índice de pesquisa JSON para uma variedade de arquivos de texto que contêm documentos JSON. Por exemplo, você pode indexar arquivos JSON no formato compactado e descompactado ou escolher outro delimitador de documento JSON. Consulte DBMS_CLOUD Package para obter mais informações sobre as opções de tratamento de arquivos.

Consulte Indexando com o Oracle Text para obter mais informações.

Criar um Índice de Pesquisa JSON em Documentos JSON em Arquivos

Use DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX para criar um índice de pesquisa JSON nos arquivos JSON armazenados no Object Storage.

Consulte Indexando com o Oracle Text para obter mais informações sobre palavras de interrupção do Oracle Text.

  1. Crie um objeto de credencial para acessar o local de origem.

    Consulte CREATE_CREDENTIAL Procedimento para obter mais informações.

  2. Execute o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX para criar um índice de pesquisa JSON nos arquivos de armazenamento de objetos.
    
    BEGIN 
     DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
            credential_name => 'OBJ_STORE_CRED',
            location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/vcnlogs/',
            index_name      => 'VNCLOGS',
            format          =>  JSON_OBJECT ('json_index' value 'true', 'json_index_doc_len' value 12000, 'file_compression' value 'gzip')
    );
    END;
    /
    Este exemplo cria o seguinte:
    • Um índice de pesquisa JSON VNCLOGS nos arquivos do serviço Object Storage localizados no URI especificado no parâmetro location_uri.

    • Uma tabela local VNCLOGS$TXTIDX. Consulte Tabela de Referência do Índice de Pesquisa JSON para obter mais informações.

    • Uma view com o nome VNCLOGS. Essa view é criada na parte superior da tabela VNCLOGS$TXTIDX. Você pode utilizar a view para executar uma pesquisa usando JSON_TEXTCONTAINS. Consulte View de Referência do Índice de Pesquisa JSON para obter mais informações.

    • Uma tabela de log de erros com o nome VNC$TXTIDX_ERR. A tabela contém uma lista de documentos JSON inválidos em seus arquivos. Consulte Tabela de Log de Erros para obter mais informações.

    Depois de criar o índice de pesquisa JSON, você pode consultar a view VNCLOGS. Por exemplo:

    SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS RECORD 
     FROM vnclogs WHERE JSON_TEXTCONTAINS(file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}');
    

    Essa consulta retorna os detalhes do log dos endereços IP especificados na condição JSON_TEXTCONTAINS. Observe que os critérios de índice de Pesquisa JSON são especificados na coluna file_line_json, na coluna indexada na tabela de índice de pesquisa JSON no banco de dados, enquanto os documentos JSON são transmitidos diretamente do Object Storage, representados por meio da coluna data. A coluna data contém o conteúdo do arquivo do Object Storage.

    Consulte CREATE_EXTERNAL_TEXT_INDEX Procedimento para obter mais informações.

Eliminar um Índice de Pesquisa JSON

Use o procedimento DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX para eliminar um índice de pesquisa JSON nos arquivos JSON armazenados no Object Storage.

Por exemplo:

BEGIN 
 DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
  index_name => 'VCNLOGS',
);
END;
/

Este exemplo elimina o índice de pesquisa JSON VCNLOGS.

Consulte DROP_EXTERNAL_TEXT_INDEX Procedimento para obter mais informações.

Monitorar a Criação do Índice de Pesquisa JSON

Quando você executa DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX, a operação de criação do índice de pesquisa JSON é registrada na view ALL_SCHEDULER_JOB_RUN_DETAILS.

Você pode consultar a view ALL_SCHEDULER_JOB_RUN_DETAILS para obter o status e qualquer erro reportado pelo job de criação de índice.

O nome do job DBMS_SCHEDULER é derivado do parâmetro INDEX_NAME especificado quando você chama DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX.

Para consultar a view ALL_SCHEDULER_JOB_RUN_DETAILS, você deve estar conectado como o usuário ADMIN ou ter o privilégio READ na view ALL_SCHEDULER_JOB_RUN_DETAILS.

Por exemplo, a seguinte instrução SELECT com uma cláusula WHERE em job_name mostra os detalhes da execução do job:

SELECT status, additional_info 
   FROM all_scheduler_job_run_details WHERE LOWER(job_name) = LOWER('index_name$JOB');

Você também pode consultar a existência de um job do scheduler de criação de índice.

Por exemplo:
SELECT status 
    FROM all_scheduler_jobs where LOWER(job_name) = LOWER('index_name$JOB');

Consulte CREATE_EXTERNAL_TEXT_INDEX Procedimento para obter mais informações.

Tabela de Referência do Índice de Pesquisa JSON

Uma tabela local é criada no seu banco de dados com um sufixo padrão INDEX_NAME$TXTIDX. Essa tabela é criada internamente quando você executa DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX.

Você pode consultar a tabela INDEX_NAME$TXTIDX para procurar uma string usando a palavra-chave JSON_TEXTCONTAINS. Por exemplo, quando você chama o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX do valor INDEX_NAME como VNCLOGS, isso cria a tabela de referência do índice de pesquisa JSON VNCLOGS$TXTIDX.

A tabela de referência do índice de pesquisa JSON tem as seguintes colunas:
  • object_name: é o nome do arquivo no Object Storage que contém a string de texto pesquisada.

  • object_path: é o bucket do serviço Object Storage ou o URI da pasta que contém o arquivo do serviço Object Storage.

  • length: é o tamanho do documento JSON.

  • offset: é o deslocamento de byte do início do arquivo.

  • mtime: é o timestamp modificado pela última vez do arquivo de armazenamento de objetos. Este é o momento em que o arquivo foi acessado pela última vez por DBMS_CLOUD.

Execute a consulta a seguir para recuperar o nome do objeto, o caminho do objeto, o deslocamento e o tamanho da tabela de referência do índice de pesquisa JSON:
SELECT * FROM (SELECT object_name, object_path, length, offset 
 FROM vnclogs$txtidx 
 WHERE JSON_TEXTCONTAINS (file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}') 
 ORDER BY mtime DESC) 
 WHERE rownum < 3;
 

Essa consulta fornece informações internas sobre onde, em seus arquivos no Object Storage, os documentos relevantes estão localizados. A consulta não fornece os documentos reais.

Para recuperar os documentos diretamente, use a seguinte consulta:

SELECT * FROM (SELECT object_name, object_path, data
 FROM vnclogs
 WHERE JSON_TEXTCONTAINS (file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}')
 ORDER BY mtime DESC)
 WHERE rownum < 3;
 

Exibição de Referência do Índice de Pesquisa JSON

Uma view com o nome INDEX_NAME é criada quando você executa DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX para criar um índice JSON.

A própria view não contém dados. Os dados são extraídos na view quando você consulta a view INDEX_NAME usando a condição SQL JSON_TEXTCONTAINS.

Coluna Descrição
OBJECT_NAME

O nome do arquivo no Object Storage que contém a string de texto pesquisada.

OBJECT_PATH

O URI do bucket ou da pasta do serviço Object Storage que contém o arquivo de armazenamento de objetos.

LENGTH

O tamanho do documento JSON.

OFFSET

O deslocamento de byte do início do arquivo.

DATA

O conteúdo do arquivo do Object Storage.

FILE_LINE_JSON

A coluna na qual o JSON_TEXTCONTAINS é executado.

MTIME

O timestamp da última modificação do arquivo de armazenamento de objetos. Este é o momento em que o arquivo foi acessado pela última vez por DBMS_CLOUD.

Tabela de Log de Erros

Uma tabela de log de erro index_name$txtidx_err é criada quando você cria um índice de pesquisa JSON nos arquivos JSON armazenados no Object Storage.

A tabela contém uma lista dos documentos JSON inválidos em seus arquivos. Um documento JSON será marcado como inválido se o tamanho do arquivo de origem indexado exceder o valor padrão ou o valor especificado. O valor padrão é 32767 bytes, e o valor máximo que você pode especificar usando a opção json_index_doc_len format é 200000 bytes. Consulte Criar um Índice de Pesquisa JSON em Documentos JSON em Arquivos para obter mais informações.

Você pode consultar o index_name$txtidx_err para obter a lista de arquivos JSON inválidos reportados durante a criação do índice.

Coluna Descrição
OBJECT_NAME O nome do arquivo no Object Storage que contém a string de texto pesquisada.
OBJECT_PATH O URI do bucket ou da pasta do serviço Object Storage que contém o arquivo de armazenamento de objetos.
OFFSET Deslocamento de bytes do início da linha.
LENGTH Tamanho do documento JSON.
LINE_NUMBER Número da linha no arquivo no armazenamento de objetos.