Feature Discussion: multiple databases, cross database query and foreign database wrapper

[exaatto]

Based on our use cases and a collection of issues ( #655, #665, #892, #1934, #2063, etc.), we believe it is worth while for DuckDB to design an unified interface for multiple databases, cross database query and foreign database wrapper. This interface might include:

1. Multiple DB "connection", like SQLite ATTACH statement. Maybe some syntax like:

   ATTACH "duck.db" as other [READ ONLY];

2. Foreign DB wrapper, maybe one DB wrapper like the PostgreSQL equivalent, and another file loader exposing the existing Appender API into SQL world, like:

   CREATE FOREIGN DATA WRAPPER posduck_fdw WITH EXTENSION "/path/to/libposduck_fdw.so";
   CREATE FOREIGN DATABASE pg FOREIGN DATA WRAPPER postduck_fdw OPTIONS (
     dbname 'pg', host 'pg', port '5432',
     user 'fdw_user', password 'secret',
     readonly 'TRUE'
   );
   
   CREATE FOREIGN DATA WRAPPER csv_loader; -- built-in extension
   CREATE FOREIGN DATABASE csv_files FOREIGN DATA WRAPPER csv_loader OPTIONS (
     dbpath '/path/to/csv/folder',
     auto 'TRUE'
   );

   Also, it seems the above ATTACH statement can be unified as a syntax sugar for this FDW, and further more seems CSV/Parquet loaders can also be unified.
3. Cross DB query, both syntax support using fully qualified table name and execution support with query push-down via FDW.

I'm not a DB specialist thus not sure about whether that is the best design for DuckDB, but from our own use cases, this could be a nice extension. As mentioned in my #1127 comment, we are trying to replace a desktop data analysis tool (well, namely, SAS). Till now every thing works fine but we just missing the fdw functionality. We have to download tables from our central DW as parquet and then load them into local DuckDB manually.

I know this could be a big change that may not be prioritized for implementation, but I will be happy to discuss these features, providing my perspective of view as app developer and data engineer.

Cheers.

[alanpaulkwan]

I think once the DuckDB ODBC driver is done, this problem will be mitigated. I imagine of course that drivers written for specific foreign data sources might be faster, but with ODBC theoretically DuckDB can integrate any ODBC source? If that's not in the works, I think this would be a great feature to add.

[SartorialOffense]

Would love the ability to attach to multiple DuckDB files. When I do this now I just use Parquet files - but it is a lot slower. I have thought about trying same approach with Arrow to see if this is acceptable.

[exaatto]

I didn't noticed duckdb is working on an odbc source driver. But ASFAIK an fdw should do more than basic odbc functions, such as sql dialet check, connection managememt, according the pg fdw docs.

However, I do think including an official odbc_fdw will be a great help.

[dioptre]

Ooo might be time to investigate again https://duckdb.org/docs/api/odbc/linux

[exaatto]

This is not related. This is odbc client for duckdb, i.e., how you connect to duckdb via odbc.

However, for foreign data source, there are already facilities as a duckdb extension, such as:

• pg scanner: https://duckdb.org/docs/extensions/postgres_scanner
• sqlite scanner: https://duckdb.org/docs/extensions/sqlite_scanner

An ATTACH statement syntax is used for foreign data as well as native duckdb.

An odbc scanner is also being worked on (thanks to @rupurt & @Kayrnt), hopefully it will become official in the future :-p

• ODBC scanner: https://github.com/rupurt/odbc-scanner-duckdb-extension

[alanpaulkwan]

I also noticed someone is working on a MySQL scanner, which is really the main complaint I have with DuckDB at this point :-)

[rupurt]

I'm currently reworking ODBC schema discovery so that it uses the patterns from the SQLite adapter. That will enable better catalog structure and predicate push downs. Soon we will be able to swim in all of the databases 😄

FWIW driver management in ODBC is very frustrating if you're on an ARM mac. None of the major legacy vendors (Oracle, IBM, M$) ship officially supported drivers for ARM mac yet. I feel like M$ is the leader, at least they have a version in beta. I've created a separate Nix project to try and abstract away a lot of the pain between platforms and I've been using it in the ODBC scanner extension.

[github-christophe-oudar]

An odbc scanner is also being worked on (thanks to @rupurt & @Kayrnt), hopefully it will become official in the future :-p

Mostly Alex these days, I've run into issues with nix with an ARM Mac (and my config isn't working well with his one either).
So I'm letting Alex figuring it out until I can make more changes (especially since he planned to refactor the structure).

I also noticed someone is working on a MySQL scanner, which is really the main complaint I have with DuckDB at this point :-)

That's likely me as I was working on https://github.com/Kayrnt/duckdb_mysql_scanner and wonder if I should move further or not on it as ODBC scanner might as well superseed it... !
The main problem I have I moved to Rust to have a nicer language and SQLX lib (as it's much simpler to manage deps on Rust and it has a connection pool!) but I ran into the duckDB C API limitations... it's far from feature complete and therefore I can't make my feature... so I started to look into writing them... but my C++ knowledge is so bad it takes me ages to make any progress.
So right now, I'm on an hiatus as I'm also contributing dbt (which is much simpler to work with language wise), I might resume once I'm getting some free time back.