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

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!

Configurando conexiones en Spoon

 

PDI no tiene problema en conectarse a cualquier base de datos del que dispongamos un driver JDBC.

La versión comunity por defecto contiene solo algunos drivers,  incluido MySQL Connector.

Si no disponemos del conector para nuestro gestor lo descargarmos y lo copiamos en la ruta /data-integration/libext/JDBC

En los siguientes enlaces podemos descargar los conectores para los gestores mas conocidos:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
http://jdbc.postgresql.org/
http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx
http://dev.mysql.com/downloads/connector/j/

Luego de esta configuración, iniciamos pentaho y creamos una nueva transformación.

En el panel izquierdo, en la pestaña View, damos clic derecho sobre Database connections -> New.

Damos un nombre a la conexión y llenamos los parámetros de nuestra conexión: Servidor, puerto, nombre de la base de datos, usuario y contraseña.

 

Probamos la conexión con el botón Test, y si la conexión es satisfactoria damos clic en OK.

Luego la conexión aparecerá en el panel izquierdo. Esta conexión solo funcionará para la transformación desde la que se creó. Si queremos compartirla simplemente damos clic derecho sobre la conexión y elegimos Share. Las conexión compartidas quedaran almacenadas en el archivo shared.xml del directorio .kettle (Este directorio por lo general queda en la carpeta del usuario en el sistema) y serán accesibles desde cualquier transformación o trabajo.

Con nuestra conexión creada, puede ser utilizada en todos los pasos que impliquen manejo de base de datos. Los mas conocidos son los que muestra la imagen:

 

Estos pasos nos permiten seleccionar una conexión desde una lista desplegable, y realizar operaciones tanto DDL como DML en la base de datos, limitado claro está, por los permisos que tenga el usuario con el que configuramos la conexión.

 

 Si tienen alguna duda no duden en comentar, que tengan 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!