duckDB as an ERDDAP™ client

[Roy Mendelssohn - NOAA Federal]

duckDB is a very popular analytical database and I have written before about using duckDB with ERDDAP™ to access filetypes that are not built into ERDDAP™ but can be accessed by duckDB (see https://groups.google.com/g/erddap/c/6Hl024ZGkes/m/DS5WzsydAQAJ).   Now I want to look at the converse,  using duckDB to access data served by an ERDDAP™ server.  Clearly one way this can be done is to download the appropriate dataset extract outside of duckDB and then read it,  but if you are developing scripts it is easier if you can do this from inside duckDB.  Ideally there would be a duckDB extension that has some of the capabilities of ‘rerddap’ or ‘erddapy’ but one doesn’t exist at the moment (any takers?  duckDB extensions are written in C).

However there is a way to do this using the “httpfs” extension if you have the percent encoded URL for the extract,  which ERDDAP™’s web interface conveniently provides. ( or you can try using the built-in duckDB function url_encode() but beware it will encode the entire URL which may mess up the URL,  there are ways to program around this but caveat emptor).

In these examples the extracted data are downloaded as parquet files which duckDB is excellent at reading.

As a gridded example,  let’s download an extract from the monthly MODISA chlorophyll product at https://coastwatch.pfeg.noaa.gov/erddap/griddap/erdMWchlamday.html

memory D LOAD httpfs;
memory D SELECT *
         FROM read_parquet('https://coastwatch.pfeg.noaa.gov/erddap/griddap/erdMWchlamday.parquet?chlorophyll%5B(2026-05-16T12:00:00Z):1:(2026-05-16T12:00:00Z)%5D%5B(0.0):1:(0.0)%5D%5B(39):1:(40.6)%5D%5B(234):1:(237.8)%5D')
         LIMIT 10;
┌──────────────────────────┬──────────┬──────────┬───────────┬─────────────┐
│           time           │ altitude │ latitude │ longitude │ chlorophyll │
│ timestamp with time zone │  double  │  double  │  double   │    float    │
├──────────────────────────┼──────────┼──────────┼───────────┼─────────────┤
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │     234.0 │  0.28415388 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │  234.0125 │  0.29331118 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │   234.025 │   0.2943314 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │  234.0375 │  0.29459232 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │    234.05 │  0.29813647 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │  234.0625 │  0.29940408 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │   234.075 │   0.2975602 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │  234.0875 │  0.29716668 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │     234.1 │  0.30333272 │
│ 2026-05-16 05:00:00-07   │      0.0 │     39.0 │  234.1125 │  0.30713347 │
└──────────────────────────┴──────────┴──────────┴───────────┴─────────────┘
  10 rows                                                        5 columns

As a tabledap example, let’s download some air temperature data from some NDBC buoys at https://coastwatch.pfeg.noaa.gov/erddap/tabledap/cwwcNDBCMet.html

memory D SELECT *
         FROM read_parquet('https://coastwatch.pfeg.noaa.gov/erddap/tabledap/cwwcNDBCMet.parquetWMeta?longitude%2Clatitude%2Ctime%2CATMP&longitude%3E=-140&longitude%3C=-110&latitude%3E=30&latitude%3C=40&time%3E=2026-08-01T00%3A00%3A00Z&time%3C=2026-09-12T17%3A50%3A00Z')
         LIMIT 10;
┌───────────┬──────────┬──────────────────────────┬───────┐
│ longitude │ latitude │           time           │ ATMP  │
│   float   │  float   │ timestamp with time zone │ float │
├───────────┼──────────┼──────────────────────────┼───────┤
│  -120.857 │   34.868 │ 2026-07-31 17:00:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 17:10:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 17:20:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 17:30:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 17:40:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 17:50:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 18:00:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 18:10:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 18:20:00-07   │  15.6 │
│  -120.857 │   34.868 │ 2026-07-31 18:30:00-07   │  15.4 │
└───────────┴──────────┴──────────────────────────┴───────┘
  10 rows                                       4 columns

While I haven’t tried it,  I imagine this can be made to work with the R package “duckplyr” if you use dplyr workflows.  All of this works because of ERDDAP™’s design to work with anything that can send an URL and receive a file.  If you are a tech nerd,  it is interesting that Quack,  duckLab’s product  for client-server applications, uses http(s) for its transport because they have found most things these days not only support it but are highly optimized for it.  

-Roy

PS - If you are really a tech nerd here is ChatGPT’s description of the Quack protocol - if it seems familiar remember ERDDAP™ is almost 20 years old.  Sometimes simple and following standards are the best way to go even if it lacks glitz and buzzwords.

DuckDB’s Quack remote protocol is HTTP-based. It can run over plain HTTP locally, or over HTTPS when exposed through a reverse proxy. DuckDB explicitly recommends putting something like nginx in front of Quack for non-local access and terminating TLS there rather than exposing the Quack endpoint directly.

The actual Quack messages are binary DuckDB protocol messages carried in HTTP request/response bodies, typically via an HTTP POST to the Quack endpoint. So it is not SQL text over HTTP in the way a REST API might work; HTTP/HTTPS is the transport, while the payload uses DuckDB’s binary serialization. 

For local connections, Quack defaults to HTTP. For remote connections, the client generally assumes SSL/TLS, i.e. HTTPS, unless overridden. 

That design is one of the interesting things about Quack: because it uses HTTP(S), it plays nicely with ordinary infrastructure—nginx/Apache, load balancers, firewalls, authentication proxies, etc. Given the reverse-proxy setup you already use for ERDDAP/Tomcat, the architecture would feel very familiar.