[HN Gopher] A sequel to SQL? An intro to Malloy
___________________________________________________________________
A sequel to SQL? An intro to Malloy
Author : carlineng
Score : 79 points
Date : 2022-09-06 15:55 UTC (7 hours ago)
(HTM) web link (carlineng.com)
(TXT) w3m dump (carlineng.com)
| dang wrote:
| Related:
|
| _Malloy - A Better SQL, from Looker_ -
| https://news.ycombinator.com/item?id=30053860 - Jan 2022 (99
| comments)
|
| _Malloy: An Experimental Language for Data_ -
| https://news.ycombinator.com/item?id=28926349 - Oct 2021 (1
| comment)
| yewenjie wrote:
| There is also PRQL which is more intuitive and simpler IMO -
| https://github.com/prql/prql
| carlineng wrote:
| PRQL is very cool, but I think Malloy is meaningfully different
| (and more useful) because of its inclusion of a semantic layer
| into the core language.
| Digit-Al wrote:
| It sounds like Malloy might be able to find a niche amongst some
| casual users, but it will never gain traction amongst DBAs. My
| reasoning is as follows.
|
| Being a DBA is more than just writing queries, you have to be
| able to maintain the database, maintain the security settings,
| and (most importantly for this discussion) do performance tuning.
|
| Performance tuning is incredibly important; I have, personally,
| seen a query that was running in only a few seconds have an
| absolutely catastrophic performance drop off when a few extra
| records were added. I had to get help from the DBA to find where
| it was going wrong and rewrite it to get the performance back to
| a reasonable state again. (I am talking about query run time
| going from seconds to minutes.)
|
| You can't do performance tuning in Malloy I doubt; You'll be
| needing to run the analyser and rewriting the produced SQL. Since
| you'll have to know SQL really well to do this, why bother
| learning Malloy as well?
| tonyhb wrote:
| Performance tuning is important. Things like
| `random_page_cost`, or `effective_cache_size` are important to
| understand - especially with nvme drives, or databases that
| partition data across multiple disks (eg. hot data on nvme,
| stale data on spinning disks).
|
| But...
|
| > Being a DBA is more than just writing queries
|
| > I had to get help from the DBA to find where it was going
| wrong and rewrite it
|
| Just wanted to chuckle at this. I agree with the point -- you
| want to write as close to the metal as possible in SQL. It's
| the same with ORMs. That means... writing SQL, not an
| intermediate language. Although https://prql-lang.org/ looks
| great!
| brightball wrote:
| I still don't understand why so many people seem to talk about
| replacing SQL. It's not complicated, can be learned in a few
| hours, it's relatively easy to read and it fairly portable
| across databases and stacks.
|
| It's been the most important detail of my job for the last 20
| years.
| bob1029 wrote:
| SQL is uniquely challenging for many to learn because you
| cannot poke around in the bushes procedurally and still
| arrive at a good outcome. The declarative nature of SQL
| usually means all or nothing.
|
| I do recall how frustrating it was to learn all of the join
| shapes. It's so trivial to me now, but it was a nightmare at
| the beginning of my journey. The popularity of ORMs is
| absolutely not a surprise to me.
| jandrewrogers wrote:
| SQL has a couple legitimate limitations as a query language,
| largely because we expect databases to do so much more today
| than when it was designed. I've been both a heavy user of SQL
| and implementor of SQL databases focused on data models like
| graph, spatial, et al, so I feel the pain from both
| directions.
|
| Some useful things are difficult to effectively express in
| SQL because it was not designed to make them expressible.
| Adding support after the fact introduced a lot of compromises
| and complexity; you can tell which parts of SQL are second-
| class citizens. SQL would have been designed differently if
| its original scope had been broader and it is an inelegant
| mess for some types of data models. You can make it work but
| it isn't pleasant. Attempts to address these gaps explain
| much of why SQL databases all have their own non-standard SQL
| dialect, and it has made SQL effectively non-portable across
| databases.
|
| In principle, a more modern query language could elegantly
| address the much broader scope of how we use databases today
| in a standardized way, instead of the zoo of non-standard
| extensions and mismatched parts that are grafted onto
| specific SQL implementations.
| bravura wrote:
| You could make the same argument that we all should learn
| assembly language, because when our C compilers produce bad
| assembly we'll have to rewrite it by hand. But I haven't
| written assembly in 30 years, and even then it was just for
| fun.
|
| As it turns out, having high-level abstractions means that in
| many ways it's easier to do automatic optimization. I look
| forward to automatic data-driven database tuning and query
| optimization.
| munk-a wrote:
| Postgres has done an incredible job optimizing both the
| overall performance of queries and the query planner - but
| these things make mistakes still and being able to fix those
| mistakes can make the difference between a two minute query
| and a twenty milisecond query - this comes with the fact that
| complex database operations can usually make or break overall
| response times, and these optimizations usually depend on
| statistic accuracy which can be hard to ensure.
|
| If SQL optimization was as good as compilers I'd be all on
| this train, but I just don't think we're there yet.
| meitros wrote:
| People do this, e.g. building a cost-based optimizer for
| queries, but it can take over 2 years for it to really get
| pretty good. And even then it can only go so far and you'll
| still want to manually tune certain queries.
| anon84873628 wrote:
| ianbicking wrote:
| Reading about the "semantic layer" it very much reminds me of the
| kind of things people do in an ORM. That is: how do tables
| relate, refinements of data types like strings where a column
| might have specific semantics... this post doesn't go into much
| so I don't know if Malloy also allows specifying things like how
| updates should happen (do you update in place or create new
| records?), reusable queries (especially given its nesting),
| knowledge of indexed vs unindexed queries, etc. All of this stuff
| usually either gets stuffed in the ORM layer, or exists only as
| folk wisdom about specific databases.
|
| It is peculiar that databases typically lack referential
| integrity, something that we've decided is absolutely essential
| in other programming environments.
| ako wrote:
| It looks closer to a Data Fabric where you have an ORM as a
| service on top of all your hetereogenerous datasources and
| services: a semantic layer that enables you to define models
| across all your sources, and a data virtualization query engine
| that gets the data from these different sources without
| replicating all the data.
| pasc1878 wrote:
| I am confused here. Referential integrity can only be
| implemnented in the database. If you try in the application
| there are race conditions that will break it. RI is a major
| reason to use a RMDBS (Look it is a Referential Database
| System)
| [deleted]
| carlineng wrote:
| My musings on why SQL has been so hard to displace, and what it
| might take to do so. Any and all feedback appreciated!
| SonOfLilit wrote:
| For a much much more mature product in this area with a very
| strong team behind it, see EdgeDB
| anon84873628 wrote:
| knutwannheden wrote:
| Out if necessity I've started working with Microsoft's Kusto
| Query Language [1] which is used by various services in Azure (e
| g. their Log Analytics Workspace).
|
| At first I found the language rather akward and was wondering why
| yet another query language. But the more I used it the more it
| grew on me. The thing I really like is that unlike the clauses in
| SQL, the order of operators isn't really fixed and it reads and
| feels like a pipe command in a Unix shell.
|
| One example where I find this far superior is when doing
| aggregations. In SQL I would have to modify both the start and
| the end of the query, which is quite a nuisance.
|
| [1] https://docs.microsoft.com/en-us/azure/data-
| explorer/kusto/q...)
| antruok wrote:
| The query structure does look nice indeed! The usage of the
| pipe character feels odd but I suppose there are benefits in
| the end
| knutwannheden wrote:
| I had the same initial reaction regarding the pipe character.
| But once I started thinking of the query as a pipe (like in
| the terminal) through which the data flows, where stuff like
| ORDER BY, SELECT, and GROUP BY are just operators, it started
| making sense.
| oldmanhorton wrote:
| Kusto is used extensively within Microsoft and has been for a
| long time. I think it's generally really well liked and really
| productive, and while it tends to be quite quick, it has some
| similar performance pitfalls as SQL
| bradford wrote:
| (disclaimer: Microsoft Employee, this is my opinion).
|
| I've been using KQL for a long time, it really is a nice
| language both for querying and maintaining the data.
|
| But, aside from fixing some serious language issues with SQL, I
| really enjoy the wide range of supported scenarios. You can use
| KQL to query a SQL database [1], you can use python [2], do all
| kinds of time-series analysis [3][4], do distinct counts on
| various fields without too much explicit query-authoring [5].
|
| My main beef with Azure Data Explorer (which, as I understand
| it, is the engine that handles the query execution) is the
| price... I wish it was easy for hobbyist developers to launch
| and try out.
|
| [1] https://docs.microsoft.com/en-us/azure/data-
| explorer/kusto/q... [2] https://docs.microsoft.com/en-
| us/azure/data-explorer/kusto/q... [3]
| https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
| [4] https://docs.microsoft.com/en-us/azure/data-
| explorer/kusto/q... [5] https://docs.microsoft.com/en-
| us/azure/data-explorer/kusto/q...
| knutwannheden wrote:
| There is this page about a free cluster [1]. It appears to be
| pretty beefy, but it seems like it is for one year only, but
| IMHO that isn't entirely clear.
|
| [1] https://docs.microsoft.com/en-us/azure/data-
| explorer/start-f...
| MaKey wrote:
| Today Microsoft announced that an emulator for Azure Data
| Explorer is generally available [1]. It is a Windows docker
| container.
|
| [1] https://aka.ms/adx.emulator.doc
| rockostrich wrote:
| I like the VS Code integration that Malloy has. There's pretty
| limited in-browser tooling for BigQuery so that bit of the
| extension is amazing.
|
| But I found practically that it's very hard to get folks that are
| writing SQL day-to-day to try to integrate a new language on top
| of something they already understand in and out so I'm thinking
| about just pulling out the BigQuery bits from their VS Code
| extension to be able to write SQL with in VS Code with auto-
| complete and references.
| Cyberdog wrote:
| If I'm understanding the article correctly, VS Code integration
| is currently _all_ Mallory has, and contrary to popular belief,
| that 's not the only code editor in existence, so it seems like
| a huge limitation to me. That it apparently just compiles to
| SQL (I guess? The article seems to imply that's how it works
| but the README.md on the GitHub repo doesn't seem to mention
| that it does that that I can find) is another limitation and
| another check in the "why not just use SQL anyway?" column.
|
| I'm all for a more humane SQL replacement, and maybe this has
| potential to be one, but right now it seems to be in the stage
| where it's little more than the code equivalent of a sketchpad
| doodle. Let's see where it goes.
| otabdeveloper4 wrote:
| > there are relatively few database targets that it must support
|
| Heh. Oh wow.
| oxfordmale wrote:
| This is the umpteenth attempt at replacing SQL. Just like all
| previous attempts, it may well address some weaknesses of SQL,
| however, it introduces a whole new range of issues. SQL has been
| around so long as it mostly works.
| dominotw wrote:
| It only works if you are just running queries in console. Any
| serious work with sql requires all sorts of weird stuff built
| around it like dbt, cubejs[1].
|
| 1. https://cube.dev/docs/
| munk-a wrote:
| I don't really think that's true - we write quite a lot of
| SQL in-house and we have no issues doing it safely. There are
| some tools we rely on to make our lives easier but these
| mostly revolve around making batch operations easier to
| express (like IN(:array) as opposed to having to glue some
| string joining logic relying on an array count into every
| individual query). SQL definitely isn't the cleanest thing
| ever and I have a number of improvements I'd personally
| really appreciate (changing statement order, trailing commas,
| better aggregate and window modifier definitions) but doing
| Serious SQL is definitely an accomplishable thing - even
| weird statistical bucketing and aggregation that produces a
| query that's... 291 lines long.
| dominotw wrote:
| what do you make of things like dbt which is awkward mix of
| python templating and sql mixed into each other.
| munk-a wrote:
| I write highly complex SQL for a living so I don't think
| I'm the best person to comment on it - I have found that
| pretty much anything you want to do can be done with pure
| SQL and the resultant mass is usually going to yield much
| better performance over mixed solutions... but I haven't
| used dbt so my comments are more directed at traditional
| mixed solutions (like sequentially submitted queries
| being stitched together in C++/PHP or pruning and joining
| multiple ORM delivered results into a full in memory data
| set). The way I usually like to write mixed SQL is
| preparation, execution, cleanup - have a blob of complex
| logic to preprocess the request into the appropriate SQL
| recipe - execute said recipe - then go back to imperative
| programming land to apply any different sourced joins
| (i.e. combine a DB query result with something coming
| back from OpenSearch, a memcached query or some other
| non-relational database source) apply any complex
| customization and value cleanup (like pulling data out of
| a JSONB blob and actually sending it as a plain array to
| the consumer) and then shuffling it off to whoever
| requested it.
|
| Again, I don't feel comfortable commenting on any tool I
| haven't personally used and I hope that was helpful - if
| you have any other questions I'm happy to try and answer.
| richiebful1 wrote:
| As someone who uses dbt, I would recommend it for an
| analytics workflow. The templating is useful for shared
| code, like a CTE you use in multiple places. That said,
| you don't need to use a lot of templating for it to be
| useful.
|
| DBT shines, not because of the language/templating, but
| because it handles a lot of the scut work of building out
| a data warehouse. Write the select statements you want to
| populate the model, write tests to constrain the model,
| and build.
| carlineng wrote:
| I think Malloy is meaningfully different from previous attempts
| (e.g., PRQL), and describe why in the post, namely the
| inclusion of a semantic layer as part of the language. Take a
| look at the post, and would love to hear if you agree or not.
| oxfordmale wrote:
| How is this better than the SQL equivalent? How can I break
| down this query and run parts of it for debugging purposes?
|
| 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 ] } } }
| RA_Fisher wrote:
| It's nice, but it's hard to beat the clarity and expressiveness
| of dplyr and purrr.
| awsrocks wrote:
| cryptonector wrote:
| The minimum enhancement I want for SQL is a version where _no
| literal values_ are allowed in queries, as this would completely
| preclude SQL injection :)
|
| To make that more tolerable for query planning purposes, there
| would have to be two types of query parameters: compile-time and
| run-time.
|
| Next up: why not allow query clauses to come in any order?
| `SELECT .. FROM .. WHERE ..;` or `FROM .. SELECT .. WHERE;` and
| so on.
|
| Since the query parser/planner has to see the whole thing
| anyways. The parser/planner can't begin coding at `SELECT`, or at
| `WHERE`, since there might be a `GROUP BY`, or an `ORDER BY` that
| affect the whole query plan, so all these clauses might as well
| come in _any_ order. Wanna put `HAVING` first? Sure, why not. It
| 's probably best to insist that table sources all come together
| rather than be all over, but I think even that doesn't _have_ to
| be so.
|
| Also, I'd like an out-of-band mechanism for expressing query
| planner hints. This would be a separate string or object passed
| along with the query, and which does things like: identify a
| table source to use as the outer-most table for the query plan,
| for each of some or all table sources identify an index to use or
| temp index to create, for each of some or all joins pick a join
| strategy, etc. Table sources would have to be addressed as
| {<CTE_name>, <table_source_name>}, naturally. Such a thing should
| also allow one to specify indices that should be created on CTEs.
| klysm wrote:
| Not sure that's worth it? It's not a difficult software
| engineering problem to prevent SQL injection categorically.
| staticassertion wrote:
| A lot of safety/security isn't hard, people just don't do it
| if it isn't forced on them/ easy.
| akshayB wrote:
| SQL is around since the dawn of relational database and its hard
| to replace. The best option for mass adoption is to have drag and
| drop tools with visualizations like no-code ETL. Template like
| and markup language or framework are easier to adopt for new
| developers but majority of the population still tend of stick
| with the original language.
| munk-a wrote:
| > The best option for mass adoption is to have drag and drop
| tools with visualizations like no-code ETL.
|
| No. I've worked with BI tools and when things get complicated
| you end up needing to go back to text to express the weird bits
| and every company has a few queries with weird bits in it.
|
| I also will gladly agree that it's network effect is what makes
| it so hard to replace (as opposed to some perceived perfection
| of the language - it definitely isn't perfect) but SQL has
| evolved significantly over time. Core SQL hasn't - but Postgres
| in particular has pushed the envelope on what can be done with
| WINDOWs, CTEs, and aggregate modifiers. I think it's a bit
| misleading to say the majority of the population still tend to
| stick with the original language since, at a previous job we
| did attempt to write "neutral SQL" that would execute on MSSQL,
| Postgres and MySQL - but in most shops you'll have a chosen
| dialect and you'll be able to make use of more recent and
| advanced language features... So the majority of the population
| is using modern SQL just like the majority of programmers
| that'd describe themselves C/C++ programmers can't grok ANSI C.
| summerlight wrote:
| Because of this reason, vast majority of new generation query
| languages are translated into SQL but in fact it is not a great
| language as a target language. I think SQL should more focus on
| features as an efficient intermediate language rather than
| adding more and more ad hoc "convenient" features that don't
| really play well with other language features...
| steve_g wrote:
| If we still use math 100 years from now, we'll still use SQL.
| It's a fine way to query relational data, and relational data
| is a fine way to model reality.
|
| I'd like to hear from people that think I'm wrong.
| anon84873628 wrote:
| pasc1878 wrote:
| SQL does not fully implement Relational Algebra or Calculus,
| which are isomorphic.
|
| See Many of Chris Dates' books and things like Tutorial D
| which do meet the ALgebra.
|
| SQL is near enough the theory to work and also has had so
| much effort put into making it work fast, reliably and scale
| for volume that a new language has too much to overcome even
| if it can deal with all cases. So it won't be replaced soon.
|
| However 100 years is longer than SQL had been around so a
| proper relation server could come around, there is just too
| much uncertainty.
| randomdata wrote:
| _> If we still use math 100 years from now, we 'll still use
| SQL._
|
| Those are incongruent. Do you mean Western notation?
|
| _> It 's a fine way to query relational data_
|
| It's quirky, but good enough for ad-hoc queries that I think
| it will be hard to overcome the momentum in that area.
|
| It's not fine for application work, where you need things
| like composition. We've tried to solve those problems with
| ORMs, but the ORM is starting to fall out of fashion due to a
| number of problems of its own. SQL is not a great compiler
| target. I do eventually see something lower level built for
| programmers, not data analysts, rising up here. If SQL is
| compared to Javascript, something akin to WASM, perhaps.
| munk-a wrote:
| ORMs, when best used (and we use them even though we're
| pretty SQL literate and maintain a lot of SQL) will survive
| forever, nothing beats an ORM for really dirt simple
| expressions that you want to be trivially testable. Never
| in my life do I want to see someone write an UPDATE query
| against a single table with no shenanigans with dynamic
| field support using string gluing to properly stitch in all
| the columns - this is something a known tool can do better,
| this is a great opportunity for an ORM.
|
| A non-great opportunity for an ORM is anything I'd call a
| "report query" (some complex read-only query involving a
| lot of JOINs, a bunch of WHERE clauses and possibly some
| nested aggregation for funsies) - this is where you pull
| out the SQL (or alternative query language!) because an ORM
| will struggle to properly support all the functionality you
| need and because trying to tune a query being produced by
| an ORM (even just to make sure it's well aligned with
| logical indices) is a task that yields nothing but endless
| frustration.
| Scarbutt wrote:
| _Never in my life do I want to see someone write an
| UPDATE query against a single table with no shenanigans
| with dynamic field support using string gluing to
| properly stitch in all the columns_
|
| These are just convenient features that most ORMs provide
| and can exist entirely outside of ORMs, they are not the
| primary purpose of ORMs.
| munk-a wrote:
| You are correct by their design. But by usage I've found
| that to be by far the most valuable thing that ORMs
| deliver. Making use of ORMs to power an ActiveRecord
| system in your codebase has only ever, to my observation,
| lead to pain. Querybuilders that are equipped with more
| advanced functionality around type security and response
| decoding are quite a valuable tool.
| cgh wrote:
| The GP is referring to SQL's mathematical roots:
| https://en.wikipedia.org/wiki/Tuple_relational_calculus
| randomdata wrote:
| Then the statement resolves to "If we still use math 100
| years from now, we'll still use math.", which is a rather
| silly statement. SQL and Western notation are interesting
| to compare in that they are the dominant, but not
| exclusive, languages used to describe their respective
| mathematical domains.
| [deleted]
| steve_g wrote:
| I meant "still use math" as a proxy for "still use formal
| languages to communicate".
|
| Maybe in 100 years AI will be so powerful that we'll just
| ask our question in natural language and get the answer we
| need. Or maybe in 100 years AI will have harvested us for
| the iron in our blood. Either way we wouldn't need SQL
| anymore.
| TillE wrote:
| It's pretty obviously silly to have an unavoidable text
| parser in between code and data. We've reached the point
| where it's fairly low overhead, but it's still not nothing.
| [deleted]
| Cyberdog wrote:
| Code source control is a vital aspect of software development
| in the modern era, and no-code tools are incompatible with that
| unless they are also able to output their representations as
| plain code so that tools like "diff" work as expected, in which
| case you might as well stick to SQL.
| Digit-Al wrote:
| That is so true! Have you ever used SSIS? A really powerful
| tool, but even small changes can cause hundreds of changes in
| the underlying XML, which makes change control a nightmare.
| Forget branching and merging anything other the most minor
| changes.
| pasc1878 wrote:
| That is a limitation of text based tools.
|
| There have been code source control tools based on the AST
| see Envy for Smalltalk.
|
| Hopefully eventually we will dump the limitations of text
| based tools and use one based on the structure of programs. I
| don't want to know line 123 has changed I want to know that
| function fn in module m has changed or that function X was
| added on this date.
| randomdata wrote:
| _> SQL is around since the dawn of relational database_
|
| There was a decent amount of competition back then, though. It
| seems the industry eventually settled on SQL to be compatible
| with Oracle's dominance. Postgres didn't gain SQL support until
| about a decade in.
| onlyrealcuzzo wrote:
| What were some of the alternatives? What did Postgres start
| with?
| randomdata wrote:
| Postgres used QUEL in the early days, as did its
| predecessor Ingres. MRDS, the first commercially available
| relational database, used a language known as Linus. Alpha
| was the language originally envisioned by Codd to describe
| his relational model.
___________________________________________________________________
(page generated 2022-09-06 23:01 UTC)