'"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"import logging\n",
"import os\n",
"import random\n",
"from datetime import datetime\n",
"from pprint import pprint\n",
"from typing import AsyncIterator, Iterator, Union\n",
"import sqlite3\n",
"import copy\n",
"from tqdm import tqdm\n",
"from tabulate import tabulate\n",
"\n",
"import pandas as pd\n",
"import jsonlines\n",
"from lamini.generation.base_prompt_object import PromptObject\n",
"from lamini.generation.generation_node import GenerationNode\n",
"from lamini.generation.base_prompt_object import PromptObject\n",
"from lamini.generation.generation_pipeline import GenerationPipeline\n",
"from util.get_schema import get_schema\n",
"from util.make_llama_3_prompt import make_llama_3_prompt\n",
"from util.setup_logging import setup_logging\n",
"from util.load_dataset import get_dataset\n",
"from util.get_default_finetune_args import get_default_finetune_args\n",
"\n",
"logger = logging.getLogger(__name__)\n",
"engine = sqlite3.connect(\"./nba_roster.db\")\n",
"setup_logging()\n",
"\n",
"class Args:\n",
" def __init__(self, \n",
" max_examples=100, \n",
" sql_model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\", \n",
" gold_file_name=\"gold-test-set.jsonl\",\n",
" training_file_name=\"generated_queries.jsonl\",\n",
" num_to_generate=10):\n",
" self.sql_model_name = sql_model_name\n",
" self.max_examples = max_examples\n",
" self.gold_file_name = gold_file_name\n",
" self.training_file_name = training_file_name\n",
" self.num_to_generate = num_to_generate"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create a SQL Model with Llama 3 and Diagnose Hallucinations"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"First let's create a SQL LLM with Llama 3 and get a baseline. You can run the following python script which uses Llama 3."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Question:\n",
" Who is the highest paid NBA player?\n",
"Answer:\n",
"To answer this question, we can use the following SQLite query:\n",
"\n",
"```sql\n",
"SELECT NAME, SALARY\n",
"FROM nba_roster\n",
"WHERE SALARY!= '--'\n",
"ORDER BY CAST(SALARY AS REAL) DESC\n",
"LIMIT 1;\n",
"```\n",
"\n",
"This query first filters out the rows where the salary is '--' (i.e., the players who don't have a salary listed). Then, it orders the remaining rows by the salary in descending order (highest to lowest). Finally, it returns the top row, which corresponds to the highest paid NBA player.\n"
]
}
],
"source": [
"llm = lamini.Lamini(model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\")\n",
"\n",
"question = \"\"\"Who is the highest paid NBA player?\"\"\"\n",
"system = f\"\"\"You are an NBA analyst with 15 years of experience writing complex SQL queries. Consider the nba_roster table with the following schema:\n",
"{get_schema()}\n",
"\n",
"Write a sqlite query to answer the following question. Follow instructions exactly\"\"\"\n",
"prompt = make_llama_3_prompt(question, system)\n",
"print(\"Question:\\n\", question)\n",
"\n",
"# Ask the model to generate a sql query to answer the question\n",
"print(\"Answer:\")\n",
"print(llm.generate(prompt, max_new_tokens=200))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" NOTE \n",
"\n",
"`make_llama_3_prompt` and `get_schema` are commonly used throughout this notebook. Let's inspect them for a second\n",
"\n",
"```python\n",
"def make_llama_3_prompt(user, system=\"\"):\n",
" system_prompt = \"\"\n",
" if system != \"\":\n",
" system_prompt = (\n",
" f\"<|start_header_id|>system<|end_header_id|>\\n\\n{system}<|eot_id|>\"\n",
" )\n",
" return f\"<|begin_of_text|>{system_prompt}<|start_header_id|>user<|end_header_id|>\\n\\n{user}<|eot_id|><|start_header_id|>assistant<|end_header_id|>\\n\\n\"\n",
"```\n",
"\n",
"Meta Llama 3 Instruct uses a prompt template, with special tags used to indicate the user query and system prompt. \n",
"You can find the documentation on this [model card](https://llama.meta.com/docs/model-cards-and-prompt-formats/meta-llama-3/#meta-llama-3-instruct).\n",
"\n",
"```python\n",
"def get_schema():\n",
" return \"\"\"\\\n",
"0|Team|TEXT eg. \"Toronto Raptors\"\n",
"1|NAME|TEXT eg. \"Otto Porter Jr.\"\n",
"2|Jersey|TEXT eg. \"0\" and when null has a value \"NA\"\n",
"3|POS|TEXT eg. \"PF\"\n",
"4|AGE|INT eg. \"22\" in years\n",
"5|HT|TEXT eg. `6' 7\"` or `6' 10\"`\n",
"6|WT|TEXT eg. \"232 lbs\" \n",
"7|COLLEGE|TEXT eg. \"Michigan\" and when null has a value \"--\"\n",
"8|SALARY|TEXT eg. \"$9,945,830\" and when null has a value \"--\"\n",
"\"\"\"\n",
"```\n",
"This `get_schema` function returns a description of the `nba_roster` table which you use to inform the model what the datatypes of the columns are (all TEXT) and provide some examples for each column. \n",
"\n",
"This helps the model know how exactly columns are formatted. \n",
"\n",
"For example, the `HT` column is formatted `6' 7\"` as opposed to `6'7\"`. This distinction is important because you may need to `CAST` this column to numerical types in order to do comparison, search, and other mathematical operations on this column. \n",
"
\n",
"\n",
"As you can see, this first script will run Llama 3 with prompt tuning to generate SQL queries that are relevant to this database. One thing you may notice is that the response is verbose, we'd have to parse out the sql from the model output.\n",
"Let's double check the sqlite query itself."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Saddiq Bey|$4,556,983\n"
]
}
],
"source": [
"!sqlite3 nba_roster.db \"SELECT NAME, SALARY FROM nba_roster WHERE SALARY!= '--' ORDER BY CAST(SALARY AS REAL) DESC LIMIT 1;\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Hey this is incorrect! Evaluating Llama 3 manually by hand will take too much time. We can start automating this process. The correct query is\n",
"\n",
"```sql\n",
"SELECT salary, name \n",
"FROM nba_roster\n",
"WHERE salary != '--'\n",
"ORDER BY CAST(REPLACE(REPLACE(salary, '$', ''), ',','') AS INTEGER) DESC\n",
"LIMIT 1;\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"$51,915,615|Stephen Curry\n"
]
}
],
"source": [
"!sqlite3 nba_roster.db \"SELECT salary, name FROM nba_roster WHERE salary != '--' ORDER BY CAST(REPLACE(REPLACE(salary, '$', ''), ',','') AS INTEGER) DESC LIMIT 1;\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create an Evaluation Dataset\n",
"\n",
"An Evaluation Dataset is a representative dataset you can use to make sure your model is consistently performing. It can start as few as 20-100 datapoints. The goal is to get started quickly on improving your model, and not get bogged down here.\n",
"\n",
"Here, you can use the example dataset about the nba_roster database at `data/gold-test-set.jsonl`.\n",
"\n",
" NOTE \n",
"\n",
"You can do it! Writing an initial evaluation dataset can feel tedious, but a minor investment in time can lead to drastic improvement in quality. In reality, this time investment is going to be made by an LLM user throughout the lifecycle of a model. For some rough time estimates, it took me ~20 minutes to write 20 queries, and that led to a jump in accuracy from 25% to 75%. Later in this notebook, a more intense ~1 hr long data cleaning workflow improved the model accuracy from 75% to 95%.\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Evaluate the SQL LLM with an Eval LLM \n",
"\n",
"Next, let's evaluate Llama 3's baseline accuracy for text-to-SQL. Here, we are using a Lamini Inference pipeline. Just as above, you'll see how the output of the model is used to query the SQL database.\n",
"\n",
"First, define a `QueryStage` and `ScoreStage` by extending the `GenerationNode` class."
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"class QueryStage(GenerationNode):\n",
" def __init__(self, model_name):\n",
" super().__init__(\n",
" model_name=model_name,\n",
" max_new_tokens=150,\n",
" )\n",
"\n",
" def generate(\n",
" self,\n",
" prompt: Union[Iterator[PromptObject], AsyncIterator[PromptObject]],\n",
" *args,\n",
" **kwargs,\n",
" ):\n",
" results = super().generate(\n",
" prompt,\n",
" output_type={\"sqlite_query\": \"str\"},\n",
" *args,\n",
" **kwargs,\n",
" )\n",
" return results\n",
"\n",
"\n",
" def postprocess(self, obj: PromptObject):\n",
" # Run both the generated and reference (Gold Dataset) SQL queries\n",
" # Assessing whether the SQL queries succeeded in hitting the database (not correctness yet!)\n",
" \n",
" query_succeeded = False\n",
"\n",
" try:\n",
" logger.info(f\"Running SQL query '{obj.response['sqlite_query']}'\")\n",
" obj.data[\"generated_query\"] = obj.response[\"sqlite_query\"]\n",
" df = pd.read_sql(obj.response[\"sqlite_query\"], con=engine)\n",
" obj.data['df'] = df\n",
" logger.info(f\"Got data: {df}\")\n",
" query_succeeded = True\n",
"\n",
" except Exception as e:\n",
" logger.error(\n",
" f\"Failed to run SQL query: {obj.response['sqlite_query']}\"\n",
" )\n",
"\n",
" logger.info(f\"Running reference SQL query '{obj.data['sql']}'\")\n",
" df = pd.read_sql(obj.data[\"sql\"], con=engine)\n",
" logger.info(f\"Got data: {df}\")\n",
" obj.data['reference_df'] = df\n",
"\n",
" logger.info(f\"For question: {obj.data['question']}\")\n",
" logger.info(f\"For query: {obj.response['sqlite_query']}\")\n",
"\n",
" obj.data[\"query_succeeded\"] = query_succeeded\n",
"\n",
" def preprocess(self, obj: PromptObject):\n",
" new_prompt = make_llama_3_prompt(**self.make_prompt(obj.data))\n",
" obj.prompt = new_prompt\n",
"\n",
" def make_prompt(self, data: dict):\n",
" system = \"You are an NBA analyst with 15 years of experience writing complex SQL queries.\\n\"\n",
" system += \"Consider the nba_roster table with the following schema:\\n\"\n",
" system += get_schema() + \"\\n\"\n",
" system += (\n",
" \"Write a sqlite SQL query that would help you answer the following question:\\n\"\n",
" )\n",
" user = data[\"question\"]\n",
" return {\n",
" \"user\": user,\n",
" \"system\": system,\n",
" }"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [],
"source": [
"class ScoreStage(GenerationNode):\n",
" def __init__(self):\n",
" super().__init__(\n",
" model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\",\n",
" max_new_tokens=150,\n",
" )\n",
"\n",
" def generate(\n",
" self,\n",
" prompt: Union[Iterator[PromptObject], AsyncIterator[PromptObject]],\n",
" *args,\n",
" **kwargs,\n",
" ):\n",
" results = super().generate(\n",
" prompt,\n",
" output_type={\"explanation\": \"str\", \"similar\": \"bool\"},\n",
" *args,\n",
" **kwargs,\n",
" )\n",
" return results\n",
"\n",
" def preprocess(self, obj: PromptObject):\n",
" obj.prompt = make_llama_3_prompt(**self.make_prompt(obj))\n",
" logger.info(f\"Scoring Stage Prompt:\\n{obj.prompt}\")\n",
"\n",
" def postprocess(self, obj: PromptObject):\n",
" obj.data['is_matching'] = self.is_matching(obj.data, obj.response)\n",
" obj.data['explanation'] = obj.response[\"explanation\"]\n",
" obj.data['similar'] = obj.response[\"similar\"]\n",
"\n",
" def is_matching(self, data, response):\n",
" return (str(data.get('df',\"None\")).lower() == str(data['reference_df']).lower() \n",
" or response['similar'])\n",
"\n",
" def make_prompt(self, obj: PromptObject):\n",
" # Your evaluation model compares SQL output from the generated and reference SQL queries, using another LLM in the pipeline\n",
" system_prompt = \"Compare the following two dataframes. They are similar if they are almost identical, or if they convey the same information about the nba_roster dataset\"\n",
" system_prompt += \"Respond with valid JSON {'explanation' : str, 'similar' : bool}\"\n",
" user_prompt = (\n",
" f\"========== Dataframe 1 =========\\n{str(obj.data.get('df','None')).lower()}\\n\\n\"\n",
" )\n",
" user_prompt += (\n",
" f\"========== Dataframe 2 =========\\n{str(obj.data['reference_df']).lower()}\\n\\n\"\n",
" )\n",
" user_prompt += f\"Can you tell me if these dataframes are similar?\"\n",
" return {\n",
" \"system\": system_prompt,\n",
" \"user\": user_prompt\n",
" }"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"With these stages, you can define an evaluation pipeline using the `Generation Pipeline` class. In this pipeline, you can indicate that one stage feeds into the next by passing the output of the query stage into the input of the score stage in the `forward` function.\n",
"\n",
"It's important that the input to the evaluation pipeline's `call` function be an iterable over instances of `PromptObject`. You'll be using these objects to store data as it passes through the pipeline."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [],
"source": [
"async def run_eval(dataset, args):\n",
"\n",
" results = await run_evaluation_pipeline(dataset, args)\n",
"\n",
" print(\"Total results:\", len(results))\n",
"\n",
" return results\n",
"\n",
"\n",
"async def run_evaluation_pipeline(dataset, args):\n",
" results = EvaluationPipeline(args).call(dataset)\n",
"\n",
" result_list = []\n",
"\n",
" pbar = tqdm(desc=\"Saving results\", unit=\" results\")\n",
" async for result in results:\n",
" result_list.append(result)\n",
" pbar.update()\n",
" return result_list\n",
"\n",
"\n",
"class EvaluationPipeline(GenerationPipeline):\n",
" def __init__(self, args):\n",
" super().__init__()\n",
" self.query_stage = QueryStage(args.sql_model_name)\n",
" self.score_stage = ScoreStage()\n",
"\n",
"\n",
" def forward(self, x):\n",
" x = self.query_stage(x)\n",
" x = self.score_stage(x)\n",
" return x\n"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [],
"source": [
"def load_gold_dataset(args):\n",
" path = f\"data/{args.gold_file_name}\"\n",
"\n",
" with jsonlines.open(path) as reader:\n",
" for index, obj in enumerate(reversed(list(reader))):\n",
" if index >= args.max_examples:\n",
" break\n",
" yield PromptObject(prompt=\"\", data=obj)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You'll need to save your results somewhere! In this notebook, you can use the `data/results` directory to log a record of your eval experiments. \n",
"\n",
"It's important to keep track of these experiments. To do this, you can log basic statistics, as well as errors and successes when the model is able to produce SQL which answers the question."
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [],
"source": [
"def save_eval_results(results, args):\n",
" base_path = \"./data/results\"\n",
" now = datetime.now().strftime(\"%Y_%m_%d_%H_%M_%S\")\n",
" experiment_name = f\"nba_sql_pipeline_{now}\"\n",
" experiment_dir = os.path.join(base_path, experiment_name)\n",
" os.makedirs(os.path.join(base_path, experiment_name))\n",
"\n",
" # Write args to file\n",
" args_file_name = f\"{experiment_dir}/args.txt\"\n",
" with open(args_file_name, \"w\") as writer:\n",
" pprint(args.__dict__, writer)\n",
"\n",
"\n",
" def is_correct(r):\n",
" if (\n",
" (result.data[\"query_succeeded\"] and result.data['is_matching']) or \n",
" result.data[\"generated_query\"] == result.data['sql']\n",
" ):\n",
" return True\n",
" return False\n",
"\n",
" # Write sql results and errors to file\n",
" results_file_name = f\"{experiment_dir}/sql_results.jsonl\"\n",
" with jsonlines.open(results_file_name, \"w\") as writer:\n",
" for result in results:\n",
" if not is_correct(result):\n",
" continue\n",
" writer.write(\n",
" {\n",
" \"question\": result.data['question'],\n",
" \"query\": result.data[\"generated_query\"],\n",
" \"query_succeeded\": result.data[\"query_succeeded\"],\n",
" \"reference_sql\": result.data['sql'],\n",
" \"df\": str(result.data.get('df', 'None')),\n",
" \"reference_df\": str(result.data['reference_df']),\n",
" 'is_matching': result.data['is_matching'],\n",
" 'similar': result.data['similar'],\n",
" }\n",
" )\n",
"\n",
" results_file_name = f\"{experiment_dir}/sql_errors.jsonl\"\n",
" with jsonlines.open(results_file_name, \"w\") as writer:\n",
" for result in results:\n",
" if is_correct(result):\n",
" continue\n",
" writer.write(\n",
" {\n",
" \"question\": result.data['question'],\n",
" \"query\": result.data[\"generated_query\"],\n",
" \"query_succeeded\": result.data[\"query_succeeded\"],\n",
" \"df\": str(result.data.get('df', 'None')),\n",
" \"reference_df\": str(result.data['reference_df']),\n",
" 'is_matching': result.data['is_matching'],\n",
" 'similar': result.data['similar'],\n",
" }\n",
" )\n",
"\n",
" # Write statistics to file\n",
" average_sql_succeeded = sum(\n",
" [result.data[\"query_succeeded\"] for result in results]\n",
" ) / len(results)\n",
" average_correct = sum(\n",
" [result.data[\"query_succeeded\"] and result.data['is_matching'] for result in results]\n",
" ) / len(results)\n",
"\n",
" file_name = f\"{experiment_dir}/summary.txt\"\n",
" with open(file_name, \"w\") as writer:\n",
" print(f\"Total size of eval dataset: {len(results)}\", file=writer)\n",
" print(f\"Total size of eval dataset: {len(results)}\")\n",
" print(f\"Percent Valid SQL Syntax: {average_sql_succeeded*100}\", file=writer)\n",
" print(f\"Percent Valid SQL Syntax: {average_sql_succeeded*100}\")\n",
" print(f\"Percent Correct SQL Query: {average_correct*100}\", file=writer)\n",
" print(f\"Percent Correct SQL Query: {average_correct*100}\")\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now, run eval on Llama 3 and see how it does on your evaluation dataset!"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 0 results [00:00, ? results/s]2024-06-21 14:08:35,116 [ERROR] Failed to run SQL query: SELECT POS, MAX(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY POS\n",
"2024-06-21 14:08:35,120 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTRING(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL\n",
"2024-06-21 14:08:35,123 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL\n",
"2024-06-21 14:08:35,125 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS average_salary FROM nba_roster WHERE POS = 'PF' AND SALARY!= '--';\n",
"2024-06-21 14:08:40,776 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) AS weight FROM nba_roster WHERE WT IS NOT NULL\n",
"2024-06-21 14:08:40,780 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) FROM nba_roster WHERE WT!= 'NA';\n",
"2024-06-21 14:08:40,783 [ERROR] Failed to run SQL query: SELECT PERCENTILE(SALARY, 0.25) FROM nba_roster WHERE SALARY!= '--';\n",
"2024-06-21 14:08:40,785 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.75) FROM (SELECT CAST(SUBSTR(salary, 2) AS INTEGER) AS salary FROM nba_roster WHERE salary!= '--') AS subquery\n",
"2024-06-21 14:08:40,788 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.99) FROM nba_roster WHERE salary IS NOT NULL\n",
"Saving results: 16 results [00:13, 1.34 results/s]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 20\n",
"Total size of eval dataset: 20\n",
"Percent Valid SQL Syntax: 55.00000000000001\n",
"Percent Correct SQL Query: 30.0\n"
]
}
],
"source": [
"args = Args()\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can view the results in the `data/results` directory, where there's a saved folder with the experiment arguments and results. \n",
"\n",
"You can see that Llama 3 can answer correctly `30%` of the time on the gold dataset. Additionally, Llama 3 can provide valid sql syntax as an answer `55%` of the time on the gold dataset. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Generate Tuning Data with Data LLMs"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You might be thinking, \"I'd like to do a little better!\" - so the next step is Lamini Memory Tuning. \n",
"\n",
"First, you need tuning data. Let's use Llama 3 to generate some tuning data! You want `question` and `sql` datapoints to help tune the model to generate SQL about the `nba_roster` dataset. The trick here is to work backwards in a pipeline (generate SQL from the schema, then questions from the generated SQL) and to constrain the prompts, so that the generations are more likely to be correct.\n",
"\n",
"You can do this using the following pipeline script."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 20 results [00:13, 1.48 results/s]\n"
]
}
],
"source": [
"class ModelStage(GenerationNode):\n",
" def __init__(self):\n",
" super().__init__(\n",
" model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\",\n",
" max_new_tokens=300,\n",
" )\n",
"\n",
" def generate(\n",
" self,\n",
" prompt: Union[Iterator[PromptObject], AsyncIterator[PromptObject]],\n",
" *args,\n",
" **kwargs,\n",
" ):\n",
" prompt = self.add_template(prompt)\n",
"\n",
" results = super().generate(\n",
" prompt,\n",
" output_type={\n",
" \"explanation\": \"str\",\n",
" \"sql_query_1\": \"str\",\n",
" \"sql_query_2\": \"str\",\n",
" },\n",
" *args,\n",
" **kwargs,\n",
" )\n",
"\n",
" return results\n",
"\n",
" async def add_template(self, prompts):\n",
" async for prompt in prompts:\n",
" new_prompt = make_llama_3_prompt(**self.make_prompt(prompt.data))\n",
" yield PromptObject(prompt=new_prompt, data=prompt.data)\n",
"\n",
" async def process_results(self, results):\n",
" async for result in results:\n",
" if result is None:\n",
" continue\n",
"\n",
" if result.response is None:\n",
" continue\n",
"\n",
" logger.info(\"=====================================\")\n",
" logger.info(f\"Generted query 1: {result.response['sql_query_1']}\")\n",
" logger.info(f\"Generted query 2: {result.response['sql_query_2']}\")\n",
" logger.info(\"=====================================\")\n",
"\n",
" if self.check_sql_query(result.response[\"sql_query_1\"]):\n",
" new_result = PromptObject(prompt=\"\", data=copy.deepcopy(result.data))\n",
" new_result.data.generated_sql_query = result.response[\"sql_query_1\"]\n",
" yield new_result\n",
"\n",
" if self.check_sql_query(result.response[\"sql_query_2\"]):\n",
" new_result = PromptObject(prompt=\"\", data=copy.deepcopy(result.data))\n",
" new_result.data.generated_sql_query = result.response[\"sql_query_2\"]\n",
" yield new_result\n",
"\n",
" def make_prompt(self, data):\n",
" system = \"You are an NBA analyst with 15 years of experience writing complex SQL queries.\\n\"\n",
" system += (\n",
" \"Consider a table called 'nba_roster' with the following schema (columns)\\n\"\n",
" )\n",
" system += get_schema()\n",
" system += \"Consider the following questions, and queries used to answer them:\\n\"\n",
" for example in data.sample:\n",
" system += \"Question: \" + example[\"question\"] + \"\\n\"\n",
" system += \"Query: \" + example[\"sql\"] + \"\\n\"\n",
"\n",
" # Important: generate relevant queries to your reference data\n",
" # Ideally, close to those that are failing so you can show the model examples of how to do it right!\n",
" user = \"Write two queries that are similar but different to those above.\\n\"\n",
" user += \"Format the queries as a JSON object, i.e.\\n\"\n",
" user += '{ \"explanation\": str, \"sql_query_1\" : str, \"sql_query_2\": str }.\\n'\n",
"\n",
" # Next, use Chain of Thought (CoT) and prompt-engineering to help with generating SQL queries\n",
" user += \"First write an explanation of why you decided to write these new queries in about 3-5 sentences, then write valid sqlite SQL queries for each of the 2 new queries. Make sure each query is complete and ends with a ;\\n\"\n",
"\n",
" return {\"system\": system, \"user\": user}\n",
"\n",
" def check_sql_query(self, query):\n",
" try:\n",
" pd.read_sql(query, con=engine)\n",
" except Exception as e:\n",
" logger.debug(f\"Error in SQL query: {e}\")\n",
" return False\n",
"\n",
" logger.info(f\"SQL query {query} is valid\")\n",
"\n",
" return True"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"class QuestionStage(GenerationNode):\n",
" def __init__(self):\n",
" super().__init__(\n",
" model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\",\n",
" max_new_tokens=150,\n",
" )\n",
"\n",
" def generate(\n",
" self,\n",
" prompt: Union[Iterator[PromptObject], AsyncIterator[PromptObject]],\n",
" *args,\n",
" **kwargs,\n",
" ):\n",
" results = super().generate(\n",
" prompt,\n",
" output_type={\n",
" \"explanation\": \"str\",\n",
" \"question\": \"str\",\n",
" },\n",
" *args,\n",
" **kwargs,\n",
" )\n",
" return results\n",
"\n",
" def preprocess(self, obj: PromptObject):\n",
" new_prompt = make_llama_3_prompt(**self.make_question_prompt(obj.data))\n",
" obj.prompt = new_prompt\n",
"\n",
" def make_question_prompt(self, data):\n",
" system = \"You are an NBA analyst with 15 years of experience writing complex SQL queries.\\n\"\n",
" system += (\n",
" \"Consider a table called 'nba_roster' with the following schema (columns)\\n\"\n",
" )\n",
" system += get_schema() + \"\\n\"\n",
" system += \"Queries, and questions that they are used to answer:\\n\"\n",
" for example in data.sample:\n",
" system += \"Query: \" + example[\"sql\"] + \"\\n\"\n",
" system += \"Question: \" + example[\"question\"] + \"\\n\"\n",
"\n",
" user = \"Now consider the following query.\\n\"\n",
" user += \"Query: \" + data.generated_sql_query + \"\\n\"\n",
" user += \"Write a question that this query could be used to answer.\\n\"\n",
"\n",
" # Using Chain of Thought (CoT) again\n",
" # This time you can do it programmatically with function calling, so you can easily extract a question out of the JSON object\n",
" user += \"Format your response as a JSON object, i.e.\\n\"\n",
" user += '{ \"explanation\": str, \"question\": str }.\\n'\n",
"\n",
" user += \"First write an explanation in about 3-5 sentences, then write a one sentence question.\\n\"\n",
"\n",
" return {\"system\": system, \"user\": user}\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can define a new pipeline to generate queries. This one also has multiple stages, and as mentioned above, the trick is that you are working backwards. The first stage writes SQL, which is pertinent to `nba_roster`. You're using prompt tuning to get queries that may be inspired by a sample of our gold dataset—that way, you're getting examples that are relevant to the evaluation (ideally, showing correct examples similar to those that were previously incorrect). Then, you use the question stage to inspect those queries and generate a question that can be answered by the generated query. \n",
"\n",
"Since the point is to create an model that can move forwards (generate), working backwards like this is just one creative method for data generation that can help constrain the prompts and produce more accurate generated data for tuning. "
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [],
"source": [
"async def run_query_gen_pipeline(seed_queries):\n",
" return QueryGenPipeline().call(seed_queries)\n",
"\n",
"\n",
"class QueryGenPipeline(GenerationPipeline):\n",
" def __init__(self):\n",
" super().__init__()\n",
" self.model_stage = ModelStage()\n",
" self.question_stage = QuestionStage()\n",
"\n",
" def forward(self, x):\n",
" x = self.model_stage(x)\n",
" x = self.question_stage(x)\n",
" return x\n"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [],
"source": [
"all_examples = []\n",
"\n",
"\n",
"async def load_seed_queries(args):\n",
" path = f\"data/{args.gold_file_name}\"\n",
"\n",
" with jsonlines.open(path) as reader:\n",
" global all_examples\n",
"\n",
" all_examples = [obj for obj in reader]\n",
"\n",
" sample_count = args.num_to_generate\n",
" sample_size = 3\n",
"\n",
" random.seed(42)\n",
"\n",
" for i in range(sample_count):\n",
" example_sample = ExampleSample(random.sample(all_examples, sample_size), i)\n",
"\n",
" yield PromptObject(prompt=\"\", data=example_sample)\n",
"\n",
"\n",
"class ExampleSample:\n",
" def __init__(self, sample, index):\n",
" self.sample = sample\n",
" self.index = index"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [],
"source": [
"async def save_generation_results(results, args):\n",
" path = f\"data/training_data/{args.training_file_name}\"\n",
"\n",
" pbar = tqdm(desc=\"Saving results\", unit=\" results\")\n",
" with jsonlines.open(path, \"a\") as writer:\n",
"\n",
" async for result in results:\n",
" writer.write(\n",
" {\n",
" \"question\": result.response[\"question\"],\n",
" \"sql\": result.data.generated_sql_query,\n",
" }\n",
" )\n",
" pbar.update()\n",
"\n",
" for example in all_examples:\n",
" writer.write(example)\n",
" pbar.update()"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 6 results [00:22, 3.31s/ results]"
]
}
],
"source": [
"args = Args()\n",
"seed_queries = load_seed_queries(args)\n",
"results = await run_query_gen_pipeline(seed_queries)\n",
"await save_generation_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Take a minute to look over the generated data. You may notice that some of the datapoints are incorrect - the SQL is invalid, the questions are duplicated, or the questions may be irrelevant. Let's continue onwards for now - but we'll return to (programmatically) clean the data later!"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Tune Llama 3 with Lamini Memory Tuning"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now it's time to tune Llama 3 with Lamini! You still want to use the Llama 3 template, so you can stream your training data with this in mind."
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [],
"source": [
"def make_question(obj):\n",
" system = \"You are an NBA analyst with 15 years of experience writing complex SQL queries.\\n\"\n",
" system += \"Consider the nba_roster table with the following schema:\\n\"\n",
" system += get_schema() + \"\\n\"\n",
" system += (\n",
" \"Write a sqlite SQL query that would help you answer the following question:\\n\"\n",
" )\n",
" user = obj[\"question\"]\n",
" return {\"system\": system, \"user\": user}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can submit your data to Lamini Tuning easily. The best defaults for the top LLMs like Llama 3 have been optimized for you."
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 30 results [00:22, 1.35 results/s]\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Uploading data....\n",
"Upload to blob completed for data.\n",
"Data pairs uploaded to blob.\n",
"\n",
"Your dataset id is: 9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e . Consider using this in the future to train using the same data. \n",
"Eg: llm.train(dataset_id='9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e')\n",
"Training job submitted! Check status of job 7502 here: https://app.lamini.ai/train/7502\n"
]
},
{
"data": {
"text/plain": [
"{'job_id': 7502,\n",
" 'status': 'SCHEDULED',\n",
" 'dataset_id': '9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e'}"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"args = Args()\n",
"llm = lamini.Lamini(model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\")\n",
"\n",
"dataset = get_dataset(args, make_question)\n",
"finetune_args = get_default_finetune_args()\n",
"\n",
"# Uncomment to train\n",
"# llm.train(\n",
"# data_or_dataset_id=dataset,\n",
"# finetune_args=finetune_args,\n",
"# is_public=True, # For sharing\n",
"# )"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" NOTE \n",
"\n",
"Tuning jobs are queued immediately after you run the above cell! Once they begin, the estimated time is 30 minutes. You can continue in this notebook by using the four pre-prepared models provided in this notebook which we tuned for your convenience. \n",
"\n",
"When your training job finishes, you can query the newly trained model by \n",
"1. Finding the model id at `https://app.lamini.ai/train`\n",
"2. Instantiating a model client with `llm = lamini.Lamini(model_name=\"\")`\n",
"\n",
"Training jobs can fail! If it does, try resubmitting your job by re-running the training cell.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"After you submit a job, you can monitor the job status at https://app.lamini.ai/train. There you'll have access to the interface shown below which will help you track jobs, view logs, and get the model ID once training is complete. \n",
"\n",
"![\"Lamini](\"assets/website.png\")
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Tuning a model takes many attempts and iterations on the generated data, by re-running evaluation and sifting through the results to adjust the data generation pipeline to cover what's still missing. \n",
"\n",
"Sometimes, those adjustments are incredibly minute—just like in prompt-engineering, it's hard to predict what those adjustments might be, so being able to quickly iterate using your evaluation pipeline and inspecting the results quickly is absolutely key.\n",
"\n",
"That's why Lamini's high-performance inference engine is built to optimize processes for both evaluation and data generation, and then unify them with tuning effectively.\n",
"\n",
"Just for a gauge of what's normal: in the creation of this notebook, over 20 models were tuned. So don't get discouraged if it's not top notch on your first try: the point is actually to build that muscle of iteration—that's the most important piece towards getting the best results.\n",
"\n",
"You'll see one of the iterations in the following sections, to get a feel for what the workflow is like."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here's a prepared tuned model, so you don't have to wait for the tuning to complete. This notebook has four prepared models for each of the four times we will tune. \n",
"\n",
"First, go ahead and ask the tuned model a question! "
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Question:\n",
" Who is the highest paid NBA player?\n",
"Answer:\n",
"select salary, name from nba_roster where SALARY!= '--' ORDER BY CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER) DESC LIMIT 1\n"
]
}
],
"source": [
"# You can replace model_name with your model_id when it's ready!\n",
"llm = lamini.Lamini(model_name=\"a5ebf1c4879569101f32444afae5adcafbfce9c5a6ed13035fd892147f7d59bc\")\n",
"\n",
"question = \"\"\"Who is the highest paid NBA player?\"\"\"\n",
"system = f\"\"\"You are an NBA analyst with 15 years of experience writing complex SQL queries. Consider the nba_roster table with the following schema:\n",
"{get_schema()}\n",
"\n",
"Write a sqlite query to answer the following question. Follow instructions exactly\"\"\"\n",
"prompt = make_llama_3_prompt(question, system)\n",
"print(\"Question:\\n\", question)\n",
"\n",
"# Ask the model to generate a sql query to answer the question\n",
"print(\"Answer:\")\n",
"print(llm.generate(prompt, max_new_tokens=200))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Much better! You can check against the database that this is correct."
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"$51,915,615|Stephen Curry\n"
]
}
],
"source": [
"!sqlite3 nba_roster.db \"select salary, name from nba_roster where SALARY!= '--' ORDER BY CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER) DESC LIMIT 1;\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Evaluate the tuned Llama 3\n",
"\n",
"To compare how results have improved quantitatively, rerun the SQL pipeline with the tuned model:"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 0 results [00:00, ? results/s]2024-06-21 14:09:34,226 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, 1, INSTR(WT,' ')) as INTEGER) FROM nba_roster WHERE WT!= 'NA') as median\n",
"Saving results: 20 results [00:26, 1.31s/ results]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 20\n",
"Total size of eval dataset: 20\n",
"Percent Valid SQL Syntax: 95.0\n",
"Percent Correct SQL Query: 75.0\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n"
]
}
],
"source": [
"# You can replace sql_model_name with your model_id when it's ready!\n",
"args = Args(sql_model_name=\"a5ebf1c4879569101f32444afae5adcafbfce9c5a6ed13035fd892147f7d59bc\")\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can see that the tuned model has 75% correct SQL (compared to 30% for base Llama 3). Bam!\n",
"\n",
"Let's take a look at the `sql_errors.jsonl` file to try and figure out what the model is getting wrong. Here is the error analysis part, which is figuring out what types of errors are occurring. You find that there are 3 types of errors:\n",
"\n",
" Error 1: The tuned model does not filter for null salaries\n",
" \n",
"\n",
"`\"What is the average salary of Power Forward players in the NBA\"`\n",
"\n",
"```sql\n",
"SELECT AVG(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary FROM nba_roster WHERE POS='PF' AND SALARY!= '--';\n",
"\n",
"12355651.6714286\n",
"```\n",
"Reference: \n",
"```sql\n",
"select avg(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary from nba_roster where POS = 'PF';\n",
"\n",
"10948045.7848101\n",
"```\n",
"
\n",
" Error 2: The tuned model incorrectly orders by desc when calculating percentile or omits the offset correction\n",
" \n",
"\n",
"`\"What is the 75th percentile salary in the NBA?\"` \n",
" `\"What is the 25th percentile salary in the NBA?\"` \n",
" `\"What is the 99th percentile salary in the NBA?\"`\n",
"\n",
"```sql\n",
"SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as salary FROM nba_roster WHERE SALARY!= '--' ORDER BY salary DESC LIMIT 1 OFFSET (SELECT COUNT(*) FROM nba_roster WHERE SALARY!= '--')*75/100-1;\n",
"\n",
"2421720\n",
"```\n",
"Reference: \n",
"```sql\n",
"SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as percentile FROM nba_roster WHERE SALARY!= '--' order by percentile limit 1 offset (select count(*) from nba_roster where SALARY != '--')*75/100-1;\n",
"\n",
"13932008\n",
"```\n",
"
\n",
" Error 3: The tuned model incorrectly used Average instead of median \n",
" \n",
"\n",
"`\"What's the median age of the Miami Heat?\"`\n",
"\n",
"```sql\n",
"SELECT AVG(AGE) FROM nba_roster WHERE team='Miami Heat';\n",
"```\n",
"\n",
"Reference:\n",
"```sql\n",
"select CAST(AGE as INTEGER) as percentile from nba_roster where team='Miami Heat' order by percentile limit 1 offset (select count(*) from nba_roster where team='Miami Heat')/2;\n",
"```\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Improve the Tuned Llama 3\n",
"\n",
"You can improve the tuned model by improving the dataset you used based on your error analysis above. To do this, you can both increase the size, coverage, and quality of your generated dataset.\n",
"\n",
"This next step will generate 10x more data. This dataset will still have quality issues, so actually playing a numbers game can help you: generating more data overall means you can filter bad examples from the dataset later and still have a hefty amount of data left."
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 6 results [00:36, 4.94s/ results]"
]
}
],
"source": [
"# If you'd like to generate more data, change num_to_generate, this cell will take longer to run!\n",
"args = Args(gold_file_name='gold-test-set.jsonl', training_file_name=\"generated_queries_large.jsonl\", num_to_generate=10)\n",
"seed_queries = load_seed_queries(args)\n",
"results = await run_query_gen_pipeline(seed_queries)\n",
"await save_generation_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here's another piece of error analysis in your data generation pipeline. After sifting through the data, one thing that stands out is that some queries and questions are duplicated, and some queries may not run.\n",
"\n",
"Here are a few improvements you can easily do — programmatically:\n",
"\n",
"1. Filter the dataset by removing duplicates\n",
"2. Only keeping queries that are valid sql.\n",
"3. Remove queries where we filter by \"Null\"\n",
"4. Returns an empty dataframe\n",
"5. Uses incorrect query components like \"AVG(HT)\" in the query\n",
"6. Add a semicolon to the end if it does not exist"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 30 results [00:36, 1.20s/ results]\n"
]
}
],
"source": [
"question_set = set()\n",
"sql_set = set()\n",
"\n",
"def is_not_valid_sql(question, sql):\n",
" try:\n",
" df = pd.read_sql(sql, con=engine)\n",
" return False\n",
" except Exception as e:\n",
" return True\n",
"\n",
"def has_null_in_sql_or_question(question, sql):\n",
" return \"null\" in sql.lower() or \"null\" in question\n",
"\n",
"def returns_empty_dataframe(question, sql):\n",
" try:\n",
" df = pd.read_sql(sql, con=engine)\n",
" return \"Empty\" in str(df) or \"None\" in str(df)\n",
" except Exception as e:\n",
" return False\n",
" \n",
"def uses_avg_on_ht_column(question, sql):\n",
" return \"avg(ht)\" in sql.lower() or \"avg(salary\" in sql.lower() \n",
"\n",
"filter_conditions = [is_not_valid_sql, has_null_in_sql_or_question, returns_empty_dataframe, uses_avg_on_ht_column]\n",
"\n",
"def training_semicolon(sql):\n",
" if sql.strip()[-1] != \";\":\n",
" return sql.strip() + \";\"\n",
" return sql\n",
"\n",
"with jsonlines.open(\"data/training_data/generated_queries_large.jsonl\", \"r\") as reader:\n",
" with jsonlines.open(\"data/training_data/generated_queries_large_filtered.jsonl\", \"w\") as writer:\n",
" for r in reader:\n",
" if r[\"question\"] in question_set or r[\"sql\"] in sql_set:\n",
" continue\n",
" question_set.add(r[\"question\"])\n",
" sql_set.add(r[\"sql\"])\n",
" \n",
" if any(c(r['question'], r['sql']) for c in filter_conditions):\n",
" continue\n",
"\n",
" sql = training_semicolon(r['sql'])\n",
" writer.write(\n",
" {\n",
" \"question\": r[\"question\"],\n",
" \"sql\": sql,\n",
" }\n",
" )"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Great! The large 1000 datapoint dataset is filtered down to 364 datapoints. This makes it way easier for the next step of sifting through the data a second time, this time more closely. You'll notice that it's the combination of analyzing and categorizing errors, with building automated pipelines to address those errors that will serve you best. It's important to dive-deep analyses of your data when tuning models, so you can reveal issues that are very difficult to detect on the surface automatically—what's helpful, however, is that you can build out reusable automated pipelines from that, which you can re-run in future iterations of model improvement, when you upgrade your base model (e.g. to Llama 4!), and even when you develop similar adjacent model applications.\n",
"\n",
"Here's what a simple manual look-over as a next step can look like:\n",
"1. Print out the SQL queries and questions for easy reading\n",
"2. Manually delete or fix obviously incorrect datapoints as you look over each datapoint"
]
},
{
"cell_type": "code",
"execution_count": 52,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"===================== 1 ======================\n",
"What college has the most players in the NBA who are 30 years old or older\n",
"SELECT COLLEGE, COUNT(*) AS count FROM nba_roster WHERE AGE >= 30 GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;\n",
" COLLEGE count\n",
"-- --------- -------\n",
" 0 -- 22\n",
"===================== 2 ======================\n",
"What is the total salary of all NBA players\n",
"SELECT SUM(CAST(SUBSTR(SALARY, 1, INSTR(SALARY, '$')-1) AS INTEGER)*1000000) FROM nba_roster;\n",
" SUM(CAST(SUBSTR(SALARY, 1, INSTR(SALARY, '$')-1) AS INTEGER)*1000000)\n",
"-- -----------------------------------------------------------------------\n",
" 0 0\n",
"===================== 3 ======================\n",
"What are the most common positions in the NBA\n",
"SELECT POS, COUNT(*) AS num_players FROM nba_roster GROUP BY POS;\n",
" POS num_players\n",
"-- ----- -------------\n",
" 0 C 81\n",
" 1 F 95\n",
" 2 G 96\n",
" 3 PF 79\n",
" 4 PG 75\n",
" 5 SF 77\n",
" 6 SG 97\n",
"===================== 4 ======================\n",
"What is the average salary for each age group in the NBA\n",
"SELECT AVG(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary, AGE as age_group FROM nba_roster WHERE SALARY!= '--' GROUP BY AGE ORDER BY age_group;\n",
" average_salary age_group\n",
"-- ---------------- -----------\n",
" 0 4.39334e+06 19\n",
" 1 4.93876e+06 20\n",
" 2 3.48698e+06 21\n",
" 3 5.22664e+06 22\n",
" 4 6.48673e+06 23\n",
" 5 1.00229e+07 24\n",
" 6 1.1199e+07 25\n",
" 7 9.53451e+06 26\n",
" 8 1.52048e+07 27\n",
" 9 1.68002e+07 28\n",
"10 1.73774e+07 29\n",
"11 1.25041e+07 30\n",
"12 1.81367e+07 31\n",
"13 1.51997e+07 32\n",
"14 2.41203e+07 33\n",
"15 2.14952e+07 34\n",
"16 1.21162e+07 35\n",
"17 2.01971e+06 36\n",
"18 1.64275e+07 37\n",
"19 2.98073e+07 38\n",
"===================== 5 ======================\n",
"What are the top 5 colleges that have produced the most NBA players\n",
"SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 5;\n",
" COLLEGE count\n",
"-- --------- -------\n",
" 0 Kentucky 28\n",
" 1 Duke 27\n",
" 2 UCLA 15\n",
" 3 Arizona 14\n",
" 4 Kansas 13\n",
"===================== 6 ======================\n",
"How many players in the NBA attended college\n",
"SELECT COUNT(*) AS num_college_players FROM nba_roster WHERE COLLEGE!= '--';\n",
" num_college_players\n",
"-- ---------------------\n",
" 0 521\n",
"===================== 7 ======================\n",
"What are the top 3 colleges with the most players in the NBA\n",
"SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 3;\n",
" COLLEGE count\n",
"-- --------- -------\n",
" 0 Kentucky 28\n",
" 1 Duke 27\n",
" 2 UCLA 15\n",
"===================== 8 ======================\n",
"What is the average age of all players in the NBA\n",
"SELECT AVG(AGE) FROM nba_roster;\n",
" AVG(AGE)\n",
"-- ----------\n",
" 0 25.655\n",
"===================== 9 ======================\n",
"What is the most represented college in the NBA\n",
"SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;\n",
" COLLEGE count\n",
"-- --------- -------\n",
" 0 Kentucky 28\n",
"===================== 10 ======================\n",
"Which college has produced the most NBA players\n",
"SELECT COLLEGE, COUNT(*) as count FROM nba_roster GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;\n",
" COLLEGE count\n",
"-- --------- -------\n",
" 0 -- 79\n",
"===================== 11 ======================\n",
"What is the average height of NBA players\n",
"SELECT AVG(CAST(SUBSTR(HT, 1, INSTR(HT,' ')-1) AS INTEGER) + CAST(SUBSTR(HT, INSTR(HT,' ')+1) AS FLOAT)/12) AS average_height FROM nba_roster;\n",
" average_height\n",
"-- ----------------\n",
" 0 6.54986\n"
]
}
],
"source": [
"limit = 10\n",
"with jsonlines.open(\"data/training_data/generated_queries_large_filtered.jsonl\", \"r\") as reader:\n",
" for i, r in enumerate(reader):\n",
" print(f\"===================== {i+1} ======================\")\n",
" print(r['question']) \n",
" print(r['sql'])\n",
" df = pd.read_sql(r['sql'], con=engine)\n",
" print(tabulate(df, headers='keys', tablefmt='sqlite'))\n",
" limit -= 1\n",
" if limit < 0: # Remove this limit if you'd like to pretty print all the data\n",
" break"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" NOTE \n",
"\n",
"This step can take time to do, for example an hour filtering through ~350 datapoints. VSCode had a view for the output, which you can get to by clicking into the \"...\" inside the output cell.\n",
"\n",
"What you're looking for are obviously incorrect datapoints to quickly remove.\n",
"\n",
"You are also scanning for interesting datapoints you had not thought to include in the Gold Dataset.\n",
"\n",
"One hack was to reverse the order of inspection and start at the bottom of the file so you could keep the numbers relevant.\n",
"\n",
"Here's an example datapoint which is incorrect upon inspection:\n",
"\n",
"```bash\n",
"\n",
"===================== 345 ======================\n",
"What is the average age of the tallest players in the NBA\n",
"SELECT NAME, TEAM, POS, AVG(AGE) AS AVG_AGE FROM nba_roster WHERE CAST(SUBSTR(HT, 1, INSTR(HT,' ')-1) AS INTEGER) + CAST(SUBSTR(HT, INSTR(HT,' ')+1) AS FLOAT)/12 > 6.67 GROUP BY NAME, TEAM, POS ORDER BY AVG_AGE DESC LIMIT 1;\n",
" NAME Team POS AVG_AGE\n",
"-- ------------ ------------------ ----- ---------\n",
" 0 LeBron James Los Angeles Lakers SF 38\n",
" \n",
"```\n",
"
\n",
"\n",
"![\"Side](\"assets/manual_filtering.png\")
\n",
"\n",
"\n",
"\n",
"\n",
"After doing this, you are left with 220 filtered and cleaned datapoints in a new file created manually `generated_queries_large_filtered_cleaned.jsonl`.\n",
"\n",
"You can use this to tune the next iteration of your model."
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Uploading data....\n",
"Upload to blob completed for data.\n",
"Data pairs uploaded to blob.\n",
"\n",
"Your dataset id is: c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704 . Consider using this in the future to train using the same data. \n",
"Eg: llm.train(dataset_id='c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704')\n",
"Training job submitted! Check status of job 7504 here: https://app.lamini.ai/train/7504\n"
]
},
{
"data": {
"text/plain": [
"{'job_id': 7504,\n",
" 'status': 'SCHEDULED',\n",
" 'dataset_id': 'c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704'}"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"args = Args(training_file_name=\"archive/generated_queries_large_filtered_cleaned.jsonl\")\n",
"llm = lamini.Lamini(model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\")\n",
"\n",
"dataset = get_dataset(args, make_question)\n",
"finetune_args = get_default_finetune_args()\n",
"\n",
"# Uncomment to train\n",
"# llm.train(\n",
"# data_or_dataset_id=dataset,\n",
"# finetune_args=finetune_args,\n",
"# is_public=True, # For sharing\n",
"# )"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Iteratively tune and improve the tuned Llama 3"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 0 results [00:00, ? results/s]2024-06-21 14:10:34,562 [ERROR] Failed to run SQL query: SELECT NAME FROM nba_roster WHERE TEAM='Brooklyn Nets' AND AGE=MAX(AGE);\n",
"Saving results: 20 results [00:16, 1.21 results/s]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 20\n",
"Total size of eval dataset: 20\n",
"Percent Valid SQL Syntax: 95.0\n",
"Percent Correct SQL Query: 90.0\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n"
]
}
],
"source": [
"# You can replace sql_model_name with your model_id when it's ready!\n",
"args = Args(sql_model_name=\"63fd73a775daf24216b46c680a1e963a8d1e02b21bca43fcea6c26737d2e887e\", gold_file_name = \"gold-test-set.jsonl\")\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Yay! The new model improved to 90% correct on the gold dataset. You can continue this process, looking over the errors and adding, editing, and filtering better data. You can do this by continuing to build more involved programmatic pipelines and skimming manually to understand patterns in the data—until you are satisfied with the accuracy. \n",
"\n",
"Accuracy on your Gold Dataset is a function of effort. You can reach near 100% accuracy on the Gold Dataset, for example. Typically, the right move is to have the easiest examples in the Gold Dataset that your best model still gets wrong. \n",
"\n",
"Once you're satisfied with the results on your Gold Dataset, it's time to make your Gold Dataset harder, and then repeat the process of improving the model again."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Iterate on the Evaluation Dataset\n",
"\n",
"Now that you've gotten good performance on the original Gold Dataset, it's a good time to expand the dataset to make evaluation harder, and in turn, get your tuned model to become even more capable. The augmented `gold-test-set-v2.jsonl` has a few more handcrafted datapoints looking to add coverage over additional complex queries.\n",
"\n",
"First, on your new Gold Dataset, re-establish a baseline performance of Llama 3 on `gold-test-set-v2.jsonl`."
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 0 results [00:00, ? results/s]2024-06-21 14:10:42,361 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) AS weight FROM nba_roster WHERE WT IS NOT NULL\n",
"2024-06-21 14:10:42,363 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) FROM nba_roster WHERE WT!= 'NA';\n",
"2024-06-21 14:10:42,365 [ERROR] Failed to run SQL query: SELECT PERCENTILE(SALARY, 0.25) FROM nba_roster WHERE SALARY!= '--';\n",
"2024-06-21 14:10:42,366 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.75) FROM (SELECT CAST(SUBSTR(salary, 2) AS INTEGER) AS salary FROM nba_roster WHERE salary!= '--') AS subquery\n",
"2024-06-21 14:10:42,368 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.99) FROM nba_roster WHERE salary IS NOT NULL\n",
"2024-06-21 14:10:42,504 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS average_salary FROM nba_roster WHERE POS = 'PF' AND SALARY!= '--';\n",
"2024-06-21 14:10:47,647 [ERROR] Failed to run SQL query: SELECT POS, MAX(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY POS\n",
"2024-06-21 14:10:47,651 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTRING(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL\n",
"2024-06-21 14:10:47,652 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL\n",
"2024-06-21 14:10:49,132 [ERROR] Failed to run SQL query: SELECT Team, AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) AS Height) AS Average_Height FROM nba_roster GROUP BY Team ORDER BY Average_Height DESC LIMIT 1\n",
"2024-06-21 14:10:49,134 [ERROR] Failed to run SQL query: SELECT Team, AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS AVG_Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY Team ORDER BY AVG_Salary LIMIT 1\n",
"2024-06-21 14:10:49,135 [ERROR] Failed to run SQL query: SELECT Team, SUM(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS TotalSalary FROM nba_roster WHERE SALARY!= '--' GROUP BY Team ORDER BY TotalSalary DESC LIMIT 1\n",
"2024-06-21 14:10:52,500 [ERROR] Failed to run SQL query: SELECT * FROM nba_roster WHERE COLLEGE = '--\n",
"2024-06-21 14:10:55,221 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) FROM nba_roster WHERE SALARY!= '--';\n",
"2024-06-21 14:10:55,223 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) FROM nba_roster WHERE SALARY!= '--';\n",
"Saving results: 36 results [00:27, 1.70 results/s]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 40\n",
"Total size of eval dataset: 40\n",
"Percent Valid SQL Syntax: 62.5\n",
"Percent Correct SQL Query: 35.0\n"
]
}
],
"source": [
"args = Args(gold_file_name='gold-test-set-v2.jsonl')\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Looks like there's plenty of room for improvment! You know how this works now:\n",
"1. Generate a new training dataset \n",
"2. Train a model\n",
"3. Evaluate"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 55 results [00:36, 1.53 results/s]\n"
]
}
],
"source": [
"args = Args(gold_file_name='gold-test-set-v2.jsonl', training_file_name=\"generated_queries_v2.jsonl\")\n",
"seed_queries = load_seed_queries(args)\n",
"results = await run_query_gen_pipeline(seed_queries)\n",
"await save_generation_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Like before, go ahead and tune a model using this dataset."
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Uploading data....\n",
"Upload to blob completed for data.\n",
"Data pairs uploaded to blob.\n",
"\n",
"Your dataset id is: b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c . Consider using this in the future to train using the same data. \n",
"Eg: llm.train(dataset_id='b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c')\n",
"Training job submitted! Check status of job 7505 here: https://app.lamini.ai/train/7505\n"
]
},
{
"data": {
"text/plain": [
"{'job_id': 7505,\n",
" 'status': 'SCHEDULED',\n",
" 'dataset_id': 'b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c'}"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"args = Args(training_file_name=\"generated_queries_v2.jsonl\")\n",
"llm = lamini.Lamini(model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\")\n",
"\n",
"dataset = get_dataset(args, make_question)\n",
"finetune_args = get_default_finetune_args()\n",
"\n",
"# Uncomment to train\n",
"# llm.train(\n",
"# data_or_dataset_id=dataset,\n",
"# finetune_args=finetune_args,\n",
"# is_public=True, # For sharing\n",
"# )"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"2024-06-21 14:11:49,387 [ERROR] Failed to run SQL query: SELECT team FROM nba_roster GROUP BY team ORDER BY COUNT(*) AS team_size ASC LIMIT 1;\n",
"Saving results: 40 results [01:16, 1.90s/ results]\n",
"2024-06-21 14:11:57,590 [ERROR] Failed to run SQL query: SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as percentile FROM nba_roster WHERE SALARY!= '--' order by percentile order by 1 ASC limit 1 offset (select count(*) from nba_roster where SALARY!= '--')*75/100-1;\n",
"Saving results: 40 results [00:25, 1.56 results/s]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 40\n",
"Total size of eval dataset: 40\n",
"Percent Valid SQL Syntax: 95.0\n",
"Percent Correct SQL Query: 75.0\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n"
]
}
],
"source": [
"# You can replace sql_model_name with your model_id when it's ready!\n",
"args = Args(sql_model_name=\"2e83542ad6df532dd861ca0d3882cd861c2e5df3cefe5dc1f98f5028069d0e8b\", gold_file_name='gold-test-set-v2.jsonl')\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Like before, it's time for a large data generation and cleaning workflow on Lamini's optimized heavy-inference engine."
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 11 results [01:01, 3.64s/ results]"
]
}
],
"source": [
"args = Args(gold_file_name='gold-test-set-v2.jsonl', training_file_name=\"generated_queries_v2_large.jsonl\")\n",
"seed_queries = load_seed_queries(args)\n",
"results = await run_query_gen_pipeline(seed_queries)\n",
"await save_generation_results(results, args)"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 55 results [01:02, 1.13s/ results]\n"
]
}
],
"source": [
"with jsonlines.open(\"data/training_data/generated_queries_v2_large.jsonl\", \"r\") as reader:\n",
" with jsonlines.open(\"data/training_data/generated_queries_v2_large_filtered.jsonl\", \"w\") as writer:\n",
" for r in reader:\n",
" if r[\"question\"] in question_set or r[\"sql\"] in sql_set:\n",
" continue\n",
" question_set.add(r[\"question\"])\n",
" sql_set.add(r[\"sql\"])\n",
" \n",
" if any(c(r['question'], r['sql']) for c in filter_conditions):\n",
" continue\n",
"\n",
" sql = training_semicolon(r['sql'])\n",
" writer.write(\n",
" {\n",
" \"question\": r[\"question\"],\n",
" \"sql\": sql,\n",
" }\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"limit = 10\n",
"with jsonlines.open(\"data/training_data/generated_queries_v2_large_filtered.jsonl\", \"r\") as reader:\n",
" for i, r in enumerate(reader):\n",
" print(f\"===================== {i+1} ======================\")\n",
" print(r['question']) \n",
" print(r['sql'])\n",
" df = pd.read_sql(r['sql'], con=engine)\n",
" print(tabulate(df, headers='keys', tablefmt='sqlite'))\n",
" limit -= 1\n",
" if limit < 0: # Remove this limit if you'd like to pretty print all the data\n",
" break"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Uploading data....\n",
"Upload to blob completed for data.\n",
"Data pairs uploaded to blob.\n",
"\n",
"Your dataset id is: cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8 . Consider using this in the future to train using the same data. \n",
"Eg: llm.train(dataset_id='cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8')\n",
"Training job submitted! Check status of job 7520 here: https://app.lamini.ai/train/7520\n"
]
},
{
"data": {
"text/plain": [
"{'job_id': 7520,\n",
" 'status': 'SCHEDULED',\n",
" 'dataset_id': 'cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8'}"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"args = Args(training_file_name=\"archive/generated_queries_v2_large_filtered_cleaned.jsonl\")\n",
"llm = lamini.Lamini(model_name=\"meta-llama/Meta-Llama-3.1-8B-Instruct\")\n",
"\n",
"dataset = get_dataset(args, make_question)\n",
"finetune_args = get_default_finetune_args()\n",
"\n",
"# Uncomment to train\n",
"# llm.train(\n",
"# data_or_dataset_id=dataset,\n",
"# finetune_args=finetune_args,\n",
"# is_public=True, # For sharing\n",
"# )"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Evaluate the tuned Llama 3 (again)\n",
"\n",
"Now that you've tuned another model, you can finally check and see how your tuning impacted the quality of the SQL output—and compare it quantitatively."
]
},
{
"cell_type": "code",
"execution_count": 38,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"Saving results: 40 results [00:25, 1.57 results/s]"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Total results: 40\n",
"Total size of eval dataset: 40\n",
"Percent Valid SQL Syntax: 100.0\n",
"Percent Correct SQL Query: 95.0\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n"
]
}
],
"source": [
"# You can replace sql_model_name with your model_id when it's ready!\n",
"args = Args(sql_model_name=\"3f7e740c0ea2227631a30d293b51564ad1b80727c3768a3b136fbae93170c1e2\", gold_file_name='gold-test-set-v2.jsonl')\n",
"dataset = load_gold_dataset(args)\n",
"results = await run_eval(dataset, args)\n",
"save_eval_results(results, args)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You've improved accuracy from 30% to 95% for valid SQL query accuracy by tuning Llama 3! Amazing.\n",
"\n",
"
Lessons
\n",
"\n",
"As a realistic overlay, here are details on what it took to create this notebook:\n",
"1. Multiple automated and manual filtering and editing passes over the tuning data\n",
"2. Iterated on the Gold Dataset by adding datapoints you want the model to have coverage over\n",
"3. Many tuning jobs (30+) on different iterations of the tuning data\n",
"4. Evaluation pipeline construction and prompt-engineering — to have robust evaluation\n",
"5. Error analysis by reading the errors and determining if it's an error in our evaluation pipeline or a model error\n",
"\n",
"All this to say - Lamini Memory Tuning is a highly iterative process, don't be discouraged if it doesn't work the first time! Trust that incremental progress can be made and codified by storing training datasets.\n",
"\n",
"Keep in mind that you can always improve the model - even the archived datasets we hand filtered can be improved for further performance. Time box the process and don't hesitate to move on to the next step!\n",
"\n",
"Shipping the model in production can often gather better feedback and datapoints to incorporate into the next tuning iteration—this makes gathering data more of an automated and you can get data that your users care about but that you wouldn't have thought of in a vacuum. To make it less daunting, \"shipping in production\" can even start with a limited release to 5 users.\n",
"\n",
"Stay tuned for a follow on notebook where we explore How to build a SQL LLM on Lamini using Llama 3!\n",
"\n",
"[Contact us at Lamini](https://www.lamini.ai/contact) to learn even better techniques for building highly accurate LLM models, as well as running this all in your own VPC or on-premise environments.\n",
"\n",
""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.12.3"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
![\"Open](\"https://colab.research.google.com/assets/colab-badge.svg\"){kind=icon}