Thread by @ghalimi

Here is how #STOIC intends to use this feature. Obviously, there are many applications for a native SQL parser/serializer, but this particular one might give you some ideas for your own projects.

https://twitter.com/ghalimi/status/1615028240615370757

Here is a screenshot of our current UI. For us, SELECT is a transform that can be used in a data journey to generate a new table, either directly from an import, or downstream of other transforms.

Right now, the parameters of this transform (the clauses of the SELECT statement) must be coded by hand. This is fine if you're a SQL expert, but this is a non-starter if you're a casual Excel user. We want our product to be usable by both. The latter needs something more.

What we want is the UI to automatically generate the clauses of our SELECT statement, through UI components like this column menu. This thread explains it in more detail:

https://twitter.com/ghalimi/status/1614714449444876288

Our goal is simple, yet very ambitious: we want a casual user who received less than one hour of training to be able to produce all 22 TPC-H queries through a point-and-click interface, with minimalist use of simple Excel-like expressions.

To do so, our UI must produce the SQL clauses, and that's relatively straightforward, at least if you approach the problem the right way. The problem then becomes the following: how do you serialize the UI's output in some document (a workbook) that can be reused later on?

There, you basically have two options: either you invent your own grammar, or you embrace SQL. We've decided to use plain SQL, for several reasons:

1. It's the only industry standard.
2. It's easy to edit for SQL developers.
3. It ensures that we don't re-invent the wheel.

The second point is critical: we want the SQL code produced by our UI to be editable by SQL developers, and we want our UI to be able to import existing SQL statements. Therefore, SQL is the way to go.

Now, there is one big problem with that: which dialect of SQL should we use. SQL is the industry standard, but it comes in many different flavor (one per database engine essentially). Therefore, we need to pick one.

While we offer direct connection to multiple cloud databases (@SnowflakeDB, @DataBricks), @DuckDB is our embedded engine, which we use both server-side (using Node.js) and client-side (using WASM). Therefore, it makes sense to use @DuckDB's SQL dialect.

But if we go down that path, we want to remain as faithful to the dialect as possible, and there is no better way of doing that than having @DuckDB itself do the parsing and serializing for us. Hence the need for this particular feature.

With that approach, the SELECT transforms used in data journeys of our #STOIC workbook will be serialized in plain SQL (actually, a simple JSON with one string per root clause). From there, we will use @DuckDB to generate an AST for it.

When users will use these column menus, the AST will be updated interactively, and we will use @DuckDB's serializer to convert it back into a SQL query that will be executed on our embedded DuckDB engine.

But if the query has to be executed on another database (@SnowflakeDB, @DataBricks), we will simply use a different serializer (writing a SQL serializer is a lot easier than writing a SQL dialect translator).

Having the parser available client-side (through @DuckDB WASM) will also be useful when we let SQL developers modify the SQL clauses of SELECT transforms directly: this will allow us to feed our auto-completion engine with highly-contextual suggestions.

To make this UI as interactive as possible, we will automatically materialize and denormalize all tables of the FROM clause in memory (using a CREATE TABLE). As long as the data is smaller than 2 TB, this will work just fine.

We will then encourage users to build queries through our column menus in this order:

1. Filter the data as aggressively as you can
2. Set your GROUP BY column (if any)
3. Add your columns (feeding the SELECT clause)
4. Define your inner queries (recursively)
5. Sort rows

The resulting SQL query will be re-executed at every interaction, giving users real-time feedback on their work. And if they start becoming slow, they can always configure some sampling options that will make them a lot faster.

Now, here is a very important detail: since we're producing all this SQL ourselves from basic user interactions, we can hope to understand what's going on, and we can feed this knowledge to our own distributed query planner.

Therefore, the SQL query that you will see in the SELECT transform isn't necessarily the actual SQL query that will be executed by @DuckDB running on our EC2 monostore (single VM).

Instead, it's an abstract query that might be broken down into multiple queries. The main query will run on the monostore, but some upstream queries might run on a fleet of Lambda functions (especially when adding more tables to the FROM clause).

Similarly, correlated inner queries might also be deployed on a fleet of Lambda functions, themselves interacting with an EC2 cluster holding large partitions of the denormalized table used in the inner queries.

Bottomline: not only will this UI help SQL-illiterate users implement all TPC-H queries, it will allow them to distribute them on large fleets of Lambda functions and large clusters of EC2 VMs, automatically.