Documentação do Oracle Cloud Infrastructure

Consultar Tabelas Externas com Particionamento Implícito

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

Tópico principal: Consultar Dados Externos com Autonomous Database

Sobre Tabelas Externas com Particionamento Implícito

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

Use o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para criar tabelas externas particionadas implícitas. 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 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.

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:
  • 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:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"]}');
  • 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:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"');
  • 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:

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'mycredential',
   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"]}');

Consulte CREATE_EXTERNAL_TABLE Procedures 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 => 'DEF_CRED_NAME',
    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.
    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 Database não tentará descobrir as colunas.

    Para detectar as colunas de partição, o Autonomous 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. 

    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'DEF_CRED_NAME',
   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"');

    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 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.

    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 Database aproveita as informações de particionamento da 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.

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 => 'DEF_CRED_NAME',
    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.
    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. 
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'DEF_CRED_NAME',
   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"]}');

    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 Database aproveita as informações de particionamento da 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.