text2sql.ipynb

{
  "cells": [
    {
      "cell_type": "raw",
      "metadata": {},
      "source": [
        "---\n",
        "editor: \n",
        "  markdown: \n",
        "    wrap: 72\n",
        "editor_options: \n",
        "  chunk_output_type: console\n",
        "---"
      ],
      "id": "631faead"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# 자연어 SQL\n",
        "\n",
        "1950년대부터 본격적으로 컴퓨터가 도입되면서 CLI를 필두로 \n",
        "다양한 사용자 인터페이스(User Interface)가 적용되었다.\n",
        "스티브 잡스의 애플사는 매킨토시 GUI에 이어 아이폰 모바일 인터페이스를 \n",
        "일반화시켰다면, 최근 챗GPT는 언어 사용자 인터페이스(LUI)를 통해\n",
        "각 분야에 혁신을 예고하고 있다. 챗GPT 데이터 과학도 사용자 관점에서 보자. \n",
        "기존 R, 파이썬, SQL, 엑셀 등 데이터 과학 구문을 머리속에 암기하고 있거나 구글이나 \n",
        "네이버를 통해 중요 키워드를 통해 문제를 해결해야 했었다. 하지만, 이제 챗GPT가 자연어를 이해하기 때문에 데이터 전처리, 통계 작업, 데이터 분석, 시각화, 모형개발 등 \n",
        "데이터 과학 전반에 변화는 필연적이다.\\index{User Inferface!CLI}\\index{User Inferface!GUI}\\index{User Inferface!WUI}\\index{User Inferface!MUI}\n",
        "\\index{User Inferface!LUI}\\index{사용자 인터페이스} [@wickham2023r] [@wickham2019welcome] [@gozalo2023chatgpt]\n",
        "\n",
        "\n",
        "```{mermaid}\n",
        "%%| label: fig-ui-evolution\n",
        "%%| fig-cap: 사용자 인터페이스 진화\n",
        "\n",
        "flowchart TB\n",
        "    \n",
        "    subgraph A[\"사용자 인터페이스\"]\n",
        "    direction LR\n",
        "        CLI[\"CLI 1950~<br>Command Line Interface<br> GUI 1970~<br> Graphic User Inferface\"]     --> WIMI\n",
        "        WIMI[\"Web Interface <br> 1994~ <br> Mobile Interface <br> 2007~\"] --> NUI[\"LUI 2023~<br> Language <br> User Interface\"]\n",
        "    end\n",
        "\n",
        "classDef default fill:#f9f7f7,stroke:#333,stroke-width:2px;\n",
        "classDef A font-size:16px;\n",
        "\n",
        "```\n",
        "\n",
        "\n",
        "\n",
        "챗GPT는 인터넷에서 방대한 양의 데이터를 학습하여 이를 정말 잘 압축한 하나의 저장소로 이해할 수 있다. 따라서, 압축을 풀게 되면 정확히 원본을 복원할 수 있는 부분도 있지만, 그렇지 못한 부분도 당영히 있게 된다.\n",
        "\n",
        "챗GPT를 “웹의 흐릿한 JPEG”[@chiang2023chatgpt]으로 비유하고 있다. JPEC 기술 자체는 손실 압축기술로 무손실 압축기술로 대표적인 PNG와 대비된다. 흐릿한 이미지가 선명하지 않거나 정확하지 않은 것처럼 ChatGPT도 항상 완벽한 답변을 제공하거나 모든 질문을 제대로 이해하는 것도 아니다. 하지만 사용자와의 대화를 기반으로 끊임없이 학습하고 개선하고 있다. 더 많은 사람들이 챗GPT를 사용할수록 사람의 언어를 더 잘 이해하고 반응할 수 있게 개발된 기술이다.\n",
        "\n",
        "## 프롬프트 공학\n",
        "\n",
        "**프롬프트 공학(Prompt Engineering)**은 챗GPT와 같은 AI 언어 모형로부터 구체적이고 정확하며 관련성 있는 응답을 도출하기 위해 프롬프트(Prompt, 지시명령어)를 설계하고 개선하는 과정이다. 프롬프트의 품질이 GPT 모형 출력결과에 큰 영향을 미칠 수 있기 때문에 이 작업은 매우 중요하다. 프롬프트 엔지니어링의 목표는 사용자와 AI 모델 사이 커뮤니케이션을 최적화하여 AI 시스템의 유용성과 효율성을 향상시키는 것이다.\n",
        "\n",
        "프롬프트 엔지니어링은 반복적인 작업과정으로 AI의 응답에 따라 프롬프트를 조정하고 개선해야 할 수도 있다는 점을 항상 염두에 두고, 다음 프롬프트 구성요소를 프롬프트에 녹여 제작할 경우 AI 언어 모델이 목표에 부합하는 정확하고 관련성 있는 구체적인 답변을 효과적으로 생성할 수 있다.\n",
        "\n",
        "AI 언어 모델과의 효과적인 커뮤니케이션을 위해 프롬프트를 작성할 때 고려해야 할 몇 가지 구성 요소가 있다. 잘 만들어진 프롬프트의 몇 가지 핵심 구성 요소를 다음과 같이 정리할 수 있다.\n",
        "\n",
        "- 명확성: 프롬프트는 명확하고 이해하기 쉬워야 한다. 즉, AI 언어모델이 혼동할 수 있는 전문 용어, 은어 또는 모호한 언어는 사용하지 않는다.\n",
        "\n",
        "- 맥락(Context): AI 언어모델이 해결해야 할 주제나 지시 업무를 파악하는 데 도움이 되는 충분한 **맥락(Context)**를 제공한다. 질문 혹은 요청과 관련된 배경 정보, 구체적인 세부 정보 또는 예시가 포함된다.\n",
        "\n",
        "- 구체성: AI 언어모델이 원하는 답변으로 안내할 수 있도록 프롬프트를 최대한 구체적으로 작성한다. 답변의 형식, 정보의 범위 또는 집중적으로 다루고 싶은 주제의 특정 측면을 지정하는 행위가 포함된다.\n",
        "\n",
        "- 모호성 제거: AI 언어모델이 질문을 오해하거나 관련 없는 답변을 제공할 가능성을 줄이려면 프롬프트에 모호성이 있는지 확인한다. 프롬프트가 여러 가지 의미로 해석될 수 있는 경우, 모호함이 없도록 프롬프트를 다시 작성한다.\n",
        "\n",
        "- 제약 조건: 단어 수 제한이나 주제의 특정 측면과 같은 제약 조건을 포함하면 AI 언어모델이 보다 집중적이고 관련성 높은 답변을 제공하도록 유도할 수 있다. 특히, 광범위한 주제에 대한 정보를 찾거나 간결한 답변을 찾을 때 유용하다.\n",
        "\n",
        "- 지시사항: AI 언어모델이 특정 작업을 수행하거나 특정 방식으로 동작하도록 하려면 프롬프트에 명시적인 지침을 포함한다. 예를 들어, AI에게 장단점을 나열하거나, 두 항목을 비교하거나, 특정 관점을 고려하도록 요청한다.\n",
        "\n",
        "- **문법과 철자**: AI 언어모형이 수행 작업을 해석할 때 문법 정보를 사용하기 때문에 올바른 문법과 철자법을 맞게 작성하는 것은 중요하다.\n",
        "\n",
        "\n",
        "## `survey.db`\n",
        "\n",
        "::: {.panel-tabset}\n",
        "\n",
        "### 프롬프트\n",
        "\n",
        "``` bash\n",
        "You are an expert in SQL. The following table definitions have been provided to you. Please convert my query into an appropriate SQL statement.\n",
        "\n",
        "CREATE TABLE Person(\n",
        "        ident    text,\n",
        "        personal text,\n",
        "        family   text\n",
        ");\n",
        "CREATE TABLE Site(\n",
        "        name text,\n",
        "        lat  real,\n",
        "        long real\n",
        ");\n",
        "CREATE TABLE Visited(\n",
        "        ident integer,\n",
        "        site  text,\n",
        "        dated text\n",
        ");\n",
        "CREATE TABLE Survey(\n",
        "        taken   integer,\n",
        "        person  text,\n",
        "        quant   text,\n",
        "        reading real\n",
        ");\n",
        "\n",
        "Let's write an SQL query to display the names of scientists on the screen.\n",
        "```\n",
        "\n",
        "### 출력결과\n",
        "\n",
        "To write an SQL query that displays the names of scientists, we need to determine which table and column(s) contain the relevant information about the scientists. Based on the provided table definitions, it seems that the `Person` table holds the information about individuals, which likely includes scientists. The columns `personal` and `family` in the `Person` table seem to be the ones that store the names of individuals.\n",
        "\n",
        "The query would be straightforward, as it involves selecting data from a single table without any joins or complex conditions. Here is the SQL statement:\n",
        "\n",
        "```sql\n",
        "SELECT personal, family\n",
        "FROM Person;\n",
        "```\n",
        "\n",
        "This query will display the personal (given name) and family (surname) of each individual in the `Person` table. If you need just the full names combined or have other specific requirements, the query can be adjusted accordingly.\n",
        "\n",
        ":::\n",
        "\n",
        "::: {#fig-prompt layout-ncol=2}\n",
        "\n",
        "![챗GPT 자연어 질의](images/chatGPT_sql_prompt.jpg)\n",
        "\n",
        "![챗GPT SQL 쿼리 생성](images/chatGPT_sql_result.jpg)\n",
        "\n",
        "챗GPT 자연어 질의와 SQL 쿼리 생성\n",
        "\n",
        ":::\n",
        "\n",
        "\n",
        "## 프로그래밍\n",
        "\n",
        "GUI 챗팅 인터페이스 대신 프로그래밍을 통해 파악해보자. \n",
        "자연어로 SQL 프로그래밍 들어가기 전에 기본적인 설정을 다음과 같이 설정한다. 파이썬에서 OpenAI의 GPT 모델을 사용해 사용자 질문에 자동으로 답하는 스크립트를 작성한다.\n",
        "\n",
        "1. **모듈 가져오기**: \n",
        "   - `os`는 운영 체제와 상호작용하고, 환경 변수에 접근하는 데 사용된다.\n",
        "   - `openai`는 OpenAI의 Python 클라이언트 라이브러리로, GPT 모델을 사용하는 데 필요하다.\n",
        "   - `dotenv`는 `.env` 파일에서 환경 변수를 로드하는 데 사용된다.\n",
        "\n",
        "2. **환경 변수 로드**:\n",
        "   - `load_dotenv()`는 프로젝트 루트의 `.env` 파일로부터 환경 변수를 로드한다. `.env` 파일에 저장된 `OPENAI_API_KEY` 환경변수를 가져온다.\n",
        "\n",
        "3. **OpenAI 클라이언트 초기화**:\n",
        "   - `OpenAI`를 사용해 API 클라이언트를 생성한다.\n",
        "   - `api_key=os.getenv('OPENAI_API_KEY')`는 환경 변수에서 `OPENAI_API_KEY`를 가져와 클라이언트를 인증한다.\n",
        "\n",
        "4. **채팅 완성 생성**:\n",
        "   - `client.chat.completions.create`는 OpenAI의 채팅 완성 API를 사용해 채팅 대화를 생성한다.\n",
        "   - `messages`는 사용자의 입력 메시지를 담고 있다. 이 경우 \"SQL이 뭔지 간략하게 설명해줘\"라는 질문이 포함되어 있다.\n",
        "   - `model=\"gpt-3.5-turbo\"`는 사용할 GPT 모델을 지정한다.\n",
        "\n",
        "5. **결과 출력**:\n",
        "   - `print(chat_completion.choices[0].message.content)`는 생성된 채팅 대화에서 첫 번째 선택 항목의 메시지 내용을 출력한다. 이는 GPT 모델이 생성한 답변을 보여준다.\n"
      ],
      "id": "df595c61"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "#| eval: false\n",
        "import os\n",
        "from openai import OpenAI\n",
        "from dotenv import load_dotenv\n",
        "\n",
        "load_dotenv()\n",
        "\n",
        "client = OpenAI(\n",
        "    api_key=os.getenv('OPENAI_API_KEY'),\n",
        ")\n",
        "\n",
        "chat_completion = client.chat.completions.create(\n",
        "    messages=[\n",
        "        {\n",
        "            \"role\": \"user\",\n",
        "            \"content\": \"SQL이 뭔지 간략하게 설명해줘\",\n",
        "        }\n",
        "    ],\n",
        "    model=\"gpt-3.5-turbo\",\n",
        ")\n",
        "\n",
        "print(chat_completion.choices[0].message.content)"
      ],
      "id": "b8793a28",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "``` bash\n",
        "SQL(Structured Query Language)은 데이터베이스 관리 시스템(DBMS)에서 데이터를 관리하고 조작하는 데 사용하는 표준화된 프로그래밍 언어입니다. SQL은 사용자가 데이터베이스에 대한 쿼리를 작성하여 데이터를 검색, 삽입, 수정, 삭제하는 등 데이터베이스에 대한 다양한 작업을 수행할 수 있게 해줍니다. SQL은 간결한 구문과 명확한 명령어를 사용하며, 이를 통해 데이터베이스에 저장된 대량의 데이터를 효율적으로 조작할 수 있습니다. SQL은 관계형 데이터베이스 시스템(RDBMS)에서 주로 사용되며, 사용자의 요구에 따라 데이터를 가져오거나 수정하는 등 다양한 작업을 수행할 수 있으므로 데이터베이스 관리에 필수적인 언어로 사용됩니다.\n",
        "```\n"
      ],
      "id": "7e3ab2a9"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "#| eval: false\n",
        "\n",
        "sql_message = \"\"\"\n",
        "You are an expert in SQL. The following table definitions have been provided to you. Please convert my query into an appropriate SQL statement.\n",
        "\n",
        "CREATE TABLE Person(\n",
        "        ident    text,\n",
        "        personal text,\n",
        "        family   text\n",
        ");\n",
        "CREATE TABLE Site(\n",
        "        name text,\n",
        "        lat  real,\n",
        "        long real\n",
        ");\n",
        "CREATE TABLE Visited(\n",
        "        ident integer,\n",
        "        site  text,\n",
        "        dated text\n",
        ");\n",
        "CREATE TABLE Survey(\n",
        "        taken   integer,\n",
        "        person  text,\n",
        "        quant   text,\n",
        "        reading real\n",
        ");\n",
        "\n",
        "Let's write an SQL query to display the names of scientists on the screen.\n",
        "\"\"\"\n",
        "\n",
        "chat_completion = client.chat.completions.create(\n",
        "    messages=[\n",
        "        {\n",
        "            \"role\": \"user\",\n",
        "            \"content\": sql_message,\n",
        "        }\n",
        "    ],\n",
        "    model=\"gpt-3.5-turbo\",\n",
        ")\n",
        "\n",
        "print(chat_completion.choices[0].message.content)"
      ],
      "id": "b921512e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "``` bash\n",
        "To write an SQL query to display the names of scientists on the screen, we need to identify what table contains the names of scientists. Based on the given table definitions, it appears that the \"Person\" table contains the relevant information. Therefore, we can use the following SQL statement to achieve the desired result:\n",
        "\n",
        "SELECT personal, family\n",
        "FROM Person;\n",
        "```\n"
      ],
      "id": "18b0c411"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "#| eval: false\n",
        "\n",
        "def create_message(query):\n",
        "\n",
        "    class message:\n",
        "        def __init__(message, system, user):\n",
        "            message.system = system\n",
        "            message.user = user\n",
        "    \n",
        "    system_message = \"\"\"\n",
        "    You are an expert in SQL. The following table definitions have been provided to you. Please convert my query into an appropriate SQL statement. \\n\n",
        "    \n",
        "    CREATE TABLE Person(\n",
        "            ident    text,\n",
        "            personal text,\n",
        "            family   text\n",
        "    );\n",
        "    CREATE TABLE Site(\n",
        "            name text,\n",
        "            lat  real,\n",
        "            long real\n",
        "    );\n",
        "    CREATE TABLE Visited(\n",
        "            ident integer,\n",
        "            site  text,\n",
        "            dated text\n",
        "    );\n",
        "    CREATE TABLE Survey(\n",
        "            taken   integer,\n",
        "            person  text,\n",
        "            quant   text,\n",
        "            reading real\n",
        "    ); \\n\n",
        "    \"\"\"\n",
        "\n",
        "    user_message = \"No explanation. Write only SQL query that returns - {}\"\n",
        "\n",
        "    system = system_message\n",
        "    user = user_message.format(query)\n",
        "\n",
        "    m = message(system = system, user = user)\n",
        "    \n",
        "    return m\n",
        "\n",
        "\n",
        "query = \"display the names of scientists on the screen\"\n",
        "\n",
        "msg = create_message(query = query)\n",
        "\n",
        "# print(msg.system)\n",
        "print(msg.user)"
      ],
      "id": "541c0eae",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "``` bash\n",
        "Write an SQL query that returns - display the names of scientists on the screen\n",
        "```\n"
      ],
      "id": "ba004809"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "#| eval: false\n",
        "\n",
        "query = \"display the names of scientists on the screen\"\n",
        "prompt = create_message(query = query)\n",
        "\n",
        "response = client.chat.completions.create(\n",
        "    messages = [\n",
        "      {\n",
        "        \"role\": \"system\",\n",
        "        \"content\": prompt.system\n",
        "      },\n",
        "      {\n",
        "        \"role\": \"user\",\n",
        "        \"content\": prompt.user\n",
        "      }\n",
        "    ],\n",
        "    model=\"gpt-3.5-turbo\",\n",
        ")\n",
        "\n",
        "sql = response.choices[0].message.content\n",
        "\n",
        "print(sql)"
      ],
      "id": "f13a9a2c",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "``` bash\n",
        "'SELECT personal FROM Person;'\n",
        "```\n"
      ],
      "id": "bb495830"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "#| eval: false\n",
        "\n",
        "def translate_sql(query):\n",
        "  \n",
        "  prompt = create_message(query = query)\n",
        "\n",
        "  response = client.chat.completions.create(\n",
        "      messages = [\n",
        "        {\n",
        "          \"role\": \"system\",\n",
        "          \"content\": prompt.system\n",
        "        },\n",
        "        {\n",
        "          \"role\": \"user\",\n",
        "          \"content\": prompt.user\n",
        "        }\n",
        "      ],\n",
        "      model=\"gpt-3.5-turbo\",\n",
        "  )\n",
        "\n",
        "  sql = response.choices[0].message.content\n",
        "  \n",
        "  return sql\n",
        "\n",
        "translate_sql(query = 'aggregate the hours for each scientist separately')"
      ],
      "id": "242c6822",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "``` bash\n",
        "'SELECT person, SUM(reading) AS total_hours\\nFROM Survey\\nGROUP BY person;'\n",
        "```\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "## SQL 데이터셋\n",
        "\n",
        "[@dataherald2023texttosql]\n",
        "\n",
        "GPT-4와 Bard 같은 AI 모델의 자연어 처리 능력이 급격히 향상되면서,\n",
        "자연어로 질문된 내용에 대한 SQL 쿼리를 생성하는 '텍스트-투-SQL'과 같은\n",
        "다양한 NLP 사용 사례에 대한 발전이 가속화되고 있다. 여러 접근 방식과\n",
        "솔루션이 시장에 나오면서 어느 것이 가장 효율적인지, 어느 것이 정확한\n",
        "답을 더 신뢰성 있게 생성하는지, 어느 것이 다양한 데이터셋에 가장 잘\n",
        "적응하는지 평가하는 문제가 생겼다. 이 질문들에 답하기 위해 오픈소스\n",
        "산업과 학계는 여러 벤치마크를 제시했지만, 오늘날 가장 많이 사용되는 세\n",
        "가지는 WikiSQL, Spider, BIRD(BIg Bench for LaRge-scale Database Grounded\n",
        "Text-to-SQL Evaluation)이다. WikiSQL은 Salesforce에 의해 2017년 말에\n",
        "소개된 최초의 대규모 텍스트-투-SQL 데이터 집합이지만, 단순함이라는 큰\n",
        "단점이 있다. 제공되는 모든 SQL 쿼리는 SELECT, FROM, WHERE 절만을\n",
        "포함하는 매우 간단하며, 데이터셋 내의 테이블들은 다른 테이블과의 연결이\n",
        "없다. WikiSQL로 훈련된 모델은 새로운 데이터베이스에서도 작동할 수\n",
        "있지만, 간단한 자연어 질문에 대해서만 답할 수 있다. 이러한 이유로 최근\n",
        "텍스트-투-SQL 분야의 연구는 더 복잡한 벤치마크에 초점을 맞추고 있다.\n",
        "실제로 WikiSQL 리더보드에는 2021년 이전의 제출물만 있으며, 테스트 정확도\n",
        "90% 이상을 달성한 여러 제출물들이 있지만(가장 성능이 좋은 제출물은 93%에\n",
        "도달함), 이제 실무자들은 WikiSQL로는 턱없이 부족한 훨씬 더 복잡한 쿼리\n",
        "생성에 초점을 맞추고 있다.\n",
        "\n",
        "Spider 데이터셋은 WikiSQL 데이터셋의 단점 중 일부를 보완하려고 한다.\n",
        "예일 대학의 11명 학생들이 1,000시간 이상의 노력을 통해 개발된 Spider\n",
        "데이터셋은 복잡성과 교차 도메인성이라는 두 가지 중요한 요소를 도입한다.\n",
        "복잡성 측면에서 SQL 쿼리는 WikiSQL이 한정된 간단한 SELECT와 WHERE 절을\n",
        "넘어서, 더 복잡한 GROUP BY, ORDER BY, HAVING 절과 중첩된 쿼리를\n",
        "포함한다. 또한, 모든 데이터베이스는 외래 키를 통해 여러 테이블이\n",
        "연결되어 있어 테이블 간에 조인하는 복잡한 쿼리를 가능하게 한다. 교차\n",
        "도메인성 측면에서 Spider는 200개의 복잡한 데이터베이스를 많은 도메인에\n",
        "걸쳐 포함하여, 테스트 세트에서 본 적 없는 데이터베이스를 포함시켜 모델의\n",
        "일반화 가능성을 테스트할 수 있게 한다. "
      ],
      "id": "34a8183c"
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "language": "python",
      "display_name": "Python 3 (ipykernel)"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}