Liking cljdoc? Tell your friends :D

SQL

SQL adapter for XTDB making use of Apache Calcite.

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
<dependency>
    <groupId>com.xtdb</groupId>
    <artifactId>xtdb-sql</artifactId>
    <version>{xtdb_version}</version>
</dependency>
deps.edn
com.xtdb/xtdb-sql {:mvn/version "{xtdb_version}"}

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
{
  ...
  "xtdb.calcite/server": {
    "port": 1501
  }
}
Clojure
{...
 :xtdb.calcite/server {:port 1501}}
EDN
{...
 :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

Table Definitions

To define a table, transact a document into XTDB:

{:xt/id :xtdb.sql/person-schema
 :xtdb.sql/table-name "person"
 :xtdb.sql/table-query '{:find [id name homeworld]
                         :where [[id :name name]
                                 [id :homeworld homeworld]]}
 :xtdb.sql/table-columns '{id :keyword, name :varchar, homeworld :varchar}}

A SQL table is a schema that maps XTDB attributes to SQL table columns. Any document in XTDB that matches the :xtdb.sql/table-query in the schema document is eligible to be returned via the SQL query.

SQL table column names are mapped from the symbols used by the :xtdb.sql/table-query query backing the table and referred to by :xtdb.sql/table-columns.

Note that in the case where symbols are prefixed with ?, then ? is stripped for the SQL column name.

For example with the following XTDB transaction operation:

[::xt/put {:xt/id :ivan :name "Ivan" :homeworld "Earth"}]

Get a connection and query as such:

(require '[xtdb.calcite])

(defn query [node q]
  (with-open [conn (xtdb.calcite/jdbc-connection node)]
    (let [stmt (.createStatement conn)]
      (->> q (.executeQuery stmt) resultset-seq))))

So that:

(query "SELECT PERSON.NAME FROM PERSON")

Returns:

[{:name "Ivan"}]

Note that using JDBC PreparedStatements for queries will be faster.

Bitemporal Querying

To query using VALID TIME:

VALIDTIME ('2016-12-01T10:13:30Z')
SELECT * FROM PERSON

Use TRANSACTIONTIME/TRANSACTIONID also for query consistency:

VALIDTIME ('2016-12-01T10:13:30Z')
TRANSACTIONTIME ('2016-12-01T10:13:30Z')
TRANSACTIONID (4)
SELECT * FROM PERSON

Both VALIDTIME and TRANSACTIONTIME take an RFC 3339-like timestamp string, which is compatible with ISO-8601.

Examples of RFC 3339-like supported syntax:

VALIDTIME ('2016-12-01')
VALIDTIME ('2016')

Column Types

We support a subset of java.sql.Types:

  • :bigint

  • :boolean

  • :double

  • `:decimal

  • :float

  • :timestamp

  • :varchar

  • :keyword

  • :uuid

Note that bigint maps to Long.

Keyword value are returned as Strings in results. If you need to filter against a keyword column, then you can use the KEYWORD SQL function, for example:

SELECT ID,NAME FROM PERSON WHERE ID = KEYWORD('human/ivan')

Same for UUID:

SELECT NAME FROM PERSON WHERE AUUID = UUID('e7ae4200-d619-4c20-9d64-87d1f90d0fd2')

Note that currently we do not support UUIDs and Keywords being set inside of prepared statements.

Current Limitations

  • We support a range of calculations (ceil, lower, upper, concat), but we do not support all.

  • Projections, filters and inner-joins are handled by XTDB. Left outer joins and aggregations are handled by Calcite in memory.

❮AWS S3Transactions❯

Can you improve this documentation? These fine people already did:
James Henderson, Daniel Mason & Dan MasonEdit on GitHub

cljdoc is a website building & hosting documentation for Clojure/Script libraries

Keyboard shortcutsReport a problemcljdoc on GitHub
× close