> > 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. >
