6 ms·
Example looks like this: query: sessionize is { group_by: flight_date is dep_time.day group_by: carrier aggregate: daily_flight_count is
by fouc 5y ago
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/examples/faa.html https://looker-open-source.github.io/malloy/documentation/ex...
- boomskats 5y agoAmongst 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.
- civilized 5y agoThat's why they tell rather than show.
- wruza 5y agoWhich part of it you couldn’t understand?
- fphhotchips 5y agoI'll bite. What is that code example actually returning? I don't have a clue what fields I expect to get back from that.
- wruza 5y agoI’d expect [{ flight_date: carrier: daily_flight_count: per_plane_data: [ { tail_num: plane_flight_count: flight_legs: [ And so on.
- ltabb 5y agoScroll to the bottom of the page. It shows the returned JSON https://looker-open-source.github.io/malloy/documentation/patterns/sessionize.html https://looker-open-source.github.io/malloy/documentation/pa... Here is the SQL we generate. https://gist.github.com/lloydtabb/8c144d2dac978dda9bf3ec4d6bbd2015 https://gist.github.com/lloydtabb/8c144d2dac978dda9bf3ec4d6b...
- da_chicken 5y agoWhat'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.
- ltabb 5y ago> 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/language/nesting.html 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/examples/iowa/step6.html 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/language/order_by.html 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.
- orthoxerox 5y agoOuch, this syntax isn't a looker.
- Yoric 5y agoWell, this syntax certainly looks less arbitrary than SQL. Feels that it's worth giving a try.
- ralfn 5y agoIt 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.
- Yoric 5y agoI 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.
- stingraycharles 5y agoIt 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.
- tjpnz 5y agoI get a headache just looking at this. I don't think the SQL version would look any worse either.
- ash 5y agoI don't think there is a good way to return nested JSON using SQL, which this example does.
- valenterry 5y agoI think postgres' json syntax in the latest version isn't too bad for these things.
- ash 5y agoIt would be nice to see how this query could look in Postgres.
- mritchie712 5y agoPostgres has excellent syntax for dealing with JSON https://www.postgresql.org/docs/14/functions-json.html https://www.postgresql.org/docs/14/functions-json.html
- ralfn 5y agoThank 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.
- gigatexal 5y agoYeah 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).
- throwaway894345 5y agoI 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?
- darrenf 5y ago> 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).
- shepardrtc 5y agoThis just looks like someone wanted to build something new regardless of whether it was better or not.