Merge y lookup en Pentaho para tabla de hechos

Curso de Fundamentos de ETL con Python y Pentaho

Contenido del curso

Introducción a ETL

Extracción de datos

Transformación de datos

Carga de datos

ETL con Pentaho

Conclusión

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:0010:35

Merge y lookup en Pentaho para tabla de hechos

Curso de Fundamentos de ETL con Python y Pentaho

Clase 22 de 25

Clase 22 / 25

Recursos

Cerrar el proceso de transformación en Pentaho implica unir cada fuente con su identificador correspondiente para construir una vista minable lista para cargar a un data warehouse. Aquí verás cómo aplicar merge y lookup en Pentaho sobre una tabla de trades para enlazar dimensiones como flow, year, quality, country y codes, y dejar todo listo para Redshift.

¿Cómo se duplica un flujo de datos en Pentaho con Dummy?

Antes de aplicar los lookups necesitas una copia del stream principal de trades. La forma más limpia es usar un step Dummy, que no ejecuta ninguna lógica pero permite generar una réplica del flujo para trabajar en paralelo [01:00].

¿Para qué sirve el step Dummy en Pentaho? Es un step que no transforma datos. Se usa para duplicar un flujo, organizar visualmente la transformación o como punto de paso cuando necesitas mantener una rama disponible sin alterarla.

Podrías omitirlo y conectar directo, pero conocerlo te da flexibilidad cuando armes pipelines más complejos.

¿Cómo aplicar Stream Lookup para cruzar dimensiones?

El Stream Lookup es el corazón de esta etapa. Permite hacer un merge entre dos flujos usando una llave común y traer un valor específico de la dimensión hacia la tabla principal [01:20].

En este pipeline se crearon cinco lookups, uno por cada dimensión:

Lookup Flow: cruza el campo flow contra flow para traer el ID Flow.
Lookup Year: cruza el campo año contra año para obtener el ID Year.
Lookup Quality: cruza quality name contra quality name para traer el ID Quality.
Lookup Country: cruza el Country Code contra el Country Code para obtener el ID Country.
Lookup Codes: cruza el Com Code contra el Clean Code para traer el ID Code.

Cada lookup recibe el flujo principal de trades y una fuente secundaria que contiene la dimensión con su identificador.

¿Qué hacer cuando un lookup arroja error de campo?

Durante la ejecución apareció un error en el Lookup Country: el step buscaba contra un campo llamado Alfa 3 que en realidad se llamaba Country Code [04:30]. La solución fue validar el nombre exacto del campo en el select values de origen y ajustarlo en la configuración del lookup.

¿Por qué falla un Stream Lookup en Pentaho? Casi siempre por un nombre de campo mal escrito o porque el campo se renombró en un step anterior. Revisa el select values fuente y confirma que la llave coincida exactamente.

¿Cómo generar el ID de la tabla de hechos?

Una vez encadenados los cinco lookups, se agrega un step de secuencia llamado ID_Trades para asignar un identificador único a cada registro de la tabla de hechos [05:10]. Esta secuencia es la llave primaria que distinguirá cada transacción de exportación o importación.

¿Cómo construir la vista minable final con Select Values?

El último paso es filtrar solo las columnas que la tabla de hechos necesita. Se usa un step Select Values llamado Select Value Trades que conserva:

ID Trade como llave primaria.
Métricas: USD, kilos y cantidad.
Identificadores foráneos: ID Code, ID Country, ID Flow, ID Year e ID Quantity.

Con esto queda una vista minable que contiene un identificador de trade, todas las métricas del negocio y las llaves hacia las dimensiones de código, país, flow, año y quantity [06:00]. Es exactamente la estructura que espera un modelo dimensional en estrella.

¿Por qué Pentaho funciona para proyectos ETL de comercio internacional?

Lo interesante de Pentaho es que transformaciones que suenan complejas, como cruzar cinco dimensiones contra una tabla de hechos, se resuelven arrastrando cajitas y configurando llaves. No escribiste una sola línea de SQL para lograrlo.

La herramienta tiene decenas de stages adicionales para limpieza, validación, joins, agregaciones y conexiones a bases de datos. Cada uno se conecta con el mismo principio: entrada, configuración mínima, salida.

El siguiente paso del pipeline es la carga hacia Redshift, donde esta vista minable se convertirá en la tabla de hechos lista para análisis. ¿Cuál de estos stages te parece más útil para tus propios proyectos? Déjalo en los comentarios.

Resumen

¿Cómo se duplica un flujo de datos en Pentaho con Dummy?

¿Para qué sirve el step Dummy en Pentaho? Es un step que no transforma datos. Se usa para duplicar un flujo, organizar visualmente la transformación o como punto de paso cuando necesitas mantener una rama disponible sin alterarla.

Podrías omitirlo y conectar directo, pero conocerlo te da flexibilidad cuando armes pipelines más complejos.

¿Cómo aplicar Stream Lookup para cruzar dimensiones?

El Stream Lookup es el corazón de esta etapa. Permite hacer un merge entre dos flujos usando una llave común y traer un valor específico de la dimensión hacia la tabla principal [01:20].

En este pipeline se crearon cinco lookups, uno por cada dimensión:

Lookup Flow: cruza el campo flow contra flow para traer el ID Flow.
Lookup Year: cruza el campo año contra año para obtener el ID Year.
Lookup Quality: cruza quality name contra quality name para traer el ID Quality.
Lookup Country: cruza el Country Code contra el Country Code para obtener el ID Country.
Lookup Codes: cruza el Com Code contra el Clean Code para traer el ID Code.

Cada lookup recibe el flujo principal de trades y una fuente secundaria que contiene la dimensión con su identificador.

¿Qué hacer cuando un lookup arroja error de campo?

¿Por qué falla un Stream Lookup en Pentaho? Casi siempre por un nombre de campo mal escrito o porque el campo se renombró en un step anterior. Revisa el select values fuente y confirma que la llave coincida exactamente.

¿Cómo generar el ID de la tabla de hechos?

¿Cómo construir la vista minable final con Select Values?

El último paso es filtrar solo las columnas que la tabla de hechos necesita. Se usa un step Select Values llamado Select Value Trades que conserva:

ID Trade como llave primaria.
Métricas: USD, kilos y cantidad.
Identificadores foráneos: ID Code, ID Country, ID Flow, ID Year e ID Quantity.

¿Por qué Pentaho funciona para proyectos ETL de comercio internacional?

Mario Alexander Vargas Celis

Hace 2 años

Transformación de Datos con Pentaho: Parte 3

Ahora que hemos abordado cómo diagnosticar y solucionar errores comunes en las transformaciones de Pentaho, vamos a enfocarnos en cómo realizar una transformación de datos robusta y eficiente en un flujo más complejo.

1. Optimización de la Entrada de Datos

Asegúrate de que los pasos iniciales, como Table Input o CSV Input, estén correctamente configurados:

Conexiones a Bases de Datos:
- Verifica que la conexión esté activa y las credenciales sean correctas.
- Testea la consulta SQL en el paso Table Input y valida que devuelva datos esperados.
Archivos CSV:
- Usa el paso CSV Input para leer los archivos. Configura delimitadores, encabezados y codificación correctamente.

2. Limpieza de Datos

Pentaho ofrece múltiples pasos para transformar y limpiar datos antes de procesarlos:

Select Values:
- Renombra campos, elimina columnas innecesarias y ajusta tipos de datos.
String Operations:
- Modifica cadenas, como convertirlas a mayúsculas/minúsculas o aplicar expresiones regulares.
Replace Values:
- Sustituye valores nulos o incorrectos por predeterminados.

Ejemplo: Unifica valores inconsistentes en una columna Country.

USA -> United States US -> United States

3. Combinación de Datos

Utiliza pasos como Merge Join o Append Streams para consolidar datos de múltiples fuentes:

Merge Join:
- Realiza combinaciones basadas en una clave común (similar a un JOIN en SQL).
- Tipos soportados: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER.
Append Streams:
- Une dos flujos de datos con la misma estructura en uno solo.

Ejemplo de configuración de un Merge Join:

<join_type>INNER</join_type> <step1>Stream A</step1> <step2>Stream B</step2> <keys_1> <key>id</key> </keys_1> <keys_2> <key>id</key> </keys_2>

4. Transformación Compleja

Si necesitas realizar cálculos avanzados o transformar los datos:

Calculator:
- Realiza operaciones matemáticas como sumar, dividir o calcular porcentajes.
User Defined Java Expression:
- Aplica lógica personalizada usando expresiones en Java.
Modified JavaScript Value:
- Permite escribir transformaciones más complejas usando JavaScript.

Ejemplo: Convertir precios en USD a EUR usando una tasa de cambio.

price_eur = price_usd * 0.92;

5. Validación de Datos

Antes de cargar datos, verifica su calidad:

Filter Rows:
- Filtra registros según condiciones específicas.
Data Validator:
- Asegúrate de que los datos cumplan con criterios como valores no nulos o rangos aceptables.

6. Carga de Datos

Finalmente, carga los datos en el destino:

Table Output:
- Inserta datos en una base de datos (PostgreSQL, Redshift, MySQL, etc.).
S3 File Output:
- Almacena resultados en Amazon S3.
CSV File Output:
- Genera archivos listos para compartir.

7. Manejo de Errores

Configura el manejo de errores para evitar interrupciones en la transformación:

Haz clic derecho en un paso y selecciona Error Handling.
Define una salida secundaria para registrar errores y analizarlos posteriormente.
Almacena errores en un archivo CSV para su auditoría.

Ejemplo de Flujo Completo

Entrada:
- Paso Table Input para extraer datos de una base de datos.
Transformación:
- Usa Select Values para renombrar columnas.
- Usa Filter Rows para excluir datos inconsistentes.
- Aplica un Merge Join para combinar datos de dos fuentes.
Salida:
- Usa Table Output para insertar datos limpios en una base de datos destino.
- Genera un archivo de log con registros rechazados.

8. Ejecución y Verificación

Ejecuta la transformación con métricas activadas para monitorizar:
- Registros procesados.
- Registros escritos y rechazados.
- Tiempo de ejecución.
Analiza los logs de Spoon para confirmar que no haya errores.

Si necesitas ejemplos específicos o tienes un flujo particular que desees implementar, compártelo y podemos desarrollarlo en detalle. 😊

Introducción a ETL

¿Qué es un ETL en ingeniería de datos?

Conceptos clave de ETL: source, target y staging

Consideraciones clave antes de implementar ETL

Herramientas ETL: Pentaho, Pandas y más

Extracción de datos

Consideraciones clave al extraer datos en ETL

Configuración de base de datos source y entorno para ETL en Python

Extracción ETL con Pandas desde Postgres

Transformación de datos

Consideraciones clave antes de transformar datos ETL

Limpieza de códigos de productos con Pandas

Merge de países y productos con pandas

Cómo convertir un DataFrame en tabla de hechos

Carga de datos

Consideraciones clave antes de cargar a Redshift

Configuración de clúster en AWS Redshift

Crear tablas en AWS Redshift

Carga de CSV a Redshift con COPY

Estructurar sentence para carga de datos de S3 a Redshift

Carga masiva de CSVs a Redshift con Python

ETL con Pentaho

Instalación de Pentaho

Extracción de datos con Pentaho desde cero

Transformación de códigos con Pentaho

Transformaciones ETL en Pentaho sin código

Merge y lookup en Pentaho para tabla de hechos

Carga de datos ETL con Pentaho a Redshift

Conclusión

Apache Airflow como siguiente paso en ETL

Comparte tu proyecto de ETL y obtén tu certificado

Merge y lookup en Pentaho para tabla de hechos