Consultar Dados Particionados Externos com a Organização do Arquivo de Origem de Formato do Hive

Use DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE para criar uma tabela particionada externa e gerar as informações de particionamento do caminho do arquivo do Cloud Object Store.

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 particionada externa na parte superior dos arquivos de origem usando o procedimento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

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

   Por exemplo:

   BEGIN
       DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
           TABLE_NAME => 'sales_sample',
           CREDENTIAL_NAME => 'DEF_CRED_NAME',
           FILE_URI_LIST => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet',
           FORMAT => '{"type":"parquet", "schema": "first","partition_columns":[{"name":"month","type":"varchar2(100)"}]}');
   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).

     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. O parâmetro partition_columns format especifica os nomes das colunas de partição.

     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.

   A chamada DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE resultaria na seguinte definição de tabela:

   CREATE TABLE "ADMIN"."SALES_SAMPLE"
      ( "DAY_ID" TIMESTAMP (6),
         "GENRE_ID" NUMBER(19,0),
         "MOVIE_ID" NUMBER(19,0),
         "CUST_ID" NUMBER(19,0),
         "APP" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
         "DEVICE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
         "OS" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
         "PAYMENT_METHOD" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
         "LIST_PRICE" BINARY_DOUBLE,
         "DISCOUNT_TYPE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
         "DISCOUNT_PERCENT" BINARY_DOUBLE,
         "ACTUAL_PRICE" BINARY_DOUBLE,
         "MONTH" VARCHAR2(100 BYTE) COLLATE "USING_NLS_COMP"
      ) DEFAULT COLLATION "USING_NLS_COMP"
      ORGANIZATION EXTERNAL
       ( TYPE ORACLE_BIGDATA
         DEFAULT DIRECTORY "DATA_PUMP_DIR"
         ACCESS PARAMETERS
         ( com.oracle.bigdata.fileformat=parquet
   com.oracle.bigdata.filename.columns=["month"]
   com.oracle.bigdata.file_uri_list="https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet"
   com.oracle.bigdata.credential.schema="ADMIN"
   com.oracle.bigdata.credential.name=CRED_OCI
   com.oracle.bigdata.trimspaces=notrim
     )
       )
      REJECT LIMIT 0
     PARTITION BY LIST ("MONTH")
    (PARTITION "P1" VALUES (('2019-01'))
         LOCATION
          ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-01/*.parquet'
          ),
    PARTITION "P2" VALUES (('2019-02'))
         LOCATION
          ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-02/*.parquet'
          ))
     PARALLEL ;

   Consulte CREATE_EXTERNAL_PART_TABLE Procedure 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 movie_id, month FROM sales WHERE month='2019-02'

   As tabelas particionadas externas que você cria com DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE incluem duas colunas invisíveis file$path e file$name. Essas colunas ajudam a identificar de qual arquivo um registro está vindo. Consulte Colunas de Metadados da Tabela Externa para obter mais informações.