PRQL for Abstracting KSQLDB queries

[narioinc]

Team, just stumbled upon PRQL and I am just so happy to see that some of my exact needs being addressed. I am thinking of writing a UI for PRQL wherein we can create these PRQL queries with simple drag and drop DAG pipeline interface.

However, a more interesting use case that i wanted to see if it can be addressed (OR if i can help implement it :) ) is to see if PRQL can be made to support KSQLDB (https://ksqldb.iot). KSQLDB is a streaming database implementation that can make PERFECT use of the PRQL abstraction to convert its ANSI-SQL like syntax to something more closed to a pipeline definition.

This is a huge ask but if there is some guidance available on how to extend PRQL to other SQL-Like DBs (such as KSQLDB) i wouldnt blink an eye and jump to helping support the query language.

[narioinc]

I have experience with Angular/React for UI and Java/.Net/Typescript/Javascript/Swift/Kotlin just for info.

[max-sixty]

Hi @narioinc ! Thanks for you interest and suggestion. I can definitely see a pipeline design being a really good fit for a streaming system like Kafka.

I'm not that familiar with KSQL, but my understanding is that it's similar to SQL with a few extensions; is that right? I'm looking at the examples at https://ksqldb.io/examples.html#describe-a-stream-or-table. The biggest difference seems to be EMIT CHANGES, which we could find a way of allowing.

If there were lots of customizations, we'd want to implement a dialect. We use https://github.com/sqlparser-rs/sqlparser-rs for writing the query (it's mainly a parser but we use it to do the reverse), so a dialect would need to be added there.

So I'd suggest seeing how far we can get with the generic dialect, see whether that's sufficient (maybe just write EMIT CHANGES from the calling script for the moment), and then we could explore what would need to be customized.

[narioinc]

Thanks for the inputs @max-sixty. I had a similar conclusion with the SQL standards that were followed by KSQLDB. Since a couple of months i have been using ksqldb in production systems and have come to appreciate some of the useful SQL extensions they have provided (for example for windowing, runnign averagaes, medians etc)

But I assume that it would be good to see if the current system can atleast provide the basic constructs (for KSQL tables and streams) and to see if we need to extend further using the dialect plugin. Ill start work this week to check a few things and will keep this thread updated.

For now, ill be skipping any of the analytical query extensions and EMIT changes. KSQLDB currently implicitly assumes that all of its 'persistent queries' have EMIT changes but may defer from this in the future.

[max-sixty]

Great — feel free to ask any questions here as you explore.

For transparency — the current dialect support is fairly nascent, though we'd like to make it much better. So this will either be quite easy or quite difficult depending on how much KSQL specialization there is.

[narioinc]

I had a look at sqlparser-rs (even though I have no experience with Rust :) ) but it seems that, at a high level, we can add our own dialects. Although the author of the library does specify in his notes that he found a work around on supporting new dialects by delegating them to an ANSI parser: https://github.com/sqlparser-rs/sqlparser-rs/blob/main/docs/custom_sql_parser.md

Again, with no working knowledge in rust, i wasn't able to dig a bit deeper, but will progress on checking our the ANSI dialect support that is there in PRQL out of the box. It was good to note that sqlparser-rs doesnt care about the semantics of the dialect which seemed to point that it might not worry too much on the KSQL specifics as long as it is close enough to the ANSI-SQL specs.