|

martes, 3 de septiembre de 2013

Cambiando la contraseña del Repositorio de KETTLE

A password key? 

En el momento de crear el repositorio, PDI en ninguna parte, nos da la opción de elegir la contraseña del repositorio, sino que nos deja las credenciales por defecto user:admin y pass:admin. Para cambiar la contraseña, luego de conectarnos al repositorio, debemos ir al menú Tools -> Repository -> Explore y damos clic en el pestaña Security.


Seleccionamos el usuario admin y damos clic en el botón editar  


En la ventana que nos aparece, podemos escribir la nueva contraseña y dar clic en OK. Demasiado sencillo ¿no?, tan sencillo que ni siquiera nos pide confirmar la nueva contraseña o ingresar primero la actual (FAIL de Pentaho), así que debemos ser muy cuidadosos al escribirla, no sea que por un caracter perdamos el acceso a nuestro repositorio.

Entrada muy corta pero espero que les haya sido útil. Éxitos a todos.

domingo, 11 de agosto de 2013

Crear un Repositorio en KETTLE: alternativa mas segura a los .ktr y .kjb

bodega 
Si creamos jobs y transformaciones, se guardarán en archivos xml con extensión .kjb y .ktr, respectivamente. Este será nuestro código fuente cuando desarrollamos en PDI. Estos archivos pueden presentar ciertos inconvenientes, pues al no ser un compilado, cualquier persona con acceso a él puede tener acceso a nuestro código. Para aumentar los niveles de seguridad y tener nuestras transformaciones y jobs mejor organizadas, podemos crear un Repositorio de Kettle. Este repositorio nos permite guardar nuestro código en tablas relacionales creadas por la misma herramienta, además que tenemos una ganancia en seguridad al estar protegidos tanto por la contraseña del repositorio como por la contraseña del esquema en la base de datos. Para crearlo debemos seguir los siguientes pasos:

1. Primero que todo para crear un repositorio, debemos tener un esquema de bases de datos exclusivo para el repositorio, que tenga permisos para crear tablas. Puede ser cualquier base de datos soportada por Pentaho (es decir casi todas) y de la que tengamos el driver JDBC configurado.(Mas información en este enlace)

Por ejemplo en Oracle creo el usuario con las siguiente líneas de código:

create user repositorio_kettle identified by repositorio_kettle;
grant connect, resource to repositorio_kettle;

2. Iniciamos Spoon. Nos aparece una ventana con Título Repository Connection y damos clic en el botón verde con el símbolo (+) . (Si no aparece esta ventana al iniciar vamos en la herramienta a Tools -> Repository -> Connect)




3.  En la ventana que nos aparece, elegimos la opción: Kettle database repository: this repository uses a central relational database to store ETL metadata y dar clic en OK.

4.  En la pantalla de selección de Conexión de Bases de Datos, damos clic en el botón New, para desplegar el formulario con los parámetros de conexión. Lo llenamos con los datos del esquema creado, probamos con el botón Test y guardamos con el botón OK




5. Volvemos a la ventana Repository Connection, damos un ID y un nombre a nuestro repostorio. p. ej REPOSITORIO_DESARROLLO, y clic en el botón Create or Upgrade. Nos aparecerá una ventana de confirmación a la que le diremos que Sí.



6. Aparece una pantalla con los Scripts de creación de las tablas, y damos clic en Execute




7. Luego de que la herramienta cree el repositorio, regresamos a la venta Repository Connection, damos clic en OK.

Listo la próxima vez que iniciemos Spoon elegimos el repostiorio REPOSITORIO_DESARROLLO, y nos autenticamos con el nombre de usuario y contraseña por defecto admin - admin.

Todas nuestras transformaciones, trabajos y conexiones de bases de datos quedaran guardados en el repositorio.

Seguiremos en las próximas entradas hablaremos de importar y exportar repositorios, así como de su administración. Exitos a todos.




sábado, 27 de julio de 2013

Cambiando la ubicación de .kettle: Definir KETTLE_HOME

Kettle 

No siempre la ubicación por defecto del directorio .kettle (Ver mas sobre la carpeta .kettle) es la mas adecuada para un servidor de Pruebas o Producción. Si deseamos cambiar esta ruta, es necesario crear la variable de entorno KETTLE_HOME. A continuación explico los pasos para realizar estos cambios tanto en Windows como en Linux.

En Windows


En el explorador de Windows damos clic derecho sobre Equipo -> Propiedades -> Configuración Avanzada del Sistema

En la pestaña Opciones Avanzadas damos clic en el botón Variables de Entorno y luego en el botón Nueva ...

Creamos una variable con nombre KETTLE_HOME, cuyo valor será la ruta asignada para la configuración, en este caso c:\pentaho



Aceptamos y reiniciamos spoon.bat para que Pentaho tome los cambios.

En Linux

En este sistema operativo debemos editar algunos archivos de configuración y ejecutar algunos comandos, por lo que deberíamos tener acceso root.
Editar el archivo con ruta /etc/profile y agregar al final las siguientes línea:
KETTLE_HOME=/datos/pentaho/kettle/common
export KETTLE_HOME
Guardar el archivo y ejecutar el siguiente comando en una consola con permisos root.
source /etc/profile
Comprobar con el siguiente comando que la variable este asignada:
echo $KETTLE_HOME
    Ejecutar spoon.sh para que Pentaho tome los cambios.
      Hasta un próxima entrada, que sigan pasando un buen día.

      jueves, 25 de julio de 2013

      Migrando la Configuración de PDI: el directorio .kettle

      Traveling bags


      Es común que tengamos varios equipos de desarrollo, o que necesitamos migrar la configuración de Kettle desde un servidor de desarrollo a uno de pruebas o producción. Esta configuración incluye variables, conexiones compartidas a bases de datos y nuestros repositorios. Si algún día necesitan migrar esta configuración la siguiente información les puede resultar muy útil.

      Los archivos de configuración de Pentaho Data Integration los podemos ubicar bajo el directorio .kettle, el cual esta alojado en nuestra carpeta de usuario:

      En Windows (Vista, 7 y 8) -> C:\Users\NombreUsuario
      En Linux -> /home/NombreUsuario. 

      Para poder acceder a él es necesario tener habilitada la visualización de archivos ocultos. Si queremos un configuración idéntica basta con copiar todo el contenido de la carpeta a nuestra .kettle de la nueva máquina. Si queremos copiar solo ciertas configuraciones, tenemos tener claro el contenido de cada archivo:

      Los principales archivos de configuración que encontramos son:

      kettle.properties: Archivo que nos permite definir variables globales del aplicativo. Por ejemplo podemos crear una variable con el nombre de nuestro servidor de bases de datos, TEST_SERVER, y luego llamarla en nuestras transformaciones de la forma ${TEST_SERVER}. Así que si llegara a cambiar el nombre del servidor, simplemente debemos asignarle el nuevo valor en el kettle.properties y no modificar nuestras transformaciones y jobs.

      PRODUCTION_SERVER = hercules
      TEST_SERVER = zeus
      DEVELOPMENT_SERVER = thor
      SERVER_PORT = 1521
      SERVER_SCHEMA = TRANSACT
      


      shared.xml: En este archivo encontramos todas nuestras conexiones compartidas (Mas información en Configurando conexiones en Sppon). Si simplemente queremos copiar una conexión compertida, podemos identificarla dentro de las etiqueta <connection>, copiarla y pegarlo en el shared.xml de otra servidor con Pentaho. Las contraseñas de conexión en este archivo están encriptadas.


      <connection>
          <name>DATAWAREHOUSE</name>
          <server>localhost</server>
          <type>ORACLE</type>
          <access>Native</access>
          <database>xe</database>
          <port>1521</port>
          <username>DWAREHOUSE</username>
          <password>Encrypted 2be98fc8aa7bb38a2b8b58f1a79cff</password>
          <servername/>
          <data_tablespace/>
          <index_tablespace/>
          <attributes>
            <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
            <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
            <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
            <attribute><code>PORT_NUMBER</code><attribute>1521</attribute></attribute>
            <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
            <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute>
            <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
          </attributes>
        </connection>
      
      

      repositories.xml: Aparte de los archivos .ktr y .kjb, podemos crear las transformaciones y jobs en un repositorio de bases de datos. Si queremos acceder al repositorios desde varias computadores, es necesario tener definida tanto la conexión como el repositorio, que podemos identificar con las etiquetas <connection> y <repository> respectivamente.

      <repositories>
        <connection>
          <name>KREPOSITORY</name>
          <server>albafika</server>
          <type>ORACLE</type>
          <access>Native</access>
          <database>xe</database>
          <port>1521</port>
          <username>KREPOSITORY</username>
          <password>Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde</password>
          <servername/>
          <data_tablespace/>
          <index_tablespace/>
          <attributes>
            <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
            <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
            <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
            <attribute><code>PORT_NUMBER</code><attribute>1521</attribute></attribute>
            <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
            <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute>
            <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
          </attributes>
        </connection>
        <repository>    <id>KettleDatabaseRepository</id>
          <name>REPOSITORIO_DESARROLLO</name>
          <description>REPOSITORIO_DESARROLLO</description>
          <connection>KREPOSITORY</connection>
        </repository>
        </repositories>
      


      En una próxima entrada veremos como cambiar la ruta del directorio .kettle

      Dios los bendiga y exitos

      domingo, 7 de julio de 2013

      TIP SQL: Reemplazando caracteres especiales con ORACLE

      Griego


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

      jueves, 4 de julio de 2013

      Reemplazando caracteres especiales en KETTLE

      A regexp to match regexp
      El step Replace in String nos simplifica bastante la limpieza de información, y más, si se utiliza para realizar reemplazos utilizando expresiones regulares.  Tal vez queremos borrar los caracteres especiales de una cadena de texto (Al día de hoy la definición exacta de caracter especial sigue siendo una incógnita).  Sería engorroso escribir una regla, que reemplace por una cadena vacía cada carácter especial que existe.  En este caso sería mas fácil decirle que reemplace un conjunto de caracteres por la cadena vacía. Aquí es donde la expresiones regulares entran en el juego.

      Con una expresión regular básica podemos podemos definir un conjunto de caracteres. En la Internet encontramos una expresión regular muy común, la cual representa caracteres alfanuméricos:

      [0-9A-Za-z] 

      Esta expresión sería suficiente por ejemplo para representar nombres de personas en países anglosajones, pero como hispano parlantes, sería suficiente para representar nombres propios como ¿EDUARDO NUÑEZ RODRÍGUEZ? De nuestro idioma hacen parte diacríticos y la letra Ñ, así las tecnologías de la información estimulen su desuso.

      Así que mi versión de caracteres NO ESPECIALES (Incluyendo el espacio en blanco) sería la siguiente:

      [0-9A-Za-zÁÉÍÓÚáéíóúÑñ ]

      Entonces los caracteres ESPECIALES serían todos que aquellos que esten por fuera de este conjunto, y esto lo podemos hacer anteponiendo el caracter ^:

      [^0-9A-Za-zÁÉÍÓÚáéíóúÑñ ]

      Con una variación de esta expresión regular, podríamos en una sola instrucción del Replace in String, borrar todos los caracteres distintos a los especificados.

      Nuestra transformación constará simplemente de un Generate Rows para realizar un caso de prueba y un Replace in String:


      El paso Generate Rows, generará 10 filas, con la columna CADENA, todas con el valor a limpiar ANA@ M@RÍA #ROJAS HERNA&NDEZ''''''''''''''''


      El paso Replace in String, elegiremos realizar la operación sobre la columna CADENA, habilitamos la opción useRegEx (Y), en Search escribimos nuestra expresión regular [^0-9A-Za-zÁÉÍÓÚáéíóúÑñ ] y en Replace With dejamos vacío.


      Guardando la transformación y haciendo una previsualización, el valor en la columna CADENA quedó como ANA MRÍA ROJAS HERNANDEZ.


      Al cambiar la expresión regular por [0-9A-Za-zÁÉÍÓÚáéíóúÑñ ] obtendremos el resultado contrario:


      Podemos cambiar nuestra expresión para aceptar otros caracteres comunes como el ampersand (&) o la cedilla (Ç), o utilizar la expresion [^0-9] para borrar todos los caracteres no numéricos. Les aconsejo el siguiente link http://www.w3schools.com/jsref/jsref_obj_regexp.asp para conocer un poco más de las Expresiones Regulares.

      ¡Exitos y Buena Suerte!


      ..

      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.

      Related Posts Plugin for WordPress, Blogger...