UseCases/Recommendations_product_search/Recommendations_product_search_OpenAI_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "38bc532d-c78c-4c89-b191-242da0733f39",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Recommendations during a product search using Generative AI with Vantage\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fff71661-19b4-423a-867a-7c815b064c81",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>Introduction:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The combination of <b>OpenAIEmbeddings</b> and <b>Vantage in the db_function</b> assists consumers in receiving product recommendations while looking for items on the website in the recommendations system using generative AI demo.</p>\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233c'><b>Vantage usage:</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will build a product recommendation system using OpenAI embeddings and Vantage in db_function <b>VectorDistance</b>. We will also use <b>Vantage as VectorDB</b>, to store the embeddings. We are focusing on recommendation systems, which are a type of information filtering system that seeks to predict the rating or preference that a user would give to an item. We often use them on e-commerce websites to recommend products to users based on their past purchase history, browsing behavior, and other factors. In our demo, we use product-to-product recommendations based on embedding distances. The VectorDistance function will return the closest products from the databases as our recommendations.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will illustrate the architecture using the following diagram.</p>\n",
    "\n",
    "<center><img src=\"images/openai_emb3.png\" alt=\"Product_search_architecture\"  width=1000 height=800/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79e54d47-af28-412e-b27d-c2dae812ea11",
   "metadata": {},
   "source": [
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Before we go any farther, let's get a better understanding of Cosine similarity (our chosen distance measure method) and Embeddings.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b89e1d8-35fc-44de-8783-aa762d996a7b",
   "metadata": {},
   "source": [
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Cosine similarity:</b></li></ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; In natural language processing (NLP), we use vectors to represent words or phrases as sets of numbers. These numbers capture the meaning of the word or phrase in a manner understandable by computers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use cosine distance as a method for measuring the similarity between two vectors. We calculate the cosine of the angle between the two vectors. The cosine of an angle falls within the range of -1 to 1. A value of 0 indicates that the vectors are perpendicular, 1 suggests they are pointing in the same direction, and -1 indicates they are pointing in opposite directions.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>So, if we have two vectors that are very similar, the cosine of the angle between them will be close to 1. And if we have two vectors that are very different, the cosine of the angle between them will be close to 0.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have a bunch of products, and we want to know how similar they are to each other. We represent each product as a vector of numbers, where each number represents a different feature of the product. For example, we could have a vector for <b>cheese</b> that looks like this: <b>[0.6, -0.2, 0.8, 0.9, -0.1, -0.7]</b>. Once we have represented each product as a vector, we can use cosine similarity to measure how similar they are.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, we would expect the cosine of an angle to be close to 1 between cheese and butter, because they have many similar features and they both are dairy products. However, we would expect the cosine of an angle to be close to 0 or less than 0 between cheese and eggs, because they are not as similar.</p>\n",
    "\n",
    "<center><img src=\"images/cosine.png\" alt=\"cosine\" width=1000 height=800/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "133c67cc-17ae-4317-becd-26f96fd6a444",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li> <b>Embeddings:</b></li></ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> &emsp;  &emsp; We believe that embeddings are the A.I.-native way to represent any kind of data, making them the perfect fit for us when working with all kinds of A.I.-powered tools and algorithms. We can represent text, images, and soon audio and video. We have many options for creating embeddings, whether locally using an installed library or by calling an API.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> Embedding models, like Word2Vec or GloVe, learn vector representations for words based on co-occurrence statistics. For instance, in Word2Vec, a word's vector is optimized to predict surrounding words in a context window. Each word's vector captures semantic relationships, with similar words having closer vectors. In essence, the model learns to represent words in a multi-dimensional space where similar words are close together. For example, \"king\" and \"queen\" might have similar vectors due to their contextual similarity in many sentences.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Imagine we have a bunch of words, and we want to find a way to represent them in a way that captures their meaning. One way we can do this is by creating a word embedding. A word embedding is a vector of numbers that represents the meaning of a word. We choose the numbers in the vector so that words that are similar in meaning have similar vectors.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, we might have vectors for words like \"cheese,\" \"butter,\" \"chocolate,\" and \"sauce\" that look like the following:</p>\n",
    "\n",
    "<center><img src=\"images/word_embeddings.png\" alt=\"word_embeddings\"  width=1000 height=800/></center>\n",
    "\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our vector, the numbers don't have any special meaning by themselves. They just represent the way that the word \"cheese\" is related to other words in our vocabulary.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can use word embeddings to find the similarity between words. For example, we can calculate the cosine similarity between the vector for \"cheese\" and the vector for \"butter\". The cosine similarity is a measure of how similar two vectors are, and it ranges from 0 to 1. A cosine similarity of 1 means that the two vectors are perfectly aligned, and a cosine similarity of 0 means that the two vectors are completely unrelated.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our case, the cosine similarity between our vector for \"cheese\" and our vector for \"butter\" would be very high. This is because we consider the words \"cheese\" and \"butter\" to be very similar in meaning. We know they are both foods that are made from milk, and they are both often used in cooking.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can also use word embeddings to find related words. For example, we can find all of the words that are similar in meaning to \"cheese\". This would include words like \"milk\", \"cream\", \"yogurt\", and \"feta\".</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We find word embeddings to be a powerful tool for natural language processing. We can utilize them for a variety of tasks, such as sentiment analysis, machine translation, and question answering.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Above is a visual representation of how we, as word embeddings, work.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Imagine we have a bunch of points in a high-dimensional space. Each point represents a word, and our position in space represents the meaning of the word. Words that are similar in meaning will be close together in space, and words that are different in meaning will be far apart.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, imagine that we take a slice through our high-dimensional space. This slice will be a two-dimensional space, and the points in our two-dimensional space will represent our word embeddings. The distance between two points in our two-dimensional space will be a measure of the similarity between the two words.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this way, we can use word embeddings to represent the meaning of words in a way that is both compact and informative.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ca49b9d-df0b-400a-acf8-0252ab8a2618",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233c'><b>Steps in the analysis:</b></p>\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Configuring the environment</li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Data Exploration</li>\n",
    "    <li>Generate the embeddings</li>\n",
    "    <li>Load the existing embeddings to DB</li>\n",
    "    <li>Calculate the VectorDistance using Teradata Vantage in-DB function</li>\n",
    "    <li>Display the recommended products for the users</li>\n",
    "    <li>Cleanup</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a833b5bf-74af-42be-8543-3782e1da95dc",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>1. Configuring the environment</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.1 Install the required libraries</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f6027a7-888d-441f-abc7-a6ea1c45f0a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install --upgrade -r requirements_openai.txt --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca97cdce-0d5e-4e54-b404-da7bae24ef51",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <i>The above statements will install the required libraries for us to run this demo. To gain access to the installed libraries after running this, we should restart the kernel.</i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86b160ce-5ace-4116-86b6-394d6502553b",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style='font-size:16px;font-family:Arial;color:#00233C'><i><b>Note:</b> We want to bring to your attention that the above statements may need to be uncommented if you run the notebooks on a platform other than ClearScape Analytics Experience that does not have the libraries installed. If we uncomment those installs, we ensure to restart the kernel after executing those lines to bring the installed libraries into memory. The simplest way to restart the Kernel is by typing zero zero: <b>0 0</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61067c88-2e9a-4c92-985b-34dc4ab74a13",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>1.2 Import the required libraries</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea50a4aa-1211-44fc-8166-317c35253207",
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# vis\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "import timeit\n",
    "from tqdm.auto import tqdm\n",
    "from tqdm.notebook import *\n",
    "\n",
    "tqdm_notebook.pandas()\n",
    "\n",
    "# teradata lib\n",
    "from teradataml import *\n",
    "from teradataml import VectorDistance\n",
    "\n",
    "# helper functions\n",
    "from utils.sql_helper_func import *\n",
    "from utils.openai_helper_func import *\n",
    "\n",
    "# open AI\n",
    "import openai\n",
    "\n",
    "from IPython.display import display, Markdown\n",
    "\n",
    "# Suppress warnings\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "display.max_rows = 5\n",
    "\n",
    "display.print_sqlmr_query = False\n",
    "display.suppress_vantage_runtime_warnings = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a59718f8-7af4-4d1a-abc7-a860eb7cbae3",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>2. Connect Vantage and OpenAI</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c60e4d23-3f17-4d43-b9b9-7e125f59b8c7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.1 Connect to Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will be prompted to provide the password. We will enter the password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "164cfc91-93ed-45b9-98ba-73b91a50c28b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)\n",
    "execute_sql('''SET query_band='DEMO= Recommendations_product_search_OpenAI_Python.ipynb;' UPDATE FOR SESSION;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b22db506-84a7-406b-be9f-9fe69d268ba4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8beec557-411b-4418-acf3-2f28d3c6beac",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>2.2 Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. We have the option of either running the demo using foreign tables to access the data without using any storage on our environment or downloading the data to local storage, which may yield somewhat faster execution. However, we need to consider available storage. There are two statements in the following cell, and one is commented out. We may switch which mode we choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e26ca7c-fa9e-4200-8e9e-27f5886aa5ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_cloud');\"        # Takes 1 minute\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_local');\"        # Takes 2 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "639f799a-4895-4179-a27e-a6117692ce20",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – We should execute the below step only if we want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79252a5e-b2c1-404a-ae22-6c3d698510c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "490f3fe2-ed63-4838-bb19-4c0d0157453d",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>3. Data Exploration</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Product recommendation systems are a type of recommender system that suggests products to users based on what they are searching for in the search box. To recommend products to users, we will use OpenAI embeddings and Vantage in db_function.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For this demo, we're using Instacart Market Basket Analysis data downloaded from  <a href=\"https://www.kaggle.com/competitions/instacart-market-basket-analysis/data\">kaggle</a>. There are total 5 tables like product, orders, aisles, departments, and order_products_prior. However, for this demonstration, we'll solely focus on utilizing the <b>products</b> table.</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In our case, each row represents a snapshot of data taken from the products table. Below, we have the list of columns in our product table:</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> \n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>product_id</li>\n",
    "    <li>product_name</li>\n",
    "    <li>aisle_id</li>\n",
    "   <li>department_id</li>\n",
    "\n",
    "</ol>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Market Basket Analysis data from <a href=\"https://www.kaggle.com/competitions/instacart-market-basket-analysis/data\">kaggle</a> is loaded in Vantage with table named <i>Products</i>.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b><i>*Please scroll down to the end of the notebook for detailed column descriptions of our dataset.</i></b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eda8e565-b58b-4cc6-8d9c-50790ca3dcab",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1 Examine the Products table</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's look at the sample data in our Products table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a9951cf-e2b0-40d7-9a8f-a01c7f48ef09",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"products\"))\n",
    "print(\"Data information: \\n\", tdf.shape)\n",
    "tdf.sort(\"product_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0d6d954-b72f-41c3-9f8c-bc15cf74d672",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have approximately 50k records in total, with four variables. The products are listed from various departments. When users search for items on the page, we will recommend the products to them.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e22503f-9573-4625-b03d-988a707cbfb5",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.1 Analyze Number of products per aisle.</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's do some data exploration with aisle and number of products. \n",
    "A histogram of the number of products per aisle is a useful tool for us to understand the distribution of products in our store. It can help us identify aisles with a high or low number of products, as well as aisles with a wide or narrow range of products.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a13ec2d-5c45-4668-b4f6-f0ce97eb6efa",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_count_by_aisle = (\n",
    "    tdf.groupby([\"aisle_id\"])\n",
    "    .agg({\"product_id\": [\"count\"]})\n",
    "    .sort(\"count_product_id\", False)\n",
    ")\n",
    "\n",
    "tdf_aisles = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"aisles\"))\n",
    "\n",
    "# join aisle and product\n",
    "product_count_by_aisle = product_count_by_aisle.join(\n",
    "    tdf_aisles, on=\"aisle_id\", how=\"left\", lsuffix=\"p_\", rsuffix=\"a_\"\n",
    ").sort(\"count_product_id\", False)\n",
    "\n",
    "product_count_by_aisle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8b683eb-caf1-4cfc-8e26-3be528db93c0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that we have calculated the histogram values using the teradataml functions. Vantage's Clearscape Analytics can easily integrate with 3rd party visualization tools like Tableau, PowerBI or many python modules available like plotly, seaborn etc. We can do all the calculations and pre-processing on Vantage and pass only the necessary information to visualization tools, this will not only make the calculation faster but also reduce the time due to less data movement between tools. We do the data transfer for this and the subsequent visualizations wherever necessary.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97dd104f-76f0-48fd-8ff4-2e660d745ba6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "res = product_count_by_aisle.to_pandas()[1:11]\n",
    "ax = res.plot(\n",
    "    x=\"aisle\",\n",
    "    y=\"count_product_id\",\n",
    "    kind=\"bar\",\n",
    "    figsize=(15, 7),\n",
    "    legend=False,\n",
    "    xlabel=\"aisle\",\n",
    "    ylabel=\"no_products\",\n",
    "    rot=45,\n",
    "    fontsize=12,\n",
    ")\n",
    "\n",
    "# Display y-axis values on bars\n",
    "for p in ax.patches:\n",
    "    ax.annotate(\n",
    "        str(p.get_height()),\n",
    "        (p.get_x() + p.get_width() / 2.0, p.get_height()),\n",
    "        ha=\"center\",\n",
    "        va=\"center\",\n",
    "        xytext=(0, 10),\n",
    "        textcoords=\"offset points\",\n",
    "        fontsize=12,\n",
    "    )\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8782959f-e27a-4709-9b78-c2969fe1a4d0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Based on the graph presented above, we can see that aisle <b>candy chocolate</b> boasts the highest number of products, with a total of 1,258 items.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d769c12-652f-4ef5-8b19-d241d4589422",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.1.2 Sample the data.</b></p>   \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To save the cost of generating embeddings from OpenAI, we will use the <b>50 products from snacks department</b> in our demo. This will allow us to test the system without incurring too much cost. Once we have validated the system, we can then consider expanding it to include more products.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b693c31-465d-4aef-afd4-42b66e36cd6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_sample = tdf.loc[tdf[\"department_id\"] == 19]\n",
    "print(tdf_sample.shape)\n",
    "tdf_sample.sort(\"product_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "760c5c6e-d610-4416-8e33-91177db63a4f",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>3.2 Do we want to generate the embeddings?</b></p>    \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have already generated embeddings for the snacks department and stored them in files.</p>\n",
    "\n",
    "<center><img src=\"images/decision_emb_gen.png\" alt=\"embeddings_decision\" width=300 height=300/></center>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: If we would like to skip the embedding generation step and move on to the next section, we can click  <a href=\"#section51\">here</a> to skip.</b></i></p>\n",
    "</div>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>To save time, we can move to the already generated embeddings section. However, if you would like to see how we generate the embeddings, or if you need to generate the embeddings for a different dataset, then continue to the following section.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7db03d40-0df5-4d9c-a1d3-5fc74b3683a7",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>4. Generate the embeddings </b>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>In this section, we are creating the OpenAI embeddings for 6000+ snacks products. It will cost us a few dollars on our OpenAI account.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dcc305e-2d63-41c6-82d3-2b01d0dffa25",
   "metadata": {},
   "source": [
    "<a id='section41'></a>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.1 Get the OpenAI API key</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bee4908-59e4-4578-abbf-bac9e76e7fc1",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In order to generate the OpenAI embeddings for section 4, we will need an OpenAI API key. If we do not have one, we should refer to the instructions provided in this guide to obtain our OpenAI API key: </p>\n",
    "\n",
    "[OpenAI_setup_api_key_guide](..//Openai_setup_api_key/Openai_setup_api_key.md)\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: We estimate that the embedding generation step will take approximately 30 minutes to complete. If we prefer to skip this step and proceed to the next section, we can click  <a href=\"#section51\">here</a> to skip.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b388e4ab-796d-41fc-90a9-abbe5bb140d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "# enter your OpenAI api key\n",
    "api_key = getpass.getpass(prompt=\"\\n Please Enter OpenAI api key: \")\n",
    "\n",
    "# set api key\n",
    "openai.api_key = api_key"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89367d27-22ca-4a0e-b299-0f8e1a669157",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.2 Generate the embeddings for product table</b></p>    \n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Under the hood, we will use the OpenAI embeddings method to generate the embeddings. We will use OpenAI embeddings, a type of word embedding that we can utilize to represent products in a way that captures their semantic meaning. To generate embeddings for a product table, we will use the product name field. We will utilize the OpenAI Embeddings API to generate embeddings for each product. Please refer to the <a href=\"https://platform.openai.com/docs/guides/embeddings\">Embeddings documentation</a> for more information about embeddings and types of models available.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We take a text string as input and return a vector of numbers that represent the embedding using the OpenAI Embeddings API. The length of the vector depends on the model we are using. For example, the text-embedding-3-small model returns a vector of 1536 numbers.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will use <b>text-embedding-3-small</b> as the model and <b>cl100k_base</b> as the encoding technique.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bd05d2b-bc64-4620-abc9-17dadab064c0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To generate the embeddings, we will call the <b>get_embeddings()</b> function. This function will convert our Teradata DataFrame to a Pandas DataFrame and generate the embeddings. Once the embeddings are generated, we will store them in separate columns so that we can pass them to the <b>VectorDistance()</b> function later on.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb5e1a6d-3d83-4e9a-b74b-e3fe7c7d5f04",
   "metadata": {},
   "outputs": [],
   "source": [
    "def recursive_emb_generator(table_name, file_name, chunksize=100):\n",
    "    wallclock_time_start = timeit.default_timer()\n",
    "\n",
    "    # delete the records\n",
    "    delete_emb_from_sql(table_name, eng)\n",
    "\n",
    "    # Read the data in chunks of 1000 rows\n",
    "    temp_df = pd.read_csv(file_name, chunksize=chunksize)\n",
    "\n",
    "    # Iterate over the chunks\n",
    "    for chunk in tqdm(\n",
    "        temp_df,\n",
    "        desc=\"Overall progress \",\n",
    "    ):\n",
    "        start = timeit.default_timer()\n",
    "        print(\"\\n\\nData size in current chunk: \", chunk.shape)\n",
    "        df_chunk = get_embeddings(chunk)\n",
    "\n",
    "        copy_emb_to_sql(table_name=table_name, tdf=df_chunk)\n",
    "        print(f\"{df_chunk.shape[0]} products saved to sql\")\n",
    "        end = timeit.default_timer()\n",
    "        print(f\"time taken for {df_chunk.shape[0]} products: {end - start}\")\n",
    "\n",
    "    wallclock_time_end = timeit.default_timer()\n",
    "    wallclock_time = wallclock_time_end - wallclock_time_start\n",
    "    print(\"wallclock time:\\t\", wallclock_time)\n",
    "    print(\"-\" * 50, \" complete \", \"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44723861-2530-4adc-8053-abf46866a2c3",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: Please be patient. It will take us about 30 minutes to generate the embeddings for more than 6000 products. We are passing products in batches of 100, and after that, we create and store the embeddings in Vantage. Therefore, getting through will take time. </b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b6eaf65-cd2c-4b22-9a25-3d90d7506bdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_sample.to_csv(\"./df_snacks.csv\")\n",
    "\n",
    "recursive_emb_generator(\n",
    "    table_name=\"product_embeddings\", file_name=\"df_snacks.csv\", chunksize=100\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f28d63f-f43e-4bb4-bedc-dff7bdf8311a",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<a id='section43'></a>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>4.3 Display the product embeddings</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0e05f95-4227-4a6a-9b1e-2372b01fb2fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_embeddings = DataFrame(\"product_embeddings\")\n",
    "print(\"Data information: \\n\", product_embeddings.shape)\n",
    "product_embeddings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1932e4e0-d66d-40ae-815d-c5213e67065d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the generated embeddings for all of our products are in a vector of 1536 columns.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example: We generate embeddings for product name: <b>Chocolate Sandwich Cookies</b> consisting of 1536 numbers. They look like:<br>\n",
    "<code>-0.022753, -0.005572, 0.002955, -0.006420, -0.009042, -0.001586,  ... -0.020612\t </code></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b85fb4-20be-4b92-a565-271fb7f3a32e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, we have generated the embeddings from the product names and saved the product embeddings dataframe into a vantage table named <b>product_embeddings</b> to use it further.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4781b83c-0907-4bbd-8fe1-1067e587512b",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 4.4 Get the embedding for few product search terms</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48cfc258-b177-41e1-805f-7264d24cce13",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's take <b>10 random products from the same department</b> to check their recommended products from our database. To do this, we need to follow the same process as before: generate the embeddings for the products and store them back to the Vantage table.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a422bf9-4051-493e-af09-e71da4a42acb",
   "metadata": {},
   "outputs": [],
   "source": [
    "tdf_search_products = tdf.loc[tdf[\"department_id\"] == 19].tail(10)\n",
    "\n",
    "print(tdf_search_products.shape)\n",
    "tdf_search_products.sort(\"product_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "956aa406-e841-41b1-b22a-e4aa0a3ad7ac",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the get_embeddings() function to generate the embeddings by utilizing the OpenAI Embeddings API.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c35e938f-8324-49ee-8646-84f02dc89bd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "start = timeit.default_timer()\n",
    "df_search_products = get_embeddings(tdf_search_products)\n",
    "end = timeit.default_timer()\n",
    "load_time = end - start\n",
    "print(\n",
    "    f\"generate the embeddings for {df_search_products.shape[0]} search products:\\t\",\n",
    "    load_time,\n",
    ")\n",
    "print(\"----- complete -----\")\n",
    "\n",
    "# Print the DataFrame.\n",
    "df_search_products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9140060-c796-4f6a-8226-106482067361",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Since we searched the product names, we have now generated the embeddings. Therefore, we must save the product embeddings dataframe into a new table called <b>search_product_embeddings</b> before we can utilize it further.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0efc5ec3-3c61-4f83-a7f8-f56af34f0db0",
   "metadata": {},
   "outputs": [],
   "source": [
    "delete_and_copy_embeddings(\n",
    "    table_name=\"search_product_embeddings\", tdf=df_search_products, eng=eng\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3f2dc8e-b973-4893-987d-37c58fbd6441",
   "metadata": {},
   "source": [
    "<a id='section51'></a>\n",
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>5. Load the existing embeddings to DB</b>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.1 Load the products and searched products embeddings</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we will load existing embeddings from parquet files stored into the embeddings folder to our database. To speed up our demo execution, we have already run step 4 and generated the embeddings for 6000+ products and stored them as parquet to save time and cost. Please refer to the <a href=\"https://platform.openai.com/docs/guides/embeddings\">Embeddings documentation</a> for more information about embeddings and types of embeddings models available. This will allow us to perform further processing on the embeddings.</p>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: If we have already executed the Generate the embeddings section, then below code will be skipped automatically.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "945689ae-bfbb-46e1-b41d-e7dc1ab0b42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "is_section4_executed = False\n",
    "try:\n",
    "    is_section4_executed = (\n",
    "        DataFrame.from_query(\n",
    "            \"select count(*) as emb_cnt from product_embeddings\"\n",
    "        ).get_values()[0][0]\n",
    "        > 0\n",
    "    )\n",
    "except:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e3f77fa-1918-4e9f-b5f8-b81381869f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "\n",
    "\n",
    "def get_section5_desc_start():\n",
    "    return \"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We first read the data from the files. The files contain information about the product embeddings and our searched product embeddings. Then, we load the data into a permanent table in SQL. Once the data is loaded, we will use the Vantage in-database function <code>VectorDistance</code> to calculate the distance between the product embeddings and our searched product embeddings. Our data contains product embeddings, which are lists of numerical values, or vectors.</p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have an embeddings file with over 6,000 records, each containing 1536 numerical features. This means our file is quite large, and it may take some time for us to load it into SQL.</p>\n",
    "    <div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>:Please be patient. We are loading data from files and copying it to SQL. This process may take 50-80 seconds.</i></p>\n",
    "</div>\"\"\"\n",
    "\n",
    "\n",
    "def get_section5_desc_end():\n",
    "    return \"\"\"<a id='section52'></a><p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>5.2 Display the product embeddings</b></p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>To give you a better idea of what our embeddings look like, here are the first five rows of our product embeddings:</p>\"\"\"\n",
    "\n",
    "\n",
    "def get_section5_desc_sample():\n",
    "    return \"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see that the generated embeddings for all of our products are in a vector of 1536 columns.</p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example, the embeddings we generated for our product name, <b>Chocolate Sandwich Cookies</b>, consist of 1536 numbers and look like:<br>\n",
    "<code>-0.00819 0.01290 0.00875 -0.00294 -0.01980 -0.01041</code></p>\"\"\"\n",
    "\n",
    "\n",
    "def load_the_emb():\n",
    "    is_section5_executed = False\n",
    "\n",
    "    if not is_section4_executed:\n",
    "        is_section5_executed = True\n",
    "        start = timeit.default_timer()\n",
    "        display(Markdown(get_section5_desc_start()))\n",
    "\n",
    "        # load product_embeddings to sql\n",
    "        df_snacks_product_embeddings_prq = pd.read_parquet(\n",
    "            \"./embeddings/snacks_product_embeddings_prq.parquet.gzip\"\n",
    "        )\n",
    "        delete_and_copy_embeddings(\n",
    "            table_name=\"product_embeddings\",\n",
    "            tdf=df_snacks_product_embeddings_prq,\n",
    "            eng=eng,\n",
    "        )\n",
    "\n",
    "        # load search_product_embeddings to sql\n",
    "        snacks_search_product_embeddings_prq = pd.read_parquet(\n",
    "            \"./embeddings/snacks_search_product_embeddings_prq.parquet.gzip\"\n",
    "        )\n",
    "        delete_and_copy_embeddings(\n",
    "            table_name=\"search_product_embeddings\",\n",
    "            tdf=snacks_search_product_embeddings_prq,\n",
    "            eng=eng,\n",
    "        )\n",
    "\n",
    "        end = timeit.default_timer()\n",
    "        load_time = end - start\n",
    "        print(f\"embeddings load time:\\t\", load_time)\n",
    "\n",
    "        display(Markdown(get_section5_desc_end()))\n",
    "        product_embeddings = DataFrame(\"product_embeddings\")\n",
    "        return product_embeddings, is_section5_executed\n",
    "    else:\n",
    "        display(\n",
    "            Markdown(\n",
    "                \"\"\"<br><div class=\"alert alert-block alert-success\">\n",
    "        <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>Section 4: We have already executed the generation of the embeddings! Therefore, we are skipping the execution of the above code.</i></p></div>\"\"\"\n",
    "            )\n",
    "        )\n",
    "        return None, is_section5_executed\n",
    "\n",
    "\n",
    "sample_embeddings, flag = load_the_emb()\n",
    "sample_embeddings.sort(\"product_id\") if sample_embeddings is not None else None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fce8cc2-91f7-4217-b471-9af37327101b",
   "metadata": {},
   "outputs": [],
   "source": [
    "display(Markdown(get_section5_desc_sample())) if flag else None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "593971b5-89d1-4fa2-9098-7757889fa3ff",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>6. Calculate the VectorDistance using Teradata Vantage in-DB function</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d79ecde-2796-4745-baeb-586af0d60847",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Vector distance refers to the measurement of the length or magnitude between two vectors in a multi-dimensional space. It quantifies the dissimilarity or similarity between vectors, commonly used in fields like machine learning and data analysis for tasks such as clustering, classification, and information retrieval. Popular distance metrics include Euclidean distance, Manhattan distance, and cosine similarity, each suitable for different types of data and applications.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo, we wil use <b>TD_VectorDistance</b> with <b>Cosine</b> distance metrics. The TD_VectorDistance function accepts a table of target vectors and a table of reference vectors and returns a table that contains the distance between target-reference pairs.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The function computes the distance between the target pair and the reference pair from the same table if you provide only one table as the input.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "348cf2db-0cdc-4c63-9853-985f0559fd21",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The VectorDistance function calculates the distance between our target vector and a reference vector. We use the cosine distance metric, which measures the similarity between two vectors. We can return the maximum of 1 to 100 closest reference vectors to include in our output table for each target vector. In this demo, we want the top 2 closest reference vectors to our target vector.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The VectorDistance function has a parameter <b>distance_measure</b>. We can pass any one from the below list. Default value is cosine.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Cosine distance measures</b> the similarity between two vectors by calculating the cosine of the angle between them. It is a good measure of similarity for high-dimensional data, as it is not affected by the magnitude of the vectors.</li>\n",
    "    <li><b>Euclidean distance measures</b> the distance between two points in a Euclidean space. It is the most common distance measure, and it is a good measure of similarity for low-dimensional data.</li>\n",
    "    <li><b>Manhattan distance measures</b> the distance between two points in a Manhattan space. It is similar to Euclidean distance, but it uses the absolute value of the difference between the coordinates instead of the square of the difference.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79325118-ede2-433f-8693-38caea02048d",
   "metadata": {},
   "source": [
    "<center><img src=\"images/distance_measure.png\" alt=\"distance_measure\"  width=600 height=600/></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e117f30f-5960-42d0-9f0f-ef4c7842067c",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create a function called <code>TD_VECTORDISTANCE</code> that takes the target table, reference table, embedding column names, and number of recommendations as inputs.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62c80f2b-315f-4df1-ad14-6bd62d6e2223",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_vector_distance(target_table, reference_table, emb_column_names, topk):\n",
    "    start = timeit.default_timer()\n",
    "    VectorDistance_out = VectorDistance(\n",
    "        target_id_column=\"product_id\",\n",
    "        target_feature_columns=emb_column_names,\n",
    "        ref_id_column=\"product_id\",\n",
    "        ref_feature_columns=emb_column_names,\n",
    "        distance_measure=[\"Cosine\"],\n",
    "        topk=topk,\n",
    "        target_data=target_table,\n",
    "        reference_data=reference_table,\n",
    "    )\n",
    "\n",
    "    print(f\"vector-distance calculation time:\\t\", timeit.default_timer() - start)\n",
    "    return VectorDistance_out.result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e20175e0-4f2a-4640-9cfb-64ab81f08cbd",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: By default, we suggest 5 recommendations for each searched product. If we want to change this, we can update the value of the <code>number_of_recommendations</code> variable.</i></p>\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Please be aware that when calculating vector distances, we may experience approximately 80-100 seconds of processing time. This is because our platform is still small, and we are employing advanced mathematical algorithms to determine the cosine distance between products. This process can be computationally intensive, resulting in a slightly longer processing time.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e00b58fc-f187-4c0f-ac1a-abe2372625e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "emb_column_names = DataFrame(\"search_product_embeddings\").columns[4:]\n",
    "\n",
    "# select top matching\n",
    "number_of_recommendations = 5\n",
    "\n",
    "product_embeddings_df = DataFrame(\"product_embeddings\")\n",
    "search_product_embeddings_df = DataFrame(\"search_product_embeddings\")\n",
    "\n",
    "vector_distance_df = calculate_vector_distance(\n",
    "    target_table=search_product_embeddings_df,\n",
    "    reference_table=product_embeddings_df,\n",
    "    emb_column_names=emb_column_names,\n",
    "    topk=number_of_recommendations,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb1b578f-83c2-497f-b81c-2036d9544a9e",
   "metadata": {},
   "source": [
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>7. Display the recommended products for the users.</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5def33ea-01e1-446b-8b75-7be46b7eaa72",
   "metadata": {},
   "source": [
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>To view the recommendations, we need to join two tables together. First, we will join the vector distance result table with the product embeddings table. This will give us a table that contains the vector distance scores for each product, as well as the product embeddings. Then, we will join this table with the search products table. This will give us a final table that contains the recommendations for the search products.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64ee647a-f78e-4b0e-943a-c4894797ec2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_final_recommendations(\n",
    "    vector_distance_df, product_embeddings_df, search_product_embeddings_df\n",
    "):\n",
    "    product_embeddings_df_selected_columns = product_embeddings_df.select(\n",
    "        [\"product_id\", \"product_name\"]\n",
    "    )\n",
    "\n",
    "    # join vector-distance results and products\n",
    "    vec_prod_join_result = vector_distance_df.merge(\n",
    "        right=product_embeddings_df_selected_columns,\n",
    "        left_on=\"reference_id\",\n",
    "        right_on=\"product_id\",\n",
    "        lsuffix=\"t1\",\n",
    "        rsuffix=\"t2\",\n",
    "    )\n",
    "\n",
    "    # join the above joined table with search products\n",
    "    vec_prod_join_result_selected = vec_prod_join_result[\n",
    "        [\"product_id\", \"product_name\", \"target_id\", \"distancetype\", \"distance\"]\n",
    "    ]\n",
    "\n",
    "    # join_result_sorted_selected\n",
    "    df_search_products_selected = search_product_embeddings_df.select(\n",
    "        [\"product_id\", \"product_name\"]\n",
    "    )\n",
    "\n",
    "    # recommendation results\n",
    "    df_recommendation = df_search_products_selected.merge(\n",
    "        right=vec_prod_join_result_selected,\n",
    "        left_on=\"product_id\",\n",
    "        right_on=\"target_id\",\n",
    "        how=\"inner\",\n",
    "        lsuffix=\"_search\",\n",
    "        rsuffix=\"_recommended\",\n",
    "    )\n",
    "\n",
    "    # filter with exact match\n",
    "    df_recommendation = df_recommendation[df_recommendation.distance > 0.001]\n",
    "\n",
    "    # sort by distance\n",
    "    df_recommendation = df_recommendation.sort(\n",
    "        [\"_search_product_id\", \"distance\"], ascending=True\n",
    "    )\n",
    "\n",
    "    return df_recommendation[\n",
    "        [\n",
    "            \"_search_product_id\",\n",
    "            \"_search_product_name\",\n",
    "            \"_recommended_product_id\",\n",
    "            \"_recommended_product_name\",\n",
    "            \"distance\",\n",
    "        ]\n",
    "    ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd0dc65a-69d1-4f76-90e9-09e0b117f23b",
   "metadata": {},
   "outputs": [],
   "source": [
    "product_embeddings_df = DataFrame(\"product_embeddings\")\n",
    "search_product_embeddings_df = DataFrame(\"search_product_embeddings\")\n",
    "\n",
    "# get top-k final recommendations for each searched products\n",
    "df_recommendation = get_final_recommendations(\n",
    "    vector_distance_df, product_embeddings_df, search_product_embeddings_df\n",
    ")\n",
    "\n",
    "# copy results to sql for improve the performance\n",
    "copy_to_sql(df_recommendation, table_name=\"df_recommendation\", if_exists=\"replace\")\n",
    "\n",
    "df_recommendation = DataFrame(\"df_recommendation\")\n",
    "df_recommendation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e598589-6215-4225-af70-6e14a23ec66f",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the table above, we can see the recommendations for the products searched by the user. The cosine distance between the searched and recommended products is also shown. Note that a few products have a cosine distance of zero. This occurs when we compare the vectors of the two products. If the two products are identical, then the cosine distance will be zero.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a38e4d0c-62e4-4cbc-ac4d-b7b221595693",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>7.1 Product Matching Visualization: 2D Scatter Plot</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use a product distance scatterplot as a data visualization tool to identify and analyze the relationships between different products. We plot each product as a point on a two-dimensional plane, with the x-axis and y-axis representing two different product features or characteristics. The distance between two points on the scatterplot represents the similarity between the two products, based on the chosen features.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c62c91d-0290-4785-9484-c4b1b07a507c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_full_emb_with_recommend(\n",
    "    vector_distance_df, product_embeddings_df, search_product_embeddings_df\n",
    "):\n",
    "    product_embeddings_df_all = product_embeddings_df.to_pandas().reset_index()\n",
    "    vector_distance_df = vector_distance_df.to_pandas().reset_index()\n",
    "\n",
    "    # join vector-distance results and products\n",
    "    vec_prod_join_result = pd.merge(\n",
    "        vector_distance_df,\n",
    "        product_embeddings_df_all,\n",
    "        left_on=\"reference_id\",\n",
    "        right_on=\"product_id\",\n",
    "        how=\"inner\",\n",
    "    )\n",
    "\n",
    "    # join the above joined table with search products\n",
    "    vec_prod_join_result_all = vec_prod_join_result\n",
    "\n",
    "    # join_result_sorted_selected\n",
    "    df_search_products_selected_all = (\n",
    "        search_product_embeddings_df.to_pandas().reset_index()\n",
    "    )\n",
    "\n",
    "    # recommendation results\n",
    "    df_recommendation2 = pd.merge(\n",
    "        df_search_products_selected_all,\n",
    "        vec_prod_join_result_all,\n",
    "        left_on=\"product_id\",\n",
    "        right_on=\"target_id\",\n",
    "        how=\"inner\",\n",
    "        suffixes=[\"_search\", \"_recommended\"],\n",
    "    )\n",
    "\n",
    "    # sort by distance\n",
    "    return df_recommendation2.sort_values(\n",
    "        [\"product_id_search\", \"distance\"], ascending=True\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fc2985c-df78-43d5-9e8a-5882487f6d04",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.manifold import TSNE\n",
    "\n",
    "df_recommendation_full_emb = get_full_emb_with_recommend(\n",
    "    vector_distance_df, product_embeddings_df, search_product_embeddings_df\n",
    ")\n",
    "\n",
    "rec_cols = []\n",
    "for i in range(1536):\n",
    "    rec_cols.append(f\"embeddings_{i}_search\")\n",
    "    rec_cols.append(f\"embeddings_{i}_recommended\")\n",
    "\n",
    "df_recommendation_full_emb_sel = df_recommendation_full_emb.loc[:, rec_cols]\n",
    "warnings.filterwarnings(\"ignore\", message=\"is_sparse is deprecated\")\n",
    "\n",
    "X_embedded = TSNE(\n",
    "    n_components=2, learning_rate=\"auto\", init=\"random\", perplexity=3\n",
    ").fit_transform(df_recommendation_full_emb_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4ce71bd-0c65-4195-b33d-8acc6820857d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_temp_tnse = pd.DataFrame()\n",
    "df_temp_tnse[\"dimension-1\"] = X_embedded[:, 0]\n",
    "df_temp_tnse[\"dimension-2\"] = X_embedded[:, 1]\n",
    "df_temp_tnse[\"product-name\"] = df_recommendation_full_emb[[\"product_name_recommended\"]]\n",
    "\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)\n",
    "fig = px.scatter(\n",
    "    df_temp_tnse,\n",
    "    x=\"dimension-1\",\n",
    "    y=\"dimension-2\",\n",
    "    color=\"product-name\",\n",
    "    hover_data=[\"product-name\"],\n",
    ")\n",
    "\n",
    "fig.update_traces(marker=dict(size=15))\n",
    "\n",
    "fig.update_layout(\n",
    "    title=\"Product Matching Visualization: 2D Scatter Plot\",\n",
    "    xaxis_title=\"dimension-1\",\n",
    "    yaxis_title=\"dimension-2\",\n",
    "    xaxis=dict(tickangle=45),\n",
    "    width=1500,\n",
    "    height=800,\n",
    "    hoverlabel=dict(bgcolor=\"white\", font_size=16, font_family=\"Rockwell\"),\n",
    "    autosize=False,\n",
    ")\n",
    "\n",
    "# Customize the hover template\n",
    "fig.update_traces(\n",
    "    hovertemplate=\"<b>Product Name:</b> %{customdata[0]}<br><extra></extra>\"\n",
    ")\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e1d2c01-ac46-4599-8705-58b57410f6f2",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>From the above graph, we can observe the below points:</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>The system recommends products that are in close proximity or clustered together, taking into account their distance from each other. <b>Pomegranate Gummy Bears, Gummy Bears, Pomegranate Hard Candies, Organic Gummy Bears</b></li>\n",
    "    <li>Products that are isolated or not part of any cluster appear as scattered points on the graph.</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc13892b-8660-4f0f-b45e-553612496221",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>7.2 Product Distance Heatmap</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We provide a visual representation of the relationships and distances between different products with our Product Distance Heatmap. This heatmap offers us valuable insights into how our products relate to each other, helping us make informed decisions regarding product placement, clustering, or other strategic considerations.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3babfed7-1c82-4f76-8e0b-04956dfada2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a pivot table\n",
    "heatmap_data = df_recommendation.to_pandas().pivot(\n",
    "    index=\"_search_product_name\", columns=\"_recommended_product_name\", values=\"distance\"\n",
    ")\n",
    "\n",
    "# Create the heatmap using Plotly\n",
    "fig = go.Figure(\n",
    "    data=go.Heatmap(\n",
    "        z=heatmap_data.values,\n",
    "        x=heatmap_data.columns,\n",
    "        y=heatmap_data.index,\n",
    "        colorscale=\"RdBu\",\n",
    "    )\n",
    ")\n",
    "\n",
    "fig.update_layout(\n",
    "    title=\"Distance measures between searched product and recommended product\",\n",
    "    xaxis_title=\"Recommended Products\",\n",
    "    yaxis_title=\"Searched Products\",\n",
    "    xaxis=dict(tickangle=45),\n",
    "    width=1500,\n",
    "    height=800,\n",
    "    hoverlabel=dict(bgcolor=\"white\", font_size=16, font_family=\"Rockwell\"),\n",
    "    autosize=False,\n",
    ")\n",
    "\n",
    "fig.update(\n",
    "    data=[\n",
    "        {\n",
    "            \"hovertemplate\": \"Product Searched: %{y}<br>Product Recommended: %{x}<br>distance: %{z}<extra></extra>\"\n",
    "        }\n",
    "    ]\n",
    ")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2030e44-ea79-46de-8ca6-88ccaff0bf7b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the graph above, we illustrate distances using a color spectrum ranging from red to blue. Products marked in red represent shorter distances, while those in blue indicate greater distances.</p><br/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3657516f-ce8b-42b5-b2cc-2ba7020e02cf",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 7.3 View recommended products</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Based on the products we searched for, here we'll display the recommended products. We have created a response template which will help user to view recommendations within notebook in better way. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0654916-ba86-4f96-8f23-5b5c0c9dcdbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, Markdown\n",
    "\n",
    "def response_template(arr, cnt, top_k):\n",
    "    view = \"\"\"<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>Product Recommendations</b></p>\"\"\"\n",
    "    i = 0\n",
    "    # we are filtering records with 0 distance, so it will return 4 rec for each product\n",
    "    top_k = top_k - 1\n",
    "\n",
    "    while i < cnt * top_k and i < len(arr):\n",
    "        product_name_search = arr[i, 0]\n",
    "        view = (\n",
    "            view\n",
    "            + f\"\"\" <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>  <li> Based on your search for  <strong>{product_name_search}</strong> here are some recommended products: <ul>\"\"\"\n",
    "        )\n",
    "        j = i\n",
    "        view2 = \"\"\n",
    "        while j < i + top_k:\n",
    "            product_name_recommended = arr[j, 1]\n",
    "            view2 = view2 + f\"\"\" <li>{product_name_recommended}</li>\"\"\"\n",
    "            j += 1\n",
    "        i += top_k\n",
    "        view = view + view2 + \"</ul></ul>\"\n",
    "    return view"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c7e71d9-d50c-487b-928d-59415c8a5211",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\" id=\"no-azure\">\n",
    "   <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: By default, we display 3 searched products. To change this, we can update the value of the <code>number_of_products_to_display</code> variable.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eb37cd1-720d-45a8-a2c8-66f8d5ec7706",
   "metadata": {},
   "outputs": [],
   "source": [
    "number_of_products_to_display = 3\n",
    "\n",
    "# get values\n",
    "df_recommendation_cols = (\n",
    "    df_recommendation.select([\"_search_product_name\", \"_recommended_product_name\"])\n",
    "    .groupby(\"_search_product_name\")\n",
    "    .sort(\"_search_product_name\")\n",
    "    .get_values()\n",
    ")\n",
    "\n",
    "display(\n",
    "    Markdown(\n",
    "        response_template(\n",
    "            df_recommendation_cols,\n",
    "            number_of_products_to_display,\n",
    "            number_of_recommendations,\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "538269ba-a501-4cad-8904-cfbd5103e0e6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the above list, we can see the recommendations for the searched product, without altering HTML tags.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8c7974d-6a6b-4a7d-bd64-923c650f116e",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 7.4 Real time recommendation: Select the product</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>When we select a product from the dropdown, our system will instantly recommend a list of new products that are similar to the selected product. Our recommendations are updated in real time as we select the product, so we can always find new and relevant products to explore.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3356abdf-c5d1-4108-8a1d-1223a9a81cab",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ipywidgets import interact, Dropdown\n",
    "import ipywidgets as widgets\n",
    "\n",
    "layout = widgets.Layout(width=\"500px\", height=\"30px\")\n",
    "\n",
    "p1 = [\n",
    "    \"\".join(i)\n",
    "    for i in search_product_embeddings_df.to_pandas()\n",
    "    .reset_index()\n",
    "    .iloc[3:][\"product_name\"]\n",
    "    .tolist()\n",
    "]\n",
    "prod_dw = Dropdown(\n",
    "    options=p1,\n",
    "    description=\"Select the product:\",\n",
    "    style={\"description_width\": \"initial\"},\n",
    "    display=\"flex\",\n",
    "    flex_flow=\"column\",\n",
    "    align_items=\"stretch\",\n",
    "    layout=layout,\n",
    ")\n",
    "\n",
    "\n",
    "@interact(product_name=prod_dw)\n",
    "def print_product(product_name):\n",
    "    df_recommendation_filtered = df_recommendation[\n",
    "        df_recommendation._search_product_name == product_name\n",
    "    ]\n",
    "    df_recommendation_filtered = (\n",
    "        df_recommendation_filtered.select(\n",
    "            [\"_search_product_name\", \"_recommended_product_name\"]\n",
    "        )\n",
    "        .groupby(\"_search_product_name\")\n",
    "        .sort(\"_search_product_name\")\n",
    "        .get_values()\n",
    "    )\n",
    "    display(\n",
    "        Markdown(\n",
    "            response_template(df_recommendation_filtered, 1, number_of_recommendations)\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b4e124e-741c-4758-83c9-500287a89fb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def no_key_desc():\n",
    "    return \"\"\"<div class=\"alert alert-block alert-info\">\n",
    "    <p style='font-size:16px;font-family:Arial;color:#00233C'><i><b>Note</b>: To execute the below functionality, we should have an OpenAI key. If we don't have the key, then we can click <a href=\"#section8\">here</a> to skip section 7.5.</i></p>\n",
    "</div>\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0383c8fc-56fc-4e6d-9eca-f04b80269078",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code below will not run if you have already entered the OpenAI key.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "730e5364-c364-47bb-b882-459e77db6983",
   "metadata": {},
   "outputs": [],
   "source": [
    "if openai.api_key == None:\n",
    "    display(Markdown(no_key_desc()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "033ee744-31e5-42b9-bf03-ca1c04304154",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b> 7.5 Real time recommendation: Type the product name</b></p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Our real time product recommendation system will provide personalized recommendations to us based on keywords we are searching. When we type in the name of a product, the system will instantly generate a list of similar products that we might also be interested in. This allows us to easily find products that we are likely to love, and it can help to increase sales for our business.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f4d70a2-2b24-4638-b3b3-36cc6aec4b52",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_new_product_embeddings(new_product):\n",
    "    start = timeit.default_timer()\n",
    "    df_new_search = pd.DataFrame(\n",
    "        data={\n",
    "            \"product_id\": 50001,\n",
    "            \"product_name\": [new_product],\n",
    "            \"aisle_id\": 45,\n",
    "            \"department_id\": 19,\n",
    "        }\n",
    "    )\n",
    "\n",
    "    df_new_search_products = get_embeddings(df_new_search)\n",
    "    end = timeit.default_timer()\n",
    "    load_time = end - start\n",
    "    print(\n",
    "        f\"generate the embeddings for {df_new_search_products.shape[0]} search products:\\t\",\n",
    "        load_time,\n",
    "    )\n",
    "    print(\"----- complete -----\")\n",
    "\n",
    "    # Print the DataFrame.\n",
    "    return df_new_search_products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4a3f319-b1f9-4310-88d0-f459b0ca89cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ipywidgets import interact, Text\n",
    "from ipywidgets import IntProgress\n",
    "import ipywidgets as widgets\n",
    "\n",
    "max_count = 100\n",
    "f = IntProgress(min=0, max=max_count)\n",
    "f.layout.visibility = \"hidden\"\n",
    "\n",
    "layout = widgets.Layout(width=\"500px\", height=\"30px\")\n",
    "\n",
    "prod_txt = Text(\n",
    "    value=\"\",\n",
    "    placeholder=\"Type product name\",\n",
    "    description=\"Enter the product name:\",\n",
    "    disabled=False,\n",
    "    align_items=\"stretch\",\n",
    "    layout=layout,\n",
    "    style={\"description_width\": \"initial\"},\n",
    ")\n",
    "\n",
    "btn_search = widgets.Button(\n",
    "    description=\"Search\",\n",
    "    disabled=False,\n",
    "    align_items=\"stretch\",\n",
    "    icon=\"search\",\n",
    "    layout=layout,\n",
    "    button_style=\"primary\",\n",
    "    style=dict(font_weight=\"bold\", text_color=\"white\"),\n",
    ")\n",
    "\n",
    "btn_clr = widgets.Button(\n",
    "    description=\"Clear\",\n",
    "    disabled=False,\n",
    "    align_items=\"stretch\",\n",
    "    icon=\"eraser\",\n",
    "    layout=layout,\n",
    ")\n",
    "\n",
    "output = widgets.Output()\n",
    "\n",
    "btn_search.style.button_color = \"#00233C\"\n",
    "# btn_search.style.font_weight = \"bold\"\n",
    "# btn_search.style.text_color = \"white\"\n",
    "# btn_search.style.button_color = 'lightblue'\n",
    "\n",
    "if flag:\n",
    "    value_new = \"\"\"<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To get more interesting results, we should enter a multi-word product name. We can find product name references in         <a style='color: blue;font-family: Arial;text-decoration: underline;' href=\"#section52\">Section 5.2</a> For example, we could enter: <b><ul style = 'font-size:16px;font-family:Arial;color:#00233C'><li>Sea Salt Hummus Chips</li><li>Chocolate Mint Crisp Protein Bar</li><li>Wheat Biscuits</li></b> </p>\"\"\"\n",
    "else:\n",
    "    value_new = \"\"\"<p style='font-size:16px;font-family:Arial;color:#00233C'>To get more interesting results, we enter a multi-word product name. We can find product name references in <a style='color: blue;font-family: Arial;text-decoration: underline;' href=\"#section43\">Section 4.3</a>. For example, we could enter: <b><ul style='font-size:16px;font-family:Arial;color:#00233C'><li>Sea Salt Hummus Chips</li><li>Chocolate Mint Crisp Protein Bar</li><li>Wheat Biscuits</li></b></p>\"\"\"\n",
    "\n",
    "html_desc = widgets.HTML(\n",
    "    value=value_new,\n",
    "    placeholder=\"\",\n",
    "    description=\"\",\n",
    ")\n",
    "\n",
    "html_op = widgets.HTML(\n",
    "    value=\"\",\n",
    "    placeholder=\"\",\n",
    "    description=\"\",\n",
    ")\n",
    "\n",
    "\n",
    "def btn_clr_click(btn):\n",
    "    prod_txt.value = \"\"\n",
    "    html_op.value = \"\"\n",
    "    output.clear_output()\n",
    "\n",
    "\n",
    "@output.capture()\n",
    "def btn_click(btn):\n",
    "    f.value = 0\n",
    "    if prod_txt.value != \"\":\n",
    "        start = timeit.default_timer()\n",
    "        f.layout.visibility = \"visible\"\n",
    "        html_op.value = \"\"\n",
    "        f.value += 1  # signal to increment the progress bar\n",
    "\n",
    "        # generate new embeddings for typed product\n",
    "        print(\"Generating the embeddings for: \", prod_txt.value)\n",
    "        df_new_search_products = get_new_product_embeddings(prod_txt.value)\n",
    "        f.value = 10\n",
    "\n",
    "        # delete and copy new embeddings\n",
    "        print(\"Copy generated embeddings to SQL...\")\n",
    "        delete_and_copy_embeddings(\n",
    "            table_name=\"search_product_embeddings_user_input\",\n",
    "            tdf=df_new_search_products,\n",
    "            eng=eng,\n",
    "        )\n",
    "        f.value = 40\n",
    "\n",
    "        # calculate vector-distance\n",
    "        product_embeddings_df = DataFrame(\"product_embeddings\")\n",
    "        search_product_embeddings_user_input_df = DataFrame(\n",
    "            \"search_product_embeddings_user_input\"\n",
    "        )\n",
    "        emb_column_names = search_product_embeddings_user_input_df.columns[4:]\n",
    "        f.value = 60\n",
    "\n",
    "        # select top matching\n",
    "        print(\n",
    "            f\"Calculating the distance between the searched snack: {prod_txt.value} and all other snacks...\"\n",
    "        )\n",
    "        number_of_recommendations = 4\n",
    "        vector_distance_df = calculate_vector_distance(\n",
    "            target_table=search_product_embeddings_user_input_df,\n",
    "            reference_table=product_embeddings_df,\n",
    "            emb_column_names=emb_column_names,\n",
    "            topk=number_of_recommendations,\n",
    "        )\n",
    "\n",
    "        f.value = 80\n",
    "        print(\"Getting the recommendations...\")\n",
    "        df_recommendation = get_final_recommendations(\n",
    "            vector_distance_df, product_embeddings_df, search_product_embeddings_user_input_df\n",
    "        )\n",
    "        \n",
    "        print(\"copy recommendations to DB...\")\n",
    "        # copy results to sql for improve the performance\n",
    "        copy_to_sql(df_recommendation, table_name=\"df_recommendation\", if_exists=\"replace\")\n",
    "        df_recommendation = DataFrame(\"df_recommendation\")\n",
    "        \n",
    "        no_of_rec_received = df_recommendation.shape[0]\n",
    "\n",
    "        f.value = 100\n",
    "        f.layout.visibility = \"hidden\"\n",
    "\n",
    "        df_recommendation = (\n",
    "            df_recommendation.select(\n",
    "                [\"_search_product_name\", \"_recommended_product_name\"]\n",
    "            )\n",
    "            .groupby(\"_search_product_name\")\n",
    "            .sort(\"_search_product_name\")\n",
    "            .get_values()\n",
    "        )\n",
    "\n",
    "        html_op.value = response_template(\n",
    "            df_recommendation,\n",
    "            1,\n",
    "            (\n",
    "                no_of_rec_received\n",
    "                if no_of_rec_received < number_of_recommendations\n",
    "                else number_of_recommendations\n",
    "            ),\n",
    "        )\n",
    "        end = timeit.default_timer()\n",
    "        load_time = end - start\n",
    "        print(f\"Total time to get the recommendation:\\t\", load_time)\n",
    "        print(\"----- complete -----\")\n",
    "\n",
    "\n",
    "btn_search.on_click(btn_click)\n",
    "btn_clr.on_click(btn_clr_click)\n",
    "display(html_desc, prod_txt, btn_search, btn_clr, output, html_op, f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e85d091-4163-4189-816e-82ff8beca8c9",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style='font-size:16px;font-family:Arial;color:#00233C'><i><b>Please note that it may take 3 to 4 minutes to get the recommendations.</b> This is because our platform is still small and we are searching for products against a database of over 6,000 snacks. We are also using complex mathematics to calculate the cosine distance between the products, which can take some time.</i></p>\n",
    "</div>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>When you enter a product name in the textbox above, we will take that product name and pass it to a function to generate embeddings. Embeddings are vectors that represent the meaning of a word or phrase. Once we have the embeddings, we will store them in a SQL database.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Next, we will compare the embeddings of the product you entered with the embeddings of all the 6,000+ products in our database. We will use a technique called <code>VectorDistance</code> to measure the similarity between the embeddings. In a nutshell, we are comparing the searched product embeddings against 6,000+ product embeddings. The closer the embeddings are, the more similar the products are. We will then select the products that are the most similar to the product you entered and display them to you as recommendations.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>Finally, we will select the products that are the most similar to the product you entered and display them to you as recommendations.</p>\n",
    "\n",
    "<p style='font-size:16px;font-family:Arial;color:#00233C'>If you clear the text box and enter a new product name, the system will generate new recommendations based on the new product name. This is a great way to discover new products that you might be interested in.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b8b817-c0ff-42b3-a651-c8268ac40942",
   "metadata": {},
   "source": [
    "<div id='section8'></div>\n",
    "<hr style='height:2px;border:none;background-color:#00233C;'>\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233c'>8. Cleanup</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'><b>8.1 Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We need to clean up our work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99ccbcfb-8ae5-4a13-80af-262195c6a0fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [\n",
    "    \"product_embeddings\",\n",
    "    \"search_product_embeddings\",\n",
    "    \"df_recommendation\",\n",
    "    \"search_product_embeddings_user_input\",\n",
    "]\n",
    "\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "\n",
    "    except:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aacb87c-0d81-40f0-8754-608d47e602cc",
   "metadata": {},
   "source": [
    "<hr style='height:1px;border:none;background-color:#00233C;'>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233c'> <b>8.2 Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will use the following code to clean up tables and databases created for this demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b398148-9486-4535-8c06-d3f77669bbdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Grocery_Data');\"        # Takes 5 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ef4cbe-1beb-4c65-ac60-ffd6250668a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f945139",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Required Materials</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let’s look at the elements we have available for reference for this notebook:</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Filters:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>Industry:</b> ECommerce</li>\n",
    "    <li><b>Functionality:</b> Generative AI</li>\n",
    "    <li><b>Use Case:</b> Product Recommendation</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Related Resources:</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><a href='https://www.teradata.com/Blogs/Fraud-Busting-AI'>Fraud-Busting-AI</a></li>\n",
    "    <li><a href='https://www.teradata.com/Industries/Financial-Services'>Financial Services</a></li>\n",
    "    <li><a href='https://www.teradata.com/Resources/Datasheets/Move-from-Detection-to-Prevention-and-Outsmart-Fraudsters'>Move from Detection to Prevention and Outsmart Tech-Savvy Fraudsters</a></li>\n",
    "</ul>\n",
    "\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>Dataset:</b>\n",
    "\n",
    "- `product_id`: Unique row customer id\n",
    "- `product_name`: customer age (numeric)\n",
    "- `aisle_id` : Aisle id (numeric)\n",
    "- `department_id` : Department id (numeric)\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    Dataset source: <a href=\"https://www.kaggle.com/competitions/instacart-market-basket-analysis/data\">Kaggle</a>\n",
    "</p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Teradataml Python reference: <a href = 'https://docs.teradata.com/search/all?query=Python+Package+User+Guide&content-lang=en-US'>here</a></li>\n",
    "    <li>OpenAI embeddings reference: <a href='https://platform.openai.com/docs/guides/embeddings'>here</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5463848-592f-4321-b852-287e133872dd",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}