Vanna Local Deployment: Efficient Text2SQL Conversion with Ease

Vanna is a highly regarded Text2SQL open source framework that transforms natural language into SQL query statements. In this article, we will detail how to deploy Vanna locally and combine it with a MySQL database and a Deepseek The models are configured and tested to help you get started with the tool quickly. All operations are based on real-world testing to ensure that the steps are clear and workable.

Python Environment Setup

To run Vanna, you first need a stable Python environment. Here's a step-by-step guide to configuring Miniconda3 as an example.

Installing Miniconda3

1. Download the installation package:

   wget https://repo.continuum.io/miniconda/Miniconda3-latest-Linux-x86_64.sh
   

2. Execute the installation script:

   sh Miniconda3-latest-Linux-x86_64.sh
   

3. Configure environment variables:

   vim /etc/profile
   

   Add it to the file:

   export PATH="/data/apps/miniconda3/bin:$PATH"
   

   Save and refresh the configuration:

   source /etc/profile
   

4. If you need to uninstall, you can just delete the installation directory:

   rm -rf /data/apps/miniconda3/
   

Creating a Virtual Environment

1. Create a Python 3.10 environment:

   conda create -n test python=3.10
   

2. Activate the environment (needs to take effect on a new terminal or after a reboot):

   conda activate test
   

3. Other common commands:
   • Exit environment:

     conda deactivate
     

   • View environmental information:

     conda info --env
     

After completing the above steps, you have a standalone Python virtual environment that lays the groundwork for Vanna's deployment.

Vanna Deployment and Configuration

With the Python environment ready, let's move on to the core configuration of Vanna. The following operations refer to the official documentation (https://vanna.ai/docs/) and use the MySQL database as an example.

Database Connection Configuration

First, make sure you can log in to the database properly with your MySQL account, password and port. After testing a successful connection, open the MySQL configuration page in the official Vanna documentation (select MySQL in the left menu bar). The page will show a sample connection code, as shown below:

Based on your database information, adjust the parameters in the code (e.g., host, user, password, etc.) to ensure that Vanna connects smoothly.

Select Language Model

Vanna supports a variety of Large Language Models (LLMs). The official page prompts for model selection, for example Ollama or API calls. The Deepseek model for silicon-based flows is illustrated here as an example.

• Ollama experience: Attempts to deploy the quantized Deepseek-7b model have been made with poor results, and it is recommended that this option be skipped.
• Deepseek API: Calling Deepseek models via in silico flows performs better. Note, however, that custom LLM classes are required to use models that are not officially supported. See the Vanna open source project's Mistral implementation (mistral.py), create a class adapted to Deepseek accordingly.

The configuration screen is shown below:

Vector database setup

Vanna integrates ChromaDB as a small vector database by default, no additional installation is required. The official documentation will generate code according to your choice, as shown below:

Installation of dependencies and code preparation

1. Install Vanna and its dependencies in an activated virtual environment:

   pip install vanna
   

2. Create a .py file, copy the official generated code into it. Below is a sample code snippet for adapting MySQL and Deepseek (you need to adjust the parameters according to the actual situation):

   from vanna.remote import VannaDefault
   vn = VannaDefault(model='deepseek', api_key='your_api_key')
   vn.connect_to_mysql(host='localhost', dbname='test_db', user='root', password='your_password', port=3306)
   

data training

Vanna supports three types of training data: SQL statements, product documentation, and database table structure descriptions. Here we recommend using the table structure description, the effect is more intuitive. The training steps are as follows:

1. Prepare the table structure data (e.g., DDL file).
2. Use the officially provided training code:

   vn.train(ddl="CREATE TABLE employees (id INT, name VARCHAR(255), salary INT)")
   

3. The training process is shown below:

More training results are shown:

Running the Web Interface

Once training is complete, run the following Flask API code to launch Vanna's Web UI:

from vanna.flask import VannaFlaskApp
app = VannaFlaskApp(vn)
app.run()

Access to the local address (usually http://127.0.0.1:5000), you can make SQL queries through the interface.

Query effect display

After deployment, Vanna's Q&A functionality performed satisfactorily. Here are a couple of actual test results:

• Input: "Please inquire about the status of the quarterly report submission for the March quarter of 2024."
  
• Input: "Number of statistics"
  
• Input: "Pollutant statistics"
  

Summary and recommendations

With these steps, you can successfully deploy Vanna locally and realize efficient Text2SQL functionality in combination with MySQL and Deepseek models. Compared with other tools, Vanna has obvious advantages in ease of use and effectiveness. It is recommended that beginners prioritize the use of table structure training data and adjust the language model configuration according to actual needs.