Consulta de datos externos con Data Catalog

Al sincronizar con los metadatos de Data Catalog, Autonomous Database crea automáticamente tablas externas para cada entidad lógica recogida por Data Catalog. Estas tablas externas se definen en esquemas de base de datos que se gestionan por completo mediante el proceso de sincronización de metadatos. Los usuarios pueden consultar datos inmediatamente sin tener que derivar manualmente el esquema (columnas y tipos de dato) para orígenes de datos externos y crear tablas externas manualmente.

La sincronización es dinámica y mantiene la instancia de Autonomous Database actualizada con respecto a los cambios en los datos subyacentes, lo que reduce los costos de administración, ya que mantiene automáticamente de cientos a miles de tablas. También permite que varias instancias de Autonomous Database compartan el mismo catálogo de datos, lo que reduce aún más los costos de gestión y proporciona un juego común de definiciones de negocio.

Las carpetas/cubos de Data Catalog son contenedores que se sincronizan con los esquemas de Autonomous Database. Las entidades lógicas de esas carpetas/cubos se asignan a las tablas externas de Autonomous Database. Estos esquemas y tablas externas se generan y mantienen automáticamente mediante el proceso de sincronización:

• Las carpetas/cubos se asignan a esquemas de base de datos que sólo tienen fines organizativos.
• La organización debe ser coherente con el lago de datos y minimizar la confusión al acceder a los datos a través de diferentes rutas.
• Data Catalog es la fuente de datos de las tablas incluidas en los esquemas. Los cambios realizados en Data Catalog actualizan las tablas del esquema durante una sincronización posterior.

Para utilizar esta capacidad, un administrador de Database Data Catalog inicia una conexión a una instancia de Data Catalog, selecciona los activos de datos y las entidades lógicas que se van a sincronizar y ejecuta la sincronización. El proceso de sincronización crea esquemas y tablas externas en función de los activos de datos recopilados y las entidades lógicas seleccionados de Data Catalog. Tan pronto como se crean las tablas externas, los analistas de datos pueden empezar a consultar sus datos sin tener que derivar manualmente el esquema para orígenes de datos externos y crear tablas externas.

Para realizar consultas con Data Catalog es necesario comprender los siguientes conceptos.

Catálogo de datos

Data Catalog recopila activos de datos que apuntan a los orígenes de datos del almacén de objetos que desea consultar con Autonomous Database. En Data Catalog, puede especificar cómo se organizan los datos durante la recopilación, soportando diferentes patrones de organización de archivos. Como parte del proceso de recogida de Data Catalog, puede seleccionar los cubos y archivos que desea gestionar dentro del activo. Para obtener más información, consulte Visión general de Data Catalog.

Almacenes de Objetos

Los almacenes de objetos tienen cubos que contienen una variedad de objetos. Algunos tipos comunes de objetos que se encuentran en estos cubos incluyen: archivos CSV, parquet, avro, json y ORC. Los cubos generalmente tienen una estructura o un patrón de diseño para los objetos que contienen. Hay muchas formas diferentes de estructurar los datos y muchas formas diferentes de interpretar estos patrones.

Por ejemplo, un patrón de diseño típico utiliza carpetas de nivel superior que representan tablas. Los archivos de una carpeta determinada comparten el mismo esquema y contienen datos para esa tabla. Las subcarpetas se utilizan a menudo para representar particiones de tabla (por ejemplo, una subcarpeta para cada día). Data Catalog hace referencia a cada carpeta de nivel superior como entidad lógica y esta entidad lógica se asigna a una tabla externa de Autonomous Database.

Conexión

Una conexión es una conexión de Autonomous Database a una instancia de Data Catalog. Para cada instancia de Autonomous Database, puede haber conexiones a varias instancias de Data Catalog. La credencial de Autonomous Database debe tener derechos para acceder a los activos de Data Catalog que se han recopilado desde Object Storage.

Recogida

Proceso de Data Catalog que explora el almacenamiento de objetos y genera las entidades lógicas a partir de los juegos de datos.

Activo de datos

Un activo de datos de Data Catalog representa un origen de datos, que incluye bases de datos, Oracle Object Storage y Kafka, entre otros. Autonomous Database aprovecha los activos de Oracle Object Storage para la sincronización de metadatos.

Entidad de datos

Una entidad a los datos de Data Catalog es una recopilación de los datos, como una tabla o vista, o un único archivo, y normalmente tiene muchos atributos que describen sus datos.

Entidad lógica

En los lagos de datos, muchos archivos suelen constar de una sola entidad lógica. Por ejemplo, puede tener archivos de flujo de clics diarios y estos archivos comparten el mismo esquema y tipo de archivo.

Una entidad lógica de Data Catalog es un grupo de archivos de Object Storage que se derivan durante la recogida mediante la aplicación de patrones de nombre de archivo que se han creado y asignado a un activo de datos.

Objeto de Datos

Un objeto de datos en Data Catalog hace referencia a activos de datos y entidades de datos.

Patrón de Nombre de archivo

En un lago de datos, los datos se pueden organizar de diferentes formas. Normalmente, las carpetas capturan archivos del mismo esquema y tipo. Debe registrarse en Data Catalog como se organizan los datos. Los patrones de nombre de archivo se utilizan para identificar cómo se organizan los datos. En Data Catalog, puede definir patrones de nombre de archivo mediante expresiones regulares. Cuando Data Catalog recopila un activo de datos con un patrón de nombre de archivo asignado, las entidades lógicas se crean en función del patrón de nombre de archivo. Al definir y asignar estos patrones a los activos de datos, se pueden agrupar varios archivos como entidades lógicas en función del patrón de nombre de archivo.

Sincronizar (Sincronizar)

Autonomous Database realiza sincronizaciones con Data Catalog para mantener automáticamente su base de datos actualizada con respecto a los cambios en los datos subyacentes. La sincronización se puede realizar manualmente o según un programa.

El proceso de sincronización crea esquemas y tablas externas en función de los activos de datos y las entidades lógicas de Data Catalog. Estos esquemas están protegidos, lo que significa que sus metadatos los gestiona Data Catalog. Si desea modificar los metadatos, debe realizar los cambios en Data Catalog. Los esquemas de Autonomous Database reflejarán los cambios una vez que se ejecute la siguiente sincronización. Para obtener más información, consulte Asignación de sincronización.

El proceso de sincronización crea y actualiza esquemas y tablas externas de Autonomous Database en función de los activos de datos de Data Catalog, las carpetas, las entidades lógicas, los atributos y las sustituciones personalizadas relevantes.

Hay un flujo de trabajo típico de acciones realizadas por los usuarios que desean consultar con Data Catalog.

select * from all_dcat_connections;

En este escenario, Moviestream está capturando datos en una zona de llegada del almacenamiento de objetos. Gran parte de estos datos, pero no necesariamente todos, se utilizan para alimentar una instancia de Autonomous Database. Antes de alimentar Autonomous Database, los datos se transforman, limpian y, posteriormente, se almacenan en el área de "oro".

1. Object Store - Revisar cubos, carpetas y archivos
   1. Revise los cubos del almacén de objetos.
      Por ejemplo, a continuación se muestran los cubos de llegada (moviestream_landing) y zona dorada (moviestream_gold) en Object Storage:
   2. Revise las carpetas y los archivos de los cubos del almacén de objetos.
      Por ejemplo, a continuación se muestran las carpetas del cubo de llegada (moviestream_landing) en Object Storage:
2. Data Catalog: creación de patrones de nombre de archivo
   1. Informe a Data Catalog cómo se organizan los datos mediante patrones de nombre de archivo. Se trata de expresiones regulares que se utilizan para clasificar archivos. Los patrones de nombre de archivo los utiliza el recopilador de Data Catalog para derivar entidades lógicas. Los dos siguientes patrones de nombre de archivo se utilizan para recoger los cubos en el ejemplo MovieStream. Consulte Recopilación de archivos de Object Storage como entidades de datos lógicas para obtener más información sobre la creación de patrones de nombre de archivo.

      Estilo Hive	Estilo de carpeta
      {bucketName:.*}/{logicalEntity:[^/]+}.db/{logicalEntity:[^/]+}/.*	{bucketName:[\w]+}/{logicalEntity:[^/]+}(?<!.db)/.*$
      Crea entidades lógicas para los orígenes que contienen ".db" como la primera parte del nombre del objeto. Para garantizar la unicidad en el cubo, el nombre resultante es (db-name).(nombre de la carpeta)	Crea una entidad lógica basada en el nombre de carpeta de la raíz Para evitar la duplicación con Hive, se omiten los nombres de objetos que tienen ".db".

   2. Para crear patrones de nombre de archivo, vaya al separador Patrones de nombre de archivo de Data Catalog y haga clic en Crear patrón de nombre de archivo. Por ejemplo, el siguiente es el separador Crear patrón de nombre de archivo para el catálogo de datos moviestream:
3. Catálogo de datos - Creación de activos de datos
   1. Cree un activo de datos que se utilice para recopilar datos del almacén de objetos.
      Por ejemplo, se crea un activo de datos denominado phoenixObjStore en el catálogo de datos moviestream:
   2. Agregue una conexión al activo de datos.
      En este ejemplo, el activo de datos se conecta al compartimento para el recurso de almacenamiento de objetos moviestream.
   3. Ahora, asocie sus patrones de nombre de archivo con su activo de datos. Seleccione Asignar patrones de nombre de archivo, compruebe los patrones que desee y haga clic en Asignar.
      Por ejemplo, estos son los patrones asignados al activo de datos phoenixObjStore:
      Descripción de la ilustración assign_filename_patterns_v1.png
4. Data Catalog: recopilación de datos del almacén de objetos
   1. Recopile el activo de datos de Data Catalog. Seleccione los cubos del almacén de objetos que contienen los datos de origen.
      En este ejemplo, los cubos moviestream_gold y moviestream_landing del almacén de objeto están seleccionados para la recogida.
   2. Después de ejecutar el trabajo, verá las entidades lógicas. Utilice Examinar activos de datos para revisarlos.
      En este ejemplo, está viendo la entidad lógica customer-extension y sus atributos.

      Si tiene un glosario, Data Catalog recomienda categorías y términos para asociarlos a la entidad y sus atributos. Esto proporciona un contexto de negocio para los elementos. Los esquemas, las tablas y las columnas a menudo no se explican por sí solos.

      En nuestro ejemplo, queremos diferenciar entre los diferentes tipos de cubos y el significado de su contenido:

      • ¿Qué es una zona de aterrizaje?
      • ¿Qué tan precisos son los datos?
      • ¿Cuándo se actualizó por última vez?
      • Definición de una entidad lógica o su atributo
5. Autonomous Database: conexión a Data Catalog

   Conecte Autonomous Database a Data Catalog. Debe asegurarse de que la credencial utilizada para realizar esa conexión utiliza un principal de OCI que está autorizado para acceder al activo de Data Catalog. Para obtener más información, consulte Políticas de Data Catalog y Acceso a recursos en la nube mediante la configuración de políticas y roles.

   1. Conectar a 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. Sincronizar Data Catalog con Autonomous Database. Aquí sincronizaremos todos los activos de almacenamiento 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: ahora comienza a ejecutar consultas en el almacén de objetos.

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

6. Cambiar esquemas para objetos

   Los nombres de esquema por defecto son bastante complicados. Vamos a simplificarlos especificando tanto el activo como el atributo personalizado Oracle-Db-Schema de la carpeta en Data Catalog. Cambie el activo de datos a PHX y las carpetas a landing y gold respectivamente. El esquema es una concatenación de los dos.

   1. En Data Catalog, acceda al cubo moviestream_landing y cambie el activo a landing y gold respectivamente.

      Antes de los cambios:

      Tras el cambio:

   2. Ejecute otra sincronización.

En este escenario se muestra cómo crear tablas externas en Autonomous Database basadas en entidades lógicas de Data Catalog recopiladas a partir de datos particionados en Object Store.

1. Object Store - Revisar cubos, carpetas y archivos
   1. Revise los cubos del almacén de objetos.
      Por ejemplo, a continuación se muestran los cubos de llegada (moviestream_landing) y zona dorada (moviestream_gold) en Object Storage:
   2. Revise las carpetas y los archivos de los cubos del almacén de objetos.
      Por ejemplo, a continuación se muestran las carpetas del cubo de llegada (moviestream_landing) en Object Storage:
2. Data Catalog: creación de patrones de nombre de archivo
   1. Informe a Data Catalog cómo se organizan los datos mediante patrones de nombre de archivo. Se trata de prefijos de carpeta o expresiones regulares que se utilizan para clasificar archivos. Los patrones de nombre de archivo los utiliza el recopilador de Data Catalog para derivar entidades lógicas. Cuando se especifica un prefijo de carpeta, Data Catalog genera automáticamente entidades lógicas a partir del prefijo de carpeta especificado en el almacén de objetos. El siguiente patrón de nombre de archivo se utiliza para recoger los cubos en el ejemplo MovieStream. Consulte Recopilación de archivos de Object Storage como entidades de datos lógicas para obtener más información sobre la creación de patrones de nombre de archivo.

      Prefijo de carpeta	Descripción
      workshop.db/	Crea entidades lógicas para orígenes que contienen la ruta de acceso "workshop.db" en el almacén de objetos.

   2. Para crear patrones de nombre de archivo, vaya al separador Patrones de nombre de archivo de Data Catalog y haga clic en Crear patrón de nombre de archivo. Por ejemplo, el siguiente es el separador Crear patrón de nombre de archivo para el catálogo de datos moviestream:
3. Catálogo de datos - Creación de activos de datos
   1. Cree un activo de datos que se utilice para recopilar datos del almacén de objetos.
      Por ejemplo, se crea un activo de datos denominado amsterdamObjStore en el catálogo de datos moviestream:
   2. Agregue una conexión al activo de datos.
      En este ejemplo, el activo de datos se conecta al compartimento para el recurso de almacenamiento de objetos moviestream.
   3. Ahora, asocie sus patrones de nombre de archivo con su activo de datos. Seleccione Asignar patrones de nombre de archivo, compruebe los patrones que desee y haga clic en Asignar.
      Por ejemplo, estos son los patrones asignados al activo de datos amsterdamObjStore:
4. Data Catalog: recopilación de datos del almacén de objetos
   1. Recopile el activo de datos de Data Catalog. Seleccione los cubos del almacén de objetos que contienen los datos de origen.
      En este ejemplo, los cubos moviestream_gold y moviestream_landing del almacén de objeto están seleccionados para la recogida.
   2. Después de ejecutar el trabajo, verá las entidades lógicas. Utilice Examinar activos de datos para revisarlos.
      En este ejemplo, está viendo la entidad lógica sales_sample_parquet y sus atributos. Tenga en cuenta que Data Catalog ha identificado el atributo month como particionado.
5. Autonomous Database: conexión a Data Catalog

   Conecte Autonomous Database a Data Catalog. Debe asegurarse de que la credencial utilizada para realizar esa conexión utiliza un principal de OCI que está autorizado para acceder al activo de Data Catalog. Para obtener más información, consulte Políticas de Data Catalog y Acceso a recursos en la nube mediante la configuración de políticas y roles.

   1. Conectar a 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. Sincronizar Data Catalog con Autonomous Database. Aquí sincronizaremos todos los activos de almacenamiento 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: ahora comienza a ejecutar consultas en el almacén 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. Cambiar esquemas para objetos

   Los nombres de esquema por defecto son bastante complicados. Vamos a simplificarlos especificando tanto el activo como el atributo personalizado Oracle-Db-Schema de la carpeta en Data Catalog. Cambie el activo de datos a PHX y las carpetas a landing y gold respectivamente. El esquema es una concatenación de los dos.

   1. En Data Catalog, acceda al cubo moviestream_landing y cambie el activo a landing y gold respectivamente.

      Antes de los cambios:

      Tras el cambio:

   2. Ejecute otra sincronización.