P1-NL2SQL/nl2sql_prompt_OpenAI.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/P1-NL2SQL/nl2sql_prompt_OpenAI.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c23b4dd1",
      "metadata": {
        "id": "c23b4dd1"
      },
      "source": [
        "<div align=\"center\">\n",
        "<h1><a href=\"https:/peremartra/Large-Language-Model-Notebooks-Course\">Learn by Doing LLM Projects</a></h1>\n",
        "    <h3>Understand And Apply Large Language Models</h3>\n",
        "    <h2>Natural Language to SQL with LLMs</h2>\n",
        "    <h3>Create a prompt to generate SQL with OpenAI Models</h3>\n",
        "    by <b>Pere Martra</b>\n",
        "</div>\n",
        "\n",
        "<br>\n",
        "\n",
        "<div align=\"center\">\n",
        "    &nbsp;\n",
        "    <a target=\"_blank\" href=\"https://www.linkedin.com/in/pere-martra/\"><img src=\"https://img.shields.io/badge/style--5eba00.svg?label=LinkedIn&logo=linkedin&style=social\"></a>\n",
        "    \n",
        "</div>\n",
        "\n",
        "<br>\n",
        "<hr>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7d930c89-4954-4e39-be5a-601c1dd89512",
      "metadata": {
        "id": "7d930c89-4954-4e39-be5a-601c1dd89512"
      },
      "source": [
        "In one of the [first lessons in the course](https:/peremartra/Large-Language-Model-Notebooks-Course/blob/main/1-Introduction%20to%20LLMs%20with%20OpenAI/nl2sql.ipynb), we did a Natural Language to SQL Generator using the OpenAI API. It works relatively fine, but the prompt was built in a basic style.\n",
        "\n",
        "Now we are going to rebuild the prompt following the instructions in a paper from the Ohio University: [How to Prompt LLMs for Text-to-SQL: A Study in Zero-shot, Single-domain, and Cross-domain Settings](https://arxiv.org/abs/2305.11853).\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a1d00720",
      "metadata": {
        "id": "a1d00720"
      },
      "outputs": [],
      "source": [
        "#First install the OpenAI library to acces gpt-3.5-turbo model.\n",
        "!pip install -q openai==1.29.0"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a03f026a",
      "metadata": {
        "id": "a03f026a",
        "outputId": "4b5bcae2-5eb4-4738-b920-87d60cb2394d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "OpenAI API Key: ··········\n"
          ]
        }
      ],
      "source": [
        "#You need an OpenAI key in order to use the OpenAI API https://platform.openai.com/account/api-keys\n",
        "from getpass import getpass\n",
        "import openai\n",
        "openai.api_key=getpass(\"OpenAI API Key: \")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "53a05b80-88fd-42b2-ba89-763074ae74e9",
      "metadata": {
        "id": "53a05b80-88fd-42b2-ba89-763074ae74e9"
      },
      "source": [
        "## The old Prompt"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "922f8d24",
      "metadata": {
        "id": "922f8d24"
      },
      "outputs": [],
      "source": [
        "#The old prompt\n",
        "old_context = [ {'role':'system', 'content':\"\"\"\n",
        "you are a bot to assist in create SQL commands, all your answers should start with \\\n",
        "this is your SQL, and after that an SQL that can do what the user request. \\\n",
        "Your Database is composed by a SQL database with some tables. \\\n",
        "Try to Maintain the SQL order simple.\n",
        "Put the SQL command in white letters with a black background, and just after \\\n",
        "a simple and concise text explaining how it works.\n",
        "If the user ask for something that can not be solved with an SQL Order \\\n",
        "just answer something nice and simple, maximum 10 words, asking him for something that \\\n",
        "can be solved with SQL.\n",
        "\"\"\"} ]\n",
        "\n",
        "old_context.append( {'role':'system', 'content':\"\"\"\n",
        "first table:\n",
        "{\n",
        "  \"tableName\": \"employees\",\n",
        "  \"fields\": [\n",
        "    {\n",
        "      \"nombre\": \"ID_usr\",\n",
        "      \"tipo\": \"int\"\n",
        "    },\n",
        "    {\n",
        "      \"nombre\": \"name\",\n",
        "      \"tipo\": \"varchar\"\n",
        "    }\n",
        "  ]\n",
        "}\n",
        "\"\"\"\n",
        "})\n",
        "\n",
        "old_context.append( {'role':'system', 'content':\"\"\"\n",
        "second table:\n",
        "{\n",
        "  \"tableName\": \"salary\",\n",
        "  \"fields\": [\n",
        "    {\n",
        "      \"nombre\": \"ID_usr\",\n",
        "      \"type\": \"int\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"year\",\n",
        "      \"type\": \"date\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"salary\",\n",
        "      \"type\": \"float\"\n",
        "    }\n",
        "  ]\n",
        "}\n",
        "\"\"\"\n",
        "})\n",
        "\n",
        "old_context.append( {'role':'system', 'content':\"\"\"\n",
        "third table:\n",
        "{\n",
        "  \"tablename\": \"studies\",\n",
        "  \"fields\": [\n",
        "    {\n",
        "      \"name\": \"ID\",\n",
        "      \"type\": \"int\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"ID_usr\",\n",
        "      \"type\": \"int\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"educational_level\",\n",
        "      \"type\": \"int\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"Institution\",\n",
        "      \"type\": \"varchar\"\n",
        "    },\n",
        "    {\n",
        "      \"name\": \"Years\",\n",
        "      \"type\": \"date\"\n",
        "    }\n",
        "    {\n",
        "      \"name\": \"Speciality\",\n",
        "      \"type\": \"varchar\"\n",
        "    }\n",
        "  ]\n",
        "}\n",
        "\"\"\"\n",
        "})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "377acaae-7dd0-4d13-bc68-9e33741c231c",
      "metadata": {
        "id": "377acaae-7dd0-4d13-bc68-9e33741c231c"
      },
      "source": [
        "## New Prompt.\n",
        "We are going to improve it following the instructions of a Paper from the Ohio University: [How to Prompt LLMs for Text-to-SQL: A Study in Zero-shot, Single-domain, and Cross-domain Settings](https://arxiv.org/abs/2305.11853).\n",
        "\n",
        "For each table, we will define the structure using the same syntax as in a SQL create table command, and add the sample rows of the content.\n",
        "\n",
        "Finally, at the end of the prompt, we'll include some example queries with the SQL that the model should generate. This technique is called Few-Shot Samples, in which we provide the prompt with some examples to assist it in generating the correct SQL.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5334f942",
      "metadata": {
        "id": "5334f942"
      },
      "outputs": [],
      "source": [
        "context = [ {'role':'system', 'content':\"\"\"\n",
        "    create table employees(\n",
        "        ID_Usr INT primary key,\n",
        "        name VARCHAR);\n",
        "    /*3 example rows\n",
        "    select * from employees limit 3;\n",
        "    ID_Usr    name\n",
        "    1344      George StPierre\n",
        "    2122      Jon jones\n",
        "    1265      Anderson Silva\n",
        "    */\n",
        "\n",
        "    create table salary(\n",
        "        ID_Usr INT,\n",
        "        year DATE,\n",
        "        salary FLOAT,\n",
        "        foreign key (ID_Usr) references employees(ID_Usr));\n",
        "    /*3 example rows\n",
        "    select * from salary limit 3\n",
        "    ID_Usr    date          salary\n",
        "    1344      01/01/2023    61000\n",
        "    1344      01/01/2022    60000\n",
        "    1265      01/01/2023    55000\n",
        "    */\n",
        "\n",
        "    create table studies(\n",
        "        ID_study INT,\n",
        "        ID_Usr INT,\n",
        "        educational_level INT,  /* 5=phd, 4=Master, 3=Bachelor */\n",
        "        Institution VARCHAR,\n",
        "        Years DATE,\n",
        "        Speciality VARCHAR,\n",
        "        primary key (ID_study, ID_Usr),\n",
        "        foreign key(ID_Usr) references employees (ID_Usr));\n",
        "    /*3 example rows\n",
        "    select * from studies limit 3\n",
        "    ID_Study ID_Usr educational_level Institution    Years       Speciality\n",
        "    2782     1344   3                 UC San Diego   01/01/2010  Bachelor of Science in Marketing\n",
        "    2334     1344   5                 MIT            01/01/2023  Phd. Data Science.\n",
        "    2782     2122   3                 UC San Diego   01/01/2010  Bachelor of Science in Marketing\n",
        "    */\n",
        "\"\"\"} ]\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "330e69b0-3f5f-4bb2-8185-aedded2bb993",
      "metadata": {
        "id": "330e69b0-3f5f-4bb2-8185-aedded2bb993"
      },
      "outputs": [],
      "source": [
        "#FEW SHOT SAMPLES\n",
        "context.append( {'role':'system', 'content':\"\"\"\n",
        " -- Maintain the SQL order simple and efficient as you can, using valid SQL Lite, answer the following questions for the table provided above.\n",
        " Question: How Many employes we have with a salary bigger than 50000?\n",
        "SELECT COUNT(*) AS total_employees\n",
        "FROM employees e\n",
        "INNER JOIN salary s ON e.ID_Usr = s.ID_Usr\n",
        "WHERE s.salary > 50000;\n",
        " Question: Return the names of the three people who have had the highest salary increase in the last three years.\n",
        "SELECT e.name\n",
        "FROM employees e\n",
        "JOIN salary s ON e.ID_usr = s.ID_usr\n",
        "WHERE s.year >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)\n",
        "GROUP BY e.name\n",
        "ORDER BY (MAX(s.salary) - MIN(s.salary)) DESC\n",
        "LIMIT 3;\n",
        "\"\"\"\n",
        "})"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b90f417a",
      "metadata": {
        "id": "b90f417a"
      },
      "outputs": [],
      "source": [
        "#Functio to call the model.\n",
        "def return_CCRMSQL(user_message, context):\n",
        "\n",
        "    newcontext = context.copy()\n",
        "    newcontext.append({'role':'user', 'content':\"question: \" + user_message})\n",
        "\n",
        "    response = openai.chat.completions.create(\n",
        "            model=\"gpt-3.5-turbo\",\n",
        "            messages=newcontext,\n",
        "            temperature=0,\n",
        "        )\n",
        "\n",
        "    return (response.choices[0].message.content)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "9e0a4c11-dfe1-46fe-ac2b-3ff825f9749c",
      "metadata": {
        "id": "9e0a4c11-dfe1-46fe-ac2b-3ff825f9749c"
      },
      "source": [
        "## NL2SQL Samples\n",
        "We're going to review some examples generated with the old prompt and others with the new prompt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "59e8202c-ce34-487e-9037-c65a263423ed",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "59e8202c-ce34-487e-9037-c65a263423ed",
        "outputId": "8d446ea5-f5ef-434f-cdea-5d260e769f4c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "```sql\n",
            "SELECT e.name AS best_paid_employee\n",
            "FROM employees e\n",
            "JOIN salary s ON e.ID_Usr = s.ID_Usr\n",
            "ORDER BY s.salary DESC\n",
            "LIMIT 1;\n",
            "```\n"
          ]
        }
      ],
      "source": [
        "#new\n",
        "context_user = context.copy()\n",
        "print(return_CCRMSQL(\"The name of the best paid employee\", context_user))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9c4766b1-48a9-456e-bc6c-4b6f41909aa4",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9c4766b1-48a9-456e-bc6c-4b6f41909aa4",
        "outputId": "42d351b8-df58-428e-a6c6-be18a536d13d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "This is your SQL:\n",
            "```sql\n",
            "SELECT e.name\n",
            "FROM employees e\n",
            "JOIN salary s ON e.ID_usr = s.ID_usr\n",
            "ORDER BY s.salary DESC\n",
            "LIMIT 1;\n",
            "```\n",
            "\n",
            "This SQL query selects the name of the employee who is the best paid by joining the \"employees\" table with the \"salary\" table on the ID_usr field, then ordering the results by salary in descending order and limiting the output to only the top result.\n"
          ]
        }
      ],
      "source": [
        "#old\n",
        "old_context_user = old_context.copy()\n",
        "print(return_CCRMSQL(\"The name of the best paid employee\", old_context_user))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "38d8d370-0524-4a28-bd54-5e5cddb08e2c",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "38d8d370-0524-4a28-bd54-5e5cddb08e2c",
        "outputId": "1d2987f2-a56a-4413-858c-b282ee29d0e2"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "```sql\n",
            "SELECT AVG(s.salary) AS average_salary\n",
            "FROM employees e\n",
            "JOIN studies st ON e.ID_Usr = st.ID_Usr\n",
            "JOIN salary s ON e.ID_Usr = s.ID_Usr\n",
            "WHERE st.educational_level = 3;\n",
            "```\n"
          ]
        }
      ],
      "source": [
        "#new\n",
        "print(return_CCRMSQL(\"What is the average salary of employees with a Bachelor's degree?\", context_user))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5aa318d4-dd9f-41db-8ff4-c1d87220f766",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5aa318d4-dd9f-41db-8ff4-c1d87220f766",
        "outputId": "a04f1c28-9815-4d9e-e8c5-0b93f4862da3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "This is your SQL:\n",
            "```sql\n",
            "SELECT AVG(s.salary) AS average_salary\n",
            "FROM employees e\n",
            "JOIN studies st ON e.ID_usr = st.ID_usr\n",
            "JOIN salary s ON e.ID_usr = s.ID_usr\n",
            "WHERE st.educational_level = 'Bachelor'\n",
            "```\n",
            "\n",
            "This SQL query calculates the average salary of employees who have a Bachelor's degree by joining the employees, studies, and salary tables on the employee ID. It then filters the results to only include employees with a Bachelor's degree and calculates the average salary for this group.\n"
          ]
        }
      ],
      "source": [
        "#old\n",
        "print(return_CCRMSQL(\"What is the average salary of employees with a Bachelor's degree?\", old_context_user))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "0f31968e-74ad-4ae2-9537-b3d550b1be47",
      "metadata": {
        "id": "0f31968e-74ad-4ae2-9537-b3d550b1be47"
      },
      "source": [
        "# Conclusion\n",
        "\n",
        "I used this structure of prompt in several projects to generate SQL from natural language, and its result is really good. For simple Databases structure like the one in the notebook the differences in the results are really small, we must understand that GPT3.5-turbo is an excellent Large Language Model and well-trained for SQL generation.\n",
        "\n",
        "As you can see in the second samples, as we increase the complexity of the question, we can find some differences in the SQL returned by the Model. It is not an easy task to decide which one is better or more efficient.\n",
        "\n",
        "If you have access to a database with real data just try to obtain some SQL orders using a prompt with this structure and test by yourself how it works."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7810e219-cbeb-4350-a2a0-77fead1049bd",
      "metadata": {
        "id": "7810e219-cbeb-4350-a2a0-77fead1049bd"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "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.10.12"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}