Provide facilities for DuckDB

[josevalim]

There are two features I would like to see:

1. Passing existing datasets as temporary tables. The idea is to use CREATE TEMPORARY TABLE my_result_set and insert data into it

2. Integration with FSS so we can easily register S3 and similar

Perhaps they could be encapsulated into a Adbc.DuckDB module.

[cocoa-xu]

No problem, I'll look into this weekend

[nyo16]

Hello folks, can we explore to upgrade to the latest v1.3.0 to try DuckLake? Or we cannot install duckdb extensions with ADBC?
They already have an extension for s3 and other storages.

[josevalim]

v0.7.9 is out and it defaults to v1.3.0 for DuckDB. I have also update the docs to show how you can install latest DuckDB.

Or we cannot install duckdb extensions with ADBC? They already have an extension for s3 and other storages.

Yes, you can. This is about making it easy to pass credentials from Elixir to DuckDB.

[DnOberon]

There are two features I would like to see:

1. Passing existing datasets as temporary tables. The idea is to use `CREATE TEMPORARY TABLE my_result_set` and insert data into it

At least the postgres driver supports TEMPORARY TABLEs as well. Should we consider supporting this kind of behavior in all drivers that support it? Either way - we're going to need to touch the C code here - if only to more easily work with the Arrow frames in memory.

2. Integration with FSS so we can easily register S3 and similar

This shouldn't need to touch any of the C code. We'd manage this using DuckDB's Secrets Manager and sending queries through the ADBC connection. We'd want to make sure we echo the same warning they give, that if they're working on a file the secrets are saved unencrypted. Don't need to (or want to) open that can of worms ourselves. Probably go with your idea of a Adbc.DuckDB module to encapsulate this and other aspects.

Happy to start work on the secrets manager first and then the temp tables once I get some clarification.

[josevalim]

At least the postgres driver supports TEMPORARY TABLEs as well.

Good call out. We can probably support it in a generic way indeed!

[DnOberon]

The more I think about it @josevalim - I'm not sure we should orchestrate the use of DuckDB secrets here as we'd have to pull fss in as a dependency, and that seems messy - especially since this doesn't really touch the underlying C code. Would it make more sense to make this a part of Explorer.DataFrame.from_query/4 somehow? I'm thinking we can pass in an FSS layer as one of the options and then orchestrate the registration of secrets from FSS to Adbc in that call. fss and adbc are already both a part of explorer so it feels less convoluted.

If someone needs to load a secret outside of the realm of explorer it's easy - https://github.com/IdahoLabUnsupported/Datum/blob/main/lib/datum/duckdb.ex#L168 - as an example. We can update adbc's documentation to demonstrate how you'd do it and move on.

[josevalim]

@DnOberon I agree with you. Let's skip that issue then. For the first issue, creating temporary datasets as tables, I think one of the things we could start with is to do a query in PostgreSQL and then load it as a temporary table into a DuckDB database, and vice-versa. In a future task we can then figure out how to use it to load regular Elixir data into ADBC as well. WDYT?

[DnOberon]

@josevalim I'll prioritize that issue first then, and we can explore the explorer stuff at a later date :)

[DnOberon]

Some more questions for you @josevalim -

When you talk about 'passing existing datasets' - what format are you thinking these datasets will be in? Are they Adbc.Result, Explorer.DataFrame, thinly wrapped Arrow frames/streams? I think I need to get this answered before I can show you the function signatures I'm thinking about.

[josevalim]

The simplest version to get started with is to pass Adbc.Result before we materialize them (i.e. we still have the references in memory). The second step would be for us to pass regular Adbc.Column, created by hand, so we would need to convert the data to arrow entries, which I assume may not be straight-forward.

[DnOberon]

Alright, then I think we're going to need only one additional NIF function - I'm proposing:
def adbc_column_dematerialize([%Adbc.Column]), returns: data_ref

The functionality is that of taking the Adbc.Column list return and converting it back into an Arrow stream and returning a reference to that stream. We then should be able to use the bind_stream functionality and a normal SQL execution statement to create those temp tables.

[josevalim]

Sounds good to me!