{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# How to use Semantic Search\n",
    "\n",
    "This tutorial will show you how to use indexing and semantic search.\n",
    "\n",
    "## Background\n",
    "\n",
    "LinkML-Store allows you to *compose* different indexing strategies with any backend. Currently there are two\n",
    "indexing strategies:\n",
    "\n",
    "- Simple trigram-based\n",
    "- LLM text or image embedding based (using models from OpenAI, HuggingFace, and others)\n",
    "\n",
    "These indexes can be added into any backend (duckdb, mongo, ...)\n",
    "\n",
    "Additionally, some backends may have their own indexing strategy\n",
    "\n",
    "- Solr has a number of text-based indexing strategies\n",
    "- ChromaDB can use text-based vector embeddings\n",
    "\n",
    "LinkML-Store allows for maximum flexibility.\n",
    "\n",
    "This tutorial shows how to use an OpenAI-based embedding strategy in combination with DuckDB."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "315813bcb5f486a4"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Obtaining upstream files\n",
    "\n",
    "We will use the OBO Graphs encoding of the Enzyme Commission (EC) database, via [biogragmatics](https://w3id.org/biopragmatics)\n",
    "\n",
    "We will use the pystow library to cache the upstream file. "
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "2559f81507693d24"
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import pystow\n",
    "path=pystow.ensure(\"tmp\", \"eccode.json\", url=\"https://w3id.org/biopragmatics/resources/eccode/eccode.json\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:04.786347Z",
     "start_time": "2024-07-05T19:07:04.677568Z"
    }
   },
   "id": "5a0030a3e24b1545"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Let's examining the structure of the JSON. There is a top level `graphs` index, each of which holds a set of `nodes` and `edges`:"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "8e046de9070b82c1"
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "graphdoc = json.load(open(path))\n",
    "graph = graphdoc[\"graphs\"][0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:04.976093Z",
     "start_time": "2024-07-05T19:07:04.749596Z"
    }
   },
   "id": "793566db96fc1cd9"
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "(7177, 506022)"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(graph[\"nodes\"]), len(graph[\"edges\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:04.979929Z",
     "start_time": "2024-07-05T19:07:04.977334Z"
    }
   },
   "id": "b3c3cf57c2d9aeed"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Storing the JSON\n",
    "\n",
    "We will create a duckdb database to insert the JSON objects. We'll put this in a `tmp/` folder"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "9f11d6d2b7a2c602"
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "!mkdir -p tmp"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:05.099895Z",
     "start_time": "2024-07-05T19:07:04.980371Z"
    }
   },
   "id": "cbae2c783889c9b3"
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [],
   "source": [
    "from linkml_store import Client\n",
    "\n",
    "client = Client()\n",
    "db = client.attach_database(\"duckdb:///tmp/eccode.db\", \"eccode\", recreate_if_exists=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:06.181320Z",
     "start_time": "2024-07-05T19:07:05.100573Z"
    }
   },
   "id": "6a8adce3d3ec93c6"
  },
  {
   "cell_type": "markdown",
   "source": [
    "We will create an index for nodes. (we could make a separate collection for edges, but this is less relevant\n",
    "for this tutorial)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "1dcfaf6e8d4b3bf6"
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [],
   "source": [
    "nodes_collection = db.create_collection(\"Node\", \"nodes\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:06.183588Z",
     "start_time": "2024-07-05T19:07:06.181604Z"
    }
   },
   "id": "4fa95b75cd1f19cf"
  },
  {
   "cell_type": "markdown",
   "source": [
    "For demonstration purposes we'll only store the first 200 entries (it can be slow to index everything via the OpenAI API)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ecc62533a80b1175"
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [],
   "source": [
    "nodes_collection.insert(graph[\"nodes\"][0:200])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:06.360142Z",
     "start_time": "2024-07-05T19:07:06.184591Z"
    }
   },
   "id": "aee6a95b4a86432d"
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "[{'id': 'http://purl.obolibrary.org/obo/RO_0002327',\n  'lbl': 'enables',\n  'type': 'PROPERTY',\n  'meta': None},\n {'id': 'http://purl.obolibrary.org/obo/RO_0002351',\n  'lbl': 'has member',\n  'type': 'PROPERTY',\n  'meta': None},\n {'id': 'http://purl.obolibrary.org/obo/eccode_1',\n  'lbl': 'Oxidoreductases',\n  'type': 'CLASS',\n  'meta': None},\n {'id': 'http://purl.obolibrary.org/obo/eccode_1.1',\n  'lbl': 'Acting on the CH-OH group of donors',\n  'type': 'CLASS',\n  'meta': None},\n {'id': 'http://purl.obolibrary.org/obo/eccode_1.1.1',\n  'lbl': 'With NAD(+) or NADP(+) as acceptor',\n  'type': 'CLASS',\n  'meta': None},\n {'id': 'http://purl.obolibrary.org/obo/eccode_1.1.1.1',\n  'lbl': 'alcohol dehydrogenase',\n  'type': 'CLASS',\n  'meta': ['synonyms']}]"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_collection.find(limit=6).rows"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:06.390591Z",
     "start_time": "2024-07-05T19:07:06.363698Z"
    }
   },
   "id": "95c06a10ecfcfb67"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating an LLMIndexer\n",
    "\n",
    "We will create an indexer, and configure it to cache calls. This means that the 2nd time we run this notebook\n",
    "it will be much faster, since all the embeddings will be cached.\n",
    "\n",
    "The indexer will index using the `lbl` field. In OBO Graphs JSON, this is the name/label of the concept."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "80d64d4e6570d69c"
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [],
   "source": [
    "from linkml_store.index.implementations.llm_indexer import LLMIndexer\n",
    "\n",
    "index = LLMIndexer(name=\"test\", cached_embeddings_database=\"tmp/llm_cache.db\", index_attributes=[\"lbl\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:06.393562Z",
     "start_time": "2024-07-05T19:07:06.390800Z"
    }
   },
   "id": "8e75156bfdafe7b"
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "nodes_collection.attach_indexer(index)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:12.102912Z",
     "start_time": "2024-07-05T19:07:06.393220Z"
    }
   },
   "id": "993c0360941dbb3b"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Searching using the index\n",
    "\n",
    "Now we have attached an index, we can use it in semantic search. We'll search our EC subset nodes collection for a string `sugar transporters`. Note that this string doesn't occur precisely in the index but we can still rank closeness in semantic space.\n",
    "\n",
    "When using `search` the field `ranked_rows` is populated in the result object. This is a list of `(score, object)` tuples, which we will look at by translating into a pandas DataFrame:"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "47c1cc117f379d63"
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "qr = nodes_collection.search(\"sugar transporters\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:12.376668Z",
     "start_time": "2024-07-05T19:07:12.106295Z"
    }
   },
   "id": "e3abb5d529063c6e"
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [],
   "source": [
    "results = [{\"sim\": r[0], \"id\": r[1][\"id\"], \"name\": r[1][\"lbl\"]} for r in qr.ranked_rows]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:12.380018Z",
     "start_time": "2024-07-05T19:07:12.377643Z"
    }
   },
   "id": "cc3bce09e81d66a"
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(results)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:12.383310Z",
     "start_time": "2024-07-05T19:07:12.381381Z"
    }
   },
   "id": "5b9d6d63561b72db"
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "          sim                                               id  \\\n0    0.811164   http://purl.obolibrary.org/obo/eccode_1.1.1.22   \n1    0.809901  http://purl.obolibrary.org/obo/eccode_1.1.1.124   \n2    0.808242   http://purl.obolibrary.org/obo/eccode_1.1.1.10   \n3    0.804669  http://purl.obolibrary.org/obo/eccode_1.1.1.162   \n4    0.804353  http://purl.obolibrary.org/obo/eccode_1.1.1.271   \n..        ...                                              ...   \n195  0.741834  http://purl.obolibrary.org/obo/eccode_1.1.1.141   \n196  0.738374  http://purl.obolibrary.org/obo/eccode_1.1.1.147   \n197  0.738128        http://purl.obolibrary.org/obo/RO_0002351   \n198  0.729969  http://purl.obolibrary.org/obo/eccode_1.1.1.104   \n199  0.722231  http://purl.obolibrary.org/obo/eccode_1.1.1.223   \n\n                                               name  \n0                       UDP-glucose 6-dehydrogenase  \n1                fructose 5-dehydrogenase (NADP(+))  \n2                              L-xylulose reductase  \n3                             erythrulose reductase  \n4                             GDP-L-fucose synthase  \n..                                              ...  \n195  15-hydroxyprostaglandin dehydrogenase (NAD(+))  \n196            16alpha-hydroxysteroid dehydrogenase  \n197                                      has member  \n198                          4-oxoproline reductase  \n199                    isopiperitenol dehydrogenase  \n\n[200 rows x 3 columns]",
      "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>sim</th>\n      <th>id</th>\n      <th>name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.811164</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.22</td>\n      <td>UDP-glucose 6-dehydrogenase</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.809901</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.124</td>\n      <td>fructose 5-dehydrogenase (NADP(+))</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0.808242</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.10</td>\n      <td>L-xylulose reductase</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.804669</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.162</td>\n      <td>erythrulose reductase</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.804353</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.271</td>\n      <td>GDP-L-fucose synthase</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>195</th>\n      <td>0.741834</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.141</td>\n      <td>15-hydroxyprostaglandin dehydrogenase (NAD(+))</td>\n    </tr>\n    <tr>\n      <th>196</th>\n      <td>0.738374</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.147</td>\n      <td>16alpha-hydroxysteroid dehydrogenase</td>\n    </tr>\n    <tr>\n      <th>197</th>\n      <td>0.738128</td>\n      <td>http://purl.obolibrary.org/obo/RO_0002351</td>\n      <td>has member</td>\n    </tr>\n    <tr>\n      <th>198</th>\n      <td>0.729969</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.104</td>\n      <td>4-oxoproline reductase</td>\n    </tr>\n    <tr>\n      <th>199</th>\n      <td>0.722231</td>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.223</td>\n      <td>isopiperitenol dehydrogenase</td>\n    </tr>\n  </tbody>\n</table>\n<p>200 rows × 3 columns</p>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:12.391729Z",
     "start_time": "2024-07-05T19:07:12.386207Z"
    }
   },
   "id": "dc81533a0b074360"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Even though our dataset had no actual sugar transporters, there are still ranked results, with the top 3 ranked highly by virtue of concerning sugars (even if they are not transporters).\n",
    "\n",
    "Note if we had indexed all of EC we would see sugar transporters."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "f0b5eadd3dd4885a"
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "[(0.8111048198475599,\n  {'id': 'http://purl.obolibrary.org/obo/eccode_1.1.1.22',\n   'lbl': 'UDP-glucose 6-dehydrogenase',\n   'type': 'CLASS',\n   'meta': None}),\n (0.8098110004639347,\n  {'id': 'http://purl.obolibrary.org/obo/eccode_1.1.1.124',\n   'lbl': 'fructose 5-dehydrogenase (NADP(+))',\n   'type': 'CLASS',\n   'meta': ['synonyms']}),\n (0.8081767571833294,\n  {'id': 'http://purl.obolibrary.org/obo/eccode_1.1.1.10',\n   'lbl': 'L-xylulose reductase',\n   'type': 'CLASS',\n   'meta': None})]"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qr = nodes_collection.search(\"sugar transporters\", where={\"type\": \"CLASS\"})\n",
    "qr.ranked_rows[0:3]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:13.628448Z",
     "start_time": "2024-07-05T19:07:12.396573Z"
    }
   },
   "id": "c4904a4d788a4241"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## How it works\n",
    "\n",
    "Let's peek under the hood into the duckdb instance to see how this is all implemented in DuckDB.\n",
    "\n",
    "To do this we'll connect to the duckdb instance directly using the `sql` extension in Jupyter"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "69357d61c2eea997"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Load extension:"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "8d140543ebaed624"
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "Tip: You may define configurations in /Users/cjm/repos/linkml-store/pyproject.toml or /Users/cjm/.jupysql/config. ",
      "text/html": "<span style=\"None\">Tip: You may define configurations in /Users/cjm/repos/linkml-store/pyproject.toml or /Users/cjm/.jupysql/config. </span>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "<IPython.core.display.HTML object>",
      "text/html": "Please review our <a href='https://jupysql.ploomber.io/en/latest/api/configuration.html#loading-from-a-file'>configuration guideline</a>."
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "Did not find user configurations in /Users/cjm/repos/linkml-store/pyproject.toml.",
      "text/html": "<span style=\"None\">Did not find user configurations in /Users/cjm/repos/linkml-store/pyproject.toml.</span>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%load_ext sql"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:13.990957Z",
     "start_time": "2024-07-05T19:07:13.628556Z"
    }
   },
   "id": "c1df3a0598731367"
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas = True\n",
    "%config SqlMagic.feedback = False\n",
    "%config SqlMagic.displaycon = False"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:13.991061Z",
     "start_time": "2024-07-05T19:07:13.981858Z"
    }
   },
   "id": "21d66e04be33e641"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Connect to the duckdb database\n",
    "\n",
    "__NOTE__ in general you don't need to do this - we are just doing this here to show the internals."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "26e3dcd952672b18"
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [],
   "source": [
    "%sql duckdb:///tmp/eccode.db"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:13.994789Z",
     "start_time": "2024-07-05T19:07:13.989443Z"
    }
   },
   "id": "7a747b441ebfe095"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Query the `nodes` table (no index)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "884ec42c3c80e6e0"
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "data": {
      "text/plain": "                                                  id  \\\n0          http://purl.obolibrary.org/obo/RO_0002327   \n1          http://purl.obolibrary.org/obo/RO_0002351   \n2            http://purl.obolibrary.org/obo/eccode_1   \n3          http://purl.obolibrary.org/obo/eccode_1.1   \n4        http://purl.obolibrary.org/obo/eccode_1.1.1   \n..                                               ...   \n195  http://purl.obolibrary.org/obo/eccode_1.1.1.284   \n196  http://purl.obolibrary.org/obo/eccode_1.1.1.285   \n197  http://purl.obolibrary.org/obo/eccode_1.1.1.286   \n198  http://purl.obolibrary.org/obo/eccode_1.1.1.287   \n199  http://purl.obolibrary.org/obo/eccode_1.1.1.288   \n\n                                            lbl      type        meta  \n0                                       enables  PROPERTY         NaN  \n1                                    has member  PROPERTY         NaN  \n2                               Oxidoreductases     CLASS         NaN  \n3           Acting on the CH-OH group of donors     CLASS         NaN  \n4            With NAD(+) or NADP(+) as acceptor     CLASS         NaN  \n..                                          ...       ...         ...  \n195  S-(hydroxymethyl)glutathione dehydrogenase     CLASS  [synonyms]  \n196  3''-deamino-3''-oxonicotianamine reductase     CLASS         NaN  \n197    isocitrate--homoisocitrate dehydrogenase     CLASS  [synonyms]  \n198        D-arabinitol dehydrogenase (NADP(+))     CLASS  [synonyms]  \n199                     xanthoxin dehydrogenase     CLASS  [synonyms]  \n\n[200 rows x 4 columns]",
      "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>lbl</th>\n      <th>type</th>\n      <th>meta</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>http://purl.obolibrary.org/obo/RO_0002327</td>\n      <td>enables</td>\n      <td>PROPERTY</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>http://purl.obolibrary.org/obo/RO_0002351</td>\n      <td>has member</td>\n      <td>PROPERTY</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1</td>\n      <td>Oxidoreductases</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1</td>\n      <td>Acting on the CH-OH group of donors</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1</td>\n      <td>With NAD(+) or NADP(+) as acceptor</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>195</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.284</td>\n      <td>S-(hydroxymethyl)glutathione dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n    </tr>\n    <tr>\n      <th>196</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.285</td>\n      <td>3''-deamino-3''-oxonicotianamine reductase</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>197</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.286</td>\n      <td>isocitrate--homoisocitrate dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n    </tr>\n    <tr>\n      <th>198</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.287</td>\n      <td>D-arabinitol dehydrogenase (NADP(+))</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n    </tr>\n    <tr>\n      <th>199</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.288</td>\n      <td>xanthoxin dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n    </tr>\n  </tbody>\n</table>\n<p>200 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM nodes;"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T19:07:14.071710Z",
     "start_time": "2024-07-05T19:07:13.999228Z"
    }
   },
   "id": "262c07565b1cd356"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Query the index. Behind the scenes, linkml-store will create a table to cache each index for each collection. These currently start with `internal__index__` and are followed by the type of the objects, followed by the name of the index."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "4aacb2992bc49785"
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "                                                  id  \\\n0          http://purl.obolibrary.org/obo/RO_0002327   \n1          http://purl.obolibrary.org/obo/RO_0002351   \n2            http://purl.obolibrary.org/obo/eccode_1   \n3          http://purl.obolibrary.org/obo/eccode_1.1   \n4        http://purl.obolibrary.org/obo/eccode_1.1.1   \n..                                               ...   \n195  http://purl.obolibrary.org/obo/eccode_1.1.1.284   \n196  http://purl.obolibrary.org/obo/eccode_1.1.1.285   \n197  http://purl.obolibrary.org/obo/eccode_1.1.1.286   \n198  http://purl.obolibrary.org/obo/eccode_1.1.1.287   \n199  http://purl.obolibrary.org/obo/eccode_1.1.1.288   \n\n                                            lbl      type        meta  \\\n0                                       enables  PROPERTY         NaN   \n1                                    has member  PROPERTY         NaN   \n2                               Oxidoreductases     CLASS         NaN   \n3           Acting on the CH-OH group of donors     CLASS         NaN   \n4            With NAD(+) or NADP(+) as acceptor     CLASS         NaN   \n..                                          ...       ...         ...   \n195  S-(hydroxymethyl)glutathione dehydrogenase     CLASS  [synonyms]   \n196  3''-deamino-3''-oxonicotianamine reductase     CLASS         NaN   \n197    isocitrate--homoisocitrate dehydrogenase     CLASS  [synonyms]   \n198        D-arabinitol dehydrogenase (NADP(+))     CLASS  [synonyms]   \n199                     xanthoxin dehydrogenase     CLASS  [synonyms]   \n\n                                             __index__  \n0    [-0.021716245, -0.024930306, -0.015913868, -0....  \n1    [-0.03492431, -0.015462456, 0.002913293, -0.02...  \n2    [-0.031664208, -0.026391044, 8.377296e-05, -0....  \n3    [-0.023240522, -0.019391688, -0.006624823, -0....  \n4    [0.00993415, -0.039508518, 0.023213472, -0.016...  \n..                                                 ...  \n195  [-0.020920139, -0.0042932644, 0.0039249077, -0...  \n196  [-0.02360331, -0.025488937, -0.010397324, -0.0...  \n197  [-0.019758105, -0.016041763, 0.017833093, -0.0...  \n198  [-0.012204822, -0.034410186, 0.007919805, -0.0...  \n199  [-0.012760352, -0.016459865, 0.011843718, -0.0...  \n\n[200 rows x 5 columns]",
      "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>lbl</th>\n      <th>type</th>\n      <th>meta</th>\n      <th>__index__</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>http://purl.obolibrary.org/obo/RO_0002327</td>\n      <td>enables</td>\n      <td>PROPERTY</td>\n      <td>NaN</td>\n      <td>[-0.021716245, -0.024930306, -0.015913868, -0....</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>http://purl.obolibrary.org/obo/RO_0002351</td>\n      <td>has member</td>\n      <td>PROPERTY</td>\n      <td>NaN</td>\n      <td>[-0.03492431, -0.015462456, 0.002913293, -0.02...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1</td>\n      <td>Oxidoreductases</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n      <td>[-0.031664208, -0.026391044, 8.377296e-05, -0....</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1</td>\n      <td>Acting on the CH-OH group of donors</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n      <td>[-0.023240522, -0.019391688, -0.006624823, -0....</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1</td>\n      <td>With NAD(+) or NADP(+) as acceptor</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n      <td>[0.00993415, -0.039508518, 0.023213472, -0.016...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>195</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.284</td>\n      <td>S-(hydroxymethyl)glutathione dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n      <td>[-0.020920139, -0.0042932644, 0.0039249077, -0...</td>\n    </tr>\n    <tr>\n      <th>196</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.285</td>\n      <td>3''-deamino-3''-oxonicotianamine reductase</td>\n      <td>CLASS</td>\n      <td>NaN</td>\n      <td>[-0.02360331, -0.025488937, -0.010397324, -0.0...</td>\n    </tr>\n    <tr>\n      <th>197</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.286</td>\n      <td>isocitrate--homoisocitrate dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n      <td>[-0.019758105, -0.016041763, 0.017833093, -0.0...</td>\n    </tr>\n    <tr>\n      <th>198</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.287</td>\n      <td>D-arabinitol dehydrogenase (NADP(+))</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n      <td>[-0.012204822, -0.034410186, 0.007919805, -0.0...</td>\n    </tr>\n    <tr>\n      <th>199</th>\n      <td>http://purl.obolibrary.org/obo/eccode_1.1.1.288</td>\n      <td>xanthoxin dehydrogenase</td>\n      <td>CLASS</td>\n      <td>[synonyms]</td>\n      <td>[-0.012760352, -0.016459865, 0.011843718, -0.0...</td>\n    </tr>\n  </tbody>\n</table>\n<p>200 rows × 5 columns</p>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM internal__index__nodes__test;"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-07-05T20:16:38.030872Z",
     "start_time": "2024-07-05T20:16:37.997608Z"
    }
   },
   "id": "c9424eca0ae2ebbd"
  },
  {
   "cell_type": "markdown",
   "source": [
    "We can see that the index duplicates the content of the main table, and adds an additional vector column with the embedding."
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "cccee7d6d1f46b9e"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-07-05T19:07:14.030555Z"
    }
   },
   "id": "2a53092d82802b01"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}