Apppublic

anmolparimoo/t2sql

sourceHugging Facemitupdated 2y agoView on Hugging Face

0likes

App README

Text-to-SQL Query Generator

A natural language to SQL query generator that leverages schema metadata and SQL best practices to produce syntactically correct SQL queries for Google BigQuery. This project integrates a HuggingFace language model with an interactive Gradio interface to convert natural language questions into precise SQL commands.

Overview

The Text-to-SQL Query Generator transforms user-provided natural language queries into SQL queries by:

—Retrieving Relevant Context: It extracts relevant best practices and schema metadata from JSON files.
—Leveraging a Language Model: It uses a HuggingFace inference client to generate SQL based on the provided context.
—Ensuring Best Practices: It guides the query generation using pre-defined best practices for SQL formatting, optimization, and clarity.

Project Structure

—app.py The main application file that:
—Loads schema metadata from metadata.json.
—Loads SQL best practices from best_practices.json.
—Retrieves context based on user queries using Python’s difflib.
—Constructs a full system prompt by combining user input, metadata, and best practices.
—Utilizes a HuggingFace language model via an InferenceClient to generate SQL.
—Launches an interactive Gradio chat interface for user interaction.

—metadata.json Contains detailed information about the database schema. Each table entry includes:
—Table descriptions
—Field definitions (names, types, descriptions, and keys)
—Relationships between tables
—Granularity and sample use cases

—best_practices.json A list of best practices and guidelines for writing SQL queries. This ensures that generated queries are:
—Well-formatted and structured
—Optimized for performance (especially for Google BigQuery)
—In line with naming conventions and query optimization strategies

Getting Started

Prerequisites

—Python 3.8+
—Install the required packages:

bash

  pip install gradio huggingface-hub


## Running the Application
- Clone the Repository

git clone https://github.com/yourusername/text-to-sql.git cd text-to-sql

—Ensure Files are in Place Verify that app.py, metadata.json, and best_practices.json are in the project root directory.

—Launch the App

bash

  python app.py

This will start the Gradio interface. Follow the provided local URL in your browser to interact with the app.

## Usage

- Natural Language Input:
  Type your question or query in plain English (e.g., "Show me all customers who placed orders in the last month").

- System Prompt:
  A pre-set system message instructs the model to:
  * Use the provided schema metadata and best practices.
  * Generate a syntactically correct SQL query compatible with Google BigQuery.
  * Output only the SQL code without any additional commentary.

- Adjustable Parameters:
  Use the additional inputs (sliders and textboxes) in the Gradio interface to modify:
  * Max New Tokens: The maximum number of tokens to generate.
  * Temperature: The randomness of the model's responses.
  * Top-p (Nucleus Sampling): Controls diversity via probability mass.

- SQL Generation:
  The application streams the generated SQL query in real time, providing immediate feedback.

## Customization
  - Changing the Model:
    Modify the model used by editing the model identifier in `app.py`:

client = InferenceClient("HuggingFaceH4/zephyr-7b-beta")

—Alternatively, choose another model:

python

    client = InferenceClient("deepseek-ai/DeepSeek-R1")
    client = InferenceClient("ngxson/MiniThinky-v2-1B-Llama-3.2")

## Updating Schema Metadata:
Modify `metadata.json` to match your database schema. This ensures that generated queries refer to the correct tables and fields.

## Enhancing Best Practices:
Update `best_practices.json` with any new guidelines or modifications to further refine SQL generation quality.

## Contributing
Contributions are welcome! If you have improvements, bug fixes, or suggestions, please:
- Fork the repository.
- Create a new branch for your feature or fix.
- Submit a pull request detailing your changes.

## License
This project is licensed under the MIT License. See the LICENSE file for details.

## Acknowledgments
- HuggingFace: For providing robust language models and inference tools.
- Gradio: For the simple and effective interactive UI framework.
- The Open Source Community: For continuous contributions and best practices in SQL query optimization.

# Happy querying!

An example chatbot using [Gradio](https://gradio.app), [`huggingface_hub`](https://huggingface.co/docs/huggingface_hub/v0.22.2/en/index), and the [Hugging Face Inference API](https://huggingface.co/docs/api-inference/index).