Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Google's new pipe syntax in SQL

simonwillison.net

Post reply on HN
Print view View original

111–120 of 192 posts

Re: Google's new pipe syntax in SQL

#111

Post by self »

philippta wrote: post #100

Why even add the pipe operator? If the DB engine is executing the statement out of order, why not allow the statement to be written in any order and let itself figure it out?

> Why even add the pipe operator?

To make it easier for humans to read/write the queries.

Re: Google's new pipe syntax in SQL

#112

Post by gopiandcode »

I find this particular choice of syntax somewhat amusing because the pipe notation based query construction was something I ended up using a year ago when making an SQL library in OCaml:

https://github.com/kiranandcode/petrol

An example query being:

```

let insert_person ~name:n ~age:a db = Query.insert ~table:example_table ~values:Expr.[ name := s n; age := i a ] |> Request.make_zero |> Petrol.exec db

```

Re: Google's new pipe syntax in SQL

#113

Post by julien040 »

I haven't seen it mentioned yet, but it reminds me of PQL (not PRQL): https://pql.dev

It's inspired by Kusto and available as an open-source CLI. I've made it compatible with SQLite in one of my tools, and it's refreshing to use.

An example:

  StormEvents
  | where State startswith "W"
  | summarize Count=count() by State

Re: Google's new pipe syntax in SQL

#114

Post by datadeft »

> It's been 50 years. It's time to clean up SQL. This

Is it though?

Are we trying to solve the human SQL parser and generator problem or there is some underlying implementation detail that benefits from pipes?

Re: Google's new pipe syntax in SQL

#115

Post by minkles »

That is basically R with tidyverse.

  flights |>
    filter(
      carrier == "UA",
      dest %in% c("IAH", "HOU"),
      sched_dep_time > 0900,
      sched_arr_time 
    group_by(flight) |>
    summarize(
      delay = mean(arr_delay, na.rm = TRUE),
      cancelled = sum(is.na(arr_delay)),
      n = n()
      ) |>
    filter(n > 10)
If you haven't used R, it has some serious data manipulation legs built into it.

Re: Google's new pipe syntax in SQL

#116

Post by OscarCunningham »

> Rationale: We used the same operator name for full-table and grouped aggregation to minimize edit distance between these operations. Unfortunately, this puts the grouping and aggregate columns in different orders in the syntax and output. Putting GROUP BY first would require adding a required keyword before the AGGREGATE list.

I think this is bad rationale. Having the columns in order is much more important than having neat syntax for full-table aggregation.

Re: Google's new pipe syntax in SQL

#117

Post by nagisa »

People here are describing many projects that already have something resembling this syntax and concept, so I'll add another query language to the pile too: Influx's now-mostly-abandoned Flux. Uses the same |> token and structures the query descriptions starting with an equivalent of "FROM".

Re: Google's new pipe syntax in SQL

#118

Post by oaiey »

tehlike wrote: post #15

LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ It's a welcome change in the industry. I made this prediction a couple years back: https://x.com/tehlike/status/1517533067497201666

Is "from" keyword originating from .NET (Framework 3.5 in 2007) or is this pre-existing somewhere in research?

Re: Google's new pipe syntax in SQL

#119

Post by dan-robertson »

minkles wrote: post #115

That is basically R with tidyverse. flights |> filter( carrier == "UA", dest %in% c("IAH", "HOU"), sched_dep_time > 0900, sched_arr_time group_by(flight) |> summarize( delay = mean(arr_delay, na.rm = TRUE), cancelled = sum(is.na(arr_delay)), n = n() ) |> filter(n > 10) If you haven't used R, it has some serious data manipulation legs built into it.

An interesting thing to me about all these dplyr-style syntaxes is that Wickham thinks the group_by operator was a design mistake. In modern dplyr you can often specify a .by on an operation instead. I found switching to this style a pretty easy adjustment, and I think it’s a bit better. Example:

  d |> filter(id==max(id),.by=orderId)
I think PRQL were thinking a bit about ways to avoid a group_by operation and I think what they have is a kind of ‘scoped’ or ‘higher order’ group_by operation which takes your grouping keys and a pipeline and outputs a pipeline step that applies the inner pipeline to each group.

Re: Google's new pipe syntax in SQL

#120

Post by bvrmn »

philippta wrote: post #100

Why even add the pipe operator? If the DB engine is executing the statement out of order, why not allow the statement to be written in any order and let itself figure it out?

Aggregations could be non-commutative in general case and order is important. Filters before and after grouping are also tied to a particular place in the pipeline.
Post reply on HN