SQL — juxt/crux-test 20.09-1.11.0

SQL

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:

[juxt/crux-sql "RELEASE"]

Then add the calcite module to your topology:

{:crux.node/topology ['crux.kafka/topology 'crux.calcite/module]}

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 TRANSACTION TIME also for query consistency:

VALIDTIME ('2016-12-01T10:13:30Z')
TRANSACTIONTIME ('2016-12-01T10:13:30Z')
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')

Connecting

If you want to connect in-process, you can do so with the above method of using crux.calcite/jdbc-connection to establish a java.sql.Connection.

You can also use Apache Avatica to connect on-the-wire. Add to the Avatica SQL driver to your project and use the connection string:

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

server-hostname is the hostname of the Crux node.

The port by default is 1501 but you can swap it out by configuring the :crux.calcite/port Crux configuration property, i.e.:

{:crux.node/topology ['crux.kafka/topology 'crux.calcite/module]
 :crux.calcite/port 1502
...}

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

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

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" -d org.apache.calcite.avatica.remote.Driver

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.