Las bases de datos Oracle ofrecen diferentes opciones para la implementación de una solución a requerimientos que tienen los sistemas. Por ejemplo, para acceder a información de fuentes externas al motor de la base de datos se puede utilizar el utilitario SQL*Loader o la característica de tablas externas, con esta última las fuentes de datos se ven como otras tablas.
Dependiendo de las necesidades del requerimiento, el uso de una u otra opción vendría a ser la solución. En el caso del SQL*Loader se tiene la posibilidad de crear índices sobre la tabla donde son cargados los archivos o si los datos son cargados de múltiples archivos, se generará un archivo de rechazadas y filas malas por cada uno de ellos.
A pesar de que las tablas externas están incluidas como parte de la base de datos desde versiones anteriores, antes de la versión 10g eran únicamente de solo lectura. Ahora con la capacidad de que sean de escritura, tienen ventajas y diferentes usos en relación con el SQL*Loader.
Con la salida de la versión 12c de la base de datos, se vuelven a incluir nuevas características, que permiten simplificar la definición de este tipo de objeto. Las más importantes son:
- Campos csv: indica que el archivo contiene valores separados por comas
- Formato de fecha a nivel de registro: permite indicar el formato común que tendrán todos los campos tipo fecha en el archivo, sin necesita de indicar uno por uno
- Nombre de campos: permite definir un encabezado en el archivo para mapear los campos a las columnas en un orden diferente al de la tabla
- Partición de los datos: en 12c R2 se podrá particionar los datos contenidos en la tabla externa, tomando las mismas ventajas de las tablas normales particionadas
A continuación, vamos a presentar un ejemplo para cada una de las tres primeras características.
Formato de fecha
Antes de la versión 12c, cada campo tipo fecha que viene en el archivo debería de indicar el formato a utilizar. Si venían muchos campos y todos con el mismo formato, se repetía la misma información. Con la nueva característica tenemos lo siguiente:
CREATE TABLE customer_file (
customer_id INTEGER,
customer_name VARCHAR2(20),
created_date DATE
) ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY test_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELD NAMES ALL FILES
FIELDS csv WITH ENBEDDED TERMINATED by ‘;’
ALL FIELDS OVERRIDE
DATE_FORMAT DATE MASK ‘DD-MON-YYYY’
)
LOCATION (‘customers.dat’)
);
Nombre de campos
Cuando se crea la tabla externa se puede indicar la cantidad de registros que deben ser saltados en el archivo usando la cláusula “skip N”. Esto permitirá especificar en la primera línea los nombres de las columnas de la tabla cuando es diferente al orden de cómo fue creada la tabla.
Por ejemplo, se podría tener el siguiente contenido de un archivo:
CUSTOMER_ID;CUSTOMER_NAME;CREATED_DATE
1;Big Customer;1-JAN-2018
2;Small Customer;1-JAN-2018
Formato csv
Permite simplificar la definición la definición de la tabla externa a la hora de leer archivos que tienen separados por punto y coma. Esta cláusula también se puede utilizar con otros delimitadores, simplemente agregando “terminated by” al final.
create table toys_csv (
toy_name varchar2(10),
weight number,
colour varchar2(10)
) organization external (
default directory ext_files
location ( ‘toys.csv’ )
);
También puede ver otro ejemplo en el código de la primera característica expuesta.
En conclusión, las tablas externas son excelentes para cargar archivos utilizando sentencias SQL, aunque en ocasiones es posible que necesite hacer ajustes a los datos antes de guardarlos en sus tablas reales, o necesita leer el mismo registro muchas veces durante la carga.
Al hacer esto, es útil organizar los datos en un área de trabajo privada, en algún lugar se pueden almacenar las filas antes de ser cargadas para uso del resto de los usuarios.
Para más información de las tablas externas en la versión 12c de la base de datos, pueden consultar la documentación de Oracle: