solutions/dna_sequence_classification/dna_sequence_classification.ipynb

{
 "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",
    "## 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."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5acd878c",
   "metadata": {},
   "source": [
    "|    Packages    |     Servers    |\n",
    "| --------------- | -------------- |\n",
    "| pymilvus==2.0.0rc5 | milvus-2.0.0-rc5|\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.\n",
    "Since v2.0.0rc5, pymilvus-orm is merged to pymilvus. Uninstall previous pymilvus-orm if needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eab3ff3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymilvus==2.0.0rc5\n",
      "  Using cached pymilvus-2.0.0rc5-py3-none-any.whl (115 kB)\n",
      "Collecting sklearn\n",
      "  Using cached sklearn-0.0-py2.py3-none-any.whl\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.2-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",
      "  Downloading fastapi-0.68.1-py3-none-any.whl (52 kB)\n",
      "\u001b[K     |████████████████████████████████| 52 kB 146 kB/s eta 0:00:01\n",
      "\u001b[?25hCollecting pandas==1.2.4\n",
      "  Using cached pandas-1.2.4-cp38-cp38-macosx_10_9_x86_64.whl (10.5 MB)\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 mmh3\n",
      "  Using cached mmh3-3.0.0-cp38-cp38-macosx_10_9_x86_64.whl (12 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 ujson>=2.0.0\n",
      "  Using cached ujson-4.1.0-cp38-cp38-macosx_10_14_x86_64.whl (45 kB)\n",
      "Requirement already satisfied: six>=1.5.2 in /Users/mengjiagu/opt/anaconda3/envs/dna/lib/python3.8/site-packages (from grpcio==1.37.1->pymilvus==2.0.0rc5->-r requirements.txt (line 1)) (1.15.0)\n",
      "Requirement already satisfied: setuptools in /Users/mengjiagu/opt/anaconda3/envs/dna/lib/python3.8/site-packages (from grpcio-tools==1.37.1->pymilvus==2.0.0rc5->-r requirements.txt (line 1)) (52.0.0.post20210125)\n",
      "Collecting protobuf<4.0dev,>=3.5.0.post1\n",
      "  Using cached protobuf-3.17.3-cp38-cp38-macosx_10_9_x86_64.whl (1.0 MB)\n",
      "Collecting pytz>=2017.3\n",
      "  Using cached pytz-2021.1-py2.py3-none-any.whl (510 kB)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /Users/mengjiagu/opt/anaconda3/envs/dna/lib/python3.8/site-packages (from pandas==1.2.4->pymilvus==2.0.0rc5->-r requirements.txt (line 1)) (2.8.1)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/mengjiagu/opt/anaconda3/envs/dna/lib/python3.8/site-packages (from requests>=2.22.0->pymilvus==2.0.0rc5->-r requirements.txt (line 1)) (2020.6.20)\n",
      "Collecting charset-normalizer~=2.0.0\n",
      "  Using cached charset_normalizer-2.0.4-py3-none-any.whl (36 kB)\n",
      "Collecting idna<4,>=2.5\n",
      "  Using cached idna-3.2-py3-none-any.whl (59 kB)\n",
      "Collecting urllib3<1.27,>=1.21.1\n",
      "  Using cached urllib3-1.26.6-py2.py3-none-any.whl (138 kB)\n",
      "Collecting starlette==0.14.2\n",
      "  Using cached starlette-0.14.2-py3-none-any.whl (60 kB)\n",
      "Collecting 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 typing-extensions>=3.7.4.3\n",
      "  Downloading typing_extensions-3.10.0.2-py3-none-any.whl (26 kB)\n",
      "Collecting scikit-learn\n",
      "  Using cached scikit_learn-0.24.2-cp38-cp38-macosx_10_13_x86_64.whl (7.2 MB)\n",
      "Collecting scipy>=0.19.1\n",
      "  Using cached scipy-1.7.1-cp38-cp38-macosx_10_9_x86_64.whl (32.6 MB)\n",
      "Collecting threadpoolctl>=2.0.0\n",
      "  Using cached threadpoolctl-2.2.0-py3-none-any.whl (12 kB)\n",
      "Collecting joblib>=0.11\n",
      "  Using cached joblib-1.0.1-py3-none-any.whl (303 kB)\n",
      "Installing collected packages: numpy, urllib3, typing-extensions, threadpoolctl, scipy, pytz, protobuf, joblib, idna, grpcio, charset-normalizer, ujson, starlette, scikit-learn, requests, pydantic, pandas, mmh3, grpcio-tools, sklearn, pymysql, pymilvus, pickle-mixin, fastapi\n",
      "Successfully installed charset-normalizer-2.0.4 fastapi-0.68.1 grpcio-1.37.1 grpcio-tools-1.37.1 idna-3.2 joblib-1.0.1 mmh3-3.0.0 numpy-1.21.2 pandas-1.2.4 pickle-mixin-1.0.2 protobuf-3.17.3 pydantic-1.8.2 pymilvus-2.0.0rc5 pymysql-1.0.2 pytz-2021.1 requests-2.26.0 scikit-learn-0.24.2 scipy-1.7.1 sklearn-0.0 starlette-0.14.2 threadpoolctl-2.2.0 typing-extensions-3.10.0.2 ujson-4.1.0 urllib3-1.26.6\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": 2,
   "id": "bf19cd94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2021-09-02 11:45:15--  https://github.com/milvus-io/milvus/releases/download/v2.0.0-rc5-hotfix1/milvus-standalone-docker-compose.yml\n",
      "Resolving github.com (github.com)... 10.16.100.206\n",
      "Connecting to github.com (github.com)|10.16.100.206|:443... connected.\n",
      "HTTP request sent, awaiting response... 302 Found\n",
      "Location: https://github-releases.githubusercontent.com/208728772/3ea455ae-d2d4-4f6d-9041-b35b940074e3?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210902%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210902T034516Z&X-Amz-Expires=300&X-Amz-Signature=683ac0bcc3494b0d40808fdf64a386705344c25659ec11c26c6f64a6eb1f10b7&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-09-02 11:45:16--  https://github-releases.githubusercontent.com/208728772/3ea455ae-d2d4-4f6d-9041-b35b940074e3?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210902%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210902T034516Z&X-Amz-Expires=300&X-Amz-Signature=683ac0bcc3494b0d40808fdf64a386705344c25659ec11c26c6f64a6eb1f10b7&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",
      "Resolving github-releases.githubusercontent.com (github-releases.githubusercontent.com)... 10.16.165.169\n",
      "Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|10.16.165.169|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1184 (1.2K) [application/octet-stream]\n",
      "Saving to: ‘docker-compose.yml’\n",
      "\n",
      "docker-compose.yml  100%[===================>]   1.16K  --.-KB/s    in 0s      \n",
      "\n",
      "2021-09-02 11:45:18 (17.9 MB/s) - ‘docker-compose.yml’ saved [1184/1184]\n",
      "\n",
      "\u001b[1A\u001b[1B\u001b[0G\u001b[?25l[+] Running 0/0\n",
      "\u001b[37m ⠋ Container milvus-etcd   Creating                                        0.1s\n",
      "\u001b[0m\u001b[37m ⠋ Container milvus-minio  Creating                                        0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 1/2\n",
      "\u001b[37m ⠿ Container milvus-etcd        Starting                                   0.2s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-minio       Starting                                   0.2s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 1/3\n",
      "\u001b[37m ⠿ Container milvus-etcd        Starting                                   0.3s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-minio       Starting                                   0.3s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 1/3\n",
      "\u001b[37m ⠿ Container milvus-etcd        Starting                                   0.4s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-minio       Starting                                   0.4s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 1/3\n",
      "\u001b[37m ⠿ Container milvus-etcd        Starting                                   0.5s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-minio       Starting                                   0.5s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 1/3\n",
      "\u001b[37m ⠿ Container milvus-etcd        Starting                                   0.6s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-minio       Starting                                   0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Created                                    0.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 2/3\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-standalone  Starting                                   1.1s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 2/3\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-standalone  Starting                                   1.2s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 2/3\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-standalone  Starting                                   1.3s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l[+] Running 2/3\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[37m ⠿ Container milvus-standalone  Starting                                   1.4s\n",
      "\u001b[0m\u001b[?25h\u001b[1A\u001b[1A\u001b[1A\u001b[1A\u001b[0G\u001b[?25l\u001b[34m[+] Running 3/3\u001b[0m\n",
      "\u001b[34m ⠿ Container milvus-etcd        Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-minio       Started                                    0.6s\n",
      "\u001b[0m\u001b[34m ⠿ Container milvus-standalone  Started                                    1.4s\n",
      "\u001b[0m\u001b[?25h"
     ]
    }
   ],
   "source": [
    "!wget https://github.com/milvus-io/milvus/releases/download/v2.0.0-rc5-hotfix1/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": 3,
   "id": "6e9b030e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ade7ac90a30e58efeee268cfc1d6f78e8b5800649773d2ea6398e4e308990a4f\r\n"
     ]
    }
   ],
   "source": [
    "!docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d --name qa_mysql mysql:5.7"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9128fbfb",
   "metadata": {},
   "source": [
    "### Check Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "61716943",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS                    PORTS                                                  NAMES\r\n",
      "ade7ac90a30e   mysql:5.7      \"docker-entrypoint.s…\"   18 seconds ago   Up 17 seconds             0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   qa_mysql\r\n",
      "d1577179001a   d4f48c432200   \"/tini -- milvus run…\"   36 seconds ago   Up 35 seconds             0.0.0.0:19530->19530/tcp, :::19530->19530/tcp          milvus-standalone\r\n",
      "4ce7ddaf41ce   b849e0c26c4c   \"/usr/bin/docker-ent…\"   36 seconds ago   Up 36 seconds (healthy)   9000/tcp                                               milvus-minio\r\n",
      "0912ec243df2   a7908fd5fb88   \"etcd -advertise-cli…\"   36 seconds ago   Up 36 seconds             2379-2380/tcp                                          milvus-etcd\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": 6,
   "id": "050822e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pymilvus import *\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": 7,
   "id": "99dac31e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collection is successfully created: dna_seq\n"
     ]
    }
   ],
   "source": [
    "import time\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": 8,
   "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": 8,
     "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": 9,
   "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": 10,
   "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": 11,
   "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": 12,
   "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": 13,
   "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": 14,
   "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": 15,
   "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": 16,
   "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": 17,
   "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": 17,
     "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": 18,
   "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": 19,
   "id": "2a57e056",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Search...\n",
      "search latency = 0.3017s\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": 20,
   "id": "4adce68e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Search for 'human: 1'\n",
      "[label] distance\n",
      "['human: 1'] 0.9450757503509521\n",
      "['human: 1'] 0.7422963380813599\n",
      "['human: 1'] 0.6540967226028442\n",
      "['human: 1'] 0.5550222992897034\n",
      "['human: 1'] 0.5497077703475952\n",
      "['human: 5'] 0.34349900484085083\n",
      "['human: 3'] 0.3374307453632355\n",
      "['human: 5'] 0.334918349981308\n",
      "['human: 5'] 0.334236741065979\n",
      "['human: 5'] 0.33349767327308655\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.8702723979949951\n",
      "['human: 6'] 0.67112135887146\n",
      "['human: 6'] 0.6579180955886841\n",
      "['human: 6'] 0.6579180955886841\n",
      "['human: 6'] 0.6056888103485107\n",
      "['human: 6'] 0.3893663287162781\n",
      "['human: 6'] 0.38837116956710815\n",
      "['human: 6'] 0.3786880373954773\n",
      "['human: 6'] 0.3786880373954773\n",
      "['human: 6'] 0.37562549114227295\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.561901867389679\n",
      "['human: 0'] 0.3902236521244049\n",
      "['human: 0'] 0.3556520938873291\n",
      "['human: 0'] 0.35561567544937134\n",
      "['human: 0'] 0.34723198413848877\n",
      "['human: 0'] 0.33800268173217773\n",
      "['human: 0'] 0.251504510641098\n",
      "['human: 4'] 0.238668292760849\n",
      "['human: 1'] 0.2321547567844391\n",
      "['human: 1'] 0.2236199975013733\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.47377529740333557\n",
      "['human: 1'] 0.4195064902305603\n",
      "['human: 0'] 0.4168679416179657\n",
      "['human: 1'] 0.41614317893981934\n",
      "['human: 1'] 0.4137476086616516\n",
      "['human: 5'] 0.4123908281326294\n",
      "['human: 6'] 0.4118211269378662\n",
      "['human: 6'] 0.4115660488605499\n",
      "['human: 5'] 0.41095662117004395\n",
      "['human: 6'] 0.4108020067214966\n",
      "\n",
      "Search for 'human: 3'\n",
      "[label] distance\n",
      "['human: 3'] 0.5421205163002014\n",
      "['human: 2'] 0.3211418092250824\n",
      "['human: 2'] 0.321071982383728\n",
      "['human: 0'] 0.3156488537788391\n",
      "['human: 2'] 0.31419122219085693\n",
      "['human: 0'] 0.3090417683124542\n",
      "['human: 0'] 0.30887463688850403\n",
      "['human: 0'] 0.3084040582180023\n",
      "['human: 1'] 0.3079562187194824\n",
      "['human: 2'] 0.3075237274169922\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9713033437728882\n",
      "['human: 6'] 0.9229640364646912\n",
      "['human: 6'] 0.7372866272926331\n",
      "['human: 6'] 0.7247106432914734\n",
      "['human: 6'] 0.6711876392364502\n",
      "['human: 6'] 0.6485116481781006\n",
      "['human: 6'] 0.6471219658851624\n",
      "['human: 6'] 0.6439886093139648\n",
      "['human: 6'] 0.6128034591674805\n",
      "['human: 2'] 0.5042526125907898\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9984890222549438\n",
      "['human: 6'] 0.9437240958213806\n",
      "['human: 6'] 0.7079021334648132\n",
      "['human: 2'] 0.6230461597442627\n",
      "['human: 4'] 0.6172001361846924\n",
      "['human: 4'] 0.6166049838066101\n",
      "['human: 6'] 0.6138026714324951\n",
      "['human: 2'] 0.6111201047897339\n",
      "['human: 1'] 0.6063146591186523\n",
      "['human: 1'] 0.6061341762542725\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 1.0000104904174805\n",
      "['human: 6'] 0.9829837083816528\n",
      "['human: 6'] 0.9825929999351501\n",
      "['human: 6'] 0.9672328233718872\n",
      "['human: 6'] 0.9670814275741577\n",
      "['human: 6'] 0.9543920755386353\n",
      "['human: 6'] 0.9534107446670532\n",
      "['human: 6'] 0.9463043808937073\n",
      "['human: 6'] 0.9463043808937073\n",
      "['human: 6'] 0.9420653581619263\n",
      "\n",
      "Search for 'human: 1'\n",
      "[label] distance\n",
      "['human: 1'] 0.980971097946167\n",
      "['human: 1'] 0.9711356163024902\n",
      "['human: 1'] 0.9629136919975281\n",
      "['human: 1'] 0.9561055302619934\n",
      "['human: 1'] 0.8788124322891235\n",
      "['human: 4'] 0.6010207533836365\n",
      "['human: 4'] 0.5990103483200073\n",
      "['human: 1'] 0.5897881984710693\n",
      "['human: 1'] 0.5834859013557434\n",
      "['human: 6'] 0.5589426755905151\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.9585026502609253\n",
      "['human: 6'] 0.8690930008888245\n",
      "['human: 6'] 0.6880456209182739\n",
      "['human: 6'] 0.6624029278755188\n",
      "['human: 4'] 0.564928412437439\n",
      "['human: 4'] 0.5623241662979126\n",
      "['human: 4'] 0.5585187673568726\n",
      "['human: 3'] 0.5411080718040466\n",
      "['human: 3'] 0.5396809577941895\n",
      "['human: 0'] 0.5341933369636536\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.5228403210639954\n",
      "['human: 0'] 0.28860214352607727\n",
      "['human: 5'] 0.2753247618675232\n",
      "['human: 0'] 0.273336797952652\n",
      "['human: 5'] 0.26616549491882324\n",
      "['human: 0'] 0.2660927474498749\n",
      "['human: 5'] 0.26493266224861145\n",
      "['human: 1'] 0.2620023488998413\n",
      "['human: 1'] 0.25886890292167664\n",
      "['human: 1'] 0.25540465116500854\n",
      "\n",
      "Search for 'human: 6'\n",
      "[label] distance\n",
      "['human: 6'] 0.44523316621780396\n",
      "['human: 6'] 0.4447862505912781\n",
      "['human: 6'] 0.34046152234077454\n",
      "['human: 6'] 0.34046152234077454\n",
      "['human: 2'] 0.3341520130634308\n",
      "['human: 6'] 0.3337559103965759\n",
      "['human: 2'] 0.3288792073726654\n",
      "['human: 2'] 0.3285311758518219\n",
      "['human: 2'] 0.327978253364563\n",
      "['human: 6'] 0.3272181451320648\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.547831118106842\n",
      "['human: 0'] 0.42217257618904114\n",
      "['human: 1'] 0.3835963010787964\n",
      "['human: 1'] 0.38274842500686646\n",
      "['human: 5'] 0.3803880512714386\n",
      "['human: 0'] 0.38024136424064636\n",
      "['human: 5'] 0.3792631924152374\n",
      "['human: 5'] 0.37752118706703186\n",
      "['human: 4'] 0.37243974208831787\n",
      "['human: 4'] 0.3712049722671509\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.9443944096565247\n",
      "['human: 4'] 0.855256974697113\n",
      "['human: 4'] 0.8170849084854126\n",
      "['human: 4'] 0.761267900466919\n",
      "['human: 4'] 0.7514246702194214\n",
      "['human: 4'] 0.751309871673584\n",
      "['human: 4'] 0.7113007307052612\n",
      "['human: 4'] 0.695239245891571\n",
      "['human: 4'] 0.6855788826942444\n",
      "['human: 4'] 0.6617913842201233\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.8863165378570557\n",
      "['human: 4'] 0.8107112646102905\n",
      "['human: 4'] 0.6157439947128296\n",
      "['human: 5'] 0.3936389684677124\n",
      "['human: 0'] 0.38912466168403625\n",
      "['human: 0'] 0.38901421427726746\n",
      "['human: 6'] 0.3739807605743408\n",
      "['human: 6'] 0.3694756329059601\n",
      "['human: 0'] 0.36722564697265625\n",
      "['human: 0'] 0.3651857078075409\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.9750650525093079\n",
      "['human: 4'] 0.917722225189209\n",
      "['human: 4'] 0.6932716369628906\n",
      "['human: 4'] 0.58705735206604\n",
      "['human: 4'] 0.5813706517219543\n",
      "['human: 4'] 0.5652733445167542\n",
      "['human: 4'] 0.5508490800857544\n",
      "['human: 4'] 0.5505164861679077\n",
      "['human: 4'] 0.5319580435752869\n",
      "['human: 4'] 0.5122538208961487\n",
      "\n",
      "Search for 'human: 4'\n",
      "[label] distance\n",
      "['human: 4'] 0.5433626174926758\n",
      "['human: 4'] 0.5413291454315186\n",
      "['human: 4'] 0.5405317544937134\n",
      "['human: 4'] 0.4848265051841736\n",
      "['human: 4'] 0.3655065894126892\n",
      "['human: 6'] 0.3561324179172516\n",
      "['human: 4'] 0.3401263952255249\n",
      "['human: 4'] 0.3362541198730469\n",
      "['human: 2'] 0.3341509699821472\n",
      "['human: 6'] 0.3320785164833069\n",
      "\n",
      "Search for 'human: 2'\n",
      "[label] distance\n",
      "['human: 2'] 0.8956040740013123\n",
      "['human: 2'] 0.786155104637146\n",
      "['human: 2'] 0.6936513781547546\n",
      "['human: 2'] 0.6936513781547546\n",
      "['human: 2'] 0.6632592678070068\n",
      "['human: 2'] 0.6242187023162842\n",
      "['human: 2'] 0.36682331562042236\n",
      "['human: 6'] 0.3438706398010254\n",
      "['human: 5'] 0.34329184889793396\n",
      "['human: 5'] 0.3391764461994171\n",
      "\n",
      "Search for 'human: 3'\n",
      "[label] distance\n",
      "['human: 3'] 0.9155896902084351\n",
      "['human: 4'] 0.47078585624694824\n",
      "['human: 4'] 0.47043168544769287\n",
      "['human: 3'] 0.4430820345878601\n",
      "['human: 3'] 0.442610502243042\n",
      "['human: 2'] 0.4361966848373413\n",
      "['human: 2'] 0.433763325214386\n",
      "['human: 4'] 0.43178117275238037\n",
      "['human: 5'] 0.42457419633865356\n",
      "['human: 3'] 0.4206104874610901\n",
      "\n",
      "Search for 'human: 0'\n",
      "[label] distance\n",
      "['human: 0'] 0.9888718724250793\n",
      "['human: 0'] 0.9823843836784363\n",
      "['human: 0'] 0.7576547861099243\n",
      "['human: 0'] 0.7065188884735107\n",
      "['human: 0'] 0.6825615167617798\n",
      "['human: 0'] 0.5103759169578552\n",
      "['human: 0'] 0.508694052696228\n",
      "['human: 5'] 0.503524899482727\n",
      "['human: 4'] 0.49091580510139465\n",
      "['human: 4'] 0.4884316921234131\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": 23,
   "id": "654eed93",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chimp-human similarity score: 0.9690521497279405\n",
      "dog-human similarity score: 0.7043271789699793\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\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a04f9051",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:dna]",
   "language": "python",
   "name": "conda-env-dna-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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}