Crux SQL

by yogthos • 6 years ago

181 28 6 years ago

Does this mean I can use tools like https://github.com/metabase/metabase with crux? Awesome if so!

I don't believe Metabase supports the Calcite SQL dialect (https://github.com/metabase/metabase/issues/6230), which is what Crux is using for the SQL layer. So I believe the answer is no - but I'm not an expert here, so don't take this answer as definitive.

refset7• 6 years ago

As it happens there was a comment on a related issue about Dremio support [0] (which also uses Calcite) where someone shared that they got a Dremio driver working. I was able to fork their driver and get Metabase working against Crux in time for a live demo of the crux-sql module back in May [1].

I just switched the forked driver repo to public if anyone wants to test it out [2]. The Metabase driver docs are pretty straightforward to get things running. There's definitely work to be done though and I didn't get very deep into it really - but I hope to pick it up again soon!

[0] https://github.com/metabase/metabase/issues/5562

[1] https://youtu.be/StXLmWvb5Xs?t=996

[2] https://github.com/crux-labs/crux-metabase-driver

(I work on Crux :)

EDIT: it's also worth a mention that the linked issue #6230 discusses a lot of problems deriving from Druid's lack of support for prepared statements, but Crux and Dremio don't have that limitation. Although reading the most recent comment it looks like Druid may have overcome that hurdle now, so with a bit of luck there might now be more traction to get mainline Metabase support for a generic Calcite driver!

tsm• 6 years ago

You can write your own driver for Metabase without _too_ much hassle: https://github.com/metabase/metabase/wiki/Writing-A-Driver

reilly3000• 6 years ago

Its worth noting that Datomic has an analytics adapter which supports SQL and Metabase is of course a first-class citizen:

https://docs.datomic.com/cloud/analytics/analytics-concepts....

wry_discontent• 6 years ago

I learned datalog through Crux and I really enjoyed it. After not very long, it was more intuitive and easier to reason about that similar sql.

slifin• 6 years ago

I'm trying to learn datalog casually, there's lots of queries that I still don't know how they translate

I.e. group by, window aggregates, order by, limit

I'd be interested in seeing the equivalent datalog that Crux makes given some of those statements for learning what efficient datalog looks like for some of those problems

refset7• 6 years ago

You always have the option to wire together multiple Datalog queries using Clojure/JVM-lang-of-choice (the N+1 query problem doesn't affect Crux when embedded), but we are currently analysing the possibilities for a higher-level query API, potentially in a similar vein to Mongo's pipelines [0], where a sequence of "stages" is defined. In the ideal case there would be _closure_ in the intermediate steps of a pipeline such that the output to any given stage (including Datalog queries) is a fully-fledged graph in its own right.

As for your specific examples, Crux already implements an order-by+limit+offset combination that automatically spills to disk if needed, but for efficient pagination you would probably want to maintain additional pre-sorted value ranges. For basic aggregation we have an alpha API decorator that composes Clojure transducers to great effect [1].

[0] https://docs.mongodb.com/manual/reference/operator/aggregati...

[1] https://github.com/crux-labs/crux-decorators/blob/master/tes...

fnordsensei• 6 years ago

I highly recommend http://www.learndatalogtoday.org/ as a learning resource.

I also enjoyed this talk: https://youtu.be/oo-7mN9WXTw though it’s not so much about syntax as the logic of it.

LambdaComplex• 6 years ago

Any relation to the CRUX Linux distro? Or is this just an unfortunate overlap of names?

0xdeadb00f• 6 years ago

Doesn't look like there's any relation.

Off-topic, but I quite liked CRUX linux when I did use it. It introduced me to BSD-style init scripts

lukashrb• 6 years ago

Anyone with experience has some detailed info about the limitation, trade-offs and problems of using Datalog instead of SQL? Datalog seems like a natural choice as declarative query language but has not yet become mainstream. I wonder why?

lgessler3• 6 years ago

Using Crux's Datalog query language from Clojure is great. Normally if your query building library is giving you a data-like interface, then you're relying on some abstraction that will inevitably leak in places (and let's not talk about ORMs), but since Crux really does natively use EDN (Clojure's data syntax, roughly analogous to JSON) for its query language, you've eliminated an entire layer of complexity in your application, and you also have the entire programming language at your disposal for compositionally building queries.

That's not to say that using the SQL API is a bad idea, but if you're using Crux from Clojure, you'd be missing out on some stuff.

jonahbenton• 6 years ago

Short answer is that Datalog is originally a logic/proof language, while SQL is an arithmetic / statistical / tabular aggregation language.

One can make one solve problems of the other but they're really different mental models, which impacts the representation of the data needed to enable their use.

For Datalog one needs relationships- a graph- not what SQL calls relations, which is just data. Datalog requires a richer set of opinions- at least conceptually- about the data.

Languages like Datalog will not become mainstream until graph modeling is mainstream.

liminal• 6 years ago

This looks really interesting - especially the temporal aspects. Does anyone have any insight into using it with RDF/SPARQL data/queries?

lgessler3• 6 years ago

From their docs[0]:

The REST API also provides an experimental endpoint for SPARQL 1.1 Protocol queries under /sparql/, rewriting the query into the Crux Datalog dialect. Only a small subset of SPARQL is supported and no other RDF features are available.

[0] https://opencrux.com/docs

refset7• 6 years ago

The context here is that we benchmark ourselves against the likes of Neo4j and RDF4J using SPARQL test suites, in particular WatDiv [0] and LUBM [1] which are specifically designed for stress testing various subgraph matching queries [2]. Therefore we had to translate the data sets and queries from RDF/SPARQL to edn/Datalog etc.

There is an open issue in regards more general RDF support with details on the kinds of things Crux would need to add: https://github.com/juxt/crux/issues/317

[0] https://dsg.uwaterloo.ca/watdiv/

[1] http://swat.cse.lehigh.edu/projects/lubm/

[2] https://en.wikipedia.org/wiki/Subgraph_isomorphism_problem

krick2• 6 years ago

Oh dear, I feel like there is some gap between the set of technologies I'm aware of and all this stuff. First of, can somebody (preferably not involved with crux) tell me, if I'm actually should be aware of crux? Is it "yet another Cassandra"? Is it preferable in any way to Cassandra (or whatever "yet another of" it is)?

refset7• 6 years ago

(I am involved with Crux - sorry!)

I see 4 main reasons why someone may want to be aware of Crux:

- if you have a bitemporal problem

- if you have a graph problem, i.e. something you might initially look to Neo4j to help with

- if you want to use Datalog because it can make writing an application simpler

- if you are thinking of building something similar (immutable event log + indexes) and want to save time

Crux is very different from Cassandra (strong consistency, fat nodes, arbitrary joins etc.), but you could definitely use Cassandra in your Crux architecture.

The closest "yet another" comparison would be Datomic. Crux and Datomic both strive to reimagine what a "general purpose" DBMS should look like, with the primary goal being developer productivity/sanity, whereas Cassandra's goal is simply to be a highly-scalable document store.

Hope that helps!

krick2• 6 years ago

Thank you, it explains a lot. However, I'm struggling to imagine from the top of my head a problem, where I'd need a DB that is both bitemporal and graph-based, while keeping in mind that both conditions are relatively "expensive" compared to what more common relational design could offer — so I really better have a good reason to chose something exotic like that! Do you perhaps have some example?

amgreg• 6 years ago

Open source vs closed source aside, why would one use Crux instead of Datomic?

ledgerdev2• 6 years ago

What's the hosting story for crux if one wanted to try it out on a side project?

refset7• 6 years ago

:) well I already answered your same question on r/Clojure but here's a link for others: https://www.reddit.com/r/Clojure/comments/i3gzxy/crux_sql/g0...

ledgerdev2• 6 years ago

Thanks!

Crux SQL

Related Stories