# kaifeng-cmd/autoetl_flask_tableau architecture

> Architecture diagram of the GitHub repository kaifeng-cmd/autoetl_flask_tableau, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-27). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/kaifeng-cmd/autoetl_flask_tableau
- Repository: https://github.com/kaifeng-cmd/autoetl_flask_tableau

## Overview

##### Project type and purpose
- This is a full-stack ETL pipeline + visualization system.
- Purpose: Continuously fetch near-real-time earthquake data (USGS), enrich with reverse geocoding (BigDataCloud), store in MySQL, and display via a live Tableau dashboard.

##### File structure
- Top-level:
- app/ – Flask application modules
- scheduler/ – Windows Task Scheduler scripts (run_fetch.bat, scheduler_call.py)
- run.py – Flask app entry point
- config.py – central configuration loader (.env variables)
- requirements.txt – Python dependencies
- app/ directory:
- fetch_usgs.py – ETL logic (fetch from USGS, call BigDataCloud, transform)
- db.py – MySQL connection and ORM/helper functions
- routes.py – Flask API endpoint (GET /fetch-data)
- __init__.py – Flask app factory
- scheduler/ directory:
- scheduler_call.py – lightweight script to invoke app.fetch_usgs
- run_fetch.bat – batch file wired into Task Scheduler

##### Pull out insights from README
- Data sources: USGS GeoJSON feed (past-hour vs. past-30-days)
- Enrichment: BigDataCloud Reverse Geocoding API
- Storage: MySQL “earthquakes” table
- Orchestration: Windows Task Scheduler running every minute
- Presentation: Tableau Desktop connects live to MySQL

##### Main components
- USGS GeoJSON API
- BigDataCloud Reverse Geocoding API
b. Scheduler / Orchestrator
- Windows Task Scheduler → run_fetch.bat → scheduler_call.py
c. ETL Service (compute layer)
- Python Flask app (app/fetch_usgs.py + app/db.py)
- Configuration loader (config.py + .env)
d. Storage
- Table: earthquakes
e. API Layer
- Flask route GET /fetch-data (routes.py)
f. Monitoring & Logging
- app.log (logs/)
- Logging calls inside fetch_usgs.py and routes.py
g. Visualization
- Tableau Desktop (connected to MySQL)

##### Relationships and data flows
- Scheduler triggers scheduler_call.py every minute → calls Flask’s fetch-usgs function
- fetch_usgs:
1) GET USGS feed → parse JSON
2) For each record → call BigDataCloud API → enrich location data
3) Write/merge into MySQL via db.py
4) Log success/failures to app.log
- Tableau continuously queries MySQL “earthquakes” table for live updates

##### Architectural patterns & design principles
- ETL as a modular pipeline
- Single responsibility: scheduler only orchestrates, Flask app handles business logic & API
- Separation of concerns: external services, compute, storage, presentation
- Config-driven design: config.py + .env for secrets and endpoints
- Idempotency: incremental fetch logic avoids duplicates

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Sources Layer
    subgraph "Data Sources" 
        direction TB
        USGS["USGS GeoJSON API"]:::external
        BigDataCloud["BigDataCloud Reverse Geocoding API"]:::external
    end

    %% Orchestration & Compute Layer
    subgraph "Orchestration & Compute"
        direction TB
        TSK["Windows Task Scheduler"]:::compute
        BAT["run_fetch.bat"]:::compute
        SCHED["scheduler_call.py"]:::compute
        CONFIG["config.py"]:::compute
        ENTRY["run.py"]:::compute
        INIT["__init__.py"]:::compute
        FETCH["fetch_usgs.py"]:::compute
        DBMOD["db.py"]:::compute
        ROUTES["routes.py"]:::compute
        REQ["requirements.txt"]:::compute
        README["README.md"]:::compute
        LOGS["app.log"]:::compute
    end

    %% Storage Layer
    subgraph "Storage"
        direction TB
        MYSQL["MySQL (earthquakes table)"]:::storage
    end

    %% Presentation Layer
    subgraph "Presentation"
        direction TB
        TABLEAU["Tableau Desktop (Live)"]:::presentation
    end

    %% Relationships
    TSK -->|"every minute"| BAT
    BAT -->|"executes"| SCHED
    SCHED -->|"calls ETL"| FETCH

    FETCH -->|"Fetch GeoJSON"| USGS
    FETCH -->|"Reverse geocode"| BigDataCloud
    FETCH -->|"uses ORM"| DBMOD
    FETCH -->|"INSERT/UPDATE"| MYSQL

    ROUTES -->|"GET /fetch-data"| FETCH
    ROUTES -->|"uses ORM"| DBMOD

    ENTRY -->|"imports factory"| INIT
    INIT -->|"registers routes"| ROUTES
    INIT -->|"loads config"| CONFIG

    FETCH -->|"logs info"| LOGS
    ROUTES -->|"logs requests"| LOGS

    MYSQL -->|"Live query"| TABLEAU

    %% Click Events
    click BAT "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/scheduler/run_fetch.bat"
    click SCHED "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/scheduler/scheduler_call.py"
    click FETCH "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/app/fetch_usgs.py"
    click DBMOD "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/app/db.py"
    click ROUTES "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/app/routes.py"
    click INIT "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/app/__init__.py"
    click ENTRY "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/run.py"
    click CONFIG "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/config.py"
    click REQ "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/requirements.txt"
    click README "https://github.com/kaifeng-cmd/autoetl_flask_tableau/blob/main/README.md"

    %% Styles
    classDef external fill:#D0E6FF,stroke:#0288D1,stroke-width:2px
    classDef compute fill:#E8F5E9,stroke:#388E3C,stroke-width:2px
    classDef storage fill:#FFF3E0,stroke:#F57C00,stroke-width:2px
    classDef presentation fill:#F3E5F5,stroke:#8E24AA,stroke-width:2px
```
