SQL replacements is like not understanding the magnitude of the success of something so old.
SQL is fine.
SQL has been the state of the art for db queries for 40 years.
And it will continue to be when we all retire.
Google's new pipe syntax in SQL
181–190 of 192 posts
Re: Google's new pipe syntax in SQL
#182We should really standardize a core language for SQL. Rust has MIR, Clang is making a CIR for C/C++. Once we have that, we'll be able to to communicate much better. Right now, it's everyone faffing around with different mental models and ugly single pass compilers (my understanding is that parsing-->query planning is not nearly as well-separated in most DBs as parsing-->optomize-->codegen in most compilers).
You might enjoy https://substrait.io/
Re: Google's new pipe syntax in SQL
#183Earlier quoted context omitted.
> We should really standardize a core language for SQL Do you mean something other than ISO/IEC 9075:2023 (the 9th edition of the SQL standard)?
A core language is a minimal AST without surface syntax (and thus no bikeshedding of that) that distills the surface language to its essence.
SQL is basically the list monad, with various quotients / refinements:
- Sometimes the order doesn't matter - Sometimes there are functional dependencies - Sometimes one knows the length of the list in question is 1 (foreign key constraints)
Re: Google's new pipe syntax in SQL
#184Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…
For examples of larger queries, see here for all TPC-H queries in standard syntax and converted to pipe syntax:
https://github.com/google/zetasql/blob/master/zetasql/exampl...
And several more examples with pipe syntax here: https://github.com/google/zetasql/blob/master/zetasql/exampl...
Re: Google's new pipe syntax in SQL
#185The next thing I would like is to define a function / macro that has a bunch of |> terms. I pointed out that you can do this with shell: Pipelines Support Vectorized, Point-Free, and Imperative Style https://www.oilshell.org/blog/2017/01/15.html e.g. hist() { sort | uniq -c | sort -n -r } $ { echo a; echo bb; echo a; } | hist 1 bb 2 a $ foo | hist ... Something like that should be possible in SQL!
It is, using table-valued functions (TVFs).
There's an example at the bottom of this file:
https://github.com/google/zetasql/blob/master/zetasql/exampl...
Re: Google's new pipe syntax in SQL
#186int *ptr;
// but let's change it to *int ptr;
// because the pointer symbol is more logical to write first
Please can we solve a real problem instead?
Re: Google's new pipe syntax in SQL
#187Earlier quoted context omitted.
https://prql-lang.org/ has a bunch of good examples on its home page. If you engage the syntax with your System 2 thinking (prefrontal cortex, slow, the part of thinking we're naturally lazy to engage) rather than System 1 (automated, instinctual, optimized brain path to things we're used to) you'll most likely find that it is simpler, makes more logical sense so that you're filtering down things naturally like a sie…
> If you engage the syntax with your System 2 thinking (prefrontal cortex, slow, the part of thinking we're naturally lazy to engage) rather than System 1 (automated, instinctual, optimized brain path to things we're used to) You might not have intended it this way, but your choice of phrasing is very condescending.
Re-reading it I can see how it could be perceived by some people as such, thanks for pointing it out. There's probably better phrasing or adding more context could make it more amicable:
The goal was to explicitly tell people not to bother "just reading it" as one (and by one I mean myself and most people I know, surely there are exceptions) is naturally inclined to do unless something is particularly piquing our interest.
Without engaging in active, conscious effort, syntax that is different than what we're used to (specially something as established as SQL) where the changes aren't groundbreaking at first glance can easily make us dismissive without realizing the benefits. And after seeing it too many times with all kinds of technologies that stray away from the familiar, I just want to prepare the reader so that their judgment can be formed with full use of their faculties rather than a reflex response.
Re: Google's new pipe syntax in SQL
#188Earlier quoted context omitted.
What’s group-by-all? Sounds like distinct?
It's different from distinct. Distinct just eliminates duplicates but does not group entries. Suppose... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY brand, model, revision This is not solved by using distinct as you would not get the correct count. Group By All allows you to write it a bit more compact... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY ALL
Gotcha. Thanks. That’s actually super useful! Looks like Postgres doesn’t implement it unfortunately.
I revert to “group by 1, 2, 3… “ when I’m just hacking about. Group by all would definitely be an improvement.
Re: Google's new pipe syntax in SQL
#189This is why I like tools like datastation and hex.tech. You write the initial query using SQL than process the results as a dataframe using Python/pandas. Surely, mixing Pandas and SQL like that is not good for data pipelines but for exploration and analytics, I have found this approach to be enjoyable.
Yes, it's very convenient to be able to use SQL with your massively parallel commercial database (Oracle, Snowflake, etc.) and then again with the results sets (Pandas, etc.). Interestingly, it's a concept that was implemented 35 years ago in SAS (link below) but is just now gaining traction in today's "modern" software (e.g., via DuckDB). USING THE NEW SQL PROCEDURE IN SAS PROGRAMS (1989) https://support.sas.com/res…
Wow, that is really cool. One of my theses is that DuckDB will be bought by GCP (BigQuery), and polars will be bought by Databricks (or AWS). The thesis is based on the idea that Snowflake bought the Modin platform. The movement in DE seems to be towards data warehouse platforms streaming data (views/results) down to dataframe (Modin, Polars, DuckDB) platforms, which then stream down to their BI platforms. Because these database platforms are designed as OLAP platforms so, this approach makes sense.
Re: Google's new pipe syntax in SQL
#190The next thing I would like is to define a function / macro that has a bunch of |> terms. I pointed out that you can do this with shell: Pipelines Support Vectorized, Point-Free, and Imperative Style https://www.oilshell.org/blog/2017/01/15.html e.g. hist() { sort | uniq -c | sort -n -r } $ { echo a; echo bb; echo a; } | hist 1 bb 2 a $ foo | hist ... Something like that should be possible in SQL!
It is, using table-valued functions (TVFs). There's an example at the bottom of this file: https://github.com/google/zetasql/blob/master/zetasql/exampl...
That's cool, thanks!
What about scalar valued functions? :) So I can reuse an expression in a WHERE and so forth
(and I appreciate that HAVING can be generalized/removed)