Historical Database

Creating a Historical Table¶

A historical table is created with the same CREATE TABLE statement as any other Center table, plus a USE clause that selects the on-disk storage engine.

Overview¶

One clause separates a historical table from a realtime one:

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

Everything else behaves as it does for a realtime table. The columns take the same types, the table is queried with the same SQL, and it appears in SHOW TABLES alongside everything else.

Warning

PUBLIC is required. Without it the statement fails with USE options not supported: PersistEngine, which names the option rather than the real cause. A non-public table accepts no USE options at all, so every option produces that same message.

Syntax¶

1
2
3

CREATE PUBLIC TABLE <name> (
    <column> <TYPE> [PARTITION | <STORAGE_MODE>] [, ...]
) USE PersistEngine="HISTORICAL" [<option>="<value>" ...] ;

Two rules are easy to get wrong:

USE options are separated by spaces, not commas. A comma fails with Unexpected return value: class java.lang.String.
The storage mode or PARTITION keyword follows the type with no punctuation. It is a bare token, not a comma-separated argument.

At least one column must not be a partition column. A table of nothing but partition columns fails with Must have at least one NON-PARTITION column.

For the column types and storage modes available, see Data Types and Storage Modes. For what the PARTITION keyword does, see Partitioning.

USE options¶

Three options are accepted. Any other name fails with HISTORICAL does not support USE option: <name>.

Option	Values	Purpose
`PersistEngine`	`"HISTORICAL"`	Required. The only value that selects on-disk storage
`MinOptimizePct`	`0` to `1` inclusive	Enables storage-mode re-encoding during optimize. See Optimization
`MaxOptimizeSeconds`	a number of seconds	Bounds how long a single optimize run holds the table lock

MinOptimizePct outside its range fails with MinOptimizePct must be between 0 to 1: 5.

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

Adding data¶

INSERT works as it does against a realtime table, one row or many:

INSERT INTO Trades VALUES (20260101,'IBM',100,183.4),(20260101,'AAPL',250,201.1);

INSERT ... SELECT loads from another table, which is the usual way history is populated:

INSERT INTO Trades SELECT * FROM LiveTrades;

See Archiving Realtime Data for driving that on a schedule.

Changing the schema¶

Columns are added with ALTER TABLE:

ALTER TABLE Trades ADD venue STRING;

Warning

There is no COLUMN keyword. ALTER TABLE Trades ADD COLUMN venue STRING fails with Invalid type: venue, because the parser reads COLUMN as the column name and venue as its type.

Existing partitions do not hold a file for the new column, and rows in them read as null for it. Nothing is rewritten, so the operation is cheap regardless of how much data the table holds.

Other schema changes¶

Three further operations work on a historical table:

ALTER TABLE Trades RENAME venue TO execVenue;
ALTER TABLE Trades MOVE execVenue BEFORE qty;
ALTER TABLE Trades DROP execVenue;

DESCRIBE TABLE echoes the resulting schema back, which is the quickest way to confirm one took effect.

Updating and deleting rows¶

UPDATE and DELETE both work, despite the store being append-oriented:

UPDATE Trades SET qty=250 WHERE qty==100;
DELETE FROM Trades WHERE tradeDate==20260101;

Neither is what a historical table is built for. Use them for corrections, not as a routine workload; see Querying.

Partition columns cannot be changed once data exists¶

Adding, modifying, or dropping a partition column on a table with rows fails with:

1	`Can only add/modify/drop PARTITION columns on empty tables. You must TRUNCATE TABLE first`

The partition column determines the directory layout on disk, so changing it would mean rewriting every partition. TRUNCATE TABLE removes the rows and lifts the restriction.

This makes the partition column the one decision worth getting right before loading data. See Partitioning for how to choose it.

Inspecting a table¶

DESCRIBE TABLE returns the column definitions. DIAGNOSE TABLE returns considerably more, including the storage mode actually in force per partition, the on-disk path of every file, and the optimize state:

DIAGNOSE TABLE Trades;

It is the most useful command for understanding what a historical table is doing. See Optimization for reading its output.

Removing a table¶

DROP TABLE removes a historical table:

DROP TABLE Trades;

Unlike a realtime table, a historical table also has files on disk. Dropping it removes both: the table disappears from SHOW TABLES, and its .3fhtab directory is deleted from the storage root, leaving nothing orphaned behind.

The drop is a schema change, so it is written to data/managed_schema.amisql and persists across a restart. A dropped table is not recovered by the presence of an old data directory. See Configuration.

Note

DROP TABLE changes state, so an AI client runs it through the state-changing tool, not the read-only query path. See Operating over MCP.

Rules the engine enforces¶

Rule	Error
`PUBLIC` is required for `USE` options	`USE options not supported: PersistEngine`
At least one non-partition column	`Must have at least one NON-PARTITION column`
`USE` options are space-separated	`Unexpected return value: class java.lang.String`
`MinOptimizePct` is a fraction	`MinOptimizePct must be between 0 to 1: 5`
Type names are exact	`Invalid type: BIGINT`
`ALTER ... ADD` takes no `COLUMN` keyword	`Invalid type: venue`
Partition columns are fixed while rows exist	`Can only add/modify/drop PARTITION columns on empty tables. You must TRUNCATE TABLE first`