UseCases/Grocery_Recommendation/Grocery_Recommendation_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "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",
    "       Using Generative AI Large Language Models to Enhance Shopping Experience\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",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <!-- ![business problem](images/bertrec_businessprroblem.png) -->\n",
    "    <p>\n",
    "        Traditional product recommendations have relied on static techniques like product affinity or collaborative filtering. While they offer some value, they fail to consider the dynamic context of shopping, resulting in predictable and often repetitive suggestions. This limitation hinders the potential for a truly personalized and satisfying purchase experience.\n",
    "    </p>\n",
    "    <p>\n",
    "        However, with our cutting-edge <strong>context-based recommendations</strong>, we are revolutionizing the way consumers shop. By harnessing the power of large language models (LLM), we enable customers to enjoy a seamless and delightful shopping journey, where each product recommendation is tailored to their unique preferences and needs.\n",
    "    </p>\n",
    "    <p>\n",
    "        In this demo, we will showcase how Vantage and AzureML synergize to train a state-of-the-art LLM for context-based product recommendations. This sophisticated model will then transform shopping content into relevant and timely product recommendations, allowing users to explore a diverse array of items that genuinely interest them.\n",
    "    </p>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Business Values</b></p>\n",
    "    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "        <li>Personalized Customer Experience.</li>\n",
    "        <li>Improved Customer Engagement and Retention.</li>\n",
    "        <li>Optimized Inventory Management.</li>\n",
    "    </ul>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Why Vantage?</b></p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ML and AI industry continues to innovate at an unprecedented rate. Tools, technologies, and algorithms are being developed and improved in both the open source and commercial communities.\n",
    "<br><br>\n",
    "Unfortunately, many of these techniques haven’t matured to the point where they are readily deployable to a stable, mature operational environment. Furthermore, many open-source techniques rely on fragile, manual enabling technologies.\n",
    "<br><br>\n",
    "ClearScape Analytics <b>Bring Your Own Model</b> capabilities allow organizations to leverage third party and open-source models for scoring inside the Vantage Platform; providing enterprise-class scalability and operational stability for any number of users, applications, or volume of data.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A critical strategy for Vantage and ClearScape Analytics is to embrace the value and innovation in the open-source and partner ML and AI community. A cornerstone of that strategy is to allow users to leverage their ML or AI tools and models of choice to deploy those models directly to the Vantage Platform.  This provides enterprises with the most scalable option for deploying custom machine learning pipelines. Users can leverage the innovation and familiarity of a broad range of tools and techniques, with the ability to prepare and score new data in near-real-time and at any scale; allowing the products of machine learning to become pervasive across all applications, reporting tools, and consumers in an organization.</p>\n",
    "    <p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Dataset:</b>\n",
    "    <p>The data is from Kaggle: <a href = 'https://www.kaggle.com/c/instacart-market-basket-analysis/data'>Instacart Market Basket Analysis</a>. The dataset is anonymized and contains a sample of over 3 million grocery orders from more than 200,000 Instacart users. For each user, there are between 4 and 100 of their orders, with the sequence of products purchased in each order.</p>\n",
    "    \n",
    "<p style=\"font-size: 16px; font-family: Arial; color:#00233C\"><b>No Azure Credentials:</b></p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    If you do not have the required Azure credentials or do not wish to create an Azure account, you can still follow the demo. You will be informed when to skip the steps that require Azure credentials, and we will guide you through the alternative process.\n",
    "    </p>\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    However, if you are interested in using Azure Machine Learning services and want to try the full functionality of the demo, you can follow the instructions in the <a href=\"../Energy_Consumption_Forecasting_AzureML/Getting Started with Azure.ipynb\">Getting Started with Azure</a> guide. This will walk you through setting up an Azure account and acquiring the necessary credentials to fully experience the demo's capabilities.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Downloading and installing additional software needed</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "gather": {
     "logged": 1686211620662
    },
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install tdnpathviz"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Note: </b><i>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 you uncomment those installs, be sure 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>\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,
   "metadata": {
    "gather": {
     "logged": 1686213392957
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import plotly.express as px\n",
    "import tdnpathviz\n",
    "from teradataml import *\n",
    "\n",
    "display.max_rows = 5\n",
    "configure.val_install_location = 'val'\n",
    "configure.byom_install_location = 'mldb'\n",
    "# Column width set to 500 characters\n",
    "pd.set_option('display.max_colwidth', 500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage.</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, and then use the down arrow to go to the next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Grocery_Recommendation_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Begin running steps with Shift + Enter keys. </p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>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. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_cloud');\"        # Takes 30 seconds\n",
    "%run -i ../run_procedure.py \"call get_data('DEMO_Grocery_Data_local');\"        # Takes 3 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see the status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Data Exploration</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's take the first step of our data exploration journey and dive into the rich dataset that comprises customer orders and the products they have added to their shopping carts. To accomplish this, we will leverage the power of TeradataML's dataframes</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "gather": {
     "logged": 1686069956929
    },
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "orders = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"order_products_train\"))\n",
    "orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <p>\n",
    "        The dataset contains information about customers' orders.\n",
    "    </p>\n",
    "    <ul>\n",
    "        <li><strong>order_id:</strong> This column represents a unique identifier for each customer order.</li>\n",
    "<!--         not unique -->\n",
    "        <li><strong>product_id:</strong> This column contains unique identifiers for the products available.</li>\n",
    "        <li><strong>add_to_cart_order:</strong> This column represents the sequence or order in which products were added to the customer's shopping cart during a specific order. For example, if a customer added three products to their cart in the order A, B, C, the <em>add_to_cart_order</em> values would be 1, 2, and 3, respectively.</li>\n",
    "        <li><strong>reordered:</strong> This binary column indicates whether a product has been reordered by a customer in a subsequent order. If a product has a value of 1 in this column, it means that the product was reordered by the customer after being previously purchased. If the value is 0, it means the product was not reordered, i.e., it was either a one-time purchase or a product the customer hasn't purchased again yet.</li>\n",
    "    </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "products = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"products\"))\n",
    "products"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <ol>\n",
    "    <li>\n",
    "        <strong>product_id:</strong> This column contains unique identifiers for each product in the dataset.\n",
    "    </li>\n",
    "    <li>\n",
    "        <strong>product_name:</strong> This column stores the names or descriptions of the products available in the dataset.\n",
    "    </li>\n",
    "    <li>\n",
    "        <strong>aisle_id:</strong> This column represents the aisle to which the product belongs in a physical store.\n",
    "    </li>\n",
    "    <li>\n",
    "        <strong>department_id:</strong> This column indicates the department to which the product is categorized within the store.\n",
    "    </li>\n",
    "    </ol>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "product_id_to_seq_product_id = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"product_id_to_seq_product_id\"))\n",
    "product_id_to_seq_product_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <p>\n",
    "        Let us now try to answer a <strong>business question</strong> - How many items do customers usually buy? Vantage has a rich set of in-database functions, which can help answer this question. Let us use the <strong>in-database Histogram function</strong>.\n",
    "    </p>\n",
    "    <p>\n",
    "        Let me also mention that you can use <strong>the tool of your choice</strong>, such as Python or SQL, to work with Vantage.\n",
    "    </p>\n",
    "    <p>\n",
    "        Let us start with SQL, which is the preferred language for business analysts.\n",
    "    </p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "gather": {
     "logged": 1686213153918
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "qry = \"\"\"\n",
    "SELECT *\n",
    "FROM TD_Histogram (\n",
    "    ON (\n",
    "        SELECT order_id, count(*) AS cnt\n",
    "        FROM DEMO_Grocery_Data.order_products_train\n",
    "        GROUP BY order_id\n",
    "    ) AS InputTable\n",
    "    USING\n",
    "    TargetColumn('cnt')\n",
    "    MethodType('STURGES')\n",
    ") AS dt\n",
    "ORDER BY 1, 2, 3, 4, 5, 6;\n",
    "\"\"\"\n",
    "\n",
    "dft = pd.read_sql(qry, eng)\n",
    "\n",
    "fig = px.bar(dft, x=\"MaxValue\", y=\"Bin_Percent\", labels={\"MaxValue\": \"Max Value Range\", \"Bin_Percent\": \"Percentage\"},\n",
    "             title=\"Percentage Distribution in Max Value Ranges\",\n",
    "             template=\"plotly_white\")  # Use a white background template for better readability\n",
    "\n",
    "# Customize the appearance of the plot\n",
    "fig.update_traces(marker_color='rgb(78, 119, 189)',  # Change bar color to a custom RGB value\n",
    "                  marker_line_color='rgb(8, 48, 107)',  # Set bar edge color to a custom RGB value\n",
    "                  marker_line_width=1.5)  # Set the bar edge width\n",
    "\n",
    "fig.update_layout(xaxis_title_font=dict(size=14, family=\"Arial\"),  # Set x-axis label font size and family\n",
    "                  yaxis_title_font=dict(size=14, family=\"Arial\"),  # Set y-axis label font size and family\n",
    "                  title_font=dict(size=18, family=\"Arial\", color='rgb(8, 48, 107)'),  # Set title font size, family, and color\n",
    "                  xaxis_tickangle=-45,  # Rotate x-axis labels to improve readability\n",
    "                  showlegend=False)  # Hide the legend since it's a simple bar plot\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <p>\n",
    "        From the histogram, we can make an insightful observation: <strong>most consumers typically purchase around 10 items</strong> during their shopping sessions. This valuable insight helps us better understand the shopping behavior of our customers and lays the groundwork for optimizing inventory, promotions, and customer experiences.\n",
    "    </p>\n",
    "\n",
    "<p>Now let us try to find out what are <strong>the top items purchases</strong></p>\n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1686213910147
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "qry = \"\"\"\n",
    "SELECT TOP 20 product_name, COUNT(*) AS cnt\n",
    "FROM DEMO_Grocery_Data.order_products_train AS t, DEMO_Grocery_Data.products AS v\n",
    "WHERE t.product_id = v.product_id \n",
    "GROUP BY product_name\n",
    "ORDER BY cnt DESC\n",
    "\"\"\"\n",
    "\n",
    "dft = pd.read_sql(qry, eng)\n",
    "dft.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1686213910147
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "fig = px.bar(dft, x=\"product_name\", y=\"cnt\", labels={\"product_name\": \"Product Name\", \"cnt\": \"Count\"}, \n",
    "             title=\"Product Purchase Count\",\n",
    "             template=\"plotly_white\") # Use a white background template for better readability\n",
    "\n",
    "# Customize the appearance of the plot\n",
    "fig.update_traces(marker_color='rgb(78, 119, 189)', # Change bar color to a custom RGB value\n",
    "                  marker_line_color='rgb(8, 48, 107)', # Set bar edge color to a custom RGB value\n",
    "                  marker_line_width=1.5) # Set the bar edge width\n",
    "\n",
    "fig.update_layout(xaxis_title_font=dict(size=14, family=\"Arial\"), # Set x-axis label font size and family\n",
    "                  yaxis_title_font=dict(size=14, family=\"Arial\"), # Set y-axis label font size and family\n",
    "                  title_font=dict(size=18, family=\"Arial\", color='rgb(8, 48, 107)'), # Set title font size, family, and color\n",
    "                  xaxis_tickangle=-45, # Rotate x-axis labels to improve readability\n",
    "                  showlegend=False) # Hide the legend since it's a simple bar plot\n",
    "\n",
    "fig.show()\n",
    "\n",
    "display(HTML(f'''\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can observe that top products purchased are <strong>{dft['product_name'][0]}, {dft['product_name'][1]} and {dft['product_name'][2]}</p>\n",
    "'''))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>3. Data preparation</b>\n",
    "\n",
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <p>Let's move on to the data preparation phase to train our powerful large language model (LLM). Now, you might wonder, what does a language model have to do with grocery products?</p>\n",
    "    <p>Well, language models have versatile applications beyond natural language processing. They can predict the next word in a text, just like the helpful auto-completion feature on your phone. Similarly, we can utilize this capability to predict the next item a shopper might add to their cart during grocery shopping.</p>\n",
    "    <p>The LLM we are using goes beyond mere word prediction; it excels at understanding the context of the entire shopping sequence. By harnessing this contextual understanding, the model can recommend items that perfectly align with the customer's shopping preferences.</p>\n",
    "</div>\n",
    "<!-- ![llm for grocery recommendation](images/bertrec_lllm.png) -->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1685958040080
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "product_id_to_seq_product_id = DataFrame(in_schema(\"DEMO_Grocery_Data\", \"product_id_to_seq_product_id\"))\n",
    "product_id_to_seq_product_id.sort(\"seq_product_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The code joins two dataframes(orders and product_id_to_seq_product_id) based on the \"product_id\" column, adds a new column \"bgn\" with a fixed value(101), and then selects and displays a subset of columns from the resultant dataframe. The purpose of this code snippet is to enhance the \"orders\" dataframe with additional information from the \"product_id_to_seq_product_id\" dataframe and prepare it for further analysis or processing.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1685958061996
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "orders_with_seq_ids = orders.join(\n",
    "    other = product_id_to_seq_product_id, \n",
    "    on = \"product_id\",\n",
    "    how = \"inner\", \n",
    "    lsuffix = \"ordrs\", \n",
    "    rsuffix = \"si\")\n",
    "\n",
    "\n",
    "orders_with_seq_ids = orders_with_seq_ids.assign(\n",
    "    bgn = 101\n",
    ").select([\"order_id\", \"add_to_cart_order\", \"seq_product_id\", \"bgn\"])\n",
    "\n",
    "orders_with_seq_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    Vantage in-database function <strong>nPath is perfectly suitable for data preparation for Large language models</strong>. It can efficiently <strong>sequence of products which are purchased</strong>.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1686221702992
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "qry = \"\"\"\n",
    "SELECT dt.order_id, dt.path, dt.countrank\n",
    "FROM nPath (\n",
    "    ON (\n",
    "        SELECT TOP 10000 order_id, add_to_cart_order, product_name \n",
    "        FROM DEMO_Grocery_Data.order_products_train as t, DEMO_Grocery_Data.products as v\n",
    "        WHERE t.product_id = v.product_id \n",
    "        ORDER BY order_id, add_to_cart_order\n",
    "    )\n",
    "    PARTITION BY order_id\n",
    "    ORDER BY add_to_cart_order DESC\n",
    "    USING\n",
    "    Mode (NONOVERLAPPING)\n",
    "    Pattern ('A{1,5}')\n",
    "    Symbols (TRUE AS A)\n",
    "    Result (\n",
    "        FIRST (order_id OF A) AS order_id,\n",
    "        ACCUMULATE (product_name OF A) AS path,\n",
    "        COUNT (* OF A) AS countrank\n",
    "    )\n",
    ") AS dt\n",
    "where path like '%Banana%';\n",
    "\"\"\"\n",
    "\n",
    "dft = DataFrame.from_query(qry)\n",
    "dft"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Shown below is a sankey visualization based on output of Npath function</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dft_head = dft.sort(\"order_id\").head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "from tdnpathviz.visualizations import plot_first_main_paths\n",
    "\n",
    "plot_first_main_paths(dft_head, path_column='path', id_column='order_id', width = 1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us now use NPath function to <strong>prepare the data and store it in AzureML Blob Storage</strong> using the function <strong>WRITE_NOS_FM</strong></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "gather": {
     "logged": 1685968613649
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "prepared_ds = NPath(\n",
    "    data1=orders_with_seq_ids,\n",
    "    data1_partition_column=\"order_id\",\n",
    "    data1_order_column=\"add_to_cart_order\",\n",
    "    mode=\"NONOVERLAPPING\",\n",
    "    pattern=\"A*\",\n",
    "    symbols=\"TRUE as A\",\n",
    "    result=[\"FIRST (bgn OF A) AS c0\",\n",
    "            \"NTH (seq_product_id, 1 OF A) as c1\",\n",
    "            \"NTH (seq_product_id, 2 OF A) as c2\",\n",
    "            \"NTH (seq_product_id, 3 OF A) as c3\",\n",
    "            \"NTH (seq_product_id, 4 OF A) as c4\",\n",
    "            \"NTH (seq_product_id, 5 OF A) as c5\",\n",
    "            \"NTH (seq_product_id, 6 OF A) as c6\",\n",
    "            \"NTH (seq_product_id, 7 OF A) as c7\",\n",
    "            \"NTH (seq_product_id, 8 OF A) as c8\",\n",
    "            \"NTH (seq_product_id, 9 OF A) as c9\",\n",
    "            \"NTH (seq_product_id, 10 OF A) as c10\",\n",
    "            \"NTH (seq_product_id, 11 OF A) as c11\",\n",
    "            \"NTH (seq_product_id, 12 OF A) as c12\",\n",
    "            \"NTH (seq_product_id, 13 OF A) as c13\",\n",
    "            \"NTH (seq_product_id, 14 OF A) as c14\",\n",
    "            \"NTH (seq_product_id, 15 OF A) as c15\",\n",
    "            \"NTH (seq_product_id, 16 OF A) as c16\",\n",
    "            \"NTH (seq_product_id, 17 OF A) as c17\",\n",
    "            \"NTH (seq_product_id, 18 OF A) as c18\",\n",
    "            \"NTH (seq_product_id, 19 OF A) as c19\",\n",
    "            \"NTH (seq_product_id, 20 OF A) as c20\",\n",
    "            \"NTH (seq_product_id, 21 OF A) as c21\",\n",
    "            \"NTH (seq_product_id, 22 OF A) as c22\",\n",
    "            \"NTH (seq_product_id, 23 OF A) as c23\",\n",
    "            \"NTH (seq_product_id, 24 OF A) as c24\",\n",
    "            \"NTH (seq_product_id, 25 OF A) as c25\",\n",
    "            \"NTH (seq_product_id, 26 OF A) as c26\",\n",
    "            \"NTH (seq_product_id, 27 OF A) as c27\",\n",
    "            \"NTH (seq_product_id, 28 OF A) as c28\",\n",
    "            \"NTH (seq_product_id, 29 OF A) as c29\",\n",
    "            \"NTH (seq_product_id, 30 OF A) as c30\",\n",
    "            \"NTH (seq_product_id, 31 OF A) as c31\"\n",
    "    ]\n",
    ").result\n",
    "prepared_ds.to_sql(\"prepared_ds\", if_exists=\"replace\")\n",
    "prepared_ds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "    <p style = 'font-size:16px;font-family:Arial;color:#00233C'><i><b>Note: If you do not have AzureML please click here <a href=\"#no-azure\">here</a> to skip.</b></i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following video will guide you through the process of creating a workspace, a storage account, a container, and obtaining the access ID and access key in Microsoft Azure.</p>\n",
    "<video controls width=\"800\" height=\"500\" src=\"https://storage.googleapis.com/clearscape_analytics_videos/AzureML-Workspace-Creation.mp4\" />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Please edit the following to enter appropriate credentials.\n",
    "location = \"your_location\"\n",
    "access_id = \"your_access_id\"\n",
    "access_key = \"your_access_key\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT NodeId, AmpId, Sequence, ObjectName, ObjectSize, RecordCount\n",
    "FROM WRITE_NOS (\n",
    "    ON (\n",
    "        SELECT\n",
    "            c0,\n",
    "            c1,\n",
    "            coalesce(c2, CASE WHEN c1 IS NULL THEN 0 ELSE 102 END) c2,\n",
    "            coalesce(c3, CASE WHEN c2 IS NULL THEN 0 ELSE 102 END) c3,\n",
    "            coalesce(c4, CASE WHEN c3 IS NULL THEN 0 ELSE 102 END) c4,\n",
    "            coalesce(c5, CASE WHEN c4 IS NULL THEN 0 ELSE 102 END) c5,\n",
    "            coalesce(c6, CASE WHEN c5 IS NULL THEN 0 ELSE 102 END) c6,\n",
    "            coalesce(c7, CASE WHEN c6 IS NULL THEN 0 ELSE 102 END) c7,\n",
    "            coalesce(c8, CASE WHEN c7 IS NULL THEN 0 ELSE 102 END) c8,\n",
    "            coalesce(c9, CASE WHEN c8 IS NULL THEN 0 ELSE 102 END) c9,\n",
    "            coalesce(c10, CASE WHEN c9 IS NULL THEN 0 ELSE 102 END) c10,\n",
    "            coalesce(c11, CASE WHEN c10 IS NULL THEN 0 ELSE 102 END) c11,\n",
    "            coalesce(c12, CASE WHEN c11 IS NULL THEN 0 ELSE 102 END) c12,\n",
    "            coalesce(c13, CASE WHEN c12 IS NULL THEN 0 ELSE 102 END) c13,\n",
    "            coalesce(c14, CASE WHEN c13 IS NULL THEN 0 ELSE 102 END) c14,\n",
    "            coalesce(c15, CASE WHEN c14 IS NULL THEN 0 ELSE 102 END) c15,\n",
    "            coalesce(c16, CASE WHEN c15 IS NULL THEN 0 ELSE 102 END) c16,\n",
    "            coalesce(c17, CASE WHEN c16 IS NULL THEN 0 ELSE 102 END) c17,\n",
    "            coalesce(c18, CASE WHEN c17 IS NULL THEN 0 ELSE 102 END) c18,\n",
    "            coalesce(c19, CASE WHEN c18 IS NULL THEN 0 ELSE 102 END) c19,\n",
    "            coalesce(c20, CASE WHEN c19 IS NULL THEN 0 ELSE 102 END) c20,\n",
    "            coalesce(c21, CASE WHEN c20 IS NULL THEN 0 ELSE 102 END) c21,\n",
    "            coalesce(c22, CASE WHEN c21 IS NULL THEN 0 ELSE 102 END) c22,\n",
    "            coalesce(c23, CASE WHEN c22 IS NULL THEN 0 ELSE 102 END) c23,\n",
    "            coalesce(c24, CASE WHEN c23 IS NULL THEN 0 ELSE 102 END) c24,\n",
    "            coalesce(c25, CASE WHEN c24 IS NULL THEN 0 ELSE 102 END) c25,\n",
    "            coalesce(c26, CASE WHEN c25 IS NULL THEN 0 ELSE 102 END) c26,\n",
    "            coalesce(c27, CASE WHEN c26 IS NULL THEN 0 ELSE 102 END) c27,\n",
    "            coalesce(c28, CASE WHEN c27 IS NULL THEN 0 ELSE 102 END) c28,\n",
    "            coalesce(c29, CASE WHEN c28 IS NULL THEN 0 ELSE 102 END) c29,\n",
    "            coalesce(c30, CASE WHEN c29 IS NULL THEN 0 ELSE 102 END) c30,\n",
    "            CASE WHEN c30 IS NULL THEN 0 ELSE 102 END c31\n",
    "        FROM prepared_ds\n",
    "    )\n",
    "    USING\n",
    "    LOCATION(%s)\n",
    "    AUTHORIZATION('{\"Access_ID\": \"%s\", \"Access_Key\": \"%s\"}')\n",
    "    STOREDAS('PARQUET')\n",
    "    COMPRESSION('GZIP')\n",
    "    NAMING('RANGE')\n",
    "    INCLUDE_ORDERING('TRUE')\n",
    "    MAXOBJECTSIZE('4MB')\n",
    ") AS d \n",
    "ORDER BY AmpId;\n",
    "\"\"\"\n",
    "\n",
    "query = query % (location, access_id, access_key)\n",
    "execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>4. Large Language Model Training</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following video will guide you through the process of creating a data asset, an environment, a job, and running the job to get the model in ONNX format.</p>\n",
    "<video controls width=\"800\" height=\"500\" src=\"https://storage.googleapis.com/clearscape_analytics_videos/AzureML-Job.mp4\" />\n",
    "<!-- ![business problem](images/bertrec_llm_technical.png) -->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>5. Deploying Model in Vantage</b>\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 you do not have AzureML or did not perform the above steps, the following cell will do the required setup to run the remaining notebook.</i></p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The model ONNX file can be now imported into a table in Vantage</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the ONNX file into Vantage\n",
    "model_id = 'bert'\n",
    "model_file = 'plum_flower.onnx'\n",
    "table_name = 'onnx_models'\n",
    "\n",
    "if not get_connection().dialect.has_table(get_connection(), table_name):\n",
    "    try:\n",
    "        save_byom(model_id = model_id, model_file = model_file, table_name = table_name)\n",
    "    except Exception as e:\n",
    "        # if our model exists, delete and rewrite\n",
    "        if str(e.args).find('TDML_2200') >= 1:\n",
    "            delete_byom(model_id = model_id, table_name = table_name)\n",
    "            save_byom(model_id = model_id, model_file = model_file, table_name = table_name)\n",
    "        else:\n",
    "            raise ValueError(f\"Unable to save the model '{model_id}' in '{table_name}' due to the following error: {e}\")\n",
    "\n",
    "# Show the onnx_models table\n",
    "list_byom(table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>6. Generating Recommendation</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we are ready to generate recommendations to delight our customer. We will use Vantage function ONNXPredict</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def collect_seq_ids(prouct_names):\n",
    "\n",
    "    prouct_names_reversed = prouct_names[::-1]\n",
    "    \n",
    "    prod_lst_string = \"'\"+\"','\".join(prouct_names_reversed)+\"'\"\n",
    "\n",
    "    sql_res = DataFrame.from_query(f\"\"\"\n",
    "    select \n",
    "        p.product_name,\n",
    "        pitspi.seq_product_id\n",
    "    from\n",
    "        DEMO_Grocery_Data.products p join\n",
    "        DEMO_Grocery_Data.product_id_to_seq_product_id pitspi on p.product_id = pitspi.product_id\n",
    "    where product_name in (%s)\n",
    "    \"\"\"%prod_lst_string).to_pandas()\n",
    "\n",
    "    result = [None] * len(prouct_names_reversed)\n",
    "    for index, row in sql_res.iterrows():\n",
    "        result[prouct_names_reversed.index(row[\"product_name\"])] = row[\"seq_product_id\"]\n",
    "\n",
    "\n",
    "    return [x for x in result if x is not None]\n",
    "\n",
    "def generate_select_tensor(seq_ids, tensor_length):\n",
    "    result_list = [\n",
    "        # \"101 as input_0_0\",\n",
    "        \"103 as input_0_0\"]\n",
    "\n",
    "    for i in range(0,tensor_length - 1):\n",
    "        if i<len(seq_ids):\n",
    "            val = seq_ids[i]\n",
    "        # elif i == len(seq_ids):\n",
    "        #     val = 102\n",
    "        else:\n",
    "            val = 0\n",
    "\n",
    "        result_list.append(f\"%d as input_0_%d\"%(val, i+1))\n",
    "    return \"select\\n\" + \",\\n\".join(result_list)\n",
    "\n",
    "def get_recomendations(prouct_names, user_id, rec_number = 3, overwrite_cache = True):\n",
    "    seq_ids = collect_seq_ids(prouct_names)\n",
    "\n",
    "    select_tensor = generate_select_tensor(seq_ids, 32)\n",
    "\n",
    "    query = f\"\"\"\n",
    "    select\n",
    "    top %d\n",
    "        tokennum as num, \n",
    "        COALESCE(utp.product_name, p.product_name) as product_name, \n",
    "        COALESCE(utp.product_id, p.product_id) as product_id, \n",
    "        COALESCE(utp.department_id, p.department_id) as department_id,\n",
    "        COALESCE(utp.aisle_id, p.aisle_id) as aisle_id\n",
    "    from\n",
    "    (\n",
    "        with tbl as\n",
    "        (\n",
    "            select\n",
    "                REGEXP_SUBSTR(json_report,'([0-9,]+)', 1, 1, 'c') score\n",
    "            from\n",
    "                mldb.ONNXPredict(\n",
    "                on (%s)\n",
    "                    on (select * from %s where model_id = '%s') dimension\n",
    "                    using\n",
    "                        Accumulate('input_0_0')\n",
    "                        %s\n",
    "                ) a\n",
    "            )\n",
    "        SELECT \n",
    "            tokennum, \n",
    "            cast(seq_product_id as int) seq_product_id\n",
    "        FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, tbl.score, ',')\n",
    "            RETURNS (outkey INTEGER,\n",
    "                    tokennum INTEGER,\n",
    "                    seq_product_id VARCHAR(30) CHARACTER SET UNICODE)\n",
    "                ) AS d\n",
    "        ) f\n",
    "        join DEMO_Grocery_Data.product_id_to_seq_product_id pitspi on pitspi.seq_product_id = f.seq_product_id\n",
    "        join DEMO_Grocery_Data.products p on p.product_id = pitspi.product_id\n",
    "        left join DEMO_Grocery_Data.user_top_products utp on utp.user_id = %d and utp.department_id = p.department_id \n",
    "    where COALESCE(utp.product_name, p.product_name) not in (%s)\n",
    "    order by tokennum\n",
    "    \"\"\"%(\n",
    "        rec_number,\n",
    "        select_tensor, \n",
    "        table_name, \n",
    "        model_id, \n",
    "        f\"OverwriteCachedModel('%s')\"%model_id if overwrite_cache else \"\",\n",
    "        user_id,\n",
    "        \"'\"+\"','\".join(prouct_names[::-1])+\"'\"\n",
    "        )\n",
    "\n",
    "    # print(query)\n",
    "    result = {}\n",
    "\n",
    "    sql_res = DataFrame.from_query(query).to_pandas()\n",
    "\n",
    "    for index, row in sql_res.iterrows():\n",
    "        result[row[\"num\"]] = {\"product_name\": row[\"product_name\"], \"product_id\": row[\"product_id\"], \"department_id\": row[\"department_id\"], \"aisle_id\": row[\"aisle_id\"]}\n",
    "\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Breakfast guy\n",
    "\n",
    "recomendations_dict = get_recomendations(['Egg', 'Center Cut Bacon', 'Limited Edition Pumpkin Spice Cheerios Cereal'], 170516)\n",
    "pd.DataFrame.from_dict(recomendations_dict, orient='index').sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Hard day night guy\n",
    "\n",
    "recomendations_dict = get_recomendations(['Ultra Light Beer'], 170464)\n",
    "pd.DataFrame.from_dict(recomendations_dict, orient='index').sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Italian cocktails lover\n",
    "\n",
    "recomendations_dict = get_recomendations(['Gelato Dessert, Amalfi Lemon', 'Prosecco'], 129098)\n",
    "pd.DataFrame.from_dict(recomendations_dict, orient='index').sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Conclusion</b></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "<div style=\"font-size: 16px; font-family: Arial;color:#00233C\">\n",
    "    <p>The powerful combination of Vantage and AzureML opens up possibilities for creating end-to-end generative AI pipelines. Leveraging in-database functions on Vantage facilitates efficient data exploration and preparation.</p>\n",
    "    <p>The training process for Large Language Models (LLM) benefits from AzureML GPU instances, enabling faster and more effective model development. Once trained, the LLM can be deployed as an ONNX model within Vantage.</p>\n",
    "    <p>With the deployed model, you can scale its operationalization and seamlessly integrate it with your operational data, unlocking the potential for enhanced language-based recommendations and other innovative applications.</p>\n",
    "</div>\n",
    "\n",
    "<!-- ![pipeline](images/bertrec_pipeline.png) -->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. Cleanup</b>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_drop_table(table_name = 'onnx_models', schema_name = 'demo_user')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_Grocery_Data');\"        # Takes 10 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "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, 2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernel_info": {
   "name": "python38-azureml"
  },
  "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"
  },
  "microsoft": {
   "host": {
    "AzureML": {
     "notebookHasBeenCompleted": true
    }
   },
   "ms_spell_check": {
    "ms_spell_check_language": "en"
   }
  },
  "nteract": {
   "version": "nteract-front-end@1.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}