Diferencia entre revisiones de «Tablas Externas»

De Dos Ideas.
Saltar a: navegación, buscar
(→‎Reemplazo SQL*Loader)
 
(No se muestran 3 ediciones intermedias de 3 usuarios)
Línea 1: Línea 1:
 +
[[Category:Oracle]]
 
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.
 
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.
  
Línea 225: Línea 226:
  
 
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.
 
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.
 +
dhndfggf
  
 
==Data Warehouse==
 
==Data Warehouse==

Revisión actual del 15:01 30 sep 2011

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.

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. dhndfggf

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.

Ver también

Obtenido de «https://dosideas.com/wiki/index.php?title=Tablas_Externas&oldid=6385»