My other database is a compiler | Hacker News Reader

My other database is a compiler

blog.chiselstrike.com

27 pointsalainchabat13 comments

A long held view of mine is that our current architecture where we keep logic on the server and data in the database is rather unfortunate, because it means you either have to

* marshall data to your server to process (which can be slow), or

* marshall code to your db to run (which can be messy, in part because you're translating from one programming language to your db's language - this is the main reason ORMs exist)

I always thought that the solution is to have all computation live in the DB (similar to how kdb does it), but this is pretty nice too. If you squint your eyes this looks like a much cleverer ORM.

It would be even better if the language supported SQL as a first class citizen, but that's not really an option here, since you're stuck with js.

fyi kdb+ now supports ANSI-SQL. https://code.kx.com/insights/1.1/core/sql.html

Including support for the PostgreSQL wire protocol https://code.kx.com/insights/1.1/core/pgwire.html

KDB+ has always supported SQL92 I thought (minus the DDL stuff). I've seen and edited Arthur's files (and blind now). Much of '99 was there too when k had those alternate syntax libraries just translated down to k (may still be that way). How is this different?

When FD picked up Kx, I was kind of hoping for better admin tooling than support for poor query languages that you shouldn't even be writing in. I know it is there as a necessity to help move code to kdb, but the more sql the less kdb shines. Its strange to push something that makes the product inferior. sql is just a really bad fit for a column db.

The implementation is similar to before where the sql support is loaded as a library that translates to k - however it goes a few steps further by supporting (not an exhaustive list):

- Intermingling q and sql in the same expression. e.g SELECT * from qt('marketSnap`IBM')

- extensive joins, aggregates, sub-selects, cte, parameters etc - whereas the SQL92/'99 support was v. limited

- Distributed support for executing/joining across multiple targets - allowing a gateway sql query to run against in-memory (rdb) and on-disk (idb/hdb) databases. We are also testing support for querying external databases through 'virtual' tables (as if the data was stored natively in kdb+). The sql support will never replace Q APIs but we're really keen to see what our users will try to do with it. e.g single-pane governance/data mesh patterns

- PostgreSQL dialect and wire support to allow sql/no-code users to connect from 3rd party tools using the ootb PostgreSQL connectors (tableau/powerbi/glue etc) without loading and maintaining special odbc/jdbc drivers for kdb+. Queries generated via tools will push down aggregates/predicates to execute directly against kdb+ (versus the legacy import model)

Specifics aside, I hear all your points loud and clear - and agree with sql being inferior to q-sql (opinions are my own!) - but the intention is not to replace q-sql here. We're simply opening/democratising the technology for less advanced users by enabling new options for them to get more value on day 1 without the typical on-ramp usually required to start using/writing analytics in KX.

With the ultimate aim being that they see huge performance benefits when using KX (even via SQL) vs. other databases with minimal changes to their applications.

From there we hope they will begin to use more q-sql and realise how much easier and succinct it is to express questions against their time-series data e.g window functions, order and temporal based queries.

For some colour on the topic, SQL is by far the biggest request we get from new users (especially outside finance) - so this will help us grow the community and create more opportunity for careers in new sectors for kdb+ developers.

p.s We've invested heavily in better tooling for kdb+ this past 2 years which you may not have seen but I'd mostly love to discuss your ideas/requests and share with Product Strategy who I spend a lot of time working with.

Isn't this what an ORM does? You write "User.findOne(user => user.name == "Glauber Costa" && user.age >= 40);" and it will be translated into SQL. Maybe I'm missing something here, but I don't see the innovation.

Remember, that argument to `findOne()` is an arrow function. You need a compiler to turn it into a efficient, deferred query; otherwise you need to _evaluate_ the function at runtime.

This is the disconnect I've found in my self-taught compiler education journey. Every time I try to tackle this topic, I get bogged down by parsers and lexers. I already have that structure.

Users.find(x => x.name == "Bob").

My programming language has done that for me. What's the next phase? What data structures do I need to maintain to construct these DSLs? How do I optimize them?

I can fight my way through this but I wish I just had the theory and making this kind of stuff easier. It just comes up so often in the problems that I solve and I'd like to reach for this tool more often.

Perhaps you are looking for macros? Rust had a substantial macro system while being low-level enough to fit into most architectures. I haven’t implemented a macro but I’ve used many. Have you looked there?

As I was writing my comment, I think maybe I should start with DSL, but this isn't conclusive. Spark, Scalding, LINQ, RxJava, even Slick are examples of the types of DSLs I'd like to understand how to construct. They're essentially compilers.

Looking at the code for these technologies it's incredible how complex it is to implement the nice DSL that they expose. How much of that is accidental complexity?

So the innovation here is instead of doing "User.findOne({name: "Glauber Costa", age: {superiorOrEqual: 40}});" you do what's in my previous comment?

newer postgresql also jit's some queries in its new query optimizer.

but for such use-cases (exact matches, no range queries) it's still better to compile a perfect hash at run-time into a .so/.dll and load this at run-time. way faster.

Interesting. Aligns with something Martin Odersky(creator of Scala) said a while ago

I find it said that query plans an SQL database generated were not discussed more. Drawing this comparison and highlighting differences would have been very helpful.

Reply on news.ycombinator.com