Skip to content

Tuesday, 04 August, 2020

XTDB SQL

Jon Pither

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

XTDB 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

(Note that, at the time the ThoughtWorks Technology Radar was written, XTDB was still known as "Crux".)

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.

xtdb-sql

We have created a new xtdb-sql module in XTDB 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 an XTDB node, so there’s no need to set up any additional infrastructure. Rather you just need to add the xtdb-sql module dependency:

[com.xtdb/xtdb-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:

(Because of the immutable nature of YouTube videos, you’ll note XTDB is still referred to as "Crux" in the video and the title.)

The xtdb-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 xtdb-sql README for more details.

Stay tuned for another post and video on how to write XTDB SQL bitemporal queries.

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

Discussions

Hacker News | 181 points | 35 comments

Image credit: Oliver Hine