[DISCUSSION] Restore Flight SQL support as a pluggable query frontend

[andygrove]

Disclaimer: this issue was drafted with an LLM (Claude Code) at my request. The code, git, and issue references were verified against main at 5e54503, but the design proposal is a starting point for discussion, not a settled plan.

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

Ballista has no way for a generic SQL client to connect to it. Every client must be a Rust program that builds a DataFusion LogicalPlan, serializes it, and speaks SchedulerGrpc. That rules out JDBC/ODBC tooling, BI tools, DBeaver, Python (adbc/pyarrow), and anything else that expects to send SQL text and get Arrow back.

Flight SQL used to fill that gap, and was removed in 46.0.0 (#1227, #1228). It's worth being precise about why, because it wasn't "Flight SQL is a bad fit for Ballista":

• Nobody was maintaining it, and it had accumulated unaddressed bugs (wrong location in FlightEndpoint causing not support Flight SQL JDBC Driver #1012, DecodeErrors using pyarrow flight connector #941, Execution error: Unable to find factory for PARQUET #839, Critical bug for flight sql on version 0.11.0 #756).
• It was substantially incomplete. Of ~40 FlightSqlService methods in ballista/scheduler/src/flight_sql.rs, well over half were Status::unimplemented, including CommandGetSqlInfo — which carried the comment // TODO: implement for FlightSQL JDBC to work. Catalog metadata (GetCatalogs, GetDbSchemas, GetTables as a real RPC, GetXdbcTypeInfo, keys) was stubbed, so driver-level introspection didn't work.
• It was structurally coupled to scheduler internals: it took a concrete SchedulerServer<LogicalPlanNode, PhysicalPlanNode>, reached into server.state.config, faked catalog responses by smuggling well-known strings ("get_flight_info_tables") through the job_id field of a FetchPartition ticket, and carried its own inline Flight proxy.
• Authentication was if user != "admin" || pass != "password", Basic-only.
• It had zero tests (grep -c '#\[test\]' <old file> → 0).

So the removal was right, and reviving that file as-is would be wrong. But the capability is still valuable, and @avantgardnerio made the point directly in #2249: "using this to restore FlightSQL could add a significant audience as well." This issue is to design a new implementation properly.

Why now

Several things have landed since 46.0.0 that make this materially easier than it was in 2023–2025:

1. The scheduler has a real embedded Flight proxy (feat: Add arrow flight proxy to scheduler #1351, ballista/scheduler/src/flight_proxy_service.rs), plus FlightProxy::Local/External advertisement in GetJobStatusResult. This fixes the old implementation's worst structural problem: it built FlightEndpoint locations out of per-executor host:port (and make_local_fieps hardcoded 127.0.0.1:50050 behind a // TODO: use advertise host), which is exactly why it broke behind NAT, Docker, Kubernetes, and load balancers (wrong location in FlightEndpoint causing not support Flight SQL JDBC Driver #1012, Clarify usage of advertise_flight_sql_endpoint #1349). A new implementation can hand clients one stable address and let the scheduler fan out to executors.
2. The scheduler already owns a per-session SessionContext built by a pluggable SessionBuilder (ballista/scheduler/src/state/session_manager.rs:48-85). Server-side SQL planning needs a catalog, and this is an existing, documented extension point for supplying one — rather than the old UX of "re-register your tables with CREATE EXTERNAL TABLE on every new connection."
3. A richer scheduler RPC surface: CreateUpdateSession, RemoveSession, ExecuteQueryPush (streaming status, so no polling loop), GetJobMetrics, CancelJob, CleanJobData.
4. arrow-flight 58.4 is already a workspace dependency with flight-sql-experimental enabled (Cargo.toml:41), so this adds no new dependency — that feature flag is currently enabled for nothing.

Relationship to #2249

This should be built on top of the pluggable-frontend layering @phillipleblanc proposed in #2249, not alongside it:

 Spark Connect / Ballista gRPC / Flight SQL / future protocols
                              |
                  mountable protocol frontend
                              |
               plan decoder / relation translator
                              |
                    DataFusion LogicalPlan
                              |
                 transport-neutral QueryBackend

Flight SQL is arguably the best first consumer of that abstraction, and a useful forcing function for it:

• It is much smaller in scope than Spark Connect (no artifacts, no UDF upload, no Spark relation tree to translate) — SQL text in, Arrow out.
• It exercises every part of the contract anyway: session open/close, auth, plan decode, submit, status, cancel, result streaming, and metadata.
• It has an existing, external, standards-defined client population, so "does the abstraction actually work" gets tested by third-party drivers rather than by our own client.

If #2249's QueryBackend/GrpcQueryFrontend traits land first, this issue implements a FlightSqlFrontend against them. If Flight SQL work starts first, it should define its needs in those terms so the traits are shaped by two real consumers instead of one. Concretely, this frontend must not depend on SchedulerServer internals the way the old one did — that coupling is what made it unmaintainable.

Describe the solution you'd like

Placement

A new optional crate, ballista/flight-sql, exposed through a non-default flight-sql feature on the scheduler (and mountable by embedders into their own Tonic server per #2249). Rationale: it keeps the scheduler's default dependency surface unchanged, and it means the thing can be maintained — or deprecated — independently. That directly answers the maintainability objection in #1227.

Core flow

GetFlightInfo(CommandStatementQuery):

1. Resolve the Flight SQL session (from the handshake token) to a Ballista session_id; CreateUpdateSession if new.
2. Plan the SQL text server-side with that session's SessionContext → LogicalPlan.
3. Submit via the query backend (today: ExecuteQuery), obtaining a job_id.
4. Await completion via ExecuteQueryPush's status stream rather than a poll loop.
5. Build FlightEndpoints whose tickets are the existing Action::FetchPartition protobuf, and whose location is resolved through the existing FlightProxy logic — scheduler/LB by default, direct-to-executor only when explicitly configured.

DoGet(ticket) is then satisfied by the existing proxy path in flight_proxy_service.rs, which already decodes Action::FetchPartition and forwards to the owning executor. Ideally the frontend reuses that service rather than re-implementing a proxy inline, as the old code did.

Open design decisions

These are the things I'd like input on; I don't think any of them should be settled unilaterally.

1. Catalog / planning model. SQL text has to be planned against something. Options: (a) session-scoped DDL only, as before — simple, but poor UX and no cross-connection persistence; (b) embedder-supplied catalog via SessionBuilder — my preference, reuses an existing extension point; (c) scheduler-side configured tables/object stores. Also: should CommandStatementSubstraitPlan be supported for plan-shaped clients, given substrait is already an optional scheduler feature?
2. Long-running queries. Blocking GetFlightInfo until the job completes (old behaviour) will hit client and proxy timeouts for TPC-H-scale queries. PollFlightInfo exists in arrow-flight 58 (the old impl stubbed it) and is designed for exactly this. Do we require it, and do the drivers we care about actually use it?
3. Metadata surface. What is the minimum viable set for real drivers? At minimum CommandGetSqlInfo and GetXdbcTypeInfo, which the old implementation never had — and without which the Arrow Flight JDBC driver doesn't work. Proposal: define the set by "the driver connects and introspects," not by "the trait method exists," and drive catalog answers from the session's real DataFusion catalog rather than hand-built RecordBatches.
4. Prepared statements. Plan cache keyed by handle needs a TTL/eviction policy — the old DashMap<Uuid, LogicalPlan> only shed entries on explicit close, so any client that disconnected leaked. Parameter binding (DoPutPreparedStatementQuery, parameter_schema) was a // TODO: parameters; is bound-parameter support in scope for v1 or explicitly deferred?
5. Auth. Proposal: a pluggable authenticator trait with no built-in credentials, so embedders wire in their own, plus a clearly-labelled insecure dev default (or none at all). Reuse the existing use_tls and endpoint-customization hooks (feat: Cluster RPC customisations to support TLS and custom headers #1400). Anything resembling admin/password should not come back.
6. Lifecycle and cleanup. Map CancelFlightInfo/CancelQuery → CancelJob, connection close → RemoveSession, and result cleanup → CleanJobData. The old implementation cleaned up none of this.
7. Write path. CommandStatementUpdate (DDL/DML/INSERT INTO) — in scope for v1 or deferred?
8. Config naming. advertise_flight_sql_endpoint currently has nothing to do with Flight SQL — it's the plain-Arrow-Flight result-proxy address, misnamed because it predates the Flight SQL removal (see Replace the advertise_flight_sql_endpoint empty-string sentinel with an explicit flag #2281 and my comment on feat: embedded flight proxy explicit config entry #2288). Either this frontend gives that name a legitimate meaning again, or the existing knob should be renamed and the Flight SQL frontend gets its own. Worth settling here rather than accumulating a third overlapping option.

Suggested phasing

Each phase should be independently reviewable and independently useful:

1. GetFlightInfo/DoGet for CommandStatementQuery against a scheduler-configured catalog, endpoints resolved via the Flight proxy, with a Rust FlightSqlServiceClient integration test.
2. Session + handshake + pluggable auth; cancellation and cleanup wired to CancelJob/RemoveSession/CleanJobData.
3. Metadata RPCs sufficient for the Arrow Flight JDBC driver to connect and introspect, sourced from the real catalog.
4. Prepared statements with parameter binding; PollFlightInfo for long queries.
5. Docs (a replacement for the removed docs/source/user-guide/flightsql.md) and a docker-compose example that works from outside the container network — the scenario Clarify usage of advertise_flight_sql_endpoint #1349 reported as broken.

Definition of done (proposed)

• A third-party client works end-to-end: Arrow Flight SQL JDBC driver and adbc/pyarrow both connect, introspect, and run TPC-H queries against a multi-executor cluster.
• Results are fetchable from outside the cluster network without leaking executor addresses to the client.
• Integration tests in CI covering the query, metadata, prepared-statement, and cancellation paths — non-negotiable this time, given the old implementation shipped with none.
• Regression coverage for the specific historical failures: wrong location in FlightEndpoint causing not support Flight SQL JDBC Driver #1012 (endpoint location correctness), DecodeErrors using pyarrow flight connector #941 (pyarrow decode), Critical bug for flight sql on version 0.11.0 #756, Execution error: Unable to find factory for PARQUET #839.
• No new coupling to SchedulerServer internals.

Describe alternatives you've considered

• Resurrect flight_sql.rs from 45.0.0. Cheapest start, and it's a genuinely useful reference for statement handling and endpoint construction (git show 559bcf29^:ballista/scheduler/src/flight_sql.rs). But it's coupled to concrete scheduler generics, predates the Flight proxy, targets an arrow-flight API that has drifted several major versions, and carries the incompleteness that got it removed. Reference, don't restore.
• Reuse an existing DataFusion Flight SQL server implementation (e.g. from datafusion-contrib, which Remove flight-sql from ballista in 46.0.0 #1227 floated as the home for the old code) and adapt it to submit through Ballista instead of executing locally. Worth evaluating seriously before writing a new server from scratch — I haven't assessed how well its abstractions fit distributed submission and multi-endpoint results. If it fits, most of this issue reduces to a QueryBackend implementation.
• Do nothing; wait for Spark Connect ([DISCUSSION] Add support for Spark Connect protocol #2249). Spark Connect is a much larger surface and reaches a different audience. It doesn't serve JDBC/ODBC/BI tools, so it isn't a substitute.
• Client-side Flight SQL gateway (a separate process holding a DataFusion SessionContext that forwards to Ballista). Keeps the scheduler clean, but duplicates session and catalog state and adds a hop; probably strictly worse than a mountable frontend once [DISCUSSION] Add support for Spark Connect protocol #2249 exists.

Additional context

• Removal: Remove flight-sql from ballista in 46.0.0 #1227 → feat: remove flight-sql from scheduler #1228 (559bcf2, released 46.0.0). Also removed docs/source/user-guide/flightsql.md.
• Historical bugs worth reading before designing: wrong location in FlightEndpoint causing not support Flight SQL JDBC Driver #1012, DecodeErrors using pyarrow flight connector #941, Execution error: Unable to find factory for PARQUET #839, Critical bug for flight sql on version 0.11.0 #756, Add optional flag which advertises host for Arrow Flight SQL #418, Add user guide section on Flight SQL support #230.
• The Flight proxy this would build on: Clarify usage of advertise_flight_sql_endpoint #1349 → feat: Add arrow flight proxy to scheduler #1351 (ac49c18), plus Replace the advertise_flight_sql_endpoint empty-string sentinel with an explicit flag #2281/feat: embedded flight proxy explicit config entry #2288 currently cleaning up its configuration.
• Pluggable frontend discussion: [DISCUSSION] Add support for Spark Connect protocol #2249. @phillipleblanc mentioned intending to open an epic for that work — if that lands, this should become a child of it.

I'm happy to help shepherd this, but I'd rather agree the layering and the open questions above first, particularly (1) the catalog model and (3) the driver-driven definition of the metadata surface.

[avantgardnerio]

I'm the worst kind of stakeholder here: I'd love to see it Implemented, but probably can't justify my own time to contribute 😕

[phillipleblanc]

Unfortunately the FlightSQL protocol as is doesn't match well with the async query nature of Ballista. This was something we were thinking about how to enable in Spice, and we eventually scoped FlightSQL out from being able to start Ballista jobs - it didn't match our HA requirements and the timeout issues that you mentioned.

I do think its useful to have a FlightSQL frontend for Ballista to make it easier for testing though.

Catalog / planning model. SQL text has to be planned against something.

embedder-supplied catalog via SessionBuilder

Spice supplies the catalog via SessionBuilder, so that matches what we already do. I'm not sure who the users of a standalone Ballista cluster are and what their requirements would be. If its just for testing, then maybe a simple bootstrap set of DDL commands by the scheduler would suffice?

Long-running queries. Blocking GetFlightInfo until the job completes (old behaviour) will hit client and proxy timeouts for TPC-H-scale queries.

I don't think that FlightSQL as designed works for this. We may want to think about either implementing our own Ballista extension to FlightSQL that has first class support for submitting a job via SQL, being able to poll for its status, retrieve results, etc. Could potentially do this without any protobuf changes if we used custom Flight Actions and/or PollFlightInfo - but I think its worth at least prototyping what a custom FlightSQL extension looks like that would allow clients the ability to manage these long-running jobs. If its useful enough, then we could even think about trying to update the FlightSQL spec itself to have this.

But I think that would be a separate feature, getting regular FlightSQL working again is still a good goal.

Metadata surface.

Agree that we should implement enough for clients to be usable. You are free to reference the spiceai implementation for FlightSQL here which does implement enough for real clients: https://github.com/spiceai/spiceai/blob/trunk/crates/runtime/src/flight/mod.rs

Prepared statements.

Agree we should have a TTL/eviction policy for this. spiceai has a session store that uses moka for this: https://github.com/spiceai/spiceai/blob/trunk/crates/runtime/src/flight/session.rs#L80

Auth

Flight already has an authentication method that Ballista can use, anything additional can be implemented using tonic middleware so I don't think Ballista needs anything special for this.

Lifecycle and cleanup

Yes, and if we go down the custom protobuf extension route then we could potentially tie into that as well.

Write path.

I think deferring for now makes sense.

Config naming

My understanding is that this is for the executors? Reusing it for the scheduler frontend could be confusing.