[26ai] DBMS_CLOUD — Capitulo Uno

El artículo también se encuentra publicado en Ingles English version.

En el artículo de hoy vamos a ver algunos beneficios que podemos tener en el día a día al utilizar DBMS_CLOUD en 26ai para nuestras tareas diarias, como puede ser tareas de respaldo de un datapump o guardar informes, ya que se conecta a un Object Storage de OCI.

Por defecto, no viene configurado en nuestra 26ai, con lo que el primer paso que debemos hacer es configurarlo para un usuario válido de nuestra tenancy.

Antes de meternos en faena, debemos asegurarnos que nuestra base de datos tiene DBMS_CLOUD configurado y listo para usarse. Dejo por aquí la nota de Oracle para la instalación en caso que no tengamos configurado ese punto:

How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

A nivel de OCI, debemos configurar para nuestro usuario un token o API Key ya que necesitamos definir una credencial porque nuestro Bucket es privado. No sería necesario definir la credencial si nuestro Bucket es público*.*

Token

El primer paso que vamos a realizar es generar el token de nuestro usuario en OCI. Para ello, tendremos que ir a la parte de configuración de nuestro usuario, justo en la parte superior derecha:

Una vez dentro, vamos a la pestaña Tokens and keys:

Ahí vamos a ver tres secciones: API Keys, Auth Tokens y Customer Secrets Keys.

Bien, la que nos interesa es la de Auth Tokens:

Ahí pulsamos en Generate token. Nos pedirá un nombre y pulsamos en Generate token:

La siguiente ventana no la debemos cerrar sin antes copiar el token.

Una vez que hayamos copiado el token, definimos la credential en nuestra base de datos usando el token que hemos generado en el punto anterior.

Configuración de la Credencial

Para generarlo, debemos usar DBMS_CLOUD.CREATE_CREDENTIAL.

Este procedimiento recibe los siguientes parámetros:

• CREDENTIAL_NAME: Nombre de la credencial que vamos a crear.

• USERNAME: Nombre del nuestro usuario, siempre debe seguir la estructura de “/”.

• Password: Valor del que hemos generado antes

SQL>EXEC  dbms_cloud.create_credential(
    credential_name => 'DBA_IN_THE_SHADOW',
    username        => '<IdentityDomain>/<usuario_tenancy>',
    password        => '<token>');

SQL> R
  1  select credential_name,
  2         username,
  3         enabled
  4  from   DBA_credentials
  5* order by credential_name

CREDENTIAL_NAME           USERNAME                                           ENABLED
------------------------- -------------------------------------------------- --------------------
DBA_IN_THE_SHADOW         <IdentityDomain>/<usuario_tenancy>       TRUE

Una manera rápida de verificar que funciona correctamente es listando el contenido que tenemos en nuestra Bucket. Para ello nos apoyaremos en la función DBMS_CLOUD.LIST_OBJECTS.

Esta función recibe dos parámetros, el primero sería el nombre de la credencial que hemos generado en el punto anterior, y el segundo sería la URL para acceder a nuestro Bucket.

La manera más fácil de obtener la url, es ir a un objecto de nuestro Bucket e ir a los detalles del mismo:

Ahí copiamos el valor de “URL path (URI)”.

Una vez que lo tenemos copiado, listamos vía SQL el contenido del Bucket:

SQL> 
Select object_name
  From Dbms_cloud.list_objects('DBA_IN_THE_SHADOW','<location_uri>'); 

OBJECT_NAME
--------------------------------------------------------------------------------
XXXXXXXX.zip
XXXXXXXX.zip
XXXXXXXX.zip

Perfecto, tenemos acceso.

Subida de ficheros

Generamos un fichero csv y lo subimos a nuestro Bucket.

Utilizaremos DBMS_CLOUD.PUT_OBJECTS para subir el fichero generado. Para utilizarlo, definiremos el directory apuntando al path donde se encuentra nuestro fichero.

SQL> ! cat generate.sql
set heading off
set feedback off
set echo off
set verify off
set termout off
set markup csv on
spool myfile.csv
Select trunc(dbms_random.value(1, 1000)), dbms_random.string('x', 10)     
 From dual  
connect by level <= 10000;
spool off

SQL> @generate.sql
SQL> ! ls -lac *csv
-rw-r--r-- 1 oracle oinstall 168924 Feb 18 12:42 myfile.csv

SQL> CREATE DIRECTORY OCI_HOME as '/home/oracle';
SQL> GRANT READ, WRITE ON DIRECTORY OCI_HOME TO PUBLIC;
SQL> begin
  dbms_cloud.put_object (
    credential_name => 'DBA_IN_THE_SHADOW',
    object_uri      => '<location_uri>/myfile.csv',
    directory_name  => 'OCI_HOME',
    file_name       => 'myfile.csv');
end;
/ 
SQL>
SQL> Select object_name
  From 
   dbms_cloud.list_objects('DBA_IN_THE_SHADOW','<location_uri>');  

OBJECT_NAME
--------------------------------------------------------------------------------
XXXXXXXX.zip
XXXXXXXX.zip
XXXXXXXX.zip
myfile.csv

Listo, ya tenemos el fichero en nuestro Bucket.

Una vez subido, podemos generar una tabla externa que lee ese fichero. Una manera rápida de definir este punto, es usando DBMS_CLOUD.CREATE_EXTERNAL_TABLE, que genera una tabla en nuestro schema que lee el fichero que le pasemos por parámetro.

SQL> 
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'EXT_MYFILE_CSV',
    credential_name => 'DBA_IN_THE_SHADOW',
    file_uri_list   => '<location_uri>/myfile.csv',
    format          => JSON_OBJECT(
                         'type' VALUE 'csv',
                         'delimiter' VALUE ',',
                         'quote' VALUE '"',
                         'skipheaders' VALUE '0'
                       ),
    column_list     => '
        id   NUMBER,
        code VARCHAR2(50)
    '
  );
END;
/

SQL> SELECT COUNT(*) FROM EXT_MYFILE_CSV;
1000
SQL>
 SELECT * FROM EXT_MYFILE_CSV FETCH FIRST 25 ROWS ONLY
        ID CODE
---------- --------------------------------------------------
       224 3LU82RWW27
       654 LASYRZWUUE
        17 UW34WB3O4Y
       764 5Z2I01ITAX
       132 V4XCFDS9GA
       131 6PRK6G0VTC
       650 NST09S6RY9
       133 R8IIPLJZ95
       477 WK8Y1XC5XK
       930 O574M5RJK2
       371 P8G6PF91A9
       214 WRUYMY4BWY
       273 YD5NG441MV
       706 DQYEBYNF5H
       699 J3T3RHNHU0
       744 VVXATGVW0N
       945 U55X9EATLH
       371 RHW44MZ1DQ
       595 B1RU6VN33B
       864 P02I2A9Z8S
       218 R02XSI9MLY
       980 M0CSWHE4G7
       396 UZYFEL7UKI
       619 DYKCFI912X
       881 2TZ0GEQ6MM

25 rows selected.

También podemos exportar la información de una consulta válida a nuestro Bucket usando DBMS_CLOUD.EXPORT_DATA.

Este procedimiento recibe los siguientes parámetros:

• CREDENTIAL_NAME: Nombre de la credencial que vamos a crear.

• URL: “URL path (URI)” más el nombre del fichero, no es necesario indicar la extensión, ya que eso lo indicamos en el parámetro Format.

• Format: Formato en que queremos guardar el spool de nuestra consulta, en nuestra caso es json.

• Query: SQL válida que queremos exportar.

SQL> 
BEGIN
      DBMS_CLOUD.EXPORT_DATA(  
        credential_name =>'DBA_IN_THE_SHADOW',
        file_uri_list =>'<location_uri>/config_system_parameter',
        format => json_object(  'type' VALUE 'json' ),
        query => 'SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER'
      );
   END;
  / 

SQL> Select object_name
  From 
   dbms_cloud.list_objects('DBA_IN_THE_SHADOW','<location_uri>');  

OBJECT_NAME
--------------------------------------------------------------------------------
XXXXXXXX.zip
XXXXXXXX.zip
XXXXXXXX.zip
config_system_parameter_1_1_1.json
myfile.csv

Y si necesitamos descargar ese fichero en una tabla, podríamos usar el procedimiento DBMS_CLOUD.COPY_DATA.

SQL> 
  Create table BACK_SYSTEM_PARAMETER ( 
    INFO_PARAMETER CLOB  CONSTRAINT INFO_PARAMETER_JSON CHECK ( INFO_PARAMETER IS JSON )
      );

Table created.

SQL>
begin
  DBMS_CLOUD.COPY_DATA (
    table_name      => 'BACK_SYSTEM_PARAMETER',
    credential_name => 'DBA_IN_THE_SHADOW',
    file_uri_list   => '<location_uri>/config_system_parameter_1_1_1.json'
     );
end;
/
PL/SQL procedure successfully completed.

Ahí he definido una tabla BACK_SYSTEM_PARAMETER con una única columna con el objecto de inyectar ahí el contenido del JSON. Una vez definida, ejecuto el procedimiento para descargar la información.

Si consultamos la tabla, vemos que se ha cargado correctamente la información:

SQL>
 select 
    JSON_VALUE(INFO_PARAMETER, '$.NAME') AS NAME, 
    JSON_VALUE(INFO_PARAMETER, '$.VALUE') AS VALUE 
   from BACK_SYSTEM_PARAMETER
 Where INSTR(UPPER(JSON_VALUE(INFO_PARAMETER, '$.NAME')),'OPTIMIZER') != 0
  FETCH FIRST 5 ROWS ONLY;

NAME                                     VALUE
---------------------------------------- ----------------------------------------
optimizer_ignore_hints                   FALSE
optimizer_secure_view_merging            TRUE
optimizer_use_pending_statistics         FALSE
optimizer_capture_sql_plan_baselines     FALSE
optimizer_use_sql_plan_baselines         TRUE

10 rows selected.

En el próximo artículo abordaremos el uso del Data Pump junto con el DBMS_CLOUD

Espero que os guste. ¡Nos vemos en el próximo artículo!