Creating an FAQ Chatbot on BTP/040 Add additional Questions and Answers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c8e40c81-009f-476c-a60e-624f31246da2",
   "metadata": {},
   "source": [
    "# Add additional Questions and Answers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "389bf4c5-e352-413c-b25e-4c45201de616",
   "metadata": {},
   "source": [
    "### Get credentials for SAP HANA Cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "06b01b15-402a-4505-badd-e1d181f81ee6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open('./credentials.json', 'r') as creds:\n",
    "  credentials = json.load(creds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "63350ba9-b6b4-4601-810b-d8e6bdce1c0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "SAP_HANA_CLOUD_ADDRESS  = credentials[\"SAP_HANA_CLOUD\"][\"HANA_ADDRESS\"]\n",
    "SAP_HANA_CLOUD_PORT     = credentials[\"SAP_HANA_CLOUD\"][\"HANA_PORT\"]\n",
    "SAP_HANA_CLOUD_USER     = credentials[\"SAP_HANA_CLOUD\"][\"HANA_USER\"]\n",
    "SAP_HANA_CLOUD_PASSWORD = credentials[\"SAP_HANA_CLOUD\"][\"HANA_PASSWORD\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e21eb2f-2eb5-4648-b0cc-163bb840643f",
   "metadata": {},
   "source": [
    "### Get credentials for SAP AI Core"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "637043c3-2e14-4eba-b828-c5ca2a746247",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ[\"AICORE_CLIENT_ID\"]      = credentials[\"SAP_AI_CORE\"][\"AICORE_CLIENT_ID\"]\n",
    "os.environ[\"AICORE_CLIENT_SECRET\"]  = credentials[\"SAP_AI_CORE\"][\"AICORE_CLIENT_SECRET\"]\n",
    "os.environ[\"AICORE_AUTH_URL\"]       = credentials[\"SAP_AI_CORE\"][\"AICORE_AUTH_URL\"]\n",
    "os.environ[\"AICORE_RESOURCE_GROUP\"] = credentials[\"SAP_AI_CORE\"][\"AICORE_RESOURCE_GROUP\"]\n",
    "os.environ[\"AICORE_BASE_URL\"]       = credentials[\"SAP_AI_CORE\"][\"AICORE_BASE_URL\"]   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be641a0a-8d45-4d8b-9410-94871d9fff19",
   "metadata": {},
   "source": [
    "### Logon to SAP HANA Cloud that has vector engine enabled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b23b166a-0ff6-45bf-b34f-cbac84dc7e56",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import hana_ml.dataframe as dataframe\n",
    "conn = dataframe.ConnectionContext(\n",
    "                                   address  = SAP_HANA_CLOUD_ADDRESS,\n",
    "                                   port     = SAP_HANA_CLOUD_PORT,\n",
    "                                   user     = SAP_HANA_CLOUD_USER,\n",
    "                                   password = SAP_HANA_CLOUD_PASSWORD, \n",
    "                                  )\n",
    "conn.connection.isconnected()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c0422ac-9b4a-4d06-ba04-9c72eacf09c8",
   "metadata": {},
   "source": [
    "### Upload the questions, with empty columns for the embeddings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "217bce4c-6c82-4477-a84b-4a7dffb2a4b6",
   "metadata": {},
   "source": [
    "Load data from Excel to pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "79eca48d-249c-4e1f-a30d-076394ef5e6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install openpyxl\n",
    "import pandas as pd\n",
    "df_q_local = pd.read_excel ('FAQ_QUESTIONS.xlsx') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "537e42c3-4e04-408e-b03b-948c1c1d6c48",
   "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>AID</th>\n",
       "      <th>QID</th>\n",
       "      <th>QUESTION</th>\n",
       "      <th>QUESTION_VECTOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>When was SAP founded?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>What does the acronym \"SAP\" stand for?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>What is SAP’s vision and mission?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>1</td>\n",
       "      <td>What is the business outlook for the current f...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>1</td>\n",
       "      <td>Are you planning to grow organically or throug...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1005</td>\n",
       "      <td>1</td>\n",
       "      <td>Where can I find information about the SAP Exe...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1006</td>\n",
       "      <td>1</td>\n",
       "      <td>Why was SAP AG converted into an SE?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1007</td>\n",
       "      <td>1</td>\n",
       "      <td>What is SAP's current headcount?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1008</td>\n",
       "      <td>1</td>\n",
       "      <td>Do SAP employees participate in the company's ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1009</td>\n",
       "      <td>1</td>\n",
       "      <td>\\nWhat is SAP’s purpose and overarching strate...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AID  QID                                           QUESTION   \n",
       "0  1000    1                              When was SAP founded?  \\\n",
       "1  1001    1             What does the acronym \"SAP\" stand for?   \n",
       "2  1002    1                  What is SAP’s vision and mission?   \n",
       "3  1003    1  What is the business outlook for the current f...   \n",
       "4  1004    1  Are you planning to grow organically or throug...   \n",
       "5  1005    1  Where can I find information about the SAP Exe...   \n",
       "6  1006    1               Why was SAP AG converted into an SE?   \n",
       "7  1007    1                   What is SAP's current headcount?   \n",
       "8  1008    1  Do SAP employees participate in the company's ...   \n",
       "9  1009    1  \\nWhat is SAP’s purpose and overarching strate...   \n",
       "\n",
       "   QUESTION_VECTOR  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  \n",
       "5              NaN  \n",
       "6              NaN  \n",
       "7              NaN  \n",
       "8              NaN  \n",
       "9              NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q_local.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ea8ec6d-d887-442f-851d-0efa0c5eaae4",
   "metadata": {},
   "source": [
    "Download existing questions from SAP HANA Cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f021db4d-9286-4c99-9134-838e9c4628e3",
   "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>AID</th>\n",
       "      <th>QID</th>\n",
       "      <th>QUESTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>When was SAP founded?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>What does the acronym \"SAP\" stand for?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>What is SAP’s vision and mission?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>1</td>\n",
       "      <td>What is the business outlook for the current f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>1</td>\n",
       "      <td>Are you planning to grow organically or throug...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1005</td>\n",
       "      <td>1</td>\n",
       "      <td>Where can I find information about the SAP Exe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1006</td>\n",
       "      <td>1</td>\n",
       "      <td>Why was SAP AG converted into an SE?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1007</td>\n",
       "      <td>1</td>\n",
       "      <td>What is SAP's current headcount?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1008</td>\n",
       "      <td>1</td>\n",
       "      <td>Do SAP employees participate in the company's ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1009</td>\n",
       "      <td>1</td>\n",
       "      <td>\\nWhat is SAP’s purpose and overarching strate...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AID  QID                                           QUESTION\n",
       "0  1000    1                              When was SAP founded?\n",
       "1  1001    1             What does the acronym \"SAP\" stand for?\n",
       "2  1002    1                  What is SAP’s vision and mission?\n",
       "3  1003    1  What is the business outlook for the current f...\n",
       "4  1004    1  Are you planning to grow organically or throug...\n",
       "5  1005    1  Where can I find information about the SAP Exe...\n",
       "6  1006    1               Why was SAP AG converted into an SE?\n",
       "7  1007    1                   What is SAP's current headcount?\n",
       "8  1008    1  Do SAP employees participate in the company's ...\n",
       "9  1009    1  \\nWhat is SAP’s purpose and overarching strate..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q_fromhana = conn.table('FAQ_QUESTIONS').drop('QUESTION_VECTOR').collect()\n",
    "df_q_fromhana.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0629419c-7bd6-4d46-830e-df70b4aa9526",
   "metadata": {},
   "source": [
    "Compare local data with data from SAP HANA Cloud to identify which questions are new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7e50b46c-d76c-405b-895f-a6a113569325",
   "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>AID</th>\n",
       "      <th>QID</th>\n",
       "      <th>QUESTION</th>\n",
       "      <th>QUESTION_VECTOR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1001</td>\n",
       "      <td>2</td>\n",
       "      <td>A Applications and P Products, but what about ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     AID  QID                                           QUESTION   \n",
       "24  1001    2  A Applications and P Products, but what about ...  \\\n",
       "\n",
       "    QUESTION_VECTOR  \n",
       "24              NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = df_q_local.merge(df_q_fromhana, on=['AID', 'QUESTION', 'QID'], \n",
    "                   how='left', indicator=True)\n",
    "df_new = df_all[df_all['_merge'] == 'left_only']\n",
    "df_new = df_new.drop('_merge', axis=1)\n",
    "df_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7949de7-75e6-4011-a20b-ce49d7b6a784",
   "metadata": {},
   "source": [
    "Append new questions to existing SAP HANA Cloud table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "32cff58e-2f56-4a28-a132-bdd60ddfbd87",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 29.40it/s]\n"
     ]
    }
   ],
   "source": [
    "import hana_ml.dataframe as dataframe\n",
    "df_remote = dataframe.create_dataframe_from_pandas(connection_context=conn, \n",
    "                                                   pandas_df=df_new, \n",
    "                                                   table_name='FAQ_QUESTIONS',\n",
    "                                                   force=False,\n",
    "                                                   replace=False, \n",
    "                                                   append=True\n",
    "                                                   )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7f3e64f8-6baa-48e2-88ac-f8e09044c310",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.table('FAQ_QUESTIONS').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ebc93b3-1654-4686-93ec-e2150dabb360",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Upload the answers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "604a1744-a3cf-4531-8dc0-70b349bb4137",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_data= pd.read_excel ('FAQ_ANSWERS.xlsx') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3591e92c-aeda-4af5-8ca2-808ccb423b40",
   "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>AID</th>\n",
       "      <th>ANSWER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>In 1972, five former IBM employees founded the...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>\"SAP\" stands for Systems, Applications, and Pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1002</td>\n",
       "      <td>In the digital era, data is the “new currency”...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>The outlook and mid-term ambition can be found...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>Organic growth remains the primary driver of o...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AID                                             ANSWER\n",
       "0  1000  In 1972, five former IBM employees founded the...\n",
       "1  1001  \"SAP\" stands for Systems, Applications, and Pr...\n",
       "2  1002  In the digital era, data is the “new currency”...\n",
       "3  1003  The outlook and mid-term ambition can be found...\n",
       "4  1004  Organic growth remains the primary driver of o..."
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "892573db-64d0-4e19-b3b3-21057947d771",
   "metadata": {},
   "source": [
    "Because these texts don't need to be vectorised (which might require some time), the table gets deleted and all rows get updated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "403a9664-6596-4407-8f62-6f8c555a4ba2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 29.84it/s]\n"
     ]
    }
   ],
   "source": [
    "import hana_ml.dataframe as dataframe\n",
    "df_remote = dataframe.create_dataframe_from_pandas(connection_context=conn, \n",
    "                                                   pandas_df=df_data, \n",
    "                                                   table_name='FAQ_ANSWERS',\n",
    "                                                   force=True,\n",
    "                                                   replace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3a020694-b0cf-4658-8348-76d2a8835830",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.table('FAQ_ANSWERS').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c361bd53-17f3-4ec0-9ffb-1c13a97d5790",
   "metadata": {},
   "source": [
    "### Calculate text vectors"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01b32744-71b2-4581-a289-90ba69d6e0e5",
   "metadata": {},
   "source": [
    "<font color='red'>Now run the Notebook \"020 Create embeddings of Questions\" that calculates and stores missing vectors for new questions</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d486bfd-7bb9-4f56-83ca-84b376dca880",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sandbox",
   "language": "python",
   "name": "sandbox"
  },
  "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.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}