{ "cells": [ { "cell_type": "markdown", "id": "d31716b432b31c92", "metadata": {}, "source": [ "# Evaluating Generating SQL with LLM\n", "\n", "In this recipe, we will be using taking our [SQL Agent](../agents/sql_agent.ipynb) example and running evaluations on LLM call. We will be exploring various different evaluations we can run to ensure quality and expected behavior.\n", "\n", "
\n", "

Mirascope Concepts Used

\n", "\n", "
\n", "\n", "
\n", "

Check out the SQL Agent Cookbook

\n", "

\n", "We will be using our LibrarianAgent for our evaluations. For a detailed explanation regarding this code snippet, refer to the SQL Agent Cookbook.\n", "

\n", "
" ] }, { "cell_type": "markdown", "id": "6b76025f", "metadata": {}, "source": [ "## Setup\n", "\n", "To set up our environment, first let's install all of the packages we will use:" ] }, { "cell_type": "code", "execution_count": null, "id": "3378318e", "metadata": {}, "outputs": [], "source": [ "!pip install \"mirascope[openai]\"\n", "!pip install pytest ipytest pytest-asyncio" ] }, { "cell_type": "code", "execution_count": null, "id": "83507b46", "metadata": {}, "outputs": [], "source": [ "import os\n", "\n", "os.environ[\"OPENAI_API_KEY\"] = \"YOUR_API_KEY\"\n", "# Set the appropriate API key for the provider you're using" ] }, { "cell_type": "markdown", "id": "98f078d3", "metadata": {}, "source": [ "## Evaluating the prompt using a golden dataset\n", "\n", "One effective approach is to establish a golden dataset that the prompt must successfully pass. We'll leverage `pytest` for this purpose, as it offers numerous testing conveniences." ] }, { "cell_type": "code", "execution_count": null, "id": "30522e4858497b70", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T14:52:38.422495Z", "start_time": "2024-09-30T14:52:26.019553Z" } }, "outputs": [], "source": [ "import sqlite3\n", "from typing import ClassVar\n", "from unittest.mock import MagicMock\n", "\n", "import ipytest\n", "import pytest\n", "from mirascope.core import BaseMessageParam, openai, prompt_template\n", "from pydantic import BaseModel, ConfigDict\n", "\n", "ipytest.autoconfig(run_in_thread=True)\n", "\n", "\n", "class Librarian(BaseModel):\n", " con: ClassVar[sqlite3.Connection] = sqlite3.connect(\"database.db\")\n", " messages: list[BaseMessageParam | openai.OpenAIMessageParam] = []\n", "\n", " model_config = ConfigDict(arbitrary_types_allowed=True)\n", "\n", " def _run_query(self, query: str) -> str:\n", " \"\"\"A SELECT query to run.\"\"\"\n", " print(query)\n", " try:\n", " cursor = self.con.cursor()\n", " cursor.execute(query)\n", " res = cursor.fetchall()\n", " return str(res)\n", " except sqlite3.Error as e:\n", " return str(e)\n", "\n", " def _execute_query(self, query: str) -> str:\n", " \"\"\"An INSERT, UPDATE, or DELETE query to execute.\"\"\"\n", " print(query)\n", " try:\n", " cursor = self.con.cursor()\n", " cursor.execute(query)\n", " rows_affected = cursor.rowcount\n", " self.con.commit()\n", " if rows_affected > 0:\n", " return f\"Query executed successfully, {rows_affected} row(s) were updated/inserted.\"\n", " else:\n", " return \"No rows were updated/inserted.\"\n", " except sqlite3.Error as e:\n", " print(e)\n", " return str(e)\n", "\n", " @openai.call(model=\"gpt-4o-mini\", stream=True)\n", " @prompt_template(\n", " \"\"\"\n", " SYSTEM:\n", " You are a friendly and knowledgeable librarian named Mira. Your role is to \n", " assist patrons with their queries, recommend books, \n", " and provide information on a wide range of topics.\n", "\n", " Personality:\n", " - Warm and approachable, always ready with a kind word\n", " - Patient and understanding, especially with those who are hesitant or confused\n", " - Enthusiastic about books and learning\n", " - Respectful of all patrons, regardless of their background or level of knowledge\n", "\n", " Services:\n", " - Keep track of patrons' reading lists using a SQLite database. Assume that the user is non technical and will ask you\n", " questions in plain English.\n", " - Recommend books based on the user's preferences\n", " Your task is to write a query based on the user's request.\n", "\n", " The database schema is as follows:\n", "\n", " TABLE ReadingList (\n", " id INTEGER PRIMARY KEY AUTOINCREMENT,\n", " title TEXT NOT NULL,\n", " status TEXT CHECK(status IN ('Not Started', 'In Progress', 'Complete')) NOT NULL,\n", " rating INTEGER CHECK(rating >= 1 AND rating <= 5),\n", " );\n", "\n", " You must interpret the user's request and write the appropriate SQL query to\n", " pass in the tools.\n", "\n", " Example interactions:\n", " 1. Select\n", " - USER: \"Show me all books.\"\n", " - ASSISTANT: \"SELECT * FROM ReadingList;\"\n", " 2. Insert\n", " - USER: \"Add Gone with the Wind to my reading list.\"\n", " - ASSISTANT: \"INSERT INTO ReadingList (title, status) VALUES ('Gone with the Wind', 'Not Started');\"\n", " 3. Update\n", " - USER: \"I just finished Gone with the Wind, can you update the status, and give it 5 stars??\"\n", " - ASSISTANT: \"UPDATE ReadingList SET status = 'Complete' and rating = 5 WHERE title = 'Gone with the Wind';\"\n", " 4. Delete\n", " - USER: \"Remove Gone with the Wind from my reading list.\"\n", " - ASSISTANT: \"DELETE FROM ReadingList WHERE title = 'Gone with the Wind';\"\n", "\n", " If field are not mentioned, omit them from the query.\n", " All queries must end with a semicolon.\n", "\n", " You have access to the following tools:\n", " - `_run_query`: When user asks for recommendations, you can use this tool to see what they have read.\n", " - `_execute_query`: Use the query generated to execute an \n", " INSERT, UPDATE, or DELETE query.\n", "\n", " You must use these tools to interact with the database.\n", "\n", " MESSAGES: {self.messages}\n", " USER: {query}\n", " \"\"\"\n", " )\n", " async def _stream(self, query: str) -> openai.OpenAIDynamicConfig:\n", " return {\"tools\": [self._run_query, self._execute_query]}\n", "\n", " async def _step(self, question: str):\n", " response = await self._stream(question)\n", " tools_and_outputs = []\n", " async for chunk, tool in response:\n", " if tool:\n", " tools_and_outputs.append((tool, tool.call()))\n", " else:\n", " print(chunk.content, end=\"\", flush=True)\n", " if response.user_message_param:\n", " self.messages.append(response.user_message_param)\n", " self.messages.append(response.message_param)\n", " if tools_and_outputs:\n", " self.messages += response.tool_message_params(tools_and_outputs)\n", " await self._step(\"\")\n", "\n", " async def run(self):\n", " while True:\n", " question = input(\"(User): \")\n", " if question == \"exit\":\n", " break\n", " print(\"(Assistant): \", end=\"\", flush=True)\n", " await self._step(question)\n", " print()\n", "\n", "\n", "@pytest.fixture\n", "def mock_librarian():\n", " class MockLibrarian(Librarian):\n", " con: ClassVar[sqlite3.Connection] = MagicMock()\n", "\n", " return MockLibrarian()\n", "\n", "\n", "@pytest.mark.asyncio\n", "@pytest.mark.parametrize(\n", " \"select_query\",\n", " [\n", " \"Get all books\",\n", " \"Get every single book\",\n", " \"Show me all books\",\n", " \"List all books\",\n", " \"Display all books\",\n", " ],\n", ")\n", "async def test_select_query(select_query: str, mock_librarian: Librarian):\n", " response = await mock_librarian._stream(select_query)\n", " async for _, tool in response:\n", " query = tool.args.get(\"query\", \"\") if tool else \"\"\n", " assert query == \"SELECT * FROM ReadingList;\"\n", "\n", "\n", "@pytest.mark.asyncio\n", "@pytest.mark.parametrize(\n", " \"insert_query\",\n", " [\n", " \"Please add Gone with the Wind to my reading list\",\n", " \"You recently recommended Gone with the Wind, can you add it to my reading list.\",\n", " ],\n", ")\n", "async def test_insert_query(insert_query: str, mock_librarian: Librarian):\n", " response = await mock_librarian._stream(insert_query)\n", " async for _, tool in response:\n", " query = tool.args.get(\"query\", \"\") if tool else \"\"\n", " assert (\n", " query\n", " == \"INSERT INTO ReadingList (title, status) VALUES ('Gone with the Wind', 'Not Started');\"\n", " )\n", "\n", "\n", "@pytest.mark.asyncio\n", "@pytest.mark.parametrize(\n", " \"update_query\",\n", " [\n", " \"Can you mark Gone with the Wind as read?\",\n", " \"I just finished Gone with the Wind, can you update the status?\",\n", " ],\n", ")\n", "async def test_update_query(update_query: str, mock_librarian: Librarian):\n", " response = await mock_librarian._stream(update_query)\n", " async for _, tool in response:\n", " query = tool.args.get(\"query\", \"\") if tool else \"\"\n", " assert (\n", " query\n", " == \"UPDATE ReadingList SET status = 'Complete' WHERE title = 'Gone with the Wind';\"\n", " )\n", "\n", "\n", "@pytest.mark.asyncio\n", "@pytest.mark.parametrize(\n", " \"delete_query\",\n", " [\n", " \"Can you remove Gone with the Wind from my reading list?\",\n", " \"Can you delete Gone with the Wind?\",\n", " ],\n", ")\n", "async def test_delete_query(delete_query: str, mock_librarian: Librarian):\n", " response = await mock_librarian._stream(delete_query)\n", " async for _, tool in response:\n", " query = tool.args.get(\"query\", \"\") if tool else \"\"\n", " assert query == \"DELETE FROM ReadingList WHERE title = 'Gone with the Wind';\"\n", "\n", "\n", "ipytest.run()" ] }, { "cell_type": "markdown", "id": "68b17a2973a519a5", "metadata": {}, "source": [ "\n", "The golden dataset serves as our test fixtures, allowing us to verify basic text-to-SQL conversions and ensure our Agent maintains this functionality.\n", "\n", "As we expand our tools or enhance our prompt, we should consistently implement additional prompt regression tests to maintain quality and functionality.\n", "\n", "
\n", "

Tip

\n", "

\n", "SQL queries can get complex so be sure to add very specific tests, especially for mutable operations like INSERTS, UPDATES, and DELETES.\n", "

\n", "
\n", "\n", "\n", "## Evaluating conversations\n", "\n", "With the above techniques, we've shown how to evaluate simple input and output pairs, but we also want to test conversations with history. We can define some messages we want the LLM to have and get some expected output, like so:\n", "\n" ] }, { "cell_type": "code", "execution_count": 5, "id": "183d9f5707f63e3e", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T15:03:12.230034Z", "start_time": "2024-09-30T15:02:59.032644Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/Users/koudai/PycharmProjects/mirascope/.venv_notebook/lib/python3.12/site-packages/pytest_asyncio/plugin.py:208: PytestDeprecationWarning: The configuration option \"asyncio_default_fixture_loop_scope\" is unset.\n", "The event loop scope for asynchronous fixtures will default to the fixture caching scope. Future versions of pytest-asyncio will default the loop scope for asynchronous fixtures to function scope. Set the default fixture loop scope explicitly in order to avoid unexpected behavior in the future. Valid fixture loop scopes are: \"function\", \"class\", \"module\", \"package\", \"session\"\n", "\n", " warnings.warn(PytestDeprecationWarning(_DEFAULT_FIXTURE_LOOP_SCOPE_UNSET))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[33m [100%]\u001b[0m\n", "\u001b[33m========================================= warnings summary =========================================\u001b[0m\n", "../../../.venv_notebook/lib/python3.12/site-packages/_pytest/config/__init__.py:1277\n", " /Users/koudai/PycharmProjects/mirascope/.venv_notebook/lib/python3.12/site-packages/_pytest/config/__init__.py:1277: PytestAssertRewriteWarning: Module already imported so cannot be rewritten: anyio\n", " self._mark_plugins_for_rewrite(hook)\n", "\n", "-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html\n", "\u001b[33m\u001b[32m12 passed\u001b[0m, \u001b[33m\u001b[1m1 warning\u001b[0m\u001b[33m in 13.13s\u001b[0m\u001b[0m\n" ] }, { "data": { "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from openai.types.chat import (\n", " ChatCompletionAssistantMessageParam,\n", " ChatCompletionUserMessageParam,\n", ")\n", "\n", "\n", "@pytest.mark.asyncio\n", "async def test_conversation(mock_librarian: Librarian):\n", " mock_librarian.messages = [\n", " ChatCompletionUserMessageParam(\n", " role=\"user\", content=\"Can you recommend me a fantasy book?\"\n", " ),\n", " ChatCompletionAssistantMessageParam(\n", " role=\"assistant\",\n", " content=\"I would recommend 'The Name of the Wind' by Patrick Rothfuss. It’s the first book in 'The Kingkiller Chronicle' series and features a beautifully written story about a gifted young man who grows up to be a legendary figure. It's filled with magic, adventure, and rich character development. I believe you'll enjoy it! Would you like to add it to your reading list?\",\n", " ),\n", " ]\n", " response = await mock_librarian._stream(\"Can you add it to my reading list?\")\n", " async for _, tool in response:\n", " query = tool.args.get(\"query\", \"\") if tool else \"\"\n", " assert (\n", " query\n", " == \"INSERT INTO ReadingList (title, status) VALUES ('The Name of the Wind', 'Not Started');\"\n", " )\n", "\n", "\n", "ipytest.run()" ] }, { "cell_type": "markdown", "id": "52207794c6f2cd59", "metadata": {}, "source": [ "\n", "We expect that the LLM understands what \"it\" means in this case. If history was not working, then the LLM would not know what \"it\" refers to and would not be able to use the tool properly. This is particular useful because we can now test user flows easily to make sure certain flows behave properly.\n", "\n", "Next, we'll be taking a look at using LLMs as judges for testing the tone we expect the LLM to take.\n", "\n", "## LLMs as judges\n", "\n", "With tools, it's easy to do an equality comparison to check that the LLM outputs proper SQL, but tone is a bit more complex. Thankfully, using LLMs makes this much simpler. We can use the LLM to evaluate the `friendliness` of the LLM and adjust the prompt as necessary.\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "d05d473ca852767b", "metadata": { "ExecuteTime": { "end_time": "2024-09-30T15:05:58.625884Z", "start_time": "2024-09-30T15:05:41.476198Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/Users/koudai/PycharmProjects/mirascope/.venv_notebook/lib/python3.12/site-packages/pytest_asyncio/plugin.py:208: PytestDeprecationWarning: The configuration option \"asyncio_default_fixture_loop_scope\" is unset.\n", "The event loop scope for asynchronous fixtures will default to the fixture caching scope. Future versions of pytest-asyncio will default the loop scope for asynchronous fixtures to function scope. Set the default fixture loop scope explicitly in order to avoid unexpected behavior in the future. Valid fixture loop scopes are: \"function\", \"class\", \"module\", \"package\", \"session\"\n", "\n", " warnings.warn(PytestDeprecationWarning(_DEFAULT_FIXTURE_LOOP_SCOPE_UNSET))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[32m.\u001b[0m\u001b[33m [100%]\u001b[0m\n", "\u001b[33m========================================= warnings summary =========================================\u001b[0m\n", "../../../.venv_notebook/lib/python3.12/site-packages/_pytest/config/__init__.py:1277\n", " /Users/koudai/PycharmProjects/mirascope/.venv_notebook/lib/python3.12/site-packages/_pytest/config/__init__.py:1277: PytestAssertRewriteWarning: Module already imported so cannot be rewritten: anyio\n", " self._mark_plugins_for_rewrite(hook)\n", "\n", "-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html\n", "\u001b[33m\u001b[32m13 passed\u001b[0m, \u001b[33m\u001b[1m1 warning\u001b[0m\u001b[33m in 17.07s\u001b[0m\u001b[0m\n" ] }, { "data": { "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import asyncio\n", "\n", "from mirascope.core import BasePrompt, anthropic\n", "from openai.types.chat import ChatCompletionToolMessageParam\n", "from pydantic import Field\n", "\n", "\n", "@prompt_template(\n", " \"\"\"\n", " Evaluate the friendliness of generated text on a continuous scale from 1 to 5.\n", " A generation is friendly (score > 1) if it contains content that is:\n", "\n", " - Warm, welcoming, and positive in tone\n", " - Respectful, considerate, and empathetic towards others\n", " - Encouraging, supportive, or promotes kindness\n", " - Inclusive and mindful of diverse perspectives\n", "\n", " Provide a brief reasoning for your assigned score.\n", " Make sure to highlight the specific aspects that impacted your score.\n", "\n", " Use the following friendliness scale:\n", "\n", " 1 - No friendliness; cold, distant, or neutral tone\n", " 2 - Mild friendliness; slightly warm or polite language\n", " 3 - Moderate friendliness; noticeably kind and welcoming content\n", " 4 - High friendliness; very warm, supportive, and empathetic language\n", " 5 - Extreme friendliness; exceptionally kind, caring, and uplifting content\n", "\n", " Input Query: {input_query}\n", " Output Content: {output_content}\n", " \"\"\"\n", ")\n", "class FriendlinessEvaluator(BasePrompt):\n", " input_query: str\n", " output_content: str\n", "\n", "\n", "@pytest.mark.asyncio\n", "async def test_friendliness(mock_librarian: Librarian):\n", " input_query = \"Can you add gone with the wind\"\n", " mock_librarian.messages = [\n", " ChatCompletionUserMessageParam(role=\"user\", content=input_query),\n", " ChatCompletionAssistantMessageParam(\n", " role=\"assistant\",\n", " tool_calls=[\n", " {\n", " \"type\": \"function\",\n", " \"function\": {\n", " \"arguments\": \"{\\\"query\\\":\\\"INSERT INTO ReadingList (title, status) VALUES ('Gone with the Wind', 'Not Started');\\\"}\",\n", " \"name\": \"_execute_query\",\n", " },\n", " \"id\": \"1\",\n", " }\n", " ],\n", " ),\n", " ChatCompletionToolMessageParam(\n", " role=\"tool\",\n", " content=\"Query executed successfully, 1 row(s) were updated/inserted.\",\n", " tool_call_id=\"1\",\n", " ),\n", " ]\n", " response = await mock_librarian._stream(\"\")\n", " output_content = \"\"\n", " async for chunk, _ in response:\n", " output_content += chunk.content\n", " prompt = FriendlinessEvaluator(\n", " input_query=input_query, output_content=output_content\n", " )\n", "\n", " class Eval(BaseModel):\n", " score: float = Field(..., description=\"A score between [1.0, 5.0]\")\n", " reasoning: str = Field(..., description=\"The reasoning for the score\")\n", "\n", " async def run_evals() -> list[Eval]:\n", " judges = [\n", " openai.call(\n", " \"gpt-4o-mini\",\n", " response_model=Eval,\n", " json_mode=True,\n", " ),\n", " anthropic.call(\n", " \"claude-3-5-sonnet-20240620\",\n", " response_model=Eval,\n", " json_mode=True,\n", " ),\n", " ]\n", " calls = [prompt.run_async(judge) for judge in judges]\n", " return await asyncio.gather(*calls)\n", "\n", " evals = await run_evals()\n", " total_score = sum(eval.score for eval in evals)\n", " average_score = total_score / len(evals)\n", " assert average_score > 2\n", "\n", "\n", "ipytest.run()" ] }, { "cell_type": "markdown", "id": "21fd967bd9a4a3bd", "metadata": {}, "source": [ "\n", " Similar to the conversations test, we simulate a user interaction by passing a messages array to a mock librarian. To assess the friendliness of this response, we utilize a custom `FriendlinessEvaluator`, which is a Mirascope `BasePrompt` that scores friendliness on a scale from 1 to 5 based on specific criteria. Leveraging Mirascope's model-agnostic capabilities, we send this evaluation prompt to both GPT-4 and Claude 3.5 Sonnet and average out these results.\n", "\n", "\n", " When adapting this recipe to your specific use-case, consider the following:\n", "\n", "- Experiment with the prompt, add specific examples to the `FriendlinessEvaluator` for more accurate evaluations\n", "- Experiment with the call prompt, add more examples of friendly conversation or persona.\n", "- Add evaluations for retrieval if you're using RAG, or evaluations for business metrics.\n", "e\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.6" } }, "nbformat": 4, "nbformat_minor": 5 }