journal-sql

Overview

journal-sql is an interactive SQL shell (REPL) for querying the Ember journal. It translates SQL SELECT statements into JavaScript and executes them against the binary journal files using the same engine as journal-transform.

The tool is read-only: it scans the journal and prints matching rows to stdout without modifying any journal data.

Prerequisites

• Familiarize yourself with the Ember Trading Model. Field names in SQL queries map directly to message fields.
• The tool uses the Rhino JavaScript Engine internally. No JavaScript knowledge is required to use it, but understanding the generated scripts helps when editing them.

tip

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

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 SQL query ending with ; and press Enter:

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

The REPL will:

1. Translate the SQL to JavaScript and print the generated script.
2. Ask Apply this script to binary data? [y/n/e(dit)].
3. Stream results line-by-line to the terminal.
4. Print a (N rows) footer when done.

Each result row is printed as a JSON object:

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

REPL Commands

Command	Effect
<SQL>;	Compile and optionally run the query
\c	Clear the current input buffer
\h or help	Show help
\q, exit, quit	Exit the REPL

At the confirmation prompt:

Answer	Effect
y	Run the generated script against the journal
n	Discard and return to the prompt
e	Open the generated script in $EDITOR (fallback: vim) before running

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

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

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

Tables

SQL Table	Message type	Status
MESSAGES	deltix.ember.message.trade.TradeApiMessage	Supported
TRADES	deltix.ember.message.trade.OrderTradeEvent	Supported
ORDERS	—	Not supported yet

MESSAGES covers all trading API messages (requests and events). TRADES is a narrower view limited to trade report events.

Supported SQL Features

SELECT

Basic projections, filtering, and pagination are supported.

Column projections — list specific fields or use arithmetic expressions:

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

SELECT * — prints the full toString() of each message:

SELECT * FROM MESSAGES LIMIT 5;

DISTINCT — deduplicates output rows:

SELECT DISTINCT symbol FROM MESSAGES;

WHERE clause — supports:

• Logical operators: AND, OR, NOT
• Comparison operators: =, !=, <, >, <=, >=
• IN — checks membership in a list
• BETWEEN — inclusive range check
• LIKE — pattern matching with % as wildcard

LIMIT and OFFSET:

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

Field Types

The SQL compiler automatically handles Ember's encoding conventions:

Alphanumeric fields

Fields such as sourceId, destinationId, exchangeId, currency, commissionCurrency, counterPartySourceId, and parentSourceId are encoded as packed longs. The compiler wraps them with AlphanumericCodec transparently, so you can write plain strings in SQL:

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

Decimal64 fields

Price and quantity fields (limitPrice, avgExecutedPrice, tradePrice, quantity, tradeQuantity, leavesQuantity, filledQuantity, etc.) are stored as Decimal64-encoded longs. The compiler converts them to/from doubles automatically:

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;

Enum fields

Fields such as side, orderType, orderStatus, state, timeInForce, and tradeType are Java enums. Compare them as plain strings in SQL:

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

Limitations

warning

The following features are not supported:

• ORDERS table — not available yet.
• INSERT, UPDATE, DELETE — DML statements are not supported; use journal-transform for journal modifications.
• JOIN and UNION — multi-table and set operations are not supported.
• Aggregate functions — COUNT(), SUM(), MIN(), MAX(), AVG() are not supported.
• GROUP BY and ORDER BY — accepted by the grammar but ignored.
• Subqueries — not supported.

To modify the journal, use the journal-transform tool with a hand-written JavaScript transformation script.