How cvincent27/de_05_etl_vlille_spark works
Project Type & Purpose
• This project is focused on extracting data from an external API (dataMEL), cleaning and transforming it using PySpark, and then loading it into a MySQL database. Airflow is used to orchestrate the ETL (Extract, Transform, Load) process, and PowerBI is mentioned for subsequent data reporting.
• It is essentially a data pipeline project that involves external integration, data transformation, and automation/orchestration.
Analyzing the File Structure
• Top-level items:
• Files like .gitattributes, .gitignore, and requirements.txt indicate standard project configuration.
• DE_ETL_VLille.ipynb suggests that there may be notebook demonstrations or testing workflows.
• The README provides the overview of the pipeline steps and technical stack.
• Directories and key files:
• The “config” directory (and spark_config.json) and settings files hint at centralized configuration management.
• The “src” folder contains core pipeline scripts: “extract.py”, “transform.py”, “load.py”, and “config.py”. These clearly map to the three main phases of the ETL process.
Key Components
a. External Data Source:
• DataMEL API—a REST API from which V'Lille stations data is extracted.
b. Data Extraction:
• “extract.py” component responsible for making API calls (using the “requests” library).
• This component should feed into the first stage of the ETL.
c. Data Transformation:
• The “transform.py” component utilizes PySpark for cleaning and formatting data.
• Mention aspects like date formatting, column additions (e.g., nb_places_total, % remplissage), and possibly the creation of intermediate DataFrame representations.
d. Data Loading:
• “load.py” loads the transformed data into the MySQL database.
e. Orchestration:
• Airflow acts as the scheduler that connects and triggers these tasks.
• That Airflow manages sequencing (as DAGs) across extraction, transformation, and loading.
f. Configuration:
• Configuration elements (from the config directory and spark_config.json) to show how parameters for the extraction and Spark processing are managed.
g. Reporting:
• Although not directly part of the ETL code, include PowerBI as a downstream component that consumes the data stored in MySQL for generating reports.
Relationships and Data Flow
1. From the external API (dataMEL) → Extraction component
2. From Extraction → Transformation (handled by PySpark)
3. From Transformation → Loading component → MySQL database
• Airflow as the overarching orchestrator that links these steps, initiating each phase of the ETL pipeline.
Architectural Patterns & Technologies
• ETL Pipeline: The classic separation into Extract, Transform, and Load stages.
• Configuration Management: Centralized settings stored in the config directory and JSON config files.
• Orchestration: Airflow’s DAGs ensuring scheduled and orderly execution.
• Data Processing: Usage of PySpark for scalable data transformation.
• External Integration: API-driven extraction using requests.
• Database: MySQL for data persistence.