Notebooks
O
OpenAI How To Evaluate LLMs For SQL Generation

How To Evaluate LLMs For SQL Generation

chatgptopenaigpt-4evaluationexamplesopenai-apiopenai-cookbook
alph-notebooks/openai-cookbook / How_to_evaluate_LLMs_for_SQL_generation.ipynb

How to test and evaluate LLMs for SQL generation

LLMs are fundamentally non-deterministic in their responses, this attribute makes them wonderfully creative and dynamic in their responses. However, this trait poses significant challenges in achieving consistency, a crucial aspect for integrating LLMs into production environments.

The key to harnessing the potential of LLMs in practical applications lies in consistent and systematic evaluation. This enables the identification and rectification of inconsistencies and helps with monitoring progress over time as the application evolves.

Scope of this notebook

This notebook aims to demonstrate a framework for evaluating LLMs, particularly focusing on:

  • Unit Testing: Essential for assessing individual components of the application.
  • Evaluation Metrics: Methods to quantitatively measure the model's effectiveness.
  • Runbook Documentation: A record of historical evaluations to track progress and regression.

This example focuses on a natural language to SQL use case - code generation use cases fit well with this approach when you combine code validation with code execution, so your application can test code for real as it is generated to ensure consistency.

Although this notebook uses SQL generation usecase to demonstrate the concept, the approach is generic and can be applied to a wide variety of LLM driven applications.

We will use two versions of a prompt to perform SQL generation. We will then use the unit tests and evaluation functions to test the perforamance of the prompts. Specifically, in this demonstration, we will evaluate:

  1. The consistency of JSON response.
  2. Syntactic correctness of SQL in response.

Table of contents

  1. Setup: Install required libraries, download data consisting of SQL queries and corresponding natural language translations.
  2. Test Development: Create unit tests and define evaluation metrics for the SQL generation process.
  3. Evaluation: Conduct tests using different prompts to assess the impact on performance.
  4. Reporting: Compile a report that succinctly presents the performance differences observed across various tests.

Setup

Import our libraries and the dataset we'll use, which is the natural language to SQL b-mc2/sql-create-context dataset from HuggingFace.

[1]
[2]
78577 rows

Looking at the dataset

We use Huggingface datasets library to download SQL create context dataset. This dataset consists of:

  1. Question, expressed in natural language
  2. Answer, expressed in SQL designed to answer the question in natural language.
  3. Context, expressed as a CREATE SQL statement, that describes the table that may be used to answer the question.

In our demonstration today, we will use LLM to attempt to answer the question (in natural language). The LLM will be expected to generate a CREATE SQL statement to create a context suitable to answer the user question and a coresponding SELECT SQL query designed to answer the user question completely.

The dataset looks like this:

[3]

Test development

To test the output of the LLM generations, we'll develop two unit tests and an evaluation, which will combine to give us a basic evaluation framework to grade the quality of our LLM iterations.

To re-iterate, our purpose is to measure the correctness and consistency of LLM output given our questions.

Unit tests

Unit tests should test the most granular components of your LLM application.

For this section we'll develop unit tests to test the following:

  • test_valid_schema will check that a parseable create and select statement are returned by the LLM.
  • test_llm_sql will execute both the create and select statements on a sqlite database to ensure they are syntactically correct.
[4]

Prompting the LLM

For this demonstration purposes, we use a fairly simple prompt requesting GPT to generate a (context, answer) pair. context is the CREATE SQL statement, and answer is the SELECT SQL statement. We supply the natural language question as part of the prompt. We request the response to be in JSON format, so that it can be parsed easily.

[5]
Question: How many heads of the departments are older than 56 ?
Answer: {"create":"CREATE TABLE DepartmentHeads (\n    id INT PRIMARY KEY,\n    name VARCHAR(100),\n    age INT,\n    department VARCHAR(100)\n);","select":"SELECT COUNT(*) AS NumberOfHeadsOlderThan56 \nFROM DepartmentHeads \nWHERE age > 56;"}

Check JSON formatting

Our first simple unit test checks that the LLM response is parseable into the LLMResponse Pydantic class that we've defined.

We'll test that our first response passes, then create a failing example to check that the check fails. This logic will be wrapped in a simple function test_valid_schema.

We expect GPT to respond with a valid SQL, we can validate this using LLMResponse base model. test_valid_schema is designed to help us validate this.

[6]
[7]
True

Testing negative scenario

To simulate a scenario in which we get an invalid JSON response from GPT, we hardcode an invalid JSON as response. We expect test_valid_schema function to throw an exception.

[8]
ERROR: Invalid schema: 1 validation error for LLMResponse
  Invalid JSON: expected value at line 1 column 1 [type=json_invalid, input_value='CREATE departments, select * from departments', input_type=str]
    For further information visit https://errors.pydantic.dev/2.10/v/json_invalid

False

As expected, we get an exception thrown from the test_valid_schema fucntion.

Test SQL queries

Next we'll validate the correctness of the SQL. This test will be desined to validate:

  1. The CREATE SQL returned in GPT response is syntactically correct.
  2. The SELECT SQL returned in the GPT response is syntactically correct.

To achieve this, we will use a sqlite instance. We will direct the retured SQL functions to a sqlite instance. If the SQL statements are valid, sqlite instance will accept and execute the statements; otherwise we will expect an exception to be thrown.

create_connection function below will setup a sqlite instance (in-memory by default) and create a connection to be used later.

[9]

Next, we will create the following functions to carry out the syntactical correctness checks.

  • test_create: Function testing if the CREATE SQL statement succeeds.
  • test_select: Function testing if the SELECT SQL statement succeeds.
  • test_llm_sql: Wrapper function executing the two tests above.
[10]
[11]
CREATE SQL is: CREATE TABLE DepartmentHeads (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);
SELECT SQL is: SELECT COUNT(*) AS NumberOfHeadsOlderThan56 
FROM DepartmentHeads 
WHERE age > 56;
[12]
Testing create query: CREATE TABLE DepartmentHeads (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);
Testing select query: SELECT COUNT(*) AS NumberOfHeadsOlderThan56 
FROM DepartmentHeads 
WHERE age > 56;
Result of query: [(0,)]

True
[13]
Testing create query: CREATE TABLE departments (id INT, name VARCHAR(255), head_of_department VARCHAR(255))
Testing select query: SELECT COUNT(*) FROM departments WHERE age > 56
Error while executing select query: no such column: age

False

Using an LLM to evaluate relevancy

Next, we evaluate whether the generated SQL actually answers the user's question. This test will be performed by gpt-4o-mini, and will assess how relevant the produced SQL query is when compared to the initial user request.

This is a simple example which adapts an approach outlined in the G-Eval paper, and tested in one of our other cookbooks.

[14]
[15]
[16]
[17]
User Question 	: How many heads of the departments are older than 56 ?
CREATE SQL Returned 	: CREATE TABLE head (age INTEGER)
SELECT SQL Returned 	: SELECT COUNT(*) FROM head WHERE age > 56
5
********************
User Question 	: List the name, born state and age of the heads of departments ordered by age.
CREATE SQL Returned 	: CREATE TABLE head (name VARCHAR, born_state VARCHAR, age VARCHAR)
SELECT SQL Returned 	: SELECT name, born_state, age FROM head ORDER BY age
4
********************
User Question 	: List the creation year, name and budget of each department.
CREATE SQL Returned 	: CREATE TABLE department (creation VARCHAR, name VARCHAR, budget_in_billions VARCHAR)
SELECT SQL Returned 	: SELECT creation, name, budget_in_billions FROM department
4
********************

Evaluation

We will test these functions in combination including our unit test and evaluations to test out two system prompts.

Each iteration of input/output and scores should be stored as a run. Optionally you can add GPT-4 annotation within your evaluations or as a separate step to review an entire run and highlight the reasons for errors.

For this example, the second system prompt will include an extra line of clarification, so we can assess the impact of this for both SQL validity and quality of solution.

Building the test framework

We want to build a function, test_system_prompt, which will run our unit tests and evaluation against a given system prompt.

[18]

System Prompt 1

The system under test is the first system prompt as shown below. This run will generate responses for this system prompt and evaluate the responses using the functions we've created so far.

[19]
  0%|          | 0/50 [00:00<?, ?it/s]

We can now group the outcomes of:

  • the unit tests, which test the structure of response; and
  • the evaluation, which checks if the SQL is syntatically correct.
[20]
unit_test_evaluation
,SQL correct      46
,SQL incorrect     4
,Name: count, dtype: int64
[21]
evaluation_score
,5    33
,4    16
,3     1
,Name: count, dtype: int64

System Prompt 2

We now use a new system prompt to run same unit test and evaluation.

[22]
  0%|          | 0/50 [00:00<?, ?it/s]

As above, we can group the unit test and evaluation results.

[23]
unit_test_evaluation
,SQL correct      44
,SQL incorrect     6
,Name: count, dtype: int64
[24]
evaluation_score
,5    34
,4    15
,3     1
,Name: count, dtype: int64

Reporting

We'll make a simple dataframe to store and display the run performance - this is where you can use tools like Weights & Biases Prompts or Gantry to store the results for analytics on your different iterations.

[25]

Plotting unit test results

We can create a simple bar chart to visualise the results of unit tests for both runs.

[26]
[27]
Output

Plotting evaluation results

We can similarly plot the results of the evaluation.

[28]
[29]
Output

Conclusion

Now you have a framework to test SQL generation using LLMs, and with some tweaks this approach can be extended to many other code generation use cases. With GPT-4 and engaged human labellers you can aim to automate the evaluation of these test cases, making an iterative loop where new examples are added to the test set and this structure detects any performance regressions.

We hope you find this useful, and please supply any feedback.

[ ]