Model_evaluation_Spider.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4784567e",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2023-07-24 09:59:16,286] [INFO] [real_accelerator.py:110:get_accelerator] Setting ds_accelerator to cuda (auto detect)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-24 09:59:22.414409: I tensorflow/core/platform/cpu_feature_guard.cc:193] This TensorFlow binary is optimized with oneAPI Deep Neural Network Library (oneDNN) to use the following CPU instructions in performance-critical operations:  AVX2 AVX512F AVX512_VNNI FMA\n",
      "To enable them in other operations, rebuild TensorFlow with the appropriate compiler flags.\n",
      "2023-07-24 09:59:22.864785: I tensorflow/core/util/util.cc:169] oneDNN custom operations are on. You may see slightly different numerical results due to floating-point round-off errors from different computation orders. To turn them off, set the environment variable `TF_ENABLE_ONEDNN_OPTS=0`.\n",
      "2023-07-24 09:59:22.927217: E tensorflow/stream_executor/cuda/cuda_blas.cc:2981] Unable to register cuBLAS factory: Attempting to register factory for plugin cuBLAS when one has already been registered\n",
      "2023-07-24 09:59:25.423355: W tensorflow/stream_executor/platform/default/dso_loader.cc:64] Could not load dynamic library 'libnvinfer.so.7'; dlerror: libnvinfer.so.7: cannot open shared object file: No such file or directory\n",
      "2023-07-24 09:59:25.424526: W tensorflow/stream_executor/platform/default/dso_loader.cc:64] Could not load dynamic library 'libnvinfer_plugin.so.7'; dlerror: libnvinfer_plugin.so.7: cannot open shared object file: No such file or directory\n",
      "2023-07-24 09:59:25.424556: W tensorflow/compiler/tf2tensorrt/utils/py_utils.cc:38] TF-TRT Warning: Cannot dlopen some TensorRT libraries. If you would like to use Nvidia GPU with TensorRT, please make sure the missing libraries mentioned above are installed properly.\n"
     ]
    }
   ],
   "source": [
    "from transformers import AutoTokenizer, T5ForConditionalGeneration\n",
    "import torch\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f5878dda",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'cuda'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_name = \"T5-Finetuned-with-IMDB-Spider\"\n",
    "\n",
    "if torch.cuda.is_available():\n",
    "    device = \"cuda\"\n",
    "else:\n",
    "    device = \"cpu\"\n",
    "device"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "013e25cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "T5ForConditionalGeneration(\n",
       "  (shared): Embedding(32128, 512)\n",
       "  (encoder): T5Stack(\n",
       "    (embed_tokens): Embedding(32128, 512)\n",
       "    (block): ModuleList(\n",
       "      (0): T5Block(\n",
       "        (layer): ModuleList(\n",
       "          (0): T5LayerSelfAttention(\n",
       "            (SelfAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (relative_attention_bias): Embedding(32, 8)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (1): T5LayerFF(\n",
       "            (DenseReluDense): T5DenseActDense(\n",
       "              (wi): Linear(in_features=512, out_features=2048, bias=False)\n",
       "              (wo): Linear(in_features=2048, out_features=512, bias=False)\n",
       "              (dropout): Dropout(p=0.1, inplace=False)\n",
       "              (act): ReLU()\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "        )\n",
       "      )\n",
       "      (1-5): 5 x T5Block(\n",
       "        (layer): ModuleList(\n",
       "          (0): T5LayerSelfAttention(\n",
       "            (SelfAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (1): T5LayerFF(\n",
       "            (DenseReluDense): T5DenseActDense(\n",
       "              (wi): Linear(in_features=512, out_features=2048, bias=False)\n",
       "              (wo): Linear(in_features=2048, out_features=512, bias=False)\n",
       "              (dropout): Dropout(p=0.1, inplace=False)\n",
       "              (act): ReLU()\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "        )\n",
       "      )\n",
       "    )\n",
       "    (final_layer_norm): T5LayerNorm()\n",
       "    (dropout): Dropout(p=0.1, inplace=False)\n",
       "  )\n",
       "  (decoder): T5Stack(\n",
       "    (embed_tokens): Embedding(32128, 512)\n",
       "    (block): ModuleList(\n",
       "      (0): T5Block(\n",
       "        (layer): ModuleList(\n",
       "          (0): T5LayerSelfAttention(\n",
       "            (SelfAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (relative_attention_bias): Embedding(32, 8)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (1): T5LayerCrossAttention(\n",
       "            (EncDecAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (2): T5LayerFF(\n",
       "            (DenseReluDense): T5DenseActDense(\n",
       "              (wi): Linear(in_features=512, out_features=2048, bias=False)\n",
       "              (wo): Linear(in_features=2048, out_features=512, bias=False)\n",
       "              (dropout): Dropout(p=0.1, inplace=False)\n",
       "              (act): ReLU()\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "        )\n",
       "      )\n",
       "      (1-5): 5 x T5Block(\n",
       "        (layer): ModuleList(\n",
       "          (0): T5LayerSelfAttention(\n",
       "            (SelfAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (1): T5LayerCrossAttention(\n",
       "            (EncDecAttention): T5Attention(\n",
       "              (q): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (k): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (v): Linear(in_features=512, out_features=512, bias=False)\n",
       "              (o): Linear(in_features=512, out_features=512, bias=False)\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "          (2): T5LayerFF(\n",
       "            (DenseReluDense): T5DenseActDense(\n",
       "              (wi): Linear(in_features=512, out_features=2048, bias=False)\n",
       "              (wo): Linear(in_features=2048, out_features=512, bias=False)\n",
       "              (dropout): Dropout(p=0.1, inplace=False)\n",
       "              (act): ReLU()\n",
       "            )\n",
       "            (layer_norm): T5LayerNorm()\n",
       "            (dropout): Dropout(p=0.1, inplace=False)\n",
       "          )\n",
       "        )\n",
       "      )\n",
       "    )\n",
       "    (final_layer_norm): T5LayerNorm()\n",
       "    (dropout): Dropout(p=0.1, inplace=False)\n",
       "  )\n",
       "  (lm_head): Linear(in_features=512, out_features=32128, bias=False)\n",
       ")"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tokenizer = AutoTokenizer.from_pretrained(model_name)\n",
    "model = T5ForConditionalGeneration.from_pretrained(model_name).to(device)\n",
    "model.eval()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "95f8e0ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datasets import Dataset\n",
    "\n",
    "df = pd.read_csv('Training_set_IMDB/testing_set_no_target.csv')\n",
    "df = df.sample(frac =1).reset_index(drop=True)\n",
    "for index,row in df.iterrows():\n",
    "    df.loc[index, 'Text'] = \"Translate to SQL: \" + row['Text']\n",
    "\n",
    "\n",
    "test_set_seen = Dataset.from_pandas(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c05c9b67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select rank from movies t1 where t1.name = 'Misleading Lady, The'\n",
      "Translate to SQL: what is the movie 'Misleading Lady, The' rank\n"
     ]
    }
   ],
   "source": [
    "test_set_seen.set_format(type = \"torch\")\n",
    "\n",
    "print(test_set_seen[\"SQL\"][1])\n",
    "print(test_set_seen[\"Text\"][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cbfd20e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_features(example_batch, padding = \"max_length\",input_max = 256, output_max = 256):\n",
    "    inputs = tokenizer.batch_encode_plus(example_batch[\"Text\"], max_length=input_max, is_split_into_words = False, padding='max_length', truncation=True, return_tensors = \"pt\")\n",
    "    \n",
    "    targets = tokenizer.batch_encode_plus(example_batch[\"SQL\"], max_length=output_max, padding = \"max_length\",truncation = True)\n",
    "    if padding == \"max_length\":\n",
    "        targets[\"inputs_ids\"] = [\n",
    "            [(l if l != tokenizer.pad_token_id else -100) for l in target] for target in targets[\"input_ids\"]\n",
    "        ]\n",
    "    \n",
    "    inputs[\"labels\"] = targets['input_ids']\n",
    "    return inputs\n",
    "\n",
    "def evaluate_peft_model(sample):\n",
    "    outputs = model.generate(input_ids=sample[\"input_ids\"].unsqueeze(0).cuda(), max_length = 200, top_p=0.9)\n",
    "    prediction = tokenizer.decode(outputs[0].detach().cpu().numpy(), skip_special_tokens=True)\n",
    "    label = np.where(sample['labels'] != -100, sample['labels'], tokenizer.pad_token_id)\n",
    "    label = tokenizer.decode(label, skip_special_tokens=True)\n",
    "    _ = execution_accuracy(prediction, label)\n",
    "    return prediction, label\n",
    "\n",
    "def execution_accuracy(prediction, label):\n",
    "    try:\n",
    "        \n",
    "        cursor.execute(label)\n",
    "        result_label = cursor.fetchall()\n",
    "        all_executions_overall.append(1)\n",
    "        try:\n",
    "            cursor.execute(prediction)\n",
    "            result_pred = cursor.fetchall()\n",
    "            all_executions_accuracy.append(1)\n",
    "            if len(result_label)>10:\n",
    "                if len(result_label) == len(result_pred):\n",
    "                    accurate_executions.append(1) \n",
    "            elif result_label == result_pred:\n",
    "                accurate_executions.append(1)\n",
    "            else:\n",
    "                for_checking_label.append(label)\n",
    "                for_checking_prediction.append(prediction)\n",
    "                \n",
    "        except:\n",
    "            failed_executions.append(1)\n",
    "            failed_predicted_SQL.append(prediction)\n",
    "                \n",
    "    except:\n",
    "        failed_original_SQL.append(label)\n",
    "    return None\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5669ed13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mapping both datasets\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Map (num_proc=4):   0%|          | 0/2315 [00:00<?, ? examples/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mapped both dataset\n",
      "Document we have: tokenized_dataset for seen data\n",
      "\n",
      "\n",
      " Running executions for seen dataset\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 2315/2315 [40:33<00:00,  1.05s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All SQL runs:  2254\n",
      "Model SQLs that failed:  162\n",
      "Execution rate: 92.81277728482698%\n",
      "Execution rate: 92.81277728482698%\n",
      "Execution accuracy: 94.16826003824092%\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "import evaluate\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import mysql.connector\n",
    "\n",
    "\n",
    "\n",
    "connection = mysql.connector.connect(\n",
    "    host=\"relational.fit.cvut.cz\",\n",
    "    user=\"guest\",\n",
    "    password=\"relational\",\n",
    "    database=\"imdb_ijs\"\n",
    ")\n",
    "cursor = connection.cursor()\n",
    "\n",
    "\n",
    "\n",
    "print(\"mapping both datasets\")\n",
    "tokenized_dataset = test_set_seen.map(convert_to_features, batched=True, num_proc=4)\n",
    "\n",
    "print(\"mapped both dataset\")\n",
    "print(\"Document we have: tokenized_dataset for seen data\")\n",
    "\n",
    "\n",
    "print(\"\\n\\n Running executions for seen dataset\")\n",
    "all_executions_overall = []\n",
    "failed_executions = []\n",
    "all_executions_accuracy = []\n",
    "accurate_executions = []\n",
    "for_checking_label = []\n",
    "for_checking_prediction = []\n",
    "failed_original_SQL = []\n",
    "failed_predicted_SQL = []\n",
    "\n",
    "\n",
    "\n",
    "for sample in tqdm(tokenized_dataset):\n",
    "    p,l = evaluate_peft_model(sample)\n",
    "\n",
    "\n",
    "\n",
    "print(\"All SQL runs: \", len(all_executions_overall))\n",
    "print(\"Model SQLs that failed: \", len(failed_executions))\n",
    "print(f\"Execution rate: {len(all_executions_accuracy)/len(all_executions_overall)*100}%\")\n",
    "print(f\"Execution rate: {100 - len(failed_executions)/len(all_executions_overall)*100}%\")\n",
    "print(f\"Execution accuracy: {len(accurate_executions)/len(all_executions_accuracy)*100}%\")\n",
    "\n",
    "# failed_original_sql_df = pd.DataFrame(failed_original_SQL)\n",
    "# failed_predicted_sql_df = pd.DataFrame(failed_predicted_SQL)\n",
    "# not_equals = pd.DataFrame({\n",
    "#     'Label':for_checking_label,\n",
    "#     'Prediction': for_checking_prediction\n",
    "# })\n",
    "\n",
    "#not_equals.to_csv(\"/home/toibazd/Data/Text2SQL/Training_set_IMDB/Not_equals_Spider.csv\", index = False)\n",
    "#failed_original_sql_df.to_csv(\"/home/toibazd/Data/Text2SQL/Training_set_IMDB/Failed_originals_Spider.csv\", index = False)\n",
    "#failed_predicted_sql_df.to_csv(\"/home/toibazd/Data/Text2SQL/Training_set_IMDB/Failed_predicted_Spider.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce0e615e",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}