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>pro.juxt.crux</groupId>
    <artifactId>crux-sql</artifactId>
    <version>1.18.0</version>
</dependency>

pro.juxt.crux/crux-sql {:mvn/version "1.18.0"}

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.

Reference

SQL