11 ms·
Pql, a pipelined query language that compiles to SQL
- gembeMx 3y ago[flagged]
- RedShift1 3y agoInfluxDB tried to do this with InfluxQL but abandoned it, and are now back to SQL. The biggest problem I had with it when I tried it, was that is was simply too slow, queries were on average 6x slower than their SQL equivalents. I think a language like this is just too hard to optimize well.
- loic-sharma 3y agoThis is incorrect. It was their query engine that was hard to optimize, not the language. InfluxDB has been working on a new query engine based off Apache DataFusion to fix this. If you squint, this query language is very similar to Polars, which is state-of-the-art for performance. I expect Pql could be as performant with sufficient investment. The real problem is that creating a new query language is a ton of work. You need to create tooling, language servers, integrate with notebooks, etc… If you use SQL you get all of this for free.
- Reubensson 3y agoI dont think they have abandoned InfluxQL. They are still supporting it in the InfluxDB 3 as far I know. But they are abandoning flux, which was a mess and pain to use.
- crooked-v 3y agoWhat's the reason to go for this over PRQL?
- zhiboz 3y agoI had the exact same question when I saw the post.
- IshKebab 3y agoLooks like PRQL doesn't have a Go library so I guess they just really wanted something in Go? I would guess they didn't wrap the main PRQL library (which is written in Rust) because Go code is a lot easier to deal with when it's pure Go. And they probably didn't just write a Go version of PRQL because that would be a mountain of work. Still I think that's a mistake. PRQL is a far more mature project and has things like IDE support and an online playground which they are never going to do... Better just to bite the bullet and wrap the Rust library.
- tstack 3y ago> Looks like PRQL doesn't have a Go library so I guess they just really wanted something in Go? There's some C bindings and the example in the README shows integration with Go: https://github.com/PRQL/prql/tree/main/prqlc/bindings/prqlc-c https://github.com/PRQL/prql/tree/main/prqlc/bindings/prqlc-...
- IshKebab 3y agoAh yeah I wonder why they don't mention that in their docs.
- pkolaczk 3y agoIt’s not the problem with binding to Rust. Python can do it, Zig can do it, C can do it, even JS can do. It is Go which doesn’t integrate well with anything that isn’t Go.
- randomdata 3y ago
- anotherhue 3y agoSimilarities to PRQL: https://prql-lang.org/ https://prql-lang.org/
- hashmash 3y agoPRQL is actually a first class functional programming language, with syntax for supporting readable query processing pipelines. The documentation for PQL is a bit light, so I don't know if it's as powerful as PRQL.
- danpalmer 3y agoThe "Why?" questions are getting downvoted, but to dissect the why section from the page a little... "designed to be small and efficient" – adding a layer on top of SQL is necessarily less efficient, adding a layer of indirection on underlying optimisations means it is likely (but not guaranteed) to also generate less efficient queries. "make developing queries simple" – this seems to be just syntactic preference. The examples are certainly shorter, but in part that's the SQL style used. I think it either needs more evidence that the syntax is actually better or cases it simplifies, the ways in which it can optimise that are hard in SQL, or it perhaps needs to be more honest about the intent of the project being to just be a different interface for those who prefer it that way. It's an interesting exercise, and I'm glad it exists in that respect, and hope the author enjoyed making it and learnt something from it. That can be enough of a why!
- ejcx 3y agoThe main goal was to help security engineers / analysts, who _loathe_ sql (for better or worse). I tend to think this is a little more user friendly, personally, and it's nice to give some open-source competition to the major languages that are used in security (SPL, Sumologic, KQL, and ES|QL). We were surprised that there weren't syntactic competitiors (i.e. -- while prql has some similar goals, the syntax and audience in mind were very different)
- throwanem 3y agoHow's perf for the compiled queries? The first thing I see in the examples is what appears to be a CTE-by-default approach that, in most (all?) engines, means the generated query ultimately runs over an unindexed (and maybe materialized!) intermediary resultset.
- fwip 3y agoHigher level languages often have opportunities for additional optimisation over the straightforward implementation in the target language. This is because the semantics of the high-level language offer guarantees that the target doesn't, or because the intent is more clearly preserved in the source language. Whether or not this is true for PQL/SQL, I don't know enough to say. But I do know that I don't write SQL at a high-enough level to be sure that a wrapper couldn't compile to something more efficient than what I produce, especially for complicated queries.
- gtroja 3y agoThe only thing I like in SQL is that is almost the same language in decades. Learn it once and you're done. If you really need, you could write macros yourself. I don't see the value of learning a new language to do the same thing
- quaunaut 3y agoSQL's nonsensical handling of null is reason enough to learn other query languages.
- FridgeSeal 3y agoUsing this language on top won’t solve that though, it still compiles into sql, warts and all.
- munk-a 3y agoDo you mean that NULL <> NULL and NULL infects boolean logic? NULL is always an awkward thing to deal with - how you want to handle it depends on the specific thing you're trying to accomplish. I'd probably prefer it if NULL equaled NULL when dealing with where conditions but it actually makes join evaluations a lot cleaner - if NULL equaled NULL then joining on columns with nulls would get really weird. At the end of the day IS NULL and IS DISTINCT FROM/IS NOT DISTINCT FROM exist so you can handle cases where it'd be weird.
- nextaccountic 3y agothe best way to handle nulls is with Option / Maybe types. that is, without null at all unfortunately they were not invented at the time sql was created
- munk-a 3y agoI think that's just a question on syntactic sugaring here - so, concretely, what would that mean for comparison operators? If I wanted to `id = id` and both were nullable would I need to express that as two layers of statements where I tried to unwrap both sides first or would we have a maybe vs maybe comparison operator - if we had such an operator what would it do in this case?
- darcien 3y agoThis is actually pretty awesome! I use KQL every few days for reading some logs from Azure App Insight. The syntax is pretty nice and you can make pretty complex stuff out of it. But that's it, I can't use KQL anywhere else outside Azure. With this, I can show off my KQL-fu to my teammates and surprise them with how fast you can write KQL-like syntax compared to SQL.
- alfalfasprout 3y agoAt this stage I feel that the natural evolution for SQL is instead to use english to describe what you want and have an LLM generate SQL. Often with comments. For some reason, a lot of these SQL alternatives seem to be syntactic preference and not much simpler or clearer than the original.
- caust1c 3y agoWe built LLM-to-SQL before this at RunReveal, and while it's useful and gets queries mostly correct 80% of the time, 20% of the time it's way off or requires nontrivial manual intervention. We're still fairly bullish on the LLM-to-SQL front though, but in the meantime PQL is a good bridge.
- munk-a 3y agoAs a company that's invested into this. Would you mind talking as to why you don't want to use raw SQL - are there particular deficiencies you've found in it?
- jodrellblank 3y agoHere is a long blogpost "against SQL" which lists many deficiencies of it: https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/ In short, it has a longer spec than famously-complex C++ while making a much less expressive language out of it.
- ejcx 3y agoWe do use raw sql, but we're a security business which tends to have heavy reliance on other languages that have a similar syntax to pql
- __mharrison__ 3y agoSee tools like pandas and Polars. These database libraries are abstractions that give you a spray of SQL functionality. I prefer using these libraries because it feels much more intuitive (and works with the Python/arrow ecosystem). (I'm also biased since I make a portion of my living off of pandas training material.)
- sgammon 3y ago[flagged]
- munk-a 3y agoTcl wants its namespace back - it predates all these. Additionally there is PECL (again, ancient) but the popularity of that has been waning for quite some time with composer being the main package management system for PHP now.
- sgammon 3y agoTcl and PECL are ancient and don't have the community described above. Neither have serious name collisions that I know of. Syntax highlighting is going to be hard when you name things this way. Documentation is going to be hard to find. I'm just saying: why do this to your users? It's a name. It is one of those things you have complete and total creative control over.
- kylecazar 3y agoMaybe it's meant to be pronounced 'pea-qul'
- smurda 3y agoThis is cool. Splunk Search Processing Language (SPL) is a real vendor lock-in feature. Once the team has invested time to get ramped up on SPL, and it gets integrated in your workflows, ripping out Splunk has an even higher switching cost.
- helloericsf 3y agoWow, so many query languages, right? Do we really need another one? What's the story behind that decision? Cheers.
- brettv2 3y agoThis is answered on their blog: https://blog.runreveal.com/introducing-pql/ https://blog.runreveal.com/introducing-pql/
- helloericsf 3y agoCheers, mate! The blog cleared up a chunk of my question and the chat here gave me a better grasp of why it's over PRQL.
- 654wak654 3y agoReminds me of this classic: https://xkcd.com/927/ https://xkcd.com/927/
- vincnetas 3y agofor anyone using anything more than basic SQL functionality so far this looks very limiting. No window functions, no agregate filtering, no data type specific functions (ranges).
- sigmonsays 3y ago[flagged]
- brikym 3y ago[flagged]
- pknerd 3y agoSorry I might be dumb but why do we need this?
- falserum 3y agoBecause SQL is a nightmare. (Standard is in thousands of pages; nobody fully implements it; not actually a single language as usually each db has deviations and extensions; nonmodular, composability is hard - an afterthought) And the worst part: nothing better exist; single’ish bad language is better than dozens of new shortlived ones that have quirks in various other places. But somebody needs to be idealist and keep trying.
- RoyTyrell 3y agoThat is quite a hyperbolic statement and I have to disagree with you. I've used SQL on a variety of databases over 15 years, and yes while some like MySQL have poorly implemented anything other than basic SQL/RDBMS features, most are very similar in feature sets. There are vendors-specific additional features like how Oracle supports hierarchical queries with CONNECT, but you don't have to use them. - CTEs are very close if not the same across Oracle, PostgreSQL, DB2, Hive, Snowflake, and MS SQL Server - I believe even Sybase too but it's been a while. - Joins work all largely the same even though a couple of those support additional join types, especially when you want to join on functions that return data sets. - Window functions are supported by every major DB with similar or the same syntax too. Any differences take 5sec to lookup in documentation. My only complaint is loading data is highly vendor specific.
- bradford 3y agoI used SQL in various implementations for about 15 years. I didn't find much fault in it until I started using KQL (the language which seems to have inspired Pql). The difference in enjoyability is stark: I truly hate SQL now. More robust criticism is provided here (https://carlineng.com/?postid=sql-critique#blog https://carlineng.com/?postid=sql-critique#blog). The quote I usually drag out is from Chris Date, who helped pioneer relational DBs: "At the same time, I have to say too that we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." https://www.red-gate.com/simple-talk/opinion/opinion-pieces/chris-date-and-the-relational-model/ https://www.red-gate.com/simple-talk/opinion/opinion-pieces/...
- Alifatisk 3y agoReminds me of how ActiveRecord works in Rails
- memset 3y agoThis is really great! Maybe I'll incorporate this into my own software (scratchdata/scratchdb) Question: it looks like you wrote the parser by hand. How did you decide that that was the right approach? I myself am new to parsers and am working on implementing the PostgREST syntax in go using PEG to translate to Clickhouse, which is to say, a similar mission as this project. Would love to learn how you approached this problem!
- seer 3y agoI also wrote a parser (in typescript) for postgres (https://github.com/ivank/potygen https://github.com/ivank/potygen), and it turned out quite the educational experience - Learned _a lot_ about the intricacies of SQL, and how to build parsers in general. Turned out in webdev there are a lot of instances where you actually want a parser - legacy places where they used to save things in plain text for example, and I started seeing the pattern everywhere. Where I would have reached for some monstrosity of a regex to solve this, now I just whip out a recursive decent parser and call it a day, takes surprisingly small amount of code! (https://github.com/dmaevsky/rd-parse https://github.com/dmaevsky/rd-parse)
- brikym 3y agoIt looks a lot like Kusto query language. Here is a kusto query: StormEvents | where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) and DamageCrops > 0 | summarize EventCount = count() by bin(StartTime, 7d) edit... yes it indeed was inspired by Kusto as they mention on the github Readme https://github.com/runreveal/pql https://github.com/runreveal/pql
- beoberha 3y ago[flagged]
- loic-sharma 3y agoAgreed! Azure’s best product isn’t CosmosDB, AKS, or their AI cognitive services - it’s Kusto! Sadly no one knows about it. If you’re interested, you can try it here: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...
- brikym 3y agoThere is also the Kusto Detective Agency site where you learn by pretending to be a detective investigating leads using the the data. You also get a little certificate at the end. https://detective.kusto.io/ https://detective.kusto.io/
- benrutter 3y agoYes, me too!! I think the most natural way to think about more complex data manipulations is as a series of functions. I think there's a lot love for SQL, and people can tend to get a little defensive around new query languages. But I think having a better suited query languages to is often a much nicer solution than ORMs.
- deleted 3y ago[deleted]
- nitrix 3y agoPipelining is cool, though this could've easily just been a library with nice chaining and combinators in your language of choice (seems to be Go here).
- benrutter 3y agoYeah, but isn't the nice thing here that you can run it directly in your database? Which has all the data and probably a fair bit more compute power than your laptop/PC. Edit: my comment acted like ORM type libraries than execute within databases like Ibis don't exist. My bad!
- mcphage 3y agoTheir very first example has issues: > users > | where like(email, 'gmail') > | count becomes > WITH > "__subquery0" AS ( > SELECT > * > FROM > "users" > WHERE > like ("email", 'gmail') > ) > SELECT > COUNT(*) AS "count()" > FROM > "__subquery0"; Fetching everything from the users table can be a ton slower than just running a count on that table, if the table is indexed on email. I had to deal with that very problem this week.
- kccqzy 3y agoShould not be a problem on modern Postgres.
- paulddraper 3y agoThat is the same query plan for any contemporary query planner. (Just like any C compiler will produce the same output for `x += 2` and `x += 1 + 1`.) --- A notable exception was PostgreSQL prior to version 12, which treated CTEs as an optimization fences.
- throwanem 3y agoI'd be hesitant to assume the generated CTEs are always going to be amenable to optimization. The examples on the linked page are pretty trivial queries - I wonder what happens when that ceases to be the case, as seems very likely with a tool that apparently doesn't do a great deal to promote understanding what goes on under the hood.
- paulddraper 3y agoIt's possible for sure. It's just worth recognizing that SQL itself is a tool that doesn't do a great deal promote understanding what goes on under the hood. (I've witnessed that firsthand many times.)
- throwanem 3y agoGranted, but I've not once yet seen it help to add a second hood for things to be going on under. If nothing else, having to write SQL tends to lead engineers to the engine manual, where they have at least a chance to become aware of the existence of query planners.
- dkga 3y agoThe R {dbplyr} package is also a very good way in practice to pipe SQL.
- civilized 3y agoAlso AFAICT the only one that is fully integrated with a general purpose programming language with an excellent IDE.
- mavam 3y agoWe're developing TQL (Tenzir Query Language, "tea-quel") that is very similar to PQL: https://docs.tenzir.com/pipelines https://docs.tenzir.com/pipelines Also a pipeline language, PRQL-inspired, but differing in that (i) TQL supports multiple data types between operators, both unstructured blocks of bytes and structured data frames as Arrow record batches, (ii) TQL is multi-schema, i.e., a single pipeline can have different "tables", as if you're processing semi-structured JSON, and (iii) TQL has support for batch and stream processing, with a light-weight indexed storage layer on top of Parquet/Feather files for historical workloads and a streaming executor. We're in the middle of getting TQL v2 [@] out of the door with support for expressions and more advanced control flow, e.g., match-case statements. There's a blog post [#] about the core design of the engine as well. While it's a general-purpose ETL tool, we're targeting primary operational security use case where people today use Splunk, Sentinel/ADX, Elastic, etc. So some operators are very security'ish, like Sigma, YARA, or Velociraptor. Comparison: users | where eventTime > minus(now(), toIntervalDay(1)) | project user_id, user_email vs TQL: export where eventTime > now() - 1d select user_id, user_email [@] https://github.com/tenzir/tenzir/blob/64ef997d736e9416e859bfcd5f6fa74970565204/rfc/004-query-language/README.md https://github.com/tenzir/tenzir/blob/64ef997d736e9416e859bf... [#] https://docs.tenzir.com/blog/five-design-principles-for-building-a-data-pipeline-engine https://docs.tenzir.com/blog/five-design-principles-for-buil...
- ben_jones 3y agoI’m glad this exists but would caution extensibility as the most important thing for devs to consider when picking there “ORM” stack especially in terse Golang. For that I use squirrel which uses the builder pattern to compose sql strings. Keeping it as strings and interfaces allow it to be very easily extended, for example I was able to customize it to speak SOQL (salesforce). plenty of downide though.
- shp0ngle 3y agoHow do I get parametered queries into this? can I? should I? edit: guess I can't https://pkg.go.dev/github.com/runreveal/pql https://pkg.go.dev/github.com/runreveal/pql
- xiaodai 3y agoPiping the results of each query into text is definitely NOT efficient
- neonsunset 3y agoAnother day, another framework that tries to reinvent a limited subset of C#'s LINQ query syntax :)
- jerrygenser 3y agoThis seems really cool. This is not meant to be a negative comment - why does it matter that it's written in Go? It's stated multiple times but could this be written in multiple other languages and still be functionally the same?
- deleted 3y ago[deleted]
- sinuhe69 3y agoTheir first example doesn't look idiomatic at all: SELECT * FROM "users" WHERE like ("email", 'gmail') Should “like” here be a user-defined function? Because that’s not the syntax for SQL-like. To which SQL version will Pql translate its queries?
- hans_castorp 3y agoFurther down is an example using "minus (now (), toIntervalDay (1))" which is also non-standard SQL. I have no idea which DBMS they are targeting.
- shellcromancer 3y ago> The where operator will validate that the syntax is valid, but it will pass unknown function calls through to the underlying database. In RunReveal's case, we use Clickhouse under the hood, so if we wanted to do a case-insensitive match we could still use Clickhouse's lower function. From the release blog [1] they mention that unknown functions are passed through to the underlying SQL engine -- this let's them target anything from mysql, Postgres, ClickHouse or proprietary engines like Snowflake. 1. https://blog.runreveal.com/introducing-pql/ https://blog.runreveal.com/introducing-pql/
- prasoonds 3y agoOne potential advantage of these compile-to-SQL languages seems to be - they might be easier to tune a codegen LLM on. SQL is very verbose and IME, english -> SQL doesn't really work too well, even in high end products (i.e. pricy SaaS offerings using presumably GPT-4) My hunch is tuning english prompts on less verbose, more "left to right" flowing languages might yield better results.
- Cloudef 3y agoI never liked the original SQL syntax, it's weird. It's one of the few places where I think using s-exprs would've made more sense.
- krick 3y agoI don't get it. These examples are kinda uninspiring, generated SQL output being unnecessarily complicated doesn't help. I haven't used PRQL, but at least it's pretty obvious from examples, how it's nicer to use than SQL. But this one — yeah, examples on the left are "nicer" than convoluted output on the right, but if you write SQL normally, it's basically just a lot of "|" and table name in the beginning, instead of in the middle. So what's the point?
- jkn23jn23jn 3y agoSo basically the same as C# LINQ feature that allows you to automatically generate SQL and perform database operations without using SQL language.
- outside1234 3y agoSerious question - in this day and age why not just as GPT4 in English to write the SQL you need?
- datadrivenangel 3y agoDo you want your SQL to be correct?
- hans_castorp 3y agoWhat target database is that in the examples? like ("email", 'gmail') minus (now (), toIntervalDay (1)) are non-standard functions/conditions
- swman 3y agoMaybe I’m totally missing it but why would I use it over sql? All those companies have their own flavor DSL so are you saying this is to standardize using it? Thanks
- eitland 3y agoMy primary reason for caring ATM is because a better query language for SQL, one that specifies what tables to operate on before what to do, could simplify tooling quite a bit.
- _v7gu 3y agoIs the "piping" associative? As in, does it allow me to put `where eventTime > minus(now(), toIntervalMinute(15)) | count` into a variable so I can use it later on multiple different tables/queries? I remember failing to do the same thing with ggplot2 when I wanted to share styling between components. If the operator is not associative, then the reading order will have to be mixed since composing will require functions (and Go doesn't have pipes/UFCS)
- wingi 3y agoThe first example is worse. Why learning a piping syntax instead learning SQL?
- otabdeveloper4 3y agoNot to be confused with Prql.
- SomeoneFromCA 3y agoSQL in the examples is deliberately convoluted, to make pql look more elegant.
- SuaveSteve 3y agoLooks similar to PRQL[0]. Neither PRQL nor Pql seem to be able to do anything outside of SELECT like Preql[1] can. I propose we call all attempts at transpiling to SQL "quels". [0] https://prql-lang.org/ https://prql-lang.org/ [1] https://github.com/erezsh/Preql https://github.com/erezsh/Preql
- lincpa 3y ago[dead]