notebook/FAQIR/02.Ingesting_Data_to_Elasticsearch.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: elasticsearch in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (7.9.1)\n",
      "Requirement already satisfied: certifi in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch) (2020.12.5)\n",
      "Requirement already satisfied: urllib3>=1.21.1 in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch) (1.25.10)\n"
     ]
    }
   ],
   "source": [
    "!pip3 install elasticsearch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: elasticsearch-dsl in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (7.3.0)\n",
      "Requirement already satisfied: elasticsearch<8.0.0,>=7.0.0 in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch-dsl) (7.9.1)\n",
      "Requirement already satisfied: six in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch-dsl) (1.15.0)\n",
      "Requirement already satisfied: python-dateutil in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch-dsl) (2.8.1)\n",
      "Requirement already satisfied: urllib3>=1.21.1 in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch<8.0.0,>=7.0.0->elasticsearch-dsl) (1.25.10)\n",
      "Requirement already satisfied: certifi in /Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages (from elasticsearch<8.0.0,>=7.0.0->elasticsearch-dsl) (2020.12.5)\n"
     ]
    }
   ],
   "source": [
    "!pip3 install elasticsearch-dsl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.insert(0, '../../../BERT-FAQ/')\n",
    "\n",
    "# import required libraries\n",
    "from elasticsearch_dsl import Index, Document, Integer, Text, analyzer, Keyword, Double\n",
    "from elasticsearch_dsl.connections import connections\n",
    "from elasticsearch import Elasticsearch, helpers\n",
    "\n",
    "from evaluation import get_relevance_label_df\n",
    "from shared.utils import load_from_json\n",
    "from shared.utils import dump_to_json\n",
    "from shared.utils import make_dirs\n",
    "from indexer import ingest_data\n",
    "from indexer import QA\n",
    "import logging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get list of query answer pairs\n",
    "\n",
    "query_answer_pairs_filepath = '../../../BERT-FAQ/data/FAQIR/query_answer_pairs.json'\n",
    "relevance_label_df = get_relevance_label_df(query_answer_pairs_filepath)\n",
    "faq_qa_pair_df = relevance_label_df[relevance_label_df['query_type'] == 'faq']\n",
    "faq_qa_pairs = faq_qa_pair_df.T.to_dict().values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>answer</th>\n",
       "      <th>id</th>\n",
       "      <th>label</th>\n",
       "      <th>query_type</th>\n",
       "      <th>question</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I have had good luck with commercial products ...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to remove tree sap from car? Hi,I have som...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goo Gone is a fantastic product (available at ...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How do I get a sticker off of a car window  (p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1. Inspect the black rubber cover that fits in...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to get rid of garbage disposal odor?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>To properly maintain your lawnmover, you'll ne...</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to maintain a lawn mower?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Click on the link and follow the steps.</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to fix vertical blinds? I can no longer re...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16319</th>\n",
       "      <td>Get a bucket, fold some cotton towels into it,...</td>\n",
       "      <td>16320</td>\n",
       "      <td>1</td>\n",
       "      <td>user_query</td>\n",
       "      <td>Cigarette smoke odor is so annoying. Some frie...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16320</th>\n",
       "      <td>Get a bucket, fold some cotton towels into it,...</td>\n",
       "      <td>16321</td>\n",
       "      <td>1</td>\n",
       "      <td>user_query</td>\n",
       "      <td>I love smoking a pipe but the smell is quite d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16321</th>\n",
       "      <td>Get a bucket, fold some cotton towels into it,...</td>\n",
       "      <td>16322</td>\n",
       "      <td>1</td>\n",
       "      <td>user_query</td>\n",
       "      <td>What are the best tips for getting rid of the ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16322</th>\n",
       "      <td>Get a bucket, fold some cotton towels into it,...</td>\n",
       "      <td>16323</td>\n",
       "      <td>1</td>\n",
       "      <td>user_query</td>\n",
       "      <td>The weed smell is really hard to get rid of. D...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16323</th>\n",
       "      <td>Get a bucket, fold some cotton towels into it,...</td>\n",
       "      <td>16324</td>\n",
       "      <td>1</td>\n",
       "      <td>user_query</td>\n",
       "      <td>My roommate started smoking marijuana every th...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16324 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  answer     id  label  \\\n",
       "0      I have had good luck with commercial products ...      1      1   \n",
       "1      Goo Gone is a fantastic product (available at ...      2      1   \n",
       "2      1. Inspect the black rubber cover that fits in...      3      1   \n",
       "3      To properly maintain your lawnmover, you'll ne...      4      1   \n",
       "4                Click on the link and follow the steps.      5      1   \n",
       "...                                                  ...    ...    ...   \n",
       "16319  Get a bucket, fold some cotton towels into it,...  16320      1   \n",
       "16320  Get a bucket, fold some cotton towels into it,...  16321      1   \n",
       "16321  Get a bucket, fold some cotton towels into it,...  16322      1   \n",
       "16322  Get a bucket, fold some cotton towels into it,...  16323      1   \n",
       "16323  Get a bucket, fold some cotton towels into it,...  16324      1   \n",
       "\n",
       "       query_type                                           question  \n",
       "0             faq  How to remove tree sap from car? Hi,I have som...  \n",
       "1             faq  How do I get a sticker off of a car window  (p...  \n",
       "2             faq           How to get rid of garbage disposal odor?  \n",
       "3             faq                      How to maintain a lawn mower?  \n",
       "4             faq  How to fix vertical blinds? I can no longer re...  \n",
       "...           ...                                                ...  \n",
       "16319  user_query  Cigarette smoke odor is so annoying. Some frie...  \n",
       "16320  user_query  I love smoking a pipe but the smell is quite d...  \n",
       "16321  user_query  What are the best tips for getting rid of the ...  \n",
       "16322  user_query  The weed smell is really hard to get rid of. D...  \n",
       "16323  user_query  My roommate started smoking marijuana every th...  \n",
       "\n",
       "[16324 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check relevance_label_df\n",
    "relevance_label_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>answer</th>\n",
       "      <th>id</th>\n",
       "      <th>label</th>\n",
       "      <th>query_type</th>\n",
       "      <th>question</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>I have had good luck with commercial products ...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to remove tree sap from car? Hi,I have som...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goo Gone is a fantastic product (available at ...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How do I get a sticker off of a car window  (p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1. Inspect the black rubber cover that fits in...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to get rid of garbage disposal odor?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>To properly maintain your lawnmover, you'll ne...</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to maintain a lawn mower?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Click on the link and follow the steps.</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How to fix vertical blinds? I can no longer re...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4308</th>\n",
       "      <td>Rubbing compound then put some wax on it...bug...</td>\n",
       "      <td>4309</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How do you remove other car paint rubs from yo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4309</th>\n",
       "      <td>If you didn't buy the fan yet you have to cons...</td>\n",
       "      <td>4310</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>how to replace bathroom fan?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4310</th>\n",
       "      <td>The halogen is made such that the actual lamp ...</td>\n",
       "      <td>4311</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How do you change the small down lights used i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4311</th>\n",
       "      <td>Wow, so many answers, so few correct.  It amaz...</td>\n",
       "      <td>4312</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>How do I know how much air to put in car tires...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4312</th>\n",
       "      <td>Time travel back and induce labor on your moth...</td>\n",
       "      <td>4313</td>\n",
       "      <td>1</td>\n",
       "      <td>faq</td>\n",
       "      <td>how can i change  my birthday?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4313 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 answer    id  label  \\\n",
       "0     I have had good luck with commercial products ...     1      1   \n",
       "1     Goo Gone is a fantastic product (available at ...     2      1   \n",
       "2     1. Inspect the black rubber cover that fits in...     3      1   \n",
       "3     To properly maintain your lawnmover, you'll ne...     4      1   \n",
       "4               Click on the link and follow the steps.     5      1   \n",
       "...                                                 ...   ...    ...   \n",
       "4308  Rubbing compound then put some wax on it...bug...  4309      1   \n",
       "4309  If you didn't buy the fan yet you have to cons...  4310      1   \n",
       "4310  The halogen is made such that the actual lamp ...  4311      1   \n",
       "4311  Wow, so many answers, so few correct.  It amaz...  4312      1   \n",
       "4312  Time travel back and induce labor on your moth...  4313      1   \n",
       "\n",
       "     query_type                                           question  \n",
       "0           faq  How to remove tree sap from car? Hi,I have som...  \n",
       "1           faq  How do I get a sticker off of a car window  (p...  \n",
       "2           faq           How to get rid of garbage disposal odor?  \n",
       "3           faq                      How to maintain a lawn mower?  \n",
       "4           faq  How to fix vertical blinds? I can no longer re...  \n",
       "...         ...                                                ...  \n",
       "4308        faq  How do you remove other car paint rubs from yo...  \n",
       "4309        faq                       how to replace bathroom fan?  \n",
       "4310        faq  How do you change the small down lights used i...  \n",
       "4311        faq  How do I know how much air to put in car tires...  \n",
       "4312        faq                     how can i change  my birthday?  \n",
       "\n",
       "[4313 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check faq_qa_pair_df\n",
    "faq_qa_pair_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to list\n",
    "faq_qa_pairs = list(faq_qa_pairs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'answer': 'I have had good luck with commercial products available at auto supply stores for just a few dollars. Below are some additional suggestions if you want to try DIY:',\n",
       "  'id': 1,\n",
       "  'label': 1,\n",
       "  'query_type': 'faq',\n",
       "  'question': 'How to remove tree sap from car? Hi,I have some tree sap on my car, both older (pretty dry) and some newer from a redwood tree.Any tips on how to remove it?'},\n",
       " {'answer': \"Goo Gone is a fantastic product (available at Target and most any hardware store or drug store) and works amazingly well on even pesky sticker residue.  Soak the area, and wipe it away.  It might take repeated applications, but you can remove the sticker without scratching, and without leaving a gooey residue.Note that Goo Gone can affect some surfaces, including some plastics, so you should be careful, especially if it is clear plastic.  I've used Goo Gone many times on plastic glassic and CD cases, so it will certainly work on many plastics, but you should be careful. In the event that Goo Gone seems too risky, you can frequently remove just the adhesive residue using plain old Scotch tape.  Remove as much of the sticker as possible (the paper/plastic backing) and then apply tape to the sticker residue firmly.  Pull the tape off sharply, and some of the residue will come with it.  This is a slow process so only do it if the area is relatively small, however, I frequently use this tactic for price tags and the like and it is surprisingly effective.\",\n",
       "  'id': 2,\n",
       "  'label': 1,\n",
       "  'query_type': 'faq',\n",
       "  'question': 'How do I get a sticker off of a car window  (plastic part)?  Nail polish remover seems risky?'},\n",
       " {'answer': '1. Inspect the black rubber cover that fits in the disposal. Sometimes bits of garbage collect under the flaps and create a smell.    2. Clean the rubber cover, if necessary. If yours lifts out, remove it and clean away debris with a scrub brush and warm, soapy water. If your rubber cover is installed permanently, lift up each flap and clean it with soapy water and an old toothbrush.    3. Deodorize the disposal. Cut a lemon in half and drop the fruit and a handful of baking soda into the disposal. Turn on the cold water faucet and then the disposal. The unit will clean itself as it grinds up the mixture.',\n",
       "  'id': 3,\n",
       "  'label': 1,\n",
       "  'query_type': 'faq',\n",
       "  'question': 'How to get rid of garbage disposal odor?'},\n",
       " {'answer': \"To properly maintain your lawnmover, you'll need to do the things below. If you use use your lawnmover year round, you might need to do maintaince twice a year.1) Changing the oil (for non-electric)2) Removing dirt, grass, and grime from the underside3) Cleaning the air filter4) Cleaning the spark plugsYou'll need the following tools to do the job:1) Putty knife2) Socket wrench3) File or sharpening tool4) Wire brush5) 30-weight oilIt actually didn't sound so hard to maintain. The most important safety concern is to remember to remove the spark plug before working on the blade.You'll find the instructions on the home depot site.\",\n",
       "  'id': 4,\n",
       "  'label': 1,\n",
       "  'query_type': 'faq',\n",
       "  'question': 'How to maintain a lawn mower?'},\n",
       " {'answer': 'Click on the link and follow the steps.',\n",
       "  'id': 5,\n",
       "  'label': 1,\n",
       "  'query_type': 'faq',\n",
       "  'question': 'How to fix vertical blinds? I can no longer re-hang them because they are broken.'}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check first 5 entries in faq_qa_pairs\n",
    "faq_qa_pairs[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 4313/4313 [00:00<00:00, 5586.11it/s]\n",
      "/Users/nattiya/miniconda3/envs/dl/lib/python3.8/site-packages/elasticsearch/connection/base.py:190: ElasticsearchDeprecationWarning: [types removal] Specifying types in bulk requests is deprecated.\n",
      "  warnings.warn(message, category=ElasticsearchDeprecationWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished indexing 4313 records to faqir index\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    \n",
    "    es = connections.create_connection(hosts=['localhost'])\n",
    "    \n",
    "    # Index data to Elasticsearch \n",
    "    index_name = \"faqir\"\n",
    "    \n",
    "    # Initialize index (only perform once)\n",
    "    index = Index(index_name)\n",
    "\n",
    "    # Define custom settings\n",
    "    index.settings(\n",
    "        number_of_shards=1,\n",
    "        number_of_replicas=0\n",
    "    )\n",
    "\n",
    "    # Delete the index, ignore if it doesn't exist\n",
    "    index.delete(ignore=404)\n",
    "\n",
    "    # Create the index in Elasticsearch\n",
    "    index.create()\n",
    "\n",
    "    # Register a document with the index\n",
    "    index.document(QA)\n",
    "\n",
    "    # Ingest data to Elasticsearch\n",
    "    ingest_data(faq_qa_pairs, es=es, index=index_name)\n",
    "\n",
    "    print(\"Finished indexing {} records to {} index\".format(len(faq_qa_pairs), index_name))\n",
    "\n",
    "except Exception:\n",
    "    logging.error('exception occured', exc_info=True)\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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}