Hacker News Reader

Comment by fouc | Hacker News Reader

Example looks like this:

    query: sessionize is {
      group_by: flight_date is dep_time.day
      group_by: carrier
      aggregate: daily_flight_count is flight_count
      nest: per_plane_data is {
        top: 20
        group_by: tail_num
        aggregate: plane_flight_count is flight_count
        nest: flight_legs is {
          order_by: 2
          group_by: [
            tail_num
            dep_minute is dep_time.minute
            origin_code
            dest_code is destination_code
            dep_delay
            arr_delay
          ]
        }
      }
    }

https://looker-open-source.github.io/malloy/documentation/ex...

View on HN

Thank you for making the effort the author's couldn't be bothered to.

It's horrible btw. You can't even tell if and when order in this syntax is important.

It's also not clear if it's declarative or operational syntax. But since they are compiling to SQL it has to be the first. Yet the syntax strongly misleads people into assuming a particular execution strategy.

Not even naming of things is consistent (why shorten "array" into "arr" but not minute into "min").

It just looks like random syntax choices, without a cohesive rationale made by people that think the declarative syntax of SQL looks worse than for-loops, completely ignoring that the database will not execute anything the way it is structured here.

> Not even naming of things is consistent (why shorten "array" into "arr" but not minute into "min").

I don't believe it shortens "array" to "arr", does it? "Dep" and "arr" here seem to mean departure and arrival, which I'm reasonably sure is usual for the domain (flights).

You're correct although they did demonstrate just how incomprehensible this DSL is. At least with SQL it's easy to distinguish between your keywords, types and columns.

Is it easy with SQL? Or are you just used to it?

On the documentation site, they have syntax highlighting to help distinguish keywords

It seems pretty straightforward to see what's a keyword vs not tbh

> not minute into "min"

That’s because MIN() is minimum?

Yeah I don't like it. And I am not trying to gate keep as SQL is one of the few things I am decent at ... it just looks clunky and way too verbose. SQL is far easier to read. To me this has the verbosity of C whereas SQL is clean like Python (non-typed python).

I share your enthusiasm for SQL. I've been using it for nearly 20 years now, and it's an important technology. But I've done a lot of SQL generation for tools that manage arbitrary tables and SQL definitely makes this hard problem virtually impossible (or at least too hard for yours truly). So I try to keep an open mind for anything that purports to solve these problems.

> it just looks clunky and way too verbose.

Surely you need to compare this with the equivalent SQL before claiming that the SQL is cleaner? What would be the equivalent SQL.

> To me this has the verbosity of C whereas SQL is clean like Python (non-typed python).

Is this just a reaction to curly braces?

Amongst many other things, the README tells me that `Malloy is immediately understandable by SQL users, and far easier to use and learn`. Having just looked at this code sample, I really don't agree with that README.

That's why they tell rather than show.

Which part of it you couldn’t understand?

What's nest actually doing? Is it an inner join/lateral/apply? An outer join/lateral/apply? A correlated subquery? If so, what's the key? It doesn't look like it has a key, so it's equivalent to a cross join? That's hardly ever useful. Even this example query feels like it would just be better as 3 queries if it's just a cross join. I don't want every row to have a list of the exact same 20 records on it.

How does aggregate know what function to use? The examples on the aggregate page actually list aggregate functions, but everywhere else it's just... magically decided?

It supports TOP or LIMIT, but where's the windowing? Where's OFFSET 100 ROWS FETCH NEXT 25 ROWS ONLY for page 5?

What the heck does "order by: 2" mean? Even if it's an ordinal position, that's not a great example. Yes, SQL technically allows you to use ordinal positioning of columns in the ORDER BY, but it's horrible practice to use that because it's difficult to maintain. There's no way to tell what the original intent was, and the output columns change all the time when queries are reused.

My real question, however, is: "How is this actually easier?". Yes, sure, using symbols instead of English words is shorter, and a lot of programmers mistake a lower character count for being easier. However, it's not actually easier. It's just a bit less typing.

> What's nest actually doing?

1) Nesting builds nested results (like GraphQL). This is particularly hard to do in SQL but is allows very large complex data sets to be returned in a single query.

https://looker-open-source.github.io/malloy/documentation/la...

For example the dashboard on the page below is a single SQL query:

https://looker-open-source.github.io/malloy/documentation/ex...

> How does aggregate know what function to use?

You can pre-define calculations with 'measure:' or decalre them explicitly in a query.

> where's the windowing

It's missing with a bunch of other things (like union for example). Its coming of course. The goal is that everything represent able in SQL is represent able in Malloy.

> heck does "order by: 2" mean

Same thing as it does in SQL. We try and have reasonable defaults.

https://looker-open-source.github.io/malloy/documentation/la...

> "How is this actually easier?"

The goal here is to be able to create data models that are re-usable and compose-able and verifiable. Yeah, there is a learning curve as there is with anything powerful. There are many things expressible in Malloy that cannot be easily expressed in SQL.

I'll bite. What is that code example actually returning? I don't have a clue what fields I expect to get back from that.

Scroll to the bottom of the page. It shows the returned JSON

https://looker-open-source.github.io/malloy/documentation/pa...

Here is the SQL we generate.

https://gist.github.com/lloydtabb/8c144d2dac978dda9bf3ec4d6b...

I’d expect

  [{
     flight_date:
     carrier:
     daily_flight_count:
     per_plane_data: [
       {
         tail_num:
         plane_flight_count:
         flight_legs: [

And so on.

It almost is as if someone repurposed YAML as a query language, which I guess is doable since SQL is also a declarative language, which YAML is used for a lot as well.

But I can’t help but feel like this is basically a different way to describe the same constructs as we have in SQL. It almost feels like a query builder’s syntax.

To me, the real improvements over SQL are when they part ways with its constructs, such as seen in for example Datalog.

I get a headache just looking at this. I don't think the SQL version would look any worse either.

I don't think there is a good way to return nested JSON using SQL, which this example does.

Postgres has excellent syntax for dealing with JSON

https://www.postgresql.org/docs/14/functions-json.html

I think postgres' json syntax in the latest version isn't too bad for these things.

It would be nice to see how this query could look in Postgres.

Well, this syntax certainly looks less arbitrary than SQL.

Feels that it's worth giving a try.

It compiles to SQL, so the actual execution plan is as arbitrary as the SQL this compiles to.

I love database where queries and commands are not declarative but imperative. Like RethinkDB or Mongo.

Databases with this type of syntax.

But to use this syntax for an SQL database is a horrible mistake, because it is misleading.

I believe that declarative is typically better at letting the db compile it to something efficient while imperative is typically better when the author of the request knows exactly what they're doing.

> It compiles to SQL, so the actual execution plan is as arbitrary as the SQL this compiles to.

Yes, I'm purely talking syntax. I find the syntax of SQL to be arbitrary. Do I need a single quote or two? Do I need parentheses here? Or double parentheses? What does this bind, exactly?

I believe that there is lots to be gained by making the syntax more regular, without affecting semantics.

This just looks like someone wanted to build something new regardless of whether it was better or not.

Ouch, this syntax isn't a looker.

Reply on news.ycombinator.com