duckdb -ui pre_seeded_db.db
duckdb -ui -init startup.sql
CREATE TABLE person (name VARCHAR, age BIGINT);
INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25);
CREATE OR REPLACE MACRO my_query_table(t) AS TABLE (SELECT * FROM query_table(t));
SELECT * FROM my_query_table('person');
CREATE OR REPLACE MACRO query_macro(m, i) AS TABLE (SELECT * FROM query(CONCAT('SELECT * FROM ', m::STRING, $$($$, i::STRING, $$)$$)));
SELECT * FROM query_macro('my_query_table', 'person');
ADBC --sql--> Flight SQL Server --flight--> Flight Servers (plural) --> datasources
SELECT * FROM <relation> WHERE ...
WHERE A IN (3, 5, 7)
ADBC client --> Flight SQL (duckdb/whatever) --> Flight --> ?
create view x
as select
*
from
iceberg_scan('metadata_file');
set ts = {last_materialization_ts};
select * from X CHANGES AT(timestamp=>$ts);
select * from Y CHANGES AT(timestamp=>$ts);
-- triggered task to kick off materialization to duckdb
create task M
when
SYSTEM$HAS_DATA(X_STREAM) AND
SYSTEM$HAS_DATA(Y_STREAM) AND ...
AS
CALL MY_MATERIALIZE(); -- send SNS, invoke ext-func, etc
It should be extremely simple for databases that support ADBC (for example Snowflake, PostgreSQL).
For others it might just be a matter of mapping DDL, DML, DQL, etc to a supported database protocol driver (JDBC, ODBC, etc). Of course this is where things may get challenging as it would become the responsibility of your server to convert result to Arrows (tables/streams/etc). But could potentially be delegated to "worker" Flight servers (not a Flight SQL server) and then the server could return/forward their Arrow results (Flight results).
Of course some of this is to some degree already possible through DuckDB's MySQL/Postgres Extensions.
I imagine this could also be useful for developing/testing locally?
It might also provide a way to interchange databases while potentially easing database migrations (vendor to vendor) if ADBC isn't supported by the vendor.
Another potential value-addition could be to provide SQL dialect management by providing Substrait conversions (or sqlglot but looks like the server is Java, so unsure if possible, maybe Graal?).