16.3. Construccion procesos ETL utilizando Kettle (Pentaho Data Integration).
Posted by Roberto Espinosa en 10 May 2010
Antes de empezar el desarrollo de los procesos ETL con Pentaho Data Integration (Kettle), vamos a dar un repaso a la interfaz de usuario y a las características mas importantes del producto. Igualmente, veremos las herramientas de las que disponemos para las diferentes tareas a realizar. Existe una variada documentación del producto (mucha de ella en castellano):
- Documentación en castellano en la web de Pentaho: se ha traducido gran parte de la documentación original del ingles al castellano. Por ejemplo, tenemos el Manual de Usuario de Spoon, Documentación de usuario de Pan, Kitchen y Carte, Lista de funcionalidades, etc.
- Documentación en ingles: ademas de los elementos anteriores, podemos destacar la documentación de los Pasos – Steps (ver 3.1 y ver 3.2), Trabajos – Jobs (ver 3.1 y ver 3.2), lista de plugins disponibles, Pentaho Data Integration Home Page (News, Downloads, Forums, Bug tracking etc.), Getting Started, Pentaho Data Integration Case Studies, Pentaho Data Integration Recorded Demos, Pentaho Data Integration Screenshots, Pentaho Data Integration Articles, Pentaho Data Integration (Kettle) Tutorial, Monitorización de rendimiento, Tecnical Tips, Faq, Special database issues and experiences o Special Operating System issues and experiences.
- Libros: Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL, Pentaho 3.2 Data Integration- Beginner’s Guide.
- Blogs: Roland Bouman´s, Matt Casters, Nicholas Goodman, Samatar Hassan.
- Otros recursos: manual, grupo PDI en redopenbi.com, ejemplos online.
Historia de Kettle.
En el año 2001, el belga Matt Casters empezo el desarrollo de una herramienta para uso personal, consciente de las dificultades que habia tenido durante su experiencia laboral como constructor de Data Warehouse para la integración de sistemas. Durante los siguientes años, fue desarrollando la herramienta, primero utilizando Java y su librería gráfica AWT, para finalmente pasar a SWT. La herramienta fue añadiendo funcionalidades, acceso a bases de datos, tratamiento de ficheros y componentes hasta llegar a 2004 con la versión 1.2. El proyecto fue subido a Javaforge, donde la gente podía descargarselo y utilizarlo. En la versión 2.0 se incluyo un sistema de plugins para permitir el desarrrollo de conectores de Kettle con otros sistemas (como Sap) y en 2005 fue liberado el código y puesto a disposición de todos en Javaforge. El proyecto creció con rapidez y la comunidad se involucro en su desarrollo con mucha actividad, hasta entrar dentro de la orbita de Pentaho (al ser vendido por el autor), que lo incluyo como herramienta ETL en su suite de productos. Matt Caster ha estado desde entonces trabajando en Pentaho y desarrollando su arquitectura como parte del equipo de Pentaho, interveniendo en las diferentes versiones hasta llegar a la 3.2 (y el desarrollo de la nueva versión 4.0, que saldrá durante 2010).
El nombre de Kettle viene de KDE Extraction, Transportation, Transformation and Loading Environment, pues originariamente la herramienta iba a ser escrita para KDE, el famoso escritorio de Linux. El producto ha sido renombrado como Pentaho Data Integration y a partir de ahora nos referiremos a el como PDI.
Programas que forman la herramienta.
PDI esta formado por un conjunto de herramientas, cada una con un proposito específico.
- Spoon: es la herramienta gráfica que nos permite el diseño de las transformaciones y trabajos. Incluye opciones para previsualizar y testear los elementos desarrollados. Es la principal herramienta de trabajo de PDI y con la que construiremos y validaremos nuestros procesos ETL.
- Pan: es la herramienta que nos permite la ejecución de las transformaciones diseñadas en spoon (bien desde un fichero o desde el repositorio). Nos permite desde la linea de comandos preparar la ejecución mediante scripts.
- Kitchen: similar a Pan, pero para ejecutar los trabajos o jobs.
- Carte: es un pequeño servidor web que permite la ejecución remota de transformaciones y jobs.
Trabajando con ficheros Xml o repositorio.
Cuando trabajamos con Spoon, tenemos dos formas de guardar los elementos que vamos diseñando:
- Repositorio: disponemos de una base de datos, con una estructura especial, donde son guardadas las transformaciones y trabajos construidos. Puede ser útil para el trabajo en equipo y para disponer de un lugar centralizado donde se va registrando todo lo realizado.
- Ficheros: las transformaciones y trabajos son guardados a nivel del sistema de ficheros, en archivos xml (con extensión .ktr para las transformaciones y .kjb para los jobs). Cada transformación y trabajo tiene un fichero asociado, que incluye en formato Xml el metadata que define su comportamiento.
Aunque seleccionemos uno u otro tipo de repositorio, siempre tendremos la opción de convertir de uno a otro modo utilizando componentes de PDI. Veremos un ejemplo de conversión del repositorio cuando terminemos el diseño de los procesos ETL. No se puede trabajar simultaneamente con los dos métodos, por lo que siempre habrá que elegir uno en concreto.
Concepto de transformación.
La transformación es el elemento básico de diseño de los procesos ETL en PDI. Una transformación se compone de pasos o steps, que estan enlazados entre si a través de los saltos o hops. Los pasos son el elemento mas pequeño dentro de las transformaciones. Los saltos constituyen el elemento a través del cual fluye la información entre los diferentes pasos (siempre es la salida de un paso y la entrada de otro). En el ejemplo de la imagen, en el primer paso estamos recuperando registros de una tabla de la base de datos, y los registros recuperados van siendo transmitidos a los siguientes pasos a través del salto, y se van realizando operaciones sobre los datos con los diferentes pasos incluidos.
Tenemos un amplio repertorio disponible de pasos que nos permiten abordar casi cualquier necesidad en el diseño de nuestros procesos de integración de datos. Los pasos estan agrupados por categorías y cada uno de ellos esta diseñado para cumplir una función determinada. Cada paso tiene una ventana de configuración especifica, donde se determina los elementos a tratar y su forma de comportamiento.
Una transformación no es ningún programa ni un ejecutable, simplemente es un conjunto de metadatos en XML que le indican al motor de PDI las acciones a realizar.
Concepto de trabajo o job.
Un trabajo o job es similar al concepto de proceso. Un proceso es un conjunto sencillo o complejo de tareas con el objetivo de realizar una acción determinada. En los trabajos podemos utilizar pasos especificos (que son diferentes a los disponibles en las transformaciones) como para recibir un fichero via ftp, mandar un email, ejecutar un comando, etc. Además, podemos ejecutar una o varias transformaciones de las que hayamos diseñado y orquestar una secuencia de ejecución de ellas. Los trabajos estarían en un nivel superior a las transformaciones.
Los saltos o hops entre los componentes de un job indican el orden de ejecución de cada uno de ellos ( no empezando la ejecución del elemento siguiente hasta que el anterior no ha concluido). El paso de un componente del job a otro también puede ser condicional, según si el resultado de ejecución ha sido correcto o no (tal y como vemos en el ejemplo de la imagen).
Al igual que las transformaciones, un job no es ningun programa, es también un conjunto de metadatos en XML, que le describen al motor de PDI la forma de realizar las diferentes acciones.
Interfaz de usuario.
La interfaz de usuario es muy sencilla, disponiendo de dos perspectivas. Una de visualización (view), donde vemos los componentes que forma el job o la transformación, y otra de diseño (Design), donde vemos los pasos disponibles. Según estemos trabajando con transformaciones o con trabajos, los steps disponibles irán cambiando. En la imagen, podeis ver la perspectiva Diseño. A la izquierda tenemos los diferentes pasos que iremos arrastrando a la sección de la derecha (grid de diseño). Los pasos tanto de transformaciones como de trabajos los iremos enlanzando con los correspondientes saltos.
En la imagen siguiente,vemos la perspectiva Vista o View. En forma de arbol visualizamos los diferentes elementos utilizados en el diseño de la transformación o trabajo. También observamos las conexiones a base de datos (que podrán ser propias de un solo trabajo o transformación), o compartidas por todos. Haciendo doble click en cada uno de los elementos podemos acceder a la configuración de sus propiedades.
En la parte derecha de la pantalla (la cuadricula), también podemos acceder a las propiedades de cada elemento con doble click o bien con el menu contextual del botón derecho del ratón. Todo de una forma muy sencilla e intuitiva.
Steps disponibles para las transformaciones.
Disponemos de un amplio conjunto de pasos para las transformaciones. Las categorías y pasos disponibles son los siguientes:
Steps disponibles para los trabajos.
Respecto a los trabajos, disponemos de los siguientes pasos, agrupados en las correspondientes categorías:
General
|
|
File management |
Conditions |
Scripting |
|||||
Bulk Loading
|
XML |
File transfer
|
Repository
|
A continuación vamos a ir desarrollando los diferentes procesos ETL para la carga de nuestro DW. En ellos iremos profundizando en las funcionalidades de PDI, lo que nos permitira realizar una comparativa final entre PDI y Talend Open Studio.
Bibliografía utilizada para esta entrada
Pentaho 3.2 Data Integration - Beginner´s Guide. Maria Carina Roldan. Pentaho Community - Documentación Online.
<job-jobentries>
<entry>
<name>Envio Email OK</name>
<description>Envio Email OK</description>
<type>MAIL</type>
<server/>
<port/>
<destination/>
<destinationCc/>
<destinationBCc/>
<replyto/>
<replytoname/>
<subject/>
<include_date>N</include_date>
<contact_person/>
<contact_phone/>
<comment/>
<include_files>N</include_files>
<zip_files>N</zip_files>
<zip_name/>
<use_auth>N</use_auth>
<use_secure_auth>N</use_secure_auth>
<auth_user/>
<auth_password>Encrypted </auth_password>
<only_comment>N</only_comment>
<use_HTML>N</use_HTML>
<use_Priority>N</use_Priority>
<encoding>UTF-8</encoding>
<priority>normal</priority>
<importance>normal</importance>
<secureconnectiontype>SSL</secureconnectiontype>
<replyToAddresses/>
<filetypes> </filetypes> <parallel>N</parallel>
<draw>Y</draw>
<nr>0</nr>
<xloc>369</xloc>
<yloc>54</yloc>
</entry>
</job-jobentries>
Valen said
que tal?
estoy empezando con kettle, venia trabajando con SSIS, pero me tope con la duda de como hacer un insert de una BD a otra dentro de kettle, o sea:
tengo una conexion A con una tabla X y una conexion B con una tabla Y
debo hacer un select de A.X y el resultado insertarlo en B.Y
pero no se como especificar el origen de los datos al hacer la sentencia SQL
encontre este post:
http://forums.pentaho.org/showthread.php?t=60137
pero sinceramente no entiendo
agradeceria tu ayuda
saludos.
nota buen blog!!
Roberto Espinosa said
Es muy sencillo, Valen.
En primer lugar, defines las conexiones a base de datos. Ahi indicas el tipo de base de datos a la que te vas a conectar, usuario, contraseña, esquema, etc. En tu caso, tendras que definir dos, una para la base de datos A y otra para la base de datos B.
A continuación, te crearas un paso del tipo Table Input para la lectura de la tabla A. En la configuración del paso, seleccionaras las conexión correspondiente de las que has definido anteriormente. Y añades en SQL la sentencia SQL para la recuperación de datos (puedes usar también el asistente para traerte la tabla y sus campos a la sentencia). La sentencia SQL la puedes modificar para indicar las correspondientse condiciones en el where:
SELECT
INFNR
, ESOKZ
, MWSKZ
FROM SAPR3.EINE
WHERE INFNR = ‘0’ AND MWSKZ = ‘S3’
A continuación, los datos que hayas leido los iras pasando por otros pasos (para hacer las transformaciones o cambios que quieras realizar).
Como paso final, indicaras el paso para insertar en la tabla destino. Pentaho te da varias posibilidades (todos en la categoria Output):
Table Output
Insert/Update
Update
Si estas traspasando registros nuevos y no tienes que mirar nada, puedes utilizar el Table Output. En el caso de que sean actualizaciones, tendras que ver los otros dos pasos.
Tienes un ejemplo de utilización de este paso en la entrada del blog:
Espero que te sea de utilidad.
Yessica Lopez said
Hola que tal? Me estoy Iniciando en la migración de datos con kettle y estuve leyendo sobre en la entrada o input la opcion de obtención de nombres de fichero y me gustaría aplicarlo quería saber si de casualidad tenias conocimiento sobre como hacer para pasar del proceso de la obtención del fichero a entrada XBase para posteriormente generar la salida Cualquier respuesta te la agradeceré.. Feliz día!
Roberto Espinosa said
Hola Yessica:
A ver si te he entendido.
Quieres leer una serie de ficheros, luego procesarlos (porque son Xbase) y luego generar la salida hacia otro formato de salida. Creo que podrias hacer lo siguiente:
1) Con el paso Get FIle Names obtienes los nombres de los ficheros que quieres procesar. Deberan de indicar la carpeta donde estan los ficheros y la expresión regular (en el caso de que tengas varias ficheros con nombres variables). Si los ficheros son de nombres fijos, los puedes añadir uno a uno. Acuerdate en la pestaña Filters marcar el flag «Add filename to result». Asi se pasan los nombres de los ficheros al paso siguiente.
2) El segundo paso será uno del tipo Xbase input. Muy importante, para recibir el nombre de fichero del paso anterior, marcar el flag que pone «Accept filenames from previous» e indicar el nombre del paso anterior en «Step to read filenames from».
El paso siguiente será alguno para procesar la información que lees de los ficheros o el paso de salida (Output), según el formato destino. Por ejemplo, Excel o alguna base de datos como destino.
Espero haberte sido de ayuda.
Rodolfo said
hola sabes no se muxo sobre este tema si me puedes ayudar, se trata de hacer una transformacion de mi b.d. transaccinal (oracle) a etl, sin hacer repositorio, sin job,me podrias decir como crear un dw a partir de mi base de datos, y cuales son las conecciones para hacerlo paso a paso, sobre como crear esa transformacion.
tengo la version 4.0.1
ayudame please,
rivelino_21@hotmail.com
ese es mi msn, muchas gracias bye.
Roberto Espinosa said
Hola Rodolfo:
En este blog tienes toda la serie de entradas para construir un DW…No es un proceso que te pueda explicar en un minuto. Primero tendras que decidir que quieres analizar, que modelo tiene y construir la base de datos de dicho modelo. Luego tendras que analizar tus sistemas origen para ver que datos tienes que extraer y hacer las transformaciones para pasar del Oracle Original a la base de datos del DW. FInalmente, tendras que explotar el DW con las correspondientes herramientas. Te recomiendo la lectura de la serie de entradas de mi blog para hacer este trabajo.
Un saludo.
Leandro said
Hola, me podrian ayudar, lo que necesito es un componente en el que pueda hacer una consulta sql durante la transformacion, ya probe con el table input, pero me da un bateo cuando voy a ejecutar el script o el lookup que le sigue al componente, no importa en que parte de la transformacion lo ponga, siempre el componente que le sigue me da error, lo que necesito es un componente donde pueda hacer una consulta sql select, para mas tarde mapear ese valor que obtuve.
Roberto Espinosa said
Hola Leandro:
Puedes mirar este ejemplo en mi blog:
En el se utiliza el componente table input, con una imagen donde puedes ver como se configura el control:
1)COnexión: la base de datos a la que te vas a conectar.
2) Sentencia SQL: que determina los datos que vas a recuperar de esa base de datos y de que tabla.
Espero que te sea de ayuda.
En la misma entrada del blog te puedes descargar la transformación completa (fichero .ktr), cuyo link esta al final de dicha entrada.
Leandro said
Si ese proceso yo lo conozco, la cuestion es la siguiente:
Yo estoy migrando de una tabla a otra, utilizo el table input, donde utilizo una consulta select para extaer todos los atributos de la tabla origen, eso es al inicio de la transformacion, pero mas tarde necesito, hacer otra consulta sql select a otra tabla de la base de datos y utilizo otro table input, pero al parecer cuando utilizo 2 table input en una misma transformacion da algun tipo de bateo, y los componentes que utilizo a continuacion del segundo table input tienen problemas con la definicion de variables o sea que el sistema no llega a reconocerlas. La pregunta es:
Existe algun otro componente que pueda usar para hacer la consulta sql que no sea otro table input?
Realmente no se pueden usar dos table input en una misma transformacion ?
Roberto Espinosa said
No entiendo porque no te funciona. Aqui te dejo ejemplos donde se utilizan dos table inputs o mas:
1) Desde un table input se llama a otro posterior (para desde la cabecera de un pedido obtener las lineas): https://churriwifi.wordpress.com/2010/05/30/16-3-4-etl-carga-de-hechos-de-ventas-con-pdi/
2) Utilizacion de Database Lookup: a partir de los datos de un table input, ejecuto unp osterior databas lookup para recuperar valores de otra tabla(por ejemplo, descripciones). Los tienes en la entrada:
En este ejemplo también tienes un ejemplo del paso Execute SQL script que te permite ejecutar sentencias SQL construidas en tiempo de ejecución.
Te puedes descargar todas las transformaciones. Y como ves, tienen varios table input y funcionan, porque han sido utilizadas en un sistema de pruebas.
Un saludo.
Leandro said
Bueno Roberto, por eso estoy preguntando si existe algun otro componente donde pueda usar una consulta select sql, ya que en el componente «Execute sql Script» solo se pueden usar consultas Insert, Update y Delete, mi objetivo es obtener un atributo para mapearlo al final de la transformacion.
Saludos y Gracias por la ayuda.
Leandro said
Tengo otra duda, me hace falta cojer de un listado de fechas, la ultima de ellas, la pregunta es si en un componente java script hay alguna funcion que me ayude a obtener la fecha que yo quiero.
Saludos y gracias por la ayuda
Roberto Espinosa said
Hola Leandro:
Supongo que se puede hacer de muchas maneras. Una de ellas sería ordenar la lista de fecha con el control Sort Rows (si realmente te hace falta ordenar la lista) y luego quedarte con la ultima fecha de la lista con el control Last Row in a stream (creo que puedes marcar con un campo que un registro sea el ultimo de un flujo).
A ver si con este enfoque lo puedes conseguir.
Un saludo.
Leandro said
Muchas gracias por la ayuda voy a intentar con esos componentes a ver si lo consigo.
Saludos
Leandro said
Ah roberto tienes alguna guia o manualito que me enseñe mas o menos como trabajar con esos componentes.
Roberto Espinosa said
Lo siento, no tengo ninguna documentacin sobre ellos. Mira en la web de pentaho a ver que hay.
Germain Venzal said
Hola,
me estoy metiendo al mundo de spoon, el cual me ha gustado mucho hasta ahora.
Despues de terminar de configurar las distintas transformaciones y los trabajos, pude realizar mi labor con éxito. Luego, al moemento de pasar mis transformaciones al ambiente de desarrollo, tube que volver a configurar TODAS las conecciones a base de datos, transformación por transformación. Como en realidad utilizo dos conecciones a base de datos, no existe la posibilidad de crear las conecciones a base de datos «Globales» ? para solo tener que modificar estas y no cada una en cada transformación ?
Gracias,
Germain
Roberto Espinosa said
Hola Germain:
La forma de hacer las conexiones globales (o compartidas) es muy sencilla. Cuando estés trabajando con Spoon, en la parte izquierda de la pantalla (tienes dos vistas, la View y la Design). En la primera de ellas, veras una carpeta que pone conexiones a base de datos. Seleccionas la conexión que quieras compartir y pulsando el botón derecho, seleccionas la opción compartir. Verás que cambia el color de la conexión (se pone en negrita). Con eso haces disponible la conexión a todas las transformaciones.
A nivel de sistema, esta información se guarda en un fichero llamado shared.xml, que esta en el directorio(carpeta) .kettle que estará en tu carpeta de usuario dentro del sistema operativo. Esto te vale para llevarte todas las conexiones de un sistema a otro, llevandote el fichero y dejandolo caer en el directorio apropiado.
Un saludo
Germain Venzal said
Gracias Roberto,
la verdad que es de grán ayuda tu respuesta, apenas llegue a casa lo pruebo.
Abrazo,
Germain
Germain Venzal said
Hola,
En primera instancia no me funcionaba. Defini mi conección global y no la veia en las otras transformaciones …
Fui a ver el archivo shared.xml y este estaba vacio, solo tenia el tag de sharedobjects …
Finalmente usando la neurona que me quedaba guardé el trabajo sobre el cual estaba cuando defini las conecciones globales, y recien ahi el archivo shared.xml se llenó.
Haciendo la prueba nuevamente, tampoco funcionó, asi que cerré el spoon y la abri nuevamente.
Ahora si me anda !!!
Gracias por la ayuda,
Saludos,
Germain
Renier Gutierrez Prendes said
hola tengo un problema con el spoon y quizas alguien de ustedes me pueda ayudar el caso es que estoy haciendo una migracion de SQL Server a PostgresSQL y por ejemplo de la tabla persona necesito obtener a partir de un numero de identidad la fecha de nacimiento de la persona su sexo etc….esta operacion la realizo utilizando la funcionalidad Modified Java Script Value y utilizo el siguiente codigo
var aux = CI; // CI es el numero de identidad de la persona ej:86102410983 donde la fecha de nac es 24/10/1986 el decir los primeros 6 digitos
var dia = substr(aux,4,2);
var mes = substr(aux,2,2);
var anno = substr(aux,0,2);
var fech_nac = dia+».»+mes+».»+anno;
fech_nac = str2date(fech_nac,»dd.MM.yyyy»); //cambio del tipo de dato de string a date
cuando trato de obtener las variables me saca el sgte error:
Could not apply the given format dd.MM.yyyy on the string for v.st.20te : Format.parseObject(String) failed (script#18)
como puedo cambiar el tipo de dato de entrada(CI) para poder realizar la operacion????
Agradeceria si alguien me pudiera ayudar en esto saludos
Germain Venzal said
Hola,
creo que el problema que estas teniendo es mas de javascript.
Has intentado con el constructor Date ?
var fecha_nac = new Date(anno,mes,dia);
Espero que te ayude
Saludos, Germain
Germain Venzal said
Mirando tu funcion un poco mas de cerca, veo que tu variable anno es cargada con el valor 86, lo cual no respeta el formato yyyy …. tu problema puede venir de ahi tambien … una prueba que podes hacer para descartar esto es asignarle a tu variable anno un valor fijo ‘1986’ por ejemplo, y si funciona, ya sabes que es eso.
Saludos,
Germain
Renier Gutierrez Prendes said
gracias Germain lo del for4mato del año yo le adiciono el 19 o 20 lo que no puse el codigo porque no pense que fuera relevante voy a probar con el constructor date a ver si me funciona muchas garcias de veras te agradesco tu ayuda
sebas said
quiero transformar un carpeta que contiene archivos txt separado por coma, a una base de datos cualquiera. Mi pregunta es como puedo hacer para seleccionar el directorio donde estan esos archivos y realizar la transformacion automaticamente, ya que individualmente los puedo transformar, pero selecionando de a uno cada uno de los archivos txt. y yo lo quiero hacer automaticamente.
gracias
Germain Venzal said
Hola,
no tengo el spoon delante de mi, pero de memoria, me parece que tenes herramientas para ir a un directorio especifico e iterar por sus archivos … capas que dichas herramientas no están entre las transformaciones pero si entre los trabajos.
Esta noche, verifico y te confirmo.
Saludos,
Germain
Roberto Espinosa said
Hola Sebas:
Lo podrias hacer en una transformacion con dos pasos:
1)Primer paso: lees los nombres de fichero de un directorio determinado, utilizando el paso Obtener nombres de fichero. Ahí le puedes indicar uno o varios directorios a leer, y una expresion regular para limitar los ficheros que coges (por ejemplo, solo los que acaben en la extension txt).
2)Segundo paso: es el paso de lectura del contenido del fichero (CSV File input,por ejemplo o cualquier otro). Este paso recibira el nombre del fichero del paso anterior.
Espero que te valga esta solucion.
Un saludo.
antonio said
buenas!
Tengo un problemita, a ver si me pueden ayudar.
tengo que realizar una aplicacion que coja de un fichero una tabla y modifique la cabecera de la tabla (el nombre de cada columna), para ello me dan otro fichero de configuracion que tiene dos columnas (en una el nombre de la columna a modificar y en la otra el nuevo nombre que deseamos poner). no soy capaz de realizar la tarea, separo la tabla en columnas diferentes, incluso cojo solo el nombre de la columna, pero luego no soy capaz de compararlos, debido a que con javaScript no me deja. me da error por llegarle dos enlaces con diferente nombres de campos, y si es con el mismo nombre no peude hacer la comparacion.
a ver si me pueden decir como podria hacerlo, gracias
Roberto Espinosa said
Hola Antonio:
El proceso que tienes que realizar es bastante complejo. Te recomiendo el libro de Maria Carina Roldan sobre Pentaho Data Integration donde se plantean ejemplos complejos similar a lo que tu tienes que realizar (link al libro: http://www.packtpub.com/pentaho-3-2-data-integration-beginners-guide/book).
De todas formas, yo haría algo así:
1) Lectura del fichero de la tabla (se supone que la primera linea es las que vas a procesar para cambiar los nombres de los campos): solo lees las primera linea.
2) El registro leido en el punto 1( por ejemplo NOmbre_campo1, NOmbre_campo2, Nombre_campo3) lo tienes que convertir tantas lineas como nombres de campo tengas. Eso lo harias con el control Row Normaliser (http://wiki.pentaho.com/display/EAI/Row+Normalizer).
Pasarias de un registro con el formato:
Nombre_Campo1 | Nombre_campo2 | Nombre_campo3 | Nombre_campo4 |……
A un formato:
Nombre_Campo1
Nombre_campo2
Nombre_campo3
Nombre_campo4
……
3) Lectura del fichero donde tienes las tuplas: nombre de la columna a modificar, nombre de la nueva columna.
4) Utilizando el step Stream Lookup (http://wiki.pentaho.com/display/EAI/Stream+Lookup), buscas para cada nombre de campo su equivalente: este step recibe los dos flujos que has definido en el paso 2 y 3. Vas a construir una secuencia de salida de sera:
Nombre_Campo1 | Nuevo_nombre1
Nombre_campo2 | Nuevo_nombre2
Nombre_campo3 | Nuevo_nombre3
Nombre_campo4 | Nuevo_nombre4
……
5) Eliminas la primera columna: lo puedes hacer con el paso Selec Values (http://wiki.pentaho.com/display/EAI/Select+Values).
6) Ahora desnormalizarias los registros (juntas todos los registros en uno).Pasarias de un registro con el formato:
Nombre_Campo1
Nombre_campo2
Nombre_campo3
Nombre_campo4
……
A un formato:
Nombre_Campo1 | Nombre_campo2 | Nombre_campo3 | Nombre_campo4 |……
Creo que lo puedes hacer con el step Denormalizer (http://wiki.pentaho.com/display/EAI/Row+De-normalizer).
7) Ya tendrias la linea de cabecera correcta construida. La podrias insertar en un fichero de texto. Y en otro paso leerias el fichero original, quitarias la primera linea, y el resto las añadirias a este fichero de texto donde ya tienes la cabecera correcta.
es un poco complicado, como ves, pero seguro que se puede hacer.
Un saludo.
Juan said
Hola
Estoy realizando una transformacion con pdi y quiero capturar en un fichero la salida de una consulta select a la base de datos y no puedo, lo mas que he conseguido es capturar el numero de lineas que lee en cada consulta. como puedo hacerlo? porq poniendo el fichero detras de un javascript sql con la consulta no funciona, no lee la salida o por lo menos no la escribe.
gracias
Roberto Espinosa said
No se que paso estaras utilizando, pero en teoría utilizando lo siguiente debería de funcionarte:
1) Lectura de la base de datos: utilizando un step Table Input (http://wiki.pentaho.com/display/EAI/Table+Input) donde indicas la tabla y la sentencia SQL que vas a utilizar para leer. EN la select defines los campos que utilizas.
2) La salida del primer step la pasas a un paso para generar el fichero: bien un fichero excel (con el paso Excel Output: http://wiki.pentaho.com/display/EAI/Excel+Output o bien a fichero de texto con el paso Text file output: http://wiki.pentaho.com/display/EAI/Text+File+Output).
No tiene mas complicación.
Gabriela said
Hola, me podrian ayudar, necesito tomar los datos de una tabla en excel y mostrarlos en pentaho. Pero no se como hacerlo, alguiern q me pueda explicar o talvez algun manual donde pueda enocntrar eso. Gracias
Roberto Espinosa said
Hola Gabriela:
Para hacer lo que tu quieres, tendrías que leer el fichero Excel utilizando un paso o step Excel Input (http://wiki.pentaho.com/display/EAI/Excel+Input).
A continuación, lo que hubieras leido lo introduciras en una tabla de la base de datos que estes utilizando (MySql, Access, Oracle, lo que sea), utilizando el step Table Output (http://wiki.pentaho.com/display/EAI/Table+Output).
A partir de ahí, ya tendrias los datos disponibles para atacarlos utilizando las herramientas de BI de Pentaho (que supongo que es lo que quieres hacer).
Un saludo.
Elbio said
Hola! mi pregunta es si dada una transformación puedo ejecutarla desde, por ejemplo la línea de comandos, y pasarle parámetros. Realmente no me queda claro como trabajar con parametros.
Gracias por el sitio, es muy útil.
Yosuan Hernandez said
Hola Elbio, sí se puede ejecutar desde la línea de comando tanto una transformación como un trabajo. Para ello se utilizan los script Kipchen y Pan que aparecen el directorio de instalación de Kettle. Prueba moviendote para ese directorio en la consola de comandos y ejecutar Kitchen o Pan y aparecerán las opciones que puedes utilizar.
Buena suerte.
Un saludo.
Roberto Espinosa said
Hola Elbio:
Tal y como indica Yosuan, las transformaciones que definas con Spoon, las puedes luego ejecutar a nivel de linea de comando, pasandoles parámetros. Para ello, utilizas las herramientas Pan (http://wiki.pentaho.com/pages/viewpage.action?pageId=11869063) y Kitchen (http://wiki.pentaho.com/pages/viewpage.action?pageId=11869458). Con Pan ejecutas las transformaciones y con Kitchen los trabajos o jobs.
Y puedes pasar los parametros que necesites para personalizar el comportamiento de los procesos.
Yosuan Hernandez said
Hola
Tengo una transformación con un step Sorted Merge. El problema es que cuando doy clic en el botón de Verificar la transformación me da el siguiente warning:
[3 – Warning] Join
Este paso no se ha verificado todavía: no esta implementado.
No se si es un problema que pueda tener consecuencias mayores durante la ejecución de la transformación o si puedo omitirlo.
Por favor, me pueden ayudar.
Saludos.
Germain Venzal said
Hola,
estamos teniendo un pequeño problema en la carga de nuestros datos. Tenemos varias transformaciones que utilizan tres conecciones globales.
Estas tres conecciones son a tres bases de datos distintas : fuente, intermedia, data warehouse.
Desde los datos fuentes tenemos distintas tablas con mas de 2.000.000 de registros. La pregunta es la siguiente : causa algún problema (tiene alguna contraindicación) utilizar una sola conección para una misma base de datos ? ya que la carga se hace secuencial (distintas transformaciones dentro de un mismo trabajo van cargando de funte –> intermedia).
Otra pregunta, la fuente de datos, pertenece a una base de datos postgres. Cuando hacemos un select * from la_tabla; directamente desde psql, obtenemos un lindo «out of memory for query result» …. tengo alguna forma de fechear los datos desde el kettle ? si la respuesta es afirmativa, como lo harían ?
Espero sus respuestas,
atentamente,
Germain
Roberto Espinosa said
Hola Germain:
Respecto a la primera pregunta, no tiene ninguna contraindicación utilizar la misma conexión para acceder a varias tablas. La conexión es la forma de decirle a Kettle donde te vas a conectar, de forma general, y no tendria sentido tener que definir una conexion para cada tabla (no es lógico).
Respecto a tu segunda pregunta, supongo que estas hablando de volumenes de información tan grandes que hacen que te quedes sin recursos en tu sistema. ¿Cuales son las caracteristicas de tu equipo?.¿Tienes un fichero de paginación apropiado?.
Si ese no es el problema, Kettle permite trabajar con particionado. Se define a nivel de transformación, y puedes partir la información tratado en «partes», por un criterio determinado, para optimizar el rendimiento.
Primero se configura a nivel de propiedades de la transformación. Luego a nivel de cada paso, con el boton derecho (opcion Particionado). No la he utilizado nunca, igual en la web de pentaho tienes mas información sobre la forma de utilizar esta funcionalidad.
Un saludo, Germain.
Germain Venzal said
Hola Roberto,
en primer lugar quiero agradecer tu participación activa en este foro, brindándonos siempre soluciones.
Ahora paso a contarte las vueltas que hemos dado con este problema …
Luego de buscar en muchos lugares la causa del problema, nos vino la idea de que podía llegar a ser un tema de la conexión global, pero lo descartamos … aunque gracias por reafirmarnos la idea.
Estamos utilizando un openSUSE 11.4 (x86_64) VERSION = 11.4 CODENAME = Celadon como sistema operativo dentro de una máquina virtual. Estábamos trabajando con 2.5 GB de RAM y 5 GB libres en disco.
Monitoreando estos recursos, nos dimos cuenta que el problema surgía en cualquier momento, independiente de los recursos consumidos.
La solución, que funcionó parcialmente, ya que la máquina se sigue trancando pero con menos frecuencia es la de reducir el número de columnas que queremos cargar. Solucion trivial si es que no vamos a utilizar algunas de las columnas, aunque en un futuro podriamos querer esta información ….
Cargando una de estas tablas 10 veces seguidas (haciendo pruebas de carga), se trancará 3 veces en promedio. Lo cual implica que no tenemos aun una solución, ya que no podemos hacer una carga completa de nuestro DW sin que se tranque antes al copiar a nustra base de datos temporal. No descartamos como decís que sea un problema del sistema operativo, o su estrategia de paginación o algo por el estilo, pero tengo que reconocer que no es mi fuerte andar jugando con las configuraciones del equipo.
La prueba que vamos a hacer hoy es utilizar otro sistema operativo (Ubuntu 10.04.1 LTS), con el cual en ambientes mas reducidos he tenido mejores resultados.
No tenia idea de que existía ese concepto de particionado por partes con un criterio … me parece algo extremadamente interesante, y lo voy a investigar mas.
Muchas gracias por tu ayuda nuevamente,
Atentamente,
Germain Venzal
Roberto Espinosa said
Hola de nuevo, Germain:
Viendo la configuracion que me comentas, 2,5Gb de Ram y 5 Gb de Disco, ya te adelanto que no estais trabajando de forma correcta.
En cualquier sistema, se recomienda, si tienes X de memoria Ram, que tengas 4X de archivo de paginación (particion SWAP en Linux). Eso da margen al sistema operativo para poder paginar, sobre todo si estas moviendo volumenes de información muy grandes. El area Swap es fundamental para un correcto rendimiento del sistema y que hayan los recursos suficientes.
Entiendo que deberias de enfocar la solucion por ese lado. En esta maquina virtual deberias de tener mas espacio para esa particion, y darle el tamaño apropiado.
Y luego tambien saber que para trabajar con mnaquina virtuales que funcionen, es necesario tener la maquina que alberga el software (no se si estaras con VMWARE) con unas condiciones de hardware apropiadas, pq sino la maquina virtual y el software que la gestiona se queda sin recursos. Yo he tenido alguna experiencia en ese sentido.
Espero que encuentres una solución a tu problema.
Un saludo.
Maria said
Hola,
Yo empecé a trabajar con Kettle – Spoon 3.2.5, pero ahora he de hacerlo con la versión 4.
Con la nueva versión se cambia de esquema.
¿Alguien conoce algún sistema para traspasar Transformaciones y Jobs hechas con la versión 3 al nuevo esquema de una forma «rápida»?
Gracias
María
Roberto Espinosa said
Hola Maria:
Yo creo que no tienes ningún problema en trabajar con los ficheros de las transformaciones y jobs en ls version 4 de Spoon (Pentaho Data Integration).
Solo tienes que abrir los ficheros. Si las transformaciones las tienes en un catalogo en base de datos (en lugar de ficheros), deberían de verificar que no han cambiado la estructura de este catalogo.
Por lo demas, puedes trabajar sin problema. Puede que te hayan cambiado algun step, que tendras que ajustar, pero en principio en la version nueva lo que hay son nuevas funcionalidades y controles que lo hacen mas completo, así como una mejora de la interfaz de usuario.
Un saludo.
erluak said
Buenas tardes,
Soy nuevo con kettle y tengo una duda.
Tengo 3 pasos.
1.- En el primero con «información del sistema» obtengo la fecha del primer día del mes anterior.
2.- En el segundo, con js, creo las variables dia,mes y anyo, donde relleno su valor con sus correspondientes valores. (supongamos dia=01, mes=01, anyo=2011).
3.- En el tercer paso, creo un «Get data from XML» e intento utilizar el valor de anyo y mes para localizar una serie de ficheros concretos.
Para ello utilizo el apartado «Selected files» y en la columna «Wildcard (RegExp)» pongo lo siguiente:
120_sent_${anyo}${mes}04.xml
Pero me arroja un error:
Caused by: java.util.regex.PatternSyntaxException: Illegal repetition near index 9
120_sent_${anyo}0404.xml
Veo que no coge el valor de la variable, y supongo que será porque no la paso bien.
¿me podéis ayudar a utilizar el valor de la variable obtenida en el paso anterior?
Muchas gracias.
Un saludo,
Ismael.
Germain Venzal said
Hola, creo que esto podria ser porque de tu javascript no pones como variable de salida anyo, o que esa variabe esté vacia.
Verificá que esté anyo (ya que se ve que mes te anda porque es remplazada), si está mirá que tiene la variable, podes usar la función Alert dentro del javascript.
Espero que ayude,
Saludos, Germain
lorena said
tengo un proyecto, debo extraer de una maquina fisica donde tengo una base de datos en oracle y hacer el proceso ETC y cargar en una base de datos de oracle en una maquina virtual(vmware), me podrian indicar donde hago las conexiones a las distintas maquinas? utilizando data integration el kettle
Roberto Espinosa said
Tu empezaras en Kettle diseñando una transformación. En la sección o ventana View (Vista) de la transformación, tienes una carpeta que se llama conexiones a base de datos. Ahí tendrás que definir todas las conexiones que te hagan falta y que luego utilizaras en los pasos de la transformacion.
En principio, las conexiones a base de datos que definas ahí son locales a la transformación. Si quieres que sean globales, has de seleccionarlas y con el botón derecho del ratón, seleccionar la opción Compartir. Eso hace que se guarden en el fichero de parámetros de Kettle y esten disponibles para todas las transformaciones que definas.
Espero que te sea de utilidad y suerte con tu proyecto.
maday said
hola , soy nueva en esto de almacenes y me encuentro en la parte donde estoy haciendo el Etl , me dijeron que consta de 34 fases , he buscado pero no veo nada concreto, necesito saber cuales son esas fases o que me digan si el comentario fue erroneo.
Saludos
maday
Germain Venzal said
Hola,
me parece que el comentario que te hicieron es erroneo.
En los ETL principalmente lo que haces es obtener los datos de una o varias fuentes, que puede ser mediante una conección a una base de datos, o de un archivo. Procesarlos como te convenga. Aveces necesitas difinirte una mapeo, para despues insertarlos en tu base de datos de destino (dase de datos del DW por ejemplo).
En esta sección tenes definidos unos ejemplos, y en la parte «Steps disponibles para las transformaciones» te indica como se utiliza cada elemento disponible para hacer tus transformaciones.
Espero que ayude, saludos,
Germain
jfmartinez said
Hola, soy nuevo utilizando PDI Spoon y deseo saber sí puedo crear unas tablas en Oracle desde Access, es decir, las tablas y la data que están en MS Access, transferirlas a un esquema Oracle. ¿Esto se podrá?
Roberto Espinosa said
Se puede hacer sin problema. El imputado leerá de Access y la salida será un paso que insertada en una tabla orales, dentro de la misma transformación. Incluso podrás añadir pasos intermedios si quieres transformar los datos de alguna manera, filtrar, normalizar o desnormalizar.
Un saludo.
cristina said
Hola a todos,
Quisiera consultar pues tengo un problema, tengo una tabla de access que quiero guardar en un xml, esta tabla tiene un dato que es double. asi que en spoon la definí como bigint y en el archivo xml la recibe como string, pero el problema es que agrega ceros a los datos.
¿alguien sabe como solucionarlo?
de antemano gracias
RCastillo said
Estimado , primero que todo los felicito por este excelente sitio.
Soy nuevo en PDI Spoon (v3.1) y tengo un problema, necesito cargar unas tablas de Access a Oracle (ya esta resuelto) , el problema esta en que en cada carga debo pasar un parametro que corresponde al periodo de carga (fecha en formato yyyymmdd) y este debe ser agregado a cada unas de las filas de la tabla que esta en oracle ….. ¿que se puede hacer?
Saludos.
vgv said
hola son vgv estoy comensando con lo del bi para un trabajo q estoy realizando como pasante de una empresa pero la verdad q no entiendo mucho ya estoy creando las dimensiones pero tengo q en base a una fechas que tienen formato de fecha y hora tengo q hacer operaciones de calculos de tiempo pero no se como realizarlas. espero me puedan ayudar estoy trabajando con kettle.
de antemano muy agradecido por cualkier ayuda
Suricato said
Hola…soy bastante nuevo en esto de Pentaho….mi problema es el siguiente:
De un archivo de entrada necesito agregarle un campo adicional que contenga el numero de registro que es partiendo de 1…hasta «n».¿existe alguna funcion que realice esto automaticamente? Es porque tengo que matchear mi archivo de entrada contra uno de errores de una aplicaicón externa que no me devuelve la clave del registro con error sino que me devuelve el numero de linea que contenia el error.
Gracias!!
Suricato said
Me autorespondo lo solucione agregando un campo con la utilidad «Transformar» –> «Añadir secuencia»
Javier said
Soy nuevo en el mundo del Spoon pero quiero saber soble el componente Synchronize After Mege, por ejemplo como se utiliza dento de la transformación, ejemplos de su uso en un flujo de trabajo y si tienen una pequeña descripción de este seria genial
Mº José said
Buenos días Roberto, he llegado a su blog tratando de dar respuesta a una duda que me ha surgido en Kettle. Sea dicho de paso es la primera vez que voy a usar esta herramienta para cosas algo más complejas que pasar información de una tabla a otra y me gustaría contar con alquien con más experiencia que me aconseje qué camino seguir.
En mi caso, necesito comparar dos esquemas, una vez que los esquemas son comparados si son iguales entonces por cada dupla de tablas realizo una serie de transformaciones y finalmente estos resultados son guardado en una de las dos tablas (digamos que actualizo una de las tablas).
Es decir, si E1=E2 –> Cojo X1 y X2 —->Realizo operaciones—-> Guardo en X2
Donde Ei son los esquemas y Xi las tablas de cada esquema.
Para comparar esquemas he usado, y no sé si eso es lo más correcto, dos elementos del tipo «Get table names» y ambos elementos los uno a otro elemento único «Búsqueda en Flujo de Datos».
Hasta donde yo sé lo que me hace es comparar cada uno de los nombres de las tablas de E1 con los de E2, eso funciona, porque si cambio de esquema me da error. Pero por ejemplo, me da la impresión de que eso no tiene en cuenta ni si tienen el mismo número de columnas, ni si son el mismo tipo de datos…
creo que se limita a que se llamen igual y eso no es exactamente lo que quiero.
¿Existe alguna forma más fiable de comparar correctamente dos esquemas? Es decir que tengan el mismo número de tablas, que se denominen igual, que cada tabla tenga los mismos campos y del mismo tipo….
En cuanto al siguiente punto, me gustaría saber si se puede realizar una transformación para cada dupla de tablas X1 y X2 de forma automática, es decir que los nombres los tome del paso anterior y no tenga yo que seleccionar en el elemento, por ejemplo, «Fundir Filas», el nombre de la tabla ya que en este caso estoy añadiendo la posibilidad de equivocarme.
Muchas gracias por su blog y su atención.
Jonathan camargo said
Buen día, tal vez a pasado mucho tiempo desde tu pregunta pero posiblemente puedas tener contacto con tu correo registrado…. De tal forma quisiera saber cual fue la manera que resolviste tu inconveniente… Pues me dispongo a realizar una comparación de tablas fuentes a tablas fact para saber si los datos están bien.
Reynaldo Vidal Rodriguez said
Programadores me veo enredado en una situación y necesito ayuda, necesito saber como hacer un parser para los fichero ktr (XML) generados por la herramienta Pentaho Data Integration, gracias de antemano.
establishing blog said
I do believe all the ideas you’ve introduced to your post. They are really convincing and will definitely work. Still, the posts are too brief for beginners. May just you please extend them a bit from next time? Thanks for the post.
Marvel said
Buenas espero que me puedan ayudar con un problema que tengo, lo que quiero es migrar una bd a otra con el asistente de copia de tablas pero las tablas estan en esquemas. estoy tratando de especificar el esquema de la fuente de una base de datos en las opciones de conexión pero por alguna razón me sigue cojiendo solo el esquema public, es decir me dice que la tabla no existe porque no esta en public porsupuesto, quisiera saber si es un error del pentaho y como se pudiera solucionar. Gracias de antemano.
Rodrigo said
Buena.
Estimados, me gustaria saber si alguno de ustedes sabe como puedo cargar o crear variables de entorno dependiendo del resultado de una consulta en mi base de datos mysql.
La idea es generar variables de entorno de manera dinámica según el nombre y valor que este guardado dentro de una base de datos.
Muchas gracias !!
Jaume said
Buenas!
Tengo una dudilla.. En mi caso tengo 2 valores con dos fechas distintas y lo que yo quiero es comprobar si la diferencia entre una y otra es superior a 6 meses..
Gracias!
Anmelicri said
Hola!!!
soy muy nuevo trabajando con Pentaho y necesito una ayuda, ¿como puedo introducir en un Data Validator una comparacion con una variable o con un valor que he recuperado de una tabla?, es decir, como puedo introducir en un minimun value el valor, no me funciona poniendo el campo de la tabla o la variable???
jUAN dIEZ said
Con las etl estoy teniendo un problema de tablas bloqueadas , resulta que paso datos de una bd a otra con una etl, y en el servidor el proceso se vé como COMMITTED READ , como hago para que sea DIRTY READ …
Hernán Molina said
Buenos días,
Estoy cargando un archivo .txt a una base de datos sql. El problema es que existen varias columnas en el archivo plano con valores negativos, pero el signo lo tienen a la derecha del número,por ejemplo (251.212-). Cuando cargo los datos a mi tabla no me los reconoce como negativos, simplemente los carga pero sin el signo.
La única manera que he encontrado para que me cargue el signo correctamente es pasar el archivo .txt a excel, pero eso no lo quiero hacer porque todos los días debo cargar aproximadamente 10 archivos txt a mi tabla cada uno con 100 mil registros y me parece dispendioso hacer esa conversión de archivos.
Por favor me pueden ayudar para saber cual es la forma para que me cargue correctamente los datos a mi base de datos provenientes del archivo .txt que tiene el signo negativo a la derecha.
Muchas gracias
raymond said
hola soy nuevo en esto de trabajar con pdi.Quisiea que alguen me ayudara quiero hacer una migración de datos de access para postgresql pero las tablas no tienen ni el mismo nombre ni estructura.En access tengo una tabla A con dos campos: id y descripcion y en postgres tengo una tabla B con los campos: name,code y otros mas, lo que quiero es pasar los datos de A.id para B.code y de A.descripcion para B.name.
Alguien que me ayude por favor aunqye sea con ejemplos…
Gracias
karizelko said
Hola,
Disculpen las molestias estoy trabajando con pentaho, soy nueva y conozco poco, hice mis Transformaciones sin ningún problema (Fichero > Nuevo > Transformación) y las guardo en una carpeta dentro de mi PC.
Pero ahora quiero hacer que se ejecuten solas a determinada hora del día, pero nose que pasos seguir, ni de que manera tendría que hacerlo.
Agradecería mucho si pueden ayudarme.
property maintenance said
Thanks in support of sharing such a pleasant thinking, article is pleasant, thats
why i have read it entirely
Irisandy Fernández said
Hello
How can I get the result of a step, whether it is successful or unsuccessful?
Sole said
Hola necesito enviar un archivo .csv por pentaho, pero debe contener registros desde el ultimo dia de mes anterior al actual hasta penultimo dia del mes actual. Ejm: fecha>=2018-03-30 and fecha<='2018-07-30'
Tengo que considerar los años bisextos los fines de mes de 31, 30, 29 o 28 dias dependiendo del mes, para que se mantenga esa regla de ultimo dia mes anterior y penultimo dia mes actual.
Alguien ha realziado algo similar.
Cristina said
Saludos, yo juego a los dardos desde que tengo uso de razón. Mi consejo es que si quieres comprar una diana, busques algo de calidad en web como estas.
Alicia said
Buenas, vosotros tambien teneis adversidades para encontrar informacion en castellano sobre bullet journal? Si es asi, les sugiero que le echeis un ojo a esta web si no la conoceis ya. Es de lo poco que hay que no sea en ingles.