Oracle External Tables

Level200

Imagina que llegas a una empresa donde cada mañana llegan cientos de archivos CSV u otro formato provenientes de sistemas externos, aplicaciones legadas, proveedores y plataformas en la nube.

Durante años el proceso fue siempre el mismo: recibir el archivo, cargarlo a una tabla temporal, validar los datos, ejecutar transformaciones y finalmente poner la información a disposición de los usuarios. Un ciclo repetitivo que consumía tiempo, almacenamiento y recursos.

Un día surge una pregunta y si pudiéramos consultar el archivo directamente sin cargarlo a la base de datos?..si es posible!!!, Oracle tiene las tablas externas, para el usuario la experiencia es completamente transparente, ejecuta sentencias SQL, aplica filtros, ordena, hace joins...como una tabla normal.

Veamos como creamos la tabla external:

1. Definimos una ruta de sistema operativo y copiamos el archivo que queremos ver mediante una tabla extern.

** el archivo para este caso es clientes.csv, este es parte de su contenido.

1. Creamos el objeto directory a nivel de oracle apuntando a la ruta definida en el sistema operativo.

   

2. Otorgamos permisos al usuario ace sobre ese directorio:

   

3. Ahora viene el punto mas importante que es crear la tabla externa.

CREATE TABLE clientes_ext

( id NUMBER,

nombre VARCHAR2(100),

ciudad VARCHAR2(100)

) ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER DEFAULT DIRECTORY ace

ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ('clientes.csv') )

REJECT LIMIT UNLIMITED;

Aca hay varios parametros:

TYPE: puede tener los valores de ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HIVE y ORACLE_BIGDATA, este ultimo permite acceder a datos alamacenados en object store de cloud providers como S3 de AWS.

DEFAULT DIRECTORY: especifica el directorio a usar para los archivos tanto de entrada como de salida.

LOCATION: especifica el archivo a usar para la tabla externa.

1. Realizamos el select.

1. Aplicamos filtros.

1. Ejecutamos agregaciones.

Como vemos la tabla externa a este punto se comporta como una tabla normal, la salvedad aca es que es solo de lectura.

Y que sucede internamente?

Oracle ubica el archivo en el directorio, lee el archivo, interpreta el formato definido, convierte los datos a columnas de Oracle y finalmente devuelve el resultado al usuario.

**Si queremos ver la tablas externas que existen en nuestra base de datos ejecutamos:

SELECT table_name, type_name, default_directory_name FROM dba_external_tables;

Y si deseamos ver los archivos asociados:

SELECT *

FROM dba_external_locations;

Casos de uso

Una de las mayores fortalezas de las External Tables es que permiten acceder a información externa utilizando SQL estándar, eliminando la necesidad de desarrollar procesos de carga para ciertos escenarios, entre los casos de usos podemos mencionar:

• Integración de archivos provenientes de sistemas externos.

• Carga masiva de datos.

• Migración de base de datos.

• Auditoria e investigación de incidentes.

• Intercambio de información entre organizaciones.

Limitaciones:

• Son solo de lectura

• No almacenan datos dentro de la base de datos.

• Dependencia del sistema operativo o del respositorio donde esta el archivo.

• No permiten triggers.

• No son adecuadas para transacciones.

• Soporta formato csv, texto, parquet, avro, aún no soporta formatos mas modernos como delta Lake.

Conclusion:

Las tablas externas representan una de las formas más elegantes de integración en Oracle, permitiendo consultar archivos externos con SQL estándar como si fueran tablas tradicionales. Aunque presentan limitaciones como ser de solo lectura y depender de archivos del sistema operativo, son una excelente alternativa para procesos de carga, validación, migración e intercambio de información, ofreciendo un acceso transparente a datos que nunca llegan a almacenarse dentro de la base de datos.