14 ms·

A sequel to SQL? An intro to Malloy

117 points by carlineng 4y ago

carlineng 4y ago
My musings on why SQL has been so hard to displace, and what it might take to do so. Any and all feedback appreciated!
- dsr_ 4y ago
  The thing that is absolutely essential to displace SQL is to have the replacement be the native interface to a high-performance database that people want to use.
rockostrich 4y ago
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 4y ago
  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.
akshayB 4y ago
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.
- Cyberdog 4y ago
  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 4y ago
    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 4y ago
    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.
- summerlight 4y ago
  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 4y ago
ianbicking 4y ago
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.
- pasc1878 4y ago
  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)
- ako 4y ago
  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.
- kthejoker2 4y ago
  Semantic layers usually go an extra mile beyond just ORM/E-R modeling. It is best thought of as an abstraction layer between the actual physical data system and the consumption tool (usually a BI or analytics app.) They do things like define KPIs (how do we as an organization calculate "cost of goods sold") and business logic (what is an "invalid" order?), harmonize entities and attributes across multiple data sources (System A calls something foo, System B calls the same thing bar), provide localization options (currency, date formatting) and so on. It can also do "basic" things like referential integrity, E-R modeling, aliasing columns, fixing data types for downstream consumption, etc. Usually it is agnostic to the actual underlying data system (warehouse, lake, SaaS API ...)
knutwannheden 4y ago
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/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...)
- antruok 4y ago
  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 4y ago
    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 4y ago
  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 4y ago
  (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/query/sqlrequestplugin?pivots=azuredataexplorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [2] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/pythonplugin?pivots=azuredataexplorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [3] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/series-firfunction https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [4] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/series-fft-function https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [5] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/dcount-intersect-plugin https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
oxfordmale 4y ago
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.
- carlineng 4y ago
  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 4y ago
    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 ] } } }
    - anon84873628 4y ago
      That's an example with lots of model definitions inline. The idea is that you would define various components separately so they can be reused (and to your point, broken down and debugged separately). Malloy brings the modularization and reusability that is missing from SQL itself.
      - oxfordmale 4y ago
        It is the first thing I try to teach CS graduates: DRY ( re-usability) is not applicable in the SQL world. Trying to re-use code, as proposed in Malloy generally results in poor performing queries. Malloy also seems needlessly complex compared to highly successful tools like DBT. One other reason that DBT is successful is the low threshold to migrate your existing codebase. It is larger than Typescript Vs JavaScript, however, it can generalky be done in a week or two.
Digit-Al 4y ago
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?
- bravura 4y ago
  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.
  - meitros 4y ago
    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.
  - munk-a 4y ago
    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.
yewenjie 4y ago
There is also PRQL which is more intuitive and simpler IMO - https://github.com/prql/prql https://github.com/prql/prql
- carlineng 4y ago
  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.
  - fatherzine 4y ago
    Thanks for posting your thoughts. I'm having a bit of difficulty breaking down "semantic layer" into concrete technological concepts. An attempt: * Schema definition. PK/FK columns and their relationships. Standard ER fare. * Dimensions. Partially overlap with PK/FK structure, but may include fields that don't map to an explicit key column, e.g. [trunc] date or zip code or even binned measures. Can see the value of having dimensions documented across a team. * Measures. Mainly named aggregations, e.g. value = sum(price * quant), which can be aggregated over many dimensional combinations. Definitely useful, though I'd expect PRQL "functions" to be usable in the same role. * Formatting rules. Am I missing something crucial?
    - carlineng 4y ago
      Nope, those are the primary components of a semantic layer. Most other semantic layer products have three main components: input data sources -- typically SQL queries or table names, configuration -- the semantic layer describing all the things you mention above, relative to the input data sources, and the access layer -- usually a non-SQL API that consumers must use to consume data that has been modeled by the semantic layer. Check out the docs for Cube [1] for an example of this. Cube also has a SQL API, but it's not fully fleshed out yet. What makes Malloy shine is that all 3 of these things are integrated in the same language, so users don't have to jump between different tools to model and explore their data. You can query/explore data in Malloy, iterate on functions to express your business logic, and immediately view the results. Doing this in something like Cube would require you to: (1) write SQL queries to prototype the function, (2) update the Cube configuration files with your changes, and (3) hit the REST API with a request to view results. In Malloy, it's all just writing and running Malloy queries. [1]: https://cube.dev/docs/query-format https://cube.dev/docs/query-format
awsrocks 4y ago
deleted 4y ago
[deleted]
SonOfLilit 4y ago
For a much much more mature product in this area with a very strong team behind it, see EdgeDB
- anon84873628 4y ago
dang 4y ago
Related: Malloy – A Better SQL, from Looker - https://news.ycombinator.com/item?id=30053860 https://news.ycombinator.com/item?id=30053860 - Jan 2022 (99 comments) Malloy: An Experimental Language for Data - https://news.ycombinator.com/item?id=28926349 https://news.ycombinator.com/item?id=28926349 - Oct 2021 (1 comment)
otabdeveloper4 4y ago
> there are relatively few database targets that it must support Heh. Oh wow.
RA_Fisher 4y ago
It’s nice, but it’s hard to beat the clarity and expressiveness of dplyr and purrr.
cryptonector 4y ago
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 4y ago
  Not sure that’s worth it? It’s not a difficult software engineering problem to prevent SQL injection categorically.
  - staticassertion 4y ago
    A lot of safety/security isn't hard, people just don't do it if it isn't forced on them/ easy.
    - cryptonector 4y ago
      This. If SQL injection was infeasible because the language had no literals, then there would be no SQL injection. Given that SQL injection is possible because the language does have literals, we should and do in fact still have SQL injection issues.
cryptonector 4y ago
This is the example at https://github.com/looker-open-source/malloy https://github.com/looker-open-source/malloy query: table('malloy-data.faa.flights') -> { where: origin ? 'SFO' group_by: carrier aggregate: flight_count is count() average_flight_time is flight_time.avg() } SELECT carrier, COUNT(*) as flight_count, AVG(flight_time) as average_flight_time FROM `malloy-data.faa.flights` WHERE origin = 'SFO' GROUP BY carrier ORDER BY flight_count desc -- malloy automatically orders by the first aggregate I don't see much value in this. This is not aesthetically better than SQL. It's also semantically better. This is just a different syntax that would parse to the same AST. And what's with the `?` for equality?!
- ape4 4y ago
  Yeah the ? for equals is not better
  - cryptonector 4y ago
    None of it is. Examples of things that would be better would be syntax for reference dereferencing. E.g., in SQL-like syntax: SELECT user->home_server->OS_version.version AS v, user->home_server->OS_version.is_obsolete AS obsolete FROM users WHERE name = 'whatever'; or SELECT user->home_server->OS_version.{version, is_obsolete} AS {version, obsolete} FROM users WHERE name = 'whatever'; where we don't have to join to the home servers table or the OS versions table, and also we don't have to repeat ourselves too much. Where the relations are not 1-1 then an array_agg() aggregation could be inferred, or some alternative aggregation could be explicitly given: -- this would be an array of {name, type} values SELECT g.members.{name, type} FROM groups g WHERE g.name = 'whatever'; -- ditto SELECT g.members.{name, type}.array_agg() FROM groups g WHERE g.name = 'whatever'; SELECT g.members.name.count() FROM groups g WHERE g.name = 'whatever'; Now all that would be fantastic. Tinkering with totally different syntax where the shape of the query looks just about the same as in SQL seems like a dead end to me.
    - wruza 4y ago
deleted 4y ago
[deleted]
bloaf 4y ago
I don't think SQL gets replaced until we move to a different database paradigm. For example, I could imagine a function database model [0] getting paired with a "grammar of graphics" type of metadata for the records to create much more concise query/aggregate declarations, especially where time series are concerned. [0] https://en.wikipedia.org/wiki/Functional_database_model https://en.wikipedia.org/wiki/Functional_database_model
- randomdata 4y ago
  SQL, for the most part, has already been replaced as a language used by people with alternatives like query builders, ORMs, etc. Beyond the occasional ad-hoc query, SQL simply isn't suitable for the tasks we require of modern relational databases, lacking features like composition that today's applications need. It remains as a compiler target for those tools, but I am not sure that is the level of abstraction in question.
ltabb 4y ago
If you would like to play with Malloy... You can Fiddle using just a web browser. The Malloy Fiddle uses DuckDB and WASM. https://twitter.com/lloydtabb/status/1567671348306264064

117 points by carlineng 4y ago

carlineng 4y ago

My musings on why SQL has been so hard to displace, and what it might take to do so. Any and all feedback appreciated!

dsr_ 4y ago
The thing that is absolutely essential to displace SQL is to have the replacement be the native interface to a high-performance database that people want to use.

rockostrich 4y ago

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 4y ago
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.

akshayB 4y ago

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.

Cyberdog 4y ago
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 4y ago
  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 4y ago
  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.
summerlight 4y ago
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 4y ago

ianbicking 4y ago

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.

pasc1878 4y ago
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)
ako 4y ago
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.
kthejoker2 4y ago
Semantic layers usually go an extra mile beyond just ORM/E-R modeling. It is best thought of as an abstraction layer between the actual physical data system and the consumption tool (usually a BI or analytics app.) They do things like define KPIs (how do we as an organization calculate "cost of goods sold") and business logic (what is an "invalid" order?), harmonize entities and attributes across multiple data sources (System A calls something foo, System B calls the same thing bar), provide localization options (currency, date formatting) and so on. It can also do "basic" things like referential integrity, E-R modeling, aliasing columns, fixing data types for downstream consumption, etc. Usually it is agnostic to the actual underlying data system (warehouse, lake, SaaS API ...)

knutwannheden 4y ago

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/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...)

antruok 4y ago
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 4y ago
  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 4y ago
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 4y ago
(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/query/sqlrequestplugin?pivots=azuredataexplorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [2] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/pythonplugin?pivots=azuredataexplorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [3] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/series-firfunction https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [4] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/series-fft-function https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q... [5] https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/dcount-intersect-plugin https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...

oxfordmale 4y ago

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.

carlineng 4y ago
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 4y ago
  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 ] } } }
  - anon84873628 4y ago
    That's an example with lots of model definitions inline. The idea is that you would define various components separately so they can be reused (and to your point, broken down and debugged separately). Malloy brings the modularization and reusability that is missing from SQL itself.
    - oxfordmale 4y ago
      It is the first thing I try to teach CS graduates: DRY ( re-usability) is not applicable in the SQL world. Trying to re-use code, as proposed in Malloy generally results in poor performing queries. Malloy also seems needlessly complex compared to highly successful tools like DBT. One other reason that DBT is successful is the low threshold to migrate your existing codebase. It is larger than Typescript Vs JavaScript, however, it can generalky be done in a week or two.

Digit-Al 4y ago

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?

bravura 4y ago
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.
- meitros 4y ago
  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.
- munk-a 4y ago
  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.

yewenjie 4y ago

There is also PRQL which is more intuitive and simpler IMO - https://github.com/prql/prql https://github.com/prql/prql

carlineng 4y ago
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.
- fatherzine 4y ago
  Thanks for posting your thoughts. I'm having a bit of difficulty breaking down "semantic layer" into concrete technological concepts. An attempt: * Schema definition. PK/FK columns and their relationships. Standard ER fare. * Dimensions. Partially overlap with PK/FK structure, but may include fields that don't map to an explicit key column, e.g. [trunc] date or zip code or even binned measures. Can see the value of having dimensions documented across a team. * Measures. Mainly named aggregations, e.g. value = sum(price * quant), which can be aggregated over many dimensional combinations. Definitely useful, though I'd expect PRQL "functions" to be usable in the same role. * Formatting rules. Am I missing something crucial?
  - carlineng 4y ago
    Nope, those are the primary components of a semantic layer. Most other semantic layer products have three main components: input data sources -- typically SQL queries or table names, configuration -- the semantic layer describing all the things you mention above, relative to the input data sources, and the access layer -- usually a non-SQL API that consumers must use to consume data that has been modeled by the semantic layer. Check out the docs for Cube [1] for an example of this. Cube also has a SQL API, but it's not fully fleshed out yet. What makes Malloy shine is that all 3 of these things are integrated in the same language, so users don't have to jump between different tools to model and explore their data. You can query/explore data in Malloy, iterate on functions to express your business logic, and immediately view the results. Doing this in something like Cube would require you to: (1) write SQL queries to prototype the function, (2) update the Cube configuration files with your changes, and (3) hit the REST API with a request to view results. In Malloy, it's all just writing and running Malloy queries. [1]: https://cube.dev/docs/query-format https://cube.dev/docs/query-format

awsrocks 4y ago

deleted 4y ago

[deleted]

SonOfLilit 4y ago

For a much much more mature product in this area with a very strong team behind it, see EdgeDB

anon84873628 4y ago

dang 4y ago

Related: Malloy – A Better SQL, from Looker - https://news.ycombinator.com/item?id=30053860 https://news.ycombinator.com/item?id=30053860 - Jan 2022 (99 comments) Malloy: An Experimental Language for Data - https://news.ycombinator.com/item?id=28926349 https://news.ycombinator.com/item?id=28926349 - Oct 2021 (1 comment)

otabdeveloper4 4y ago

> there are relatively few database targets that it must support Heh. Oh wow.

RA_Fisher 4y ago

It’s nice, but it’s hard to beat the clarity and expressiveness of dplyr and purrr.

cryptonector 4y ago

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 4y ago
Not sure that’s worth it? It’s not a difficult software engineering problem to prevent SQL injection categorically.
- staticassertion 4y ago
  A lot of safety/security isn't hard, people just don't do it if it isn't forced on them/ easy.
  - cryptonector 4y ago
    This. If SQL injection was infeasible because the language had no literals, then there would be no SQL injection. Given that SQL injection is possible because the language does have literals, we should and do in fact still have SQL injection issues.

cryptonector 4y ago

This is the example at https://github.com/looker-open-source/malloy https://github.com/looker-open-source/malloy query: table('malloy-data.faa.flights') -> { where: origin ? 'SFO' group_by: carrier aggregate: flight_count is count() average_flight_time is flight_time.avg() } SELECT carrier, COUNT(*) as flight_count, AVG(flight_time) as average_flight_time FROM `malloy-data.faa.flights` WHERE origin = 'SFO' GROUP BY carrier ORDER BY flight_count desc -- malloy automatically orders by the first aggregate I don't see much value in this. This is not aesthetically better than SQL. It's also semantically better. This is just a different syntax that would parse to the same AST. And what's with the `?` for equality?!

ape4 4y ago
Yeah the ? for equals is not better
- cryptonector 4y ago
  None of it is. Examples of things that would be better would be syntax for reference dereferencing. E.g., in SQL-like syntax: SELECT user->home_server->OS_version.version AS v, user->home_server->OS_version.is_obsolete AS obsolete FROM users WHERE name = 'whatever'; or SELECT user->home_server->OS_version.{version, is_obsolete} AS {version, obsolete} FROM users WHERE name = 'whatever'; where we don't have to join to the home servers table or the OS versions table, and also we don't have to repeat ourselves too much. Where the relations are not 1-1 then an array_agg() aggregation could be inferred, or some alternative aggregation could be explicitly given: -- this would be an array of {name, type} values SELECT g.members.{name, type} FROM groups g WHERE g.name = 'whatever'; -- ditto SELECT g.members.{name, type}.array_agg() FROM groups g WHERE g.name = 'whatever'; SELECT g.members.name.count() FROM groups g WHERE g.name = 'whatever'; Now all that would be fantastic. Tinkering with totally different syntax where the shape of the query looks just about the same as in SQL seems like a dead end to me.
  - wruza 4y ago

deleted 4y ago

[deleted]

bloaf 4y ago

I don't think SQL gets replaced until we move to a different database paradigm. For example, I could imagine a function database model [0] getting paired with a "grammar of graphics" type of metadata for the records to create much more concise query/aggregate declarations, especially where time series are concerned. [0] https://en.wikipedia.org/wiki/Functional_database_model https://en.wikipedia.org/wiki/Functional_database_model

randomdata 4y ago
SQL, for the most part, has already been replaced as a language used by people with alternatives like query builders, ORMs, etc. Beyond the occasional ad-hoc query, SQL simply isn't suitable for the tasks we require of modern relational databases, lacking features like composition that today's applications need. It remains as a compiler target for those tools, but I am not sure that is the level of abstraction in question.

ltabb 4y ago

If you would like to play with Malloy... You can Fiddle using just a web browser. The Malloy Fiddle uses DuckDB and WASM. https://twitter.com/lloydtabb/status/1567671348306264064