Extending Parquet support: live S3 queries via DuckDB and ADBC

[boshek]

#215 delivered EDDTableFromParquetFiles which is an implementation for reading local or cached Parquet files viaparquet-java. But if my understanding is correct, this implementation will read Parquet files in full then filter the result in memory.

That work is amazing but I think could be extended in this way: Parquet data in S3, queried live with duckdb, without downloading files to the ERDDAP host first. This type of workflow can be so useful and it would be nice to bring it to ERDDAP. As an example here is duckdb going from 4.36 million rows of data:

D select count(*) 
  from read_parquet(
      's3://noaa-ghcn-pds/parquet/by_year/YEAR=2023/ELEMENT=TMAX/*.parquet',
      hive_partitioning = true);  
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    4355909     │
│ (4.36 million) │
└────────────────┘

and using the pushdown predicates to efficiently query these parquet files stored in s3:

D select 
      count(*) as n_rows,
      s_flag
  from read_parquet(                                                                                       
        's3://noaa-ghcn-pds/parquet/by_year/YEAR=2023/ELEMENT=TMAX/*.parquet',                               
        hive_partitioning = true                                                                             
    )
  where ELEMENT = 'TMAX' and DATA_VALUE >= 300 and DATE = '20230105'
  group by S_FLAG;
┌────────┬─────────┐
│ n_rows │ S_FLAG  │
│ int64  │ varchar │
├────────┼─────────┤
│      5 │ U       │
│     19 │ 7       │
│    186 │ a       │
│     10 │ W       │
│    318 │ S       │
└────────┴─────────┘

Indeed #281 took one approach using DuckDB's JDBC driver.

This discussion proposes an alternative path that could potentially avoid JDBC entirely. Full disclosure some of this is fleshed out with Claude. Still it is not entirely fleshed out (hence opening as a discussion) but am gauging if any folks have tried this and if folks would be interested in this:

Proposal: EDDTableFromDuckDB via ADBC

ADBC (Arrow Database Connectivity) is a database connectivity standard built on Apache Arrow. DuckDB has first-class ADBC support, and Java bindings exist.

Critically, ADBC talks to DuckDB's native API rather than the JDBC layer, which means read_parquet('s3://...'), Hive-partitioned datasets, and Iceberg tables are all available without the .db file constraint that blocked #281

The proposed data flow:
User ERDDAP request

• ERDDAP translates constraints to SQL WHERE clauses (same logic as EDDTableFromDatabase)
• DuckDB receives query via ADBC
• DuckDB pushes predicates down to Parquet row groups on S3
• Only matching data is returned across the wire
• ERDDAP maps Arrow record batches to its PrimitiveArray columns

Is there appetite for a dataset type that introduces a DuckDB process dependency, or is the preference to keep ERDDAP dependencies minimal?

[rmendels]

@boshek @ChrisJohnNOAA First, I am not certain exactly how ERDDAP™ accesses data in a parquet file, I will leave that to Chris, but since you like Claude.AI give it this prompt:

does the Java parquet driver require you to read in the entire file, or does it allow you to select without reading the entire file into memory

And you will see that no it is not required to read the entire file into memory to make an extract. Generally we do not like to add dataset types that are specific to one application. But if I understand your suggestion in a broader sense you are requesting to add ADBC as one of the available services along with JDBC. This will still mean that ERDDAP™ is accessed in the usual way, through an URL, but ERDDAP™s access would be more efficient for supported "filetypes". This might be considered since it is application neutral, but as with all requests we have to weigh the time and effort to develop it against the benefits relative to what already exists, how many people would benefit from this, and how this compares to other things that our development efforts could be put to.

[ChrisJohnNOAA]

The EDDTableFromParquetFiles reads entire local files currently. You can see the read implementation here: https://github.com/ERDDAP/erddap/blob/main/WEB-INF/classes/gov/noaa/pfel/coastwatch/pointdata/Table.java#L14059

We possibly could add support for partial file reads (I think likely very complicated to get significant performance benefits - my guess is the main benefit to what DuckDB is doing above is reducing the over the wire data, not the local file parsing) and cloud files to the current implementation. If there's an easy way to support that through EDDTableFromDuckDB it might be a more maintainable approach.

EDDTableFromDuckDB via ADBC sounds interesting. Can DuckDB ADBC be installed through Maven (this is how we manage dependencies for ERDDAP)? The instructions involve manually downloading a library, which may be difficult to broadly support for ERDDAP installations across all operating systems. I also didn't see examples for Java which concerned me.

I'd also be curious what dependencies we'd need to add (Apache Arrow, DuckDB ADBC, others?), how large they are, and how well maintained.

Is there a full list of cloud provider's and data formats it supports? This one is more trying to define how much benefit this would bring to ERDDAP.

[srstsavage]

This seems really promising to me! I would love to be able to use ERDDAP to read cloud hosted hive partitioned datasets efficiently.

Interestingly the ADBC Java offering seems to be a JDBC wrapper:

https://arrow.apache.org/adbc/current/java/quickstart.html

Installable via Maven:

<dependency>
  <groupId>org.apache.arrow.adbc</groupId>
  <artifactId>adbc-driver-jdbc</artifactId>
  <version>0.22.0</version>
</dependency>

With a few dependencies:

https://central.sonatype.com/artifact/org.apache.arrow.adbc/adbc-driver-jdbc/dependencies

All told it adds ~3MB of dependency jars to WEB-INF/lib.

[rmendels]

@srstsavage @ChrisJohnNOAA @boshek I dont think it is correct that ADBC Java is just an ODBC wrapper. LLM's can be wrong, but see below my query to Claude. An advantage of ADBC is that it is column oriented while JDBC is row oriented, so it is much more efficient for applications and formats that are also column oriented.

Does the ADBC (Arrow) java library just build on top of JDBC, or does it only use that if the target doesn't support Arrow,

The ADBC Java library is not built on top of JDBC. The JDBC adapter is a separate, explicitly-chosen driver for legacy databases — not a fallback that kicks in automatically. Here's the architecture:
Two Distinct Things

1. Native ADBC drivers — Arrow all the way through. You pick one of these when the target database supports Arrow natively:

adbc-driver-flight-sql — for databases supporting the Flight SQL wire protocol (Dremio, etc.)
adbc-driver-jdbc wrapping a native Arrow transport — e.g., Snowflake's own Arrow-native API

2. The JDBC Adapter (adbc-driver-jdbc) — an explicit opt-in for databases that only speak JDBC. The ADBC JDBC adapter adapts the JDBC API to the ADBC API, so that an ADBC user can interact with databases that only have JDBC APIs available. While this doesn't give you the best possible performance (you're paying for transposing the data back and forth), it does save you the hassle of writing those conversions yourself. Apache Arrow
   There's no auto-detection — you pick the driver that matches your database.
   Why the JDBC Path Has a Cost
   JDBC uses row-based interfaces like ResultSet. When working with columnar Arrow data, this means converting the data at least once and possibly twice: once in the driver or database to convert columnar data into row-based format for the JDBC APIs, and once always when a client application pulls data from the JDBC API to convert rows back into columns. Apache Arrow
   The Preferred Path for Arrow-Native Databases
   Flight SQL requires a database to specifically implement support for it, while ADBC is a client API specification for wrapping existing database protocols which could be Arrow-native or not. Together, ADBC and Flight SQL offer a fully Arrow-native solution for clients and database vendors. GitHub
   So in practice the hierarchy is:

Database has a native ADBC driver (Snowflake, DuckDB, BigQuery) → pure Arrow, no JDBC involved
Database supports Flight SQL → use adbc-driver-flight-sql, still pure Arrow
Database only has JDBC → use adbc-driver-jdbc, which does the row↔column conversion for you as a convenience

The JDBC adapter exists to maximize database coverage, not as a performance shortcut.Want to be notified when Claude responds?

[rmendels]

@srstsavage @ChrisJohnNOAA @boshek Also, we may already be able to do this. I have written up how to use duckdb from ERDDAP™ using JDBC. But that just sends the SQL query, duckDB does its own thing (and my timings on this were very good). Again from Claude:

If I access a parquet file using duckdb, but access duckdb using JDBC, does duckdb just use the JDBC to get the SQL query, and then uses its more efficient access libraries to read the parquet file

Yes, exactly right — and this is an important and commonly misunderstood point. The JDBC layer is just the query delivery mechanism; DuckDB's execution engine operates entirely independently of it.
Here's what actually happens:

JDBC receives your SQL string and passes it to DuckDB's query parser/planner — that's all JDBC does on the input side.
DuckDB's native engine takes over completely: it plans the query, applies predicate pushdown, column projection, row group skipping, uses its vectorized execution engine, and reads only the necessary bytes from the Parquet file using its own C++ Parquet reader — none of which has anything to do with JDBC.
JDBC comes back into the picture only at result delivery: DuckDB has to serialize its columnar Arrow-format results into JDBC's row-based ResultSet, which is where you pay the JDBC tax.

So the overhead of using JDBC with DuckDB is:

Negligible on the read side — DuckDB reads the Parquet file exactly as efficiently as if you called it natively
Real but one-directional on the output side — you pay a columnar→row conversion cost when pulling results through ResultSet

This is why the ADBC DuckDB driver (which keeps results in Arrow columnar format end-to-end) is faster than the JDBC driver for large result sets, but both will read the Parquet file with equal efficiency. The bottleneck with JDBC is getting the data out of DuckDB, not getting data into DuckDB's query engine.

[rmendels]

@srstsavage @ChrisJohnNOAA @boshek Also:

Can duckDB itself be accessed through ADBC?

Yes — and this actually closes the loop on the whole conversation nicely. From the earlier search results we already have the answer:
DuckDB provides ADBC support since version 0.8.0, with C/C++ status: Stable. Apache Arrow
So the full stack you could use from Java is:
Your Java app
↓ ADBC API
DuckDB ADBC driver (via driver manager)
↓ Arrow RecordBatches (columnar, no row conversion)
DuckDB C++ engine
↓ vectorized execution, predicate/projection pushdown
DuckDB's native Parquet reader
↓
Parquet file
This eliminates the one remaining inefficiency in the JDBC path — the row serialization on the output side. Results come back as Arrow RecordBatches rather than being transposed row-by-row through ResultSet.
The practical tradeoff: JDBC is universally supported by Java tooling (ORMs, connection pools, SQL clients like DBeaver, etc.), so it's often the pragmatic choice even with the output conversion cost. But if you're writing code specifically to move large volumes of analytical data from Parquet through DuckDB into an Arrow-native downstream (like writing to another Parquet file, feeding a data pipeline, etc.), the ADBC path avoids that last unnecessary copy.

So the most useful thing in my opinion is to have an ADBC datatype in ERDDAP™ and then to do what Sam requested use that to hook into duckDB, as I have done with JDBC. That should provide the fastest solution while still maintaining the ERDDAP™ structure

[rmendels]

@srstsavage @ChrisJohnNOAA @boshek and as my coffee kicks in, my writeup on using ERDDAP™ with duckDB through JDBC:

https://erddap.github.io/docs/server-admin/admin-tips/duckdb

[rmendels]

Also some useful tools if we want to add an ADBC datatype:

ADBC-quickstarts - https://github.com/columnar-tech/adbc-quickstarts/tree/main/java
Download ADBC drivers (this would be the equivalent of the JDBC drivers put in WEB-INF) - https://columnar.tech/dbc/

I have been following ADBC and Arrow Flight for awhile, and was thinking of suggesting adding them, was waiting for more adoption and stable tooling, but that appears to have arrived.

[boshek]

Great discussion!

One question of @ChrisJohnNOAA that hasn't been answered yet is:

Is there a full list of cloud provider's and data formats it supports? This one is more trying to define how much benefit this would bring to ERDDAP.

From duckdb docs:

The httpfs filesystem is tested with AWS S3, Minio, Google Cloud and lakeFS. Other services that implement the S3 API (such as Cloudflare R2) should also work, but not all features may be supported.

And for data source duckdb supports lots but I think some of the most exciting here would Parquet and Iceberg.

So the scope of what this would unlock is broad.

[rmendels]

@boshek @ChrisJohnNOAA @srstsavage All of these data storage types can already be accessed in ERDDAP™ using the duckDB JDBC connector (maybe if I get motivated I will write this up not just for csv and parquet as I have already done, but for other data stores, the problem is I don't have anything using those data stores so I can't test my usual gibberish), or if I ever finish writing it up the Trino JDBC connector. So accessing these storage already exists in ERDDAP™. The difference is, as in the responses I have posted above, ADBC is both columnar and can do zero copy should be faster - not faster in the data read step itself, as discussed above, but faster in then getting the data that has been extracted. So as in the example above, whether by ADBC or JDBC if we hook up to duckDB once duckDB receives the SQL query how it handles the data access is identical, but then extracting back into ERDDAP™ what has been extracted is not, and that is where the speed gain resides. For very large extracts, that can be significantly faster.

[rmendels]

It appears that duckDB now has a standard interface for the small DuckDB database that needs to be constructed to use with other data store. Again being lazy I turned to Claude:

In the past,  to hook ERDDAP™ up to duckDB using JDBC and then actual use duckDB not to access a duckDB file,  but say a csv file or a parquet file or to postgres,  I creates a small duckDB database that pointed to the actual datastore as a virtual dataset  (I believe that is the correct name),  but how I had to do that varied with each datastore.  Is there now a standard way to do a virtual dataset that is the same for each data store

The short answer is: CREATE VIEW is now the standard unified mechanism, and the pattern is essentially the same regardless of the underlying data store. The view wraps the appropriate reader/scanner function, so ERDDAP (via JDBC) always just sees a named view it can query with plain SQL.
The general pattern is always:
sqlCREATE VIEW my_dataset AS
  SELECT * FROM <reader_function>('path/or/connection');

What changes is only the reader function and its arguments — the view creation syntax and how JDBC/ERDDAP sees it is identical. Here's how that looks per data store:

CSV:
sqlCREATE VIEW my_dataset AS
  SELECT * FROM read_csv('path/to/file.csv', auto_detect = true);
-- or glob patterns:
CREATE VIEW my_dataset AS
  SELECT * FROM read_csv('/data/obs/*.csv', auto_detect = true);

Parquet:
sqlCREATE VIEW my_dataset AS
  SELECT * FROM read_parquet('path/to/file.parquet');
-- or glob for many files:
CREATE VIEW my_dataset AS
  SELECT * FROM read_parquet('/data/sst/year=*/month=*/*.parquet');

PostgreSQL (via the postgres extension):
sqlINSTALL postgres;
LOAD postgres;
CREATE VIEW my_dataset AS
  SELECT * FROM postgres_scan('host=myhost dbname=mydb user=myuser password=pw',
                               'public', 'my_table');
-- or attach the whole postgres DB and use a view over it:
ATTACH 'host=myhost dbname=mydb user=myuser password=pw' AS pg (TYPE postgres);
CREATE VIEW my_dataset AS SELECT * FROM pg.public.my_table;
Remote files over HTTPS/S3 (with httpfs extension):
sqlINSTALL httpfs; LOAD httpfs;
CREATE VIEW my_dataset AS
  SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet');

A few things worth noting for your ERDDAP/JDBC use case:

The .duckdb file you create just needs to contain these view definitions — it stays small since no data is copied into it. DuckDB reads the underlying source at query time.
You can mix sources in a single view (e.g., join a local Parquet file against a Postgres table), which is something that was awkward before.
You can create a view like CREATE VIEW events AS SELECT * FROM read_parquet('s3://lake/events/date=*/hour=*/*.parquet') and then query it with standard SQL, with DuckDB handling partition pruning automatically. [Medium](https://medium.com/@duckweave/querying-parquet-with-duckdb-8222beec6929)
The term you were using — "virtual dataset" — maps well to what DuckDB now calls a view over a scan function. The terminology isn't formally standardized in DuckDB docs, but CREATE VIEW is the idiomatic approach across all data store types.

So compared to what you were doing before, the good news is you no longer need to vary the approach much per store — it's always CREATE VIEW ... AS SELECT * FROM <scanner>(), you just swap the scanner function.

I guess I should redo the writeup and test again with more examples. But anything duckDB can now do, ERDDAP™ can do through JDBC. As I have said, the big difference with ADBC is speed.

[ChrisJohnNOAA]

I think the big question here is, is there a benefit to directly integrating duckdb (rather than using the EDDTableFromDatabase approach Roy demonstrated)?

I assume it would simplify the process for admins to use it. It might also allow us to take advantage of additional duckdb features or have a performance advantage (like through ADBC).

That is a good set of cloud providers supported, I had previously only found a page that listed AWS, Azure, and non-specific mention of other providers.

[rmendels]

@ChrisJohnNOAA duckDB itself is C++ and platform specific. That is why I use the EDDTableFromDatabase approach. I got very good speeds doing that, ADBC should make it even faster.

Direct Java access to duckDB can only happen if the data are in a duckDB database file (you can check on this). To access a "foreign" format, you have to use the approach I used, where you create basically a stub duckDB database file. It is different when you are running duckDB on your desktop and working in that environment because duckDB handles those issues, but when accessing it through JDBC or ADBC you need the stub, We could create a bunch of sample files to ease the setup, But the steps pretty much are:

1. Add the driver to WEB-INF
2. Create the duckDB database file that points to the actual data.
3. point that stub file as the data source.

The setup would be the same for ADBC

[ChrisJohnNOAA]

@rmendels DuckDb supports direct integration in Java programs:
https://duckdb.org/install/?environment=java

My understanding is we can do a lot through the Java client: http://duckdb.org/docs/stable/clients/java
Most likely we could handle the create view (DuckDB database) in the Java code and/or allow admins to specify a path to the DuckDB database they want to use.

I haven't tried to set that up yet, so it's possible there's a roadblock I missed.

[rmendels]

@ChrisJohnNOAA I clicked on the bottom of page where it says "Client Documentation Page" That takes you to:

https://duckdb.org/docs/stable/clients/java

which is headed "Java JDBC Client". That is the same thing I ran into when I first looked at this awhile back. If you can find a change let me know. One advantage of ADBC is it is supported by any number of data services and databases, not just duckDB. So you use it with Trino and Databricks and the like (snowflakes - is that the correct name?).

[ChrisJohnNOAA]

What I was linking to is DuckDB provides a java library (called duckdb_jdbc) that can be used to integrate directly with Java code: https://duckdb.org/install/?environment=java

Based on the documentation for Linux and Mac it should enable you to use DuckDB directly in Java with no additional dependencies. On Windows you just need to have the Microsoft Visual C++ Redistributable installed on the system.

This would mean admins don't need to install the standalone DuckDB on the machine running ERDDAP.

The https://duckdb.org/docs/stable/clients/java page is docuementation for how to use the direct Java client.

[rmendels]

@ChrisJohnNOAA I just checked and you are correct, but it is in memory. I am pretty certain when I checked awhile back it could not directly do foreign formats. One drawback is that the view doesn't persist across connections. It does with the way I approached it (the view information is stored in the duckdb file) or use a startup script to recreate the view before making the connection. So depending. you may find creating the stub file gives better performance, it depends on how long it takes to create the view. The stub file does not have to be on the ERDDAP™ server (i think) it just needs to be on a reachable server that has duckDB installed - I mean it has to be installed somewhere. But i think you will get better performance if the duckDB instance is on the ERDDAP™ server, even if the data files are not, though O am not certain of this.

[ChrisJohnNOAA]

I believe it'd be possible to save the view to a file in the ERDDAP™ cache. My understanding is if you give the duckDB connection a file path that doesn't exist it will create the file.

[rmendels]

@ChrisJohnNOAA I was right and wrong about the persistence of the view. If the connection is opened when running a tomcat it will persist as long the tomcat is running. so that means the connection remains open unless the program closes it. I am not certain I fully grasp everything about duckDB. but the big difference is unlike postgresql which has a persistent daemon you connect to , duckDB is a library that runs inside your application process, there is no server listening to a port.

One thing to speed things up is there is " arrowExportStream()"which streams the data using arrow. This is part of the Java client, something you might look at. One thing I did find out is ADBC is not really supported on Windows (I think) so that would limit things Using "arrowExportStream()" gives you a lot of the same speed-up and runs across platforms

One other advantage of the duckDB stub file is that you do not need to know about the different foreign formats that duckDB supports (and keeps growing). In the direct client approach I believe you need a reader defined for each format, and that would have to be changed as more formats are added With the stub, you only need to know how to read a duckDB file, that defines the reader in the file.

Clearly there are tradeoffs. Leave it up to you. Clearly we should be doing something along these lines, I am actually a fan of duckDB so I would not be against doing this (how many links to duckDB videos have I sent), just want to make certain we use our resources wisely and as generally as possible.

[rmendels]

Our messages crossed. I would guess that the file you can save in cache would essentially be the stub file, though I haven't checked on this. So the difference is ERDDAP™ would create the stub file rather than the Admin. That would be neat if so.

What I believe happens when you create a stub duckDB file the first time is it scans the foreign file for the information it needs for the view and stores that in the duckDB file. Then it can read that in quick and know how to access the dataset. One thing I have been thinking about for a long time is rewriting the ERDDAP caching to be be more like this and things like virtualizarr, rather than both how the info is presently stored and what info is stored. I imagine that would take an incredible amount of work so I haven't suggested it.

As an aside, so far we have seen good results using rclone to mount S3 buckets on the VM machine in the cloud. This is because rclone gives a lot of options both in cacheing and in the size of chunks it uses to transport things. And generally with some of the recent changes I have been seeing good speed despite being hit very hard.

[ChrisJohnNOAA]

I've opened #448 to track this idea.

There's also an existing issue #434 about updating the cache strategy. While not well defined at this time, I believe it's similar to what @rmendels mentioned above.