Bucles con Pentaho Data Integration

Muchas veces tenemos la necesidad de crear un proceso determinado de ETL que necesita ser ejecutado de manera repetida, pero para varios parámetros diferentes. Frente a esto, podríamos recurrir a crear una ETL con una parte de configuración inicial manual, donde en cada iteración podríamos definir una variable y lanzar el proceso a mano.

Sin embargo, con PDI, podemos automatizar esta tarea, creando una función genérica a la que le pasaremos los parámetros correspondientes en cada iteración. Vamos con ello…

El objetivo de esta práctica será crear un proceso de ETL que genere un fichero csv con los datos de una tabla, por cada uno de los días de la semana 22-28 de diciembre.

El modo clásico de crear esta ETL sería

  1. Creamos un Input con un where por fecha a la tabla
  2. Arrastramos el modulo de ouput a fichero de texto y lo configuramos.
  3. Vamos ejecutando este mini proceso, cambiando el where de la query, para los 7 días de la semana.

Bien, es fácil, pero imaginemos que en lugar de tener que hacerlo para siete días, necesitáramos hacerlo para los 365 días del año, ¿lo haríamos así? Puede ser, pero creo que merece la pena conocer como hacerlo de forma automática.

Crearemos un Job principal, añadiendo una transformation, el módulo Write to Log y un job.

job_principal

  1. GENERA_DIAS
    Esta va a ser la transformation donde definiremos el rango de fechas por el que luego iteraremos.
  2. Escribe en pantalla (log)
    Simplemente es para ver en pantalla que ha entrado en el bucle
  3. EXPORTA_POR_DIAS
    Esta es la función que se va a ejecutar n veces, tantas como fechas definamos en el paso de GENERA_DIAS. Es una función genérica preparada para recibir una variable y generar una query en función de ella.

El pase de diapositivas requiere JavaScript.

Analicemos en detalle la transformation y el job

GENERA_DIAS
Como decíamos anteriormente, esta transformation va a ser la encargada de generar un listado de fechas con el rango que nosotros le especifiquemos

  1. Fecha de inicio
    Módulo Generate Rows. Definimos un campo, su formato y su valor inicial. Será el punto de partida para la generación de fechas.
  2. Generador de días
    Modulo Get Value from Sequence. Lo hemos visto en otras ocasiones este módulo nos sirve para generar una cuenta incremental de numeros a partir de un valor inicial.
  3. Calculo de fechas
    Módulo Calculator. Uno de los módulos más interesantes de PDI, en esta ocasión nos sirve para ir sumándole el valor de módulo anterior a la fecha inicial, de tal manera que cuando este valor llegue a 6, por ejemplo, la fecha que obtendremos será 28/12.
  4. Generación de Fechas
    Módulo Select Values. Simple modulo que nos va a servir para definir los metadatos del campo fecha
  5. Copy rows to result
    Este módulo nos va a guardar en el flujo del proceso, el resultado de la transformación para poder usarlo fuera de ella.

EXPORTA_POR_DIAS
Este job es el que realmente ejecuta la función que necesitamos, que en definitiva no deja de ser una query contra una base de datos para exportar a CSV los datos necesarios. Contiene

  • EXPORTA_POR_DIAS
    Es la transformation que realiza la query, parametrizada, contra la base de datos y la exportación a CSV. Esta compuesta por

    • Get rows from result
      Aquí recogemos los valores generados en el paso GENERA_DIAS para usarlos en el proceso.
    • Calculator
      Necesitamos generar dos tipos de fechas en este paso, ambas copias de la fecha original.

      • fecha_txt: Es la fecha en formato String para añadirsela de manera dinámica al nombre del csv al final del proceso.
      • diaQuery: Es la fecha en formato Date que nos servirá para filtrar la query más adelante.
    • Set Variables
      Con este módulo, pasamos las fechas anteriores, que están generadas como campos, a variables para poder usarlas en los diferentes procesos
    • Table input
      Aqui generamos la query que atacará nuestra tabla, parametrizada con la variable diaQuery. En nuestra caso, quedaría algo así

      Como veis, cualquier variable puede ser pasada a una sentencia poniendo ${variable} y marcando el check Replace variables in script.

      check_variable

    • Select Values Seleccionamos los campos que queremos exportar y su metadatos antes de pasarlos por el output.
    • EXPORTA_CSV_POR_DIA
      Este paso exportará la query anterior, en cada iteración, a CSV. Es importante añadirle la variable al nombre del fichero, la que tenía formato de String ${fecha_txt}, para generar n CSV, tantos como días hayamos elegido en el paso de GENERA_DIAS.csv_por_dia
  • Escribe en pantalla (log)
    Lo usamos para enseña por pantalla, en cada iteración, la fecha que está resolviendo.log_pantalla

¿Que veremos en el log de PDI?
Es curioso ver, cuando lanzamos la ETL, lo que se  va escribiendo en la parte del log de PDI. Podremos ver perfectamente como se ejecuta la iteración en cada paso, en concreto en nuestro ejemplo, el paso EXPORT_CSV se ejecuta siete veces, una por cada día de la semana. Y como veis, el resultado son los siete ficheros CSV generados.

log_ejecucion_files

 

A partir de aquí las aplicaciones que tiene esta forma de configurar una ETL, creo que son infinitas. Por ejemplo, imaginad que tenemos una base de datos con jugadores de la NBA y queremos sacar un fichero CSV por cada jugador con los datos de toda su carrera, en lugar de tener la iteración por días, podriamos crear una query que devolviera los nombres de todos los jugadores de nuestra base de datos, con ello hacer la iteración y rápidamente conseguiríamos un fichero CSV con el resumen de cada uno.

Como siempre, os dejo aquí el link a los ficheros de PDI para que podáis probarlo vosotros mismos.

He quitado datos de conexiones, querys y todo lo relativo a los datos de mi base de datos por seguridad 😉

bucle_pdi

Espero que os sirva! 😉

 

Sergio Martín
Más de seis años de experiencia gestionando equipos y proyectos de Business Intelligence para diferentes sectores, apasionado de todos los procesos técnicos relacionados con la gestión de la información, el tratamiento de datos y la automatización de procesos

** Microstrategy 9.4 | Pentaho 5 (ETL) | Oracle 11g **

3 comments

  1. Buenas tardes amigo, me parece muy bueno el trabajo, he intendado replicarlo pero no me funciona, mi proceso es un tanto diferente, yo no quiero trabajar con fechas, yo quiero trabajar con diferentes campos de una tabla, los cuales con mi proceso quiero convertir en uno solo, y manejarlos al final como tipo catalogo (para lo cual ya tengo el catalogo, id_catalogo y nombre, el nombre identifica a cada campo de la tabla raiz, desnormalizado), pero para llegar a esto debo ejecutar el mismo query “n” cantidad de veces, 1 por cada campo de la tabla, entonces yo esperaba controlar esto en un solo query, seteando variables 1 para identificar el id de cada campo (que este pues al final es autoincremental), y otra para identificar el nombre del campo.
    Datos importantes:
    1. Como no es una fecha la que estoy trabajando, no utilice los módulos de calcular.
    2. En mi job principal también utilizo un set de variables, el cual ya tenía previamente seteado para trabajar los años de para los que debe ejecutarse mi query.

    Ejemplo:
    Tabla Raíz (como esta antes del etl)
    pais ciudad campo1 campo2 campo3 campo4
    Guatemala Escuintla 1 2 4 3
    Guatemala Tiquisate 2 3 2 1
    Guatemala San Jose 1 5 1 2

    Tabla Destino (como deseo que resulte despues del etl)
    Pais Ciudad nombre cantidad
    Guatemala Escuintla Campo 1 1
    Guatemala Escuintla Campo 2 2
    Guatemala Escuintla Campo 3 4
    Guatemala Escuintla Campo 4 3
    Guatemala Tiquisate Campo 1 2
    Guatemala Tiquisate Campo 2 3

    1. Estimado,
      Por si aun estas esperando una respuesta:
      Eso lo puedes lograr con un step llamado Row Normaliser
      te dejaria algo parecido a lo que necesitas:

      Pais Ciudad Campo Cantidad
      Guatemala Tiquisate Campo1 2
      Guatemala Tiquisate Campo2 3
      Guatemala Tiquisate Campo3 2
      Guatemala Tiquisate Campo4 1
      Guatemala San Jose Campo1 1
      Guatemala San Jose Campo2 5
      Guatemala San Jose Campo3 1
      Guatemala San Jose Campo4 2
      Guatemala Escuintla Campo1 1
      Guatemala Escuintla Campo2 2
      Guatemala Escuintla Campo3 4
      Guatemala Escuintla Campo4 3

      para obtener este resultado la configuracion del step es la siguiente:

      type Field: Campo

      Filds:
      Fieldname Type new field
      Campo1 Campo1 Cantidad
      Campo2 Campo2
      Campo3 Campo3
      Campo4 Campo4

      Saludos.

  2. Hola que tal, buenas tardes
    Tnego un proceso al cual le paso un id y que me inserta a 5 tablas
    cuando lo hago con el bucle le asigno 2 ids que tendran que pasar por todo el proceso.
    La corrida con el id 1 todo va bien, pero cuando se inserta en la ultima tabla de el 2do id(segunda corrida), no lo hace adecuadamente, de hecho inserta mas registros de los que deviera.
    que estoy haciendo mal?

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *