Proposal: Create `dfdb`, a new CLI different than `datafusion-cli` with pre-built integrations

[alamb]

TLDR

• Keep datafusion-cli in the apache/datafusion repo
• Make a new repo with a new CLI called dfdb (or datafusion-cli++or dfcli) which is purposely designed for running queries against a wide variety of pre-integrated sources

Problem Statement

As of today, datafusion-cli (docs) serves two roles:

1. A debugging / testing tool for the DataFusion query engine developers
2. A CLI tool for actually doing useful processing if files (locally and remotely using object store), similar to the duckdb CLI tool

It is really sweet to have a CLI that lets you query a directory of parquet files

DataFusion CLI v41.0.0
>
> select "WatchID", "EventDate", "URL" from hits_partitioned limit 10;
+---------------------+-----------+------------------------------------------------------------------------------------------------------+
| WatchID             | EventDate | URL                                                                                                  |
+---------------------+-----------+------------------------------------------------------------------------------------------------------+
| 6904841588848398438 | 15895     | 687474703a2f2f736d6573686172696b692e72752f6d616e756661637475726572363437                             |
...
| 7551542980199423249 | 15895     | 687474703a2f2f736d6573686172696b692e72752f6d616e756661637475726572363437                             |
+---------------------+-----------+------------------------------------------------------------------------------------------------------+
10 row(s) fetched.
Elapsed 0.059 seconds.

However, similarly to the discussion with have had with datafusion-pytyhon this dual role leads to a tension between keeping the core lean and easier to embed (e.g. fewer dependencies) and making a better CLI experience

Examples of Friction

I have recently seen some PRs that are basically integrations that would make datafusion-cli a better end user tool, but bring more dependencies and complexity to DataFusion. For example

1. Hugging face Feat: Implement hf:// / "hugging face" integration in datafusion-cli #10792 from @xinlifoobar
2. FlightSQL: Generic FlightTableFactory with a default FlightSqlDriver #11938 from @ccciudatu

I realize I have been partly responsible for this mess and for that I apologize.

Proposal

I propose resolving this conflict by creating a new repository for the "CLI tool people actually use"

We would keep datafusion-cli as it is, a relatively small and a thin wrapper around the core engine. I don't think we should remove features but we also wouldn't add them (other than what was added to the engine by default)

We would add many new features / capabilitues to this dfdb tool

Examples of new features

There are several obvious examples of integrations that would be super useful for users of a CLI tool but not appropriate for the datafusion repo (due to circular dependencies, for example):

• apache iceberg: https://github.com/apache/iceberg-rust
• delta-rs: https://github.com/delta-io/delta-rs
• hudi: https://github.com/apache/hudi-rs
• The providers in https://github.com/datafusion-contrib/datafusion-table-providers from @phillipleblanc et al

@philippemnoel actually referrs to the lack of built in Apache Iceberg support in his blog about switching to using duckdb. This is sad given all the code to use datafuson and delta exists, there just isn't a pre-integrated binary that shows how to hook it up and it easy to get up and use

Other cool features

There are many other cool features I have dreamed about adding to a CLI that might be more appropriate in a separate repo. Some ideas to inspire:

1. Local catalog support (imagine if you could store your CREATE EXTERNAL TABLE definitions in a file someere (.open <filename> style)
2. Local parquet metadata cache (imagine being able to cache the parquet metadata for 100s of files in object store in some sort of persistence format so future queries were fast)
3. SQL auto completion,
4. etc.

Open questions

Should the new tool be in the datafusion-contrib organization or the apache organization?

The tradeoffs are that datafusion-contrib could move faster / has less governance overhead, but would also lose the apache community

I personally suggest we start with this tool in the datafusion-contrib organization and if there is interest we can discuss bringing it back to the apache organization.

[edmondop]

I had a related problems a couple of weeks ago, I wanted to distribute a CLI that had some additional udfs bundled with it.

I wonder if we need to set up a plugin system for the CLI. I am happy to make a design proposal about it @alamb

[alamb]

I wonder if we need to set up a plugin system for the CLI. I am happy to make a design proposal about it @alamb

That would be awesome

Another thing that comes to mind might be for you to fork this datafusion-cli code and bundle your UDFs with it.

Or we could publish most of the code for this CLI to crates.io with the idea that people could make their own CLI mashups with a little bit of configuration setup. The ideas are endless!

[matthewmturner]

Love this idea. I had been working on something similar in the past but unfortunately life got in the way so wasnt able to push it as far as I wanted.

I am currently building a new terminal app with DataFusion at the core, but it is more domain specific than general purpose like I think the new proposed tool is expected to be (and if i wasnt working on that, I would probably volunteer to push this forward). That being said, and depending on the direction of this new tool, there could be some potential overlap / common functionality with what im working on. If thats the case then I would be very happy to collaborate on those pieces. I hope to open source what I'm working on in the fall around the time of v0.1 release. I'll keep my eye on this as it progresses though.

[milenkovicm]

Making generic plugin system would be great, but may be a bit of a challenge with lack of stable rust ABI.

Making some kind of CliContextBuilder where all udfs, table factories ... could be registered may be easier to do.
Maybe existing CliSessionContext could be extended adding one liner methods for most common extensions

[andygrove]

I think this is a great idea.

[westonpace]

I don't know if others are interested / if this matches the need but it would be cool to make a "CLI-driven query engine frontend" that isn't coupled to any particular query engine (e.g. it could send queries as either SQL or substrait, potentially via something like adbc).

In other words, it could be something like squirrel / dbeaver for analytics.

[phillipleblanc]

I think this is a great idea. Happy to do the work to integrate https://github.com/datafusion-contrib/datafusion-table-providers into it, I think it would be a cool use-case to have.

[edmondop]

Will do some homework. Maybe we only need a datafusion-cli repo that one can fork stand-alone to create a proprietary CLI distribution and the plugin system is effectively an overkill

How should we go about dfdb? Do we think we should have "flavors", maybe like the following?

• dfdb-minimal (maybe datafusion-cli ?)
• dfdb-standard (whatever that means?)
• dfdb-full (whatever that means?)

Or should we aim at having a single "flavor" only?

[jayzhan211]

I don't know if others are interested / if this matches the need but it would be cool to make a "CLI-driven query engine frontend" that isn't coupled to any particular query engine (e.g. it could send queries as either SQL or substrait, potentially via something like adbc).

In other words, it could be something like squirrel / dbeaver for analytics.

I like the idea to have a cli frontend that is query engine agnostic (datafusion, duckdb), table agnostic (iceberg, delta), file agnostic (parquet, lance), and even more.

[alamb]

I like the idea to have a cli frontend that is query engine agnostic (datafusion, duckdb), table agnostic (iceberg, delta), file agnostic (parquet, lance), and even more.

This sounds like "Ibis" for CLIs and I think is a neat idea

However, I am personally not likely to work on such a thing -- I am far more interested in showing off / using the DataFusion execution engine more broadly, in part as I think it will drive more use and thus more contributions back to DataFusion

So therefore I am not opposed to creating some sort of pluggable backend query engine system, but I think I would like to focus on the DataFusion one.

I am thinking what I will try to do if no one beats me to it is to sketch out what a dfdb repo might look like

1. basically copy the datafusion-cli's code / docs into its own repo
2. polish up the code / APIs perhaps to make it clearer how to register custom functions, table providers, etc

[samuelcolvin]

I like the idea to have a cli frontend that is query engine agnostic (datafusion, duckdb), table agnostic (iceberg, delta), file agnostic (parquet, lance), and even more.

Surely this should just be a CLI that speaks arrow flight SQL? I've tried to persuade ClickHouse to adopt arrow flight SQL to get around their woeful Python client.

That sounds interesting, but I agree with @alamb that that's a different question.

---

I like the broad idea here, while I like datafusion-cli, it definitely feels incomplete.

Where I disagree (I think) with @alamb is around what it lacks — my main problem with datafusion-cli is the "UI" — all the small parts of the best CLIs which make the difference:

• syntax highlighting (like duckdb)
• auto-complete - aka a language server for datafusion (like pgcli)
• good multiline vs single line support (like pgcli)
• good error summaries e.g. point are the part of the statement that's wrong (like ipython)
• pretty tables (like duckdb or rich, not "ugly" ascii dashes), while we at it also support for more display formats like markdown
• convienient CSV and JSON export (like duckdb) — maybe datafusion-cli already does this, I haven't tried
• convient way to display rows as "details" when they're too long to show as tables (like clickhouse-cli)
• proper terminal interface for horizontal scrolling (I think rich/textual has good support for this)
• good introspection shortchuts (like pgcli) - e.g. \d or \dt
• support for running multiple queries from a file (like psql I think)
• (minor) good support for timing queries, on by default
• JSON query support, I guess since Rust has a poor story when it comes to dynamic linking, just bundling datafusion-functions-json would be good enough

There's one more thing that I've wanted from a database CLI many times and nothing (AFAIK) supports:

• Plotting right from the CLI - I guess the obvious solution is terminal ploting like this (more here), you could could also imagine a plot command opening a plot in a webbrowser for easier interaction

Anyway, that's my wish list, as you might guess I've thought about this a fair bit!

---

One last thing I'll say — virtually all of the above features would be independent of the query engine being used, so there is a good argument to build one great CLI user experience (or let someone else build it) and make it pluggable into any query engine that speaks Arrow Flight SQL, whether that be over a network, or just as an API within a single binary or between dynamically linked libaries.

[findepi]

One last thing I'll say — virtually all of the above features would be independent of the query engine being used, so there is a good argument to build one great CLI user experience (or let someone else build it)

this seems to be the goal of the https://github.com/xo/usql project.
I am not saying DF CLI shouldn't try to address those goals, just acknowledging it's both ambitions and potentially not central to the project (DF considers itself a query engine).

[alamb]

Where I disagree (I think) with @alamb is around what it lacks — my main problem with datafusion-cli is the "UI" — all the small parts of the best CLIs which make the difference:

indeed, if the goal is to make a duckdb replacement this would be a pretty great list for someone to work on

However, as my personal goal is not (at least not yet 😆 ) to make a duckdb replacement, so I am not likely going to able to focus on this list. I will definitely steal it as "wishlist for dfdb" however 🚀