TIP SQL: Reemplazando caracteres especiales con ORACLE

Una de las grandes ventajas de Pentaho Data Integration, es que podemos elegir la mejor manera de realizar una tarea de transformación o limpieza de información: Realizándola directamente en la consulta SQL, traernos los datos y realizarla con un Step de Kettle o un Script Java-JavaScript.

Hoy les muestro una sencilla consulta para reemplazar caracteres especiales de una columna en ORACLE apoyándonos en la función REGEXP_REPLACE.

SELECT
  REGEXP_REPLACE(LA_COLUMNA, '[^A-Za-z0-9ÁÉÍÓÚáéíóú ]', '') AS COLUMNA
FROM TABLA;

Para realizar pruebas sin necesidad de tener una tabla propia nos podemos apoyar de la tabla DUAL.

SELECT
  REGEXP_REPLACE('ANA MARIA R$$$""OJAS MARTÍNEZ(', '[^A-Za-z0-9ÁÉÍÓÚáéíóú ]', '') AS COLUMNA

FROM DUAL;

El resultado de esta consulta será:

ANA MARIA ROJAS MARTÍNEZ

SQL, JavaScript, Step de Kettle …¿Cual es la mejor forma de hacerlo? Dependerá de nuestra experiencia, los recursos técnicos de nuestro SGBD y del servidor de procesamiento, así que les aconsejo probar varias opciones para encontrar la  más eficiente.

¡Hasta una próxima entrada!.

Lo básico en PDI: migrando tablas entre bases de datos

Bueno ahora vamos a realizar un ejercicio básico: migrar una tabla de dos bases diferentes.

En este caso voy a utilizar los gestores de base de datos Oracle Express Edition y MySQL Server Community. La idea, es copiar una tabla del usuario de ejemplo HR en Oracle y pasarla al esquema TEST de MySQL.

Desbloquear el usuario HR en Oracle

Si acabos de instalar Oracle Express Edition, el usuario HR esta bloqueado por defecto, por desbloquearlo nos conectamos con el usuario SYSTEM y la contraseña que definimos durante la instalación y ejecutamos los siguientes comandos:

ALTER USER HR ACCOUNT UNLOCK;
-- Cambia la contraseña
ALTER USER HR IDENTIFIED BY password;
 -- Da permiso de conexión y acceso de los objetos propios
GRANT CONNECT, RESOURCE TO HR;

Acceder al usuario TEST en MySQL

El usuario TEST de MySQL por defecto no tiene objetos creados. Inicialmente la conexión a Root se hace sin contraseña.

Creando la nueva transformación en PDI

Creamos una nueva transformación que vamos denominar tr_migrar_tabla. Vamos al panel izquierdo y vamos a rastrar un paso tipo Table Input y otro Table Output. Ahora creamos las dos conexiones, las cuales vamos a nombrar CONEX_ORACLE_HR y CONEX_MYSQL_TEST respectivamente. Para mayor información de cómo configurar conexiones podemos remitirnos a la entrada Configurando conexiones en Spoon.

Configurando la Tabla de Origen

En el  paso Table Input, vamos a elegir la conexión CONEX_ORACLE_HR. En el cuadro de texto SQL, escribimos la consulta

SELECT * FROM EMPLOYEES

Error Común: No  se deben usar el carácter punto y coma (;) para finalizar la sentencia en los Table Input.

Damos clic en el botón Preview para comprobar que nuestra consulta funciona correctamente. Nos dará la opción de elegir cuantas filas queremos previsualizar, normalmente 1000. Es importante que no se nos vaya la mano con el número de filas, si escribimos una cantidad muy grande podríamos tener problemas de memoria y bloquear la aplicación.

Crear un salo entre dos pasos

Para conectar dos pasos damos clic en la paso origen (Table Input), elegimos el icono con la flecha verde apuntando a la derecha y con clic sostenido conectamos con el paso destino (Table Output). En algunos casos nos dará a elegir entre dos opciones: Main output of Step o Error handling of Step, para la mayoría de los casos escogemos Main output of Step. Error handling of Step será tema de una nueva entrada sobre captura de errores.

Configurar la tabla de destino

Vamos a Table Output, elegimos la conexión CONEX_MYSQL_TEST, y en Target Table escribimos el nombre de la tabla de destino en este caso EMPLOYEES. Luego damos clic en el botón SQL, para que Pentaho automáticamente nos genere el Script de la tabla de destino en el esquema TEST. Lo revisamos y damos clic en Execute.

Finalizando esta configuración podemos ejecutar la transformación: Clic en la flecha verde «Run transformation» y luego en el diálogo que nos aparece, sobre el botón Launch.

Si la transformación se ejecuta correctamente, desde línea de comandos de MySQL podemos comprobar que la tabla este creada y con los datos migrados, ejecutando las siguientes sentencias:

use test;
SELECT * FROM EMPLOYEES LIMIT 10;

Este fue un ejercicio muy sencillo pero muy útil cuando estamos empezando en el manejo de Kettle, pero es suficiente para ver que es una herramienta muy gráfica y fácil de manejar. Realizamos un migración de una tabla sin necesidad de exportar los datos en archivos planos u otro formato, o sin siquiera intentar el engorroso proceso de conectar las bases de datos directamente. 

Espero que les haya sido de utilidad este post. ¡Que pasen un muy buen día y exitos!

TIP SQL: ¿Es un número? como saberlo en Oracle

Continuando con los casos en los que se guardan números en campos VARCHAR o String, hoy vamos a ver como reconocer si la información en una columna es numérica o no.

Oracle no tiene ninguna función propia (hasta ahora conocida) que permita saber sin un valor en un columna tipo VARCHAR es un número.

Opcion 1

Buscando en la red encontré una muy buena opción que me ha funcionado hasta ahora. Se realiza con la función TRANSLATE, cuya documentación oficial la podemos encontrar aquí http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions196.htm 

Esta consulta nos devuele todos los registros que sean númericos en una determinada columna. Originalmente fue publicada en este enlace: http://stoneheads.wordpress.com/2010/07/16/trucos-oracle-comprobar-si-un-campo-es-numerico/

SELECT *
FROM TABLE
WHERE TRANSLATE(COLUMNA, 'T 0123456789', 'T') IS NULL
AND COLUMNA IS NOT NULL;

 Haciendole unas pequeñas mejoras de mi parte, utilizando la sentencia CASE, podemos obtener un listado con una nueva columna para mostrar si el valor es Numerico, Texto o Nulo, :

SELECT
COLUMNA,
CASE
WHEN 
  TRANSLATE(COLUMNA, 'T 0123456789', 'T') IS NULL AND COLUMNA IS NOT NULL THEN 'ES NUMERO'
WHEN 
  TRANSLATE(COLUMNA, 'T 0123456789', 'T') IS NOT NULL THEN 'ES TEXTO'
ELSE 'ES NULO'
END AS TIPO
FROM TABLA;

Opcion 2

Otra forma de hacerlo, es aprovechando el poder de la expresiones regulares,con la función REGEXP_LIKE (Documentación en http://docs.oracle.com/cd/B14117_01/server.101/b10759/conditions018.htm). REGEXP_LIKE nos devuelve verdadero, en todos los valores que hagan match con una expresión regular ingresada por parámetro.

Esta sencilla consulta nos devolverá todos los valores que sean totalmente numéricos en una determinada columna, descartando también los valores nulos.

SELECT COLUMNA 
FROM TABLA
WHERE REGEXP_LIKE (COLUMNA, '^[[:digit:]]+$');

Bueno como ven, en Integración de Datos, hay muchos caminos para llegar al mismo resultado, por lo que en una próxima entrada veremos como hacer operaciones similares utilizando los steps de PDI.

¡Dios los bendiga y que pasen un muy buen día!

TIP SQL: Quitar ceros a la izquierda en Oracle

En integración de datos, es muy común que necesitemos cruzar tablas por campos que contienen números pero cuyo tipo de dato es VARCHAR. En este tipo de campos podemos encontrar números con ceros a la izquierda, y al momento de intentar cruzar ‘00005’ con ‘5’, la consulta no nos va a dar ningún resultado.

En este caso es muy útil la función LTRIM de Oracle, la cual permite definir un campo y el caracter a la izquierda que se quiere quitar.

 

LTRIM(NOMBRE_COLUMNA, '0')

El ejemplo en una consulta SQL sería el siguiente:

SELECT *
FROM TABLA_1 T1
JOIN TABLA_2 T2
ON LTRIM(T1.CODIGO_T1, '0') = LTRIM(T2.CODIGO_T2, '0');

Bueno espero seguir alimentando el blog con estos tips, pues algunas veces es mas eficiente realizar ciertas operaciones en la base de datos, que con los pasos de PDI. Nos vemos en la siguiente entrada, ¡éxitos!

Pentaho Data Integration – Kettle ¿Cómo Instalar?

Pentaho Data Integration (PDI) o Kettle, es una poderosa, intuitiva y eficiente herramienta, para la realización de procesos de Extracción, Transformación y Carga.

Es muliplataforma, así que si trabajamos en Windows o en Linux no tenemos por qué preocuparnos. Es muy gráfica, así que nuestros procesos quedan plasmados en forma de flujos, por lo que en el trabajo suelen decirme que me la paso haciendo ‘cuadritos’. Lo díficil es conocer el funcionamiento de cada uno de estos ‘cuadritos’ o los componentes de Kettle denominados steps, son bastantes y algunos tiene poca o nula documentación en la Internet.

Empecemos por definir algunos conceptos:

Kettle: Otro nombre con el que se suele denominar Pentaho Data Integration, o mejor el nombre original de la herramienta antes que fuera adquirida por Pentaho.

Spoon: Programa que permite construir procesos con una interfaz muy gráfica e intuitiva.

Kitchen: Componente que permite ejecutar jobs por línea de comandos.

Pan: Componente que permite ejecutar transformaciones por línea de comandos.

Bueno … mucha teoría y vamos a la práctica, primero tenes que tener instalado Java en nuestra máquina. La última versión de Java la podemos conseguir en este enlace http://www.oracle.com/technetwork/es/java/javase/downloads/index.html

• Luego debemos comprobar que exista la variable de entorno JAVA_HOME, para ello damos clic derecho en MiPC -> Propiedades -> Configuración Avanzada del Sistema -> Opciones Avanzadas -> Variables de Entorno. Si no existe, damos clic en Nueva … y le asignamos la ruta de instalación del JDK o el JRE

• Ahora descargamos la versión Community de Kettle en el siguiente enlace http://sourceforge.net/projects/pentaho/files/Data%20Integration// En este caso vamos descargar la última versión estable al día de hoy la 4.4,0, así que elegimos el archivo pdi-ce-4.4.0-stable.zip.

• Cremos un directorio con nombre pentaho en algún lugar de nuestros disco (preferiblemente en la raíz del sistema) y descomprimos el archivo. Nos creará una carpeta llamada data-integration. 

• Si vamos a trabajar con bases de datos diferentes a MySQL, es necesario descargar los respectivos .jar del JDBC y copiarlos a la ruta ..,/data-integration/libext/JDBC. En mi caso que suelo trabajar con bases de datos Oracle tengo que copiar el archivo ojdbc14.jar

• Ahora podemos iniciar el programa entrando al directorio de instalación (…/data-integration) y ejecutando el archivo Spoon.bat en Windows o spoon.sh en Linux.

Si se desean descargar los demás productos de la suite versión community los puede encontrar en la dirección http://community.pentaho.com/. En la próxima entrada veremos como configurarle los parámetros de memoria al Spoon.