Consultar Dados Externos com o Serviço Data Catalog

Ao sincronizar com metadados do serviço Data Catalog, o Autonomous Database cria automaticamente tabelas externas para cada entidade lógica coletada pelo serviço Data Catalog. Essas tabelas externas são definidas em esquemas de banco de dados que são totalmente gerenciados pelo processo de sincronização de metadados. Os usuários podem consultar dados imediatamente sem precisar derivar manualmente o esquema (colunas e tipos de dados) para origens de dados externas e criar tabelas externas manualmente.

A sincronização é dinâmica, mantendo o Autonomous Database atualizado em relação às alterações nos dados subjacentes, reduzindo o custo de administração, pois ele mantém automaticamente centenas a milhares de tabelas. Ele também permite que várias instâncias do Autonomous Database compartilhem o mesmo serviço Data Catalog, reduzindo ainda mais os custos de gerenciamento e fornecendo um conjunto comum de definições de negócios.

As pastas/buckets do serviço Data Catalog são contêineres que sincronizam com esquemas do Autonomous Database. As entidades lógicas dentro dessas pastas/buckets são mapeadas para tabelas externas do Autonomous Database. Esses esquemas e tabelas externas são gerados e mantidos automaticamente por meio do processo de sincronização:

• Pastas/Buckets mapeiam para esquemas de banco de dados que são apenas para fins organizacionais.
• A organização deve ser consistente com o data lake e minimizar a confusão ao acessar dados por diferentes caminhos.
• O serviço Data Catalog é a origem da verdade para as tabelas contidas nos esquemas. As alterações feitas no serviço Data Catalog atualizam as tabelas do esquema durante uma sincronização subsequente.

Para usar esse recurso, um Administrador do Serviço Database Data Catalog inicia uma conexão com uma instância do serviço Data Catalog, seleciona quais ativos de dados e entidades lógicas serão sincronizados e executa a sincronização. O processo de sincronização cria esquemas e tabelas externas com base nos ativos de dados coletados e nas entidades lógicas do serviço Data Catalog selecionado. Assim que as tabelas externas são criadas, os Analistas de Dados podem começar a consultar seus dados sem precisar derivar manualmente o esquema para origens de dados externas e criar tabelas externas.

É necessária uma compreensão dos conceitos a seguir para consulta com o serviço Data Catalog.

Serviço Data Catalog

O serviço Data Catalog coleta ativos de dados que apontam para as origens de dados de armazenamento de objetos que você deseja consultar com o Autonomous Database. No serviço Data Catalog, você pode especificar como os dados são organizados durante a coleta, suportando diferentes padrões de organização de arquivos. Como parte do processo de coleta do serviço Data Catalog, você pode selecionar os buckets e arquivos que deseja gerenciar no ativo. Para obter mais informações, consulte Visão Geral do Serviço Data Catalog.

Armazenamentos de Objetos

As Lojas de Objetos têm buckets que contêm uma variedade de objetos. Alguns tipos comuns de objetos encontrados nesses buckets incluem: arquivos CSV, parquet, avro, json e ORC. Os buckets geralmente têm uma estrutura ou um padrão de design para os objetos que eles contêm. Há muitas maneiras diferentes de estruturar dados e muitas maneiras diferentes de interpretar esses padrões.

Por exemplo, um padrão de design típico usa pastas de nível superior que representam tabelas. Os arquivos dentro de uma determinada pasta compartilham o mesmo esquema e contêm dados para essa tabela. As subpastas são frequentemente usadas para representar partições de tabela (por exemplo, uma subpasta para cada dia). O serviço Data Catalog se refere a cada pasta de nível superior como uma entidade lógica, e essa entidade lógica é mapeada para uma tabela externa do Autonomous Database.

Conexão

Uma conexão é uma conexão do Autonomous Database com uma instância do serviço Data Catalog. Para cada instância do Autonomous Database, pode haver conexões com várias instâncias do serviço Data Catalog. A credencial do Autonomous Database deve ter direitos de acesso aos ativos do serviço Data Catalog que foram coletados do armazenamento de objetos.

Coleta

Um processo do serviço Data Catalog que verifica o armazenamento de objetos e gera as entidades lógicas de seus conjuntos de dados.

Ativo de Dados

Um ativo de dados no serviço Data Catalog representa uma origem de dados, que inclui bancos de dados, Oracle Object Storage, Kafka e muito mais. O Autonomous Database utiliza os ativos do Oracle Object Storage para sincronização de metadados.

Entidade de Dados

Entidade de dados no serviço Data Catalog é uma coleção de dados, como uma tabela ou view do banco de Dados, ou um único arquivo, e normalmente tem muitos atributos que descrevem seus dados.

Entidade Lógica

Em Data Lakes, vários arquivos geralmente compreendem uma única entidade lógica. Por exemplo, você pode ter arquivos de sequência de cliques diários e esses arquivos compartilham o mesmo esquema e tipo de arquivo.

Uma entidade lógica do serviço Data Catalog é um grupo de arquivos do serviço Object Storage que são derivados durante a coleta aplicando padrões de nome de arquivo que foram criados e designados a um ativo de dados.

Objeto de Dados

Um objeto de dados no serviço Data Catalog refere-se a ativos de dados e entidades de dados.

Padrão de Nome de Arquivo

Em um data lake, os dados podem ser organizados de diferentes maneiras. Normalmente, as pastas capturam arquivos do mesmo esquema e tipo. Você deve se registrar no serviço Data Catalog como seus dados são organizados. Os padrões de nome de arquivo são usados para identificar como seus dados são organizados. No serviço Data Catalog, você pode definir padrões de nome de arquivo usando expressões regulares. Quando o serviço Data Catalog coleta um ativo de dados com um padrão de nome de arquivo designado, as entidades lógicas são criadas com base no padrão de nome de arquivo. Ao definir e designar esses padrões a ativos de dados, vários arquivos podem ser agrupados como entidades lógicas com base no padrão de nome de arquivo.

Sincronizar (Sincronizar)

O Autonomous Database executa sincronizações com o serviço Data Catalog para manter automaticamente seu banco de dados atualizado em relação às alterações nos dados subjacentes. A sincronização pode ser realizada manualmente ou em uma programação.

O processo de sincronização cria esquemas e tabelas externas com base nos ativos de dados e entidades lógicas do serviço Data Catalog. Esses esquemas são protegidos, o que significa que seus metadados são gerenciados pelo serviço Data Catalog. Se quiser alterar os metadados, você deverá fazer as alterações no serviço Data Catalog. Os esquemas do Autonomous Database refletirão quaisquer alterações após a execução da próxima sincronização. Para obter mais detalhes, consulte Mapeamento de Sincronização.

O processo de sincronização cria e atualiza esquemas do Autonomous Database e tabelas externas com base em ativos de dados, pastas, entidades lógicas, atributos e substituições personalizadas relevantes do serviço Data Catalog.

Há um workflow típico de ações executadas por usuários que desejam consultar o serviço Data Catalog.

select * from all_dcat_connections;

Nesse cenário, o Moviestream está capturando dados em uma zona de destino no armazenamento de objetos. Grande parte desses dados, mas não necessariamente todos, é usada para alimentar um Autonomous Database. Antes de alimentar o Autonomous Database, os dados são transformados, limpos e subsequentemente armazenados na área "ouro".

1. Object Store - Revisar buckets, pastas e arquivos
   1. Revise os buckets no seu armazenamento de objetos.
      Por exemplo, abaixo estão os buckets de destino (moviestream_landing) e de zona ouro (moviestream_gold) no armazenamento de objetos:
   2. Verifique as pastas e os arquivos nos buckets do armazenamento de objetos.
      Por exemplo, abaixo estão as pastas no bucket de destino (moviestream_landing) no armazenamento de objetos:
2. Catálogo de Dados - Criar padrões de nome de arquivo
   1. Informe ao serviço Data Catalog como seus dados são organizados usando padrões de nome de arquivo. Estas são expressões regulares usadas para categorizar arquivos. Os padrões de nome de arquivo são usados pelo coletor do serviço Data Catalog para derivar entidades lógicas. Os dois padrões de nome de arquivo a seguir são usados para coletar os buckets no exemplo do MovieStream. Consulte Coletando Arquivos do Serviço Object Storage como Entidades de Dados Lógicas para obter mais detalhes sobre a criação de padrões de nome de arquivo.

      Estilo Hive	Estilo de pasta
      {bucketName:.*}/{logicalEntity:[^/]+}.db/{logicalEntity:[^/]+}/.*	{bucketName:[\w]+}/{logicalEntity:[^/]+}(?<!.db)/.*$
      Cria entidades lógicas para origens que contêm ".db" como a primeira parte do nome do objeto. Para garantir a exclusividade dentro do bucket, o nome resultante é (db-name).(nome da pasta)	Cria uma entidade lógica com base no nome da pasta fora da raiz Para evitar a duplicação com o Hive, os nomes de objetos que têm ".db" são ignorados.

   2. Para criar padrões de nome de arquivo, vá para a guia Padrões de Nome de Arquivo do seu serviço Data Catalog e clique em Criar Padrão de Nome de Arquivo. Por exemplo, a seguir está a guia Criar Padrão de Nome de Arquivo do serviço Data Catalog moviestream:
3. Catálogo de Dados - Criação de Ativo de Dados
   1. Crie um ativo de dados que seja usado para coletar dados do seu armazenamento de objetos.
      Por exemplo, um ativo de dados chamado phoenixObjStore é criado no serviço Data Catalog moviestream:
   2. Adicionar uma conexão ao seu ativo de dados.
      Neste exemplo, o ativo de dados se conecta ao compartimento do recurso de armazenamento de objetos moviestream.
   3. Agora, associe seus padrões de nome de arquivo ao seu ativo de dados. Selecione Designar Padrões de Nome de Arquivo, marque os padrões desejados e clique em Designar.
      Por exemplo, aqui estão os padrões designados ao ativo de dados phoenixObjStore:
      Descrição da ilustração assign_filename_patterns_v1.png
4. Catálogo de Dados - Coletar dados do armazenamento de objetos
   1. Colete o ativo de dados do serviço Data Catalog. Selecione os buckets do armazenamento de objetos que contêm os dados de origem.
      Neste exemplo, os buckets moviestream_gold e moviestream_landing do armazenamento de objetos são selecionados para coleta.
   2. Depois de executar o job, você verá as entidades lógicas. Use a opção Procurar Ativos de Dados para revisá-los.
      Neste exemplo, você está examinando a entidade lógica customer-extension e seus atributos.

      Se você tiver um glossário, o serviço Data Catalog recomenda categorias e termos para associar à entidade e seus atributos. Isso fornece um contexto de negócios para os itens. Esquemas, tabelas e colunas muitas vezes não são autoexplicativos.

      Em nosso exemplo, queremos diferenciar entre os diferentes tipos de buckets e o significado de seu conteúdo:

      • O que é uma zona de pouso?
      • Qual é a precisão dos dados?
      • Quando foi a última atualização?
      • qual é a definição de uma entidade lógica ou seu atributo
5. Autonomous Database - Estabelecer Conexão com o Serviço Data Catalog

   Conectar o Autonomous Database ao serviço Data Catalog. Você precisa garantir que a credencial usada para fazer essa conexão esteja usando um principal do OCI autorizado a acessar o ativo do serviço Data Catalog. Para obter mais informações, consulte Políticas do Serviço Data Catalog e Acessar Recursos da Nuvem Configurando Políticas e Atribuições.

   1. Estabelecer Conexão com o Serviço Data Catalog

      -- Variables are used to simplify usage later
      define oci_credential = 'OCI$RESOURCE_PRINCIPAL'
      define dcat_ocid = 'ocid1.datacatalog.oc1.iad.aaaaaaaardp66bg....twiq'
      define dcat_region='us-ashburn-1'
      define uri_root =    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/landing/o'
      define uri_private = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/private_data/o'
      
      -- Run as admin
      -------
      -- Enable resource principal support
      -------
      exec dbms_cloud_admin.enable_resource_principal();
      
      -- Test to make sure credential was created. Returns a row if it was successful
      select * 
      from dba_credentials 
      where credential_name = 'OCI$RESOURCE_PRINCIPAL' and owner = 'ADMIN'; 
      
      -- Query a private bucket to test the principal and privileges.
      select * 
      from dbms_cloud.list_objects('&oci_credential', '&uri_private/');
      
      --------
      -- Set the credentials to use for object store and data catalog
      -- Connect to Data Catalog
      -- Review connection
      ---------
      -- Set credentials
      exec dbms_dcat.set_data_catalog_credential(credential_name => '&oci_credential');
      exec dbms_dcat.set_object_store_credential(credential_name => '&oci_credential');
      
      -- Connect to Data Catalog
      begin
         dbms_dcat.set_data_catalog_conn (
           region => '&dcat_region',
           catalog_id => '&dcat_ocid');
      end;
      /
      -- Review the connection
      select * from all_dcat_connections;
      

   2. Sincronize o serviço Data Catalog com o Autonomous Database. Aqui, sincronizaremos todos os ativos de armazenamento de objetos:

      
      -- Sync Data Catalog with Autonomous Database
      ---- Let's sync all of the assets.
      begin
       dbms_dcat.run_sync('{"asset_list":["*"]}');
      end;
      /
      
      -- View log
      select type, start_time, status, logfile_table from user_load_operations;  -- Logfile_Table will have the name of the table containing the full log.
      select * from dbms_dcat$1_log;
      
      -- View the new external tables
      select * from dcat_entities;
      select * from dcat_attributes;
      
      

   3. Autonomous Database - Agora comece a executar consultas no armazenamento de objetos.

      -- Query the Data !
      select *from dcat$phoenixobjstore_moviestream_gold.genre
      ;

6. Alterar esquemas para objetos

   Os nomes de esquema padrão são bastante complicados. Vamos simplificá-los especificando o atributo personalizado Oracle-Db-Schema do ativo e da pasta no serviço Data Catalog. Altere o ativo de dados para PHX e as pastas para landing e gold, respectivamente. O esquema é uma concatenação dos dois.

   1. No serviço Data Catalog, navegue até o bucket moviestream_landing e altere o ativo para landing e gold, respectivamente.

      Antes da alteração:

      Após a alteração:

   2. Execute outra sincronização.

Este cenário ilustra como criar tabelas externas no Autonomous Database baseadas em entidades lógicas do serviço Data Catalog coletadas de dados particionados no Object Store.

1. Object Store - Revisar buckets, pastas e arquivos
   1. Revise os buckets no seu armazenamento de objetos.
      Por exemplo, abaixo estão os buckets de destino (moviestream_landing) e de zona ouro (moviestream_gold) no armazenamento de objetos:
   2. Verifique as pastas e os arquivos nos buckets do armazenamento de objetos.
      Por exemplo, abaixo estão as pastas no bucket de destino (moviestream_landing) no armazenamento de objetos:
2. Catálogo de Dados - Criar padrões de nome de arquivo
   1. Informe ao serviço Data Catalog como seus dados são organizados usando padrões de nome de arquivo. Estes são prefixos de pasta ou expressões regulares usadas para categorizar arquivos. Os padrões de nome de arquivo são usados pelo coletor do serviço Data Catalog para derivar entidades lógicas. Quando um prefixo de pasta é especificado, o serviço Data Catalog gera automaticamente entidades lógicas com base no prefixo de pasta especificado no armazenamento de objetos. O padrão de nome de arquivo a seguir é usado para coletar os buckets no exemplo do MovieStream. Consulte Coletando Arquivos do Serviço Object Storage como Entidades de Dados Lógicas para obter mais detalhes sobre a criação de padrões de nome de arquivo.

      Prefixo de pasta	Descrição
      workshop.db/	Cria entidades lógicas para origens que contêm o caminho "workshop.db" no armazenamento de objetos.

   2. Para criar padrões de nome de arquivo, vá para a guia Padrões de Nome de Arquivo do seu serviço Data Catalog e clique em Criar Padrão de Nome de Arquivo. Por exemplo, a seguir está a guia Criar Padrão de Nome de Arquivo do serviço Data Catalog moviestream:
3. Catálogo de Dados - Criação de Ativo de Dados
   1. Crie um ativo de dados que seja usado para coletar dados do seu armazenamento de objetos.
      Por exemplo, um ativo de dados chamado amsterdamObjStore é criado no serviço Data Catalog moviestream:
   2. Adicionar uma conexão ao seu ativo de dados.
      Neste exemplo, o ativo de dados se conecta ao compartimento do recurso de armazenamento de objetos moviestream.
   3. Agora, associe seus padrões de nome de arquivo ao seu ativo de dados. Selecione Designar Padrões de Nome de Arquivo, marque os padrões desejados e clique em Designar.
      Por exemplo, aqui estão os padrões designados ao ativo de dados amsterdamObjStore:
4. Catálogo de Dados - Coletar dados do armazenamento de objetos
   1. Colete o ativo de dados do serviço Data Catalog. Selecione os buckets do armazenamento de objetos que contêm os dados de origem.
      Neste exemplo, os buckets moviestream_gold e moviestream_landing do armazenamento de objetos são selecionados para coleta.
   2. Depois de executar o job, você verá as entidades lógicas. Use a opção Procurar Ativos de Dados para revisá-los.
      Neste exemplo, você está examinando a entidade lógica sales_sample_parquet e seus atributos. Observe que o serviço Data Catalog identificou o atributo month como particionado.
5. Autonomous Database - Estabelecer Conexão com o Serviço Data Catalog

   Conectar o Autonomous Database ao serviço Data Catalog. Você precisa garantir que a credencial usada para fazer essa conexão esteja usando um principal do OCI autorizado a acessar o ativo do serviço Data Catalog. Para obter mais informações, consulte Políticas do Serviço Data Catalog e Acessar Recursos da Nuvem Configurando Políticas e Atribuições.

   1. Estabelecer Conexão com o Serviço Data Catalog

      -- Variables are used to simplify usage later
      define oci_credential = 'OCI$RESOURCE_PRINCIPAL'
      define dcat_ocid = 'ocid1.datacatalog.oc1.eu-amsterdam-1....leguurn3dmqa'
      define dcat_region='eu-amsterdam-1'
      define uri_root =    'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/landing/o'
      define uri_private = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/private_data/o'
      
      -- Run as admin
      -------
      -- Enable resource principal support
      -------
      exec dbms_cloud_admin.enable_resource_principal();
      
      -- Test to make sure credential was created. Returns a row if it was successful
      select * 
      from dba_credentials 
      where credential_name = 'OCI$RESOURCE_PRINCIPAL' and owner = 'ADMIN'; 
      
      -- Query a private bucket to test the principal and privileges.
      select * 
      from dbms_cloud.list_objects('&oci_credential', '&uri_private/');
      
      --------
      -- Set the credentials to use for object store and data catalog
      -- Connect to Data Catalog
      -- Review connection
      ---------
      -- Set credentials
      exec dbms_dcat.set_data_catalog_credential(credential_name => '&oci_credential');
      exec dbms_dcat.set_object_store_credential(credential_name => '&oci_credential');
      
      -- Connect to Data Catalog
      begin
         dbms_dcat.set_data_catalog_conn (
           region => '&dcat_region',
           catalog_id => '&dcat_ocid');
      end;
      /
      -- Review the connection
      select * from all_dcat_connections;
      

   2. Sincronize o serviço Data Catalog com o Autonomous Database. Aqui, sincronizaremos todos os ativos de armazenamento de objetos:

      
      -- Sync Data Catalog with Autonomous Database
      ---- Let's sync all of the assets.
      begin
       dbms_dcat.run_sync('{"asset_list":["*"]}');
      end;
      /
      
      -- View log
      select type, start_time, status, logfile_table from user_load_operations;  -- Logfile_Table will have the name of the table containing the full log.
      select * from dbms_dcat$1_log;
      
      -- View the new external tables
      select * from dcat_entities;
      select * from dcat_attributes;
      
      

   3. Autonomous Database - Agora comece a executar consultas no armazenamento de objetos.

      -- Query the Data !
      select count(*) from DCAT$AMSTERDAMOBJSTORE_MOVIESTREAM_LANDING.SALES_SAMPLE_PARQUET;
      
      -- Examine the generated partitioned table
      select dbms_metadata.get_ddl('TABLE','SALES_SAMPLE_PARQUET','DCAT$AMSTERDAMOBJSTORE_MOVIESTREAM_LANDING') from dual;
      
      CREATE TABLE "DCAT$AMSTERDAMOBJSTORE_MOVIESTREAM_LANDING"."SALES_SAMPLE_PARQUET"
      (    "MONTH" VARCHAR2(4000) COLLATE "USING_NLS_COMP",
           "DAY_ID" TIMESTAMP (6),
           "GENRE_ID" NUMBER(20,0),
           "MOVIE_ID" NUMBER(20,0),
           "CUST_ID" NUMBER(20,0),
      ...
      )  DEFAULT COLLATION "USING_NLS_COMP"
      ORGANIZATION EXTERNAL
       ( TYPE ORACLE_BIGDATA
         ACCESS PARAMETERS
         ( com.oracle.bigdata.fileformat=parquet
           com.oracle.bigdata.filename.columns=["MONTH"]
           com.oracle.bigdata.file_uri_list="https://swiftobjectstorage.eu-amsterdam-1.oraclecloud.com/v1/tenancy/moviestream_landing/workshop.db/sales_sample_parquet/*"
      ...
         )
       )
      REJECT LIMIT 0
      PARTITION BY LIST ("MONTH")
      (PARTITION "P1"  VALUES (('2019-01'))
         LOCATION ( 'https://swiftobjectstorage.eu-amsterdam-1.oraclecloud.com/v1/tenancy/moviestream_landing/workshop.db/sales_sample_parquet/month=2019-01/*'),
      PARTITION "P2"  VALUES (('2019-02'))
         LOCATION ( 'https://swiftobjectstorage.eu-amsterdam-1.oraclecloud.com/v1/tenancy/moviestream_landing/workshop.db/sales_sample_parquet/month=2019-02/*'),
      ...PARTITION "P24"  VALUES (('2020-12'))
         LOCATION ( 'https://swiftobjectstorage.eu-amsterdam-1.oraclecloud.com/v1/tenancy/moviestream_landing/workshop.db/sales_sample_parquet/month=2020-12/*'))
      PARALLEL

6. Alterar esquemas para objetos

   Os nomes de esquema padrão são bastante complicados. Vamos simplificá-los especificando o atributo personalizado Oracle-Db-Schema do ativo e da pasta no serviço Data Catalog. Altere o ativo de dados para PHX e as pastas para landing e gold, respectivamente. O esquema é uma concatenação dos dois.

   1. No serviço Data Catalog, navegue até o bucket moviestream_landing e altere o ativo para landing e gold, respectivamente.

      Antes da alteração:

      Após a alteração:

   2. Execute outra sincronização.