5 ms·

I like it, but I'm not sure it's worth the trouble of further expanding an ever-ballooning extended family of SQL syntaxes. Of course, SQL has numerous issues,

by jmull 2y ago

I like it, but I'm not sure it's worth the trouble of further expanding an ever-ballooning extended family of SQL syntaxes. Of course, SQL has numerous issues, both in an absolute sense and relative to what we've come to expect. And the obvious course of action in each individual case is to add syntax to support the missing feature. But as you keep adding syntax, SQL gets more and more complex, both in the individual variants and due to the complexities of multiple variants with different syntax support at different times. A transpiling approach makes more sense to me. I'd like the sql implementors to focus on source maps and other things to better support plugging in external, alternate syntaxes (is a standardized mechanism too much to ask for?). Then individual projects/people/efforts can choose the SQL syntax variant that works for them, and it can be one that evolves separate from the host db.

_dark_matter_ 2y ago
There are a lot of transpilers, and they don't get much adoption because the people implementing them are not the people implementing the database. The database and the query language are tightly coupled. The latter determines the kinds of optimizations that the former can do, and a transpiled language means that optimization info needs to be passed through in weird and inefficient ways; it also means that obvious optimizations aren't possible, because the people building the language are not building the db. If you've ever tried used query hints, or deeply inspected a distributed query plan, you'll know exactly what I mean.
9rx 2y ago
> A transpiling approach makes more sense to me. That's the solution we've been using, oft referred to as ORM, to work around the lack of this syntax. But then you had to live in a world where you had an almost SQL, but not really, language that doesn't always quite map perfectly to SQL and all the pain to go along with that. Now instead of that song and dance you can compose queries with plain SQL and simple string concatenation. That is a huge win, even if troubling that it has taken so long (and still not widely supported).
- jmull 2y ago
  Generally, ORM's try to present a high-level abstraction of your data and data model than the engine does natively. That makes it the ORM's responsibility to implement the abstraction in a good way. Problems come in when the "good" way depends on factors the ORM doesn't consider or expose well. I actually mean transpiler in a mainly syntactic sense, and would want it to avoid higher-level abstractions (after all, as you point out, ORMs have that ground covered).
  - 9rx 2y ago
    While that is closer to ORM in the classical sense, the modern use – and why I used 'oft referred to' – is more accurately a query builder, but get called ORM regardless. The reason people reach for this type of tool is because there hasn't been a good solution to this with SQL proper, necessitating another language. Until now.
deleted 2y ago
[deleted]
harrall 2y ago
Plain transpiling won’t work because when you write a SQL query, you have to know what engine is running it because they all do worse at some things than others. For example, the same result could be fetched using a correlated subquery, a common table expression, a temporary table, a view or very hackily using aggregation and it would depend on whether you were using SQL Server, Postgres, MySQL, or SQLite because they don’t do it all fast. …and you need to know the version. This might be able to be solved if the language was a brand new higher level one though and the compiler knew the intricacies of each engines.
- michaelmior 2y ago
  It seems like this is solved by just having the transpiler aware of the target system.
- gav 2y ago
  One example is LookML, which used to build semantic data models in an analytic layer: https://cloud.google.com/looker/docs/what-is-lookml https://cloud.google.com/looker/docs/what-is-lookml
  - anon84873628 2y ago
    LookML does seem to have invested a lot in compilation to different SQL backends, generally using the best syntax for each. Unfortunately the frontend is so tightly tied to the Looker BI stuff, and it can't really express arbitrary queries without going through lots of hassle. Its unclear what Google is doing with Looker. Its would be interesting to imagine what LookML would be like as an independent SQL tool chain.
    - amcaskill 2y ago
      The creator of LookML is actually working on that with a OSS project called Malloy. https://www.malloydata.dev/ https://www.malloydata.dev/
    - leoh 2y ago
      >LookML does seem to have invested a lot in compilation to different SQL backends, generally using the best syntax for each. To some degree, yes. Yet far and away, users of Looker use engines like RedShift, BigQuery, and Snowflake because they’re extremely effective at the types of queries that Looker sends at them — not because Looker spends a huge number of hours optimizing for each engine (that’s not to say none is done); these dbs are great at analytical queries. Looker in its earlier days (early/mid 2010s) took a bet on analytical database engines getting better as opposed to other technologies; for example, Tableau had its own datastore and largely did not “push queries down to the database” for execution. In the end, BigQuery was radically faster than SparkSQL and was compelling for customers, for example; it was not that Looker spent a ton of time optimizing BigQuery as opposed to SparkSQL. Source: I was an early engineer at Looker

by jmull 2y ago

_dark_matter_ 2y ago

There are a lot of transpilers, and they don't get much adoption because the people implementing them are not the people implementing the database. The database and the query language are tightly coupled. The latter determines the kinds of optimizations that the former can do, and a transpiled language means that optimization info needs to be passed through in weird and inefficient ways; it also means that obvious optimizations aren't possible, because the people building the language are not building the db. If you've ever tried used query hints, or deeply inspected a distributed query plan, you'll know exactly what I mean.

9rx 2y ago

> A transpiling approach makes more sense to me. That's the solution we've been using, oft referred to as ORM, to work around the lack of this syntax. But then you had to live in a world where you had an almost SQL, but not really, language that doesn't always quite map perfectly to SQL and all the pain to go along with that. Now instead of that song and dance you can compose queries with plain SQL and simple string concatenation. That is a huge win, even if troubling that it has taken so long (and still not widely supported).

jmull 2y ago
Generally, ORM's try to present a high-level abstraction of your data and data model than the engine does natively. That makes it the ORM's responsibility to implement the abstraction in a good way. Problems come in when the "good" way depends on factors the ORM doesn't consider or expose well. I actually mean transpiler in a mainly syntactic sense, and would want it to avoid higher-level abstractions (after all, as you point out, ORMs have that ground covered).
- 9rx 2y ago
  While that is closer to ORM in the classical sense, the modern use – and why I used 'oft referred to' – is more accurately a query builder, but get called ORM regardless. The reason people reach for this type of tool is because there hasn't been a good solution to this with SQL proper, necessitating another language. Until now.

deleted 2y ago

[deleted]

harrall 2y ago

Plain transpiling won’t work because when you write a SQL query, you have to know what engine is running it because they all do worse at some things than others. For example, the same result could be fetched using a correlated subquery, a common table expression, a temporary table, a view or very hackily using aggregation and it would depend on whether you were using SQL Server, Postgres, MySQL, or SQLite because they don’t do it all fast. …and you need to know the version. This might be able to be solved if the language was a brand new higher level one though and the compiler knew the intricacies of each engines.

michaelmior 2y ago
It seems like this is solved by just having the transpiler aware of the target system.
gav 2y ago
One example is LookML, which used to build semantic data models in an analytic layer: https://cloud.google.com/looker/docs/what-is-lookml https://cloud.google.com/looker/docs/what-is-lookml
- anon84873628 2y ago
  LookML does seem to have invested a lot in compilation to different SQL backends, generally using the best syntax for each. Unfortunately the frontend is so tightly tied to the Looker BI stuff, and it can't really express arbitrary queries without going through lots of hassle. Its unclear what Google is doing with Looker. Its would be interesting to imagine what LookML would be like as an independent SQL tool chain.
  - amcaskill 2y ago
    The creator of LookML is actually working on that with a OSS project called Malloy. https://www.malloydata.dev/ https://www.malloydata.dev/
  - leoh 2y ago
    >LookML does seem to have invested a lot in compilation to different SQL backends, generally using the best syntax for each. To some degree, yes. Yet far and away, users of Looker use engines like RedShift, BigQuery, and Snowflake because they’re extremely effective at the types of queries that Looker sends at them — not because Looker spends a huge number of hours optimizing for each engine (that’s not to say none is done); these dbs are great at analytical queries. Looker in its earlier days (early/mid 2010s) took a bet on analytical database engines getting better as opposed to other technologies; for example, Tableau had its own datastore and largely did not “push queries down to the database” for execution. In the end, BigQuery was radically faster than SparkSQL and was compelling for customers, for example; it was not that Looker spent a ton of time optimizing BigQuery as opposed to SparkSQL. Source: I was an early engineer at Looker