chrisjc

Any thought about a pass-through server for various non-local databases?

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?).

duckdb -cmd 'CREATE TABLE my_data AS FROM READ_PARQUET($$data.parquet$$)' -ui

`duckdb -ui sqlitedb.db` should work bc duckdb can read sqlite files. If it doesn't autoload extension, you can INSTALL/LOAD in to your ~/.duckdbrc

    duckdb -ui pre_seeded_db.db

    duckdb -ui -init startup.sql

where startup.sql contains various statements/commands like `.open pre_seeded_db.db`

> In the worst-case scenario, they would be 2-3 years behind the cutting edge, which is not mission-critical.

It's also worth considering how such an event might affect the US and allies. Would it slowdown, perhaps even halt certain operations/efforts for the US. For instance, all those chips the US needs to build supercomputers for "weather research". ;)

Haven't tried it out yet, but the release notes look very promising esp relating to Arrow interactions

- Push dynamically generated join filters through UNION, UNNEST and AGGREGATE

- Fix arrow table filters

- [Arrow] Fix scan of an object providing the PyCapsuleInterface when projection pushdown is possible.

- DuckDB Arrow Non Canonical Extensions to use arrow.opaque

- Arrow Extension Type to be registered in DuckDB Extensions

- [Python] Use an ArrowQueryResult in FetchArrowTable when possible.

https://github.com/duckdb/duckdb/releases/tag/v1.2.0

Perhaps I don't fully understand what you're saying

    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');

Or do you mean that you can't use `query_table` with a macro identifier in place of the table identifier as input (after all, where would put the macro args)? Perhaps using `query(...)` instead would work?

    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');

Or maybe through some other kind of gymnastics https://duckdb.org/2024/03/01/sql-gymnastics.html

I've been eagerly awaiting this for a couple of months now. And I've long wondered why there hasn't been such an implementation/extension for Flight especially as there are extensions for mysql, postgres, sqlite...

Only seems natural that an extension could developed for ATTACHing to a Flight server.

Looking forward to watching your talk.

For read-oriented interactions...

    ADBC --sql--> Flight SQL Server --flight--> Flight Servers (plural) --> datasources

or

    SELECT * FROM <relation> WHERE ...

where the "relation" is a collection of Arrow stream from a cluster of Flight Servers.

Remember that Flight/Flight-SQL is not a query engine replacement, but rather a way to communicate and exchange data between components with different responsibilities efficiently.

Thank you for all the work you guys do. The Arrow ecosystem is just absolutely incredible.

My few gripes related to interop with duckdb are related to Arrow scanning/pushdowns. And this extends to interop with other projects like pyiceberg too.

Registering an Arrow Dataset (or pyiceberg scan) as a "duckdb relation" (virtual view) is still a little problematic. Querying these "relations" does not always result in an optimal outcome.

For Arrow datasets, you can intercept the duckdb pushdown, but duckdb will have already "optimized" the plan to its liking, and any scanning restrictions that may have been more advantageous based on the nuances of the dataset might have been lost. Eg:

    WHERE A IN (3, 5, 7)

is presented to the Arrow scanner (pushdown) as "A is between 3 and 7 inclusive" (https://duckdb.org/docs/guides/performance/indexing.html#zon...).

Perhaps in a similar way, turning an pyiceberg scan into a relation for duckdb effectively takes the entire scan and creates an Arrow Table rather than some kind of pushdown/"scan plan" for duckdb to potentially make more efficient with its READ_PARQUET() functionality.

Most of this is probably dependent on duckdb development, but all of the incredible interop work done across communities/ecosystems so far gives me a lot of confidence that these will soon be matters of the past.

And all the Arrow parts work together quite nicely.

    ADBC client --> Flight SQL (duckdb/whatever) --> Flight --> ?

The result highlights your exact point: why take your data and transpose it twice?

It's quite an exciting space, and lots of projects popping up around Arrow Flight and duckdb.

Since you're using python, have you looked into sqlglot? I think it has some pretty-print options.

https://github.com/tobymao/sqlglot

Maybe something like this? https://duckdb.org/2024/10/02/pyodide.html

Probably also worth mentioning that DuckDB can interact with SQLite dbs.

https://duckdb.org/docs/extensions/sqlite.html https://duckdb.org/docs/guides/database_integration/sqlite.h...

Thus potentially making duckdb an HTAP-like option.

Probably not exactly what you mean or asking for, but the work Motherduck is doing looks promising.

https://motherduck.com/blog/differential-storage-building-bl...

Hopefully it finds its way into duckdb's repo some day.

Ibis looks very promising.

There are also other ways to use both.

https://duckdb.org/docs/guides/python/polars.html

All of this dataframe compatibility is awesome. (much thanks to Arrow and others)

Perhaps not exactly what you're talking about, but maybe? (unsure bc the with statements are sometimes called "temp tables")

https://duckdb.org/docs/sql/query_syntax/with#cte-materializ...

Obviously, the materialization is gone after the query has ended, but still a very powerful and useful directive to add to some queries.

There are also a few DuckDB extensions for pipeline SQL languages.

https://duckdb.org/community_extensions/extensions/prql.html

https://duckdb.org/community_extensions/extensions/psql.html

And of course dbt-duckdb https://github.com/duckdb/dbt-duckdb

Although only experimental and probably off topic to the discussion, it's worth mentioning DuckDB also provides a Spark API implementation.

https://duckdb.org/docs/api/python/spark_api

Not the original parent, so unsure of their use-case. But I've seen the approach where some/basic development can be done on duckdb, before making its way to dev/qa/prd.

Something like your project might enable grabbing data (subsets of data) from a dev enviroment (seed) for offline, cheap (no SF warehouse cost) development/unit-testing, etc.

Real awesome project. This would be even better if some of the differential storage functionality makes its way into duckdb.

https://motherduck.com/blog/differential-storage-building-bl...

Unsure if it's even possible, but if there was a way to write out (simulate) the streams to WAL files, you might be able to accomplish the same without having to consolidate the duckdb file every time.

A couple of other ideas, that may or may not diverge from your project's purpose or simplicity...

It's also too bad that duckdb isn't included in the out-of-the-box Snowpark packages, potentially allowing you to run all of this in a Snowflake procedure/DAG and persisting the duckdb file to object storage. You could of course achieve this with Snowflake containers. (But this would probably ruin any plans to support Redshift/BigQuery/etc as sources)

If the source tables were Iceberg, then you could have an even simpler duckdb file to produce/maintain that would just wrap views around the Iceberg tables using the duckdb iceberg extension.

    create view x 
    as select 
        * 
    from 
        iceberg_scan('metadata_file');

If you're going to enable change tracking on tables then perhaps another solution is to use CHANGES to select the data from the table instead of reading streams. That way you could use the same timestamp across all the selects and get better consistence between your materializations to duckdb.

    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

Here's another similar (but different) project that I've been tracking https://github.com/buremba/universql

Perhaps similar to https://github.com/duckdb/dbt-duckdb , but SQLMesh instead of DBT obviously.

It should be just as easy as adding:

    authenticator="externalbrowser"

Adding a Snowflake connection configuration option that allowed for standard Snowflake connection conventions might be a good option. That way you could connect to Snowflake with your existing configurations (.snowsql/, .snowflake/). Or explicitly specify by adding matching args/params to your project's config.

    # myconf.toml
    [test-connection]
    account=mysfaccount
    authenticator="externalbrowser"
    ...

    # config/config.yaml
    source:
        type: snowflake
        connection:
            file_path: test-connection
            name: ./myconf.toml
        change_tracking_database: melchi_cdc_db
        change_tracking_schema: streams

    sf.connect(connection_name=?, connections_file_path=Path(?).resolve())

Yes, DuckDB does do Iceberg.

https://duckdb.org/docs/extensions/iceberg

Interested to hear why you switched from DataFusion?

DuckDB is really for OLAP/Columnar (analytical workloads) whereas SQLite is more for OLTP/Row-based (transactional).

When you start running aggregations/windows over large amounts of data, you'll soon see the difference in performance.

Via the AWS extension

https://duckdb.org/docs/guides/network_cloud_storage/s3_impo... https://duckdb.org/docs/extensions/aws

DuckDB can do some pushdowns to certain file formats like parquet, but every release seems to be getting better and better at doing it.

Parquet pushdowns combined with Hive structuring is a pretty good combination.

There are some HTTP and Metadata caching options in DuckDB, but I haven't really figured out how and when they really making a difference.

DuckDB?

https://duckdb.org/2024/03/29/external-aggregation.html

https://duckdb.org/2021/06/25/querying-parquet.html

If your DB is mysql or postgres, then you could read a stream from parquet, transform inline and write out to your DB

https://duckdb.org/2024/01/26/multi-database-support-in-duck...

And an unrelated, but interesting read about the parquet bomb

https://duckdb.org/2024/03/26/42-parquet-a-zip-bomb-for-the-...

In my experience, it's always come down to either using a specific database type for the wrong job, or using a database type the wrong way.

- Treating an OLAP like an OLTP.

- Using a document store for relational data bc managing relations in SQL is tedious and slows development/progress/features.

- Using OLTP for analytics.

- and so on...

And the usual reaction ensues. Conversations begin about migrating to another product while not really paying attention to interaction patterns and the intent/reason for the various workloads.

Of course such migrations only lead to either...

- Place-shifting the issues, or

- Resolving the issues that lead to the migration while creating new ones that didn't exist before. Workloads that were suited to the existing DB are now mismatched to the new one.

However, Postgres seems to be a pretty safe bet when such migrations are undertaken due to how versatile it is. Personally, I don't care which database is used (within reason of course). Just use the right database type for the job.

Keep an eye on this link. New support is being added every so often.

https://github.com/davidgasquez/awesome-duckdb?tab=readme-ov...

Though it certainly takes care of the usual "We reached out to ???, but they didn't respond" statement.