Olist NL-to-SQL Analytics Assistant

🚀 Live Demo Web Application: https://nl2sqlanalytics.streamlit.app/

An end-to-end Natural Language to SQL (NL2SQL) analytics assistant built over the Olist Brazilian E-commerce Database (~100k orders across 9 relational tables). Plain-English business questions are converted into syntactically correct MySQL queries, executed against a read-only database instance, and synthesized into natural language executive summaries.

---

🛠️ Architecture & System Design

                  ┌────────────────────────┐
                  │ Natural Language Query │
                  └───────────┬────────────┘
                              │
                              ▼
        ┌──────────────────────────────────────────┐
        │ 1. Adaptive Table Selector               │
        │ - Filters 9 tables down to 2-3 required  │
        │ - Reduces context payload by ~75%         │
        └─────────────────────┬────────────────────┘
                              │
                              ▼
        ┌──────────────────────────────────────────┐
        │ 2. ChromaDB Semantic Few-Shot Retriever  │
        │ - Vector k-NN search over 30 gold pairs  │
        │ - Injects CTE & Window Function examples │
        └─────────────────────┬────────────────────┘
                              │
                              ▼
        ┌──────────────────────────────────────────┐
        │ 3. Gemini 3.6 Flash SQL Generator        │
        │ - Generates dialect-safe MySQL           │
        │ - Strips markdown code fences            │
        └─────────────────────┬────────────────────┘
                              │
                              ▼
        ┌──────────────────────────────────────────┐
        │ 4. Read-Only Query Executor              │
        │ - Connects via nl2sql_ro MySQL user      │
        │ - Prevents DDL / DML operations          │
        └─────────────────────┬────────────────────┘
                              │
                              ▼
        ┌──────────────────────────────────────────┐
        │ 5. Conversational Synthesizer            │
        │ - Formats raw tuples into business metrics│
        └──────────────────────────────────────────┘

---

🚀 Key Features

1. Adaptive Table Routing (app/table_selector.py): Dynamically filters schema definitions so the LLM prompt sees only the 2–3 tables required for the query, lowering token costs and reducing model confusion on multi-table JOINs.
2. Semantic Few-Shot Retrieval (app/few_shot.py): Uses a local ChromaDB vector store indexing 30 curated Q→SQL pairs covering advanced MySQL window functions (ROW_NUMBER, DENSE_RANK, LAG, NTILE, CUME_DIST, moving averages) and Common Table Expressions (CTEs).
3. Automated Evaluation Harness (eval/eval_harness.py): Benchmarks pipeline performance against gold-standard SQL queries, reporting exact execution accuracy % and logging failure modes.
4. Interactive Streamlit Web UI (app/main.py): Clean chat interface with collapsible pipeline details (selected tables, generated SQL, raw execution tuples).

---

📊 Database Schema (olist)

The assistant operates over 9 relational tables:

• customers: Customer locations (city, state, zip code).
• geolocation: Coordinates mapped to zip code prefixes.
• orders: Order status and timestamps (purchase, approval, delivery).
• order_items: Line items per order, item price, freight value.
• order_payments: Payment methods, installments, payment amounts.
• order_reviews: Review scores (1–5 stars) and review text.
• products: Product dimensions, weight, and Portuguese category names.
• sellers: Seller locations and zip code prefixes.
• product_category_translation: Portuguese to English category mappings.

---

💻 Quickstart Guide

1. Environment Setup

git clone https://github.com/VedantSinha00/NL2SQL-Analytics.git
cd NL2SQL-Analytics

# Create and activate virtual environment
python -m venv venv
venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

2. Configure Environment Variables (.env)

Create a .env file in the root directory:

db_user=root
db_password=your_mysql_password
db_host=localhost
db_port=3306
db_name=olist

GEMINI_API_KEY=your_gemini_api_key

3. Build MySQL Database

python setup_db.py

This loads all 9 CSV datasets into MySQL, creates primary keys and join indexes, and sets up a read-only user (nl2sql_ro).

4. Run Evaluation Harness

python eval/eval_harness.py

5. Launch Streamlit Web UI

streamlit run app/main.py

---

🧪 Tech Stack

• Framework: LangChain (LCEL)
• Model: Google Gemini 3.6 Flash
• Vector DB: ChromaDB
• Database: MySQL 8.0 + SQLAlchemy + PyMySQL
• Frontend: Streamlit