duckDB handling?

[roolio]

Hello, now that I know that I can use Ultorg for datalakes like Snowflake, I was wondering if you had intentions of interoperating with DuckDB ? (they have a jdbc driver)

Why could it be useful? The use case is less on navigating in complex data models (because it's rather oriented towards analytic workloads with already prepared , simplified views), but I see 2 advantages :

• easy filtering + easy aggregations + easy column creation for non developers
• a way to open Ultorg to the CSV : Parquet files (bonus : blog post covering DuckDB might give some more visibility, regarding the hyper around it :) )

Cheers

Julien

[eirikbakke]

I looked at DuckDB the other day, actually! It might be the best way to let Ultorg connect to Parquet files, as you mention.

For CSV files (and Excel files, and MS Access files), Ultorg's current approach is to dump them into a local bundled PostgreSQL instance, and then use PostgreSQL as a query execution engine. PostgreSQL is quite optimized, so I'm not sure DuckDB's column-oriented engine would be faster except for queries that examine only a small subset of the table's columns. Those kinds of queries are common in analytical BI tools (e.g. "pick one field for the X axis and another for the Y axis, and aggregate over the whole database"), but perhaps less common in Ultorg, where you can easily be looking at 10-30 columns at a time. And the initial CSV loading work is the same for DuckDB, since CSV files are row-oriented.

But it would be interesting to test, and it's nice to know that there's a full-blown column-oriented query execution engine available for free these days. Tableau had to pay a lot of money for that some years ago.

[wizzard0]

DuckDB is seriously, seriously fast! And it uses the Postgres SQL parser frontend, so it might be a smaller switch than it looks :)

[eirikbakke]

@wizzard0 What do you typically use DuckDB for, in terms of data sources--CSV files or Parquet? I assume the use case for DuckDB is always to import data from somewhere else, rather than to function as a transactional data store.

[eirikbakke]

DuckDB connections are now supported in Ultorg 1.9.0! Full release notes at https://www.ultorg.com/releasenotes/#version-1_9_0 . There's a short demo video here:

https://www.youtube.com/watch?v=jzOVUbN6eVk