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.
(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