Ah - somehow I didn't think of that. Yes, we should just implement it in the same way prepared statements are already implemented.
On Thu, Jun 30, 2022, at 19:42, Micah Kornfield wrote: >> >> It would also then be good to make explicit the statefulness of >> connections in Flight SQL. While that is sort of an obvious constraint, it >> is at odds with how gRPC is usually used (especially in the presence of >> load balancing). > > > I'm not sure I understand where the statefulness requirements come in? > Could you elaborate? It seems that a transaction could be an opaque ID on > operations? > > On Thu, Jun 30, 2022 at 2:47 PM James Duong <jam...@bitquilltech.com.invalid> > wrote: > >> This is a bit of a tangent from the original discussion about >> Substrait integration. >> >> Flight SQL would definitely benefit from transaction RPC commands for >> building bridge drivers. I'm also wondering if there should be an RPC call >> to cancel a running query, as opposed to just having the client terminate >> streams. This would allow a multi-process application to cancel work across >> processes. >> >> On Thu, Jun 30, 2022 at 1:35 PM David Li <lidav...@apache.org> wrote: >> >> > Reviving this discussion: would people be interested in seeing a >> > sketched-out CommandSubstraitQuery et. al.? >> > >> > Additionally, while working on ADBC, I realized: does Flight SQL need >> > explicit Commit/Rollback commands? This would presumably be necessary if >> we >> > want to build ODBC/JDBC drivers on top, since those standards have >> explicit >> > commands, and Flight SQL doesn't have the luxury of a driver to issue >> > database-specific SQL to implement these. >> > >> > It would also then be good to make explicit the statefulness of >> > connections in Flight SQL. While that is sort of an obvious constraint, >> it >> > is at odds with how gRPC is usually used (especially in the presence of >> > load balancing). >> > >> > On Sun, Mar 6, 2022, at 14:44, Gavin Ray wrote: >> > > Got it, thank you David! >> > > I started prototyping the implementation last night, hopefully I will >> > make >> > > some good progress and have something basic functioning soon. >> > > >> > > RE: The metadata thing -- I think both Calcite and Teiid have solid >> > > interfaces for defining what capabilities a datasource has. >> > > >> > >> https://github.com/teiid/teiid/blob/8e9057a46be009d68b2d67701781f1f8c175baa7/api/src/main/java/org/teiid/translator/ExecutionFactory.java#L349-L1528 >> > > >> > > It's probably not possible to make something universal, but it seems >> like >> > > you could get pretty close to most common functionality/capabilities >> > > >> > > >> > > On Sat, Mar 5, 2022 at 11:48 PM Kyle Porter <ky...@bitquilltech.com >> > .invalid> >> > > wrote: >> > > >> > >> Yes, we should, where possible, avoid any one of metadata. This is >> where >> > >> other standards fail in that applications must be custom built for >> each >> > >> data source, if we standardize the metadata then applications can at >> > least >> > >> be built to adapt. >> > >> >> > >> On Sat., Mar. 5, 2022, 6:54 p.m. David Li, <lidav...@apache.org> >> wrote: >> > >> >> > >> > Yes, GetSqlInfo reserves a range of metadata IDs for Flight SQL's >> > use, so >> > >> > the application can use others for its own purposes. That said if >> they >> > >> seem >> > >> > commonly applicable maybe we should try to standardize them. >> > >> > >> > >> > I think what you are doing should be reasonable. You may not need >> > _all_ >> > >> of >> > >> > the capabilities in Flight SQL for this (e.g. all the various >> metadata >> > >> > calls, or prepared statements, perhaps) but I don't see why it >> > wouldn't >> > >> > work for you. >> > >> > >> > >> > On Fri, Mar 4, 2022, at 19:03, Gavin Ray wrote: >> > >> > > To touch on the question about supported features -- is it >> possible >> > to >> > >> > > advertise arbitrary/custom "capabilites" in GetSqlInfo? >> > >> > > Say that you want to represent some set of behaviors that >> FlightSQL >> > >> > > services can support. >> > >> > > >> > >> > > Stuff like "Supports grouping by multiple distinct aggregates", >> > >> "Supports >> > >> > > self-joins on aliased tables" etc >> > >> > > This is going to be unique to each implementation, but I couldn't >> > >> > determine >> > >> > > whether there was a way to express arbitrary capabilities >> > >> > > >> > >> > > Also, in case it's helpful I put together an ASCII diagram of what >> > I'm >> > >> > > trying to do with FlightSQL >> > >> > > If anyone has a moment, would appreciate input on whether it's >> > >> feasible/a >> > >> > > good idea >> > >> > > >> > >> > > https://pastebin.com/raw/VF2r0F3f >> > >> > > >> > >> > > Thank you =) >> > >> > > >> > >> > > >> > >> > > On Fri, Mar 4, 2022 at 2:37 PM David Li <lidav...@apache.org> >> > wrote: >> > >> > > >> > >> > >> We could also add say CommandSubstraitQuery as a distinct >> message, >> > and >> > >> > >> older servers would just reject it as an unknown request type. >> > >> > >> >> > >> > >> -David >> > >> > >> >> > >> > >> On Fri, Mar 4, 2022, at 17:01, Micah Kornfield wrote: >> > >> > >> >> >> > >> > >> >> 1. How does a server report that it supports each command >> type? >> > >> > Initial >> > >> > >> >> thought is a property in GetSqlInfo. >> > >> > >> > >> > >> > >> > >> > >> > >> > This sounds reasonable. >> > >> > >> > >> > >> > >> > >> > >> > >> >> What happens to client code written prior to changing the >> > command >> > >> > type >> > >> > >> >> to be a oneOf field? Same for servers. >> > >> > >> > >> > >> > >> > >> > >> > >> > It is transparent from older clients (I'm 99% sure the wire >> > protocol >> > >> > >> > doesn't change). Servers is a little harder. The one saving >> > grace >> > >> > is I >> > >> > >> > don't think an empty/not-present SQL string would be something >> > most >> > >> > >> servers >> > >> > >> > could handle, so they would probably error with something that >> > while >> > >> > >> > not-obvious would give a clue to the clients (but hopefully >> this >> > >> would >> > >> > >> be a >> > >> > >> > non-issue because the capabilities would be checked for clients >> > >> > wishing >> > >> > >> to >> > >> > >> > to use this feature first). >> > >> > >> > >> > >> > >> > -Micah >> > >> > >> > >> > >> > >> > On Fri, Mar 4, 2022 at 1:50 PM James Duong < >> > jam...@bitquilltech.com >> > >> > >> .invalid> >> > >> > >> > wrote: >> > >> > >> > >> > >> > >> >> It sounds like an interesting and useful project to use >> > Subtstrait >> > >> > as an >> > >> > >> >> alternative to SQL strings. >> > >> > >> >> >> > >> > >> >> Important aspects to spec out are: >> > >> > >> >> 1. How does a server report that it supports each command >> type? >> > >> > Initial >> > >> > >> >> thought is a property in GetSqlInfo. >> > >> > >> >> 2. What happens to client code written prior to changing the >> > >> command >> > >> > >> type >> > >> > >> >> to be a oneOf field? Same for servers. >> > >> > >> >> More generally, how should backward compatibility work, and >> what >> > >> > should >> > >> > >> >> happen if a client sends an unsupported >> > >> > >> >> command type to a server. >> > >> > >> >> 3. Should inputs to catalog RPC calls also accept Substrait >> > >> > structures? >> > >> > >> >> >> > >> > >> >> On Thu, Mar 3, 2022 at 11:00 PM Gavin Ray < >> > ray.gavi...@gmail.com> >> > >> > >> wrote: >> > >> > >> >> >> > >> > >> >> > @James Duong <jam...@bitquilltech.com> >> > >> > >> >> > >> > >> > >> >> > You are absolutely right, I realized this and confirmed >> > whether >> > >> > this >> > >> > >> >> > would be possible with Jacques to double-check. >> > >> > >> >> > It would amount to what I might call "dollar-store >> Substrait." >> > >> It's >> > >> > >> not >> > >> > >> >> > elegant or a good solution, but definitely presents a good >> > >> > duct-tape >> > >> > >> hack >> > >> > >> >> > and is a crafty idea. >> > >> > >> >> > >> > >> > >> >> > I agree with Jacques -- when you think about FlightSQL, what >> > you >> > >> > are >> > >> > >> >> > attempting with a query isn't necessarily SQL, but a general >> > >> > >> data-compute >> > >> > >> >> > operation. >> > >> > >> >> > SQL just so happens to be a fairly universal way to express >> > them, >> > >> > >> with an >> > >> > >> >> > ANSI standard, but FlightSQL doesn't recognize any >> particular >> > >> > subset >> > >> > >> of >> > >> > >> >> it >> > >> > >> >> > and for all intents and purposes it doesn't matter what the >> > >> > operation >> > >> > >> >> > string contains. >> > >> > >> >> > >> > >> > >> >> > Substrait would make a fantastic logical next-feature >> because >> > >> it's >> > >> > >> >> > targeted as a specification for expressing relational >> algebra >> > and >> > >> > >> >> > data-compute operations >> > >> > >> >> > This more-or-less equates to SQL strings (in my mind at >> least) >> > >> > with a >> > >> > >> >> much >> > >> > >> >> > better toolkit and Dev UX. If there is anything I can do to >> > help >> > >> > move >> > >> > >> >> this >> > >> > >> >> > forward, please let me know because I am extremely motivated >> > to >> > >> do >> > >> > so. >> > >> > >> >> > >> > >> > >> >> > @David Li <git...@lidavidm.me> >> > >> > >> >> > >> > >> > >> >> > Also agreed. Substrait is put together by folks much smarter >> > than >> > >> > >> myself, >> > >> > >> >> > and if I had to hedge my bets, I'd put money on it being the >> > >> > future of >> > >> > >> >> > data-compute interop. >> > >> > >> >> > I would love nothing more than to adopt this technology and >> > push >> > >> it >> > >> > >> >> along. >> > >> > >> >> > >> > >> > >> >> > Your project does sound interesting - basically, it sounds >> > like a >> > >> > >> tabular >> > >> > >> >> >> data storage service with query pushdown? >> > >> > >> >> >> >> > >> > >> >> > >> > >> > >> >> > Yeah this is more or less the details of it (my personal >> > email, >> > >> > with >> > >> > >> >> > discretion assumed, is always open) >> > >> > >> >> > >> > >> > >> >> > Imagine an environment where a backend wants to advertise >> some >> > >> > kind of >> > >> > >> >> > schema/data catalog >> > >> > >> >> > >> > >> > >> >> > And then a central service introspects these backends, and >> > >> > dynamically >> > >> > >> >> > generates an API from the data catalogues/schemas, where >> > requests >> > >> > get >> > >> > >> >> > proxied to the underlying backend service for each schema to >> > >> > actually >> > >> > >> be >> > >> > >> >> > executed >> > >> > >> >> > >> > >> > >> >> > In text, the flow would look something like: >> > >> > >> >> > >> > >> > >> >> > >> > >> > >> >> > <----> Data Provider Backend 0 >> > >> > >> >> > Client <-----> Central Service <---> Generated API <----> >> > >> > >> Data-Provider >> > >> > >> >> > Backend 1 >> > >> > >> >> > >> > >> > >> >> > <----> Data Provider Backend 2 >> > >> > >> >> > >> > >> > >> >> > >> > >> > >> >> > >> > >> > >> >> > On Thu, Mar 3, 2022 at 5:52 PM David Li < >> lidav...@apache.org> >> > >> > wrote: >> > >> > >> >> > >> > >> > >> >> >> Gavin, thanks for sharing. I'm not so sure you'll find an >> > >> > >> alternative to >> > >> > >> >> >> Substrait, at least one that isn't even more nascent or one >> > >> that's >> > >> > >> very >> > >> > >> >> >> tied to a particular language, so perhaps it might be >> better >> > to >> > >> > get >> > >> > >> >> >> involved in Substrait and see if it suits your needs? >> > >> Convincing a >> > >> > >> team >> > >> > >> >> to >> > >> > >> >> >> try something new can be hard, though, and it is somewhat >> of >> > a >> > >> > moving >> > >> > >> >> >> target - but Flight SQL is in a similar spot, I think, as >> > it's >> > >> > still >> > >> > >> >> >> getting enhancements. >> > >> > >> >> >> >> > >> > >> >> >> Your project does sound interesting - basically, it sounds >> > like >> > >> a >> > >> > >> >> tabular >> > >> > >> >> >> data storage service with query pushdown? >> > >> > >> >> >> >> > >> > >> >> >> On Thu, Mar 3, 2022, at 19:58, Jacques Nadeau wrote: >> > >> > >> >> >> > James, I agree that you could use JSON but that feels a >> bit >> > >> > hacky >> > >> > >> >> >> > (mis-use >> > >> > >> >> >> > of the paradigm). Instead, I'd really like to do >> something >> > >> like >> > >> > >> David >> > >> > >> >> is >> > >> > >> >> >> > suggesting: support Substrait as an alternative to a SQL >> > >> string. >> > >> > >> >> >> > Something like this: >> > >> > >> >> >> > >> > >> > >> >> >> >> > >> > >> >> >> > >> > >> >> > >> > >> > >> >> > >> https://github.com/jacques-n/arrow/commit/e22674fa882e77c2889cf95f69f6e3701db362bc >> > >> > >> >> >> > >> > >> > >> >> >> > It would be great if someone wanted to pick this up. It >> > would >> > >> > be a >> > >> > >> >> nice >> > >> > >> >> >> > enhancement to FlightSQL (and provide a structured way to >> > >> > express >> > >> > >> >> >> > operations). >> > >> > >> >> >> > >> > >> > >> >> >> > >> > >> > >> >> >> > >> > >> > >> >> >> > On Thu, Mar 3, 2022 at 4:56 PM James Duong < >> > >> > >> jam...@bitquilltech.com >> > >> > >> >> >> .invalid> >> > >> > >> >> >> > wrote: >> > >> > >> >> >> > >> > >> > >> >> >> >> In the same way that you could write an ODBC driver that >> > >> takes >> > >> > in >> > >> > >> >> text >> > >> > >> >> >> >> that's not SQL, you could write a Flight SQL server that >> > >> takes >> > >> > in >> > >> > >> >> text >> > >> > >> >> >> >> that's JSON. >> > >> > >> >> >> >> Flight SQL doesn't parse the query, so you could create >> > >> > commands >> > >> > >> that >> > >> > >> >> >> are >> > >> > >> >> >> >> just JSON text. >> > >> > >> >> >> >> >> > >> > >> >> >> >> Is that the only bit you need, Gavin? >> > >> > >> >> >> >> >> > >> > >> >> >> >> On Thu, Mar 3, 2022 at 4:26 PM Gavin Ray < >> > >> > ray.gavi...@gmail.com> >> > >> > >> >> >> wrote: >> > >> > >> >> >> >> >> > >> > >> >> >> >> > I am enthusiastic about Substrait and have followed >> it's >> > >> > >> progress >> > >> > >> >> >> eagerly >> > >> > >> >> >> >> > =D >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > When I presented it as a tentative option, there were >> > >> > >> reservations >> > >> > >> >> >> >> because >> > >> > >> >> >> >> > of the project/spec being young and the functionality >> > still >> > >> > >> being >> > >> > >> >> >> >> > fleshed out. >> > >> > >> >> >> >> > I think if I were having this conversation in say, >> 8-16 >> > >> > months, >> > >> > >> it >> > >> > >> >> >> would >> > >> > >> >> >> >> > have been an easy choice, no doubt. >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > On a public mailing list (and I can share more details >> > in >> > >> > >> private >> > >> > >> >> if >> > >> > >> >> >> >> you're >> > >> > >> >> >> >> > curious), the gist of it is this: >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > Some well-defined/backed-by-mature tech solution for >> > >> > expressing >> > >> > >> >> data >> > >> > >> >> >> >> > compute operations between services would be a useful >> > thing >> > >> > to >> > >> > >> have >> > >> > >> >> >> >> > (Especially if it's language-agnostic) >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > The goal is for an "implementing service" to have: >> > >> > >> >> >> >> > - An introspectable schema (IE, "describe yourself to >> > me") >> > >> > >> >> >> >> > - A query/operation execution endpoint (IE: "perform >> > this >> > >> > >> operation >> > >> > >> >> >> on >> > >> > >> >> >> >> your >> > >> > >> >> >> >> > data") >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > With FlightSQL this is possible I believe, but it >> > requires >> > >> > the >> > >> > >> >> >> operation >> > >> > >> >> >> >> to >> > >> > >> >> >> >> > be expressed as a SQL string which isn't ideal. >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > Working with some programmatic, structured object that >> > has >> > >> > the >> > >> > >> same >> > >> > >> >> >> >> > semantics ("Logical Plan", or whatnot) as a SQL query >> > would >> > >> > >> have, >> > >> > >> >> >> would >> > >> > >> >> >> >> be >> > >> > >> >> >> >> > a better experience >> > >> > >> >> >> >> > (Jacques is on to something here!) >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > This interface between services would be somewhat the >> > >> > >> equivalent of >> > >> > >> >> >> an >> > >> > >> >> >> >> > "SDK", so it would be nice to have a strongly-typed >> > library >> > >> > for >> > >> > >> >> >> >> expressing >> > >> > >> >> >> >> > and building-up query/data-compute ops. >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > On Thu, Mar 3, 2022 at 3:17 PM David Li < >> > >> lidav...@apache.org >> > >> > > >> > >> > >> >> wrote: >> > >> > >> >> >> >> > >> > >> > >> >> >> >> > > You probably want Substrait: https://substrait.io/ >> > >> > >> >> >> >> > > >> > >> > >> >> >> >> > > Which is being worked on by several people, >> including >> > >> Arrow >> > >> > >> >> >> community >> > >> > >> >> >> >> > > members. >> > >> > >> >> >> >> > > >> > >> > >> >> >> >> > > It might be interesting to generalize Flight SQL to >> > >> include >> > >> > >> >> >> support for >> > >> > >> >> >> >> > > Substrait. I'm curious what your application, if >> > you're >> > >> > able >> > >> > >> to >> > >> > >> >> >> share >> > >> > >> >> >> >> > more. >> > >> > >> >> >> >> > > >> > >> > >> >> >> >> > > -David >> > >> > >> >> >> >> > > >> > >> > >> >> >> >> > > On Thu, Mar 3, 2022, at 18:05, Gavin Ray wrote: >> > >> > >> >> >> >> > > > Hiya, >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > I am drafting a proposal for a way to enable >> > services >> > >> to >> > >> > >> >> express >> > >> > >> >> >> data >> > >> > >> >> >> >> > > > compute operations to each other. >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > However I think it'll be difficult to get buy-in >> if >> > the >> > >> > only >> > >> > >> >> >> >> > > representation >> > >> > >> >> >> >> > > > for queries is as SQL strings. >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > Is there any kind of lower-level API that can be >> > used >> > >> to >> > >> > >> >> express >> > >> > >> >> >> >> > > operations? >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > IE instead of "SELECT name FROM user" >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > A structured representation like: >> > >> > >> >> >> >> > > > { >> > >> > >> >> >> >> > > > "op": "query", >> > >> > >> >> >> >> > > > "schema": "user", >> > >> > >> >> >> >> > > > "project": ["name"] >> > >> > >> >> >> >> > > > } >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > Or maybe this is a bad idea/doesn't make sense? >> > >> > >> >> >> >> > > > >> > >> > >> >> >> >> > > > Thank you =) >> > >> > >> >> >> >> > > >> > >> > >> >> >> >> > >> > >> > >> >> >> >> >> > >> > >> >> >> >> >> > >> > >> >> >> >> -- >> > >> > >> >> >> >> >> > >> > >> >> >> >> *James Duong* >> > >> > >> >> >> >> Lead Software Developer >> > >> > >> >> >> >> Bit Quill Technologies Inc. >> > >> > >> >> >> >> Direct: +1.604.562.6082 | jam...@bitquilltech.com >> > >> > >> >> >> >> https://www.bitquilltech.com >> > >> > >> >> >> >> >> > >> > >> >> >> >> This email message is for the sole use of the intended >> > >> > >> recipient(s) >> > >> > >> >> >> and may >> > >> > >> >> >> >> contain confidential and privileged information. Any >> > >> > unauthorized >> > >> > >> >> >> review, >> > >> > >> >> >> >> use, disclosure, or distribution is prohibited. If you >> > are >> > >> not >> > >> > >> the >> > >> > >> >> >> >> intended recipient, please contact the sender by reply >> > email >> > >> > and >> > >> > >> >> >> destroy >> > >> > >> >> >> >> all copies of the original message. Thank you. >> > >> > >> >> >> >> >> > >> > >> >> >> >> > >> > >> >> > >> > >> > >> >> >> > >> > >> >> -- >> > >> > >> >> >> > >> > >> >> *James Duong* >> > >> > >> >> Lead Software Developer >> > >> > >> >> Bit Quill Technologies Inc. >> > >> > >> >> Direct: +1.604.562.6082 | jam...@bitquilltech.com >> > >> > >> >> https://www.bitquilltech.com >> > >> > >> >> >> > >> > >> >> This email message is for the sole use of the intended >> > recipient(s) >> > >> > and >> > >> > >> may >> > >> > >> >> contain confidential and privileged information. Any >> > unauthorized >> > >> > >> review, >> > >> > >> >> use, disclosure, or distribution is prohibited. If you are >> not >> > the >> > >> > >> >> intended recipient, please contact the sender by reply email >> and >> > >> > destroy >> > >> > >> >> all copies of the original message. Thank you. >> > >> > >> >> >> > >> > >> >> > >> > >> > >> >> > >> >> >> -- >> >> *James Duong* >> Lead Software Developer >> Bit Quill Technologies Inc. >> Direct: +1.604.562.6082 | jam...@bitquilltech.com >> https://www.bitquilltech.com >> >> This email message is for the sole use of the intended recipient(s) and may >> contain confidential and privileged information. Any unauthorized review, >> use, disclosure, or distribution is prohibited. If you are not the >> intended recipient, please contact the sender by reply email and destroy >> all copies of the original message. Thank you. >>
