Spaces:
Sleeping
Sleeping
|
Download README.md from rajdeepchatale/sql_query_env: direct link, hf CLI and curl.
- Browser
- Download file 9.02 kB
-
https://huggingface.co/spaces/rajdeepchatale/sql_query_env/resolve/main/README.md
- Command line
-
hf download hf://spaces/rajdeepchatale/sql_query_env/README.md
-
curl -L -o README.md https://huggingface.co/spaces/rajdeepchatale/sql_query_env/resolve/main/README.md
9.02 kB
| title: Sql Query Env | |
| emoji: π | |
| colorFrom: blue | |
| colorTo: indigo | |
| sdk: docker | |
| app_port: 8000 | |
| pinned: false | |
| base_path: /web | |
| # SQL Query Generation Environment | |
| An OpenEnv RL environment that trains AI agents to write SQL queries from natural language questions. | |
| ## Motivation | |
| We picked SQL generation because it's something we deal with almost every day in our coursework and internships. Whenever we're working with databases, translating a question like "show me the top customers last month" into actual SQL is surprisingly error-prone β wrong JOINs, missing WHERE clauses, forgetting about NULLs. We figured this would make a solid environment because: | |
| - It's a real task with clear right/wrong answers | |
| - Grading is deterministic (compare query results) | |
| - There's a natural difficulty curve from simple SELECTs to complex JOINs | |
| - Partial credit makes sense (right tables but wrong columns should score > 0) | |
| ## How it works | |
| The agent gets a database schema and a natural language question. It writes SQL. We run the query, compare results to ground truth, and return a score from 0 to 1 along with diagnostic feedback. | |
| ### Multi-domain testing | |
| We test across three different databases to make sure the agent actually *understands* SQL and isn't just memorizing one schema: | |
| | Domain | Tables | Description | | |
| |--------|--------|-------------| | |
| | Company Analytics | departments, employees, products, customers, orders, reviews | Business intelligence queries | | |
| | Hospital Management | wards, doctors, patients, appointments, medications, prescriptions | Healthcare data | | |
| | E-Commerce Platform | sellers, categories, products, users, orders, order_items, returns | Retail analytics | | |
| ### Action space | |
| The agent submits a single action per step: | |
| | Field | Type | Description | | |
| |-------|------|-------------| | |
| | `query` | string | A SQL SELECT query to execute against the database | | |
| Only SELECT statements are permitted. Destructive operations (DROP, DELETE, INSERT, UPDATE, etc.) are blocked and penalized. | |
| ### Observation space | |
| After each step, the agent receives a rich observation: | |
| | Field | Type | Description | | |
| |-------|------|-------------| | |
| | `task_id` | string | Current task identifier | | |
| | `difficulty` | string | easy, medium, or hard | | |
| | `database_domain` | string | Which database domain this task uses | | |
| | `question` | string | Natural language question to answer with SQL | | |
| | `schema_description` | string | Full database schema with table definitions | | |
| | `query_result` | string | Formatted result of the last query | | |
| | `query_error` | string (nullable) | SQL error message, if any | | |
| | `feedback` | string | Detailed grading feedback | | |
| | `diagnostics` | array | Structured error diagnostics: type, severity, message, suggestion | | |
| | `efficiency_notes` | array | SQL best-practice tips based on the submitted query | | |
| | `expected_row_count` | integer | Expected number of result rows | | |
| | `expected_columns` | array | Expected column names | | |
| | `steps_remaining` | integer | Attempts left in this episode | | |
| | `current_score` | number | Best score so far (0.0β1.0) | | |
| | `history` | array | Previous queries and scores | | |
| ### Scoring | |
| We break scoring into five components so the agent always gets useful signal: | |
| | Component | Weight | What it measures | | |
| |-----------|--------|-----------------| | |
| | Syntax | 0.10 | Does the query run at all? | | |
| | Tables | 0.15 | Right tables referenced? | | |
| | Columns | 0.20 | Correct output columns? | | |
| | Results | 0.45 | Do the rows match ground truth? | | |
| | Efficiency | 0.10 | Is it well-written SQL? (aliases, no SELECT *, etc.) | | |
| There's also a -0.10 penalty for destructive SQL (DROP, DELETE) and -0.05 for repeating the same query. | |
| All scores are clamped to the range **[0.0, 1.0]**. | |
| ### Diagnostic feedback | |
| Instead of just returning a score, we give structured diagnostics: | |
| - What tables are missing from the query | |
| - Whether a JOIN is needed | |
| - Column name mismatches | |
| - Row count differences | |
| - Efficiency tips (use aliases, avoid SELECT *, handle NULLs) | |
| This is designed for process supervision β the agent should be able to use this feedback to iteratively improve its query within an episode. | |
| ## Tasks | |
| 14 tasks across three difficulty tiers and three database domains: | |
| ### Full task list | |
| | Task ID | Domain | Difficulty | Description | Challenge Type | | |
| |---------|--------|-----------|-------------|---------------| | |
| | `company_easy_1` | Company | Easy | List active Engineering employees sorted by salary | Filter + sort | | |
| | `company_easy_2` | Company | Easy | Filter electronics products by price | Single table filter | | |
| | `hospital_easy_1` | Hospital | Easy | List patients still admitted (NULL discharge date) | NULL handling | | |
| | `ecommerce_easy_1` | E-Commerce | Easy | List premium users with signup details | Simple filter | | |
| | `company_medium_1` | Company | Medium | Total revenue per product category for completed orders | JOIN + GROUP BY | | |
| | `company_medium_2` | Company | Medium | Departments with average salary exceeding $100K | GROUP BY + HAVING | | |
| | `hospital_medium_1` | Hospital | Medium | Total medication cost for admitted patients | Multi-table JOIN + aggregation | | |
| | `ecommerce_medium_1` | E-Commerce | Medium | Revenue per seller for delivered orders | JOIN + GROUP BY | | |
| | `company_hard_1` | Company | Hard | Department salary-to-budget utilization percentage | Computed columns | | |
| | `company_hard_2` | Company | Hard | Employees earning more than their direct manager | Self-join | | |
| | `hospital_hard_1` | Hospital | Hard | Doctors prescribing to patients outside their ward | Double-join on wards | | |
| | `hospital_hard_2` | Hospital | Hard | Patients with multiple appointments with the same doctor | GROUP BY + HAVING + COUNT | | |
| | `ecommerce_hard_1` | E-Commerce | Hard | Return rate per product category with edge cases | Subquery + COALESCE | | |
| | `ecommerce_hard_2` | E-Commerce | Hard | In-stock products that have never been ordered | LEFT JOIN anti-join | | |
| ### Progressive hints | |
| When the agent gets stuck, we progressively reveal more hints on each failed attempt. First attempt gets a general hint, later attempts get more specific guidance about table structure. | |
| ## Baseline scores | |
| Baseline agent: **Qwen/Qwen2.5-72B-Instruct** via Hugging Face Inference API. | |
| | Task ID | Difficulty | Baseline Score | | |
| |---------|-----------|---------------| | |
| | `company_easy_1` | Easy | 0.90 | | |
| | `company_easy_2` | Easy | 0.90 | | |
| | `hospital_easy_1` | Easy | 0.90 | | |
| | `ecommerce_easy_1` | Easy | 0.85 | | |
| | `company_medium_1` | Medium | 0.75 | | |
| | `company_medium_2` | Medium | 0.70 | | |
| | `hospital_medium_1` | Medium | 0.65 | | |
| | `ecommerce_medium_1` | Medium | 0.70 | | |
| | `company_hard_1` | Hard | 0.55 | | |
| | `company_hard_2` | Hard | 0.45 | | |
| | `hospital_hard_1` | Hard | 0.40 | | |
| | `hospital_hard_2` | Hard | 0.50 | | |
| | `ecommerce_hard_1` | Hard | 0.35 | | |
| | `ecommerce_hard_2` | Hard | 0.45 | | |
| **Average score: ~0.65** | Easy: ~0.89 | Medium: ~0.70 | Hard: ~0.45 | |
| *Scores are approximate and may vary slightly between runs due to LLM temperature.* | |
| ## Getting started | |
| ### Environment variables | |
| Before running the inference script, set these required variables: | |
| ```bash | |
| export HF_TOKEN="your-huggingface-token" | |
| export API_BASE_URL="https://router.huggingface.co/v1" # default | |
| export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct" # default | |
| ``` | |
| ### Running locally | |
| ```bash | |
| # install dependencies | |
| uv sync | |
| # start the environment server | |
| uv run server | |
| # run the baseline agent | |
| export HF_TOKEN="your-token" | |
| python inference.py | |
| ``` | |
| ## Client example | |
| ```python | |
| import asyncio | |
| from sql_query_env import SqlQueryAction, SqlQueryEnv | |
| async def main(): | |
| async with SqlQueryEnv(base_url="http://localhost:8000") as client: | |
| result = await client.reset() | |
| print(result.observation.question) | |
| print(result.observation.schema_description) | |
| result = await client.step( | |
| SqlQueryAction(query="SELECT name, salary FROM employees ORDER BY salary DESC") | |
| ) | |
| print(f"Score: {result.reward}") | |
| print(f"Feedback: {result.observation.feedback}") | |
| for diag in result.observation.diagnostics: | |
| print(f" [{diag['type']}] {diag['message']}") | |
| asyncio.run(main()) | |
| ``` | |
| ## Docker | |
| ```bash | |
| docker build -t sql_query_env:latest . | |
| docker run -p 8000:8000 sql_query_env:latest | |
| ``` | |
| ## Project structure | |
| ``` | |
| sql_query_env/ | |
| βββ Dockerfile # Container build | |
| βββ models.py # Action/Observation Pydantic models | |
| βββ client.py # EnvClient for WebSocket connection | |
| βββ inference.py # Baseline inference script | |
| βββ openenv.yaml # Environment manifest (tasks, schemas) | |
| βββ server/ | |
| βββ app.py # FastAPI entry point | |
| βββ sql_query_env_environment.py # Core environment logic | |
| βββ tasks.py # Database schemas + all task definitions | |
| βββ graders.py # 5-component grading + diagnostics | |
| ``` | |
| ## License | |
| BSD-3-Clause | |