find_duplicates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5de5f4fc-621a-4536-80b9-c635427c4aab",
   "metadata": {},
   "source": [
    "### Finding duplicate candidates in the documentation using **AI Vector Search**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "88adce6a-a2c4-43a1-8359-e1f805a74d25",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lsaetta/miniforge3/envs/rag_env02/lib/python3.11/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from .autonotebook import tqdm as notebook_tqdm\n"
     ]
    }
   ],
   "source": [
    "# we need this for the reranker\n",
    "import ads\n",
    "from typing import List\n",
    "\n",
    "import oracledb\n",
    "import time\n",
    "import logging\n",
    "from tqdm import tqdm\n",
    "\n",
    "from langchain_core.documents import Document\n",
    "\n",
    "from oci_utils import load_oci_config\n",
    "\n",
    "from config_private import DB_HOST_IP, DB_USER, DB_PWD, DB_SERVICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1e65128f-fec0-4fad-a48c-55bc60c69f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Configure logging\n",
    "logging.basicConfig(\n",
    "    level=logging.INFO, format=\"%(asctime)s - %(levelname)s - %(message)s\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7cf672d6-41b8-45eb-acc3-fdb498b32563",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# This function returns the (id1, id2) of the candidate pairs + distance (DOT)\n",
    "#\n",
    "def self_similarity_search(\n",
    "    top_k=10,\n",
    "    vector_table_name=\"vectors\",\n",
    "    vector_field_name=\"vec\",\n",
    "    text_table_name=\"chunks\",\n",
    "    text_field_name=\"chunk\",\n",
    "    verbose=False,\n",
    ") -> List:\n",
    "    \"\"\"\n",
    "    Executes a query against an Oracle database to find the top_k candidate duplicates.\n",
    "    \"\"\"\n",
    "    tStart = time.time()\n",
    "\n",
    "    # build the DSN from data taken from config.py\n",
    "    DSN = DB_HOST_IP + \"/\" + DB_SERVICE\n",
    "\n",
    "    try:\n",
    "        with oracledb.connect(user=DB_USER, password=DB_PWD, dsn=DSN) as connection:\n",
    "            with connection.cursor() as cursor:\n",
    "\n",
    "                # only constraint: table has to have an id field\n",
    "                select = f\"\"\"select /*+ parallel(12) */ a.id, b.id, \n",
    "                vector_distance(a.{vector_field_name}, b.{vector_field_name}, COSINE) as d \n",
    "                from {vector_table_name} a, {vector_table_name} b\n",
    "                where a.id < b.id\n",
    "                order by d asc\n",
    "                fetch first {top_k} rows only\"\"\"\n",
    "\n",
    "                if verbose:\n",
    "                    logging.info(f\"Select: {select}\")\n",
    "\n",
    "                cursor.execute(select)\n",
    "\n",
    "                rows = cursor.fetchall()\n",
    "\n",
    "                ids_list = []\n",
    "                d_list = []\n",
    "                # prepare output\n",
    "                for row in rows:\n",
    "                    ids_list.append([row[0], row[1]])\n",
    "                    d_list.append(row[2])\n",
    "\n",
    "    except Exception as e:\n",
    "        logging.error(f\"Error occurred in self_similarity_search: {e}\")\n",
    "\n",
    "        return None\n",
    "\n",
    "    docs_with_distance = []\n",
    "    for (id1, id2), d in zip(tqdm(ids_list), d_list):\n",
    "        d = round(d, 4)\n",
    "        text1, page_num1 = find_text_and_metadata(id1, text_table_name, text_field_name)\n",
    "        text2, page_num2 = find_text_and_metadata(id2, text_table_name, text_field_name)\n",
    "\n",
    "        doc1 = Document(page_content=text1, metadata={\"page_num\": page_num1})\n",
    "        doc2 = Document(page_content=text2, metadata={\"page_num\": page_num2})\n",
    "\n",
    "        docs_with_distance.append((doc1, doc2, d))\n",
    "\n",
    "    tEla = time.time() - tStart\n",
    "\n",
    "    return docs_with_distance\n",
    "\n",
    "\n",
    "#\n",
    "# This function retrieve text + metadata from id\n",
    "#\n",
    "def find_text_and_metadata(\n",
    "    id, text_table_name=\"chunks\", text_field_name=\"chunk\", verbose=False\n",
    "):\n",
    "    DSN = DB_HOST_IP + \"/\" + DB_SERVICE\n",
    "\n",
    "    try:\n",
    "        with oracledb.connect(user=DB_USER, password=DB_PWD, dsn=DSN) as connection:\n",
    "            with connection.cursor() as cursor:\n",
    "                select = f\"\"\"select {text_field_name}, page_num \n",
    "                from {text_table_name}\n",
    "                where id = :1\n",
    "                \"\"\"\n",
    "\n",
    "                if verbose:\n",
    "                    logging.info(f\"select: {select}\")\n",
    "\n",
    "                cursor.execute(select, [id])\n",
    "\n",
    "                rows = cursor.fetchall()\n",
    "\n",
    "                # prepare output\n",
    "                for row in rows:\n",
    "                    clob_pointer = row[0]\n",
    "                    page_num = row[1]\n",
    "                    ifull_clob_data = clob_pointer.read()\n",
    "\n",
    "    except Exception as e:\n",
    "        logging.error(f\"Error occurred in oracle_query: {e}\")\n",
    "\n",
    "        return None\n",
    "\n",
    "    return ifull_clob_data, page_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1c015ac2-dab4-4b08-bca7-3a2bada4c1d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2024-03-25 10:11:13,173 - INFO - Select: select /*+ parallel(12) */ a.id, b.id, \n",
      "                vector_distance(a.vec, b.vec, COSINE) as d \n",
      "                from vectors a, vectors b\n",
      "                where a.id < b.id\n",
      "                order by d asc\n",
      "                fetch first 4 rows only\n",
      "100%|██████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:02<00:00,  1.46it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 172 ms, sys: 17 ms, total: 189 ms\n",
      "Wall time: 17 s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "docs_with_distance = self_similarity_search(top_k=4, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7f9062e8-efa8-4707-8a3f-1367fad58608",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COVID-19 Treatment Guidelines 99Table 3c. Therapeutic Management of Hospitalized Children With COVID-19 Disease Severity Panel’s Recommendations Hospitalized for COVID-19For children aged ≥12 years admitted for COVID-19, use prophylactic anticoagulation unless contraindicated (BIII) .a Does Not Require Supplemental OxygenFor children admitted for COVID-19 who are at the highest risk of progression to severe COVID-19,b consider using remdesivirc for children aged 12–17 years (CIII) . There is insufficient evidence for using remdesivir in children aged 28 days to <12 years. For children admitted for reasons other than COVID-19 who have mild to moderate COVID-19 and are at the highest risk of progression,b refer to Therapeutic Management of Nonhospitalized Children With COVID-19 . Requires Conventional OxygendUse 1 of the following options: •Remdesivirc (BIII) •Dexamethasone plus remdesivirc for children with increasing oxygen needs, particularly adolescents (BIII) Requires Oxygen Through\n",
      "Pag: 99\n",
      "\n",
      "COVID-19 Treatment Guidelines 73Table 3c. Therapeutic Management of Hospitalized Children With COVID-19 Disease Severity Panel’s Recommendations Hospitalized for COVID-19For children aged ≥12 years admitted for COVID-19, use prophylactic anticoagulation unless contraindicated (BIII) .a Does Not Require Supplemental OxygenFor children admitted for COVID-19 who are at the highest risk of progression to severe COVID-19,b consider using remdesivirc for children aged 12–17 years (CIII) . There is insufficient evidence for using remdesivir in children aged 28 days to <12 years. For children admitted for reasons other than COVID-19 who have mild to moderate COVID-19 and are at the highest risk of progression,b refer to Therapeutic Management of Nonhospitalized Children With COVID-19 . Requires Conventional OxygendUse 1 of the following options: •Remdesivirc (BIII) •Dexamethasone plus remdesivirc for children with increasing oxygen needs, particularly adolescents (BIII) Requires Oxygen Through\n",
      "Pag: 73\n",
      "Distance: 0.0011\n",
      "----------------------\n",
      "\n",
      "COVID-19 Treatment Guidelines 109Table 3d. Therapeutic Management of Hospitalized Pediatric Patients With MIS-C Panel’s Recommendations MIS-CInitial treatment for MIS-C includes both immunomodulatory and antithrombotic therapy. Initial Immunomodulatory Therapy •IVIG 2 g/kg IBW (up to a maximum total dose of 100 g) IV plus low to moderate dose methylprednisolone (1–2 mg/kg/day) IVa or another glucocorticoid at an equivalent dosea (AIIb) . •Glucocorticoid monotherapy , only if IVIG is unavailable or contraindicated (BIIa) . •IVIG monotherapy , only if glucocorticoids are contraindicated (BIIb) . Intensification Immunomodulatory Therapy •Intensification therapy is recommended for children with refractory MIS-C who do not improve within 24 hours of receiving initial immunomodulatory therapy (AIII) . One of the following can be used (listed in alphabetical order): •High-dose anakinra 5–10 mg/kg IV or SUBQ once daily (BIIb) •Higher-dose glucocorticoid (e.g., methylprednisolone 10–30 mg/kg/da\n",
      "Pag: 109\n",
      "\n",
      "COVID-19 Treatment Guidelines 74Table 3d. Therapeutic Management of Hospitalized Pediatric Patients With MIS-C Panel’s Recommendations MIS-CInitial treatment for MIS-C includes both immunomodulatory and antithrombotic therapy. Initial Immunomodulatory Therapy •IVIG 2 g/kg IBW (up to a maximum total dose of 100 g) IV plus low to moderate dose methylprednisolone (1–2 mg/kg/day) IVa or another glucocorticoid at an equivalent dosea (AIIb) . •Glucocorticoid monotherapy , only if IVIG is unavailable or contraindicated (BIIa) . •IVIG monotherapy , only if glucocorticoids are contraindicated (BIIb) . Intensification Immunomodulatory Therapy •Intensification therapy is recommended for children with refractory MIS-C who do not improve within 24 hours of receiving initial immunomodulatory therapy (AIII) . One of the following can be used (listed in alphabetical order): •High-dose anakinra 5–10 mg/kg IV or SUBQ once daily (BIIb) •Higher-dose glucocorticoid (e.g., methylprednisolone 10–30 mg/kg/day\n",
      "Pag: 74\n",
      "Distance: 0.0018\n",
      "----------------------\n",
      "\n",
      "Table 26-5 (Cont.) Application Drain Attributes for Exadata Cloud Software Updates and Elastic Operations Oracle Exadata Database Service Software Updates or Elastic OperationsDrain Timeout Variables Virtual machine operating system software update (Exadata Database Guest)Exadata patchmgr/dbnodeupdate software program calls drain orchestration (rhphelper). Drain orchestration has the following drain timeout settings (See Using RHPhelper to Minimize Downtime During Planned Maintenance on Exadata (Doc ID 2385790.1) for details): ·DRAIN_TIMEOUT ± if a service does not have drain_timeout defined, then this value is used. Default value is 180 seconds. ·MAX_DRAIN_TIMEOUT - overrides any higher drain_timeout value defined by database service configuration. Default value is 300 seconds. There is no maximum value. DRAIN_TIMEOUT settings defined by database service configuration are honored during service stop/relocate. Exadata X8 and earlier systems ·Scale up and down VM local /u02 file system \n",
      "Pag: 26-14\n",
      "\n",
      "Table 26-5 (Cont.) Application Drain Attributes for Exadata Cloud Software Updates and Elastic Operations Oracle Exadata Database Service Software Updates or Elastic OperationsDrain Timeout Variables Exadata X8M and later systems ·Scale down VM local file system sizeExadata X8M and later systems call drain orchestration (rhphelper). Drain orchestration has the following drain timeout settings (See Using RHPhelper to Minimize Downtime During Planned Maintenance on Exadata (Doc ID 2385790.1) for details): ·DRAIN_TIMEOUT ± if a service does not have drain_timeout defined, then this value is used. Default value is 180 seconds. ·MAX_DRAIN_TIMEOUT - overrides any higher drain_timeout value defined by database service configuration. Default value is 300 seconds. DRAIN_TIMEOUT settings defined by database service configuration are honored during service stop/relocate. The Oracle Cloud internal maximum draining time supported for this operation is 300 seconds. Exadata X8M and later systems ·Sca\n",
      "Pag: 26-15\n",
      "Distance: 0.0102\n",
      "----------------------\n",
      "\n",
      "•The --user parameter should mirror the GoldenGate administrator account specified with the initial deployment creation. •The GoldenGate administrator password will be prompted. •The reverse proxy port number specified by the --port parameter should be the default HTTPS port number (443) unless you are running multiple GoldenGate Service Managers using the same --host. In this case, specify an HTTPS port number that does not conflict with previous Service Manager reverse proxy configurations. For example, if running two Service Managers using the same hostname/VIP, the first reverse proxy configuration is created with '--port 443 --host hostvip01', and the second is created with '--port 444 --host hostvip01'. If using separate hostnames/VIPs, the two Service Manager reverse proxy configurations would be created with '--port 443 --host hostvip01' and '-port 443 --host hostvip02'. •Lastly, the HTTP port number (9100) should match the Service Manager port number specified during the deplo\n",
      "Pag: 19-55\n",
      "\n",
      "•The --user parameter should mirror the GoldenGate administrator account specified with the initial deployment creation. •The GoldenGate administrator password will be prompted. •The reverse proxy port number specified by the --port parameter should be the default HTTPS port number (443) unless you are running multiple GoldenGate Service Managers using the same --host . In this case, specify an HTTPS port number that does not conflict with previous Service Manager reverse proxy configurations. For example, if you are running two Service Managers using the same hostname/ VIP, the first reverse proxy configuration is created with --port 443 --host hostvip01 , and the second is created with --port 444 --host hostvip01 . If you are using separate hostnames/VIPs, the two Service Manager reverse proxy configurations would be created with --port 443 --host hostvip01 and --port 443 --host hostvip02 . •Lastly, the HTTP port number (9100) should match the Service Manager port number specified du\n",
      "Pag: 20-34\n",
      "Distance: 0.0129\n",
      "----------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "MAX_LEN_DISP = 1000\n",
    "\n",
    "for doc1, doc2, d in docs_with_distance:\n",
    "    print(doc1.page_content[:MAX_LEN_DISP])\n",
    "    print(f\"Pag: {doc1.metadata['page_num']}\")\n",
    "    print(\"\")\n",
    "    print(doc2.page_content[:MAX_LEN_DISP])\n",
    "    print(f\"Pag: {doc2.metadata['page_num']}\")\n",
    "    print(f\"Distance: {d}\")\n",
    "    print(\"----------------------\")\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "079ae861-289f-4dc5-99e7-bc8ef33ddcc2",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}