test-NL-SQL-alpaca.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99b1880d-fe78-437e-a88d-f813f7528c02",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install git+https://github.com/huggingface/transformers.git\n",
    "!pip install deepspeed==0.6.5\n",
    "!pip install accelerate\n",
    "!pip install sentencepiece\n",
    "!pip install langchain\n",
    "!pip install torch\n",
    "!pip install bitsandbytes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d14a16a0-b7df-40a7-9f68-3004d9caf69b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "# import deepspeed\n",
    "import torch\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd164ac4-50b4-4c86-a59c-9f56dff7bc12",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip uninstall torch -y\n",
    "!pip install torch==1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ffd58897-519d-42c4-8640-4d36791775cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "4\n",
      "NVIDIA GeForce GTX 1080 Ti\n"
     ]
    }
   ],
   "source": [
    "print(torch.cuda.is_available())\n",
    "print(torch.cuda.device_count())\n",
    "print(torch.cuda.get_device_name(0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbe5694a-74b8-49cf-997f-5be1cc3e5d17",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "81144e5f-db0f-4078-aa3f-94d2fe4e241e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.0.1+cu117\n"
     ]
    }
   ],
   "source": [
    "import torch\n",
    "print(torch.__version__)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89e5f76d-c912-4c95-b819-896ddcee8ef2",
   "metadata": {},
   "source": [
    "transformer --> deepseed\n",
    "accelerate --> deepseed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b714c8b5-9f6e-4bdb-9a35-223ea080646e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from transformers import LlamaTokenizer, LlamaForCausalLM, pipeline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "be45cd79-9cc1-449e-8373-300e80ba37b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.llms import HuggingFacePipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0caf9340-8a64-4515-90df-646ff3dab381",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "47a2689d-e378-493a-ad83-7dc71f0308d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.30.0\n"
     ]
    }
   ],
   "source": [
    "import transformers\n",
    "print(transformers.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f19781d-b5f6-4af4-b237-ae8ff4df9659",
   "metadata": {},
   "source": [
    "[deeepspeed need torch 1, not install]\n",
    "\n",
    "accelerate and others need torch 2\n",
    "transformers==4.30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bc86fa6c-5cb0-4e9f-b589-c7f6ebc6cb65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "48540460daa44785bde7f0725364334c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Loading checkpoint shards:   0%|          | 0/3 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "base_model = LlamaForCausalLM.from_pretrained(\n",
    "    \"chavinlo/alpaca-native\",\n",
    "    load_in_8bit=True,\n",
    "    device_map='auto',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f9193cd7-e378-4ab8-9a70-19a6db0b0907",
   "metadata": {},
   "outputs": [],
   "source": [
    "import scipy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7d8afe5-a8d8-403e-a7c3-be692982687d",
   "metadata": {},
   "source": [
    "66% =>\n",
    "/dev/sdb2       439G  282G  135G  68% /\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dac4af10-1ce7-4e22-a7f5-f758713e8feb",
   "metadata": {},
   "outputs": [],
   "source": [
    "tokenizer = LlamaTokenizer.from_pretrained(\"chavinlo/alpaca-native\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6cf56d0d-b49a-402f-a920-759ad717f942",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import PromptTemplate, LLMChain\n",
    "template = \"\"\"\n",
    "Write a SQL Query given the table name {Table} and columns as a list {Columns} for the given question : \n",
    "{question}.\n",
    "\"\"\"\n",
    "\n",
    "prompt = PromptTemplate(template=template, input_variables=[\"Table\",\"question\",\"Columns\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7e8fe435-8ddb-4a0d-a2af-2e80ed30e278",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import PromptTemplate, LLMChain\n",
    "template = \"\"\"\n",
    "Write a SQL Query given the table names {Tables} and their respective columns as lists {Columns} for the given question: \n",
    "{question}.\n",
    "\"\"\"\n",
    "\n",
    "prompt = PromptTemplate(template=template, input_variables=[\"Tables\", \"question\", \"Columns\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5b748cc9-7cf9-4690-9183-d2fcf9400372",
   "metadata": {},
   "outputs": [],
   "source": [
    "pipe = pipeline(\n",
    "    \"text-generation\",\n",
    "    model=base_model,\n",
    "    tokenizer=tokenizer,\n",
    "    max_length=500,\n",
    "    temperature=0.3,\n",
    "    top_p=0.95,\n",
    "    repetition_penalty=1.2\n",
    ")\n",
    "\n",
    "local_llm = HuggingFacePipeline(pipeline=pipe)\n",
    "llm_chain = LLMChain(prompt=prompt, llm=local_llm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1a613ae8-0e61-40a5-80c1-2af393b6b05d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_llm_response(tble,question,cols):\n",
    "    llm_chain = LLMChain(prompt=prompt, \n",
    "                         llm=local_llm\n",
    "                         )\n",
    "    response= llm_chain.run({\"Tables\" : tble,\"question\" :question, \"Columns\" : cols})\n",
    "    print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f01f5a75-86b7-42e0-8325-c4c58013fbc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_llm_response(tble,question,cols):\n",
    "    llm_chain = LLMChain(prompt=prompt, \n",
    "                         llm=local_llm\n",
    "                         )\n",
    "    response= llm_chain.run({\"Table\" : tble,\"question\" :question, \"Columns\" : cols})\n",
    "    print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "994c356f-a7b6-4a58-bb33-e572e632a4bd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tble = \"employee\"\n",
    "cols = [\"id\",\"name\",\"date_of_birth\",\"band\",\"manager_id\"]\n",
    "question = \"Query the count of employees in band L6 with 239045 as the manager ID\"\n",
    "get_llm_response(tble,question,cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "85100d0d-f207-4450-9731-0ea82c45d48d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/pc-leadtek/joseph/chatsql/myenv/lib/python3.8/site-packages/transformers/generation/utils.py:1259: UserWarning: You have modified the pretrained model configuration to control generation. This is a deprecated strategy to control generation and will be removed soon, in a future version. Please use a generation configuration file (see https://huggingface.co/docs/transformers/main_classes/text_generation)\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Answer: SELECT username FROM users INNER JOIN orders ON user_id = order_id;\n"
     ]
    }
   ],
   "source": [
    "Tables = [\"users\", \"orders\"]\n",
    "question = \"Find all users who have made an order\"\n",
    "Columns = [[\"user_id\", \"username\"], [\"order_id\", \"user_id\", \"product_name\"]]\n",
    "get_llm_response(Tables,question,Columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc9af130-f49c-47a8-b6b2-777e875a7bf3",
   "metadata": {},
   "source": [
    "SELECT users.username \r\n",
    "FROM users \r\n",
    "JOIN orders ON users.user_id = orders.user_id;\r\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "abeb3bd8-f4ae-4b58-9fdf-ebfe087e8d47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT u.username FROM users u INNER JOIN orders o ON u.order_id = o.user_id WHERE o.order_number = 12345;\n"
     ]
    }
   ],
   "source": [
    "question = \"請查出訂單編號 12345 的使用者資料\"\n",
    "\n",
    "get_llm_response(Tables,question,Columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "64749492-b012-4519-9b2b-5b963395e25f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Answer: SELECT COUNT(*) FROM employee WHERE band='L6' AND manager_id=239045;\n"
     ]
    }
   ],
   "source": [
    "get_llm_response(tble,question,cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f78da073-6cb0-4a81-bda2-50c6507377af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT manager_id = 23904 AND count(employee) AS total_in_L6 FROM employee WHERE band = \"L6\";\n"
     ]
    }
   ],
   "source": [
    "question = \"請查出管理員ID是23904 並在 band L6 的員工數量\"\n",
    "get_llm_response(tble,question,cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12592450-b4b1-4cbc-8eb8-64da571e6ee6",
   "metadata": {},
   "outputs": [],
   "source": [
    "question = \"what is the result of 2+100*300?\"\n",
    "get_llm_response(tble,question,cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45c6154a-eb1d-44e8-ba73-09d26f2d86e7",
   "metadata": {},
   "outputs": [],
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}