How flyingfeather/dea-sql works
Project type and purpose
• This is an academic/research codebase implementing DEA-SQL: a workflow-oriented, LLM-based Text-to-SQL system. It reads Spider dataset files, applies a five-stage decomposition and few-shot retrieval pipeline, queries an LLM (OpenAI/Azure), post-processes and corrects generated SQL, then evaluates against gold queries.
High-level architecture implied by file tree and README
• Orchestrator: main.py drives the pipeline, accepting CLI args via argsparser.py.
• Common utilities: common/ holds shared config (common/config/static_config.py), LLM wrappers (common/wrapper.py), CSV logging, enumeration helpers, etc.
• Data ingestion & preprocessing: data_preprocess.py, filter_characters.py, get_ner.py.
• Few-shot retrieval: fewshot/ with methods to build and index embeddings, dispatcher in fewshot/processor.py.
• Prompt construction: prompt/ modules assemble decomposition-aware prompts (get_prompt.py, get_sql_prompt_for_fewshot.py, questions_label_prompt.py).
• LLM invocation: llm/chat.py and llm/chatgpt.py wrap API calls, embedding.py handles embedding lookups.
• SQL generation & correction: gen_sql.py produces raw SQL, correct_sql.py applies heuristic fixes.
• Evaluation: evaluation/ contains both project-native and external Spider test suite evaluation scripts.
• Supporting scripts: single_eval.py for standalone evaluation; nltk_downloader.py to fetch linguistic assets; hardness_eval.py to analyze query difficulty.
• Outputs: outputs/ stores prediction files per dataset, organized by baseline.
• External dependencies: Spider dataset files under data/spider; Azure/OpenAI endpoints.
Main components & interactions
a. Data Layer
• Input: JSON dataset + table schemas (data/spider/…)
• Output: processed examples fed to few-shot and prompt modules
b. Preprocessing Layer
• data_preprocess.py / filter_characters.py / get_ner.py
• Normalizes questions, tokenizes, extracts named entities
c. Few-Shot Retriever
• Index training examples (fewshot/index_train_*)
• Compute/query embeddings via llm/embedding.py
• Return top-k examples based on mode (random, ques_tim, masked_ques_sim)
d. Prompt Builder
• Takes decomposed steps (from decomposition logic in argsparser.py or prompt modules)
• Inserts few-shot examples, schema info, question into a templated prompt
e. LLM Executor
• llm/chat.py / llm/chatgpt.py wrap HTTP calls to OpenAI / Azure
• Handles rate limits, retries, streaming if enabled
f. Post-processing & Correction
• gen_sql.py parses LLM raw output into SQL AST or text
• correct_sql.py applies static fixes (e.g., keyword normalization)
g. Evaluation
• Native: single_eval.py compares predictions to gold sets
• Standard: evaluation/test-suite-sql-eval uses established Spider evaluator
Architectural patterns and principles
• Modular pipeline: Each stage (preprocess → retrieve → prompt → infer → postprocess → evaluate) is a separate Python module/script.
• Wrapper pattern: common/wrapper.py and llm/* abstract external LLM API details.
• Configuration centralization: static parameters (API keys, endpoints) live in common/config/static_config.py.
• CLI-driven: main.py + argsparser.py implement a clear, reproducible command-line interface.
• Separation of concerns: evaluation modules isolated from core inference logic.