Historical Database

Querying Historical Tables

Historical tables are queried with the same AMI SQL used against realtime tables, with a defined set of rules covering joins, indexes, and predicate forms.

Overview

A historical table answers the same SELECT statements as a realtime table, in the same Center, and can be read alongside live data. A query filtered on a partition column reads only the partitions that can match, so its cost tracks the data it needs rather than the size of the table:

SELECT sym, qty FROM Trades WHERE tradeDate==20260101;

See Partitioning for how that elimination works. The differences from a realtime table are confined to three areas, each covered below: how two historical tables join, how indexes behave, and which predicate forms are accepted.

Joins

Two historical tables can be joined. Both operands are ordinary historical tables, created as in Creating a Historical Table:

CREATE PUBLIC TABLE Trades (
    tradeDate LONG PARTITION,
    sym       STRING,
    qty       LONG
) USE PersistEngine="HISTORICAL";

CREATE PUBLIC TABLE Fills (
    fillDate LONG PARTITION,
    sym      STRING,
    qty      LONG
) USE PersistEngine="HISTORICAL";

INSERT INTO Trades VALUES (20260101,'IBM',100);
INSERT INTO Fills  VALUES (20260101,'IBM',100);

SELECT Trades.qty, Fills.qty
FROM Trades JOIN Fills ON Trades.qty==Fills.qty;

There is no need to stage either side into a temporary table first. The join reads both historical tables directly.

The four-clause rule

A join has to be written in a specific shape. Each clause has one accepted form, and the wrong form fails in a distinct way:

Clause	Required form	If written the wrong way
SELECT	Table-qualified, Table.col. Output columns come back named Table_col	—
ON	Qualified equality only, Trades.qty==Fills.qty	—
WHERE	Unqualified, col, not Table.col	Unknown namespace variable: ZZ_FS_SRC.qty
aliases	Not supported at all	Class not found: ZZ_FS_SRC

So the SELECT and ON clauses qualify their column names with the table, and the WHERE clause must not. Table aliases cannot be used anywhere in the statement.

Join limits

• Two historical tables per join. A third fails with Multiple JOIN statements not supported: JOIN.
• LEFT JOIN works, but the right-hand table cannot be constrained. A WHERE or ON condition on the right-hand table of a LEFT JOIN is not applied.

Indexes

An index speeds up lookups on a non-partition column:

CREATE INDEX idx_sym ON Trades (sym);

SORT is optional and is the only index type available. There is no PRIMARY or UNIQUE index: an index does not enforce uniqueness, and an indexed column may hold duplicate values. An index is a lookup accelerator, nothing more.

A few rules apply:

• One index per column, one column per index. A second index on the same column fails with Duplicate Index on column: <name> on <col>. Indexes are single-column; there are no composite indexes.
• The column must exist. Indexing a column that is not on the table fails with Column not found for table <table>: <col>.
• A partition column cannot be indexed. It fails with Can not create INDEX on PARTITION column: d. This is not a limitation to work around: partition pruning already does the job an index would, without opening any file. Index the columns you filter on within a partition. See Partitioning.

A new index is held in memory until the table is optimized, which flushes it to disk, one index per partition. See Optimization.

Warning

Do not index a nullable column. On a column that holds any null, creating an index changes the result of MIN(). The identical aggregate query returns the true minimum before the index and null after it, and it does not recover after optimize. MAX() and COUNT(*) are unaffected.

State	MIN(qty)	MAX(qty)	COUNT(*)
no index	5	30	5
after CREATE INDEX idx ON t (qty)	null	30	5

Index only columns that never hold null.

Warning

Do not create an index on a BOOLEAN column. Creation is accepted, but the first insert then fails with Uncaught error: null <-- java.lang.NullPointerException and leaves the table unusable.

Predicate forms

Most WHERE predicates behave as they do against a realtime table. Four cases do not.

Nulls compare in two different ways. A null sorts below every value in a direct comparison, and drops out of the result once an expression wraps the column:

SELECT sym, qty FROM Trades WHERE qty<20;    -- includes rows where qty is null
SELECT sym, qty FROM Trades WHERE qty+0<20;  -- excludes them

Two predicates on one column throw when one of them wraps the column in an expression. Plain range predicates are fine:

SELECT qty FROM Trades WHERE qty>10 AND qty<100;   -- fine
SELECT qty FROM Trades WHERE qty>10 AND qty*2>40;  -- Unhandled scenario: qty > 10 and ((qty * 2) > 40)

A subquery in IN throws. WHERE qty IN (SELECT ...) surfaces Uncaught error: null <-- java.lang.NullPointerException rather than a clean rejection.

There is no LIKE operator, and no COUNT_UNIQUE:

• LIKE returns No LIKE operator: use ~~ for a filter match, or =~ for a regex. Use ~~ for a filter match or =~ for a regex.
• COUNT_UNIQUE returns No such method: COUNT_UNIQUE(long). It does not exist.

Every error string above, with its cause and correction, is collected in Troubleshooting.

Aggregates

Standard aggregates work against a historical table, on their own or with GROUP BY:

SELECT sym, SUM(qty), AVG(px), COUNT(*), MIN(qty), MAX(qty)
FROM Trades
GROUP BY sym;

Aggregate output columns are named <FUNCTION>_<column> — SUM_qty, AVG_px, MIN_qty — and COUNT(*) comes back as COUNT_.

Two cautions apply:

• MIN() returns null on a column that holds nulls once that column is indexed. See the index warning under Indexes; do not index a nullable column.
• COUNT_UNIQUE does not exist (No such method: COUNT_UNIQUE(long)).

Note

Retrieving aggregate results through an AI client's MCP tools returns a deferred handle, not rows, the same as any other data query. Use a query transport, or verify by row count. See Operating over MCP.