AzureSQLACSSamples/src/AzureSQL_CogSearch_IntegratedVectorization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Description of sample\n",
    "\n",
    "We will create a small example of an AI application that responds to users' queries based on a SQL table of Amazon product reviews. \n",
    "\n",
    "The end behavior will be something like:\n",
    "\n",
    "```\n",
    "[User search]: Canned dog food\n",
    "[AI Response]: After searching through our product database, I recommend <product ID> because... \n",
    "```\n",
    "\n",
    "Behind the scenes, we take the following steps:\n",
    "* Set up a sample table in a SQL DB and upload data to it\n",
    "* Set up an index in Azure Cognitive Search to store the data we need, including vectorized versions of the text reviews\n",
    "* Set up an indexer in Azure Cognitive Search to pull data into the index \n",
    "  * Automatically chunks and vectorizes the data using an Azure OpenAI Embedding service\n",
    "* Use Azure Cognitive Search to process the user's query and search for the most relevant data\n",
    "* Use an Azure OpenAI Completion service to respond to the user's query\n",
    "\n",
    "Copyright (c) Microsoft Corporation.\n",
    "Licensed under the MIT license."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Requirements\n",
    "\n",
    "You will need:\n",
    "* The Python packages listed in `requirements.txt` (can all be installed using `pip install -r requirements.txt`)\n",
    "  * Please note that we are using a preview version of the [azure-search-documents](https://pypi.org/project/azure-search-documents/#description), version `11.6.0b4`.\n",
    "  * This notebook uses Python 3.10.\n",
    "* An existing SQL Database with server name, DB name, username, and password copied into `example.env`\n",
    "  * You must have permission to create a new table and enable and view change tracking on the database\n",
    "  * You must whitelist your IP to access your SQL server by opening the SQL server resource in the Azure portal, navigating to Security / Networking, and adding your IP.\n",
    "* An OpenAI resource with both an embedding deployment and a completion deployment, with the details copied into `example.env`\n",
    "* A Cognitive Search resource with the endpoint and key copied into `example.env`\n",
    "* The Microsoft ODBC 18 driver, [instructions here](https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver16).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load environment variables and keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dotenv import dotenv_values\n",
    "# specify the name of the .env file name \n",
    "env_name = \"example.env\"\n",
    "config = dotenv_values(env_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Azure SQL database connection details\n",
    "server = config[\"server\"] \n",
    "database = config[\"database\"] \n",
    "username = config[\"username\"] \n",
    "password = config[\"password\"] \n",
    "driver = '{ODBC Driver 18 for SQL Server}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Open AI deployment details\n",
    "import openai\n",
    "openai.api_type = config[\"openai_api_type\"]\n",
    "openai.api_key = config['openai_api_key']\n",
    "openai.api_base = config['openai_api_base']\n",
    "openai.api_version = config['openai_api_version']\n",
    "openai_deployment_completion = config[\"openai_deployment_completion\"]\n",
    "openai_model_completion = config[\"openai_model_completion\"]\n",
    "openai_deployment_embedding = config[\"openai_deployment_embedding\"]\n",
    "openai_model_embedding = config[\"openai_model_embedding\"]\n",
    "EMBEDDING_LENGTH = 1536"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Cognitive Search service details\n",
    "cogsearch_key = config[\"cogsearch_api_key\"]\n",
    "service_endpoint = config[\"cogsearch_endpoint\"]\n",
    "index_name = config[\"cogsearch_index_name\"] # Desired name of index -- does not need to exist already\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Upload data to SQL DB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to database\n",
    "\n",
    "For simplicity, we set `autocommit=True` in the pyodbc connection parameters, which allows us to execute `ALTER` statements. In a production scenario, setting `autocommit=True` is not recommended; instead, the `ALTER` statements can be executed in SSMS or similar.\n",
    "\n",
    "If a timeout error occurs, retry the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "\n",
    "# Create a connection string\n",
    "conn_str = f\"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}\"\n",
    "\n",
    "# Establish a connection to the Azure SQL database\n",
    "conn = pyodbc.connect(conn_str, autocommit=True)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table in the database\n",
    "\n",
    "We will create a new table \"foodreview\" and upload the data from a csv file. We include a primary key, which is necessary for change tracking."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished dropping table (if existed)\n",
      "Finished creating table\n",
      "Finished creating index\n"
     ]
    }
   ],
   "source": [
    "table_name = \"foodreview\" \n",
    "\n",
    "# Drop previous table of same name if one exists\n",
    "cursor.execute(f\"DROP TABLE IF EXISTS {table_name};\")\n",
    "print(\"Finished dropping table (if existed)\")\n",
    "\n",
    "# Create a table\n",
    "cursor.execute(f\"\"\"\n",
    "               CREATE TABLE {table_name} \n",
    "               (Id int NOT NULL, \n",
    "               CONSTRAINT PK_{table_name}_Id PRIMARY KEY CLUSTERED (Id), \n",
    "               ProductId text, \n",
    "               UserId text, \n",
    "               ProfileName text, \n",
    "               HelpfulnessNumerator integer, \n",
    "               HelpfulnessDenominator integer, \n",
    "               Score integer, \n",
    "               Time bigint, \n",
    "               Summary text, \n",
    "               Text text,\n",
    "               TextConcat text);\n",
    "               \"\"\")\n",
    "print(\"Finished creating table\")\n",
    "\n",
    "# Create a index\n",
    "cursor.execute(f\"CREATE INDEX idx_Id ON {table_name}(Id);\")\n",
    "print(\"Finished creating index\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enable change tracking\n",
    "\n",
    "This allows us to automatically update the index when changes are made to the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('42000', \"[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Change tracking is already enabled for database 'test_vector_notebook'. (5088) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]ALTER DATABASE statement failed. (5069)\")\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    cursor.execute(f\"ALTER DATABASE [{database}] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)\")\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    cursor.execute(f\"ALTER TABLE {table_name} ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)\")\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Upload data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data from CSV\n",
    "\n",
    "The data contains a few product reviews, with related info."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>ProductId</th>\n",
       "      <th>UserId</th>\n",
       "      <th>ProfileName</th>\n",
       "      <th>HelpfulnessNumerator</th>\n",
       "      <th>HelpfulnessDenominator</th>\n",
       "      <th>Score</th>\n",
       "      <th>Time</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>B001E4KFG0</td>\n",
       "      <td>A3SGXH7AUHU8GW</td>\n",
       "      <td>delmartian</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1303862400</td>\n",
       "      <td>Good Quality Dog Food</td>\n",
       "      <td>I have bought several of the Vitality canned d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B00813GRG4</td>\n",
       "      <td>A1D87F6ZCVE5NK</td>\n",
       "      <td>dll pa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1346976000</td>\n",
       "      <td>Not as Advertised</td>\n",
       "      <td>Product arrived labeled as Jumbo Salted Peanut...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>B000LQOCH0</td>\n",
       "      <td>ABXLMWJIXXAIN</td>\n",
       "      <td>Natalia Corres \"Natalia Corres\"</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1219017600</td>\n",
       "      <td>\"Delight\" says it all</td>\n",
       "      <td>This is a confection that has been around a fe...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id   ProductId          UserId                      ProfileName  \\\n",
       "0   1  B001E4KFG0  A3SGXH7AUHU8GW                       delmartian   \n",
       "1   2  B00813GRG4  A1D87F6ZCVE5NK                           dll pa   \n",
       "2   3  B000LQOCH0   ABXLMWJIXXAIN  Natalia Corres \"Natalia Corres\"   \n",
       "\n",
       "   HelpfulnessNumerator  HelpfulnessDenominator  Score        Time  \\\n",
       "0                     1                       1      5  1303862400   \n",
       "1                     0                       0      1  1346976000   \n",
       "2                     1                       1      4  1219017600   \n",
       "\n",
       "                 Summary                                               Text  \n",
       "0  Good Quality Dog Food  I have bought several of the Vitality canned d...  \n",
       "1      Not as Advertised  Product arrived labeled as Jumbo Salted Peanut...  \n",
       "2  \"Delight\" says it all  This is a confection that has been around a fe...  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Load Data\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "df_all = pd.read_csv('../DataSet/Reviews_small.csv')\n",
    "\n",
    "df_all.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulate data\n",
    "\n",
    "For our example, we will combine the user's summary with the user's review text."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>ProductId</th>\n",
       "      <th>UserId</th>\n",
       "      <th>ProfileName</th>\n",
       "      <th>HelpfulnessNumerator</th>\n",
       "      <th>HelpfulnessDenominator</th>\n",
       "      <th>Score</th>\n",
       "      <th>Time</th>\n",
       "      <th>Summary</th>\n",
       "      <th>Text</th>\n",
       "      <th>TextConcat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>B001E4KFG0</td>\n",
       "      <td>A3SGXH7AUHU8GW</td>\n",
       "      <td>delmartian</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1303862400</td>\n",
       "      <td>Good Quality Dog Food</td>\n",
       "      <td>I have bought several of the Vitality canned d...</td>\n",
       "      <td>Summary: Good Quality Dog Food | Review: I hav...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>B00813GRG4</td>\n",
       "      <td>A1D87F6ZCVE5NK</td>\n",
       "      <td>dll pa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1346976000</td>\n",
       "      <td>Not as Advertised</td>\n",
       "      <td>Product arrived labeled as Jumbo Salted Peanut...</td>\n",
       "      <td>Summary: Not as Advertised | Review: Product a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>B000LQOCH0</td>\n",
       "      <td>ABXLMWJIXXAIN</td>\n",
       "      <td>Natalia Corres \"Natalia Corres\"</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1219017600</td>\n",
       "      <td>\"Delight\" says it all</td>\n",
       "      <td>This is a confection that has been around a fe...</td>\n",
       "      <td>Summary: \"Delight\" says it all | Review: This ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id   ProductId          UserId                      ProfileName  \\\n",
       "0   1  B001E4KFG0  A3SGXH7AUHU8GW                       delmartian   \n",
       "1   2  B00813GRG4  A1D87F6ZCVE5NK                           dll pa   \n",
       "2   3  B000LQOCH0   ABXLMWJIXXAIN  Natalia Corres \"Natalia Corres\"   \n",
       "\n",
       "   HelpfulnessNumerator  HelpfulnessDenominator  Score        Time  \\\n",
       "0                     1                       1      5  1303862400   \n",
       "1                     0                       0      1  1346976000   \n",
       "2                     1                       1      4  1219017600   \n",
       "\n",
       "                 Summary                                               Text  \\\n",
       "0  Good Quality Dog Food  I have bought several of the Vitality canned d...   \n",
       "1      Not as Advertised  Product arrived labeled as Jumbo Salted Peanut...   \n",
       "2  \"Delight\" says it all  This is a confection that has been around a fe...   \n",
       "\n",
       "                                          TextConcat  \n",
       "0  Summary: Good Quality Dog Food | Review: I hav...  \n",
       "1  Summary: Not as Advertised | Review: Product a...  \n",
       "2  Summary: \"Delight\" says it all | Review: This ...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all[\"TextConcat\"] = df_all.apply(lambda row: f\"Summary: {row['Summary']} | Review: {row['Text']}\",\n",
    "                                    axis = 1)\n",
    "\n",
    "df_all.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Upload to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the dataframe into batches\n",
    "batch_size = 30\n",
    "batches = [df_all[i:i + batch_size] for i in range(0, len(df_all), batch_size)]\n",
    "\n",
    "#Iterate over each batch and insert the data into the database\n",
    "for batch in batches:\n",
    "    # Convert the batch dataframe to a list of tuples for bulk insertion\n",
    "    rows = [tuple(row) for row in batch.itertuples(index=False)]\n",
    "    \n",
    "    # Define the SQL query for bulk insertion\n",
    "    query = f\"INSERT INTO {table_name} (Id, ProductId, UserId, ProfileName, HelpfulnessNumerator, HelpfulnessDenominator, Score, Time, Summary, Text, TextConcat) \\\n",
    "    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\"\n",
    "    cursor.executemany(query, rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example query\n",
    "\n",
    "This checks that the data was uploaded correctly. We should have 99 rows at this point."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(99,)\n"
     ]
    }
   ],
   "source": [
    "# Execute the SELECT statement\n",
    "try:\n",
    "    cursor.execute(f\"SELECT count(Id) FROM {table_name};\")\n",
    "    rows = cursor.fetchall()\n",
    "    for row in rows:\n",
    "        print(row)\n",
    "except Exception as e:\n",
    "    print(f\"Error executing SELECT statement: {e}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Commit changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.commit()\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set up data source connection in Cog Search"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import needed CogSearch functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from azure.core.credentials import AzureKeyCredential  \n",
    "from azure.search.documents import SearchClient  \n",
    "from azure.search.documents.indexes import SearchIndexClient, SearchIndexerClient  \n",
    "from azure.search.documents.models import VectorizableTextQuery\n",
    "from azure.search.documents.indexes.models import (  \n",
    "    AzureOpenAIEmbeddingSkill,  \n",
    "    AzureOpenAIParameters,  \n",
    "    AzureOpenAIVectorizer,  \n",
    "    HnswAlgorithmConfiguration,  \n",
    "    InputFieldMappingEntry,\n",
    "    OutputFieldMappingEntry,  \n",
    "    SemanticPrioritizedFields,    \n",
    "    SearchField,  \n",
    "    SearchFieldDataType,  \n",
    "    SearchIndex,  \n",
    "    SearchIndexer,  \n",
    "    SearchIndexerDataContainer,  \n",
    "    SearchIndexerDataSourceConnection,  \n",
    "    SearchIndexerIndexProjectionSelector,  \n",
    "    SearchIndexerIndexProjections,  \n",
    "    SearchIndexerSkillset,  \n",
    "    SemanticConfiguration,  \n",
    "    SemanticField,  \n",
    "    SemanticSearch,\n",
    "    SplitSkill,  \n",
    "    SqlIntegratedChangeTrackingPolicy,\n",
    "    VectorSearch,  \n",
    "    VectorSearchAlgorithmKind,\n",
    "    VectorSearchProfile\n",
    ")  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create data source connection\n",
    "\n",
    "This step creates a connection that will be used to pull data from our SQL table.\n",
    "\n",
    "Documentation can be found [here.](https://learn.microsoft.com/en-us/azure/search/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data source 'amazon-review-jordan-v1-azuresql-connection' created or updated\n"
     ]
    }
   ],
   "source": [
    "ds_conn_str = f'Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Server=tcp:{server};Database={database};User ID={username};Password={password};'\n",
    "\n",
    "cogsearch_credential = AzureKeyCredential(cogsearch_key)\n",
    "ds_client = SearchIndexerClient(service_endpoint, cogsearch_credential)\n",
    "container = SearchIndexerDataContainer(name=table_name)\n",
    "\n",
    "change_detection_policy = SqlIntegratedChangeTrackingPolicy()\n",
    "\n",
    "data_source_connection = SearchIndexerDataSourceConnection(\n",
    "    name=f\"{index_name}-azuresql-connection\",\n",
    "    type=\"azuresql\",\n",
    "    connection_string=ds_conn_str,\n",
    "    container=container,\n",
    "    data_change_detection_policy=change_detection_policy\n",
    ")\n",
    "data_source = ds_client.create_or_update_data_source_connection(data_source_connection)\n",
    "\n",
    "print(f\"Data source '{data_source.name}' created or updated\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set up automatic chunking + vectorization + indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create index\n",
    "\n",
    "The plan is:\n",
    "1. Take the combined text (summary + review text) from each product review\n",
    "2. Split the combined text into chunks\n",
    "3. Embed each chunk as a vector\n",
    "4. (Later) search for the most relevant chunk based on the incoming query. \n",
    "\n",
    "To enable this, the search index will store all of the following data, for each chunk of text:\n",
    "* Id of chunk\n",
    "* Chunk text\n",
    "* Vector version of chunk text\n",
    "* Id of parent row\n",
    "* Product Id from parent row\n",
    "* Review text from parent row\n",
    "* Summary text from parent row\n",
    "* Score from parent row\n",
    "\n",
    "All of these values will be stored in SearchFields specified in the code below.\n",
    "\n",
    "In this step we also configure the search algorithm(s), and the vectorizer that will automatically vectorize the incoming query.\n",
    "\n",
    "Documentation about creating indexes can be found [here.](https://learn.microsoft.com/en-us/azure/search/search-how-to-create-search-index?tabs=index-other-sdks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amazon-review-jordan-v1 created\n"
     ]
    }
   ],
   "source": [
    "# Create a search index\n",
    "index_client = SearchIndexClient(\n",
    "    endpoint=service_endpoint, credential=cogsearch_credential)\n",
    "\n",
    "fields = [\n",
    "    # Properties of individual chunk\n",
    "    SearchField(name=\"Id\", type=SearchFieldDataType.String, key=True,\n",
    "                sortable=True, filterable=True, facetable=True, analyzer_name=\"keyword\"),\n",
    "    SearchField(name=\"chunk\", type=SearchFieldDataType.String, sortable=False, filterable=False, facetable=False),\n",
    "    SearchField(name=\"vector\", type=SearchFieldDataType.Collection(SearchFieldDataType.Single), \n",
    "                vector_search_dimensions=EMBEDDING_LENGTH, vector_search_profile_name=\"my-vector-search-profile\"),\n",
    "    # Properties of original row in DB that the chunk belonged to\n",
    "    SearchField(name=\"parent_id\", type=SearchFieldDataType.String, sortable=True, filterable=True, facetable=True),\n",
    "    SearchField(name=\"parent_product_id\", type=SearchFieldDataType.String, sortable=True, filterable=True, facetable=True),\n",
    "    SearchField(name=\"parent_text\", type=SearchFieldDataType.String, sortable=True, filterable=True, facetable=True),\n",
    "    SearchField(name=\"parent_summary\", type=SearchFieldDataType.String, sortable=True, filterable=True, facetable=True),\n",
    "    SearchField(name=\"parent_score\", type=SearchFieldDataType.Int64, sortable=True, filterable=True, facetable=True)\n",
    "]\n",
    "\n",
    "# Configure the vector search configuration  \n",
    "vector_search = VectorSearch(\n",
    "    algorithms=[\n",
    "        HnswAlgorithmConfiguration(\n",
    "            name=\"my-hnsw-config\",\n",
    "            kind=VectorSearchAlgorithmKind.HNSW\n",
    "        )\n",
    "    ],\n",
    "    profiles=[\n",
    "        VectorSearchProfile(\n",
    "            name=\"my-vector-search-profile\",\n",
    "            algorithm_configuration_name=\"my-hnsw-config\",\n",
    "            vectorizer=\"my-openai\"\n",
    "        )\n",
    "    ],\n",
    "    vectorizers=[\n",
    "        AzureOpenAIVectorizer(\n",
    "            name=\"my-openai\",\n",
    "            kind=\"azureOpenAI\",\n",
    "            azure_open_ai_parameters=AzureOpenAIParameters(\n",
    "                resource_uri=openai.api_base,\n",
    "                deployment_id=openai_deployment_embedding,\n",
    "                api_key=openai.api_key,\n",
    "                model_name=openai_model_embedding\n",
    "            )\n",
    "        )  \n",
    "    ]  \n",
    ")\n",
    "\n",
    "semantic_config = SemanticConfiguration(\n",
    "    name=\"my-semantic-config\",\n",
    "    prioritized_fields=SemanticPrioritizedFields(\n",
    "        content_fields=[SemanticField(field_name=\"Id\")]\n",
    "    )\n",
    ")\n",
    "\n",
    "# Create the semantic search with the configuration above\n",
    "semantic_search = SemanticSearch(configurations=[semantic_config])\n",
    "\n",
    "# Create the search index\n",
    "index = SearchIndex(name=index_name, fields=fields,\n",
    "                    vector_search=vector_search, semantic_search=semantic_search)\n",
    "result = index_client.create_or_update_index(index)\n",
    "print(f'{result.name} created')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create skillset\n",
    "\n",
    "We use two pre-built skills:\n",
    "1. The Split Skill takes the concatenated text and divides it into chunks (to stay within the token limits for the OpenAI embedding service).\n",
    "2. The Azure OpenAI Embedding Skill takes the outputs of the Split Skill and vectorizes them individually.\n",
    "\n",
    "Afterwards, we apply an Index Projector to make it so that our final index has one item for every chunk of text (rather than one item for every original row in the DB).\n",
    "\n",
    "We recommend the following resources to learn more about the process and how one can adapt it to different applications:\n",
    "* [Overview of indexers](https://learn.microsoft.com/en-us/azure/search/search-indexer-overview)\n",
    "* [Skill context and input annotation language](https://learn.microsoft.com/en-us/azure/search/cognitive-search-skill-annotation-language)\n",
    "* [Reference inputs and outputs in skillsets](https://learn.microsoft.com/en-us/azure/search/cognitive-search-concept-annotations-syntax)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " amazon-review-jordan-v1-skillset created\n"
     ]
    }
   ],
   "source": [
    "# Create a skillset  \n",
    "skillset_name = f\"{index_name}-skillset\"\n",
    "\n",
    "split_skill = SplitSkill(  \n",
    "    description=\"Split skill to chunk documents\",  \n",
    "    text_split_mode=\"pages\",  \n",
    "    context=\"/document\",  \n",
    "    maximum_page_length=300,  \n",
    "    page_overlap_length=20,  \n",
    "    inputs=[  \n",
    "        InputFieldMappingEntry(name=\"text\", source=\"/document/TextConcat\"),  \n",
    "    ],  \n",
    "    outputs=[  \n",
    "        OutputFieldMappingEntry(name=\"textItems\", target_name=\"pages\")  \n",
    "    ]  \n",
    ")\n",
    "\n",
    "embedding_skill = AzureOpenAIEmbeddingSkill(  \n",
    "    description=\"Skill to generate embeddings via Azure OpenAI\",  \n",
    "    context=\"/document/pages/*\",  \n",
    "    resource_uri=openai.api_base,  \n",
    "    deployment_id=openai_deployment_embedding,  \n",
    "    api_key=openai.api_key,\n",
    "    model_name=openai_model_embedding,\n",
    "    inputs=[  \n",
    "        InputFieldMappingEntry(name=\"text\", source=\"/document/pages/*\"),  \n",
    "    ],  \n",
    "    outputs=[  \n",
    "        OutputFieldMappingEntry(name=\"embedding\", target_name=\"vector\")  \n",
    "    ]  \n",
    ")  \n",
    "\n",
    "index_projections = SearchIndexerIndexProjections(  \n",
    "    selectors=[  \n",
    "        SearchIndexerIndexProjectionSelector(  \n",
    "            target_index_name=index_name,  \n",
    "            parent_key_field_name=\"parent_id\", # Note: this populates the \"parent_id\" search field\n",
    "            source_context=\"/document/pages/*\",  \n",
    "            mappings=[  \n",
    "                InputFieldMappingEntry(name=\"chunk\", source=\"/document/pages/*\"),\n",
    "                InputFieldMappingEntry(name=\"vector\", source=\"/document/pages/*/vector\"),\n",
    "                InputFieldMappingEntry(name=\"parent_product_id\", source=\"/document/ProductId\"),\n",
    "                InputFieldMappingEntry(name=\"parent_text\", source=\"/document/Text\"),\n",
    "                InputFieldMappingEntry(name=\"parent_summary\", source=\"/document/Summary\"),\n",
    "                InputFieldMappingEntry(name=\"parent_score\", source=\"/document/Score\")\n",
    "            ],  \n",
    "        ),  \n",
    "    ],\n",
    ")  \n",
    "\n",
    "skillset = SearchIndexerSkillset(  \n",
    "    name=skillset_name,  \n",
    "    description=\"Skillset to chunk documents and generating embeddings\",  \n",
    "    skills=[split_skill, embedding_skill],\n",
    "    index_projections=index_projections  \n",
    ")\n",
    "  \n",
    "client = SearchIndexerClient(service_endpoint, cogsearch_credential)  \n",
    "client.create_or_update_skillset(skillset)  \n",
    "print(f' {skillset.name} created')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create indexer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " amazon-review-jordan-v1-indexer created\n"
     ]
    }
   ],
   "source": [
    "# Create an indexer  \n",
    "indexer_name = f\"{index_name}-indexer\"  \n",
    "\n",
    "indexer = SearchIndexer(  \n",
    "    name=indexer_name,  \n",
    "    description=\"Indexer to chunk documents and generate embeddings\",  \n",
    "    skillset_name=skillset_name,  \n",
    "    target_index_name=index_name,  \n",
    "    data_source_name=data_source.name\n",
    ")  \n",
    "  \n",
    "indexer_client = SearchIndexerClient(service_endpoint, cogsearch_credential)\n",
    "indexer_result = indexer_client.create_or_update_indexer(indexer)  \n",
    "\n",
    "# Run the indexer  \n",
    "indexer_client.run_indexer(indexer_name)\n",
    "print(f' {indexer_name} created')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Indexer status: running\n"
     ]
    }
   ],
   "source": [
    "# Get the status of the indexer  \n",
    "indexer_status = indexer_client.get_indexer_status(indexer_name)\n",
    "print(f\"Indexer status: {indexer_status.status}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Allow some time for the indexer to process the data\n",
    "import time\n",
    "time.sleep(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use vector search for sample application"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Perform queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_query = \"Canned dog food\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the following output, we find the top 3 chunks that are most relevant to the user's query.\n",
    "\n",
    "Feel free to retry the following cell in case of an empty response or a 429 error. An empty response probably indicates that the chunking/embedding process has not finished yet. A 429 error means there have been too many requests to the OpenAI embedding service and should go away on retrying."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Search score: 0.88524085\n",
      "Parent Id: 1 | Chunk id: 703f51d51de7_1_pages_0\n",
      "Product Id: B001E4KFG0\n",
      "Text chunk: Summary: Good Quality Dog Food | Review: I have bought several of the Vitality canned dog food products and have found them all to be of good quality. The product looks more like a stew than a processed meat and it smells better.\n",
      "Review summary: Good Quality Dog Food\n",
      "Review text: I have bought several of the Vitality canned dog food products and have found them all to be of good quality. The product looks more like a stew than a processed meat and it smells better. My Labrador is finicky and she appreciates this product better than  most.\n",
      "Review score: 5\n",
      "-----\n",
      "Search score: 0.87025785\n",
      "Parent Id: 94 | Chunk id: 703f51d51de7_94_pages_3\n",
      "Product Id: B0019CW0HE\n",
      "Text chunk: a couple of cans.  I came home and to my surprise realized that I could save $20 each time I bought dog food if I just buy it off Amazon.<br /><br />All in all, I definitely recommend and give my stamp of approval to natural balance dog food.  While I have never eaten it, my dog seems to love it.\n",
      "Review summary: Great Dog Food!\n",
      "Review text: My golden retriever is one of the most picky dogs I've ever met.  After experimenting with various types of food, I have found she loves natural balance.  What I really like about natural balance is the fact that it has multiple flavors in dry and wet varieties.  I mix her dry food with a little wet food and my golden loves it.  Furthermore, I do like mixing up the flavors each time as I think the same meal day over day might get a little boring, so I figured why not.  I tend to stay away from the fish type though as it smells...<br /><br />Additionally, I started purchasing off Amazon because Petco didn't have the wet food box and only had a couple of cans.  I came home and to my surprise realized that I could save $20 each time I bought dog food if I just buy it off Amazon.<br /><br />All in all, I definitely recommend and give my stamp of approval to natural balance dog food.  While I have never eaten it, my dog seems to love it.\n",
      "Review score: 5\n",
      "-----\n",
      "Search score: 0.8640232\n",
      "Parent Id: 98 | Chunk id: 703f51d51de7_98_pages_0\n",
      "Product Id: B0019CW0HE\n",
      "Text chunk: Summary: Great allergy sensitive dog food, dogs love it | Review: Our pup has experienced allergies in forms of hotspots and itching from other dog foods. The cheap 'you can buy it anywhere' food not only have crazy preservatives in them but can cause health problems for your pets.\n",
      "Review summary: Great allergy sensitive dog food, dogs love it\n",
      "Review text: Our pup has experienced allergies in forms of hotspots and itching from other dog foods. The cheap 'you can buy it anywhere' food not only have crazy preservatives in them but can cause health problems for your pets.  This food works wonders on reducing allergies and our dog loves the food.<br />This message is RAMSEY FrAnkenSteiN approved.\n",
      "Review score: 5\n",
      "-----\n"
     ]
    }
   ],
   "source": [
    "search_client = SearchClient(service_endpoint, index_name, credential=cogsearch_credential)\n",
    "vector_query = VectorizableTextQuery(text=user_query, fields=\"vector\", exhaustive=True)\n",
    "# Use the query below to pass in the raw vector query instead of the query vectorization\n",
    "# vector_query = RawVectorQuery(vector=generate_embeddings(user_query), k=3, fields=\"vector\")\n",
    "  \n",
    "results = search_client.search(\n",
    "    search_text=None,  \n",
    "    vector_queries= [vector_query],\n",
    "    select=[\"Id\", \"parent_id\", \"chunk\", \"parent_product_id\", \"parent_text\", \"parent_summary\", \"parent_score\"],\n",
    "    top=3\n",
    ")\n",
    "\n",
    "for result in results:\n",
    "    print(f\"Search score: {result['@search.score']}\")\n",
    "    print(f\"Parent Id: {result['parent_id']} | Chunk id: {result['Id']}\")\n",
    "    print(f\"Product Id: {result['parent_product_id']}\")\n",
    "    print(f\"Text chunk: {result['chunk']}\") \n",
    "    print(f\"Review summary: {result['parent_summary']}\")\n",
    "    print(f\"Review text: {result['parent_text']}\")\n",
    "    print(f\"Review score: {result['parent_score']}\")\n",
    "    print(\"-----\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate GPT Response"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prompt creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a prompt template \n",
    "template = \"\"\"\n",
    "    The user's query is: {query}\n",
    "    The most relevant product review is: {context}\n",
    "    The user is searching for a product matching their query. \n",
    "    Tell the user that after searching through our product database, you recommend the product described in the provided product review. \n",
    "    Your answer should summarize the review text,\n",
    "    include the product ID, and mention the score given in the review.\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product id: B001E4KFG0. Review text: I have bought several of the Vitality canned dog food products and have found them all to be of good quality. The product looks more like a stew than a processed meat and it smells better. My Labrador is finicky and she appreciates this product better than  most.. Review score: 5\n"
     ]
    }
   ],
   "source": [
    "# create the context from the search response (requires regenerating results)\n",
    "results = search_client.search(\n",
    "    search_text=None,  \n",
    "    vector_queries= [vector_query],\n",
    "    select=[\"Id\", \"chunk\", \"parent_product_id\", \"parent_text\", \"parent_score\"],\n",
    "    top=1\n",
    ")\n",
    "\n",
    "context = \"\"\n",
    "for result in results:\n",
    "    context += f\"Product id: {result['parent_product_id']}. Review text: {result['parent_text']}. Review score: {result['parent_score']}\"\n",
    "    \n",
    "print(context)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    The user's query is: Canned dog food\n",
      "    The most relevant product review is: Product id: B001E4KFG0. Review text: I have bought several of the Vitality canned dog food products and have found them all to be of good quality. The product looks more like a stew than a processed meat and it smells better. My Labrador is finicky and she appreciates this product better than  most.. Review score: 5\n",
      "    The user is searching for a product matching their query. \n",
      "    Tell the user that after searching through our product database, you recommend the product described in the provided product review. \n",
      "    Your answer should summarize the review text,\n",
      "    include the product ID, and mention the score given in the review.\n",
      "    \n"
     ]
    }
   ],
   "source": [
    "prompt = template.format(context=context, query=user_query)\n",
    "print(prompt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Call to OpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Based on your query for canned dog food, we have searched through our product database and recommend product ID B001E4KFG0. This product has received a review score of 5 and the reviewer found it to be of good quality, with a stew-like appearance and better smell than processed meat. The reviewer's Labrador, who is finicky, also appreciated this product more than most. We believe this product would be a great fit for your needs.\n"
     ]
    }
   ],
   "source": [
    "client = openai.AzureOpenAI(\n",
    "  azure_endpoint=openai.api_base, \n",
    "  api_key=openai.api_key,  \n",
    "  api_version=openai.api_version\n",
    ")\n",
    "response = client.chat.completions.create( \n",
    "    model=openai_deployment_completion,\n",
    "    messages = [{'role':'system','content':prompt}],\n",
    "    temperature=0, \n",
    "    max_tokens=600,\n",
    "    stream=False)\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to use the index in Azure OpenAI Studio Chat Playground\n",
    "The index created in this notebook can be easily integrated in Azure OpenAI studio for further testing and deployment. Learn more: [Add your data using Azure OpenAI Studio](https://learn.microsoft.com/en-us/azure/ai-services/openai/use-your-data-quickstart?tabs=command-line%2Cpython-new&pivots=programming-language-studio)\n",
    "\n",
    "1. In [Azure OpenAI Studio](https://oai.azure.com/), open Chat and make sure your completion model, such as the one used for the `openai_deployment_completion` model deployment. \n",
    "1. Select **Add your data** and set fields to:\n",
    "    - Select your Azure AI Search as data source\n",
    "    - Select deployed resource and index named `sql-customer-index`\n",
    "    - Check `Add vector search...`\n",
    "1. Select next and in Data management and set fields:\n",
    "    - Search type `Vector`\n",
    "1. Select next and select `API`\n",
    "1. Select **Review** and **Finish** > **Save** and **Close**\n",
    "1. Add the system message and save the changes:\n",
    "\n",
    "```\n",
    "The user is searching for a product matching their query. Tell the user that after searching through our product database, you recommend the product described in the provided product review. Your answer should summarize the review text, include the product ID, and mention the score given in the review. Present the answer in a readable format that clearly lists the products.\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notes\n",
    "\n",
    "After finishing the sample, remember to delete unneeded resources:\n",
    "* Table created within existing SQL DB\n",
    "* Within the Search Service resource:\n",
    "  * Data source connection\n",
    "  * Index\n",
    "  * Skillset\n",
    "  * Indexer\n",
    "\n",
    "These can always be recreated by rerunning the notebook."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}