demo/sql_demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vector Search with IRIS SQL\n",
    "This tutorial covers how to use IRIS as a vector database. \n",
    "\n",
    "For this tutorial, we will use a dataset of 2.2k online reviews of scotch (\n",
    "dataset from https://www.kaggle.com/datasets/koki25ando/22000-scotch-whisky-reviews) . With our latest vector database functionality, we can leverage the latest embedding models to run semantic search on the online reviews of scotch whiskeys. In addition, we'll be able to apply filters on columns with structured data. For example, we will be able to search for whiskeys that are priced under $100, and are 'earthy, smooth, and easy to drink'. Let's find our perfect whiskey!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/envs/iris-vector-search/lib/python3.10/site-packages/sentence_transformers/cross_encoder/CrossEncoder.py:13: TqdmExperimentalWarning: Using `tqdm.autonotebook.tqdm` in notebook mode. Use `tqdm.tqdm` instead to force console mode (e.g. in jupyter console)\n",
      "  from tqdm.autonotebook import tqdm, trange\n"
     ]
    }
   ],
   "source": [
    "import os, pandas as pd\n",
    "from sentence_transformers import SentenceTransformer\n",
    "from sqlalchemy import create_engine, text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "username = 'demo'\n",
    "password = 'demo'\n",
    "hostname = os.getenv('IRIS_HOSTNAME', 'localhost')\n",
    "port = '1972' \n",
    "namespace = 'USER'\n",
    "CONNECTION_STRING = f\"iris://{username}:{password}@{hostname}:{port}/{namespace}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(CONNECTION_STRING)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the CSV file\n",
    "df = pd.read_csv('../data/scotch_review.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Unnamed: 0</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>review.point</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Johnnie Walker Blue Label, 40%</td>\n",
       "      <td>Blended Scotch Whisky</td>\n",
       "      <td>97</td>\n",
       "      <td>225</td>\n",
       "      <td>$</td>\n",
       "      <td>Magnificently powerful and intense. Caramels, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Black Bowmore, 1964 vintage, 42 year old, 40.5%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>4500.00</td>\n",
       "      <td>$</td>\n",
       "      <td>What impresses me most is how this whisky evol...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Bowmore 46 year old (distilled 1964), 42.9%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>13500.00</td>\n",
       "      <td>$</td>\n",
       "      <td>There have been some legendary Bowmores from t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Compass Box The General, 53.4%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>325</td>\n",
       "      <td>$</td>\n",
       "      <td>With a name inspired by a 1926 Buster Keaton m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Chivas Regal Ultis, 40%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>160</td>\n",
       "      <td>$</td>\n",
       "      <td>Captivating, enticing, and wonderfully charmin...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                             name  \\\n",
       "0           1                   Johnnie Walker Blue Label, 40%   \n",
       "1           2  Black Bowmore, 1964 vintage, 42 year old, 40.5%   \n",
       "2           3      Bowmore 46 year old (distilled 1964), 42.9%   \n",
       "3           4                   Compass Box The General, 53.4%   \n",
       "4           5                          Chivas Regal Ultis, 40%   \n",
       "\n",
       "                     category  review.point     price currency  \\\n",
       "0       Blended Scotch Whisky            97       225        $   \n",
       "1          Single Malt Scotch            97   4500.00        $   \n",
       "2          Single Malt Scotch            97  13500.00        $   \n",
       "3  Blended Malt Scotch Whisky            96       325        $   \n",
       "4  Blended Malt Scotch Whisky            96       160        $   \n",
       "\n",
       "                                         description  \n",
       "0  Magnificently powerful and intense. Caramels, ...  \n",
       "1  What impresses me most is how this whisky evol...  \n",
       "2  There have been some legendary Bowmores from t...  \n",
       "3  With a name inspired by a 1926 Buster Keaton m...  \n",
       "4  Captivating, enticing, and wonderfully charmin...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean data\n",
    "# Remove the specified columns\n",
    "df.drop(['currency'], axis=1, inplace=True)\n",
    "\n",
    "# Drop the first column\n",
    "df.drop(columns=df.columns[0], inplace=True)\n",
    "\n",
    "# Remove rows without a price\n",
    "df.dropna(subset=['price'], inplace=True)\n",
    "\n",
    "# Ensure values in 'price' are numbers\n",
    "df = df[pd.to_numeric(df['price'], errors='coerce').notna()]\n",
    "\n",
    "# Replace NaN values in other columns with an empty string\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>name</th>\n",
       "      <th>category</th>\n",
       "      <th>review.point</th>\n",
       "      <th>price</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Johnnie Walker Blue Label, 40%</td>\n",
       "      <td>Blended Scotch Whisky</td>\n",
       "      <td>97</td>\n",
       "      <td>225</td>\n",
       "      <td>Magnificently powerful and intense. Caramels, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Black Bowmore, 1964 vintage, 42 year old, 40.5%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>4500.00</td>\n",
       "      <td>What impresses me most is how this whisky evol...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bowmore 46 year old (distilled 1964), 42.9%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>13500.00</td>\n",
       "      <td>There have been some legendary Bowmores from t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Compass Box The General, 53.4%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>325</td>\n",
       "      <td>With a name inspired by a 1926 Buster Keaton m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chivas Regal Ultis, 40%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>160</td>\n",
       "      <td>Captivating, enticing, and wonderfully charmin...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              name  \\\n",
       "0                   Johnnie Walker Blue Label, 40%   \n",
       "1  Black Bowmore, 1964 vintage, 42 year old, 40.5%   \n",
       "2      Bowmore 46 year old (distilled 1964), 42.9%   \n",
       "3                   Compass Box The General, 53.4%   \n",
       "4                          Chivas Regal Ultis, 40%   \n",
       "\n",
       "                     category  review.point     price  \\\n",
       "0       Blended Scotch Whisky            97       225   \n",
       "1          Single Malt Scotch            97   4500.00   \n",
       "2          Single Malt Scotch            97  13500.00   \n",
       "3  Blended Malt Scotch Whisky            96       325   \n",
       "4  Blended Malt Scotch Whisky            96       160   \n",
       "\n",
       "                                         description  \n",
       "0  Magnificently powerful and intense. Caramels, ...  \n",
       "1  What impresses me most is how this whisky evol...  \n",
       "2  There have been some legendary Bowmores from t...  \n",
       "3  With a name inspired by a 1926 Buster Keaton m...  \n",
       "4  Captivating, enticing, and wonderfully charmin...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, InterSystems IRIS supports vectors as a datatype in tables! Here, we create a table with a few different columns. The last column, 'description_vector', will be used to store vectors that are generated by passing the 'description' of a review through an embedding model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "with engine.connect() as conn:\n",
    "    with conn.begin():# Load \n",
    "        sql = f\"\"\"\n",
    "                CREATE TABLE IF NOT EXISTS scotch_reviews (\n",
    "        name VARCHAR(255),\n",
    "        category VARCHAR(255),\n",
    "        review_point INT,\n",
    "        price DOUBLE,\n",
    "        description VARCHAR(2000),\n",
    "        description_vector VECTOR(DOUBLE, 384)\n",
    "        )\n",
    "                \"\"\"\n",
    "        result = conn.execute(text(sql))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load a pre-trained sentence transformer model. This model's output vectors are of size 384\n",
    "model = SentenceTransformer('all-MiniLM-L6-v2') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Generate embeddings for all descriptions at once. Batch processing makes it faster\n",
    "embeddings = model.encode(df['description'].tolist(), normalize_embeddings=True)\n",
    "\n",
    "# Add the embeddings to the DataFrame\n",
    "df['description_vector'] = embeddings.tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>name</th>\n",
       "      <th>category</th>\n",
       "      <th>review.point</th>\n",
       "      <th>price</th>\n",
       "      <th>description</th>\n",
       "      <th>description_vector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Johnnie Walker Blue Label, 40%</td>\n",
       "      <td>Blended Scotch Whisky</td>\n",
       "      <td>97</td>\n",
       "      <td>225</td>\n",
       "      <td>Magnificently powerful and intense. Caramels, ...</td>\n",
       "      <td>[-0.010494445450603962, 0.014728965237736702, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Black Bowmore, 1964 vintage, 42 year old, 40.5%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>4500.00</td>\n",
       "      <td>What impresses me most is how this whisky evol...</td>\n",
       "      <td>[0.02318125031888485, -0.05123035982251167, 0....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bowmore 46 year old (distilled 1964), 42.9%</td>\n",
       "      <td>Single Malt Scotch</td>\n",
       "      <td>97</td>\n",
       "      <td>13500.00</td>\n",
       "      <td>There have been some legendary Bowmores from t...</td>\n",
       "      <td>[0.04333321005105972, -0.017066635191440582, -...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Compass Box The General, 53.4%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>325</td>\n",
       "      <td>With a name inspired by a 1926 Buster Keaton m...</td>\n",
       "      <td>[-0.07594005018472672, -0.03676239028573036, 0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chivas Regal Ultis, 40%</td>\n",
       "      <td>Blended Malt Scotch Whisky</td>\n",
       "      <td>96</td>\n",
       "      <td>160</td>\n",
       "      <td>Captivating, enticing, and wonderfully charmin...</td>\n",
       "      <td>[-0.012818857096135616, -0.09769789129495621, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              name  \\\n",
       "0                   Johnnie Walker Blue Label, 40%   \n",
       "1  Black Bowmore, 1964 vintage, 42 year old, 40.5%   \n",
       "2      Bowmore 46 year old (distilled 1964), 42.9%   \n",
       "3                   Compass Box The General, 53.4%   \n",
       "4                          Chivas Regal Ultis, 40%   \n",
       "\n",
       "                     category  review.point     price  \\\n",
       "0       Blended Scotch Whisky            97       225   \n",
       "1          Single Malt Scotch            97   4500.00   \n",
       "2          Single Malt Scotch            97  13500.00   \n",
       "3  Blended Malt Scotch Whisky            96       325   \n",
       "4  Blended Malt Scotch Whisky            96       160   \n",
       "\n",
       "                                         description  \\\n",
       "0  Magnificently powerful and intense. Caramels, ...   \n",
       "1  What impresses me most is how this whisky evol...   \n",
       "2  There have been some legendary Bowmores from t...   \n",
       "3  With a name inspired by a 1926 Buster Keaton m...   \n",
       "4  Captivating, enticing, and wonderfully charmin...   \n",
       "\n",
       "                                  description_vector  \n",
       "0  [-0.010494445450603962, 0.014728965237736702, ...  \n",
       "1  [0.02318125031888485, -0.05123035982251167, 0....  \n",
       "2  [0.04333321005105972, -0.017066635191440582, -...  \n",
       "3  [-0.07594005018472672, -0.03676239028573036, 0...  \n",
       "4  [-0.012818857096135616, -0.09769789129495621, ...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    with conn.begin():\n",
    "        for index, row in df.iterrows():\n",
    "            sql = text(\"\"\"\n",
    "                INSERT INTO scotch_reviews \n",
    "                (name, category, review_point, price, description, description_vector) \n",
    "                VALUES (:name, :category, :review_point, :price, :description, TO_VECTOR(:description_vector))\n",
    "            \"\"\")\n",
    "            conn.execute(sql, {\n",
    "                'name': row['name'], \n",
    "                'category': row['category'], \n",
    "                'review_point': row['review.point'], \n",
    "                'price': row['price'], \n",
    "                'description': row['description'], \n",
    "                'description_vector': str(row['description_vector'])\n",
    "            })\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look for a scotch that costs less than $100, and has an earthy and creamy taste."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "description_search = \"earthy and creamy taste\"\n",
    "search_vector = model.encode(description_search, normalize_embeddings=True).tolist() # Convert search phrase into a vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    with conn.begin():\n",
    "        sql = text(\"\"\"\n",
    "            SELECT DISTINCT TOP 3 * FROM scotch_reviews \n",
    "            WHERE price < 100 \n",
    "            ORDER BY VECTOR_DOT_PRODUCT(description_vector, TO_VECTOR(:search_vector)) DESC\n",
    "        \"\"\")\n",
    "\n",
    "        results = conn.execute(sql, {'search_vector': str(search_vector)}).fetchall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('SIGNATORY (DISTILLED AT BOWMORE), 16 YEAR OLD, 1988 VINTAGE, CASK #42508, 46%', 'SINGLE MALT SCOTCH', 87, 60.0, 'MEDIUM-BODIED AND NICELY TEXTURED. GOOD BALANCE OF FLAVORS -- AND WELL-INTEGRATED, TOO -- WITH LOVELY SWEET NOTES (CEREAL GRAIN, COOKIE DOUGH, CARAME ... (48 characters truncated) ...  FISHNETS, AND BRINE THAT IS COMPLEMENTARY, BUT NOT AGGRESSIVE, WITH A SUGGESTION OF LAVENDER AND TANGERINE. BALANCED FINISH. (332 BOTTLES PRODUCED.)', '-.048620376735925674438,-.082065843045711517333,.039660684764385223388,-.018970852717757225036,-.017485298216342926026,.042453121393918991088,.046325 ... (8848 characters truncated) ... 064819,-.0038620312698185443878,-.022344633936882019042,.052769336849451065063,-.061306387186050415039,.048756919801235198974,-.063436612486839294433'), ('SHIELDAIG 12 YEAR OLD, 40%', 'BLENDED SCOTCH WHISKY', 85, 31.0, 'THIS IS A SHARP DRESSER, WITH A FIRM, SOLID MOUTHFEEL AND AN ALTOGETHER FINER AND MORE FOCUSED TASTE THAN SHIELDAIG CLASSIC (SEE\\r\\nBELOW). IT’S NOT  ... (114 characters truncated) ... E, AND SOME SOFT FRUIT, INCLUDING A TOUCH OF OVERRIPE BANANA AND MELON NOTES. THE SAVORINESS THIS TIME COMES FROM A TOUCH OF PEPPER RATHER THAN SALT.', '-.0049302759580314159393,-.070051722228527069091,.046160325407981872558,.053877647966146469116,.0037386598996818065643,.018159903585910797119,.076887 ... (8828 characters truncated) ... 3170166,-.0015221295179799199104,.047901224344968795776,.0098907267674803733826,-.026278590783476829528,.042504664510488510131,.041063331067562103271'), ('THE ARRAN MALT, SINGLE BOURBON CASK, (CASK#1801), 1996 VINTAGE, 50.5%', 'SINGLE MALT SCOTCH', 86, 80.0, 'FRESH AND CLEAN, WITH NOTES OF VANILLA, RIPE BARLEY, HONEY, CARAMEL APPLE, AND TOASTED COCONUT. CREAMY AND MOUTH-COATING IN TEXTURE, LEADING TO A PLEASINGLY DRY, SPICY OAK FINISH. VERY DRINKABLE, YET SATISFYING. QUITE NICE. \\r\\n', '-.0010089599527418613433,-.050370443612337112426,.046052008867263793946,.074557252228260040283,-.0048394058831036090851,.039374433457851409912,.02021 ... (8848 characters truncated) ... 5349731,.0031521078199148178101,-.0083352373912930488586,.10131823271512985229,-.021709911525249481201,.037876114249229431152,.0095796724781394004821')]\n"
     ]
    }
   ],
   "source": [
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>name</th>\n",
       "      <th>category</th>\n",
       "      <th>review.point</th>\n",
       "      <th>price</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SIGNATORY (DISTILLED AT BOWMORE), 16 YEAR OLD, 1988 VINTAGE, CASK #42508, 46%</td>\n",
       "      <td>SINGLE MALT SCOTCH</td>\n",
       "      <td>87</td>\n",
       "      <td>60.0</td>\n",
       "      <td>MEDIUM-BODIED AND NICELY TEXTURED. GOOD BALANCE OF FLAVORS -- AND WELL-INTEGRATED, TOO -- WITH LOVELY SWEET NOTES (CEREAL GRAIN, COOKIE DOUGH, CARAMEL, AND VANILLA CREAM), YOUNG HEATHERY PEAT, TAR, FISHNETS, AND BRINE THAT IS COMPLEMENTARY, BUT NOT AGGRESSIVE, WITH A SUGGESTION OF LAVENDER AND TANGERINE. BALANCED FINISH. (332 BOTTLES PRODUCED.)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SHIELDAIG 12 YEAR OLD, 40%</td>\n",
       "      <td>BLENDED SCOTCH WHISKY</td>\n",
       "      <td>85</td>\n",
       "      <td>31.0</td>\n",
       "      <td>THIS IS A SHARP DRESSER, WITH A FIRM, SOLID MOUTHFEEL AND AN ALTOGETHER FINER AND MORE FOCUSED TASTE THAN SHIELDAIG CLASSIC (SEE\\r\\nBELOW). IT’S NOT COASTAL OR EARTHY PARTICULARLY, EITHER. INSTEAD THE FLAVORS ARE SOFTER AND BUILT AROUND MOCHA, SMOOTH CREAMY TOFFEE, AND SOME SOFT FRUIT, INCLUDING A TOUCH OF OVERRIPE BANANA AND MELON NOTES. THE SAVORINESS THIS TIME COMES FROM A TOUCH OF PEPPER RATHER THAN SALT.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>THE ARRAN MALT, SINGLE BOURBON CASK, (CASK#1801), 1996 VINTAGE, 50.5%</td>\n",
       "      <td>SINGLE MALT SCOTCH</td>\n",
       "      <td>86</td>\n",
       "      <td>80.0</td>\n",
       "      <td>FRESH AND CLEAN, WITH NOTES OF VANILLA, RIPE BARLEY, HONEY, CARAMEL APPLE, AND TOASTED COCONUT. CREAMY AND MOUTH-COATING IN TEXTURE, LEADING TO A PLEASINGLY DRY, SPICY OAK FINISH. VERY DRINKABLE, YET SATISFYING. QUITE NICE. \\r\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                            name  \\\n",
       "0  SIGNATORY (DISTILLED AT BOWMORE), 16 YEAR OLD, 1988 VINTAGE, CASK #42508, 46%   \n",
       "1                                                     SHIELDAIG 12 YEAR OLD, 40%   \n",
       "2          THE ARRAN MALT, SINGLE BOURBON CASK, (CASK#1801), 1996 VINTAGE, 50.5%   \n",
       "\n",
       "                category  review.point  price  \\\n",
       "0     SINGLE MALT SCOTCH            87   60.0   \n",
       "1  BLENDED SCOTCH WHISKY            85   31.0   \n",
       "2     SINGLE MALT SCOTCH            86   80.0   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                    description  \n",
       "0                                                                    MEDIUM-BODIED AND NICELY TEXTURED. GOOD BALANCE OF FLAVORS -- AND WELL-INTEGRATED, TOO -- WITH LOVELY SWEET NOTES (CEREAL GRAIN, COOKIE DOUGH, CARAMEL, AND VANILLA CREAM), YOUNG HEATHERY PEAT, TAR, FISHNETS, AND BRINE THAT IS COMPLEMENTARY, BUT NOT AGGRESSIVE, WITH A SUGGESTION OF LAVENDER AND TANGERINE. BALANCED FINISH. (332 BOTTLES PRODUCED.)  \n",
       "1  THIS IS A SHARP DRESSER, WITH A FIRM, SOLID MOUTHFEEL AND AN ALTOGETHER FINER AND MORE FOCUSED TASTE THAN SHIELDAIG CLASSIC (SEE\\r\\nBELOW). IT’S NOT COASTAL OR EARTHY PARTICULARLY, EITHER. INSTEAD THE FLAVORS ARE SOFTER AND BUILT AROUND MOCHA, SMOOTH CREAMY TOFFEE, AND SOME SOFT FRUIT, INCLUDING A TOUCH OF OVERRIPE BANANA AND MELON NOTES. THE SAVORINESS THIS TIME COMES FROM A TOUCH OF PEPPER RATHER THAN SALT.  \n",
       "2                                                                                                                                                                                          FRESH AND CLEAN, WITH NOTES OF VANILLA, RIPE BARLEY, HONEY, CARAMEL APPLE, AND TOASTED COCONUT. CREAMY AND MOUTH-COATING IN TEXTURE, LEADING TO A PLEASINGLY DRY, SPICY OAK FINISH. VERY DRINKABLE, YET SATISFYING. QUITE NICE. \\r\\n  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results_df = pd.DataFrame(results, columns=df.columns).iloc[:, :-1] # Remove vector\n",
    "pd.set_option('display.max_colwidth', None)  # Easier to read description\n",
    "results_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Vector Index\n",
    "\n",
    "The latest version of IRIS released to the Early Access Program includes not only bug fixes and performance enhancements, but a new disk-based Approximate Nearest Neighbors index that speeds up vector search for large collections of vectors (typically over 100K).\n",
    "\n",
    "Since this notebook is working with a dataset much smaller than 100K rows, there won't be a measurable performance benefit, and this is provided as an example you can adapt.\n",
    "\n",
    "NOTE: Please note this feature is currently targeted at 2025.1. Please join the [Early Access Program](https://live.evaluation.iscinternal.com/download/adminearlyaccess.csp?earlyAccessProgram=Vector_Search) if you'd like to work with a preview kit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    with conn.begin():# Load \n",
    "        sql = f\"\"\"\n",
    "                CREATE INDEX HNSWIndex \n",
    "                ON TABLE scotch_reviews (description_vector)\n",
    "                AS %SQL.Index.HNSW(M=80, Distance='DotProduct')\n",
    "                \"\"\"\n",
    "        result = conn.execute(text(sql))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}