Historical Database

Historical Database Quickstart¶

This tutorial creates a historical table, loads it, optimizes it, and queries it back, using only statements verified against a running Center.

Overview¶

The Historical Database needs no setup beyond a running Center — it is part of the Center, not a separate component. Everything below is ordinary AMI SQL. The one step that looks unusual is confirming a load by row count rather than by reading rows back; the reason is in Reading the data back.

Submitting the statements¶

Every statement below is AMI SQL sent to the Center. Nothing here is specific to the Historical Database, so use whichever channel your deployment already exposes:

Channel	Notes
REST, `/3forge_rest/query`	The quickest way to run a statement from a shell or a script. The SQL endpoint ships disabled: the shipped `ami.rest.plugin.classes` is `*,!QUERY,!ADMIN`, so add `QUERY` first
MCP tools	Already enabled out of the box. Note that a `SELECT` returns a handle rather than rows over this transport. See Operating over MCP
JDBC	For an application or a SQL client

The examples below show the SQL alone, so they work whichever channel you pick. Over REST, one statement looks like this:

curl -sk -u username:password -G \
  --data-urlencode 'cmd=SHOW TABLES' \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

Substitute your own host, port, and credentials. If that returns a table list, you are ready for step 1.

1. Create a historical table¶

One clause, USE PersistEngine="HISTORICAL", makes this an on-disk table rather than an in-memory one, and PUBLIC is required:

CREATE PUBLIC TABLE Trades (
    tradeDate LONG PARTITION,
    sym       STRING BITMAP2,
    qty       LONG,
    px        DOUBLE
) USE PersistEngine="HISTORICAL" MinOptimizePct="0";

tradeDate is the partition column, so each day's data lands in its own directory. MinOptimizePct="0" enables storage-mode re-encoding when the table is optimized in step 4. See Creating a Historical Table and Partitioning.

2. Load some rows¶

A single INSERT with several value tuples loads them all at once:

INSERT INTO Trades VALUES
    (20260101,'IBM',100,183.4),
    (20260101,'AAPL',250,201.1),
    (20260102,'IBM',75,185.0),
    (20260102,'AAPL',120,202.5);

3. Confirm the load¶

Check the row count with SHOW TABLES, which returns immediately:

SELECT TableName, RowCount FROM SHOW TABLES WHERE TableName=="Trades";

RowCount should be 4.

4. Optimize¶

Optimizing merges the partitions written during the load and, because MinOptimizePct is set, tightens column storage:

CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");

See Optimization for what the pass does.

5. Inspect what is on disk¶

DIAGNOSE TABLE shows the partitions, the storage mode of each column, and the on-disk path of every file:

DIAGNOSE TABLE Trades;

One PID row appears per partition — 20260101 and 20260102 — and the STORAGE column shows how each column is encoded.

6. Index a column¶

An index speeds up lookups on a non-partition column. Index a column that holds no nulls:

CREATE INDEX idx_qty ON Trades (qty);

Warning

Do not index a column that can be null. On a nullable column an index silently changes the result of MIN(). See Querying.

7. Query it back¶

The table is queried with ordinary AMI SQL, filtered on the partition column so only one day is read:

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

Reading the data back¶

Over a query transport such as JDBC or the REST query endpoint, that SELECT returns rows. Over an AI client's MCP tools it returns a deferred handle instead of rows, so confirm a load with the SHOW TABLES count from step 3 and inspect structure with DIAGNOSE TABLE. See Operating over MCP.

Next steps¶

Use Cases — worked scenarios across capital markets, operations, and general time series.
Configuration — the storage root and the schema-persistence requirement, both of which matter before a real deployment.
Creating a Historical Table — the full DDL, USE options, schema changes, and removing a table.