How opensearch-ai/opensearch-sql works
Main Components
• Data and Few-shot Processing Module:
• Components in the Bird directory (bird_dev.json, fewshot/questions.json) are used to provide few-shot examples.
• Under src/database_process, scripts like data_preprocess.py, generate_question.py, make_emb.py, and prepare_train_queries.py handle data preparation, embed generation, and few-shot example creation.
• LLM Integration Module:
• The src/llm subdirectory contains key files such as model.py, all_prompt.py, db_conclusion.py, and prompts.py. These implement functions to build query prompts, interface with LLMs, and process model responses.
• Pipeline Management Module:
• Located in src/pipeline, there are dedicated scripts for different stages: align_correct.py, candidate_generate.py, extract_col_value.py, generate_db_schema.py, pipeline_manager.py, etc.
• This module orchestrates processing steps including extraction, candidate selection, alignment, and evaluation.
• Runner/Execution Module:
• The src/runner folder comprises multiple scripts (execution.py, database_manager.py, run_manager.py, logger.py, etc.) that implement the execution layer – validating, correcting, retrieving columns, managing statistics, and handling database interactions.
• Supporting Infrastructure and Run Scripts:
• The run folder, containing run_main.sh and run_preprocess.sh, provides the shell scripts required to activate the processing pipeline.
• The requirements.txt file and configuration files (like bird_dev.json and few-shot JSONs) indicate external dependencies and configuration details.
Relationships and Data Flow
• User or automated data input first goes through preprocessing in the database_process module to generate few-shot examples, prepare queries, and create embeddings for schema linkage.
• LLM Processing:
• Processed data moves to the LLM module where prompt formatting occurs. Here, the input (including optional chain-of-thought information) is formatted and sent to the chosen large language model (e.g., GPT, DeepSeek, Gemini).
• Pipeline Orchestration:
• The LLM outputs then flow into the pipeline module, where the response is further refined (extraction and alignment) to handle candidate SQL generation, vote selection strategies, and correction of any hallucinated information.
• Execution Phase:
• The final validated and processed SQL is handed off to the runner module where tasks like column retrieval, execution management, and logging take place. The database_manager also coordinates any direct database interactions.
• External Configuration and Few-shot Integration:
• The Bird directory and relevant configuration files act as input sources that might be used at both preprocessing and runtime (few-shot example generation).
Architectural Patterns and Principles
• Modular Design: Separation into distinct modules (preprocessing, LLM interaction, pipeline & execution) emphasizes a clean separation of concerns.
• Pipeline Flow: A step-by-step processing pipeline model that begins with data preparation and ends with SQL execution. This is especially clear in the pipeline_manager and workflow_builder that control how different stages are sequenced.
• Dynamic Few-shot and Alignment Strategies: The design integrates dynamic few-shot learning (leveraging examples from Bird) and alignment (in the pipeline module) to fine-tune output through chain-of-thought augmentation.
• Logging and Monitoring: The runner module has dedicated logging and statistics management to monitor system performance.