demo/cloud_sql_demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6d3a14d8",
   "metadata": {},
   "source": [
    "# Vector Search with Cloud SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b151275e",
   "metadata": {},
   "source": [
    "In this notebook, we'll leverage the Vector Search capabilities available in [InterSystems IRIS Cloud SQL](https://developer.intersystems.com/products/iris-cloud-sql-integratedml/). The feature works in exactly the same way as in the InterSystems IRIS 2024.1 release, but Cloud SQL requires secure connections, and this notebook illustrates how to set those up.\n",
    "\n",
    "First, please adapt the password and hostname entries in the following cell to match your Cloud SQL deployment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57149010",
   "metadata": {},
   "outputs": [],
   "source": [
    "username = 'SQLAdmin'\n",
    "password = '...'\n",
    "hostname = '...'\n",
    "port = 443 \n",
    "namespace = 'USER'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d199a694",
   "metadata": {},
   "source": [
    "### Copying the certificate\n",
    "\n",
    "In order to connect securely, you'll need to point the driver at the `certificateSQLaaS.pem` file for your Cloud SQL deployment. You can download the certificate file from your deployment's detail screen. Look for the button that says \"Get X.509 certificate\". If you're running this notebook in a container, you can copy the certificate file into the container using the following command:\n",
    "\n",
    "```Shell\n",
    "docker cp ~/Downloads/certificateSQLaaS.pem iris-vector-search-jupyter-1:/usr/cert-demo/certificateSQLaaS.pem\n",
    "```\n",
    "\n",
    "We'll use simple DB-API commands to establish a connection in this example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6adf3bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import intersystems_iris as iris\n",
    "import ssl\n",
    "\n",
    "# change this to wherever you copied your certificate to\n",
    "certificateFile = \"/usr/cert-demo/certificateSQLaaS.pem\"\n",
    "sslcontext = ssl.create_default_context(cafile=certificateFile)\n",
    "\n",
    "connection = iris.connect( hostname, port, namespace, username, password, sslcontext = sslcontext )\n",
    "cursor = connection.cursor()\n",
    "\n",
    "cursor.execute(\"SELECT 'hello secure world!'\")\n",
    "cursor.fetchone()[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c0a4e20",
   "metadata": {},
   "source": [
    "## Double vectors, no ice\n",
    "\n",
    "Now that we have established a secure connection, let's get onto some actual vector stuff!\n",
    "\n",
    "See the neighbouring `sql_demo.ipynb` for full detail on what we're trying to achieve here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "679c7a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the CSV file\n",
    "df = pd.read_csv('../data/scotch_review.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd122719",
   "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)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6bd2994",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sentence_transformers import SentenceTransformer\n",
    "\n",
    "# Load a pre-trained sentence transformer model. This model's output vectors are of size 384\n",
    "model = SentenceTransformer('all-MiniLM-L6-v2') \n",
    "\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",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0b756b6",
   "metadata": {},
   "source": [
    "## And now load them into Cloud SQL\n",
    "\n",
    "We'll first create a table and then ingest all the rows from the dataframe we created earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d19c1ab7",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS scotch_reviews')\n",
    "cursor.execute(f\"\"\"CREATE TABLE 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",
    "seq = []\n",
    "for index, row in df.iterrows():\n",
    "    seq.append((row['name'], row['category'], row['review.point'], row['price'], row['description'], str(row['description_vector'])))\n",
    "\n",
    "success = cursor.executemany(\"INSERT INTO scotch_reviews (name, category, review_point, price, description, description_vector) VALUES (?, ?, ?, ?, ?, TO_VECTOR(?))\", seq)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7c8b0fa",
   "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\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "            SELECT TOP 3 * FROM scotch_reviews \n",
    "            WHERE price < 100 \n",
    "            ORDER BY VECTOR_DOT_PRODUCT(description_vector, TO_VECTOR(?)) DESC\n",
    "        \"\"\", [str(search_vector)])\n",
    "\n",
    "print(cursor.fetchall())"
   ]
  }
 ],
 "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}