<jupyter_start><jupyter_text>Molecular Similarity SearchIn this example we are performing a similarity retrieval of molecular formulae by generating molecular fingerprints using RDKit. With this fingerprints we use Milvus to perform a search for the most similar result to the target molecular formula.  DataWe have already prepared a SMILE file containing 10,000 molecular formulae [test_1w.smi](./smiles-data) from [pubchem](ftp://ftp.ncbi.nlm.nih.gov/pubchem/Compound/CURRENT-Full/SDF). The file is composed of two columns, SMILES and ID. Below is an example of the data: | SMILES | ID || --------------- | -------------- || [O-]C(=O)CC(C[N+](C)(C)C)OC(=O)C        | 1 || OC(=O)CC(C[N+](C)(C)C)OC(=O)C           | 2 || OC1C=CC=C(C1O)C(=O)O                    | 3 || CC(CN)O                                 | 4 || NCC(=O)COP(=O)(O)O                      | 5 |  RequirementsBefore running this notebook, you need to install [conda](https://docs.conda.io/projects/conda/en/latest/user-guide/install/index.html) or [miniconda](https://docs.conda.io/en/latest/miniconda.html) locally. This is required due to [RDKit](https://www.rdkit.org/docs/Install.html) only being installable through conda. Once conda is setup, you need to active a conda enviroment running `Python 3.7`. It can be done quickly with the following command: `conda create -n mol_search python=3.7`. Once created, activate the enviroment with `conda activate mol_search`. Once the enviroment is activated you can run the notebook, in our case we are using jupyter, so this can be done running the command `jupyter lab` in this notebooks directory. | Packages |  Servers || --------------- | -------------- || pymilvus-orm        | Milvus-2.0.0   || pymysql           | MySQL          || numpy || rdkit |We have included a requirements.txt file in order to easily satisfy the required packages.  Up and Running Installing RDkitInstall RDKit with Conda.<jupyter_code>! conda install -c rdkit rdkit -y<jupyter_output>Collecting package metadata (current_repodata.json): done
Solving environment: done


==> WARNING: A newer version of conda exists. <==
  current version: 4.9.2
  latest version: 4.10.1

Please update conda by running

    $ conda update -n base -c defaults conda



## Package Plan ##

  environment location: /Users/filiphaltmayer/opt/miniconda3/envs/test

  added / updated specs:
    - rdkit


The following packages will be downloaded:

    package                    |            build
    ---------------------------|-----------------
    blas-1.0                   |              mkl           6 KB
    bzip2-1.0.8                |       h1de35cc_0          75 KB
    cairo-1.16.0               |       h8023c5d_1         911 KB
    fontconfig-2.13.1          |       ha9ee91d_0         211 KB
    freetype-2.10.4            |       ha233b18_0         564 KB
    gettext-0.21.0             |       h7535e17_0         2.6 MB
    glib-2.68.1                |       hdf23fa2_0         2.7 MB
   [...]<jupyter_text>Installing PackagesInstall the required python packages with `requirements-v2.txt`.<jupyter_code>! pip install -r requirements.txt
! pip install --extra-index-url https://test.pypi.org/simple/  pymilvus-orm==2.0a1.dev43<jupyter_output>Requirement already satisfied: numpy in /Users/filiphaltmayer/opt/miniconda3/envs/test/lib/python3.7/site-packages (from -r requirements.txt (line 1)) (1.20.1)
Collecting pymilvus==1.1.0
  Using cached pymilvus-1.1.0-py3-none-any.whl (56 kB)
Collecting redis
  Using cached redis-3.5.3-py2.py3-none-any.whl (72 kB)
Collecting pymysql
  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)
[K     |████████████████████████████████| 43 kB 1.2 MB/s eta 0:00:01
[?25hCollecting ipyplot
  Downloading ipyplot-1.1.0-py3-none-any.whl (13 kB)
Collecting grpcio>=1.22.0
  Downloading grpcio-1.37.1-cp37-cp37m-macosx_10_10_x86_64.whl (3.9 MB)
[K     |████████████████████████████████| 3.9 MB 3.3 MB/s eta 0:00:01
[?25hCollecting grpcio-tools>=1.22.0
  Downloading grpcio_tools-1.37.1-cp37-cp37m-macosx_10_10_x86_64.whl (2.0 MB)
[K     |████████████████████████████████| 2.0 MB 19.7 MB/s eta 0:00:01
[?25hRequirement already satisfied: requests>=2.22.0 in /Users/filiphaltmayer/opt/miniconda3/envs/test/lib/[...]<jupyter_text>Starting MilvusStart Docker container without mapping the files, if you want to map the paths to the local files to the container, you can refer to [Install and Start Milvus](https://milvus.io/docs/v1.1.0/milvus_docker-cpu.md).<jupyter_code>! docker-compose up -d<jupyter_output>Docker Compose is now in the Docker CLI, try `docker compose up`

Creating network "milvus" with the default driver
Pulling etcd (quay.io/coreos/etcd:latest)...
latest: Pulling from coreos/etcd

[1B5c916c92: Pulling fs layer
[1Be24539ea: Pulling fs layer
[1Ba4d01894: Pulling fs layer
[1B2d7a61c2: Pulling fs layer
[1B26247b5c: Pulling fs layer
Digest: sha256:5b6691b7225a3f77a5a919a81261bbfb31283804418e187f7116a0a9ef65d21d    187B/187B6MBB[5A[2K[5A[2K[5A[2K[5A[2K[5A[2K[5A[2K[6A[2K[4A[2K[6A[2K[4A[2K[4A[2K[4A[2K[6A[2K[4A[2K[3A[2K[4A[2K[6A[2K[4A[2K[6A[2K[4A[2K[6A[2K[4A[2K[6A[2K[4A[2K[5A[2K[4A[2K[5A[2K[2A[2K[4A[2K[5A[2K[4A[2K[4A[2K[1A[2K[4A[2K[4A[2K[4A[2K[4A[2K[4A[2K[4A[2K[3A[2K[2A[2K[1A[2K[1A[2K
Status: Downloaded newer image for quay.io/coreos/etcd:latest
Pulling minio (minio/minio:RELEASE.2020-12-03T00-03-10Z)...
RELEASE.2020-12-03T00-03-10Z: Pulling from minio/minio

[1B0c94c7c5: Pulling fs layer
[...]<jupyter_text>Starting MySQLWe are using MySQL as a metadata storage service. Code can easily be modified to use a python dictionary, but that usually does not work in any use case outside of quick examples. We need a metadata storage service in order to be able to be able to map between embeddings and their corresponding molecular formulae.<jupyter_code>! docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<PASSWORD> -d mysql:5.7<jupyter_output>Unable to find image 'mysql:5.7' locally
5.7: Pulling from library/mysql

[1B81a07f80: Pulling fs layer 
[1Bb60610f5: Pulling fs layer 
[1Bafb77ab8: Pulling fs layer 
[1B46006037: Pulling fs layer 
[1Be54c8855: Pulling fs layer 
[1Be05cc46d: Pulling fs layer 
[1B814c833f: Pulling fs layer 
[1B5b4af634: Pulling fs layer 
[1Ba5b14385: Pulling fs layer 
[1B36297c75: Pulling fs layer 
[1BDigest: sha256:1a2f9cd257e75cc80e9118b303d1648366bc2049101449bf2c8d82b022ea86b7[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[7A[2K[11A[2K[8A[2K[11A[2K[8A[2K[11A[2K[8A[2K[11A[2K[11A[2K[8A[2K[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[6A[2K[11A[2K[6A[2K[6A[2K[11A[2K[11A[2K[5A[2K[11A[2K[6A[2K[6A[2K[11A[2K[6A[2K[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[11A[2K[1A[2K[11A[2K[11A[2K[11A[2K[10A[2K[10A[2K[3A[2K[9A[2K[3A[2K[9A[2K[3A[2K[3A[2K[8A[2K[3A[2K[7A[2K[3A[2K[6A[2K[6A[2K[3A[2K[6A[2K[6A[2K[[...]<jupyter_text>Confirm Running Servers<jupyter_code>! docker ps<jupyter_output>CONTAINER ID   IMAGE                                         COMMAND                  CREATED          STATUS                    PORTS                                                  NAMES
3a21dab57272   milvusdb/milvus:v2.0.0-rc1-20210628-b87baa1   "/tini -- milvus run…"   33 seconds ago   Up 30 seconds             0.0.0.0:19530->19530/tcp, :::19530->19530/tcp          milvus-standalone
d98393369445   minio/minio:RELEASE.2020-12-03T00-03-10Z      "/usr/bin/docker-ent…"   37 seconds ago   Up 33 seconds (healthy)   9000/tcp                                               milvus-minio
f3f02dfaed94   quay.io/coreos/etcd:latest                    "etcd -listen-peer-u…"   37 seconds ago   Up 33 seconds (healthy)   2379-2380/tcp                                          milvus-etcd
bc1096b4b1cd   mysql:5.7                                     "docker-entrypoint.s…"   17 minutes ago   Up 17 minutes             0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql<jupyter_text>Code Overview Connecting to ServersAfter preparing the conda environment (installing RDKit, etc.) and successfully starting Milvus and MySQL, we will begin by connecting to the servers. In this case the docker containers are running on localhost and the ports are the default ports.> If there was a problem downloading the required packages and no Milvus module can be found, please run the requirments.txt install again in a terminal that has the conda enviroment activated.<jupyter_code># Connecting to servers

import pymysql
import time

from pymilvus import Milvus, DataType

time.sleep(1)

milv = Milvus(host='localhost', port='19530')
conn = pymysql.connect(host='localhost', user='root', port=3306, password='<PASSWORD>', db='mysql', local_infile=True)
cursor = conn.cursor()<jupyter_output><empty_output><jupyter_text>Building Collection and Setting IndexingWe first begin by declaring some variables, such as the dimension of the vector, the name of the collection/table, the path of the SMILES file and the SMILES molecule we will be searching.<jupyter_code>VECTOR_DIMENSION = 512
collection_name = 'mol_search'
file_path = './smiles-data/test_1w.smi'
target_smiles = 'Cc1ccc(cc1)S(=O)(=O)N'
distance_type = 'JACCARD'
index_type = 'BIN_IVF_FLAT'
nlist = 512
nprobe = 16
topk = 3<jupyter_output><empty_output><jupyter_text>For this example we are using the Jaccard distance in order to calculate similarity between vectors.<jupyter_code>#Create collection
def create_collection(name, milv):
    id_field = {
        "name": "id_field",
        "type": DataType.INT64,
        "auto_id": False,
        "is_primary": True,
    }
    vector_field = {
        "name": "mol_vectors",
        "type": DataType.BINARY_VECTOR,
        "metric_type": distance_type,
        "params": {"dim": VECTOR_DIMENSION},
        "indexes": [{"metric_type": distance_type}]
    }
    fields = {"fields": [id_field, vector_field]}

    milv.create_collection(collection_name=name, fields=fields)
    print("collection created:", name)

if milv.has_collection(collection_name):
        milv.drop_collection(collection_name)
        print("collection dropped:", collection_name)

cursor.execute('DROP TABLE IF EXISTS ' + collection_name)
conn.commit()


time.sleep(.1)

create_collection(collection_name, milv)<jupyter_output>collection created: mol_search<jupyter_text>For the index we are using the binary_ivf_flat index. We can set the index before inserting data so that it indexes concurrently.<jupyter_code>def create_index(name, field_name, milv):
    index_param = {
        "metric_type": distance_type,
        "index_type": index_type,
        "params": {"nlist": nlist}
    }
    milv.create_index(name, field_name, index_param)
    print("Create index: {}".format(index_param))

create_index(collection_name, "mol_vectors", milv)<jupyter_output>Create index: {'metric_type': 'JACCARD', 'index_type': 'BIN_IVF_FLAT', 'params': {'nlist': 512}}<jupyter_text>Processing and Inserting Data into MilvusWe can use RDKit to generate Morgan fingerprints, which defines a radius from a specific atom and calculates the number of chemical structures within the range of the radius to generate a chemical fingerprint.<jupyter_code>#Process SMILES

from rdkit import Chem
from rdkit.Chem import AllChem
from rdkit import DataStructs

def smiles_to_vec(smiles):
    mols = Chem.MolFromSmiles(smiles)
    fp = AllChem.GetMorganFingerprintAsBitVect(mols, 2, VECTOR_DIMENSION)
    hex_fp = DataStructs.BitVectToFPSText(fp)
    vec = bytes.fromhex(hex_fp)
    return vec

def get_vectors(file_path):
    ids = []
    vectors = []
    with open(file_path, 'r') as f:
        for line in f:
            line = line.strip().split()
            smiles = line[0]
            num = int(line[1])
            vec = smiles_to_vec(smiles)
            vectors.append(vec)
            ids.append(num)
    return vectors, ids<jupyter_output><empty_output><jupyter_text>Then we can then read the SMILES file and batch insert it into Milvus.<jupyter_code># Batch insert

vectors, ids = get_vectors(file_path)

entities = [{"name": "id_field", "type": DataType.INT64, "values": ids}, {"name": "mol_vectors", "type": DataType.BINARY_VECTOR, "values": vectors}]
ids = milv.insert(collection_name, entities)<jupyter_output>RDKit WARNING: [18:12:36] WARNING: not removing hydrogen atom without neighbors<jupyter_text>Loading Mapping into MySQLAfter recieving the IDs from milvus we must save the mapping between the vector and the SMILES molecule. We do this buy saving the (ID, SMILES) pair in a table.<jupyter_code># Create the table in MySQL

def create_table_mysql(conn, cursor, table_name):
    sql = "create table if not exists " + table_name + "(smiles varchar(256), ids bigint);"
    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print("MYSQL ERROR:", e, sql)

# Load the local file to MySQL

def load_data_mysql(conn, cursor, table_name, file_path):
    sql = "load data local infile '" + file_path + "' into table " + table_name + ";"
    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print("MYSQL ERROR:", e, sql)
create_table_mysql(conn, cursor, collection_name)
load_data_mysql(conn, cursor, collection_name, file_path)<jupyter_output><empty_output><jupyter_text>SearchingWith the data loaded in Milvus and the mapping loaded into MySQL, we can now perform the search. To begin, we must first transform the SMILES molecule into a molecular fingerprint. Then, with this fingerprint we can search the database, which gives us the closest IDs with their distances.<jupyter_code># Perform search
def search(name, vector_field, smiles):
    search_vectors = [smiles_to_vec(smiles)]
    nq = len(search_vectors)
    search_params = {"metric_type": distance_type, "params": {"nprobe": nprobe}}
    results = milv.search_with_expression(name, search_vectors, vector_field, param=search_params, limit=topk)
    
    re_ids = [x.id for x in results[0]]
    re_distance = [x.distance for x in results[0]]
    return re_ids, re_distance

def search_in_milvus(collection_name, smiles, top_k):
    vec = smiles_to_vec(smiles)
    search_sub_param = {
        "nprobe": 16
    }

    search_param = {
        'collection_name': collection_name,
        'query_records': [vec],
        'top_k': top_k,
        'params': search_sub_param,
    }
    status, vectors = milv.search(**search_param)
    print(status)
    re_ids = [x.id for x in vectors[0]]
    re_distance = [x.distance for x in vectors[0]]

    return re_ids, re_distance<jupyter_output><empty_output><jupyter_text>With the IDs we can then search the MySQL table to find the corresponding SMILES molecules.<jupyter_code># Get SMILES results
def search_by_milvus_id(conn, cursor, collection_name, ids):
    str_ids = str(ids).replace('[','').replace(']','')
    sql = "select smiles from " + collection_name + " where ids in (" + str_ids + ") order by field (ids, " + str_ids + ");"
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        results = [res[0] for res in results]
        return results
    except Exception as e:
        print("MYSQL ERROR:", e, sql)
milv.load_collection(collection_name)
re_ids, re_distance = search(collection_name, "mol_vectors", target_smiles)
re_smiles = search_by_milvus_id(conn, cursor, collection_name, re_ids)
print(re_smiles)
#Display Results

from rdkit.Chem import Draw

def draw_smiles(smiles):
    mols = Chem.MolFromSmiles(smiles)
    img = Draw.MolsToGridImage([mols], molsPerRow=1, subImgSize=(150, 150))
    return img

img = draw_smiles(target_smiles)
print("target-smiles:", target_smiles)
display(img)
print("results: ")

for s, i, d in zip(re_smiles,re_ids, re_distance):
    print(s, "\tids:", i, "\tdistance:", d)
    display(draw_smiles(s))<jupyter_output>target-smiles: Cc1ccc(cc1)S(=O)(=O)N<jupyter_text>ConclusionThis notebook shows how to search for similar molecules. We can also search the superstructure and substructure with the different [METRICS](https://milvus.io/docs/v1.1.0/metric.mdfloating) when searching in Milvus.We have also built a [demo system](https://zilliz.com/milvus-demos), and also have an open source projects on substructure and superstructure search in [MolSearch](https://github.com/zilliztech/MolSearch).<jupyter_code>! docker-compose down<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13324017254997267, 'fraction_numerical': 0.055896470016404395, 'mean_word_length': 3.755850910141578, 'pattern_counts': {'":': 26, '<': 55, '<?xml version=': 0, '>': 63, 'https://': 8, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '208322', 'license': 'Apache-2.0', 'n_tokens_mistral': 6558, 'n_tokens_neox': 5801, 'n_words': 1436, 'path': 'solutions/molecular_similarity_search/molecular_search.ipynb', 'repo_name': 'linchpinlin/bootcamp', 'size': '16451'}