« Problema de la vida real : No se puede montar un Diskgroup en una instancia ASMOracle11gr2 : Claúsula IGNORE_ROW_ON_DUPKEY_INDEX , para evitar los errores ORA-0001 »

Oracle 11gr2 : Claúsula PREPROCESSOR para tablas externas



En Oracle11gr2 existen las tablas externas al igual que en anteriores versiones de Oracle.

Pero en la última versión de Oracle vienen con una pequeña claúsula, la cual las hace más novedoss y poderosas..



Imaginense este escenario.

a) Hay un archivo plano y necesito poder calcular la cantidad total de dinero que trae una columna X en ese archivo (tiene 400 mil filas).
Para solucionar lo anterior simplemente creo una tabla externo y ocupo las funciones por todos sabidas (SUM).

b) Hay un archivo plano , y necesito calcular el mismo valor sobre una columna de cantidad , pero ... el gran detalle es que ese archivo plano comprimido pesa 800MB, descomprimido, puede llegar sin problemas a las 250GB, ¿qué hago? , ¿lo descomprimo y hago split? |-|

Pues para lo anterior en Oracle11gr2 , nace una nueva claúsula en las tablas externas, está claúsula llamada PREPROCESSOR permite ejecutar un comando sobre el archivo y el resultado poder ser manipulado con la tabla externa, en otras palabras, puedo DESCOMPRIMIR EL ARCHIVO ANTES DE UTILIZARLO con la tabla externa :>>

He aquí un ejemplo :

1) Creo un directorio de trabajo

SQL> CREATE OR REPLACE DIRECTORY dir_externos as '/home/oracle/externo';


2) Otorgo grant de lectura y escritura (el caso sólo es pedagógico, nunca debiesen darle privilegios a PUBLIC)

SQL> grant read , write on directory dir_externos to public;

Grant succeeded.


3) Creo una tabla externa común y silvestre

SQL> create table externos1 (
2 campo1 number(10) ,
3 campo2 varchar2(10)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory dir_externos
9 ACCESS PARAMETERS
10 (
11 records delimited by newline
12 badfile dir_externos:'externos.bad'
13 logfile dir_externos:'externos.log'
14 fields terminated by ','
15 (campo1,campo2)
16 )
17 LOCATION ('archivo_sin_comprimir.txt')
18 )
19 PARALLEL
20 REJECT LIMIT UNLIMITED;

Table created.


4) Consulto el archivo plano mediante sistema operativo

[oracle@oracle11g externo]$ more archivo_sin_comprimir.txt
1,A
2,3
10000,AAAAA
1202020,HHH1111


5) Consulto el archivo plano mediante la tabla externa

SQL> select * from externos1;

CAMPO1 CAMPO2
---------- ----------
1 A
2 3
10000 AAAAA
1202020 HHH1111


6) Comprimo el archivo plano que está en disco

[oracle@oracle11g externo]$ gzip archivo_sin_comprimir.txt
[oracle@oracle11g externo]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 76 Sep 10 15:27 archivo_sin_comprimir.txt.gz


7) Creo otra tabla externa, pero esta vez apuntando al archivo plano comprimido

SQL> create table externos2 (
2 campo1 number(10) ,
3 campo2 varchar2(10)
)
4 5 organization external
6 (
type oracle_loader
7 8 default directory dir_externos
9 ACCESS PARAMETERS
10 (
11 records delimited by newline
12 badfile dir_externos:'externos2.bad'
13 logfile dir_externos:'externos2.log'
14 fields terminated by ','
15 (campo1,campo2)
16 )
17 LOCATION ('archivo_sin_comprimir.txt.gz')
18 )
19 PARALLEL
20 REJECT LIMIT UNLIMITED;


8) Y cuando consulto la tabla, no aparecen registros ¿por qué?

SQL> select * from externos2;

no rows selected

SQL>


9) Si busco el contenido del archivo de BAD del archivo plano, nos daremos cuenta que trato de leer un registro desde el archivo comprimido, pero no pudo pues no era el formato que se le había declarado al momento de crear la tabla externa

[oracle@oracle11g externo]$ more externos2.bad
#S©J
[oracle@oracle11g externo]$



10) Si vemos el log del proceso, claramente veremos el problema que hubo

[oracle@oracle11g externo]$ more externos2.log
LOG file opened at 09/10/09 15:28:29

Field Definitions for table EXTERNOS2
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:

CAMPO1 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CAMPO2 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader

LOG file opened at 09/10/09 15:28:30

Field Definitions for table EXTERNOS2
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:

CAMPO1 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CAMPO2 CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
error processing column CAMPO1 in row 1 for datafile /home/oracle/externo/archivo_sin_comprimir.txt.gz
ORA-01722: invalid number


11) Procedemos a crear otro directorio, este directorio es la ruta donde se encuentra el utilitario para descomprimir, que para nuestro caso es gunzip

SQL> CREATE OR REPLACE DIRECTORY exec_dir_externos as '/usr/bin/';
SQL> grant read , write on directory exec_dir_externos to public;


12)Procedemos a generar una tabla externa, pero con la claúsula PREPROCESSES , para que el archivo con extensión .gz sea descomprimido

create table externos_comprimidos (
campo1 number(10) ,
campo2 varchar2(10)
)
organization external
(
type oracle_loader
default directory dir_externos
ACCESS PARAMETERS
(
records delimited by newline
PREPROCESSOR exec_dir_externos:'gunzip'
badfile dir_externos:'externos2.bad'
logfile dir_externos:'externos2.log'
fields terminated by ','
(campo1,campo2)
)
LOCATION ('archivo_sin_comprimir.txt.gz')
)
PARALLEL
REJECT LIMIT UNLIMITED;


13) Consultamos la tabla externa recientemente creada y vemos como tenemos acceso al archivo plano aunque este comprimido

SQL> select * from externos_comprimidos;

CAMPO1 CAMPO2
---------- ----------
1 A
2 3
10000 AAAAA
1202020 HHH1111


14)No disponible para bases de datos con Oracle Database Vault instalado

SQL> r
1* select * from v$option where upper(parameter) like '%VAUL%' order by 1

PARAMETER VALUE
--------------------- ---------
Oracle Database Vault TRUE

SQL>



Referencias
Tablas externas en Oracle11gr2

Espero les sirva


by Ligarius
10.09.09. 18:50:47. 843 words, 17364 views. Categories: Base de datos, Oracle11gR2 ,