02-LoadCSVOneToMany-ACogSearch.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4077b4ee-73ea-4155-afbd-1de66dd6b650",
   "metadata": {},
   "source": [
    "# Load CSVs (one-to-many) to Azure AI Search\n",
    "\n",
    "In this Jupyter Notebook, we create and run steps to index a CSV file in which each row is an individual and independent record/document. Each row then becomes searchable in Azure AI Search. \n",
    "The reference documentation can be found at [Indexing blobs and files to produce multiple search documents](https://learn.microsoft.com/en-us/azure/search/search-howto-index-one-to-many-blobs).\n",
    "\n",
    "By default, an indexer will treat the contents of a blob or file as a single search document. If you want a more granular representation in a search index, you can set `parsingMode` values to create multiple search documents from one blob or file.\n",
    "\n",
    "We are going to be using the same private Blob Storage account but a different container that has abstracts of 90k Medical publications about COVID-19 published in 2020-2022. This file is a subset of a much bigger dataset (770k articles) called CORD19 [HERE](https://github.com/allenai/cord19)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c088c844-1e71-4279-a8fe-a77a007c15c4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import requests\n",
    "import pandas as pd\n",
    "import shutil\n",
    "\n",
    "from common.utils import upload_file_to_blob, extract_zip_file, upload_directory_to_blob\n",
    "\n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv(\"credentials.env\")\n",
    "\n",
    "# Name of the container in your Blob Storage Datasource ( in credentials.env)\n",
    "BLOB_CONTAINER_NAME = \"cord19\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c4908539-1d17-46a3-b9e0-dcc46a210c4f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Define the names for the data source, index and indexer\n",
    "datasource_name = \"srch-datasource-csv\"\n",
    "skillset_name = \"srch-skillset-csv\"\n",
    "index_name = \"srch-index-csv\"\n",
    "indexer_name = \"srch-indexer-csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f2434379-070e-4110-8f5a-7d5bda9a0b7c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Setup the Payloads header\n",
    "headers = {'Content-Type': 'application/json','api-key': os.environ['AZURE_SEARCH_KEY']}\n",
    "params = {'api-version': os.environ['AZURE_SEARCH_API_VERSION']}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ee479f1-7489-4b8e-8a5c-79fd80d44d3d",
   "metadata": {},
   "source": [
    "## Upload local dataset to Blob Container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c360f6bc-cc74-4756-b56a-c5009f6dd02a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Extracting ./data/cord19mini.zip ... \n",
      "Extracted ./data/cord19mini.zip to ./data/temp_extract\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Uploading Files: 100%|████████████████████████████████████████████████| 1/1 [00:06<00:00,  6.23s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Temp Folder: ./data/temp_extract removed\n",
      "CPU times: user 767 ms, sys: 305 ms, total: 1.07 s\n",
      "Wall time: 7.74 s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Define connection string and other parameters\n",
    "BLOB_CONTAINER_NAME = \"cord19\"\n",
    "BLOB_NAME = \"cord19mini.zip\"\n",
    "LOCAL_FILE_PATH = \"./data/\" + BLOB_NAME  # Path to the local file you want to upload\n",
    "upload_directory = \"./data/temp_extract\"  # Temporary directory to extract the zip file\n",
    "\n",
    "# # Extract the zip file\n",
    "extract_zip_file(LOCAL_FILE_PATH, upload_directory)\n",
    "\n",
    "# Upload the extracted files and folder structure\n",
    "upload_directory_to_blob(upload_directory, BLOB_CONTAINER_NAME)\n",
    "\n",
    "# Clean up: Optionally, you can remove the temp folder after uploading\n",
    "shutil.rmtree(upload_directory)\n",
    "print(f\"Temp Folder: {upload_directory} removed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecad0e75-e3c8-4147-b8c6-b938435bc8f5",
   "metadata": {},
   "source": [
    "## Create Data Source (Blob container with the CORD19 CSV data file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a9fa6c09-a489-4b6d-8c93-5fc26bae63a0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# Create a data source\n",
    "\n",
    "datasource_payload = {\n",
    "    \"name\": datasource_name,\n",
    "    \"description\": \"Demo files to demonstrate cognitive search capabilities of one-to-many.\",\n",
    "    \"type\": \"azureblob\",\n",
    "    \"credentials\": {\n",
    "        \"connectionString\": os.environ['BLOB_CONNECTION_STRING']\n",
    "    },\n",
    "    \"container\": {\n",
    "        \"name\": BLOB_CONTAINER_NAME\n",
    "    }\n",
    "}\n",
    "r = requests.put(os.environ['AZURE_SEARCH_ENDPOINT'] + \"/datasources/\" + datasource_name,\n",
    "                 data=json.dumps(datasource_payload), headers=headers, params=params)\n",
    "print(r.status_code)\n",
    "print(r.ok)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86b7ff86-19fc-48d3-88d1-b098e8d01302",
   "metadata": {},
   "source": [
    "## Inspect CSV file so we can understand the column types before creating the Index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cf6879a-a3da-4e54-97ed-f4122325abb1",
   "metadata": {},
   "source": [
    "In our private dataset we have place a smaller version of the original the metadata.csv file in the cord19 dataset. \n",
    "Let's see what the file looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "097c7eb4-71e9-46fd-b26a-558da0e2f48d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No. of lines: 90000\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "</style>\n",
       "<table id=\"T_e5366\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th id=\"T_e5366_level0_col0\" class=\"col_heading level0 col0\" >cord_uid</th>\n",
       "      <th id=\"T_e5366_level0_col1\" class=\"col_heading level0 col1\" >source_x</th>\n",
       "      <th id=\"T_e5366_level0_col2\" class=\"col_heading level0 col2\" >title</th>\n",
       "      <th id=\"T_e5366_level0_col3\" class=\"col_heading level0 col3\" >abstract</th>\n",
       "      <th id=\"T_e5366_level0_col4\" class=\"col_heading level0 col4\" >authors</th>\n",
       "      <th id=\"T_e5366_level0_col5\" class=\"col_heading level0 col5\" >url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_e5366_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_e5366_row0_col0\" class=\"data row0 col0\" >ug7v899j</td>\n",
       "      <td id=\"T_e5366_row0_col1\" class=\"data row0 col1\" >PMC</td>\n",
       "      <td id=\"T_e5366_row0_col2\" class=\"data row0 col2\" >Clinical features of culture-p...</td>\n",
       "      <td id=\"T_e5366_row0_col3\" class=\"data row0 col3\" >OBJECTIVE: This retrospective ...</td>\n",
       "      <td id=\"T_e5366_row0_col4\" class=\"data row0 col4\" >Madani, Tariq A; Al-Ghamdi, Ai...</td>\n",
       "      <td id=\"T_e5366_row0_col5\" class=\"data row0 col5\" ><a href=\"https://www.ncbi.nlm.nih.gov/pmc/articles/PMC35282/\">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC35282/</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e5366_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_e5366_row1_col0\" class=\"data row1 col0\" >02tnwd4m</td>\n",
       "      <td id=\"T_e5366_row1_col1\" class=\"data row1 col1\" >PMC</td>\n",
       "      <td id=\"T_e5366_row1_col2\" class=\"data row1 col2\" >Nitric oxide: a pro-inflammato...</td>\n",
       "      <td id=\"T_e5366_row1_col3\" class=\"data row1 col3\" >Inflammatory diseases of the r...</td>\n",
       "      <td id=\"T_e5366_row1_col4\" class=\"data row1 col4\" >Vliet, Albert van der; Eiseric...</td>\n",
       "      <td id=\"T_e5366_row1_col5\" class=\"data row1 col5\" ><a href=\"https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59543/\">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59543/</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e5366_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_e5366_row2_col0\" class=\"data row2 col0\" >ejv2xln0</td>\n",
       "      <td id=\"T_e5366_row2_col1\" class=\"data row2 col1\" >PMC</td>\n",
       "      <td id=\"T_e5366_row2_col2\" class=\"data row2 col2\" >Surfactant protein-D and pulmo...</td>\n",
       "      <td id=\"T_e5366_row2_col3\" class=\"data row2 col3\" >Surfactant protein-D (SP-D) pa...</td>\n",
       "      <td id=\"T_e5366_row2_col4\" class=\"data row2 col4\" >Crouch, Erika C...</td>\n",
       "      <td id=\"T_e5366_row2_col5\" class=\"data row2 col5\" ><a href=\"https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59549/\">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59549/</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e5366_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_e5366_row3_col0\" class=\"data row3 col0\" >2b73a28n</td>\n",
       "      <td id=\"T_e5366_row3_col1\" class=\"data row3 col1\" >PMC</td>\n",
       "      <td id=\"T_e5366_row3_col2\" class=\"data row3 col2\" >Role of endothelin-1 in lung d...</td>\n",
       "      <td id=\"T_e5366_row3_col3\" class=\"data row3 col3\" >Endothelin-1 (ET-1) is a 21 am...</td>\n",
       "      <td id=\"T_e5366_row3_col4\" class=\"data row3 col4\" >Fagan, Karen A; McMurtry, Ivan...</td>\n",
       "      <td id=\"T_e5366_row3_col5\" class=\"data row3 col5\" ><a href=\"https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59574/\">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59574/</a></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e5366_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_e5366_row4_col0\" class=\"data row4 col0\" >9785vg6d</td>\n",
       "      <td id=\"T_e5366_row4_col1\" class=\"data row4 col1\" >PMC</td>\n",
       "      <td id=\"T_e5366_row4_col2\" class=\"data row4 col2\" >Gene expression in epithelial ...</td>\n",
       "      <td id=\"T_e5366_row4_col3\" class=\"data row4 col3\" >Respiratory syncytial virus (R...</td>\n",
       "      <td id=\"T_e5366_row4_col4\" class=\"data row4 col4\" >Domachowske, Joseph B; Bonvill...</td>\n",
       "      <td id=\"T_e5366_row4_col5\" class=\"data row4 col5\" ><a href=\"https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59580/\">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC59580/</a></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f09e044b170>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df = pd.read_csv(LOCAL_FILE_PATH,compression='zip')\n",
    "print(\"No. of lines:\",df.shape[0])\n",
    "\n",
    "simple_schema = ['cord_uid', 'source_x', 'title', 'abstract', 'authors', 'url']\n",
    "\n",
    "def make_clickable(address):\n",
    "    '''Make the url clickable'''\n",
    "    return '<a href=\"{0}\">{0}</a>'.format(address)\n",
    "\n",
    "def preview(text):\n",
    "    '''Show only a preview of the text data.'''\n",
    "    return text[:30] + '...'\n",
    "\n",
    "format_ = {'title': preview, 'abstract': preview, 'authors': preview, 'url': make_clickable}\n",
    "\n",
    "df[simple_schema].head().style.format(format_)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a321916-cd14-4d34-837d-1d153edb1221",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Create the Index\n",
    "In Azure AI Search, both blob indexers and file indexers support a delimitedText parsing mode for CSV files that treats each line in the CSV as a separate search document.\n",
    "\n",
    "### **Important**:\n",
    "As you can see below and from the prior Notebook, there are 6 mandatory fields in the schema: `id, title, name, location, chunk, chunkVector`. These fields must exist in any index that you create regardles of the datasource. Any additional fields are good to add so the engine can search relevant documents, however the mandatory fields must exist for all the code downstream work with no issues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "74913764-9dfb-4646-aac8-d389cd4533e6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# Create an index\n",
    "index_payload = {\n",
    "    \"name\": index_name,\n",
    "    \"vectorSearch\": {\n",
    "        \"algorithms\": [\n",
    "            {\n",
    "                \"name\": \"use-hnsw\",\n",
    "                \"kind\": \"hnsw\"\n",
    "            }\n",
    "        ],\n",
    "        \"compressions\": [ # Compression (optional)\n",
    "            {\n",
    "                \"name\": \"use-scalar\",\n",
    "                \"kind\": \"scalarQuantization\",\n",
    "                \"rescoringOptions\": {\n",
    "                    \"enableRescoring\": \"true\",\n",
    "                    \"defaultOversampling\": 10,\n",
    "                    \"rescoreStorageMethod\": \"preserveOriginals\"\n",
    "                },\n",
    "                \"scalarQuantizationParameters\": {\n",
    "                    \"quantizedDataType\": \"int8\"\n",
    "                },\n",
    "                \"truncationDimension\": 1024\n",
    "            },\n",
    "            {\n",
    "                \"name\": \"use-binary\",\n",
    "                \"kind\": \"binaryQuantization\",\n",
    "                \"rescoringOptions\": {\n",
    "                    \"enableRescoring\": \"true\",\n",
    "                    \"defaultOversampling\": 10,\n",
    "                    \"rescoreStorageMethod\": \"preserveOriginals\"\n",
    "                },\n",
    "                \"truncationDimension\": 1024\n",
    "            }\n",
    "        ],\n",
    "        \"vectorizers\": [\n",
    "            {\n",
    "                \"name\": \"use-openai\",\n",
    "                \"kind\": \"azureOpenAI\",\n",
    "                \"azureOpenAIParameters\":\n",
    "                {\n",
    "                    \"resourceUri\" : os.environ['AZURE_OPENAI_ENDPOINT'],\n",
    "                    \"apiKey\" : os.environ['AZURE_OPENAI_API_KEY'],\n",
    "                    \"deploymentId\" : os.environ['EMBEDDING_DEPLOYMENT_NAME'],\n",
    "                    \"modelName\" : os.environ['EMBEDDING_DEPLOYMENT_NAME'],\n",
    "                }\n",
    "            }\n",
    "        ],\n",
    "        \"profiles\": [\n",
    "            {\n",
    "                \"name\": \"vector-profile-hnsw-binary\",\n",
    "                \"algorithm\": \"use-hnsw\",\n",
    "                \"compression\": \"use-binary\", # Compression (optional)\n",
    "                \"vectorizer\":\"use-openai\"\n",
    "            }\n",
    "        ]\n",
    "    },\n",
    "    \"semantic\": {\n",
    "        \"configurations\": [\n",
    "            {\n",
    "                \"name\": \"my-semantic-config\",\n",
    "                \"prioritizedFields\": {\n",
    "                    \"titleField\": {\n",
    "                        \"fieldName\": \"title\"\n",
    "                    },\n",
    "                    \"prioritizedContentFields\": [\n",
    "                        {\n",
    "                            \"fieldName\": \"chunk\"\n",
    "                        }\n",
    "                    ],\n",
    "                    \"prioritizedKeywordsFields\": []\n",
    "                }\n",
    "            }\n",
    "        ]\n",
    "    },\n",
    "    \"fields\": [\n",
    "        {\"name\": \"id\", \"type\": \"Edm.String\", \"key\": \"true\", \"analyzer\": \"keyword\", \"searchable\": \"true\", \"retrievable\": \"true\", \"sortable\": \"false\", \"filterable\": \"false\",\"facetable\": \"false\"},\n",
    "        {\"name\": \"ParentKey\", \"type\": \"Edm.String\", \"searchable\": \"true\", \"retrievable\": \"true\", \"facetable\": \"false\", \"filterable\": \"true\", \"sortable\": \"false\"},\n",
    "        {\"name\": \"title\", \"type\": \"Edm.String\", \"searchable\": \"true\", \"retrievable\": \"true\", \"facetable\": \"false\", \"filterable\": \"true\", \"sortable\": \"false\"},\n",
    "        {\"name\": \"name\", \"type\": \"Edm.String\", \"searchable\": \"true\", \"retrievable\": \"true\", \"sortable\": \"false\", \"filterable\": \"false\", \"facetable\": \"false\"},\n",
    "        {\"name\": \"location\", \"type\": \"Edm.String\", \"searchable\": \"true\", \"retrievable\": \"true\", \"sortable\": \"false\", \"filterable\": \"false\", \"facetable\": \"false\"},   \n",
    "        {\"name\": \"chunk\",\"type\": \"Edm.String\", \"searchable\": \"true\", \"retrievable\": \"true\", \"sortable\": \"false\", \"filterable\": \"false\", \"facetable\": \"false\"},\n",
    "        {\n",
    "            \"name\": \"chunkVector\",\n",
    "            \"type\": \"Collection(Edm.Half)\", # Compression (optional)\n",
    "            \"dimensions\": 3072,   # IMPORTANT: Make sure these dimmensions match your embedding model name\n",
    "            \"vectorSearchProfile\": \"vector-profile-hnsw-binary\",\n",
    "            \"searchable\": \"true\",\n",
    "            \"retrievable\": \"false\",\n",
    "            \"filterable\": \"false\",\n",
    "            \"sortable\": \"false\",\n",
    "            \"facetable\": \"false\",\n",
    "            \"stored\": \"false\" # Compression (optional)\n",
    "        }\n",
    "    ]\n",
    "}\n",
    "\n",
    "r = requests.put(os.environ['AZURE_SEARCH_ENDPOINT'] + \"/indexes/\" + index_name,\n",
    "                 data=json.dumps(index_payload), headers=headers, params=params)\n",
    "print(r.status_code)\n",
    "print(r.ok)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0b3935d-8546-4756-95cd-7f4fcecb9836",
   "metadata": {},
   "source": [
    "## Create Skillset - Text Splitter, Language Detection\n",
    "We don't need to use the OCR and Merge skill, since we know for sure that this is only text parsing. We will only use the SplitSkill and the AzureOpenAIEmbeddingSkill."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b46cfa90-28b4-4602-b6ff-743a3407fd72",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# Create a skillset\n",
    "skillset_payload = {\n",
    "    \"name\": skillset_name,\n",
    "    \"description\": \"e2e Skillset for RAG - One to Many\",\n",
    "    \"skills\":\n",
    "    [\n",
    "        {\n",
    "            \"@odata.type\": \"#Microsoft.Skills.Text.SplitSkill\",\n",
    "            \"context\": \"/document\",\n",
    "            \"textSplitMode\": \"pages\",\n",
    "            \"maximumPageLength\": 5000, # 5000 is default\n",
    "            \"defaultLanguageCode\": \"en\",\n",
    "            \"inputs\": [\n",
    "                {\n",
    "                    \"name\": \"text\",\n",
    "                    \"source\": \"/document/abstract\"\n",
    "                }\n",
    "            ],\n",
    "            \"outputs\": [\n",
    "                {\n",
    "                    \"name\": \"textItems\",\n",
    "                    \"targetName\": \"chunks\"\n",
    "                }\n",
    "            ]\n",
    "        },\n",
    "        {\n",
    "            \"@odata.type\": \"#Microsoft.Skills.Text.AzureOpenAIEmbeddingSkill\",\n",
    "            \"description\": \"Azure OpenAI Embedding Skill\",\n",
    "            \"context\": \"/document/chunks/*\",\n",
    "            \"resourceUri\": os.environ['AZURE_OPENAI_ENDPOINT'],\n",
    "            \"apiKey\": os.environ['AZURE_OPENAI_API_KEY'],\n",
    "            \"deploymentId\": os.environ['EMBEDDING_DEPLOYMENT_NAME'],\n",
    "            \"modelName\" : os.environ['EMBEDDING_DEPLOYMENT_NAME'],\n",
    "            \"inputs\": [\n",
    "                {\n",
    "                    \"name\": \"text\",\n",
    "                    \"source\": \"/document/chunks/*\"\n",
    "                }\n",
    "            ],\n",
    "            \"outputs\": [\n",
    "                {\n",
    "                    \"name\": \"embedding\",\n",
    "                    \"targetName\": \"vector\"\n",
    "                }\n",
    "            ]\n",
    "        }\n",
    "    ],\n",
    "    \"indexProjections\": {\n",
    "        \"selectors\": [\n",
    "            {\n",
    "                \"targetIndexName\": index_name,\n",
    "                \"parentKeyFieldName\": \"ParentKey\",\n",
    "                \"sourceContext\": \"/document/chunks/*\",\n",
    "                \"mappings\": [\n",
    "                    {\n",
    "                        \"name\": \"title\",\n",
    "                        \"source\": \"/document/title\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"name\": \"name\",\n",
    "                        \"source\": \"/document/name\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"name\": \"location\",\n",
    "                        \"source\": \"/document/location\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"name\": \"chunk\",\n",
    "                        \"source\": \"/document/chunks/*\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"name\": \"chunkVector\",\n",
    "                        \"source\": \"/document/chunks/*/vector\"\n",
    "                    }\n",
    "                ]\n",
    "            }\n",
    "        ],\n",
    "        \"parameters\": {\n",
    "            \"projectionMode\": \"skipIndexingParentDocuments\"\n",
    "        }\n",
    "    },\n",
    "    \"cognitiveServices\": {\n",
    "        \"@odata.type\": \"#Microsoft.Azure.Search.CognitiveServicesByKey\",\n",
    "        \"description\": os.environ['COG_SERVICES_NAME'],\n",
    "        \"key\": os.environ['COG_SERVICES_KEY']\n",
    "    }\n",
    "}\n",
    "\n",
    "r = requests.put(os.environ['AZURE_SEARCH_ENDPOINT'] + \"/skillsets/\" + skillset_name,\n",
    "                 data=json.dumps(skillset_payload), headers=headers, params=params)\n",
    "print(r.status_code)\n",
    "print(r.ok)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51849738-6f66-452a-b7df-d34afd11f943",
   "metadata": {},
   "source": [
    "## Create and Run the Indexer - (runs the pipeline)\n",
    "To create one-to-many indexers with CSV blobs, create or update an indexer definition with the delimitedText parsing mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b87b8ebd-8091-43b6-9124-cc17021cfb78",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "201\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "indexer_payload = {\n",
    "    \"name\": indexer_name,\n",
    "    \"dataSourceName\": datasource_name,\n",
    "    \"targetIndexName\": index_name,\n",
    "    \"skillsetName\": skillset_name,\n",
    "    \"schedule\" : { \"interval\" : \"PT30M\"}, # How often do you want to check for new content in the data source\n",
    "    \"fieldMappings\": [\n",
    "        {\n",
    "          \"sourceFieldName\" : \"metadata_storage_name\",\n",
    "          \"targetFieldName\" : \"name\"\n",
    "        },\n",
    "        {\n",
    "          \"sourceFieldName\" : \"url\",\n",
    "          \"targetFieldName\" : \"location\"\n",
    "        }\n",
    "    ],\n",
    "    \"outputFieldMappings\":\n",
    "    [],\n",
    "    \"parameters\" : { \n",
    "        \"configuration\" : { \n",
    "            \"dataToExtract\": \"contentAndMetadata\",\n",
    "            \"parsingMode\" : \"delimitedText\", \n",
    "            \"firstLineContainsHeaders\" : True,\n",
    "            \"delimitedTextDelimiter\": \",\"\n",
    "        } \n",
    "    }\n",
    "}\n",
    "r = requests.put(os.environ['AZURE_SEARCH_ENDPOINT'] + \"/indexers/\" + indexer_name,\n",
    "                 data=json.dumps(indexer_payload), headers=headers, params=params)\n",
    "print(r.status_code)\n",
    "print(r.ok)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6132c041-7213-410e-a206-1a8c7385128e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200\n",
      "Status: success\n",
      "Items Processed: 0\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# Optionally, get indexer status to confirm that it's running\n",
    "try:\n",
    "    r = requests.get(os.environ['AZURE_SEARCH_ENDPOINT'] + \"/indexers/\" + indexer_name +\n",
    "                     \"/status\", headers=headers, params=params)\n",
    "    # pprint(json.dumps(r.json(), indent=1))\n",
    "    print(r.status_code)\n",
    "    print(\"Status:\",r.json().get('lastResult').get('status'))\n",
    "    print(\"Items Processed:\",r.json().get('lastResult').get('itemsProcessed'))\n",
    "    print(r.ok)\n",
    "    \n",
    "except Exception as e:\n",
    "    print(\"Wait a few seconds until the process starts and run this cell again.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2152806f-245c-45db-93c6-c19c0569d73a",
   "metadata": {},
   "source": [
    "**When the indexer finishes running (this might some time, depending how much capacity TPM your model has) we will have all 90,000 rows indexed properly as separate documents in our Search Engine!.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eed6f22-437f-4a49-9b67-5fa2e7d066bf",
   "metadata": {},
   "source": [
    "# Reference\n",
    "\n",
    "- https://learn.microsoft.com/en-us/azure/search/search-howto-index-csv-blobs\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d9f82a9-cb4c-44b9-b125-bc124ea23aa8",
   "metadata": {},
   "source": [
    "# NEXT\n",
    "Now that we have two separate indexes loaded with two different types of information, In the next notebook 3, we will do a Multi-Index query, sort the results based on the reranker semantic score of Azure AI Search, and then use OpenAI to understand the results and give the best answer possible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7505d8f9-39c7-4b87-a85f-283b6fea3de0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "GPTSearch3 (Python 3.12)",
   "language": "python",
   "name": "gptsearch3"
  },
  "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.12.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}