Cruce de fuentes en Pentaho con Stream Lookup

Curso de Data Warehousing y Modelado OLAP

Contenido del curso

No sé dónde empezar

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

26
De datos crudos a modelo dimensional listo
01:18 min

Tomar examen

Cursos Empresas Blog Live Conf Eventos Precios

0:009:25

Cruce de fuentes en Pentaho con Stream Lookup

Curso de Data Warehousing y Modelado OLAP

Clase 19 de 26

Clase 19 / 26

Recursos

Conectar Pentaho con tu base de datos transaccional y tu data warehouse es el paso que separa una consulta SQL de un proceso ETL real. Aquí verás cómo llevar tu query a Pentaho, configurar conexiones a Postgres y Redshift, y cruzar fuentes para identificar registros nuevos antes de cargarlos en una dimensión.

¿Cómo crear una transformación y un Table Input en Pentaho?

Todo arranca con una transformación nueva. En Pentaho haces clic en el ícono de más, eliges transformación y se abre el lienzo de trabajo en blanco.

Desde ahí, el primer paso es traer los datos desde la base transaccional. Para eso usas un Table Input, que es el step encargado de ejecutar una consulta SQL contra una conexión definida y devolver el resultado como un flujo de filas.

Buscas Table Input en la lista de steps disponibles [01:00].
Lo arrastras y lo sueltas en el lienzo.
Pegas el query que ya tenías construido desde la clase anterior.
Lo renombras como Input clientes para identificarlo.

Ese query es el mismo que escribiste en SQL, así que no cambias la lógica: solo la trasladas al motor de Pentaho para que se ejecute como parte del flujo.

¿Cómo configurar la conexión a Postgres y a Redshift?

Un Table Input sin conexión no sirve. Necesitas decirle a Pentaho dónde vive la base de datos.

Para la fuente transaccional, creas una conexión nueva y eliges Postgres como motor. Llenas el host (en este caso localhost), el nombre de la base, el usuario y la contraseña. El nombre de la base aquí es adventureworks, todo en minúsculas, y ese detalle importa: si pones la W en mayúscula, el test de conexión falla.

¿Por qué falla la conexión a Postgres en Pentaho? Casi siempre es por el nombre de la base de datos mal escrito o por mayúsculas y minúsculas. Postgres distingue entre AdventureWorks y adventureworks, así que revisa la grafía exacta.

Una convención útil es nombrar la conexión como con_postgres_adventureworks, porque cuando tengas varias conexiones en el mismo proyecto vas a agradecer poder distinguirlas de un vistazo.

Para la fuente del data warehouse repites el proceso, pero eliges Redshift como motor [05:30]. Le pones un nombre tipo con_redshift_wh, ingresas servidor, base, usuario y password, y haces Test para validar.

¿Cómo extraer los datos del data warehouse para compararlos?

No basta con traer el transaccional. También necesitas leer la dimensión que ya existe en el warehouse, porque ahí está la verdad sobre qué registros son nuevos y cuáles ya viven en el modelo dimensional.

Agregas un segundo Table Input, lo conectas a Redshift y apuntas al esquema wh_adventureworks sobre la tabla de dimensión cliente. Si escribir el SQL a mano se complica, Pentaho te deja usar el botón Get SQL select statement, que recorre las tablas disponibles y arma la estructura por ti [08:45].

De todos los campos disponibles, te quedas solo con los que necesitas para el cruce:

El ID de la dimensión, para reutilizarlo en registros existentes.
El código del cliente, que es la llave de negocio.
La fecha de actualización, para saber cuándo cambió el registro por última vez.

Al hacer preview, la tabla puede venir vacía si es la primera carga. Eso es esperable y no rompe el flujo.

¿Cómo cruzar dos fuentes con Stream Lookup en Pentaho?

Con las dos extracciones listas, una desde Postgres y otra desde Redshift, llega el momento de cruzarlas. Para eso usas un Stream Lookup, que toma un flujo principal y lo enriquece buscando coincidencias contra un flujo de referencia.

La lógica es directa: el flujo principal es el Input clientes del transaccional, y el flujo de referencia es la dimensión del warehouse. La llave de comparación es el código del cliente en ambos lados.

¿Qué es un Stream Lookup? Es un step que cruza dos flujos por una llave común y devuelve campos del flujo de referencia. Si no encuentra coincidencia, puedes definir un valor por defecto.

Cuando configures el lookup, le pides que traiga el campo ID de la dimensión. Y aquí está la jugada inteligente: si ese ID viene nulo, le asignas un valor por defecto de -1 y lo defines como tipo entero [13:20].

Ese -1 es una bandera. Te dice, sin ambigüedad, que ese registro no existe todavía en la dimensión y que en el siguiente paso del ETL habrá que generarle un ID nuevo. Los que sí trajeron un ID real conservarán ese mismo identificador, porque ya están en el warehouse.

Habilidades, conceptos y datos clave de la clase

La lectura técnica de lo que acabas de hacer se apoya en varios conceptos que conviene tener nombrados.

Table Input es el step de Pentaho que ejecuta SQL contra una conexión y entrega filas al flujo [01:00].
Conexión a base de datos se configura por motor (Postgres, Redshift) con host, base, usuario y password [03:15].
Esquema en Redshift, en este caso wh_adventureworks, agrupa las tablas del modelo dimensional [07:50].
Stream Lookup cruza dos flujos por una llave y trae campos del flujo de referencia [12:10].
Valor por defecto -1 marca registros nuevos que no existen en la dimensión [13:20].
Fecha de actualización permite rastrear cuándo cambió por última vez un registro en la dimensión.

Con el cruce resuelto, ya tienes separados los registros viejos de los nuevos. El siguiente paso será aplicar las transformaciones y cargar todo en la base de datos dimensional. ¿Tú cómo nombras tus conexiones en Pentaho cuando manejas varios entornos? Cuéntalo en los comentarios.

Mario Alexander Vargas Celis

Hace 2 años

La extracción de datos en Pentaho se refiere a la capacidad de obtener datos de diversas fuentes y transformarlos en el formato adecuado para su almacenamiento o análisis. Pentaho ofrece diversas herramientas para realizar este proceso, y una de las más utilizadas es Pentaho Data Integration (PDI), también conocido como Kettle.

Pasos básicos para realizar una extracción en Pentaho Data Integration (PDI):

Abrir Pentaho Data Integration:
- Ejecuta el programa Spoon, que es la herramienta de diseño de trabajos y transformaciones en Pentaho.
Crear una nueva transformación:
- En Spoon, haz clic en Archivo > Nuevo > Transformación.
Conectar con una fuente de datos:
- Para extraer datos desde una base de datos (por ejemplo, PostgreSQL, MySQL, etc.), arrastra el paso "Base de datos de entrada" (Database Input) desde el panel izquierdo (en "Design") a la pantalla de trabajo.
- Haz doble clic en el paso para configurarlo.
- Configura la conexión a la base de datos:
  - Selecciona el tipo de base de datos.
  - Ingresa las credenciales de conexión: host, puerto, base de datos, usuario y contraseña.
Escribir una consulta SQL:
- En la ventana de configuración de "Base de datos de entrada", ve a la pestaña "Consulta SQL".
- Aquí puedes escribir una consulta SQL para extraer los datos que necesitas.
  - Por ejemplo, si deseas obtener datos de una tabla específica, podrías escribir algo como:SELECT * FROM my_table WHERE condition = 'value';
Ejecutar la consulta:
- Haz clic en el botón "Test" para verificar que la consulta funciona correctamente.
- Si la prueba es exitosa, los datos serán extraídos y podrás ver los resultados.
Transformar los datos (opcional):
- Puedes aplicar transformaciones a los datos extraídos utilizando otros pasos disponibles en Pentaho (como "Filtrar filas", "Agregación", "Modificar campos", etc.) si necesitas limpiar o modificar los datos antes de cargarlos.
Guardar los datos:
- Una vez que los datos sean extraídos (y transformados si es necesario), puedes cargarlos en otra base de datos, un archivo CSV, o cualquier otro destino que elijas.
- Arrastra el paso "Base de datos de salida" o "Archivo de salida" (como CSV File Output o Text File Output) a la pantalla de trabajo.
- Configura el destino de los datos, por ejemplo, seleccionando un archivo CSV para guardar los datos extraídos.
Ejecutar la transformación:
- Finalmente, haz clic en el botón "Ejecutar" en la parte superior para ejecutar la transformación.
- Los datos se extraerán, transformarán (si se configuró) y se cargarán en el destino que hayas configurado.

Ejemplo de una transformación básica:

Entrada: Consulta SQL desde una base de datos PostgreSQL.
Transformación: Filtrar y renombrar columnas.
Salida: Exportar los datos a un archivo CSV.

Consejos adicionales:

Si necesitas trabajar con archivos planos (CSV, Excel), Pentaho también permite extraer datos de estos tipos de archivos utilizando el paso "Archivo de entrada" (CSV File Input).
Para fuentes de datos más complejas (como APIs REST o Web Services), Pentaho tiene pasos como "HTTP Request" y "Web Service Consumer".

Con estos pasos básicos, deberías poder configurar y realizar una extracción de datos en Pentaho.

Introducción a BI y Data Warehouse

Qué es Business Intelligence y Data Warehousing

Niveles de analítica y jerarquía de datos

Qué son data warehouse, data mart y dimensiones

OLTP vs OLAP: diferencias clave

Inmon, Kimball y Hefesto en BI

Modelos dimensionales

Data Warehouse, Data Lake y Lakehouse

Modelo estrella vs copo de nieve en datos

Tipos de dimensiones lentamente cambiantes

Dimensión tipo 1: sobrescribir sin guardar historia

Dimensión tipo 2

Dimensión tipo 3: historia en columnas

Tabla de hechos (fact)

Configuración de herramientas para Data Warehouse y ETL

Cómo extraer dimensiones de preguntas de negocio

Diseño de tablas en un modelo dimensional

ETL para inserción en Data Warehouse

Documento de mapeo en ETL para data warehouse

Creando tablas dimensionales en Redshift

Extracción: querys en SQL

Cruce de fuentes en Pentaho con Stream Lookup

Transformación ETL con Pentaho paso a paso

Carga de datos transformados a Redshift con Pentaho

Cómo cargar la tabla de hechos con Pentaho

Cómo calcular MaxID y MaxDate en Pentaho

Orquestar ETL en Pentaho: job

Solución ETL con dimensiones en paralelo en Redshift

Cierre

De datos crudos a modelo dimensional listo

Cruce de fuentes en Pentaho con Stream Lookup

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos