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;