SQL

SQL module for XTDB built on Apache Calcite.

This document describes setup and configuration for the SQL module. To query using the SQL module, see the SQL Query Reference.

Setup

xtdb-sql runs in-process as part of the XTDB node as an XTDB module.

First, add the xtdb-sql dependency to your project:

  • pom.xml

  • deps.edn

<dependency>
    <groupId>com.xtdb</groupId>
    <artifactId>xtdb-sql</artifactId>
    <version>1.24.3</version>
</dependency>
com.xtdb/xtdb-sql {:mvn/version "1.24.3"}

Connecting

You can either obtain a JDBC connection in-process, or start a server port and connect through a number of external SQL tools.

To get a connection in-process, call xtdb.calcite/jdbc-connection, passing it an XTDB node:

(require '[xtdb.calcite])

(with-open [conn (xtdb.calcite/jdbc-connection node)]
  ;; ...
  )

SQL server

We use Apache Avatica to open up a SQL server port.

Add the following to your node configuration:

  • JSON

  • Clojure

  • EDN

{
  ...
  "xtdb.calcite/server": {
    "port": 1501
  }
}
{...
 :xtdb.calcite/server {:port 1501}}
{...
 :xtdb.calcite/server {:port 1501}}

Properties

  • port (int, default 1501)

  • scan-only? (boolean, default false): only use table scans in queries

Connecting via JDBC

Add to the Avatica SQL driver to your project and use the connection string:

jdbc:avatica:remote:url=http://<server-hostname>:1501;serialization=protobuf;timeZone=UTC

You can then connect using the Avatica JDBC driver, for example:

(java.sql.DriverManager/getConnection "jdbc:avatica:remote:url=http://localhost:1501;serialization=protobuf;timeZone=UTC")

Connecting via SQLLine

To connect via the SQLLine CLI tool, install Coursier and run:

coursier launch sqlline:sqlline:1.9.0 org.apache.calcite.avatica:avatica-core:1.16.0 -M sqlline.SqlLine -- -n xtdb -p xtdb -u "jdbc:avatica:remote:url=http://localhost:1501;serialization=protobuf;timeZone=UTC" -d org.apache.calcite.avatica.remote.Driver

SQL Query Reference

See the SQL Query Reference.