Lider del proyecto: Fabian Salazar Figueroa (https://github.com/alfa7g7)
Docente: Angela Villota (https://github.com/angievig)
| Equipo |
|---|
| Esteban Ordoñez (https://github.com/leoe21) |
| Raul Echeverry (https://github.com/RaulEcheverryLopez) |
El objetivo es construir el proceso ETL utilizando Pentaho Data Integration (PDI) con el fin de obtener un archivo csv, realizando el proceso de extracción y transformación de los datos disponibles en la base de datos relacional BD Ames, en archivos csv y en MongoDB.
Limite hasta el día viernes 14 de junio de 2024.
- Pentaho (integración de bases de datos)
- Mongo DB (para sistema de base de datos NoSQL)
- ElephantSQL (para sistema de base de datos relacionales postgreSQL)
- Excel (para lectura de archivos csv y xlsx)
Se descargan los archivos proveidos por la docente (archivo script y bases de datos) y se procede a crear una instacia en ElephantSQL bajo el nombre "FabianSF_DB".
El primer archivo que se carga en el browser de Elephant es el script scriptAmesDB.sql y posterior se cargan las tablas: floordetail, amesdbtemp, saleproperty, mssubclass, mssubclass, mszoning y typequality.
Se identifica la tabla de hechos y tabla dimensiones mediante el esquema proveido por la docente:
La tabla de hechos es: mesdbtemp y el resto son dimensiones (loordetail; mssubclass; mszoning; saleproperty; typequality).
Se realizan consultas para conocer cada tabla, algunos ejemplos basicos fueron:
select * from amesdbtemp;
select * from MSSubClass;
select * from MSZoning;
select * from TypeQuality;
select * from saleproperty;
select * from floordetail;
#Explorando conexion con MSZoning
SELECT DISTINCT "MS Zoning"
FROM amesdbtemp
order by "MS Zoning" asc;
#Explorando conexion con MS SubClass
SELECT DISTINCT "MS SubClass"
FROM amesdbtemp
order by "MS SubClass" asc;
Se complemento el entendimiento con el apoyo excel, copiando de cada tabla las primeras filas, el archivo excel se encuentra en https://github.com/MCD-Infrati/etlproject-infratech_24/blob/main/Postgre_understand/Muestreo_Bases.xlsx
En la base de Excel tambien se copió la base de muestra final para ir realizando el inventario de los campos.
Se enlaza MongoDB con la conexión proveida por la docente, dando como resultado 4 colecciones: bsmt, garage, misc y pool:
Se analizan los esquemas de cada una, para conocer el contenido de los documentos:
Con el uso de excel se procede a explorar el archivo AmesProperty.csv
Teniendo claro la información de cada fuente, se realiza el inventario final con respecto a la base resultado, como se muestra a continuación:
Los campos en amarillo son aquellos a los que se debia realizar un cálculo específico con el uso de consultas SQL en elephant para llevar dichos campos a la necesidad del ejercicio.
Se procede a cargar cada una de las bases en Pentaho:
La bases traídas desde Elephant, se cargan incluidas las consultas nesesarias y dando el orden y nombres identificados en el inventario de campos:
amesdbtemp en join con TypeQuality, MSZoning y MSSubClass y tranformación Gr Liv Area
Transformación FullBath, HalfBath, Bedroom
Se deben calcular teniendo en cuenta que una propiedad puede tener varios pisos. En la tabla FloorDetail se encuentran los datos de cantidad de habitaciones, y baños, por piso para cada propiedad. La transformación consiste en calcular el total de baños FullBath, HalfBath, Bedroom.
Transformación Mo Sold,Yr Sold
En la tabla saleProperty se encuentra la fecha de venta y se requiere que en la salida esté solo mes (Mo Sold) y año de venta (Yr Sold).
Year Remod/Add
Contiene la fecha de remodelación, si no tiene, en vez de dejarlo en Null colocar la misma fecha de construcción Year Built.
Garage, pool, bsmt, misc Al realizar el cargue de estos datos (garage, pool, bsmt, misc), si no se encuentran los datos, debe especificarse NA si es cualitativa y 0 si es numérica.
Para realizar esta transformación se toma como insumo la categorizacion de variables que ofrece el input de entrada de Mongo y mediante un nodo de tipo "If null" se da la instrucción de esta transformación:
Esto se implementa para cada base.
Como nota importante, no se reemplaza o se tranforma los nulos en la columna PID dado que es la llave para los merge con las demás fuentes e incluso en pasos anteriores se verifica la inexistencia de nulos.
Merge
El proceso ETL en Pentaho comienza con la base de datos Postgre_amesdbtemp, que se une inicialmente con floordetail y salesProperty. Para cada merge, se ordena previamente la columna PID en orden ascendente. Después de unir las bases de datos de PostgreSQL, se procede a hacer la unión con la base CSV, debidamente transformada y ordenada. Esta unión se realiza utilizando left outer join.
Finalizada y ordenada la unión con la base CSV, se comienza a unir con cada base de datos de MongoDB, transformada y ordenada: primero con la base bsmt, luego con la base garage, a continuación con la base misc y finalmente con la base pool. Cada unión se efectúa utilizando left outer join, garantizando que siempre se preserve la base madre.
Cada paso que se realiza, contiene una validación mediante una salida de excel dodne se revisaba cada resultado y así tener control de cada paso realizado en la transformación.
Estos test de validación se guardan en la carpeta "Test_Resultados"
Las uniones de las 3 fuentes de los datos se concluye en el test número 12, a partir de este punto se relizan las transformaciones propias de la sección opcional para neighborhood, Lot Shape y finalmente Conditional, esta última aplicada a dos columnas del datasaet "Condition1" y "Condition2"
Las transformaciones de Condition1 y Condition2, a diferencia de Neighborhood y Lot Shape, presentaban la particularidad de que algunos registros ya venían con una estructura de código, mientras que otros contenían descripciones. Debido a la longitud de las cadenas de texto, no fue suficiente utilizar un simple sort para realizar los merges. Por lo tanto, se tuvo que aplicar reglas condicionales adicionales y realizar reemplazos de strings, ya que algunos merges no eran efectivos.
Después de concluir todo el proceso, obtuvimos la base de datos con los parámetros exigidos por la docente, incluyendo el orden correcto de las columnas y las transformaciones pertinentes. El archivo resultante se puede observar en https://github.com/MCD-Infrati/etlproject-infratech_24/blob/main/Base_Resultado_Final.xlsx
- Comprensión de los Datos: Es crucial realizar un entendimiento profundo de la data antes de su tratamiento y transformación. Esto permite comprender la realidad del problema o necesidad y diseñar un proceso ETL más eficiente y preciso.
- Ventajas de las Bases Relacionales: De todas las fuentes de datos, la base de datos relacional permitió una carga más eficiente, ya que al incrustar la consulta en Pentaho, se podían realizar transformaciones preliminares directamente en la base de datos. Esto redujo la carga de trabajo en el plano del aplicativo.
- Importancia de la Validación: Es fundamental tener nodos de validación con salidas de archivos para realizar pruebas de cada merge. Aunque existen nodos que permiten merges entre varias fuentes a la vez, para este caso de decidió ir uno por uno con su debido test lo cual ayudó mucho a hacer el control de calidad de la transformación
- Limitaciones del procesamiento de Pentaho para validaciones con salida excel: Los tests que generaban archivos CSV tenían que desactivarse porque Pentaho no generaba la cantidad completa de registros esperados, solo la mitad. Esto podría deberse a la capacidad limitada de procesamiento de Pentaho. Se dejó esta cuestión abierta para ser clarificada por la docente.
- Desafíos con Merges de Cadenas Largas: Al realizar merges con cadenas de texto largas, como en los casos de Condition1 y Condition2, el merge no fue tan efectivo. Esto llevó a la necesidad de utilizar métodos condicionales y reemplazos de strings. Esta experiencia resalta la importancia de usar valores numéricos o cadenas de texto cortas como claves primarias, siendo preferibles los valores numéricos.
- Trabajo en Equipo: El trabajo en equipo es crucial en este tipo de proyectos, ya que los conocimientos multidisciplinarios ayudan a encontrar soluciones y a generar un orden adecuado para el proceso ETL. La colaboración efectiva permitió abordar los desafíos de manera más eficiente y creativa.
- Automatización del Proceso ETL: La automatización del proceso ETL a través de herramientas como Pentaho es esencial para manejar grandes volúmenes de datos de manera eficiente. Permite reducir errores humanos y garantizar la consistencia y precisión de los datos transformados.
- Rutas locales de archivos para el trabajo conjunto: Fue importante tener en cuenta que cada vez que un miembro del equipo deseaba abrir el archivo y ejecutarlo desde su máquina local, era necesario actualizar las rutas de los archivos para que el flujo funcionara correctamente. En GitHub, dejábamos las rutas locales de cada miembro para que solo fuera necesario copiar y reemplazar la ruta en cada nodo donde se presentaba este requisito, los cuales eran los archivos de entrada y salida local.
- Documentación del Proceso: Mantener una documentación detallada de cada paso del proceso ETL es fundamental. Esto facilita la identificación de problemas, la reproducción de los procesos y el mantenimiento del sistema a largo plazo.
- Uso de herramientas en paralelo: A través de python en Visual Studio Code se pudo realizar una revisión rápida y eficiente de cada conjunto de datos obtenido en los diferentes pasos a modo de validación, resultando una valiosa aliada la extención Data Wrangler, la cual nos permitía examinar de manera completa cualquier conjunto obtenido en la pruebas sin necesidad dei implentar tanto código solo bastaba leer el conjunto de datos y cargarlo como un dataframe. Así la herramienta con solo visualizar el dataframe ya nos arrojaba análisis estadístico y distribuciones de los datos.
Este ejercicio resultó interesante ya que permitió reforzar los conocimientos adquiridos en el módulo de Arquitectura e Infraestructura de TI. A través de la práctica, se pudo trabajar con diversos tipos de datos, desde la simplicidad de un archivo plano en CSV hasta la complejidad de bases de datos relacionales SQL y NoSQL. Esta experiencia práctica no solo consolidó nuestros conocimientos teóricos, sino que también nos permitió enfrentar y resolver retos reales en la transformación de datos, mejorando así nuestras habilidades en el manejo de diferentes estructuras y sistemas de bases de datos.