Crux is becoming XTDB!

You can read the rename announcement, or head to this page on the new XTDB site (where you'll be taken in 10 seconds).

Menu

SQL

SQL adapter for Crux making use of Apache Calcite.

Setup

crux-sql runs in-process as part of the Crux node as a Crux module.

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

  • pom.xml

  • deps.edn

<dependency>
    <groupId>juxt</groupId>
    <artifactId>crux-sql</artifactId>
    <version>20.12-1.13.0-alpha</version>
</dependency>
juxt/crux-sql {:mvn/version "20.12-1.13.0-alpha"}

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 crux.calcite/jdbc-connection, passing it a Crux node:

(require '[crux.calcite])

(with-open [conn (crux.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

{
  ...
  "crux.calcite/server": {
    "port": 1501
  }
}
{...
 :crux.calcite/server {:port 1501}}
{...
 :crux.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 crux -p crux -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 Crux:

{:crux.db/id :crux.sql.schema/person
 :crux.sql.table/name "person"
 :crux.sql.table/query '{:find [id name homeworld]
                         :where [[id :name name]
                                 [id :homeworld homeworld]]}
 :crux.sql.table/columns '{id :keyword name :varchar homeworld :varchar}}

A SQL table is a schema that maps Crux attributes to SQL table columns. Any document in Crux that matches the crux.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 :crux.sql.table/query query backing the table and referred to by :crux.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 Crux transaction operation:

[:crux.tx/put {:crux.db/id :ivan :name "Ivan" :homeworld "Earth"}]

Get a connection and query as such:

(require '[crux.calcite])

(defn query [node q]
  (with-open [conn (crux.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 Crux. Left outer joins and aggregations are handled by Calcite in memory.