Administer External Tables

1. Documentación en Tahiti -> Masters Book List -> Administrator’s Guide -> 20 Managing Tables -> Managing External Tables

Documentación en Tahiti -> Masters Book List -> Utilities -> 15 The ORACLE_DATAPUMP Access Driver

2. Oracle nos permite crear un tipo de tabla únicamente de lectura conocida como EXTERNAL TABLE, donde los datos residen fuera de la propia BD. Se puede realizar sentencias habituales con ellas con JOINS u ordenaciones, pero no es posible hacer DML sobre ellas. Se pueden crear sinónimos y vistas sobre ellas, pero no índices.

3. Vamos a seguir el ejemplo de la documentación para empezar ya que es bastante completo.

# Creamos la estructura de directorios con la que vamos a trabajar
mkdir -p /u01/stage/data
mkdir -p /u01/stage/log
mkdir -p /u01/stage/bad

# Creamos el fichero /u01/stage/data/empxt1.dat
vi /u01/stage/data/empxt1.dat

# Añadimos las siguientes lineas
360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

# Creamos un segundo fichero /u01/stage/data/empxt2.dat
vi /u01/stage/data/empxt2.dat

# Añadimos las siguientes lineas
401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
-- Procedemos a crear la tabla pero antes tenemos que crear los directorios
CREATE OR REPLACE DIRECTORY admin_dat_dir AS '/u01/stage/data';
CREATE OR REPLACE DIRECTORY admin_log_dir AS '/u01/stage/log';
CREATE OR REPLACE DIRECTORY admin_bad_dir AS '/u01/stage/bad';
GRANT READ ON DIRECTORY admin_dat_dir TO hr;
GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;

-- Nos conectamos con el usuario HR para crear la tabla
CONN HR/hr

-- Lanzamos la sentencia de creación
-- Repasamos las clausulas principales
--   TYPE => Driver de acceso a los datos (ORACLE_LOADER o ORACLE_DATAPUMP)
--   ACCESS PARAMETERS => Los parámetros que recibe el driver (en el ejemplo ORACLE_LOADER)
--   PARALLEL => Permite tener paralelismo en la sentencias si se dan ciertas condiciones
--   REJECT_LIMIT => Número máximo de rechazos permitidos (por cada servidor PX)
-- Todos los parámetros del driver ORACLE_LOADER en http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#SUTIL012
CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4),
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25),
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'empxt%a_%p.bad'
         logfile admin_log_dir:'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date char date_format date mask "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )
       )
       LOCATION ('empxt1.dat', 'empxt2.dat')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

-- Lanzamos una consulta de prueba
SELECT * FROM ADMIN_EXT_EMPLOYEES;

-- Si necesitaramos hacer un INSERT ... SELECT y hay muchos datos => habilitar PARALLEL DML
--   Ej. INSERT INTO EMPLOYEES (...) SELECT * FROM ADMIN_EXT_EMPLOYEES;
ALTER SESSION ENABLE PARALLEL DML;

4. Una cosa que no sabía es que se puede especificar un ejecutable para poder realizar un tratamiento previo a la lectura de los datos. En el ejemplo de la documentación se utiliza “zcat” para leer ficheros de origen comprimidos. Vamos a probarlo!

# Creamos un directorio para guardar los archivos comprimidos
mkdir -p /u01/stage/zdata

# Comprimos los dos archivos de origen del ejercicio anterior
cp /u01/stage/data/*.dat /u01/stage/zdata
cd /u01/stage/zdata
gzip *.dat

# Necesitamos copiar también el ejecutable que permite leer los archivos comprimidos "zcat"
cp /bin/zcat /u01/stage/zdata
-- Creamos el DIRECTORY dentro de Oracle
CREATE OR REPLACE DIRECTORY admin_zdat_dir AS '/u01/stage/zdata';

-- Además de lectura, necesitamos dar permiso de ejecución para utilizar "zcat"
GRANT READ, EXECUTE ON DIRECTORY admin_zdat_dir TO HR;

-- Creamos la tabla con la diferencia del argumento PREPROCESSOR donde indicamos que se utilice "zcat"
CREATE TABLE admin_ext_employees_gzip
                   (employee_id       NUMBER(4),
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25),
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_zdat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'empxt%a_%p.bad'
         preprocessor admin_zdat_dir:'zcat'
         logfile admin_log_dir:'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date char date_format date mask "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )
       )
       LOCATION ('empxt1.dat.gz', 'empxt2.dat.gz')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

5. En los ejercicios anteriores hemos usado el driver ORACLE_LOADER, pero también tenemos disponible el driver ORACLE_DATAPUMP. La ventaja de este último es que nos permite descargar datos e insertarlo en el fichero externo. Luego también podemos leer esos datos. Una posible desventaja es que sólo se descargar datos al crear la tabla (CTAS). Después de eso sólo vamos a poder hacer lecturas.

-- Vemos un ejemplo de utilización del driver
-- Damos permisos de escritura al directorio ADMIN_DAT_DIR
GRANT WRITE ON DIRECTORY admin_dat_dir TO HR;

-- Nos conectamos con el usuario HR
CONN HR/hr

-- Creamos una CTAS a partir de datos de otra tabla
--   COMPRESSION => Habilitada (ENABLED)
CREATE TABLE admin_ext_employees_dump
 ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY admin_dat_dir
 ACCESS PARAMETERS (COMPRESSION ENABLED) LOCATION ('emp.dmp'))
 AS
   SELECT * FROM EMPLOYEES;

-- Podemos utilizar el DUMP generado para crear una nueva tabla externa
CREATE TABLE admin_ext_employees_dump2
  (
    EMPLOYEE_ID       NUMBER(6),
    FIRST_NAME        VARCHAR2(20),
    LAST_NAME         VARCHAR2(25),
    EMAIL             VARCHAR2(25),
    PHONE_NUMBER      VARCHAR2(20),
    HIRE_DATE         DATE,
    JOB_ID            VARCHAR2(10),
    SALARY            NUMBER(8,2),
    COMMISSION_PCT    NUMBER(2,2),
    MANAGER_ID        NUMBER(6),
    DEPARTMENT_ID     NUMBER(4)
  )
 ORGANIZATION EXTERNAL
 (
   TYPE ORACLE_DATAPUMP
   DEFAULT DIRECTORY admin_dat_dir
   LOCATION ('emp.dmp')
 );

6. Hay una serie de tipos de datos (BFILE, LONG, LONG RAW y FINAL OBJECT TYPE) que no se puede cargar/descargar con estos drivers. Para poder resolver el problema con el tipo de datos BFILE, Oracle recomienda crear unas funciones de extracción y construcción. Tenéis las funciones en la documentación, pero aun así vamos a realizar el ejercicio. Para ello necesitamos instalar los esquema OE y PM (el procedimiento es basicamente el mismo que para SH).

Documentación en Tahiti -> Masters Book List -> Utilities -> 15 The ORACLE_DATAPUMP Access Driver -> Unsupported Datatypes

-- Creamos las funciones para extraer el directorio y nombre de fichero de un campo BFILE
CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
DIR_ALIAS VARCHAR2(255);
FILE_NAME VARCHAR2(255);
BEGIN
  IF bf is NULL
  THEN
    RETURN NULL;
  ELSE
    DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
    RETURN dir_alias;
  END IF;
END;
/

CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
dir_alias VARCHAR2(255);
file_name VARCHAR2(255);
BEGIN
  IF bf is NULL
  THEN
    RETURN NULL;
  ELSE
    DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
    RETURN file_name;
  END IF;
END;
/

-- Damos permnisos de lectura y escritura en el DIRECTORY ADMIN_DAT_DIR al usuario PM
GRANT READ, WRITE ON DIRECTORY ADMIN_DAT_DIR TO PM;

-- Creamos una tabla externa a partir de una tabla con campos BFILE
CREATE TABLE print_media_xt
ORGANIZATION EXTERNAL
(
  TYPE oracle_datapump
  DEFAULT DIRECTORY admin_dat_dir
  LOCATION ('pm_xt.dmp')
) AS
SELECT product_id, ad_id,
       get_dir_name (ad_graphic) ad_graphic_dir,
       get_file_name(ad_graphic) ad_graphic_file
FROM print_media;

-- Comprobamos que tenemos los datos correctos
SELECT * FROM PRINT_MEDIA_XT;

-- La operación inversa es crear una tabla a partir de un fichero externo con campos BFILE
-- Necesitamos una función para poder crear campos BFILE
CREATE FUNCTION get_bfile (dir VARCHAR2, file VARCHAR2) RETURN
BFILE is
bf BFILE;
BEGIN
  IF dir IS NULL
  THEN
    RETURN NULL;
  ELSE
    RETURN BFILENAME(dir,file);
  END IF;
END;
/

-- Realizamos una prueba para validar la función
-- Este sería el mecanismo para componer campos BFILE a partir de un fichero externo
CREATE TABLE print_media_int AS
SELECT product_id, ad_id,
       get_bfile (ad_graphic_dir, ad_graphic_file) ad_graphic
FROM print_media_xt;

-- Comprobamos que las operaciones son correctas
-- Si es correcto, no debe parecer ningún resultado
SELECT product_id, ad_id,
       get_dir_name(ad_graphic),
       get_file_name(ad_graphic)
FROM print_media_int
MINUS
SELECT product_id, ad_id,
       get_dir_name(ad_graphic),
       get_file_name(ad_graphic)
FROM print_media;

7. Con respecto a los tipos de datos LONG y LONG RAW, el driver ORACLE_DATAPUMP permite descargar columnas LONG y LONG RAW, pero si queremos leer desde un fichero externo, sólo podemos cargar LOB.

-- Creamos una tabla de ejemplo con un campo LONG e insertamos un registro de prueba
CREATE TABLE long_tab
  (
    key                   SMALLINT,
    description           LONG
  );
INSERT INTO long_tab VALUES (1, 'Description Text');
COMMIT;

-- Creamos una tabla externa (con el driver ORACLE_DATAPUMP)
CREATE TABLE long_tab_xt
 ORGANIZATION EXTERNAL
 (
   TYPE ORACLE_DATAPUMP
   DEFAULT DIRECTORY admin_Dat_dir
   LOCATION ('long_tab_xt.dmp')
 )
 AS SELECT key, TO_LOB(description) description FROM long_tab;

-- Comprobamos los resultados
DESC LONG_TAB_XT
SELECT * FROM LONG_TAB_XT;

8. En ocasiones debemos especificar el juego de caracteres de los datos de entrada. El Español tiene carácteres acentuados y símbolos como la ‘Ñ’ que pueden provocar problemas a la hora de crear tablas externas. En el siguiente ejemplo vamos a crear un tabla externa que contiene caracteres específicos del Español.

# Definimos el lenguaje local Español
export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P15

# Creamos un fichero de entrada
vi /u01/stage/data/test_cs.dat

# Añadimos las siguientes lineas
Jose María|Rodríguez Gómez|(57-1) 600-1122|josemario@nomail.co|Carrera 7|28050|Bogotá|Bogotá|Colombia
Guadalupe|Gonzales Sánchez|2124312|guadalupe@nomail.mx|Jesús Carranza|50130|Toluca|Toluca|México
Raúl|Ibáñez Peral|660542983|raul@dbajunior.com|Calle Palestina|28100|Alcobendas|Madrid|España
-- Creamos la tabla externa
CREATE TABLE hr.ocm_students
                   (FIRST_NAME VARCHAR2(20),
                    LAST_NAME  VARCHAR2(20),
                    PHONE      VARCHAR2(20),
                    EMAIL      VARCHAR2(30),
                    STREET     VARCHAR2(40),
                    CP         NUMBER,
                    TOWN       VARCHAR2(20),
                    CITY       VARCHAR2(20),
                    COUNTRY    VARCHAR2(20)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline characterset WE8ISO8859P15
         badfile admin_bad_dir:'test_cs.bad'
         logfile admin_log_dir:'test_cs.log'
         fields terminated by '|'
         missing field values are null
         ( FIRST_NAME, LAST_NAME, PHONE, EMAIL,
           STREET, CP, TOWN, CITY  , COUNTRY
         )
       )
       LOCATION ('test_cs.dat')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

-- Validamos que los datos son correctos
SELECT * FROM HR.OCM_STUDENTS;

9. Tenemnos las siguientes vistas para obtener información de las tablas externas (DBA_EXTERNAL_TABLES y DBA_EXTERNAL_LOCATIONS).

-- Consultas las tablas externas
SELECT * FROM DBA_EXTERNAL_TABLES;

-- Consultamos los ficheros externos utilizando por tablas externas
SELECT * FROM DBA_EXTERNAL_LOCATIONS;

10. Limpiamos el entorno

-- Borramos directories
DROP DIRECTORY ADMIN_BAD_DIR;
DROP DIRECTORY ADMIN_DAT_DIR;
DROP DIRECTORY ADMIN_ZDAT_DIR;
DROP DIRECTORY ADMIN_LOG_DIR;

-- Borramos tablas
DROP TABLE pm.long_tab_xt;
DROP TABLE pm.print_media_int;
DROP TABLE pm.print_media_xt;
DROP TABLE hr.admin_ext_employees_dump2;
DROP TABLE hr.admin_ext_employees_dump;
DROP TABLE hr.admin_ext_employees_gzip;
DROP TABLE hr.admin_ext_employees;
DROP TABLE hr.ocm_students;