Cómo calcular MaxID y MaxDate en Pentaho

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:0017:26

Cómo calcular MaxID y MaxDate en Pentaho

Curso de Data Warehousing y Modelado OLAP

Clase 23 de 26

Clase 23 / 26

Recursos

Orquestar un flujo ETL implica coordinar extracción, transformación y carga en una sola ejecución controlada. Aquí aprenderás a construir un job en Pentaho que ejecute tus transformaciones en el orden correcto y, sobre todo, a calcular dos variables clave: el máximo ID y la máxima fecha de cada tabla del data warehouse. Esto te sirve si trabajas con cargas incrementales y quieres evitar reprocesar datos.

¿Qué es un job en Pentaho y por qué lo necesitas?

Un job es el orquestador que llama a cada transformación en el orden correcto. En lugar de ejecutar manualmente cada ETL, defines una secuencia que arranca con un start y avanza por las dimensiones antes de llegar a los hechos.

¿Por qué cargar primero las dimensiones? Porque la tabla de ventas necesita cruzar con clientes, productos, territorios, tiempo y vendedores para resolver los IDs. Si las dimensiones no están listas, el cruce falla.

El flujo que vas a orquestar incluye las transformaciones de clientes (que deja un archivo en S3 y carga vía copy), territorios, tiempo, vendedores, productos y, al final, ventas.

¿Cómo calcular el máximo ID de cualquier tabla con una transformación parametrizada?

La idea es no quemar el nombre de la tabla ni el campo en la consulta. Creas una transformación genérica que reciba dos parámetros: tabla y consecutivo. Así te sirve para clientes, productos, vendedores o cualquier dimensión.

Construye el input con variables

En un table input escribes una consulta del tipo select max(${consecutivo}) as consecutivo from dwh.${tabla}. Activa la opción para que reemplace variables en el script, porque si no, Pentaho lo toma como texto literal y falla.

Al ejecutar el preview con id_cliente y dim_clientes, si la tabla está vacía obtienes un nulo. Y aquí viene lo importante: necesitas un entero, no un nulo.

Convierte nulos en ceros y suma uno

Usas el paso de validación de nulos para transformar el resultado en cero cuando no hay registros. Después, con la calculadora, defines una constante B = 1 y calculas valor_maximo = A + B, donde A es el valor que llega de la consulta.

Si la tabla tiene 500 registros, devuelve 501.
Si la tabla está vacía, devuelve 1.
El resultado se asigna a la variable MaxRegistroID.

Ese MaxRegistroID es el que las otras ETL usarán como punto de partida para generar nuevos consecutivos.

¿Cómo identificar la fecha máxima para cargas incrementales?

La fecha máxima te permite traer solo los registros nuevos. Si ayer cargaste hasta cierto punto, hoy quieres consultar a partir de esa fecha en la base transaccional, no toda la historia otra vez.

¿Qué es un campo pivot? Es la columna de fecha que define desde cuándo traer datos en la siguiente ejecución. Suele ser ModifiedDate o una fecha de actualización del registro.

Creas otro table input con una consulta que use NVL o equivalente para devolver una fecha muy lejana cuando no hay registros previos. El truco es usar 1900-01-01 00:00:00 como valor por defecto, así la primera carga trae toda la historia sin condiciones especiales.

Setea la variable MaxDate

Después del cálculo, asignas el resultado a la variable MaxDate con el paso set variables. Pentaho te avisa que esa variable solo está disponible en transformaciones llamadas desde el mismo job, por eso la orquestación importa tanto.

Para probar la transformación en aislamiento, vas a Editar, Asignar variable de entorno, y defines valores de prueba:

consecutivo: 1.
tabla: dim_clientes.
MaxRegistroID: 1.
MaxDate: 1900-01-01 00:00:00.

¿Cómo aplicar las variables en cada transformación?

En la consulta input de cada dimensión agregas un filtro que use MaxDate. Por ejemplo, en clientes:

sql where c.ModifiedDate >= cast('${MaxDate}' as datetime)

El cast es obligatorio porque la variable llega como texto y la columna es de tipo fecha. Sin esa conversión explícita, la consulta falla o trae resultados inesperados.

Estandariza los nombres de variables

Un detalle que parece menor pero rompe el flujo: si en clientes nombraste la variable MaxRegistro y en productos MaxRegistroID, el job no puede pasar un solo valor a todas. Revisa cada transformación y unifica el nombre.

Productos: MaxRegistroID.
Territorios: MaxRegistroID.
Clientes: cambia de MaxRegistro a MaxRegistroID.
Vendedores y tiempo: no usan esta variable.

Con los nombres alineados, configuras una sola variable global y todas las transformaciones la leen.

Habilidades y conceptos que se trabajan

En esta sesión aparecen varias piezas que conviene tener claras antes de avanzar a la orquestación completa.

Job de Pentaho: contenedor que ejecuta transformaciones en secuencia y comparte variables entre ellas.
Transformación parametrizada: ETL que recibe tabla y consecutivo como parámetros, evitando código duplicado.
Reemplazo de variables en script SQL: opción que debes activar para que Pentaho sustituya ${variable} antes de enviar la consulta a la base.
NVL y validación de nulos: técnica para convertir nulos en valores por defecto (cero para enteros, 1900-01-01 para fechas).
Calculadora: paso que permite operaciones aritméticas como sumar uno al máximo ID encontrado.
Set variables: paso que expone un valor calculado al resto del flujo.
Campo pivot: columna de fecha que ancla la carga incremental.
Cast as datetime: conversión explícita necesaria cuando una variable de texto se compara con una columna fecha.
Carga incremental: estrategia que solo procesa registros nuevos o modificados desde la última ejecución.

Si quieres profundizar en ETL más allá de esta orquestación básica, revisa el curso de ETL con Python y Pentaho en la plataforma. ¿Cómo estás manejando hoy las cargas incrementales en tus proyectos? Cuéntame en los comentarios.

Mario Alexander Vargas Celis

Hace 2 años

En un proceso ETL (Extract, Transform, Load), los parámetros son valores dinámicos que controlan el comportamiento del flujo de trabajo y permiten flexibilidad y reutilización del proceso. Estos parámetros son fundamentales para manejar configuraciones, ejecutar procesos dinámicos y adaptarse a diferentes entornos (desarrollo, pruebas, producción).

A continuación, se detallan los conceptos clave y ejemplos de uso:

1. Tipos de Parámetros en ETL

a) Parámetros globales

Aplican a toda la herramienta o al proyecto ETL.
Configuran valores como rutas de archivo, nombres de base de datos, o claves de acceso.
Ejemplo: DB_HOST, DB_USER, DB_PASSWORD.

b) Parámetros de transformación o flujo

Aplican únicamente a una transformación o trabajo específico dentro del proceso ETL.
Ejemplo: Rango de fechas, valores límite, nombres de tablas.

c) Variables de entorno

Definidas en el sistema operativo o en configuraciones externas (por ejemplo, archivos .env).
Se usan para separar datos sensibles o configuraciones entre entornos.

d) Parámetros dinámicos

Se generan o calculan durante la ejecución de una transformación.
Ejemplo: Fecha actual (CURRENT_DATE), rutas dinámicas (concat('data_', CURRENT_DATE, '.csv')).

2. Usos Comunes de los Parámetros

a) Conexiones dinámicas a bases de datos

Permite usar diferentes configuraciones según el entorno:

DB_HOST=localhost DB_USER=admin DB_PASSWORD=1234

En la herramienta ETL, estas variables se referencian como ${DB_HOST}, ${DB_USER}, ${DB_PASSWORD}.

b) Rutas de entrada y salida dinámicas

En lugar de codificar rutas estáticas, se usa un parámetro para flexibilizar:

INPUT_PATH=/data/input OUTPUT_PATH=/data/output

Referencia en el flujo:

${INPUT_PATH}/file_to_process.csv

c) Filtrado o partición de datos

Se usan parámetros para aplicar filtros en el proceso de extracción o transformación:

Parámetro: START_DATE, END_DATE.
Consulta SQL en el paso de extracción:

SELECT * FROM ventas WHERE fecha BETWEEN '${START_DATE}' AND '${END_DATE}';

d) Procesamiento de lotes

Si tienes que procesar un lote específico:

Parámetro: BATCH_ID.
Uso en una transformación:

DELETE FROM tabla_procesada WHERE batch_id = '${BATCH_ID}';

3. Ventajas de Usar Parámetros

Flexibilidad: Permiten ajustar el proceso sin modificar el diseño del ETL.
Reusabilidad: Puedes reutilizar el flujo con diferentes configuraciones.
Separación de configuraciones: Ayudan a mantener las configuraciones separadas del código principal.
Seguridad: Las credenciales y datos sensibles se almacenan como variables externas.

4. Ejemplo Práctico en Pentaho Data Integration

Definir un parámetro

Ve a Edit > Settings > Parameters.
Agrega un nuevo parámetro, por ejemplo:
- Nombre: FILE_PATH.
- Valor predeterminado: /data/input/.

Usar el parámetro

En cualquier paso (como "Text file input"), usa ${FILE_PATH} para referenciarlo.
Durante la ejecución, puedes sobrescribirlo con un valor diferente, ya sea desde:
- La línea de comandos:kitchen.sh -param:FILE_PATH="/new/path/to/data/"
- O desde un trabajo que invoque la transformación.

5. Parámetros en Otras Herramientas ETL

Informatica PowerCenter: Define parámetros en los archivos de configuración o variables de sesión.
Talend: Usa contextos (Contexts) para gestionar parámetros.
Apache Nifi: Configura variables en el nivel del flujo de datos (Flow Variables).
SSIS (SQL Server Integration Services): Usa variables en paquetes de integración.

¿Te interesa implementar parámetros específicos en tu proceso ETL? ¿O necesitas ayuda para configurar algún caso práctico?

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

Cómo calcular MaxID y MaxDate en Pentaho

Progreso del curso

Introducción a BI y Data Warehouse

Modelos dimensionales

ETL para inserción en Data Warehouse

Cierre

Recursos

Recursos