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 mount | What it holds |
|---|---|
| chembl_37/derived/activities_enriched.parquet | Activities 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.db | The 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
| Format | Parquet, SQLite, SDF and FASTA |
|---|---|
| Size | Release 37: a SQLite database of about 30 GB and 1.66 GB of Parquet tables |
| License | CC BY-SA 3.0 |
| Provider | EMBL-EBI (ChEMBL group); mirrored by Scigantic |
| Public source | s3://scigantic-chembl (AWS us-east-1, public, no account needed) |
| Notebook image | Cryo-EM and structural biology (DuckDB and RDKit preinstalled) |
Things to know before you read it
- Swap
CHEMBL203for any target ID. Theconfidence_score >= 8andSINGLE PROTEINfilters keep assays assigned to a single protein target. - On the day the notebook was built this query returned 18,998 activities over 11,202 compounds.
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
- How to query 122 million PubChem compounds with ADMET predictions in a Jupyter notebook
- How to open the OpenADMET CYP inhibition challenge data in a Jupyter notebook
- How to open the Alchemistry 2027 dengue binding free energy challenge data in a Jupyter notebook
- How to open the OpenBind Zika virus blind challenge data in a Jupyter notebook
Last checked 2026-11-21. All dataset guides.