Difference between revisions of "AMI Historical Database"

From 3forge Documentation
Jump to navigation Jump to search
Line 19: Line 19:
 
When inserting row(s) whose date match(es) the existing partition key(s), the query engine will automatically navigate to the corresponding partition according to the partition key and append the rows accordingly.<br>
 
When inserting row(s) whose date match(es) the existing partition key(s), the query engine will automatically navigate to the corresponding partition according to the partition key and append the rows accordingly.<br>
 
[[File:Partition illustrative2.png]]<br>
 
[[File:Partition illustrative2.png]]<br>
 +
Note that adding another day of data will automatically create a new partition without affecting
 +
existing days’ data: For example, if we want to insert two new rows into the table whose “Date”
 +
== 20221203<br>
 +
[[File:Partition illustrative3.png]]<br>
 +
To create a historical table add USE PersistEngine="HISTORICAL" and include the PARTITION keyword after the column(s) that the rows should be partitioned by. Ex:<br>
 +
 +
<syntaxhighlight lang="amiscript">CREATE PUBLIC TABLE PartitionDemo(date LONG PARTITION, symbol STRING, qty INTEGER, comments STRING) use PersistEngine="HISTORICAL"; </syntaxhighlight>

Revision as of 16:53, 26 May 2023

Introduction to AMI Historical Database

The AMI Historical Table is a columnar-based on-disk table engine with support for partitioning. The introduction section will briefly describe the core concepts of the AMI Historical database: Partitioning, Data storage and optimizations.

Partitioning

Partitioning is used to organize data for performance. To understand why partitioning is critical for large datasets, let’s first consider a non-partitioned standard table: as the number or rows increases, the performance (time to insert, update, query and alter) will decline. Partitioning provides an elegant solution by breaking up rows into separate isolated “virtual tables” called partitions. Once data is broken into partitions, the row count of one partition will not affect the performance of other partitions. In order to dictate how rows are organized into partitions, the AMI Historical Table introduces a special PARTITION column type. The value in the PARTITION column(s) determines which partition a given row will be assigned to. In other words, all rows for a given partition will have the same value in the PARTITION column(s).

By way of example, data is often partitioned by a date: Imagine we have the following historical table, partitioned by the column “date”
Partition illustrative.png
When inserting row(s) whose date match(es) the existing partition key(s), the query engine will automatically navigate to the corresponding partition according to the partition key and append the rows accordingly.
Partition illustrative2.png
Note that adding another day of data will automatically create a new partition without affecting existing days’ data: For example, if we want to insert two new rows into the table whose “Date” == 20221203
Partition illustrative3.png
To create a historical table add USE PersistEngine="HISTORICAL" and include the PARTITION keyword after the column(s) that the rows should be partitioned by. Ex:

CREATE PUBLIC TABLE PartitionDemo(date LONG PARTITION, symbol STRING, qty INTEGER, comments STRING) use PersistEngine="HISTORICAL";