SciganticGuidesTopicsExplore

How to query ChEMBL bioactivity data as Parquet without installing the database

Query the Parquet copy of ChEMBL with DuckDB: read_parquet on chembl_37/derived/activities_enriched.parquet returns every curated activity for a target in one SQL statement. In a Scigantic notebook the ChEMBL 37 release is mounted at /mnt/archive, so the 30 GB database is never downloaded or unpacked.

The code on this page runs in a Scigantic hosted notebook, where the dataset is mounted read-only at /mnt/archive. The same path is in the SCIGANTIC_MOUNT_PATH environment variable and linked from ~/archive. The public source is s3://scigantic-chembl (AWS us-east-1, public, no account needed).

Open ChEMBL 37 on SciganticOpen the sample notebook: Every measured drug-target pair

Code

Every curated activity against one target (EGFR)

import os
from pathlib import Path
import duckdb

ARCHIVE = Path(os.environ.get("SCIGANTIC_MOUNT_PATH", Path.home() / "archive"))
ENRICHED = ARCHIVE / "chembl_37" / "derived" / "activities_enriched.parquet"

df = duckdb.connect().execute(f"""
    SELECT compound_chembl_id, compound_pref_name, standard_type,
           standard_value, standard_units, pchembl_value,
           canonical_smiles, doc_year
    FROM read_parquet('{ENRICHED}')
    WHERE target_chembl_id = 'CHEMBL203'        -- EGFR. Swap in any target.
      AND target_type = 'SINGLE PROTEIN'
      AND confidence_score >= 8
    ORDER BY pchembl_value DESC
""").df()

print(f"{len(df):,} activities, {df.compound_chembl_id.nunique():,} distinct compounds")
df.head(10)

Copied from this dataset's starter notebook on Scigantic, the one that opens when you start a notebook from the archive page (one cell, unchanged).

What the files are

ChEMBL is EMBL-EBI's manually curated database of bioactive molecules. Release 37 holds 2,921,148 compounds, 24,527,044 activity measurements, 1,970,438 assays and 18,552 targets.

The archive mirrors the upstream release three ways: the SQLite database as EBI ships it, the same 72 tables as one Parquet file each, and the bulk files such as the SDF and chemical representations. derived/activities_enriched.parquet joins activities to compound and target fields and keeps only rows with a pChEMBL value, an exact relation, no validity comment and no duplicate flag.

How the files are laid out

Path under the mountWhat it holds
chembl_37/derived/activities_enriched.parquetActivities joined to compound and target columns, already filtered to clean measurements. The file the code queries.
chembl_37/parquet/The 72 database tables, one Parquet file per table (1.66 GB in total), with _index.json listing row counts and column types.
chembl_37/sqlite/chembl_37.dbThe complete relational database as EBI ships it, about 30 GB, already expanded.
chembl_37/bulk/SDF structures, the chemreps table of SMILES and InChI, fingerprints and FASTA target sequences.
chembl_35/, chembl_36/Earlier releases.

Size, format and license

FormatParquet, SQLite, SDF and FASTA
SizeRelease 37: a SQLite database of about 30 GB and 1.66 GB of Parquet tables
LicenseCC BY-SA 3.0
ProviderEMBL-EBI (ChEMBL group); mirrored by Scigantic
Public sources3://scigantic-chembl (AWS us-east-1, public, no account needed)
Notebook imageCryo-EM and structural biology (DuckDB and RDKit preinstalled)

Things to know before you read it

Run it

Start a notebook from the ChEMBL 37 archive page. The data is mounted when the notebook opens, so the code above runs as written and nothing is downloaded first. For a longer worked example, open the sample notebook Every measured drug-target pair.

Related guides

Last checked 2026-11-21. All dataset guides.