defog-ai/sql-eval

You can construct SQL generation prompts using a template containing specific variables. These variables are intended to be populated at runtime using Python's .format() method.

Available variables:

• {user_question}: The natural language question for which SQL needs to be generated.
• {table_metadata_string}: A string containing table names, column names, and types. For the sqlcoder model, this should be formatted as a SQL DDL statement.
• {instructions} (optional): Custom instructions for specific questions (e.g., date formatting, dialect adaptation).
• {k_shot_prompt} (optional): Few-shot examples containing question-SQL pairs to provide context.
• {glossary} (optional): Definitions for special terminology or specific SQL creation rules.

### Task
Generate a SQL query to answer the following question:
`{user_question}`
`{instructions}`
`{glossary}`
### Database Schema
The query will run on a database with the following schema:
{table_metadata_string}
{k_shot_prompt}
### Answer
Given the database schema, here is the SQL query that answers `{user_question}`:
```sql

The vLLM runner uses the vLLM engine to run inference as a single batch, which is faster for num_beams > 1.

• Use -g vllm.
• Use -a to pass paths to LoRA adapters.
• Use -qz or --quantized if the model is quantized with AWQ.
• For multiple prompts in one run, provide a list of files to -f and -o (the counts must match).

# Standard vLLM run
python -W ignore main.py \
  -db postgres \
  -q "data/questions_gen_postgres.csv" "data/instruct_basic_postgres.csv" "data/instruct_advanced_postgres.csv" \
  -o results/vllm_classic.csv results/vllm_basic.csv results/vllm_advanced.csv \
  -g vllm \
  -f "prompts/prompt.md" \
  -m defog/llama-3-sqlcoder-8b \
  -a path/to_adapter \
  -c 0

# Running with multiple prompts to save model loading time
python -W ignore main.py \
  -db postgres \
  -q "data/questions_gen_postgres.csv" \
  -o results/results_1.csv results/results_2.csv \
  -g vllm \
  -f prompts/prompt_1.md prompts/prompt_2.md \
  -m defog/sqlcoder2

You can automatically report evaluation results to a server (like a Google Cloud Function) using the --upload_url flag.

1. Deploy a Cloud Function

The repository provides sample functions for BigQuery and Postgres.

Setup:

1. Copy .env.yaml.template to .env.yaml and fill in the fields.
2. For BigQuery, place your service account key.json in the folder and set CREDENTIALS_PATH in .env.yaml.

Deploy BigQuery function:

gcloud functions deploy results_bigquery \
  --source results_fn_bigquery \
  --entry-point bigquery \
  --env-vars-file results_fn_bigquery/.env.yaml \
  --runtime python311 \
  --memory 512MB \
  --trigger-http \
  --allow-unauthenticated \
  --gen2

Deploy Postgres function:

gcloud functions deploy results_postgres \
  --source results_fn_postgres \
  --entry-point postgres \
  --env-vars-file results_fn_postgres/.env.yaml \
  --runtime python311 \
  --memory 512MB \
  --trigger-http \
  --allow-unauthenticated \
  --gen2

2. Run Evaluation

Pass the deployed URL to your main.py command:

python main.py \
  -db postgres \
  -o results/test.csv \
  -g oa \
  -f prompts/prompt_openai.json \
  -m gpt-4o-mini \
  -n 1 \
  --upload_url <your cloud function url>

Tip: To always report to a specific URL without passing the flag every time, set the SQL_EVAL_UPLOAD_URL environment variable.

You can repurpose the framework for private datasets by following these steps:

1. Prepare Data: Create a separate git repository for your private data containing a setup.py file (similar to defog-data). Create metadata and data files and import them into your database.
2. Define Joins: For metadata pruning, you must define a columns_join dictionary mapping database names to a nested dictionary of table tuples and column name tuples.
3. Integrate:
   • Install your data library with pip install -e ..
   • Replace function calls and variables in utils/pruning.py (specifically within prune_metadata_str) to point to your private data's modules/functions.
   • Update your questions file to point to your custom questions tailored to your schema.

Warning: Ensure your database is populated with meaningful data. Empty tables will result in empty dataframes, causing false positive matches in the evaluation.

To evaluate OpenAI models, ensure OPENAI_API_KEY is set as an environment variable.

For standard models (like o3-mini), use a standard prompt file. For o1-* models which do not support system prompts, use a specific prompt file (e.g., prompts/prompt_openai_o1.json), reduce the parallel requests (-p), and increase the timeout (-t).

# Standard OpenAI model
python main.py \
  -db postgres \
  -q "data/questions_gen_postgres.csv" "data/instruct_basic_postgres.csv" "data/instruct_advanced_postgres.csv" \
  -o results/openai_classic.csv results/openai_basic.csv results/openai_advanced.csv \
  -g oa \
  -f prompts/prompt_openai.json \
  -m o3-mini \
  -p 5 \
  -c 0

# o1 series models (no system prompt support)
python main.py \
  -db postgres \
  -q "data/questions_gen_postgres.csv" "data/instruct_basic_postgres.csv" "data/instruct_advanced_postgres.csv" \
  -o results/openai_o1mini_classic.csv results/openai_o1mini_basic.csv results/openai_o1mini_advanced.csv \
  -g oa \
  -f prompts/prompt_openai_o1.json \
  -m o1-mini \
  -p 1 \
  -t 120 \
  -c 0