Support for DuckDB JDBC

[hvbtup]

DuckDB is an in-memory column-based high-performance OLAP database.
As such it could greatly improve the "BI" in BIRT.
Furthermore, it makes accessing a great variety of data sources a snap and it has excellent SQL support.

So I thought it would be great to create a sample report demonstrating how to use DuckDB to perform advanced SQL queries over Parquet or Arrow files (both are columnar storage formats).

DuckDB also can be used as a stand-alone CLI program (similar to e.g. Oracle SQL*Plus, but more advanced).

DuckDB includes a JDBC driver.

I was able to install the driver (straightforward), create a report with a data source and test the data source successfully.

However, when I try to create a JDBC Select data set, this fails with an exception

Unrecognized configuration property ",metadataBidiFormatStr"

Note: The error message above is not copy-pasted, it is what I remember from yesterday when I was at home.

What I did:

• Install BIRT 4.16 report designer all-in-one for windows.
• Create a new new report project
• Install the JDBC driver (downloaded from https://repo1.maven.org/maven2/org/duckdb/duckdb_jdbc/1.0.0/duckdb_jdbc-1.0.0.jar )
• Create a new report
• Create a data source, choose the DuckDB JDBC driver and the URL jdbc:duckdb:
• Test the data source
• Download a parquet file as described in https://duckdb.org/2021/06/25/querying-parquet.html
• Create a JDBC select dataset with the DuckDB datasource and a query like this

  SELECT count(*)
   FROM 'taxi_2019_04.parquet'
   WHERE pickup_at BETWEEN '2019-04-15' AND '2019-04-20'

Here, I replaced the filename with the full path.

As soon as I press OK in the DS editor, BIRT fails with the exception as described.

I don't say this is an error in BIRT. Obviously the DuckDB JDBC driver does not implement some metadata calls which BIRT seems to expect. However, it would be great if we can make this work somehow.

[speckyspooky]

@hvbtup
Yes, I can confirm that there is a topic with the JDBC-driver.
Following your instruction I got the error the first time directly at creation moment of the DataSet
and further at the moment where I tried to store the query.

The last time as I saw the "metadataBidiFormatStr" it was very deep into the source.
I will take a look with the debuger and may be we have luck to see details which can help to find a solution of it.

[speckyspooky]

The optimization is added under PR #1827
The change include a specialized validation of the supported driver properties in compare to the given options.

This is currently the issue because the most common JDBC-driver fullfil the ODA-requirements and so it was never a topic before.

Attached a screen of the access of DuckDB-data-files:

[speckyspooky]

The improved handling basedon the supported property validation will be available with BIRT 4.17.

[hvbtup]

Tnanks a lot, Thomas!