Working with remote data using DuckDB

Last week at the GEOBON meeting, Carl Boettiger gave me a very quick demo of DuckDB, which is a really cool project I hadn’t paid enough attention to yet. In this post, I will show how to use it to work remotely on the VIRION database, which we curate with Verena.

To make it all work, we need the DuckDB package, and the usual data frame paackages for Julia:

using DataFrames
using DuckDB
using PrettyTables

The nice thing with DuckDB is that it is possible to work in-memory, so we will establish a new connection of this type:

db = DBInterface.connect(DuckDB.DB, ":memory:")

We can load data that are stored remotely, which only requires to add the http file-system to our database connection:

DBInterface.execute(db, "INSTALL httpfs;")
DBInterface.execute(db, "LOAD httpfs;")

This is useful when we don’t feel like downloading the data. If the data are expected to change rapidly, this is a considerable advantage: we can ensure that we work on the most recent version.

The VIRION data are stored on GitHub, and we will focus on a simple example where we pick the taxonomy of hosts and viruses, and the egdelist between these two:

hosts = "https://raw.githubusercontent.com/viralemergence/virion/main/Virion/TaxonomyHost.csv"
viruses = "https://raw.githubusercontent.com/viralemergence/virion/main/Virion/TaxonomyVirus.csv"
edges = "https://raw.githubusercontent.com/viralemergence/virion/main/Virion/Edgelist.csv"

In order to add these tables to the connection, we CREATE a new view using the URL as the table name:

DBInterface.execute(db, "CREATE OR REPLACE TEMPORARY VIEW hosts AS SELECT * FROM read_csv_auto('$(hosts)', nullstr='NA');")
DBInterface.execute(db, "CREATE OR REPLACE TEMPORARY VIEW viruses AS SELECT * FROM read_csv_auto('$(viruses)', nullstr='NA');")
DBInterface.execute(db, "CREATE OR REPLACE TEMPORARY VIEW edges AS SELECT * FROM read_csv_auto('$(edges)', nullstr='NA');")

And we’re good to go. It’s not a bad idea to make sure that the tables have been loaded correctly, so let’s take a look at the content of the host taxonomy:

DataFrame(DBInterface.execute(db, "DESCRIBE hosts;")) |>  pretty

We can start running a request like “the number of species in the 10 genus of bats for which we have the most species mapped onto the NCBI taxonomy”:

req = """
SELECT DISTINCT HostGenus AS Genus, COUNT(HostGenus) as Richness
    FROM hosts
    WHERE
        (HostOrder LIKE 'chiroptera') AND
        (HostNCBIResolved == true)
    GROUP BY HostGenus
    ORDER BY count(HostGenus) DESC
    LIMIT 10;
"""
unique_bats = DBInterface.execute(db, req)
DataFrame(unique_bats) |> pretty

Nice. Now, we can start looking at joining the different tables, to get a sense of e.g. what viruses of non-human primates are in VIRION. Again, this will be a simple matter of writing the SQL query:

req = """
CREATE VIEW hostedges
AS
SELECT HostGenus, Host, VirusClass, VirusOrder, VirusFamily, VirusGenus, Virus FROM hosts
LEFT JOIN edges
ON edges.HostTaxID = hosts.HostTaxID
RIGHT JOIN viruses
ON edges.VirusTaxID = viruses.VirusTaxID
WHERE
    (hosts.HostOrder LIKE 'primates') AND
    (hosts.Host NOT LIKE 'homo sapiens') AND
    (hosts.HostNCBIResolved = true) AND
    (viruses.VirusNCBIResolved = true);
"""
make_view = DBInterface.execute(db, req)
get_primates_viruses = DBInterface.execute(db, "SELECT * FROM hostedges LIMIT 10;")
DataFrame(get_primates_viruses) |> pretty

With this new view, we can make a table listing the number of know viruses between the species of hosts and the families of viruses:

req = """
SELECT
    Host, VirusFamily, COUNT(Virus) AS Number
FROM
    hostedges
GROUP BY
    Host, VirusFamily
ORDER BY
    Number DESC
LIMIT 20;
"""
get_num_viruses = DBInterface.execute(db, req)
DataFrame(get_num_viruses) |> pretty

That’s it! DuckDB is definitely really nice, and I am going to integrate it in my workflow.