The blog of a juvenile Geekus biologicus

Query a BioPAX ontology with DuckDB

Install DuckDB

pipx install duckdb

Download PathBank BioPAX files

Throughout this blog post, we will work with example BioPAX files from the pathbank database. I downloaded the BioPAX files from https://pathbank.org/downloads on <2026-09-28 Mon>, the announced date of generation is September 13, 2019.

Load the DuckDB rdf extension

duckdb

INSTALL rdf FROM community;
LOAD rdf;

How to load the content of a BioPAX file with DuckDB rdf extension?

I extracted the zip archive in ~/data/pathbank/pathbank_all_biopax/pathbank_all_biopax/.

To load the BioPAX subject, predicate, object triplets, I use the following DuckDB SQL command. We use the PW000001.owl file as example:

SELECT *
FROM read_rdf("~/data/pathbank/pathbank_all_biopax/pathbank_all_biopax/PW000001.owl", file_type="xml");

We can create a SQL view for later use of this query:

CREATE VIEW PW000001 AS
SELECT *
FROM read_rdf("~/data/pathbank/pathbank_all_biopax/pathbank_all_biopax/PW000001.owl", file_type="xml");

the columns are the following:
graph subject predicate object object_datatype object_lang

Example queries

Get the list of pathway identifiers in the BioPAX file

In SPARQL, we would write the query as follows:

# List all pathways referenced in the database
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
SELECT ?pathway
WHERE {
    ?pathway rdf:type bp:Pathway .
}

The SQL equivalent is:

SELECT pathway.subject
FROM PW000001 pathway
WHERE pathway.predicate = 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type'
AND pathway.object = 'http://www.biopax.org/release/biopax-level3.owl#Pathway';

Which outputs:

┌─────────────────────────────────────────┐
│                 subject                 │
│                 varchar                 │
├─────────────────────────────────────────┤
│ http://identifiers.org/smpdb/SMP0000055 │
│ http://identifiers.org/smpdb/SMP0000057 │
│ http://identifiers.org/smpdb/SMP0000029 │
└─────────────────────────────────────────┘

Get the list of biochemical reaction identifiers of a pathway

In SPARQL, we would write the query as follows:

# List all reactions of a pathway
# Example: pathway_uri=<http://biocyc.org/biopax/biopax-level3#Pathway561264>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?reaction ?reaction_name
WHERE {
    :pathway_uri rdf:type bp:Pathway ;
             bp:pathwayComponent ?reaction .
    ?reaction rdf:type bp:BiochemicalReaction ; # pathwayComponent ranges in bp:Interaction, bp:BiochemicalReaction is a bp:Conversion, which is an Interaction.
              bp:standardName ?reaction_name .
}

SELECT reaction.subject, reaction_name.object
FROM PW000001 reaction,
     PW000001 reaction_name,
     PW000001 pathway,
     PW000001 pathway_component
WHERE
pathway.subject = 'http://identifiers.org/smpdb/SMP0000055'
-- pathway rdf:type bp:Pathway
AND pathway.predicate = 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type'
AND pathway.object = 'http://www.biopax.org/release/biopax-level3.owl#Pathway'
-- pathway bp:pathwayComponent reaction
AND pathway_component.subject = pathway.subject
AND pathway_component.predicate = 'http://www.biopax.org/release/biopax-level3.owl#pathwayComponent'
AND reaction.subject = pathway_component.object
-- reaction rdf:type bp:BiochemicalReaction
AND reaction.predicate = 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type'
AND reaction.object =  'http://www.biopax.org/release/biopax-level3.owl#BiochemicalReaction'
-- reaction bp:standardName reaction_name
AND reaction_name.subject = reaction.subject
AND reaction_name.predicate = 'http://www.biopax.org/release/biopax-level3.owl#name';

Wow! So much verbosity!

It seems that there is no standardName filled in this pathbank BioPAX file, so I used here bp:name instead of the bp:standardName predicate.

Results:

┌────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────┐
│                subject                 │                                 object                                  │
│                varchar                 │                                 varchar                                 │
├────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────┤
│ http://smpdb.ca/pathways/##Reactions/2 │ L-Alanine + Oxoglutaric acid &harr; L-Glutamic acid + Pyruvic acid      │
├────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────┤
│ http://smpdb.ca/pathways/##Reactions/1 │ Adenosine triphosphate + L-Alanine + tRNA(Ala) &rarr; Adenosine monopho │
│                                        │ sphate + L-Alanyl-tRNA(Ala) + Pyrophosphate                             │
├────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────┤
│ http://smpdb.ca/pathways/##Reactions/4 │ Adenosine triphosphate + Hydrogen carbonate + Pyruvic acid &rarr; Adeno │
│                                        │ sine diphosphate + Oxalacetic acid + Phosphate                          │
├────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────┤
│ http://smpdb.ca/pathways/##Reactions/3 │ Glyoxylic acid + L-Alanine &harr; Glycine + Pyruvic acid                │
└────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘

Can we use SPARQL queries instead of hand crafted SQL equivalent?

TL;DR: Yes we can!

To do so, we will use a generic r2rml description file adapted to the table resulting from the DuckDB rdf extension’s read_rdf function: <./r2rml_duckdb_mapping.ttl>

@prefix rr: <http://www.w3.org/ns/r2rml#> .
@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

# Mapping for IRI objects (e.g., pathwayComponent, type)
<#IRIObjectMapping>
    rr:logicalTable [
        rr:sqlQuery """
        SELECT subject, predicate, object
        FROM triplets
        WHERE object_datatype IS NULL
        """
    ];
    rr:subjectMap [
        rr:column "subject";
        rr:termType rr:IRI
    ];
    rr:predicateObjectMap [
        rr:predicateMap [
            rr:column "predicate";
            rr:termType rr:IRI
        ];
        rr:objectMap [
            rr:column "object";
            rr:termType rr:IRI
        ]
    ].

# Mapping for literal objects (e.g., name)
<#LiteralObjectMapping>
    rr:logicalTable [
        rr:sqlQuery """
        SELECT subject, predicate, object, object_datatype
        FROM triplets
        """
    ];
    rr:subjectMap [
        rr:column "subject";
        rr:termType rr:IRI
    ];
    rr:predicateObjectMap [
        rr:predicateMap [
            rr:column "predicate";
            rr:termType rr:IRI
        ];
        rr:objectMap [
            rr:column "object";
            rr:termType rr:Literal;
            rr:datatypeMap [
                rr:column "object_datatype"
            ]
        ]
    ].

In this turtle R2RML file, notice that I used the table name triplets instead of the table name PW000001 we used before. This does not cause any particular difficulties, as we can easily change the name of the table in this file, or, we can use the following SQL query to create a new view of the PW000001 table with the name “triplets”:

CREATE VIEW triplets AS (SELECT * FROM PW000001);

The tricky part was with the handling of non IRI values: We split the mapping into two parts, the first one #IRIObjectMapping to handle cases where the object is a IRI, tested when object_datatype value is NULL, and #LiteralObjectMapping for the Boolean, string and float cases. In the later case, we map the rr:termType with the rr:datatypeMap construct.

Then, to perform the SPARQL query, we can run the following command in DuckDB:

SELECT * FROM execute_sparql(
   'PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
SELECT ?pathway
WHERE {
    ?pathway rdf:type bp:Pathway .
}', './r2rml_duckdb_mapping.ttl'
);

And we obtain the same list of pathways as before:

┌─────────────────────────────────────────┐
│                v_pathway                │
│                 varchar                 │
├─────────────────────────────────────────┤
│ http://identifiers.org/smpdb/SMP0000055 │
│ http://identifiers.org/smpdb/SMP0000029 │
│ http://identifiers.org/smpdb/SMP0000057 │
└─────────────────────────────────────────┘

A more complex query: the reactions of a pathway, again

SELECT * FROM execute_sparql(
   '
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?reaction ?reaction_name
WHERE {
    <http://identifiers.org/smpdb/SMP0000055> rdf:type bp:Pathway ;
             bp:pathwayComponent ?reaction .
    ?reaction rdf:type bp:BiochemicalReaction ;
              bp:name ?reaction_name .
}
', './r2rml_duckdb_mapping.ttl'
);

We can explore the content of the generated SQL query:

select sparql_to_sql(
            '
         PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
         PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
         PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
         SELECT ?reaction ?name
         WHERE {
             <http://identifiers.org/smpdb/SMP0000055> rdf:type bp:Pathway ;
                      bp:pathwayComponent ?reaction .
             ?reaction rdf:type bp:BiochemicalReaction ; bp:name ?name  }
         ', './r2rml_duckdb_mapping.ttl'
         );

A stronger stress test : the MetaCyc OWL database

With this simple OWL file, the loading of the data and the SPARQL/SQL query is fast. But what about a larger ontology? Does this method still work smoothly?

To test this, I will apply some queries on the BioPAX export of the MetaCyc database v29.5.

We start a new duckdb session:

duckdb

We load the rdf extension

INSTALL rdf FROM community;
LOAD rdf;

We load the MetaCyc owl file:

CREATE VIEW METACYC AS
SELECT *
FROM read_rdf("~/data/biocyc/metacyc-biopax-level3.owl", file_type="xml");

Counting the number of triplets

SELECT count(*) FROM METACYC;

3346748
(3.35 million)

we can already find that DuckDB takes a little bit longer time to load the table. An issue we might want to handle is linked with how DuckDB handles SQL view of the results of the read_rdf function. I guess the owl file is read every times?

How many biochemical reactions?

We will use the same R2RML mapping, so we create a view named triplets:

CREATE VIEW triplets AS (SELECT * FROM METACYC);

# name: get_all_reactions()
# List all reactions referenced in the database (type bp:BiochemicalReaction)
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
SELECT ?reaction
WHERE {
    ?reaction rdf:type bp:BiochemicalReaction .
}

SELECT * FROM execute_sparql(
   '
# name: get_all_reactions()
# List all reactions referenced in the database (type bp:BiochemicalReaction)
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bp: <http://www.biopax.org/release/biopax-level3.owl#>
SELECT ?reaction
WHERE {
    ?reaction rdf:type bp:BiochemicalReaction .
}
', './r2rml_duckdb_mapping.ttl'
);

17378

Again, it takes a bit more time, but it seems reasonable.

The Qlever graph database we used in the blog post How to load the PathwayCommons OWL file into a local QLever instance? allows to make faster query, at the expense of a more complex setup.

Conclusion

This approach leverages DuckDB to perform SPARQL queries on a OWL ontology. We demonstrated this possibility with BioPAX exports. This can certainly be a reasonable approach if you do not want to bother with the setup of a fully featured RDF triplestore and SPARQL endpoint such as QLever or Apache Jena, but it is probably not an optimal approach, as using relational query on a graph database is probably kind of twisting the usage of the relational model.

References