Crux SQL

Crux SQL

Published
August 4, 2020
by
Jon Pither
Categories

We were pleased when Crux was included in the ThoughtWorks technology radar with a recommendation to assess:

Crux is an open-source document database with bitemporal graph queries…​ it’s currently in alpha and lacks SQL support, but you can use a Datalog query interface for reading and traversing relationships.

— ThoughtWorks Technology Radar

We are fans of Datalog and the power it gives. Although it’s not directly compatible and may drift out further, we’ve based our Datalog query API on Datomic’s, where Datalog is represented as a pattern-matching declarative data-structure.

Datalog is easy to construct and parse and it is natural to make use of custom predicates and rules as part of the logical pattern matching.

We love Datalog, but it’s also fair to say that a sizeable chunk of potential users will want to use SQL. Be it for users who want to run ad-hoc SQL queries without needing to know Datalog, or for integration between systems where SQL is the lingua franca, it’s important that we support it.

crux-sql

We have created a new crux-sql module in Crux which makes use of the Apache Calcite SQL query planning engine. Calcite is a powerful library for exposing SQL queries against arbitrary data-sources and has been integrated with a variety of DBMSs such as Cassandra, Mongo and Elastic, as well as Big Data systems like Hive, Drill, Flink and Dremio.

Calcite has been around for a long while and is still very active. We’re grateful for the work the Calcite team have put into this tool and the power it gives, as well as the friendly community that has built up around it.

We run Calcite in-process as part of a Crux node, so there’s no need to set up any additional infrastructure. Rather you just need to add the crux-sql module dependency:

[juxt/crux-sql "RELEASE"]]

We have integrated Calcite such that SQL queries are translated to efficient Datalog queries, including sorts and inner joins.

You can avoid the relatively small overhead of preparing queries by using PreparedStatements.

Please visit the module README for how to get going with some simple steps, or please watch this video:

The crux-sql module allows for both in-process SQL queries and for remote JDBC queries using Avatica. This is covered in the docs.

Next Up

See the crux-sql README for more details.

Stay tuned for another blog and video on how to write Crux SQL bitemporal queries.

As ever, if you have queries about Crux, please ping the team:

Image credit: Oliver Hine