How ruckbreasoning/resdsql works
Project type and purpose
• RESDSQL is a research‐oriented, two‐stage Text-to-SQL parsing pipeline. It is not a web service or monolithic app, but rather a collection of data preprocessing modules, model training/inference code, SQL conversion utilities, and evaluation scripts. Its goal is to convert a natural-language question into an executable SQL query by (a) linking schema elements then (b) parsing a “skeleton” NatSQL representation before final SQL conversion.
File structure
• Top‐level entry points:
• run.py, text2sql.py, evaluate_text2sql_ckpts.py, schema_item_classifier.py, text2sql_data_generator.py, preprocessing.py, original_text2sql_dataset.py, nltk_downloader.py.
• NatSQL submodule:
• JSON datasets (NatSQLv1_6/*.json), converter script (natsql2sql/), helper scripts (check_and_preprocess.sh, natsql2sql.sh).
• utils/: core utilities for dataset loading, tokenization, model metrics, decoding, etc.
• scripts/: orchestrated shell pipelines for train/ inference/ robustness evaluation across Spider, Dr.Spider, CSpider.
• config.py and requirements.txt declare dependencies (PyTorch, Huggingface Transformers, SpaCy, NLTK).
README for additional insights
• Confirms two‐stage design:
• Stage 1: Cross‐encoder schema‐linker (RoBERTa / XLM‐RoBERTa) to predict schema item relevance.
• Stage 2: Skeleton parser (T5 / mT5) to generate NatSQL.
• Post‐processing: NatSQL→SQL conversion via custom parser.
• Uses external datasets/test suites (Spider, Spider-DK, Spider-Syn, Spider-Realistic, Dr.Spider, CSpider) pulled in under third_party/.
1. Data & Config Layer
– Raw datasets (.json, .pkl), database files, tables.json.
– config.py, requirements.txt, environment (conda).
2. Preprocessing
– preprocessing.py, original_text2sql_dataset.py, pattern_generation.py, setence_split.py, table_transform.py, NatSQL/check_and_preprocess.sh.
– Output: cleaned queries + schema representations.
3. Stage 1 – Schema Linking
– schema_item_classifier.py
– Bridge content encoder, classifier_model, classifier_metric (utils/)
– Output: NLQ→{linked_table, linked_column} pairs.
4. Stage 2 – Skeleton Parsing
– text2sql_data_generator.py + text2natsql scripts under scripts/train
– text2sql.py / run.py (loads links + NLQ + schema, fine‐tunes or inference with T5/mT5)
– Output: NatSQL statement.
5. NatSQL → SQL Conversion
– NatSQL/natsql2sql/natsql2sql.py + preprocessing modules under natsql2sql/preprocess
– utils for token/string matching.
– Output: valid SQL.
6. Evaluation & Metrics
– evaluate_text2sql_ckpts.py, scripts/evaluate_robustness, third_party/test_suite
– utils/spider_metric, utils/classifier_metric.
7. Orchestration Scripts
– scripts/train/*
– scripts/inference/*
– scripts/evaluate_robustness/*
• Raw Data → Preprocessing → Prepared Data
• Prepared Data → Schema Linking → Link Annotations
• (Prepared Data + Link Annotations) → Skeleton Parser → NatSQL
• NatSQL → NatSQL Converter → Final SQL
• Final SQL + Ground-truth DB → Evaluation scripts → Metrics
• Orchestration layer invokes each stage in sequence, passing file paths and models.
c. Architectural patterns & design principles:
• Two‐stage modular pipeline (separation of concerns).
• Data‐driven: JSON files as contract between stages.
• Reusable utilities under utils/.
• Script‐based orchestration for reproducibility.
d. Key technologies & frameworks:
• PyTorch / Huggingface Transformers (T5, mT5, RoBERTa).
• SpaCy, NLTK for tokenization / sentence splitting.
• Shell scripts for workflow orchestration.
• JSON/PKL for intermediate data exchange.
• External test suites (Spider, Dr.Spider, CSpider).
Elements
• Clear labels for each component (use module/script names).
• Blue for data stores (raw JSON, tables.json, DB files)
• Green for ML components (schema linker, skeleton parser)
• Orange for converters/preprocessors
• Purple for orchestration scripts
Final advice
• Be detailed: show sub‐modules (e.g., utils/spider_metric) only if they play a distinct role.
• Core two‐stage pattern: Schema Linking → Skeleton Parsing → Conversion → Evaluation.
• Avoid clutter by folding lower‐level helper scripts into “Preprocessing” or “Utilities” groups.
• Important config points (e.g., choice of model scale: base/large/3B) where relevant.