[juxt/crux-sql "RELEASE"]]Crux SQL
Jon Pither
Jon Pither is a development team leader by trade having led projects for JUXT at multiple Tier-1 Investment Banks, an online newspaper website, a major property portal, and an international public electric bikes scheme. Jon is a co-founder of JUXT.
jon@juxt.pro
Click to view full profile|
Crux is now XTDB
September 2021 Update: Crux has recently been renamed XTDB. This post still refers to XTDB as "Crux" but you will find newer versions of all the components and features mentioned in the latest version of XTDB. A completely updated version of this post can be found at https://xtdb.com/blog/xtdb-sql.html. The official home for XTDB is now https://xtdb.com. |
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:
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
