Hacker News Reader

Comment by harrall | Hacker News Reader

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.

View on HN

Yes, any such transpiler would certainly need to be engine aware (for multiple reasons). Right off the bat, the output needs to be in a syntax the engine accepts, and there are all kinds of differences from engine to engine, version to version.

And, as you say, if the language contains higher-level abstractions, it might need to work quite differently depending on the target engine.

My thought is to avoid those higher-level abstractions as much as possible and just expose the capabilities of the engine as directly as possible, albeit with a different syntax. In my experience, developers who are willing and able to write SQL are fine with targeting a specific engine and optimizing to that. (Those that aren't get someone else to do it, or live with he sad consequences of dealing with an ORM.)

To summarize:

Normal Approach: you pick an engine, and get the syntax that comes with it. You need to know what the engine does well and doesn't do well. You write SQL accordingly, using the syntax the engine accepts.

Transpiler Approach: you pick an engine, and independently choose a syntax. You still need to know what the engine does well and doesn't do well. You still write SQL accordingly, but using the syntax of the language you chose.

I don’t know if the transpiler approach really helps when you still have to be aware of the underlying database engine. I’m fine with choosing something like PostgreSQL and writing code optimized for its specific SQL dialect.

But I’m not sure I want to deal with a transpiler that still suffers from a leaky abstraction. It starts to feel a lot like using an ORM. ORMs promise database-agnostic syntax across different engines, but that promise falls apart when you need to optimize a query and suddenly realize you must account for engine-specific features—like PostgreSQL’s BRIN indexes.

To me, abstracting away the engine is not a goal. (Generally, it's pointless -- the various engines work in different ways and offer different features. While there's a bunch of stuff that widely common, that's also the stuff you don't really need to abstract.)

The point of transpiling is to allow you to pick a syntax independent of the engine. You still have to pick the engine and will deal with its peculiarities.

Today's post is about pipe syntax, which is syntax. Yesterday's was about trailing commas, which is syntax. I think there's an appetite for this.

One example is LookML, which used to build semantic data models in an analytic layer: https://cloud.google.com/looker/docs/what-is-lookml

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.

The creator of LookML is actually working on that with a OSS project called Malloy.

https://www.malloydata.dev/

>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

It's also not just that -- it depends on the data.

A database can change how it decides to execute a query based on whether a table has 100 rows or 10,000,000 rows.

Yeah that's true. Also, you, as the query writer, know how much the table has and will have and sometimes you decide what to do based on that information too.

We transpile from Python to SQL using Calcite. Calcite works well as an engine that knows enough SQL dialects to be practical.

+1 for Calcite. Any more details on what you’re doing? Are you using a python library on top of calcite? Something else?

We run Calcite in the backend. Python library sends DSL as json to the backend that returns SQL for the target engine.

It seems like this is solved by just having the transpiler aware of the target system.

This is nonsense and the person who was advocating for implementation-aware transpilation is completely wrong.

It is the role of a database engine implementation to service useful queries. Sometimes those queries are very complex; and yes, pipe syntax allows users to more easily write queries that can challenge database performance.

Yet this totally misses the point. Technologies like LookML have long allowed for extremely useful yet complicated queries to be executed by a database engine (often against one that is exceptional at complex analytical queries, like BigQuery) with high performance.

We should never handicap a SQL user’s ability to do what they want in the most ergonomic way. Transpilation largely allows for this — and I am 100% certain that implementations that allow for pipe syntax will effectively merely transform an AST with pipes to an existing kind of AST (this is, of course, effectively transpilation).

It is the job of the database engine, not the one querying (within reason) to performantly execute a query — and ditto for a pipe->non-pipe SQL transpiler. If you disagree with this, you are ignoring the fact that BigQuery, Snowflake, Redshift, and other optimized analytical databases are collectively worth billions, largely for their ability to performantly service crazy yet useful queries.

Reply on news.ycombinator.com