Tablas Externas
Las tablas externas de [[[Oracle]]] permiten consultar datos de un archivo plano como si fuera una tabla dentro de la Base De Datos Oracle. Vamos a ver a continuación una breve introducción al tema.
En [Oracle9i], solo se pueden realizar operaciones de lectura, pero en [Oracle10g], ya se pueden escribir datos a una tabla externa, aunque no se puede escribir a un archivo existente.
Las external tables son un reemplazo natural del sql*loader en su función de lectura de archivos plano.
Contenido
Ejemplo
Supongamos el archivo D:\ejemplo\external_table\empleados.txt
001,Perez,Carlos,21446992, cperez@dosideas.com 002,Garcia,Alicia,19436951, agarcia@dosideas.com 003,Lopez,Pablo,11446663, plopez@dosideas.com 004,Gomez,Valeria,22222222, vgomez@dosideas.com 005,Gonzalez,Diego,16446987, dgonzalez@dosideas.com 006,Torres,Guillermo,23444999, gtorres@dosideas.com
Creación de la tabla externa
connect system/password
create or replace directory extdata_dir as ‘D:\ejemplo\external_table’; grant read, write on directory extdata_dir to cbarbiero;
connect usuario/password
CREATE TABLE empleados_ext
( emp_id number(3),
apellido varchar2(30),
nombre varchar2(30),
documento number,
mail varchar2(50)
)
Organization external
(
default directory extdata_dir
access parameters (records delimited by newline
fields terminated by ‘,’)
location (‘empleados.txt’)
);
Query de ejemplo
SQL> select * from empleados_ext;
EMP_ID APELLIDO NOMBRE DOCUMENTO MAIL
--------- ---------- ---------- ---------- ------------------------------
1 Perez Carlos 21446992 cperez@dosideas.com
2 Garcia Alicia 19436951 agarcia@dosideas.com
3 Lopez Pablo 11446663 plopez@dosideas.com
4 Gomez Valeria 22222222 vgomez@dosideas.com
5 Gonzalez Diego 16446987 dgonzalez@dosideas.com
6 Torres Guillermo 23444999 gtorres@dosideas.com
6 rows selected.
Sintaxis
CREATE TABLE tabla
( columna1 ....,
columna2 ....,
..........
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY directorio
ACCESS PARAMETERS (records delimited by newline
fields terminated by ','
[NOBADFILE | BADFILE directorio:'archivo.bad']
[NODISCARDFILE | DISCARDFILE directorio:'archivo.dis']
[NOLOGFILE | LOGFILE directorio:'archivo.log'])
LOCATION ([directorio]:'archivos,...')
);
Las posibilidades de definición en ACCESS PARAMETERS son mucho más amplias que las cubiertas en esta nota. Para una referencia completa ir al manual Oracle9i Database Utilities.
Reglas y Restricciones
Si se puede
Ya que los datos de una tabla externa, residen en el sistema operativo, necesitamos que el/los archivos asociados a la misma estén unicados en un lugar donde Oracle tenga acceso.
Por lo tanto el primer paso es crear un directorio dentro de la base asociado a un directorio en el sistema operativo y dar permisos de acceso al mismo.
create or replace directory extdata_dir as ‘D:\ejemplo\external_table’; grant read, write on directory extdata_dir to cbarbiero;
El usuario de S.O. dueño de los binarios de Oracle, debe tener acceso al directorio del S.O. y este directorio no puede ser un link simbólico.
Hay que poner el archivo plano que contiene los datos en este directorio.
D:\ejemplo\external_table\empleados.txt 001,Perez,Carlos,21446992, cperez@dosideas.com 002,Garcia,Alicia,19436951, agarcia@dosideas.com 003,Lopez,Pablo,11446663, plopez@dosideas.com 004,Gomez,Valeria,22222222, vgomez@dosideas.com 005,Gonzalez,Diego,16446987, dgonzalez@dosideas.com 006,Torres,Guillermo,23444999, gtorres@dosideas.com
El siguiente paso es crear la tabla. La sentencia CREATE TABLE de una tabla externa tiene 2 partes.
La primer parte, como para una tabla normal, tiene el nombre de la tabla y la especificación de las columnas.
Esta es seguida por un bloque de sintánxis específica para las tablas externas, la cual le indica a Oracle como interpretar los datos del archivo externo.
CREATE TABLE empleados_ext
( emp_id number(3),
apellido varchar2(30),
nombre varchar2(30),
documento number,
mail varchar2(50)
)
Organization external
(
default directory extdata_dir
access parameters (records delimited by newline
fields terminated by ‘,’)
location (‘empleados.txt’)
);
Con la creación de la tabla, Oracle no intenta cargar los datos, ni siquiera intenta chequear la validez de los valores de los parámetros especificados. La sentencia CREATE TABLE será exitosa aunque el archivo todavía no exista en el directorio.
Con la sentencia de CREATE sólo hemos creado un metadata en el diccionario de datos que instruye a Oracle cómo debe el driver ORACLE_LOADER parsear los datos del archivo.
Recién se valida la información y se accede al archivo en el momento de consultar la tabla.
SQL> select * from empleados_ext;
EMP_ID APELLIDO NOMBRE DOCUMENTO MAIL
--------- ---------- ---------- ---------- ------------------------------
1 Perez Carlos 21446992 cperez@dosideas.com
2 Garcia Alicia 19436951 agarcia@dosideas.com
3 Lopez Pablo 11446663 plopez@dosideas.com
4 Gomez Valeria 22222222 vgomez@dosideas.com
5 Gonzalez Diego 16446987 dgonzalez@dosideas.com
6 Torres Guillermo 23444999 gtorres@dosideas.com
6 rows selected.
SQL> select * from empleados_ext where apellido like ‘G%’;
EMP_ID APELLIDO NOMBRE DOCUMENTO MAIL
--------- ---------- ---------- ---------- ------------------------------
2 Garcia Alicia 19436951 agarcia@dosideas.com
4 Gomez Valeria 22222222 vgomez@dosideas.com
5 Gonzalez Diego 16446987 dgonzalez@dosideas.com
3 rows selected.
Oracle usa el dirver ORACLE_LOADER para procesar el archivo, y como la herramienta SQL*Loader, crea un archivo de log en el directorio definido como default al crear la tabla. El nombre usado por default es <nombre de tabla>_ospid.log
D:\ejemplo\external_table\empleados_ext_452_2828.txt
LOG file opened at 12/27/06 16:17:19
Field Definitions for table EMPLEADOS_EXT Record format DELIMITED BY NEWLINE Data in file has same endianness as the platform Rows with all null fields are accepted
Fields in Data Source:
EMP_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
APELLIDO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
NOMBRE CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DOCUMENTO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MAIL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
Lo mismo sucede con los archivos “bad” y “discard” en el caso de que necesiten generarse.
Si se desea se puede configurar directorios separados y nombres específicos para cada uno de estos archivos. Lo que debe recordarse es que debemos tener permiso de READ para el directorio donde están los datos y permisos de WRITE donde se generan los archivos de losg, bad y discard.
Se puede usar un ALTER TABLE para modificar los parámetros sin necesidad de borrar y redefinir toda la tabla.
connect system/password
create or replace directory extlog_dir as ‘D:\ejemplo\external_table\log’; grant write on directory extlog_dir to cbarbiero;
connect cbarbiero/password
ALTER TABLE empleados_ext
( access parameters (records delimited by newline
logfile extlog_dir:’empleados_ext.log’
badfile extlog_dir:’empleados_ext.bad’
discardfile extlog_dir:’empleados_ext.dis’
fields terminated by ‘,’ );
Tambien podríamos setear que la tabla no genere ninguno de estos archivos y muchas otras opciones de configuración mas, que pueden consultarlas en el manual Oracle 9i Utilities.
No se puede
Aunque las external tables pueden ser consultadas, no pueden ser usadas como una tabla regular de Oracle.
Las tablas externas son read-only. No se puede realizar ninguna operación de DML ni crear índices sobre una tabla externa.
Otra cosa que debemos considerar es no terminar el ultimo registro del archivo de datos con un newline, ya que seguramente esto generará un error.
Casos prácticos de uso
El principal uso de las external tables es como medio/herramienta para cargar datos dentro de una tabla real en la base de datos. Luego de crear una tabla externa, se puede hacer:
CREATE TABLE <tabla real> AS SELECT … <tabla externa> …
INSERT INTO <tabla real> AS SELECT … <tabla externa> …
Cuando la tabla externa es accedida por una sentencia SQL, los campos de la tabla externa pueden usarse como cualquier campo de una tabla normal. En particular, los campos pueden ser usados como argumentos de cualquier función SQL, función PL/SQL o función java.
Esto permite una manipulación más amplia de los datos de la fuente externa en el momento de carga, si lo comparamos con la herramienta SQL*Loader.
También podemos hacer joins contra los datos de una tabla externa.
Y muy importante en los casos de carga masiva y volúmenes importantes, podemos paralelizar el proceso de carga creando la tabla externa con el degree de paralelismo deseado.
Reemplazo SQL*Loader
En la mayoría de las implementaciones podemos reemplazar el uso del SQL*Loader con tablas externas, evitando de esta manera la necesidad de crear una tabla de interface para cargar los datos, ahorrando espacio dentro de la base de datos.
Data Warehouse
Las tablas externas son interesantes en un ambiente data warehouse durante el proceso ETL (Extraction Transformation Loading). Evita la necesidad de crear tablas temporales a ser creadas durante las etapas de Extracción y Transformación, reduciendo el espacio alocado y el riesgo de aborto durante todo el proceso.
