[Format][FlightRPC] Flight SQL evolution

[lidavidm]

Describe the enhancement requested

From apache/arrow-rs#5731 (comment)

Originally Flight RPC was implemented as a framework wrapping gRPC. This was especially expedient for the C++ implementation. By now it's mostly a weight dragging down Flight users, especially Flight SQL.

If we have the chance to evolve Flight SQL and/or Flight RPC, some changes may include:

• Use a proper gRPC service definition, instead of opaque bytes payloads
• [Format][FlightRPC] Transfer FlightData in pieces #34485
• [C++][FlightRPC] Memory tracking for arrow flight over grpc #37900

Component(s)

FlightRPC, Format

[lidavidm]

Other potential things

• There's a mix of stateful and stateless components, e.g. transactions use explicit handles but sessions are ambient (some of this stems from trying to accommodate JDBC/ODBC more directly)
  • also see: stateles prepared statements
• The name needs to be something that doesn't make people think it's a SQL dialect
• The name needs to be clearly differentiated from Flight RPC (note: ClickHouse implemented a Flight RPC client but calls it Flight SQL!)
  • Flight SQL needs to not be tied into Flight RPC/it needs to be its own implementation
• The metadata methods are incomplete (e.g there's no procedures, types, etc)
• There's no support for multiple result sets (this is also a limitation of using IPC streams in Flight, this has been complained about before too)
• The underlying IPC stream could use enhancements
  • the small result proposal from Micah, which would embed trivial result sets into the initial response
    • Flight requires too many round trips (also see: stateless prepared statements)
  • IPC data often isn't aligned
  • IPC data has to deal with the gRPC message size limits
• Duplication between GetFlightInfo/PollFlightInfo due to evolution over time (though you could argue it makes sense to have both anyways)
• There's no first-class support for result sets/queries as an actual API resource
  • This can be useful if you want to reuse result sets and goes along nicely with retries
• There's no support for result set pagination, limited support for retries
• There's no way to extend the protocol with custom actions
• There's no way to pass extra metadata alongside queries (e.g. a query ID to use)
• It's hard for proxies to interject info into requests/responses
  • This includes telemetry frameworks
• There's very awkward support for getting progress and results from queries incrementally
• There's no support for asking query results to be dumped into cloud storage/some staging area
• There's very limited documentation
• Do we need a way to tune batch sizes to achieve optimal performance across higher-latency links? Or does gRPC (or HTTP clients) achieve that for us automatically? (I've been working with some vendor protocols that do not manage this and require manual tuning)
• Implementations of Flight SQL have a ton of boilerplate, including in libarrow etc.; ideally there is no or extremely minimal support code needed (in other words, we should aim to do something like provide a standalone gRPC service definition => no code needed in libarrow)