RDKit-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports the RDKit extension for cheminformatics and molecule retrieval. The extension adds the mol data type for molecules and the fp data type for molecular fingerprints, and provides substructure matching, Tanimoto and Dice similarity calculations, and index support.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL 7.0 instance with minor engine version V7.2.1.2 or later. You can view the minor engine version on the Basic Information page of your instance in the AnalyticDB for PostgreSQL console. If the version requirement is not met, update the minor engine version
An instance type other than AnalyticDB for PostgreSQL Serverless (not supported)

Install the extension

To install or uninstall the RDKit extension, submit a ticket to technical support with your instance ID and database name.

Usage notes

The mol data type uses SMILES (Simplified Molecular Input Line Entry Specification) for both input and output.
The fp data type uses the bytea format for binary data input and output.
Triggers are not supported.

Default parameter settings

The default similarity thresholds are:

postgres=# show rdkit.tanimoto_threshold;
 rdkit.tanimoto_threshold
--------------------------
 0.5
(1 row)

postgres=# show rdkit.dice_threshold;
 rdkit.dice_threshold
----------------------
 0.5
(1 row)

To change a threshold for the current session, use SET. For example:

SET rdkit.tanimoto_threshold = 0.5;
SET rdkit.dice_threshold = 0.5;

Work with molecules

For a complete list of SQL operations supported by the RDKit extension, see RDKit SQL.

Validate and create molecules

-- Check if a SMILES string is valid
SELECT is_valid_smiles('c1ccccc1');

-- Create a molecule from a SMILES string
SELECT mol_from_smiles('c1ccccc1');

-- Convert a molecule back to a SMILES string
SELECT mol_to_smiles(mol_from_smiles('c1ccccc1'));

Validate and create molecule queries

SMARTS-based queries let you match molecules by structural pattern rather than exact structure.

-- Check if a SMARTS string is valid
SELECT is_valid_smarts('c1ccc[n,c]1');

-- Create a query molecule from a SMARTS string
SELECT qmol_from_smarts('c1ccc[n,c]1');

-- Convert a query molecule back to a SMARTS string
SELECT mol_to_smarts(qmol_from_smarts('c1ccc[n,c]1'));

Create a table and load molecule data

Download the RDKit test data and save it to a local file named data.

-- Create a table with a molecule column
CREATE TABLE pgmol (id int, m mol);

-- Load molecules from the file
\copy pgmol from 'path/to/your/data';

Substructure matching

Use the @> and <@ operators to search for molecules that contain a given substructure.

-- Find molecules that contain benzene (molecule contains query)
SELECT count(*) FROM pgmol WHERE m @> 'c1ccccc1';

-- Find molecules contained within a query structure (query contains molecule)
SELECT count(*) FROM pgmol WHERE 'c1cccnc1' <@ m;

-- SMARTS-based substructure search
SELECT count(*) FROM pgmol WHERE m @> qmol_from_smarts('c1ccc[n,c]c1');

Generate fingerprints

-- Generate fingerprints of various types
SELECT rdkit_fp(m) AS f FROM pgmol LIMIT 1;
SELECT morgan_fp(m, 1) AS f FROM pgmol LIMIT 1;
SELECT maccs_fp(m) AS f FROM pgmol LIMIT 1;
SELECT torsion_fp(m) AS f FROM pgmol LIMIT 1;
SELECT atompair_fp(m) AS f FROM pgmol LIMIT 1;

-- Create fingerprint tables for indexed similarity search
SELECT id, rdkit_fp(m) AS f INTO pgbfp FROM pgmol;
SELECT id, morgan_fp(m, 1) AS f INTO pgsfp FROM pgmol;

Similarity search

Use the % operator (Tanimoto) or # operator (Dice) to filter molecules above a similarity threshold, then calculate the exact score with tanimoto_sml() or dice_sml().

-- Tanimoto similarity search
SELECT
    id,
    tanimoto_sml(rdkit_fp('C1C(OC2=CC(=CC(=C2C1=O)O)O)'::mol), f)
FROM pgbfp
WHERE rdkit_fp('C1C(OC2=CC(=CC(=C2C1=O)O)O)'::mol) % f
ORDER BY id
LIMIT 10;

-- Dice similarity search
SELECT
    id,
    dice_sml(rdkit_fp('C1C(OC2=CC(=CC(=C2C1=O)O)O)'::mol), f)
FROM pgbfp
WHERE rdkit_fp('C1C(OC2=CC(=CC(=C2C1=O)O)O)'::mol) % f
ORDER BY id
LIMIT 10;

Index support

Indexes significantly improve query performance for large molecule datasets.

B-tree and hash indexes support comparison operations on mol and fp:

CREATE INDEX molidx ON pgmol(m);

GiST and GIN indexes support the %, #, @>, and <@ operators on mol, and the % and # operators on fp:

CREATE INDEX molidx_gist ON pgmol USING gist(m);
CREATE INDEX bfp_gin ON pgbfp USING gin(f);
CREATE INDEX bfp_gist ON pgbfp USING gist(f);

Operators

Operator	Description
`mol % mol` / `fp % fp`	Returns TRUE if the Tanimoto similarity is less than the `rdkit.tanimoto_threshold` GUC parameter.
`mol # mol` / `fp # fp`	Returns TRUE if the Dice similarity is less than the `rdkit.dice_threshold` GUC parameter.
`mol @> mol`	Returns TRUE if the molecule on the left contains the molecule on the right (substructure search).
`mol <@ mol`	Returns TRUE if the molecule on the right contains the molecule on the left.
`mol @= mol`	Returns TRUE if the two molecules are an exact structural match.