Feature: Support Arrow Flight SQL protocol

[kesavkolla]

Summary

Currently databend support MySQL protocol, as an alternative to this databend also should support Arrow Flight SQL protocol.

databend is dealing with usecases of data warehouse/lakehouse where the data volumes are high. When a client is interacting with databend to query for data; it would be performant to support arrow data format. Typically lakehouse stores data in parquet file with MySQL protocol databend has to do deserialization from parquet to arrow and then back to MySQL data types. Again on the caller end people use data frames or MySQL result iterators this also requires serialization of types. With Arrow Flight SQL all of these serialization costs can be avoided. databend will convert parquet to arrow and does it's query operations then send arrow data directly as result. Clients can take that arrow data and can even directly send the arrow data to all the way visualization layers.

[sundy-li]

It's a community feature now, someone interested in this could take this task.

[johnhaxx7]

Hi @sundy-li , can you please share some general ideas on how to get start on this?

[BohuTANG]

@johnhaxx7

https://github.com/apache/arrow-datafusion/blob/a870e460d20e7d00bc25fefe8f28c25f88e0ffac/datafusion-examples/examples/flight_sql_server.rs

[sundy-li]

Paper related to this feature: https://www.vldb.org/pvldb/vol10/p1022-muehleisen.pdf

[johnhaxx7]

Thanks for the info! I'll take a closer look.

[xinlifoobar]

Interested in this feature also. Any hands-on work is in planning yet I could help.

[sundy-li]

We already have flight protocol that could be used to communicate with other query nodes in cluster.

https://github.com/datafuselabs/databend/blob/23281a29ba0a89f9428e11fc4ccf3b0b83ec5a9e/src/query/service/src/api/rpc_service.rs

But it's for internal usage. We can have similar handler based on flight.

[sundy-li]

c++ implementation example, flight on duckdb/sqlite.

https://github.com/voltrondata/flight-duckdb-example

[xinlifoobar]

Thanks for providing those. Just took a closer look at protocol differences between Arrow flights and it seems the Databend flight rpc service would be a really strong start!

Just 2 follow-ups during the investigations:

• By mentioning 'for internal usage', is this means the below functions described in arrow protocol are not implemented?
  https://github.com/datafuselabs/databend/blob/93f154a207bbb48525108500965ce6f483894000/src/query/service/src/api/rpc/flight_service.rs#L65-L89

• There are some codes that already covered the protocol details, i.e., query listener, planner, executer, and result set serializations. Is there anything I missed here that still needs to be added?

[sundy-li]

There are some codes that already covered the protocol details

The api/rpc This is internal usage in cluster query (server to server).

But within this issue, we are going to support client --- server protocol.

[xinlifoobar]

There are some codes that already covered the protocol details

The api/rpc This is internal usage in cluster query (server to server).

But within this issue, we are going to support client --- server protocol.

Sorry just didn't get it because the RPC listener is already on.

https://github.com/datafuselabs/databend/blob/main/src/binaries/query/main.rs#L196-L203

So to finish this issue, it just includes:

1. Expose the RPC listener in the Console UI interface, and this should be done via adding a new service here https://github.com/datafuselabs/databend/tree/c1d824f0824664a539fefca3f41fafe941bb2f01/src/query/service/src/servers.
2. Test any incompatibilities issues with the server and a popular arrow flight driver like jdbc or odbc?

[youngsofun]

I am working on it. if all goes well, the first version that can work with JDBC will be available by next weekend.

[kesavkolla]

Awesome. Curious why would anyone want to use JDBC with flight. The whole point of flight server is we can get arrow data directly in columnar format. JDBC makes again row oriented data.

[sundy-li]

JDBC is over flight SQL. If we support flight SQL, we can seamlessly connect to the jdbc ecosystem (many third-party tools use jdbc to connect).

https://www.dremio.com/blog/jdbc-driver-for-arrow-flight-sql/

But we will not use JDBC inside databend.

[youngsofun]

Awesome. Curious why would anyone want to use JDBC with flight. The whole point of flight server is we can get arrow data directly in columnar format. JDBC makes again row oriented data.

1. many data tools used JDBC to connect various databases
2. arrow has an official JDBC https://github.com/apache/arrow/tree/main/java/flight/flight-sql-jdbc-driver, and it seems to me the interface design of flight-sql is largely affected by JDBC, we can use it for testing.

by the way, do you know any client-side tools that use flight-SQL and columnar format directly? @kesavkolla

[xinlifoobar]

There are some codes that already covered the protocol details

The api/rpc This is internal usage in cluster query (server to server).
But within this issue, we are going to support client --- server protocol.

Sorry just didn't get it because the RPC listener is already on.

https://github.com/datafuselabs/databend/blob/main/src/binaries/query/main.rs#L196-L203

So to finish this issue, it just includes:

1. Expose the RPC listener in the Console UI interface, and this should be done via adding a new service here https://github.com/datafuselabs/databend/tree/c1d824f0824664a539fefca3f41fafe941bb2f01/src/query/service/src/servers.
2. Test any incompatibilities issues with the server and a popular arrow flight driver like jdbc or odbc?

I must apologize to @sundy-li for this because there are a lot of misunderstandings before I started to investigate Arrow Flight SQL. There are some deep dives afterward I could share.

• By implementing arrow fligh sql client-server protocol, there is a pretty nice Flight.proto pb file for reference. At this stage, the RPC service could handle them but some actions, like do_handshake, do_get, do_put are not implemented.
• The current databend-query includes an arrow crate, it is recommended to include an arrow-flight crate also for some grpc data structures.
• Some pretty good examples could be found in duckdb's doc SQL on Apache Arrow. I like the idea to read from the arrow flight server and transfer to the python pandas frame for computations.

Let me know if I could still be of help @youngsofun

[sundy-li]

done in #10732