wikisql/Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initialise arguments etc. Should output the folder containing the pretrained model, may be necessary to change data_path to an absolute path."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'../data_model/wikisql/'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import opts\n",
    "import argparse\n",
    "import os\n",
    "data_path = '../data_model/wikisql/'\n",
    "model_path = os.path.join(data_path,'pretrain.pt')\n",
    "parser = argparse.ArgumentParser(description='Exploration.ipynb')\n",
    "opts.translate_opts(parser)\n",
    "opt = parser.parse_args(\n",
    "    [\"-split\",\"pred\",\"-output\",\"pred.txt\",\"-data_path\",data_path,\"-model_path\",model_path])\n",
    "\n",
    "opt.anno = os.path.join(\n",
    "    opt.data_path, 'annotated_ent/{}.jsonl'.format(opt.split))\n",
    "opt.source_file = os.path.join(\n",
    "    opt.data_path, 'data/{}.jsonl'.format(opt.split))\n",
    "opt.db_file = os.path.join(opt.data_path, 'data/{}.db'.format(opt.split))\n",
    "opt.pre_word_vecs = os.path.join(opt.data_path, 'embedding')\n",
    "opt.data_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'../data_model/wikisql/pretrain.pt'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import torch\n",
    "import table\n",
    "import glob\n",
    "import json\n",
    "torch.cuda.set_device(opt.gpu)\n",
    "dummy_parser = argparse.ArgumentParser(description='train.py')\n",
    "opts.model_opts(dummy_parser)\n",
    "opts.train_opts(dummy_parser)\n",
    "dummy_opt = dummy_parser.parse_known_args([])[0]\n",
    "fn_model = glob.glob(opt.model_path)[0]\n",
    "opt.model=fn_model\n",
    "fn_model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the pretrained model - may take time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "checkpoint = torch.load(opt.model,\n",
    "                                map_location=lambda storage, loc: storage)\n",
    "fields = table.IO.TableDataset.load_fields(checkpoint['vocab'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Start the translation model running. If the embedding folder is not set correctly this may attempt to download the GloVe vectors directly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading model\n"
     ]
    }
   ],
   "source": [
    "translator = table.Translator(opt, dummy_opt.__dict__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initialise spaCy,  requires the model installed as described in https://spacy.io/models/en#en_core_web_lg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import spacy\n",
    "import codecs\n",
    "import json\n",
    "from spacy.tokens import Doc\n",
    "nlp = spacy.load('en_core_web_lg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up a new table. This example is from the training data, but can be modified. The rows are not used for the query, but at least one should exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables={}\n",
    "table_instance={\"header\": \n",
    "       [\"Customer Id\", \"Name\", \"Address\", \"Priority\", \"Value\", \"Age\", \"Gender\"],\n",
    "       \"page_title\": \"Customer applications\", \n",
    "       \"types\": [\"text\", \"text\", \"text\", \"text\", \"real\", \"real\", \"text\"], \n",
    "       \"id\": \"jca\",\n",
    "       \"section_title\": \"Customer Details\", \n",
    "       \"caption\": \"Customer Details\",\n",
    "       \"rows\": [[\"1231234A\", \"UA\", \"2013-05-20\", \"2014-07-20\", 180, 42, \"Male\"]], \n",
    "       \"name\": \"table_jca\"\n",
    "      }\n",
    "tables[table_instance['id']]=table_instance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up a question. The SQL field describes the expected query when training or testing. In this notebook it is not used, but it should still make sense for the table (e.g. conds should not specify a column number which is not in the table)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "question = {\"phase\": 1, \n",
    "            \"table_id\": \"jca\", \n",
    "            \"question\": \"How many customers have low age\", \n",
    "            \"sql\": {\"sel\": 1, \"conds\": [[2, 0, \"Williams\"], [5, 0, \"2\"]], \"agg\": 3}\n",
    "#            \"sql\": {\"sel\": 1, \"conds\": [], \"agg\": 1}\n",
    "           }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This connects to a Stanford CoreNLP server to annotate the question. The server should be running \n",
    "on http://localhost:9000/ by default, this can be modified in annotate.py when instantiating CoreNLPClient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from annotate import annotate_example\n",
    "annotated_question = annotate_example(question, table_instance)\n",
    "#annotated_question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add POS annotations to question. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "w_list = annotated_question['question']['gloss']\n",
    "ws_list = [it.isspace() for it in annotated_question['question']['after']]\n",
    "doc = Doc(nlp.vocab, words=w_list, spaces=ws_list)\n",
    "for name, proc in nlp.pipeline:\n",
    "    doc = proc(doc)\n",
    "annotated_question['question']['ent'] = [tk.tag_ for tk in doc]\n",
    "assert(len(annotated_question['question']['ent']) == len(annotated_question['question']['words']))\n",
    "#annotated_question['question']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'after': [' ', ' ', ' ', ' ', ' ', ''],\n",
       " 'ent': ['WRB', 'JJ', 'NNS', 'VBP', 'JJ', 'NN'],\n",
       " 'gloss': ['How', 'many', 'customers', 'have', 'low', 'age'],\n",
       " 'words': ['how', 'many', 'customers', 'have', 'low', 'age']}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotated_question['question']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actually generate the SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SELECT MIN col5 FROM table"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "js_list = [annotated_question]\n",
    "data = table.IO.TableDataset(js_list, translator.fields, None, False)\n",
    "test_data = table.IO.OrderedIterator(dataset=data,device=opt.gpu, batch_size=opt.batch_size, train=False, sort=True, sort_within_batch=False)\n",
    "batch=next(iter(test_data))\n",
    "result_list=translator.translate(batch)\n",
    "pred = result_list[0]\n",
    "pred.predict(annotated_question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<table.ParseResult.ParseResult at 0x7f29ccfa89b0>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[3, 0, 'low priority']]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_list[0].recover_cond_to_gloss(annotated_question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3, 0, (3, 4))]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_list[0].cond"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(['What', 'customers', 'have', 'low', 'priority'], [' ', ' ', ' ', ' ', ''])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col, op, span = (result_list[0].cond)[0]\n",
    "annotated_question['question']['gloss'],annotated_question['question']['after']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0, 0, [(3, 0, (3, 4))])"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr = result_list[0]\n",
    "pr.idx, pr.agg, pr.sel, pr.cond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Investigating incorrect select coulumn\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}