journal-sql

Overview

journal-sql is an interactive SQL shell (REPL) and batch CLI for querying and transforming the Ember journal. It compiles SQL into JavaScript, then runs that script against the binary journal with the Rhino engine — the same family of machinery as journal-transform.

• Read-only SELECT scans the journal and prints JSON lines to stdout without modifying any journal data.
• Mutating INSERT / UPDATE / DELETE rewrite the journal, similar to journal-transform.

You do not need to write JavaScript for normal use. The REPL always shows the generated script before applying it, and you can edit that script when needed.

Prerequisites

• Familiarize yourself with the Ember Trading Model. SQL column names map directly to message fields.
• Enough free disk space for a full journal rewrite when running mutating statements.

tip

Stop Ember's main process and other journal tools before running journal-sql, as it acquires an exclusive lock on the journal.

caution

Mutating statements change the journal the same way journal-transform does. Fence files are not preserved across a rewrite — copy them from the backup if Drop Copy or data warehouse depends on them. See journal-transform.

Quick Start

Launch the interactive shell:

export EMBER_HOME=/deltix/emberhome
export EMBER_WORK=/deltix/emberwork
/deltix/ember/journal-sql

After startup you will see the jsql> prompt:

+--------------------------------------+
|         Journal SQL REPL             |
|  Type SQL ending with ';' to begin   |
|  Type \h for help, \q to quit        |
+--------------------------------------+

jsql>

Type a statement ending with ; and press Enter:

jsql> SELECT orderId, symbol, side FROM MESSAGES WHERE side = 'SELL' LIMIT 5;

The REPL will:

1. Compile SQL to JavaScript and print the generated script.
2. Ask for confirmation ([y/n/e(dit)] for reads; mutating statements require typing yes).
3. Stream result rows (or rewrite the journal for DML).
4. Print a (N rows) / (N rows affected) footer.

Each SELECT row is one JSON object:

{"orderId":"1719432399330034","symbol":"BTC/USD","side":"SELL"}
{"orderId":"1719432399330035","symbol":"ETH/USD","side":"SELL"}
(2 rows)

Non-interactive mode

journal-sql -e "SELECT COUNT(*) FROM TRADES;"
journal-sql -f script.sql

-f accepts multiple statements separated by ;. There is no interactive confirmation in batch mode — statements run immediately (including DML).

REPL Commands

Command	Effect
<SQL>;	Compile and optionally run the statement
\c	Clear the current input buffer
\timing	Toggle wall-clock timing for each statement
\h or help	Show help
\q, exit, quit	Exit the REPL

At the confirmation prompt:

Answer	Effect
y / yes	Apply (INSERT/UPDATE/DELETE require the full word yes)
n	Discard and return to the prompt
e	Open the generated JS in $EDITOR (default vim), then confirm again

Multi-line queries are supported. The REPL waits for a ; before processing:

jsql> SELECT orderId, symbol, side
  ->  FROM MESSAGES
  ->  WHERE side = 'SELL'
  ->  LIMIT 5;

note

Trailing text after the statement semicolon (;) is ignored by the parser. Only the first complete statement in the buffer is compiled.

Press Ctrl+C to clear the current input buffer. Press Ctrl+D to exit.

Tab Completion

The completer suggests SQL keywords, table names, and column names.

note

The current completer is not context-aware and is case-sensitive. It does not know whether cursor is in a FROM clause or in a column list.

Tables

SQL table	Row type guard	DML	Status
MESSAGES	TradeApiMessage	SELECT, INSERT, UPDATE, DELETE	Supported
TRADES	OrderTradeEvent	SELECT, INSERT, UPDATE, DELETE	Supported
ORDERS	(order snapshot, two-pass scan)	—	Not supported yet

MESSAGES is the full trading API stream (requests and events). TRADES is the subset of messages that are trade events.

ORDERS is registered for a future materialized order view (multi-pass scan over the journal). Selecting from it currently fails with “not supported yet”.

Supported SQL

SELECT

Projections — columns, aliases, and arithmetic:

SELECT orderId, symbol, side FROM MESSAGES LIMIT 10;
SELECT quantity * 2 AS qty2, limitPrice - 0.5 FROM MESSAGES WHERE orderId = '1719432399330034';

SELECT * — prints all columns supported by message type:

SELECT * FROM TRADES LIMIT 1;

DISTINCT, WHERE, LIMIT / OFFSET:

SELECT DISTINCT symbol FROM MESSAGES;
SELECT orderId FROM MESSAGES WHERE side = 'SELL' LIMIT 10 OFFSET 5;

WHERE supports AND / OR / NOT, comparisons, IN, BETWEEN, LIKE (% wildcard), and IS [NOT] NULL.

Fields that are unset on a given message subtype (for example tradeQuantity on a non-trade request) evaluate as SQL NULL. Comparisons against NULL do not match.

Aggregates — COUNT(*), COUNT(col), SUM, MIN, MAX, AVG:

SELECT COUNT(*) FROM MESSAGES WHERE side = 'SELL';
SELECT AVG(tradePrice) AS averageTradePrice FROM TRADES;
SELECT SUM(tradeQuantity) FROM TRADES;

GROUP BY — projected group columns appear as flat top-level JSON keys alongside aggregates:

SELECT symbol, COUNT(*) FROM TRADES GROUP BY symbol;

{"symbol":"BTCUSD","COUNT(*)":7}

ORDER BY … LIMIT — implemented with a bounded top-N heap (requires LIMIT):

SELECT orderId FROM MESSAGES WHERE side = 'SELL' ORDER BY orderId LIMIT 3;

INSERT / UPDATE / DELETE

INSERT INTO MESSAGES (type, symbol, side, quantity, orderId, orderType, timeInForce)
VALUES ('OrderNewRequest', 'BTCUSD', 'BUY', 1.0, 'oid-1', 'LIMIT', 'DAY');

UPDATE MESSAGES SET limitPrice = 1.5 WHERE orderId = 'oid-1';

DELETE FROM MESSAGES WHERE orderId = 'oid-1';

INSERT INTO TRADES requires a type name that is an OrderTradeEvent subtype. INSERT … SELECT is supported with the same column list rules (no DISTINCT / LIMIT on the source select).

Field types

The compiler coerces Ember encodings so SQL literals look natural:

Alphanumeric

sourceId, destinationId, exchangeId, currency, and similar packed-long fields compare and project as strings:

SELECT orderId, sourceId, destinationId FROM MESSAGES WHERE sourceId = 'S6';

Decimal64

Prices and quantities (limitPrice, quantity, tradePrice, tradeQuantity, cumulativeQuantity, …):

SELECT orderId, quantity FROM MESSAGES WHERE quantity > 0.1 AND quantity <= 0.2 LIMIT 5;
SELECT orderId, tradeQuantity FROM TRADES WHERE tradeQuantity BETWEEN 0.05 AND 0.1;

Timestamp

@Timestamp fields (timestampNs, originalTimestampNs, expireTime, …) keep full 64-bit precision in projections. The sentinel TypeConstants.TIMESTAMP_NULL is treated as SQL / JSON null.

Enum

side, orderType, orderStatus, timeInForce, tradeType, … — compare as enum constant names:

SELECT orderId, side FROM MESSAGES WHERE side = 'BUY' LIMIT 3;
SELECT orderId, orderStatus FROM TRADES WHERE orderStatus IN ('PARTIALLY_FILLED', 'COMPLETELY_FILLED');

Integer

Plain integral fields (sequence, sequenceNumber, …) stay as Java longs in generated JS.

Caps and system properties

Property	Default	Meaning
journal.sql.distinct.cap	1000000	Max distinct keys
journal.sql.groupby.cap	1000000	Max GROUP BY groups

Exceeding a cap fails the statement with an error rather than growing without bound.

Generated JavaScript

SQL is always compiled to a Rhino script. Understanding the bindings helps when you choose edit or when debugging a failure.

Engine bindings

Name	Type	Role
sink	RowSink	SELECT output. Call sink.emit(jsonString); returns false when LIMIT is exhausted so the scan can stop.
agg	AggregateSink	Present for aggregate queries. acceptRow(...) / acceptGroupRow(groupValues, ...); the runtime calls finish() after the scan.
topN	TopNSink	Present for ORDER BY … LIMIT. Offers rows during the scan; finish() drains in order into sink.
writer	journal writer channel	Mutating statements. Accept rewritten / inserted messages.
rowsAffected	number	Updated by generated DML / used in footers.
skippedRows	number	UPDATE rows that matched the WHERE but had no applicable setter on that message type.
setRows	RowSink.DistinctSet	Present for SELECT DISTINCT.

Helper packages commonly appear in generated code:

• Packages.com.epam.deltix.dfp.Decimal64Utils
• Packages.deltix.anvil.util.codec.AlphanumericCodec
• Packages.deltix.anvil.util.TypeConstants
• Packages.deltix.ember.journal.tf.sql.JsRuntime (longEquals)
• Packages.deltix.ember.journal.tf.sql.MessageFieldAssigner (DML setters)
• Packages.deltix.ember.journal.tf.sql.RowFormatter (SELECT *)

Limitations

warning

Not supported:

• ORDERS table — disabled.
• JOIN / UNION / subqueries
• HAVING
• Window functions
• INSERT … SELECT *