solutions/dna_sequence_classification/dna_sequence_classification.ipynb · wcyxtt/milvus应用案例

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

该仓库未声明开源许可证文件（LICENSE），使用请关注具体项目描述及其代码上游依赖。

克隆/下载

dna_sequence_classification.ipynb 47.82 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2791b581",
   "metadata": {},
   "source": [
    "# DNA Sequence Classification\n",
    "\n",
    "DNA sequences can be classified in different ways. This demo shows how to build a system with Milvus 2.0 and postgres to determine gene families for DNA sequences and compare similarity between different organisms. It uses k-mer and CountVectorizer to extract features and get embeddings for dna sequences.\n",
    "\n",
    "## Data\n",
    "\n",
    "Sample data is downloaded from [Kaggle](https://www.kaggle.com/nageshsingh/dna-sequence-dataset?select=human.txt). This demo uses 6882 DNA sequences for 3 organisms: human (4380), chimpanzee (1682), dog (820), each of which has lines of DNA sequences with corresponding classes in a text file.\n",
    "\n",
    "     - data:\n",
    "        - human_data.txt\n",
    "        - chimp_data.txt\n",
    "        - dog_data.txt\n",
    "\n",
    "Each text file consists of a head line and lines of sequence (a sequence consisting of bases [A, C, G, T]) with its class (an integer from 0 to 6). Sample:\n",
    "                       \n",
    "     sequence class\n",
    "     GCTGCTGCCCCAGCACCAGGTGTCCGCGTACTGA\t6\n",
    "     CACCGGCCCTCCAGGGTCCAGCTGGTGCCCCAGGACACCATGACCAGCAGGGCCTAA\t0\n",
    "\n",
    "Our DNA sequences are classified into different gene families by class. A gene family is a group of related genes sharing a common ancestor. Members of one gene family may be paralogs or orthologs: gene with similar sequences from same or different species. See explanations for 7 classes in our dataset: [Kaggle](https://www.kaggle.com/nageshsingh/demystify-dna-sequencing-with-machine-learning)\n",
    "\n",
    "| Gene family | Class label |\n",
    "|:---|---|\n",
    "| G protein coupled receptors | 0 |\n",
    "| Tyrosine kinase | 1 |\n",
    "| Tyrosine phosphatase | 2 |\n",
    "| Synthetase | 3 |\n",
    "| Synthase | 4 |\n",
    "| Ion channel | 5 |\n",
    "| Transcription | 6 |\n",
    "\n",
    "\n",
    "Run following commands to get data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ab4e229b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Archive:  data.zip\n",
      "   creating: data/\n",
      "  inflating: data/dog_data.txt       \n",
      "  inflating: data/human_data.txt     \n",
      "  inflating: data/chimp_data.txt     \n",
      "  inflating: data/gene_class.txt     \n"
     ]
    }
   ],
   "source": [
    "! unzip data.zip && rm data.zip"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5acd878c",
   "metadata": {},
   "source": [
    "## Requirements\n",
    "\n",
    "We will run codes with python3 and start [Milvus2.0 (Standalone)](https://milvus.io/docs/v2.0.0/install_standalone-docker.md) & postgres with [docker](https://docs.docker.com/get-docker/). Required python modules are listed in `requirements.txt` to install.\n",
    "\n",
    "|    Packages    |     Servers    |\n",
    "| --------------- | -------------- |\n",
    "| pymilvus==2.0.0rc8 | milvus-2.0.0-rc8|\n",
    "|    sklearn    |    mysql    |\n",
    "|   pymysql   |\n",
    "|     numpy   |\n",
    "| pickle-mixin |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0134592f",
   "metadata": {},
   "source": [
    "## Up and Running\n",
    "\n",
    "### Install Packages\n",
    "\n",
    "\n",
    "Install the required python packages with requirements.txt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eab3ff3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymilvus==2.0.0rc8\n",
      "  Using cached pymilvus-2.0.0rc8-py3-none-any.whl (108 kB)\n",
      "Collecting sklearn\n",
      "  Using cached sklearn-0.0.tar.gz (1.1 kB)\n",
      "Collecting pymysql\n",
      "  Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB)\n",
      "Collecting numpy\n",
      "  Using cached numpy-1.21.4-cp38-cp38-macosx_10_9_x86_64.whl (16.9 MB)\n",
      "Collecting pickle-mixin\n",
      "  Using cached pickle_mixin-1.0.2-py3-none-any.whl\n",
      "Collecting fastapi\n",
      "  Using cached fastapi-0.70.0-py3-none-any.whl (51 kB)\n",
      "Collecting uvicorn\n",
      "  Using cached uvicorn-0.15.0-py3-none-any.whl (54 kB)\n",
      "Collecting Python-multipart\n",
      "  Using cached python_multipart-0.0.5-py3-none-any.whl\n",
      "Collecting ujson>=2.0.0\n",
      "  Using cached ujson-4.2.0-cp38-cp38-macosx_10_14_x86_64.whl (45 kB)\n",
      "Collecting grpcio==1.37.1\n",
      "  Using cached grpcio-1.37.1-cp38-cp38-macosx_10_10_x86_64.whl (3.9 MB)\n",
      "Collecting requests>=2.22.0\n",
      "  Using cached requests-2.26.0-py2.py3-none-any.whl (62 kB)\n",
      "Collecting mmh3\n",
      "  Using cached mmh3-3.0.0-cp38-cp38-macosx_10_9_x86_64.whl (12 kB)\n",
      "Collecting grpcio-tools==1.37.1\n",
      "  Using cached grpcio_tools-1.37.1-cp38-cp38-macosx_10_10_x86_64.whl (2.0 MB)\n",
      "Collecting pandas==1.2.4\n",
      "  Using cached pandas-1.2.4-cp38-cp38-macosx_10_9_x86_64.whl (10.5 MB)\n",
      "Requirement already satisfied: six>=1.5.2 in /Users/mengjiagu/opt/anaconda3/envs/test/lib/python3.8/site-packages (from grpcio==1.37.1->pymilvus==2.0.0rc8->-r requirements.txt (line 1)) (1.15.0)\n",
      "Requirement already satisfied: setuptools in /Users/mengjiagu/opt/anaconda3/envs/test/lib/python3.8/site-packages (from grpcio-tools==1.37.1->pymilvus==2.0.0rc8->-r requirements.txt (line 1)) (58.0.4)\n",
      "Collecting protobuf<4.0dev,>=3.5.0.post1\n",
      "  Using cached protobuf-3.19.1-cp38-cp38-macosx_10_9_x86_64.whl (1.0 MB)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /Users/mengjiagu/opt/anaconda3/envs/test/lib/python3.8/site-packages (from pandas==1.2.4->pymilvus==2.0.0rc8->-r requirements.txt (line 1)) (2.8.1)\n",
      "Collecting pytz>=2017.3\n",
      "  Using cached pytz-2021.3-py2.py3-none-any.whl (503 kB)\n",
      "Collecting scikit-learn\n",
      "  Downloading scikit_learn-1.0.1-cp38-cp38-macosx_10_13_x86_64.whl (7.9 MB)\n",
      "\u001b[K     |████████████████████████████████| 7.9 MB 6.5 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting pydantic!=1.7,!=1.7.1,!=1.7.2,!=1.7.3,!=1.8,!=1.8.1,<2.0.0,>=1.6.2\n",
      "  Using cached pydantic-1.8.2-cp38-cp38-macosx_10_9_x86_64.whl (2.6 MB)\n",
      "Collecting starlette==0.16.0\n",
      "  Downloading starlette-0.16.0-py3-none-any.whl (61 kB)\n",
      "\u001b[K     |████████████████████████████████| 61 kB 500 kB/s eta 0:00:01\n",
      "\u001b[?25hCollecting anyio<4,>=3.0.0\n",
      "  Downloading anyio-3.3.4-py3-none-any.whl (78 kB)\n",
      "\u001b[K     |████████████████████████████████| 78 kB 1.2 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting h11>=0.8\n",
      "  Using cached h11-0.12.0-py3-none-any.whl (54 kB)\n",
      "Collecting asgiref>=3.4.0\n",
      "  Downloading asgiref-3.4.1-py3-none-any.whl (25 kB)\n",
      "Collecting click>=7.0\n",
      "  Downloading click-8.0.3-py3-none-any.whl (97 kB)\n",
      "\u001b[K     |████████████████████████████████| 97 kB 1.0 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting sniffio>=1.1\n",
      "  Downloading sniffio-1.2.0-py3-none-any.whl (10 kB)\n",
      "Collecting idna>=2.8\n",
      "  Using cached idna-3.3-py3-none-any.whl (61 kB)\n",
      "Collecting typing-extensions>=3.7.4.3\n",
      "  Downloading typing_extensions-4.0.0-py3-none-any.whl (22 kB)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/mengjiagu/opt/anaconda3/envs/test/lib/python3.8/site-packages (from requests>=2.22.0->pymilvus==2.0.0rc8->-r requirements.txt (line 1)) (2020.6.20)\n",
      "Collecting urllib3<1.27,>=1.21.1\n",
      "  Using cached urllib3-1.26.7-py2.py3-none-any.whl (138 kB)\n",
      "Collecting charset-normalizer~=2.0.0\n",
      "  Using cached charset_normalizer-2.0.7-py3-none-any.whl (38 kB)\n",
      "Collecting threadpoolctl>=2.0.0\n",
      "  Downloading threadpoolctl-3.0.0-py3-none-any.whl (14 kB)\n",
      "Collecting joblib>=0.11\n",
      "  Downloading joblib-1.1.0-py2.py3-none-any.whl (306 kB)\n",
      "\u001b[K     |████████████████████████████████| 306 kB 1.1 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting scipy>=1.1.0\n",
      "  Using cached scipy-1.7.2-cp38-cp38-macosx_10_9_x86_64.whl (33.0 MB)\n",
      "Building wheels for collected packages: sklearn\n",
      "  Building wheel for sklearn (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for sklearn: filename=sklearn-0.0-py2.py3-none-any.whl size=1309 sha256=8fb0d8ca70282e67fb7fa97da0ca4388a4f178eef1ef0de2c7bcbf77e4972188\n",
      "  Stored in directory: /Users/mengjiagu/Library/Caches/pip/wheels/22/0b/40/fd3f795caaa1fb4c6cb738bc1f56100be1e57da95849bfc897\n",
      "Successfully built sklearn\n",
      "Installing collected packages: sniffio, numpy, idna, urllib3, typing-extensions, threadpoolctl, scipy, pytz, protobuf, joblib, grpcio, charset-normalizer, anyio, ujson, starlette, scikit-learn, requests, pydantic, pandas, mmh3, h11, grpcio-tools, click, asgiref, uvicorn, sklearn, Python-multipart, pymysql, pymilvus, pickle-mixin, fastapi\n",
      "Successfully installed Python-multipart-0.0.5 anyio-3.3.4 asgiref-3.4.1 charset-normalizer-2.0.7 click-8.0.3 fastapi-0.70.0 grpcio-1.37.1 grpcio-tools-1.37.1 h11-0.12.0 idna-3.3 joblib-1.1.0 mmh3-3.0.0 numpy-1.21.4 pandas-1.2.4 pickle-mixin-1.0.2 protobuf-3.19.1 pydantic-1.8.2 pymilvus-2.0.0rc8 pymysql-1.0.2 pytz-2021.3 requests-2.26.0 scikit-learn-1.0.1 scipy-1.7.2 sklearn-0.0 sniffio-1.2.0 starlette-0.16.0 threadpoolctl-3.0.0 typing-extensions-4.0.0 ujson-4.2.0 urllib3-1.26.7 uvicorn-0.15.0\n"
     ]
    }
   ],
   "source": [
    "! pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f8c5cda",
   "metadata": {},
   "source": [
    "### Start Milvus\n",
    "\n",
    "Download and save docker-compose.standalone.yml as docker-compose.yml. Start Milvus2.0 with docker-compose."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf19cd94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2021-11-16 14:44:16--  https://github.com/milvus-io/milvus/releases/download/v2.0.0-rc8/milvus-standalone-docker-compose.yml\n",
      "Connecting to 127.0.0.1:9999... connected.\n",
      "Proxy request sent, awaiting response... 302 Found\n",
      "Location: https://github-releases.githubusercontent.com/208728772/22e9d357-0e67-427d-8f31-7061b2e9e067?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211116%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211116T064225Z&X-Amz-Expires=300&X-Amz-Signature=33636b7e888304babcea6d0a4431cc4296f090a677754c0c3288b726268960e1&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=208728772&response-content-disposition=attachment%3B%20filename%3Dmilvus-standalone-docker-compose.yml&response-content-type=application%2Foctet-stream [following]\n",
      "--2021-11-16 14:44:17--  https://github-releases.githubusercontent.com/208728772/22e9d357-0e67-427d-8f31-7061b2e9e067?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211116%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211116T064225Z&X-Amz-Expires=300&X-Amz-Signature=33636b7e888304babcea6d0a4431cc4296f090a677754c0c3288b726268960e1&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=208728772&response-content-disposition=attachment%3B%20filename%3Dmilvus-standalone-docker-compose.yml&response-content-type=application%2Foctet-stream\n",
      "Connecting to 127.0.0.1:9999... connected.\n",
      "Proxy request sent, awaiting response... 200 OK\n",
      "Length: 1324 (1.3K) [application/octet-stream]\n",
      "Saving to: ‘docker-compose.yml’\n",
      "\n",
      "docker-compose.yml  100%[===================>]   1.29K  --.-KB/s    in 0s      \n",
      "\n",
      "2021-11-16 14:44:18 (105 MB/s) - ‘docker-compose.yml’ saved [1324/1324]\n",
      "\n",
      "Creating milvus-minio ... \n",
      "Creating milvus-etcd  ... \n",
      "\u001b[2BCreating milvus-standalone ... \u001b[0m\u001b[2A\u001b[2K\n",
      "\u001b[1Bting milvus-standalone ... \u001b[32mdone\u001b[0m"
     ]
    }
   ],
   "source": [
    "! wget https://github.com/milvus-io/milvus/releases/download/v2.0.0-rc8/milvus-standalone-docker-compose.yml -O docker-compose.yml\n",
    "! docker-compose up -d"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d11edf9",
   "metadata": {},
   "source": [
    "### Start Mysql\n",
    "\n",
    "Milvus2.0 does not suppport string for now. Start mysql as docker container to store and recall non-vector attributes of DNA sequences (eg. id, label/class)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6e9b030e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8d9ca3c221f923d21524916d0630daadd886745719df8349d1917f779e973543\r\n"
     ]
    }
   ],
   "source": [
    "!docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d --name mysql mysql:5.7"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9128fbfb",
   "metadata": {},
   "source": [
    "### Check Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "61716943",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONTAINER ID   IMAGE                                         COMMAND                  CREATED              STATUS                        PORTS                               NAMES\r\n",
      "8d9ca3c221f9   mysql:5.7                                     \"docker-entrypoint.s…\"   3 seconds ago        Up 3 seconds                  0.0.0.0:3306->3306/tcp, 33060/tcp   mysql\r\n",
      "98aadb1940c4   milvusdb/milvus:v2.0.0-rc8-20211104-d1f4106   \"/tini -- milvus run…\"   About a minute ago   Up About a minute             0.0.0.0:19530->19530/tcp            milvus-standalone\r\n",
      "638ee389dcbc   quay.io/coreos/etcd:v3.5.0                    \"etcd -advertise-cli…\"   About a minute ago   Up About a minute             2379-2380/tcp                       milvus-etcd\r\n",
      "01564e0bd86f   minio/minio:RELEASE.2020-12-03T00-03-10Z      \"/usr/bin/docker-ent…\"   About a minute ago   Up About a minute (healthy)   9000/tcp                            milvus-minio\r\n"
     ]
    }
   ],
   "source": [
    "!docker ps"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd40f465",
   "metadata": {},
   "source": [
    "## Code Overview\n",
    "\n",
    "### Connect to Servers\n",
    "\n",
    "Connect to servers with hosts & ports. In this case, the docker containers are running on localhost and the default ports."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "050822e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymilvus import connections\n",
    "import pymysql\n",
    "\n",
    "connections.connect(host='localhost', port='19530')\n",
    "conn = pymysql.connect(host='localhost', user='root', port=3306, password='123456', database='mysql',local_infile=True)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30d718d3",
   "metadata": {},
   "source": [
    "### Create Collection, Partitions, Index in Milvus\n",
    "\n",
    "#### 1. Create Collection\n",
    "\n",
    "Set collection name and dimension value. Create a collection with fields.\n",
    "- Collection name: dna_seq\n",
    "- Dimension: 768\n",
    "- Fields: pk (primary keys), embedding (dna sequence embeddings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "99dac31e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collection is successfully created: dna_seq\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "from pymilvus import utility, Collection, FieldSchema, DataType, CollectionSchema\n",
    "\n",
    "time.sleep(.1)\n",
    "\n",
    "collection_name = \"dna_seq\"\n",
    "dim = 768\n",
    "\n",
    "# Drop the previously stored collection for a clear run\n",
    "if utility.has_collection(collection_name) == True:\n",
    "    collection = Collection(collection_name)\n",
    "    collection.drop()\n",
    "\n",
    "# Set fields & schema\n",
    "all_fields = [\n",
    "        FieldSchema(name=\"pk\", dtype=DataType.INT64, is_primary=True),\n",
    "        FieldSchema(name=\"embedding\", dtype=DataType.FLOAT_VECTOR, dim=dim)\n",
    "        #schema.FieldSchema(name=\"class\", dtype=DataType.STRING)\n",
    "        ]\n",
    "default_schema = CollectionSchema(fields=all_fields, \n",
    "                                  description=\"DNA recognition: kmers & vectorizer\", \n",
    "                                  auto_id=False)\n",
    "\n",
    "# Create collection\n",
    "DNA_collection = Collection(name=collection_name, data=None, schema=default_schema)\n",
    "\n",
    "# Check if collection is successfully created\n",
    "if utility.has_collection(collection_name):\n",
    "    print(\n",
    "    \"Collection is successfully created: \" + collection_name)\n",
    "else:\n",
    "    raise Exception(\"Fail to create collection: \" + collection_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5eb9b187",
   "metadata": {},
   "source": [
    "#### 2. Create Partitions\n",
    "\n",
    "Create 3 partitions with proper names: human, chimp, dog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c71e4e6f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{\"name\": \"_default\", \"collection_name\": \"dna_seq\", \"description\": \"\"},\n",
       " {\"name\": \"human\", \"collection_name\": \"dna_seq\", \"description\": \"\"},\n",
       " {\"name\": \"chimp\", \"collection_name\": \"dna_seq\", \"description\": \"\"},\n",
       " {\"name\": \"dog\", \"collection_name\": \"dna_seq\", \"description\": \"\"}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_partition = DNA_collection.create_partition('human')\n",
    "chimp_partition = DNA_collection.create_partition('chimp')\n",
    "dog_partition = DNA_collection.create_partition('dog')\n",
    "\n",
    "DNA_collection.partitions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f678dddc",
   "metadata": {},
   "source": [
    "#### 3. Set Index\n",
    "\n",
    "Set index parameters after collection is created. Here index type is IVF_SQ8 and metric type is Inner Product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e0280182",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index is successfully set for collection dna_seq\n"
     ]
    }
   ],
   "source": [
    "index_params = {\n",
    "    'index_type': 'IVF_SQ8',\n",
    "    'params': {'nlist': 512},\n",
    "    'metric_type': 'IP'\n",
    "    }\n",
    "\n",
    "DNA_collection.create_index(field_name=\"embedding\", index_params=index_params)\n",
    "\n",
    "# Check if index is successfully set\n",
    "if DNA_collection.has_index():\n",
    "    print(\"Index is successfully set for collection \" + collection_name)\n",
    "else:\n",
    "    raise Exception(\"Fail to set index for collection \" + collection_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a32fed43",
   "metadata": {},
   "source": [
    "### Create Table in Mysql\n",
    "\n",
    "Create a table with collection name in mySQL to store milvus ids (i.e. field \"pk\") and corresponding labels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4dbc3a1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "create MySQL table successfully!\n"
     ]
    }
   ],
   "source": [
    "# Delete previously stored table for a clean run\n",
    "drop_table = \"DROP TABLE IF EXISTS \" + collection_name + \";\"\n",
    "cursor.execute(drop_table)\n",
    "\n",
    "try:\n",
    "    sql = \"CREATE TABLE if not exists \" + collection_name + \" (pk TEXT, label TEXT);\"\n",
    "    cursor.execute(sql)\n",
    "    print(\"create MySQL table successfully!\")\n",
    "except Exception as e:\n",
    "    print(\"can't create a MySQL table: \", e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10b04c32",
   "metadata": {},
   "source": [
    "### Process & Store Datasets\n",
    "\n",
    "#### 1. Get Data\n",
    "\n",
    "Read data from text files as dataframes. Rebuild data and replace original columns with:\n",
    "- sequence --> subsequences by [k-mer](https://en.wikipedia.org/wiki/K-mer#:~:text=Usually%2C%20the%20term%20k%2Dmer,total%20possible%20k%2Dmers%2C%20where) (k=5)\n",
    "- class --> label declaring organism & class (e.g. human: 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6be95f2b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                            sequence     label  \\\n",
      "0  ATGGAAAATGGCTGCCTGCTTAACTATCTCAGGGAGAATAAAGGAA...  human: 1   \n",
      "1  ATGCGTGGCTTCAACCTGCTCCTCTTCTGGGGATGTTGTGTTATGC...  human: 0   \n",
      "2  NGGCTCTGGGGGCTCCTTCCCCCTGGGCCACCAGCCCTGGCTTGGA...  human: 1   \n",
      "3  ATGGCCCGAAGACCCCGGCACAGCATATATAGCAGTGACGAGGATG...  human: 6   \n",
      "4  TGCTGTGGTGCCATCCTGTTCCCCGTAGTCTGGTCCATCCGGCATC...  human: 0   \n",
      "\n",
      "                                               kmers  \n",
      "0  [ATGG, TGGA, GGAA, GAAA, AAAA, AAAT, AATG, ATG...  \n",
      "1  [ATGC, TGCG, GCGT, CGTG, GTGG, TGGC, GGCT, GCT...  \n",
      "2  [NGGC, GGCT, GCTC, CTCT, TCTG, CTGG, TGGG, GGG...  \n",
      "3  [ATGG, TGGC, GGCC, GCCC, CCCG, CCGA, CGAA, GAA...  \n",
      "4  [TGCT, GCTG, CTGT, TGTG, GTGG, TGGT, GGTG, GTG...  \n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Function to get k-mers for sequence s\n",
    "def build_kmers(s, k):\n",
    "    kmers = []\n",
    "    n = len(s) - k + 1\n",
    "\n",
    "    for i in range(n):\n",
    "        kmer = s[i : i+k].upper()\n",
    "        kmers.append(kmer)\n",
    "\n",
    "    return kmers\n",
    "\n",
    "# Function to replace sequence column with kmers in df\n",
    "def seq_to_kmers(df):\n",
    "    df['kmers'] = df.apply(lambda x: build_kmers(x['sequence'], 4), axis =1)\n",
    "    df = df.drop(['sequence'],axis=1)\n",
    "\n",
    "\n",
    "# Read files\n",
    "human = pd.read_table('./data/human_data.txt')\n",
    "human = human.sample(frac=1, random_state=2021).reset_index(drop=True)\n",
    "chimp = pd.read_table('./data/chimp_data.txt')\n",
    "dog = pd.read_table('./data/dog_data.txt')\n",
    "\n",
    "# Replace classes with labels (organism: class)\n",
    "human['label']=['human: ' + str(x) for x in human['class']]\n",
    "human = human.drop(['class'], axis=1)\n",
    "chimp['label']=['chimp: ' + str(x) for x in chimp['class']]\n",
    "chimp = chimp.drop(['class'], axis=1)\n",
    "dog['label']=['dog: ' + str(x) for x in dog['class']]\n",
    "dog = dog.drop(['class'], axis=1)\n",
    "\n",
    "seq_to_kmers(human)\n",
    "seq_to_kmers(chimp)\n",
    "seq_to_kmers(dog)\n",
    "\n",
    "# Combine all dataframes\n",
    "#df = human.append(chimp).append(dog)\n",
    "#df = df.sample(frac=1,random_state=1)\n",
    "#seq_to_kmers(df)\n",
    "\n",
    "print(human.head())\n",
    "#print(chimp.head())\n",
    "#print(dog.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3ecdfa4",
   "metadata": {},
   "source": [
    "Get lists of texts for DNA sequences in k-mers & labels. Split 20 human data to test search performance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "14f3aba4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "train row count: 6101\n",
      "human(3609) chimp(1675) dog(817)\n",
      "test row count: 20\n"
     ]
    }
   ],
   "source": [
    "# Get lists of sequences in k-mers and labels in text from dataframe\n",
    "def mydata(df):\n",
    "    texts = []\n",
    "    labels = []\n",
    "    words = list(df['kmers']) # list of all sequences in kmers\n",
    "\n",
    "    for i in range(len(words)):\n",
    "        texts.append(' '.join(words[i])) \n",
    "    \n",
    "    for x in df['label']:\n",
    "        labels.append(x)\n",
    "\n",
    "    if len(texts)!=len(labels):\n",
    "        raise Exception(\"Texts & labels length are not equal!\")\n",
    "        \n",
    "    return (texts, labels)\n",
    "    \n",
    "human_texts, human_labels = mydata(human)\n",
    "chimp_texts, chimp_labels = mydata(chimp)\n",
    "dog_texts, dog_labels = mydata(dog)\n",
    "\n",
    "# Split human data to test search performance\n",
    "test_texts = human_texts[-20:]\n",
    "actual_labels = human_labels[-20:]\n",
    "human_texts = human_texts[:-20]\n",
    "human_labels = human_labels[:-20]\n",
    "\n",
    "train_texts = human_texts + chimp_texts + dog_texts\n",
    "train_labels = human_labels + chimp_labels + dog_labels\n",
    "\n",
    "print(\"train row count:\", len(train_texts))\n",
    "print(\"human({})\".format(str(len(human_texts)))\n",
    "      +\" chimp({})\".format(str(len(chimp_texts)))\n",
    "      +\" dog({})\".format(str(len(dog_texts))))\n",
    "print(\"test row count:\", len(test_texts))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ee3c37d",
   "metadata": {},
   "source": [
    "#### 2. Generate Embeddings\n",
    "\n",
    "Extract features for DNA sequences (after k-mers) by `CountVectorizer` with previously declared dimension. Normalize output by `sklearn.preprocessing` to get final embeddings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c75165f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import CountVectorizer\n",
    "from sklearn import preprocessing\n",
    "\n",
    "# Transform sequences in kmers to vectors\n",
    "def char_to_vec(v_model, text):\n",
    "    V = v_model.transform(text).toarray()\n",
    "    #features = vectorizer.get_feature_names()\n",
    "    embeddings = preprocessing.normalize(V)\n",
    "    return embeddings\n",
    "\n",
    "# Train vectorizer model \n",
    "vectorizer = CountVectorizer(ngram_range=(4,4), max_features=dim)\n",
    "X = vectorizer.fit_transform(train_texts).toarray()\n",
    "train_emb = list(preprocessing.normalize(X))\n",
    "# print(vectorizer.get_feature_names())\n",
    "\n",
    "human_emb = train_emb[:len(human_texts)]\n",
    "chimp_emb = train_emb[len(human_texts):(len(human_texts)+len(chimp_texts))]\n",
    "dog_emb = train_emb[(len(human_texts)+len(chimp_texts)):len(train_texts)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8846ba6c",
   "metadata": {},
   "source": [
    "#### 3. Insert data\n",
    "\n",
    "##### Insert to Milvus\n",
    "\n",
    "Insert all embeddings to corresponding partitions with proper primary keys. Don't insert if there exists previous data in collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "86c223ba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{\"name\": \"_default\", \"collection_name\": \"dna_seq\", \"description\": \"\"}, {\"name\": \"human\", \"collection_name\": \"dna_seq\", \"description\": \"\"}, {\"name\": \"chimp\", \"collection_name\": \"dna_seq\", \"description\": \"\"}, {\"name\": \"dog\", \"collection_name\": \"dna_seq\", \"description\": \"\"}]\n"
     ]
    }
   ],
   "source": [
    "human_pk = [x for x in range(len(human_emb))]\n",
    "chimp_pk = [x for x in range(len(human_emb), len(human_emb)+len(chimp_emb))]\n",
    "dog_pk = [x for x in range(len(human_emb)+len(chimp_emb), len(train_emb))]\n",
    "\n",
    "if DNA_collection.num_entities == 0:\n",
    "    DNA_human = DNA_collection.insert([human_pk, human_emb], partition_name='human')\n",
    "    DNA_chimp = DNA_collection.insert([chimp_pk, chimp_emb], partition_name='chimp')\n",
    "    DNA_dog = DNA_collection.insert([dog_pk, dog_emb], partition_name='dog')\n",
    "\n",
    "    if DNA_collection.is_empty:\n",
    "        print(\"Insert collection failed.\")\n",
    "    else:\n",
    "        print(DNA_collection.partitions)\n",
    "else:\n",
    "    print(\"Previous data in this collection!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2b0585e",
   "metadata": {},
   "source": [
    "##### Insert to Mysql\n",
    "\n",
    "Insert primary keys in Milvus and corresponding labels into Mysql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0e62f571",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MYSQL loads data to table: dna_seq successfully\n"
     ]
    }
   ],
   "source": [
    "import os \n",
    "\n",
    "# Combine pk and label into a list\n",
    "def format_data(pk, label):\n",
    "    data = []\n",
    "    for i in range(len(pk)):\n",
    "        value = (str(pk[i]), label[i])\n",
    "        data.append(value)\n",
    "    return data\n",
    "\n",
    "def load_data_to_mysql(cursor, conn, table_name, data):\n",
    "    sql = \"insert into \" + table_name + \" (pk,label) values (%s,%s);\"\n",
    "    try:\n",
    "        cursor.executemany(sql, data)\n",
    "        conn.commit()\n",
    "        print(\"MYSQL loads data to table: {} successfully\".format(table_name))\n",
    "    except Exception as e:\n",
    "        print(\"MYSQL ERROR: {} with sql: {}\".format(e, sql))\n",
    "\n",
    "all_pk = human_pk + chimp_pk + dog_pk\n",
    "load_data_to_mysql(cursor, conn, collection_name, format_data(all_pk, train_labels))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7f1cea5",
   "metadata": {},
   "source": [
    "### Search\n",
    "\n",
    "Load collection. Set search parameters with Inner Product as metric_type and nprobe of 20."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "086c7e21",
   "metadata": {},
   "outputs": [],
   "source": [
    "DNA_collection.load()\n",
    "search_params = {\"metric_type\": \"IP\", \"params\": {\"nprobe\": 20}}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5d460f2",
   "metadata": {},
   "source": [
    "#### 1. Classify DNA Sequences\n",
    "\n",
    "The aim is to classify 20 human DNA sequences with labels. Inputs are pre-processed subsequences in text by k-mers (k=4)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "95512ddc",
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ATGT TGTC GTCT TCTG CTGG TGGG GGGG GGGT GGTG G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ATGG TGGA GGAT GATG ATGA TGAA GAAG AAGA AGAA G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GCCG CCGA CGAG GAGT AGTA GTAT TATG ATGA TGAA G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ATGG TGGC GGCC GCCC CCCA CCAG CAGC AGCC GCCC C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NNTG NTGA TGAC GACA ACAG CAGC AGCA GCAG CAGT A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0\n",
       "0  ATGT TGTC GTCT TCTG CTGG TGGG GGGG GGGT GGTG G...\n",
       "1  ATGG TGGA GGAT GATG ATGA TGAA GAAG AAGA AGAA G...\n",
       "2  GCCG CCGA CGAG GAGT AGTA GTAT TATG ATGA TGAA G...\n",
       "3  ATGG TGGC GGCC GCCC CCCA CCAG CAGC AGCC GCCC C...\n",
       "4  NNTG NTGA TGAC GACA ACAG CAGC AGCA GCAG CAGT A..."
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(test_texts).head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91054c48",
   "metadata": {},
   "source": [
    "Transform each input to vector with pre-trained vectorizer model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "307498e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_vector(text, vectorizer):\n",
    "    x = vectorizer.transform(text).toarray()\n",
    "    return list(preprocessing.normalize(x))\n",
    "\n",
    "embs = get_vector(test_texts, vectorizer)\n",
    "test_emb = []\n",
    "for x in embs:\n",
    "    test_emb.append(list(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c4493df",
   "metadata": {},
   "source": [
    "Search for top 10 results in human partition for each input vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2a57e056",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Search...\n",
      "search latency = 0.3584s\n"
     ]
    }
   ],
   "source": [
    "start_time = time.time()\n",
    "print(f\"\\nSearch...\")\n",
    "# define output_fields of search result\n",
    "res = DNA_collection.search(test_emb, \"embedding\", search_params,\n",
    "                                    limit=10, partition_names=['human'])\n",
    "end_time = time.time()\n",
    "print(\"search latency = %.4fs\" % (end_time - start_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f0e9773",
   "metadata": {},
   "source": [
    "Display search result (recall labels from mysql by result ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4adce68e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Search for 'human: 1'\n",
      "[label] distance\n",
      "['human: 1'] 0.9445110559463501\n",
      "['human: 1'] 0.7415125966072083\n",
      "['human: 1'] 0.6539482474327087\n",
      "['human: 1'] 0.5537956953048706\n",
      "['human: 1'] 0.5494828820228577\n",
      "['human: 5'] 0.34415939450263977\n",
      "['human: 3'] 0.33702877163887024\n",
      "['human: 5'] 0.3339894711971283\n",
      "['human: 5'] 0.33389437198638916\n",
      "['human: 5'] 0.33303314447402954\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.8699960708618164\n",
      "['human: 6'] 0.6718601584434509\n",
      "['human: 6'] 0.657863199710846\n",
      "['human: 6'] 0.6572762131690979\n",
      "['human: 6'] 0.6064621806144714\n",
      "['human: 6'] 0.3899722397327423\n",
      "['human: 6'] 0.3887454569339752\n",
      "['human: 6'] 0.3793696463108063\n",
      "['human: 6'] 0.3793696463108063\n",
      "['human: 6'] 0.37457188963890076\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.5615419149398804\n",
      "['human: 0'] 0.39085471630096436\n",
      "['human: 0'] 0.356171190738678\n",
      "['human: 0'] 0.35518762469291687\n",
      "['human: 0'] 0.346833735704422\n",
      "['human: 0'] 0.33972054719924927\n",
      "['human: 0'] 0.25198104977607727\n",
      "['human: 4'] 0.23797142505645752\n",
      "['human: 1'] 0.23127681016921997\n",
      "['human: 1'] 0.2229383885860443\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.47336268424987793\n",
      "['human: 1'] 0.4200447201728821\n",
      "['human: 0'] 0.41692596673965454\n",
      "['human: 1'] 0.41631579399108887\n",
      "['human: 1'] 0.4141400158405304\n",
      "['human: 5'] 0.4116525650024414\n",
      "['human: 6'] 0.4114045798778534\n",
      "['human: 5'] 0.4113733172416687\n",
      "['human: 1'] 0.41096600890159607\n",
      "['human: 6'] 0.410582959651947\n",
      "\n",
      "Search for 'human: 3'\n",
      "[label] distance\n",
      "['human: 3'] 0.5426241159439087\n",
      "['human: 2'] 0.3214586079120636\n",
      "['human: 2'] 0.3209339678287506\n",
      "['human: 0'] 0.315309077501297\n",
      "['human: 2'] 0.3142853379249573\n",
      "['human: 0'] 0.3101691007614136\n",
      "['human: 0'] 0.309027761220932\n",
      "['human: 0'] 0.30830782651901245\n",
      "['human: 1'] 0.3074454665184021\n",
      "['human: 2'] 0.3067685663700104\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9709874987602234\n",
      "['human: 6'] 0.9232423305511475\n",
      "['human: 6'] 0.737858772277832\n",
      "['human: 6'] 0.724646806716919\n",
      "['human: 6'] 0.6715950965881348\n",
      "['human: 6'] 0.6481513977050781\n",
      "['human: 6'] 0.646144449710846\n",
      "['human: 6'] 0.6449624300003052\n",
      "['human: 6'] 0.6134665012359619\n",
      "['human: 2'] 0.5037984251976013\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9986848831176758\n",
      "['human: 6'] 0.9431586265563965\n",
      "['human: 6'] 0.7079516053199768\n",
      "['human: 2'] 0.6238735914230347\n",
      "['human: 4'] 0.6178328990936279\n",
      "['human: 4'] 0.6163788437843323\n",
      "['human: 6'] 0.6151997447013855\n",
      "['human: 2'] 0.6113831400871277\n",
      "['human: 1'] 0.6057740449905396\n",
      "['human: 1'] 0.6056747436523438\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9997666478157043\n",
      "['human: 6'] 0.9828557372093201\n",
      "['human: 6'] 0.9828557372093201\n",
      "['human: 6'] 0.967312753200531\n",
      "['human: 6'] 0.967312753200531\n",
      "['human: 6'] 0.9542778730392456\n",
      "['human: 6'] 0.9537703990936279\n",
      "['human: 6'] 0.9457848072052002\n",
      "['human: 6'] 0.9457848072052002\n",
      "['human: 6'] 0.9415895342826843\n",
      "\n",
      "Search for 'human: 1'\n",
      "[label] distance\n",
      "['human: 1'] 0.9817506074905396\n",
      "['human: 1'] 0.9708588123321533\n",
      "['human: 1'] 0.9627314805984497\n",
      "['human: 1'] 0.9566783905029297\n",
      "['human: 1'] 0.8783367276191711\n",
      "['human: 4'] 0.601869523525238\n",
      "['human: 4'] 0.5995450019836426\n",
      "['human: 1'] 0.590391993522644\n",
      "['human: 1'] 0.5834988951683044\n",
      "['human: 6'] 0.558573842048645\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9590650200843811\n",
      "['human: 6'] 0.8682348132133484\n",
      "['human: 6'] 0.6882621049880981\n",
      "['human: 6'] 0.6626104116439819\n",
      "['human: 4'] 0.5646600127220154\n",
      "['human: 4'] 0.5624855756759644\n",
      "['human: 4'] 0.5584137439727783\n",
      "['human: 3'] 0.5412178635597229\n",
      "['human: 3'] 0.5394489765167236\n",
      "['human: 0'] 0.5345959663391113\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.5229933261871338\n",
      "['human: 0'] 0.28829848766326904\n",
      "['human: 5'] 0.274934858083725\n",
      "['human: 0'] 0.2729986608028412\n",
      "['human: 0'] 0.2661600410938263\n",
      "['human: 5'] 0.26505494117736816\n",
      "['human: 5'] 0.26504114270210266\n",
      "['human: 1'] 0.26143163442611694\n",
      "['human: 1'] 0.2581135928630829\n",
      "['human: 1'] 0.25534507632255554\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.4450095295906067\n",
      "['human: 6'] 0.4450095295906067\n",
      "['human: 6'] 0.34046071767807007\n",
      "['human: 6'] 0.3396899998188019\n",
      "['human: 2'] 0.33469900488853455\n",
      "['human: 6'] 0.33417654037475586\n",
      "['human: 2'] 0.3288331627845764\n",
      "['human: 2'] 0.32753321528434753\n",
      "['human: 2'] 0.32753321528434753\n",
      "['human: 6'] 0.3269915282726288\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.5469512939453125\n",
      "['human: 0'] 0.42223647236824036\n",
      "['human: 1'] 0.3834993243217468\n",
      "['human: 1'] 0.38337206840515137\n",
      "['human: 0'] 0.3804449737071991\n",
      "['human: 5'] 0.38019347190856934\n",
      "['human: 5'] 0.3796458840370178\n",
      "['human: 5'] 0.3781411945819855\n",
      "['human: 4'] 0.37355852127075195\n",
      "['human: 4'] 0.37032297253608704\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.944923996925354\n",
      "['human: 4'] 0.8561531901359558\n",
      "['human: 4'] 0.8169742226600647\n",
      "['human: 4'] 0.7614181041717529\n",
      "['human: 4'] 0.7516887187957764\n",
      "['human: 4'] 0.7512236833572388\n",
      "['human: 4'] 0.7125766277313232\n",
      "['human: 4'] 0.696834921836853\n",
      "['human: 4'] 0.6849642992019653\n",
      "['human: 4'] 0.6619492173194885\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.8867402672767639\n",
      "['human: 4'] 0.8112719058990479\n",
      "['human: 4'] 0.6163673400878906\n",
      "['human: 5'] 0.39335885643959045\n",
      "['human: 0'] 0.3900836110115051\n",
      "['human: 0'] 0.38901132345199585\n",
      "['human: 6'] 0.3734312653541565\n",
      "['human: 6'] 0.37068477272987366\n",
      "['human: 0'] 0.36751610040664673\n",
      "['human: 0'] 0.365020215511322\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.9747942686080933\n",
      "['human: 4'] 0.9184384346008301\n",
      "['human: 4'] 0.6930786371231079\n",
      "['human: 4'] 0.5876184701919556\n",
      "['human: 4'] 0.5809458494186401\n",
      "['human: 4'] 0.564608633518219\n",
      "['human: 4'] 0.550567090511322\n",
      "['human: 4'] 0.5500834584236145\n",
      "['human: 4'] 0.532068133354187\n",
      "['human: 4'] 0.5124872922897339\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.543660044670105\n",
      "['human: 4'] 0.5416793823242188\n",
      "['human: 4'] 0.5401456356048584\n",
      "['human: 4'] 0.4861065745353699\n",
      "['human: 4'] 0.3666258752346039\n",
      "['human: 6'] 0.35624048113822937\n",
      "['human: 4'] 0.33977705240249634\n",
      "['human: 4'] 0.33658263087272644\n",
      "['human: 2'] 0.3341104984283447\n",
      "['human: 6'] 0.3326117992401123\n",
      "\n",
      "Search for 'human: 2'\n",
      "[label] distance\n",
      "['human: 2'] 0.8951742649078369\n",
      "['human: 2'] 0.7860726118087769\n",
      "['human: 2'] 0.6931322813034058\n",
      "['human: 2'] 0.6931322813034058\n",
      "['human: 2'] 0.6624330282211304\n",
      "['human: 2'] 0.623199462890625\n",
      "['human: 2'] 0.36748045682907104\n",
      "['human: 5'] 0.3443704843521118\n",
      "['human: 6'] 0.34293484687805176\n",
      "['human: 5'] 0.3383386433124542\n",
      "\n",
      "Search for 'human: 3'\n",
      "[label] distance\n",
      "['human: 3'] 0.9157018065452576\n",
      "['human: 4'] 0.47097501158714294\n",
      "['human: 4'] 0.47090038657188416\n",
      "['human: 3'] 0.44358134269714355\n",
      "['human: 3'] 0.44295573234558105\n",
      "['human: 2'] 0.43643444776535034\n",
      "['human: 2'] 0.4340135455131531\n",
      "['human: 4'] 0.43264079093933105\n",
      "['human: 5'] 0.42454779148101807\n",
      "['human: 3'] 0.4200666546821594\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.988943338394165\n",
      "['human: 0'] 0.9813806414604187\n",
      "['human: 0'] 0.7580568790435791\n",
      "['human: 0'] 0.7055071592330933\n",
      "['human: 0'] 0.6831194162368774\n",
      "['human: 0'] 0.5104460716247559\n",
      "['human: 0'] 0.5079376697540283\n",
      "['human: 5'] 0.5026563405990601\n",
      "['human: 4'] 0.4903676211833954\n",
      "['human: 4'] 0.48899298906326294\n"
     ]
    }
   ],
   "source": [
    "def get_label_by_pk(cursor, m_pk, table_name):\n",
    "    sql = \"select label from \" + table_name + \" where pk=\" + str(m_pk) +\";\"\n",
    "    try:\n",
    "        cursor.execute(sql)\n",
    "        myresult = cursor.fetchall()\n",
    "        myresult = [x[0] for x in myresult]\n",
    "        return myresult\n",
    "    except Exception as e:\n",
    "        print(\"MYSQL ERROR: {} with sql: {}\".format(e, sql))\n",
    "        \n",
    "for i in range(len(res)):\n",
    "    print(\"\\nSearch for '{}'\".format(actual_labels[i]))\n",
    "    print('[label]', 'distance')\n",
    "    for x in res[i]:\n",
    "        C = get_label_by_pk(cursor, str(x.id), collection_name)\n",
    "        D = x.distance\n",
    "        print(C, D)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a036bafe",
   "metadata": {},
   "source": [
    "#### 2. Compare Similarity\n",
    "\n",
    "According to IP distance value got from similarity search in Milvus, we can compare similarities between organisms.\n",
    "\n",
    "Search top 1 results for 800 chimp/dog vectors in human partition. Average distances to reflect how close between chimp/dog and human DNA sequences. The larger the IP distance, the closer between organisims with respect to DNA sequence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "654eed93",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chimp-human similarity score: 0.9690572810918092\n",
      "dog-human similarity score: 0.7043551710620523\n"
     ]
    }
   ],
   "source": [
    "chimp_search = []\n",
    "dog_search = []\n",
    "for x in chimp_emb[:800]:\n",
    "    chimp_search.append(list(x))\n",
    "for x in dog_emb[:800]:\n",
    "    dog_search.append(list(x))\n",
    "\n",
    "chimp_res = DNA_collection.search(chimp_search, \"embedding\", search_params,\n",
    "                                    limit=1, partition_names=['human'])\n",
    "dog_res = DNA_collection.search(dog_search, \"embedding\", search_params,\n",
    "                                    limit=1, partition_names=['human'])\n",
    "\n",
    "def similarity(search_res):\n",
    "    total_d = 0\n",
    "    for hits in search_res:\n",
    "        total_d = total_d + sum(hits.distances)/len(hits)\n",
    "    return total_d/(len(search_res))\n",
    "\n",
    "print('chimp-human similarity score:', similarity(chimp_res))\n",
    "print('dog-human similarity score:', similarity(dog_res))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2018703e",
   "metadata": {},
   "source": [
    "From above similarity scores from milvus, we can tell that chimpanzee is closer to human compared to dog, fitting the [biological research facts](https://education.seattlepi.com/animals-share-human-dna-sequences-6693.html) \"...humans share 98.8 percent of their DNA with bonobos and chimpanzees...Humans and dogs share 84 percent of their DNA\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:test]",
   "language": "python",
   "name": "conda-env-test-py"
  },
  "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}

一键复制编辑原始数据按行查看历史

提交于 2022-03-17 10:20 . first code

https://gitee.com/wangchunyi/artificial-intelligence.git

git@gitee.com:wangchunyi/artificial-intelligence.git

wangchunyi

artificial-intelligence

milvus应用案例

master

wcyxtt / milvus应用案例

简介

发行版

贡献者

近期动态

wcyxtt / milvus应用案例 .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

wcyxtt / milvus应用案例