Consultar Dados Externos com Arquivos de Origem ORC, Parquet ou Avro

Quando você acessa dados ORC, Parquet ou Avro armazenados no armazenamento de objetos usando tabelas externas no Autonomous Database, pode definir automática ou explicitamente o tamanho máximo das colunas de texto.

1. Armazene suas credenciais do armazenamento de objetos para acessar o armazenamento de objetos, usando o procedimento DBMS_CLOUD.CREATE_CREDENTIAL:

   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 para ORC, Parquet ou Avro sobre seus arquivos de origem usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE e especifique o parâmetro de formato maxvarchar.

   O procedimento DBMS_CLOUD.CREATE_EXTERNAL_TABLE suporta arquivos externos nos serviços de armazenamento de objetos na nuvem suportados, incluindo: Oracle Cloud Infrastructure Object Storage, Azure Blob Storage ou Azure Data Lake Storage, Amazon S3 e Amazon S3-Compatible, incluindo: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage e Wasabi Hot Cloud Storage. A credencial é uma propriedade em nível de tabela; portanto, os arquivos externos devem estar no mesmo armazenamento de objetos.

   Por padrão, as colunas criadas na tabela externa mapeiam automaticamente seus tipos de dados para os tipos de dados Oracle dos campos encontrados nos arquivos de origem e os nomes das colunas da tabela externa correspondem aos nomes dos campos de origem.

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"extended"}'
    );
   END;
   /     
   

   Os parâmetros são:

   • table_name: é o nome da tabela externa.

   • credential_name: é o nome da credencial criada na etapa anterior. O parâmetro credential_name deve estar de acordo com as convenções de nomeação de objetos da Oracle. Consulte Regras de Nomeação de Objeto de Banco de Dados para obter mais informações.

   • file_uri_list: é uma lista delimitada por vírgulas dos arquivos de origem que você deseja consultar. O formato de URI para Pontos Finais Dedicados é suportado em realms comerciais (OC1). Consulte Pontos Finais Dedicados do Serviço Object Storage e DBMS_CLOUD Formatos de URI para obter mais informações.

   • format: define as opções para descrever o formato do arquivo do arquivo. Para um arquivo Parquet, use o parâmetro format para especificar o type parquet. Para um arquivo Avro, use o parâmetro format para especificar type avro. Para um arquivo ORC, use o parâmetro format para especificar o type orc.

     A opção maxvarchar com o valor extended especifica que as colunas de texto são criadas como varchar(32767) em uma instância do Autonomous Database com tamanho de string estendida. Os valores possíveis são standard com varchar(4000), extended com varchar(32767) e auto. O valor padrão do maxvarchar é auto. Com esse valor, o tamanho máximo do texto se baseia no valor de MAX_STRING_SIZE.

   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.

   Por padrão, o parâmetro format schema é definido e as colunas e os tipos de dados são derivados automaticamente e os campos na origem correspondem às colunas da tabela externa por nome. Os tipos de dados de origem são convertidos para os tipos de dados Oracle da coluna da tabela externa de acordo com o mapeamento DBMS_CLOUD para os tipos de dados ORC, Parquet ou Avro. Os valores válidos do parâmetro schema são:

   • first: Analise o esquema do primeiro arquivo ORC, Parquet ou Avro que DBMS_CLOUD encontra na file_uri_list especificada (first é o valor padrão para schema).

   • all: Analise todos os esquemas de todos os arquivos ORC, Parquet ou Avro encontrados no file_uri_list. Como esses são simplesmente arquivos capturados em um armazenamento de objetos, não há garantia de que os metadados de cada arquivo sejam os mesmos. Por exemplo, File1 pode conter um campo chamado "endereço", enquanto File2 pode não ter esse campo. Examinar cada arquivo para derivar as colunas é um pouco mais caro, mas pode ser necessário se o primeiro arquivo não contiver todos os campos obrigatórios.

   Observação
   
   Se o parâmetro column_list for especificado, você fornecerá os nomes e os tipos de dados da coluna para a tabela externa e o valor schema, se especificado, será ignorado. Usando o column_list, você pode limitar as colunas na tabela externa. Se column_list não for especificado, o valor padrão schema será first.
3. Agora você pode executar consultas na tabela externa criada na etapa anterior:

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(32767) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(32767) 
   CITY                 VARCHAR2(32767) 
   STATE_PROVINCE       VARCHAR2(32767) 
   INCOME_LEVEL         VARCHAR2(32767)
   

   SELECT prod_id, quantity_sold, gender, city, income_level 
        FROM sales_extended_ext 
        WHERE ROWNUM < 8;
   
        PROD_ID     QUANTITY_SOLD     GENDER    CITY              INCOME_LEVEL
   1    13          1                 M         Adelaide          K: 250,000 – 299,999
   2    13          1                 M         Dolores           L: 300,000 and above
   3    13          1                 M         Cayuga            F: 110,000 – 129,999
   4    13          1                 F         Bergen op Zoom    C: 50,000 – 69,999
   5    13          1                 F         Neuss             J: 190,000 – 249,999
   6    13          1                 F         Darwin            F: 110,000 – 129,999
   7    13          1                 M         Sabadell          K:250,000 – 299,999
   

   Esta consulta mostra valores para linhas na tabela externa. Se quiser consultar esses dados com frequência, depois de examinar os dados, você poderá carregá-los em uma tabela com DBMS_CLOUD.COPY_DATA.

   Se você especificar a opção format maxvarchar como standard, as colunas de texto varchar2() serão criadas com o tamanho 4000. Por exemplo:

   BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
       table_name =>'sales_extended_ext',
       credential_name =>'DEF_CRED_NAME',
       file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales_extended.parquet',
       format =>  '{"type":"parquet", "schema": "first", "maxvarchar":"standard"}'
    );
   END;
   /     
   

   O parâmetro format com a opção maxvarchar definida com o valor standard especifica que as colunas de texto são criadas como varchar(4000).

   DESC sales_extended_ext;
   Name           Null? Type           
   -------------- ----- -------------- 
   PROD_ID              NUMBER(10)     
   CUST_ID              NUMBER(10)     
   TIME_ID              VARCHAR2(4000) 
   CHANNEL_ID           NUMBER(10)     
   PROMO_ID             NUMBER(10)     
   QUANTITY_SOLD        NUMBER(10)     
   AMOUNT_SOLD          NUMBER(10,2)   
   GENDER               VARCHAR2(4000) 
   CITY                 VARCHAR2(4000) 
   STATE_PROVINCE       VARCHAR2(4000) 
   INCOME_LEVEL         VARCHAR2(4000)