How kaifeng-cmd/autoetl_flask_tableau works
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