Documentação do Oracle Cloud Infrastructure

Consultar Tabelas Externas com Particionamento Implícito

No Autonomous AI Database, você pode criar tabelas externas particionadas implícitas com base em dados particionados no estilo Hive ou com base em dados particionados de pastas simples armazenados no Cloud Object Store.

Tópico principal: Consultar Dados Externos com o Autonomous AI Database

Sobre Tabelas Externas com Particionamento Implícito

No Autonomous AI Database, use o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para criar tabelas externas particionadas implícitas com base em dados particionados no estilo Hive ou com base em dados particionados de pastas simples armazenados no Cloud Object Store.

Ao passar as opções apropriadas para este procedimento, as partições são derivadas dos dados de origem. As tabelas externas particionadas suportam a descoberta de runtime de colunas de partição e seus valores. A descoberta de runtime de alterações na estrutura de armazenamento de objetos subjacente, como adicionar ou remover objetos, simplifica o processo de manutenção eliminando a necessidade de procedimentos de sincronização adicionais exigidos pelo DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE. Isso garante que os dados estejam atualizados no tempo de execução da consulta.

Com o particionamento implícito, o Autonomous AI Database determina automaticamente as colunas nas quais uma tabela é "particionada" com base na estrutura de arquivos hierárquicos da origem de armazenamento de objetos. Não é necessário declarar explicitamente um esquema de particionamento. O particionamento implícito oferece benefícios de desempenho semelhantes a tabelas de partições sem a necessidade de definir explicitamente uma tabela externa particionada usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

Para otimizar o tempo de resposta da consulta para consultas de conjuntos de dados muito grandes usando o particionamento implícito, você pode ativar a otimização definindo strict_column_order como verdadeiro. O parâmetro strict_column_order é uma subopção da opção de formato implicit_partition_config. Essa otimização só se aplica quando partition_type é hive. Consulte Consultar Dados Particionados Implícitos Externos com a Organização do Arquivo de Origem do Formato Hive para obter um exemplo e DBMS_CLOUD Opções de Formato de Pacote para obter mais informações sobre os parâmetros implícitos do formato de particionamento.

As tabelas externas particionadas implícitas suportam os seguintes estilos de nomeação para objetos particionados no armazenamento de objetos:

Para obter mais informações sobre formatos de nomeação, consulte Sobre Tabelas Externas com Particionamento de Arquivos de Origem.

Usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE, as tabelas externas particionadas implícitas podem ser criadas das seguintes maneiras:
  • Otimize o particionamento implícito. Use a opção implicit_partition_config para ativar o particionamento implícito e a otimização do tempo de consulta decorrido.

    Neste exemplo, o particionamento implícito é ativado definindo partition_type como hive. O único valor válido para partition_type é hive. A otimização é ativada definindo setting strict_column_order como verdadeiro. A opção partition_columns especifica quais colunas são particionadas.

    Por exemplo:

    BEGIN
dbms_cloud.create_external_table (
   table_name        => 'partitions1',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'c varchar2(100), y number, total_sales number',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["org","tenant","dbtype","year","month","day"]
                          }
                          }');
END;
/
  • Defina o tipo de partição como hive com uma determinada lista de colunas de partição

    Nesse caso, implicit_partition_type é definido como hive e implicit_partition_columns fornece uma lista de colunas de partição.

    Por exemplo:

    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",
                          "implicit_partition_columns":["country","year","month"]}');
END;
/
  • Defina o tipo de partição como hive sem fornecer uma lista de colunas de partição

    Nesse caso, implicit_partition_type é definido como hive e implicit_partition_columns não é fornecido. As colunas de partição são detectadas automaticamente ao procurar '=' no caminho especificado por file_uri_list. O column_name fica à esquerda de '=' e o valor está no lado direito. Se um column_name não for encontrado no caminho, um erro será gerado.

    Por exemplo:

    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;
/
  • Forneça uma lista de colunas de partição sem especificar o tipo

    Nesse caso, implicit_partition_type não está definido e implicit_partition_columns fornece uma lista de colunas.

    Por exemplo:

    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;
/

Consulte Consultar Dados Particionados Implícitos Externos com a Organização do Arquivo de Origem do Formato Hive e Procedimento CREATE_EXTERNAL_TABLE para obter mais informações.

Consultar Dados Particionados Implícitos Externos com Organização de Arquivos de Origem do Formato Hive

Use o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para criar uma tabela externa particionada implícita a partir de dados no Object Store que foi gerada a partir de dados do Hive.

Os arquivos de origem de amostra no exemplo a seguir usam este formato de nomeação:

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

Considere os seguintes arquivos de origem de amostra:

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

Para criar uma tabela externa particionada implícita com dados armazenados neste formato de amostra do Hive, faça o seguinte:

  1. Armazene credenciais do Object Store usando o procedimento DBMS_CLOUD.CREATE_CREDENTIAL.

    Por exemplo:

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

    Não será necessário criar uma credencial para acessar o Oracle Cloud Infrastructure Object Store se você ativar as credenciais do controlador de recursos. Consulte Usar o Controlador de Recursos para Acessar Recursos da Oracle Cloud Infrastructure para obter mais informações.

    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 é obrigatória apenas uma vez, a menos que as credenciais do armazenamento de objetos sejam alteradas. Depois de armazenar as credenciais, você poderá usar o mesmo nome de credencial para criar tabelas externas.

    Consulte Procedimento CREATE_CREDENTIAL para obter informações sobre os parâmetros username e password para diferentes serviços de armazenamento de objetos.

  2. Crie uma tabela externa particionada implícita na parte superior dos arquivos de origem usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    1. Caso 1: Ativar particionamento implícito.
      Neste exemplo, uma vez que a opção de formato implicit_partition_columns não é fornecida, as colunas de partição são detectadas automaticamente e o particionamento implícito é ativado definindo a opção de formato implicit_partition_type como hive.

      No entanto, se uma lista de colunas for fornecida, essas colunas serão usadas como colunas de partição e o Autonomous AI Database não tentará descobrir as colunas.

      Para detectar as colunas de partição, o Autonomous AI Database começa a pesquisar no início do caminho, especificado por file_uri_list, por '='. Quando encontrada, a parte esquerda de '=' até a última '/' é tomada como coluna (por exemplo, "país") e a parte direita de '=', até a primeira '/' como valor (por exemplo, "EUA"). A pesquisa continua para '=' até depois do '/' que segue o primeiro valor de partição, detectando o segundo '=' no caminho, e assim por diante. 

      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;
/

      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 de URIs de arquivos de origem. Há duas opções para esta lista:

        • Especifique uma lista delimitada por vírgulas de URIs de arquivos individuais sem curinga.

        • Especifique um único URI de arquivo com curingas, em que os curingas só podem ser posteriores à última barra "/". O caractere "*" pode ser usado como curinga para vários caracteres, o caractere "?" pode ser usado como curinga para um único caractere.

        Neste exemplo, namespace-string é o namespace do Oracle Cloud Infrastructure Object Storage e bucketname é o nome do bucket. Consulte Noções Básicas de Namespaces do serviço Object Storage para obter mais informações.

      • column_list: é uma lista delimitada por vírgula de nomes de coluna e tipos de dados para a tabela externa. A lista inclui as colunas dentro do arquivo de dados e as derivadas do nome do objeto (dos nomes no caminho do arquivo especificado por file_uri_list).

        O column_list não é necessário quando os arquivos de dados são arquivos estruturados (Parquet, Avro ou ORC).

      • format: define as opções que você pode especificar para descrever o formato do arquivo de origem. A opção implicit_partition_type especifica o tipo de formato de dados como hive.

        Se os dados em seu arquivo de origem forem criptografados, decriptografe os dados especificando a opção de formato encryption. Consulte Descriptografar Dados Durante a Importação do Serviço Object Storage para obter mais informações sobre como decriptografar dados.

        Consulte DBMS_CLOUD Opções de Formato de Pacote para obter mais informações.

    1. Caso 2: Particionamento implícito otimizado
      Neste exemplo, o particionamento implícito é ativado especificando a opção de formato implicit_partition_config, e o particionamento implícito otimizado é ativado definindo strict_column_order como verdadeiro.

      Como partition_columns especifica uma lista de colunas particionadas, essas colunas são usadas como colunas de partição e o Autonomous AI Database não tenta descobrir as colunas. 

      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;
/

      Os parâmetros de formato são:

      • format: define as opções que você pode especificar para descrever o formato do arquivo de origem. A opção implicit_partition_config tem as seguintes subopções:
        • partition_type

          O único valor aceito é "hive". O padrão é um caminho de nome de arquivo não ativo. Se partition_type não for especificado, um erro de cliente será retornado.

        • partition_columns

          Lista de colunas particionadas.

        • strict_column_order

          Defina como true para ativar o particionamento implícito otimizado. Defina como true somente para casos em que você sabe que o caminho do Armazenamento de Objetos não será alterado e que não há prefixos ausentes. Defina como false ou omita para desativar.

        Se os dados em seu arquivo de origem forem criptografados, decriptografe os dados especificando a opção de formato encryption. Consulte Descriptografar Dados Durante a Importação do Serviço Object Storage para obter mais informações sobre como decriptografar dados.

        Consulte DBMS_CLOUD Opções de Formato de Pacote para obter mais informações.

    Consulte Procedimento CREATE_EXTERNAL_TABLE para obter informações detalhadas sobre os parâmetros. Consulte DBMS_CLOUD Formatos de URI para obter mais informações sobre os serviços de armazenamento de objetos na nuvem suportados.

  3. Agora você pode executar consultas na tabela particionada externa que você criou na etapa anterior.

    O Autonomous AI Database aproveita as informações de particionamento da sua tabela particionada externa, garantindo que a consulta acesse apenas os arquivos de dados relevantes no Object Store.

    Por exemplo:

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

    Esta instrução SQL consulta apenas dados na partição para o mês 02 do ano de 2024.

Tópico principal: Consultar Tabelas Externas com Particionamento Implícito

Otimize o Planejamento de Consultas do Armazenamento de Objetos com a opção strict_column_order

Quando uma consulta se destina a uma pasta de armazenamento de objetos com um número muito grande de arquivos e subpastas, a fase de planejamento e listagem pode se tornar o custo principal antes que qualquer dado seja realmente digitalizado. Isso é comum com layouts de pasta de estilo Hive em que os valores de partição são incorporados no caminho.

Observação

  • A definição strict_column_order é desativada por padrão. Ativar somente se o layout do caminho for consistente.

  • Se as convenções de pasta forem alteradas (como adicionar, remover ou reordenar colunas de partição), será necessário atualizar a opção partition_columns e pode ser necessário desativar essa opção.

Ative a opção strict_column_order pelo seguinte:

Se seus dados usarem caminhos particionados no estilo Hive e as colunas de partição sempre aparecerem em uma ordem fixa e consistente sem segmentos ausentes, ative a otimização pela seguinte definição: 

strict_column_order = true  (with partition_type = "hive")

O banco de dados pode ignorar diretórios desnecessários seguindo a ordem das partições já definidas. Isso significa que não precisa listar todos os objetos, o que pode acelerar significativamente o tempo de planejamento para grandes conjuntos de dados.

Observações sobre o uso da opção strict_column_order:

  • Os caminhos seguem a nomenclatura e a ordem do Hive, por exemplo:

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

  • As colunas de partição mantêm um conjunto e uma sequência fixos, sem reordenação ou prefixos ignorados.

  • Você precisa otimizar a listagem de tempo de planejamento para pastas que contêm um número muito grande de objetos.
  • Não é possível usar esta opção em conjuntos de dados em que nem todos os prefixos estejam presentes. Por exemplo, algumas pastas incluem year=, enquanto outras começam com month=).

Exemplos: Use a opção strict_column_order para Partições de Estilo Hive com 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;
/

Verifique se a lógica de planejamento evita listar caminhos de nível superior não relacionados (por exemplo, caminhos pertencentes a outros países ou anos):

  • Execute uma consulta seletiva que restrinja partições à esquerda, por exemplo:
    SELECT COUNT(*) FROM sales_xt WHERE country = ''US'' AND year = 2025;
    com
    strict_column_order = true
    .
  • Se os pressupostos de layout não forem atendidos, desative a opção e tente novamente.

Consultar Dados de Estilo Não Hive Particionados Implícitos Externos

Use o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para criar uma tabela externa particionada implícita com base em dados no armazenamento de objetos que foi gerada a partir de dados não Hive.

Os arquivos de origem de amostra no exemplo a seguir usam este formato de nomeação:

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

Considere os seguintes arquivos de origem de amostra:

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

Para criar uma tabela externa particionada implícita com dados armazenados neste formato de amostra do Hive, faça o seguinte:

  1. Armazene credenciais do Object Store usando o procedimento DBMS_CLOUD.CREATE_CREDENTIAL.

    Por exemplo:

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

    Não será necessário criar uma credencial para acessar o Oracle Cloud Infrastructure Object Store se você ativar as credenciais do controlador de recursos. Consulte Usar o Controlador de Recursos para Acessar Recursos do Oracle Cloud Infrastructure para obter mais informações.

    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 é obrigatória apenas uma vez, a menos que as credenciais do armazenamento de objetos sejam alteradas. Depois de armazenar as credenciais, você poderá usar o mesmo nome de credencial para criar tabelas externas.

    Consulte Procedimento CREATE_CREDENTIAL para obter informações sobre os parâmetros username e password para diferentes serviços de armazenamento de objetos.

  2. Crie uma tabela externa particionada implícita na parte superior dos arquivos de origem usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    Neste exemplo, o particionamento implícito é ativado fornecendo as colunas de partição com a opção de formato implicit_partition_columns. Como o nome da pasta não inclui as colunas de partição, os valores de partição no caminho, especificados por file_uri_list, só poderão ser descobertos se uma lista explícita de colunas for fornecida com a opção de formato implicit_partition_columns. Para detectar os respectivos valores de coluna no caminho, a ordem das colunas deve ser igual à ordem dos valores.

    As colunas de partição são detectadas 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;
/

    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 de URIs de arquivos de origem. Há duas opções para esta lista:

      • Especifique uma lista delimitada por vírgulas de URIs de arquivos individuais sem curinga.

      • Especifique um único URI de arquivo com curingas, em que os curingas só podem ser posteriores à última barra "/". O caractere "*" pode ser usado como curinga para vários caracteres, o caractere "?" pode ser usado como curinga para um único caractere.

    • column_list: é uma lista delimitada por vírgula de nomes de coluna e tipos de dados para a tabela externa. A lista inclui as colunas dentro do arquivo de dados e as derivadas do nome do objeto (dos nomes no caminho do arquivo especificado por file_uri_list).

      O column_list não é necessário quando os arquivos de dados são arquivos estruturados (Parquet, Avro ou ORC).

    • format: define as opções que você pode especificar para descrever o formato do arquivo de origem. A opção implicit_partition_type não está definida. Como implicit_partition_columns é fornecido, o tipo é detectado automaticamente como não-hive.

      Se os dados em seu arquivo de origem forem criptografados, decriptografe os dados especificando a opção de formato encryption. Consulte Descriptografar Dados Durante a Importação do Serviço Object Storage para obter mais informações sobre como decriptografar dados.

      Consulte DBMS_CLOUD Opções de Formato de Pacote para obter mais informações.

    Neste exemplo, namespace-string é o namespace do Oracle Cloud Infrastructure Object Storage e bucketname é o nome do bucket. Consulte Noções Básicas de Namespaces do serviço Object Storage para obter mais informações.

    Consulte Procedimento CREATE_EXTERNAL_TABLE para obter informações detalhadas sobre os parâmetros.

    Consulte DBMS_CLOUD Formatos de URI para obter mais informações sobre os serviços de armazenamento de objetos na nuvem suportados.

  3. Agora você pode executar consultas na tabela particionada externa que você criou na etapa anterior.

    O Autonomous AI Database aproveita as informações de particionamento da sua tabela particionada externa, garantindo que a consulta acesse apenas os arquivos de dados relevantes no Object Store.

    Por exemplo:

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

    Esta instrução SQL consulta apenas dados na partição para o ano de 2024.