|

miércoles, 26 de junio de 2013

Operaciones sobre cadenas en KETTLE

Pick a letter...

En la entrada anterior tratamos como pasar una tabla de un base de datos a otra, sin realizar ningún proceso intermedio, es decir, realizamos un proceso de Extracción y Carga, pero no aplicamos ningún tipo de Transformación sobre los datos, que es tal vez, la parte mas importante dentro del proceso ETL.

Kettle nos proporciona diversos pasos para manipular y limpiar los datos, ahorrándonos bastante escritura de código, y con la facilidad de que cada componente utilizado, realiza los procesos para todos registros del flujo, olvidándonos del manejo de ciclos, arrays o cursores, que en los lenguajes de programación tendríamos que controlar por nuestra cuenta. Cuando manejamos información de distintas fuentes, las cadenas de texto pueden llegar a ser un gran dolor de cabeza: espacios dobles, caracteres especiales, tildes, espacios a la izquierda o a la derecha, mayúsculas, minúsculas ... en fin problemas que si no se solucionan, pueden generar errores al realizar inserciones en la base de datos destino. En esta entrada trataré dos de los pasos más utilizados durante mi experiencia para realizar limpieza de información: String Operations y Replace in String.

Step String Operations

No hay mejor explicación que un ejemplo, así que para empezar, podemos arrastrar un paso String Operations entre el Table Input y el Table Output del ejemplo que tratamos en la entrada anterior .

Esta transformación va a cambiar a mayúscula sostenida los atributos FIRST_NAME y LAST_NAME, de los datos obtenidos de la tabla EMPLOYEES del modelo HR. Para ello damos clic sobre el paso String Operations y en la columna In Stream Field, seleccionamos alguno de los campos del flujo que sean tipo String. Como podemos ver, podemos realizar operaciones sobre varias columnas a la vez.

Este step nos brinda opciones varias opciones:

Trim Type: Permite quitar espacios en blancos, ya sea a la derecha, a la izquierda o a ambos lados de la cadena.

Lower/Upper: Nos da la opción de poner la cadena, en mayúscula o minúscula sostenida.

Padding, Pad Char y Pad Length: Con padding podemos elegir si queremos rellenar la cadena a la izquierda o a la derecha con el carácter definido en Pad Char, hasta la longitud definida en Pad Length. Ejemplo: si tenemos el cadena '11', definimos un Pad Cahr '0' y un Pad Length 5,el resultado será 00011.

InitCap: Agrega mayúscula inicial a la cadena, muy útil en el manejo de nombres propios.

Escape: una función muy útil para escapar caracteres especiales según un lenguaje de programación o formato de archivo. Por ejemplo, en  XML, las letras tildadas, las cambiará a su respectivo código.

Digits: Deja solo los caracteres 0-9 si elegimos la opción only, o los elimina por completo si dejamos la opción remove.

Remove Special character: permite elegir entre una lista de caracteres especiales a remover, por ejemplo, los retornos de carro, espacios en blanco o tabuladores.

Volviendo a nuestro ejemplo, el paso String operations quedará como muestra la imagen, simplemente se pasarán a mayúscula las columnas FIRST_NAME y LAST_NAME del flujo:



Step Replace in String


Otro paso muy útil en el manejo de cadenas es Replace in String, que simplemente nos permite reemplazar una cadena por otra

En el ejemplo, lo podemos utilizar para reemplazar los puntos (.) en PHONE_NUMBER por guiones (-) . Para ellos arrastramos un Replace in String a la transformación, en In stream field elegimos PHONE_NOMBRE, en Use Regex escribimos N para no utilizar expresiones regulares (La utilización de expresiones regulares la veremos en otra entrada) y en Search y Replace with, ponemos los cadenas a buscar y reemplazar (. por -), respectivamente, como lo muestra la imagen. Si la idea es desaparecer el carácter buscado, simplemente dejamos vacía la columan Replace With.


Previsualizando el flujo de KETTLE

Hasta este punto, podemos realizar una pre visualización  para ver como van nuestros datos transformados, haciendo clic en un paso y luego en el botón Execute Preview. Nos aparecerá un diálogo donde nos pedirá confirmar la cantidad de filas a previsualizar. Como siempre les digo, evitemos poner un valor muy alto, si nuestra máquina de desarrollo no es muy potente o no hemos configurado los parámetros de memoria de nuestro PDI. Finalmente clic en el botón Launch.



Con estos dos steps, ya podemos idear varias transformaciones para realizar limpieza de información. ¿Que tal reemplazar todos los espacios dobles por uno solo? o ¿Cambiar los símbolos @ por la letra 'a'?, bueno al final todo dependerá de los requerimientos en nuestros proyectos. Nos podría surgir la pregunta ¿Porqué no hacer esto directamente en el script SQL que trae los datos? ... Bueno Pentaho ya viene optimizado para realizar estas tareas, y utilizará los recursos del servidor en el que se ejecute, evitando ponerle una carga mayor al SGBD, además no siempre nuestro origen de información será una base de datos, los ominipresentes archivos planos y las hojas de excel, seguirán siendo pan de cada día en las empresas. Aunque tengamos en cuenta, los SGBD serán mas eficientes en otras tareas como ordenamientos y JOINS, así que como desarrolladores, podemos analizar, pobrar y elegir la mejor opción.

Esto fue todo por hoy ¡Buen día y bendiciones!
Enhanced by Zemanta

domingo, 23 de junio de 2013

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

Migracion

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!

jueves, 13 de junio de 2013

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

Number Photography


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!



lunes, 10 de junio de 2013

Configurando conexiones en Spoon

Connection 

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!



jueves, 6 de junio de 2013

TIP SQL: Quitar ceros a la izquierda en Oracle

Un cero a la izquierda?

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!



miércoles, 5 de junio de 2013

Steps, Transformations and Jobs


in step
Spoon es el IDE de Pentaho para construir procesos ETL. Con el podemos crear dos tipos de componentes, Transformaciones y Trabajos.

Transformacion (Transformation): Conjunto de pasos (steps) interconectados por medio de saltos (hops) que procesan registros a partir de un origen de datos y cuya salida son uno o mas registros. Los steps dentro de una transformación se procesan de manera simultánea y asíncrona.



Cada step nos permite realizar un proceso de entrada, transformación o salida de registros. PDI se puede conectar a prácticamente cualquier origen de datos, la siguiente imagen muestra los mas utilizados:


Trabajo (Job): Conjunto de transformaciones, trabajos y pasos que se ejecutan de manera secuencial. El job en sí no maneja registros sino secuencias de tareas. La salida de cada job es un estado exitoso o fallido.

EXPLORANDO STEPS
  • Abrimos Spoon, si nos pide conectar a un repositorio (tema de una próxima entrada) damos clic en el botón Cancelar (Cancel).
  • Vamos al menú File -> Nueva -> Transformation
  • Al lado izquierdo podemos encontrar dos pestañas: View y Design. Elegimos Design y damos clic en la carpeta Input. Esta carpeta contiene todos los componentes que pueden tomarse como origen de datos. Para agregar un componente a nuestra transformación, basta con arrastrarlo al área de trabajo.

  • En las pestaña design podemos encontrar otras carpeta Transform, que contiene pasos para Limpiar datos, realizar cálculos o cambiar el tipo del dato.
  • La carpeta Scripting, contiene componentes que posibilitan agregar y ejecutar código de Java, JavaScript o SQL en nuestra transformación. Otros lenguajes como Ruby estan soportados mediante plugin.
  • La carpeta Flow, contiene los pasos que nos permiten controlar y dividir el flujo: Filtrar registros, realizar sentencias SWITCH/CASE, abortar la transformación o detenerla mientras termina algún proceso.
  • La carpeta Output, contiene los pasos que nos permiten tener salidas de datos: tablas de bases de datos, archivos de texto, archivos de excel, archivos xml, entre muchos otros.
Esta fue una breve reseña de los STEPS de pentaho, ya simplemente es de cada uno explorar cada uno de los componentes. El siguiente enlace se puede encontrar la documentación oficial de cada paso http://wiki.pentaho.com/display/EAI/Pentaho+Data+Integration+Steps

martes, 4 de junio de 2013

Aumentando la memoria de Spoon




Por defecto, Spoon, viene configurado para utilizar 256 Mb de tu memoria RAM. Esta cantidad no es suficiente si estamos trabajando con procesos ETL que manejen cifras a nivel de millones de registros, y puede que se llegue a presentar un error temible y muy común en Pentaho:

java.lang.OutOfMemoryError: Java heap space


Aumentar la memoria del PDI es bastante sencillo, simplemente tenemos que ir a la ruta de instalación (.../data-integration) y editar el archivo Spoon.bat en Windows o spoon.sh en Linux, no sin antes hacer una copia de seguridad de estos archivos por sí algo sale mal.

Debemos ubicarnos en la siguiente línea en el código:

if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xmx256m" "-XX:MaxPermSize=128m"



Allí podemos cambiar los parámetros de memoria según nuestro hardware lo permita, usualmente suelo cambiar a estos parámetros a:

if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xmx1024m" "-XX:MaxPermSize=512m"

Es importante tener en cuenta que el aumento de memoria esta limitado por la versión de Java que tengamos:  Si usamos la versión de 32 bits máximo nos dejara configurar 512m en PENTAHO_DI_JAVA_OPTIONS. Ya con versiones de 64 bits de Java, la limitación dependerá de la cantidad de memoria de la que disponga nuestro servidor.

Finalmente, guardamos el archivo y ejecutamos de nuevo Spoon.bat o spoon.sh.



lunes, 3 de junio de 2013

Pentaho Data Integration - Kettle ¿Cómo Instalar?


Tea Kettle

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


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

sábado, 1 de junio de 2013

Pentaho: Introducción



Pentaho
Pentaho es un conjunto de programas libres, que abordan las distintas fases de la inteligencia de negocios. En la actualidad se perfila como la suite de inteligencia de negocio de código abierto por excelencia, gracias a su versatilidad, facilidad de uso e integración con prácticamente cualquier origen y base de datos. Ofrece dos versiones, la de pago, Pentaho Enterprise, y la gratuita, Pentaho Community.

La versión enterprise la podemos descargar del sitio  http://www.pentaho.com/testdrive/ y probarla por 30 días. La versión community, nos ofrece los productos de pentaho por separado, por lo que debemos descargar, instalar y configurar cada uno, tarea que no puede resultar tan sencilla. De igual forma la funcionalidad de estas no son muy limitadas, por lo que gracias a esta versión tenemos poderosas herramientas, para la distintas fase de la inteligencia de negocios, de forma gratuita y abierta.

Fase Inteligencia de Negocios
Herramienta
Extracción, Transformación y Carga
Pentaho Data Integration – Kettle, Spoon, Carte, Kitchen, Pan
Diseño de Cubos
Schema Workbench, Mondrian
Visualización de Cubos y Reportes
BI-Server – User Console
Diseño de Reportes y Aplicaciones
Pentaho Design Studio
Minería de Datos
Weka
Configuración y Administración
Pentaho Administration Console

Este blog se centrará en los procesos de Extracción, Transformación y Cargue, realizados con Pentaho Data Integration (Kettle).

El cuadro de abajo, muestra las principales difierencias entre la versión Enterprise y Community de Pentaho.


Pentaho Enterprise Edition
Pentaho Community Edition
Suite Completa
Herramientas vienen por Separada
Paga – Con Soporte
Gratuita – Sin soporte
Incluye Pentaho Analyzer, excelente herramienta para visualizar cubos OLAP.
No incluye Analyzer report, se puede suplir con pluggins como Saiku
Configuración Automática
Configuración Manual
Consola de Administración Completa
Consola de Administración Básica

Related Posts Plugin for WordPress, Blogger...