Difference between revisions of "AMI Historical Database"
Line 265: | Line 265: | ||
Once committed (let’s say we have just restarted the AMI ), in the comments section, the | Once committed (let’s say we have just restarted the AMI ), in the comments section, the | ||
'''''IN_APPEND_MODE and NOT_OPTIMIZED''''' keywords will disappear.<br> | '''''IN_APPEND_MODE and NOT_OPTIMIZED''''' keywords will disappear.<br> | ||
+ | [[File:Partition readonly.png]]<br> | ||
+ | |||
+ | <syntaxhighlight lang="amiscript"> | ||
+ | Now let’s insert some more rows into the table and check the table status again.<br> | ||
+ | insert into PartitionDemo values("20161120","AAPL",40,"big blockbuster"); | ||
+ | insert into PartitionDemo values("20161124","IBM",100,"extremely user-friendly"); | ||
+ | insert into PartitionDemo values("20161124","MSFT",50,"enjoyable experience"); | ||
+ | </syntaxhighlight> | ||
+ | Diagnose table PartitionDemo; |
Revision as of 09:25, 30 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”
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.
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
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";
The file structure of AMIHDB is such that each table has its own directory and each partition is
assigned its own subdirectory. Each column and index has its own file(s) per partition directory.
<amiroot>/hdb/Table_Name.3fhtab/Partition_Id.dfhpar/<Files within a partition>
- ● Use ami.hdb.root.dir property to override location of hdb root
- ● Use ami.hdb.filehandles.max property to control how many files can be open at a time
- ● Table_Name - is the name of the table
- ● Partition_Id - An auto-incrementing id (starting at 0001)
- ● Files within a partition:
- ○ KEY.3fhkey - This stores the (plaintext) key for this partition
- ○ ColumnName.3fhcol - Stores data for the column
- ○ IndexName.3fhidx - Stores index data
- ○ KEY.3fhkey - This stores the (plaintext) key for this partition
This paradigm is designed for fast bulk INSERTs, fast random SELECTs and fast DELETES of
entire partitions. UPDATES, and sparse DELETES are possible but will operate slower
Optimized for Schema Alterations
Each partition is a self-contained set of data. This means that altering a schema does not
impact the way existing partitions are saved on disk. Instead, AMIHDB will map the “old”
partitions using the “old” schema to the current schema definition at query time. This applies for
ADD, DROP and MODIFY of columns.
Important Note: The PARTITION columns are the only columns that can not be altered
after the table is created.
Data storage types and optimizations
The AMI historical database has different data storage types designed for data of various
natures and characteristics, such as length, cardinality and partition keys. While continuously
inserting, the nature of the data in the table is unpredictable so a more flexible but less efficient
strategy is used for storage. Therefore, after the partition is completed, it is important to optimize
the storage strategy to adapt to the existing data to improve query performance and minimize
disk storage. Data storage types and optimizations will be further discussed in Section 2: Data
types and storage types, and Section 4.1:Optimization on existing data.
PARTITION MODES
As a historical database, it is important to have a mode for fast insert-on-demand data. In
AMIHDB, when a new partition is created it will have a status of
IN_APPEND_MODE/NOT_OPTIMIZED. Then, after that table is optimized, the partition will
become optimized for data compression and query times.
There are two ways to force optimize partitions:
(a). By calling CALL __OPTIMIZE_HISTORICIAL_TABLE(“Your_Historical_Table_Name”)
(b). By restarting AMI Center
You can use the DIAGNOSE TABLE <Your_Hitorical_Table_Name> command to view the optimized status of each partition:
Before commit:
After commit:
Data Types and Storage Type
AMIHDB supports the same data types as the AMI Real-time database:
BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, CHAR, UTC, UTCN, UUID, COMPLEX, BINARY,
STRING, BIGINT, BIGDEC
Each data type has several storage types each suited to optimize storage/retrieval depending
on the cardinality and/or length of data. In AMIHDB, there are four storage types:
FLAT, BITMAP, VARSIZE, PARTITION.
Important Note: Different partitions can have different storage types for the same column.
VARSIZE
VARSIZE is the storage engine for variable-length data types:
STRING,BINARY,BIGINT,BIGDEC
There are three varieties of VARSIZE:
VARSIZE3,VARSIZE4,VARSIZE5 (default is VARSIZE5)
The number at the end indicates the number of bytes of the address pointer. For example,
VARSIZE3 indicates that the pointer has an address size of 3 Bytes, meaning that it can
address up to approximately 16 Megabytes of data (256*256*256).
Here are is the amount of total data that a column can hold depending on the type:
VARSIZE() | Size |
---|---|
VARSIZE3 | 16MB |
VARSIZE4 | 4GB |
VARSIZE5 | 1TB |
Note, that if data exceeds the size of a column type, AMIHDB will automatically reconfigure and
extend the VARSIZE type (Note: this will be an expensive operation though). AMIHDB will
default to VARSIZE5, if not explicitly specified in the CREATE TABLE schema.
The way AMIHDB stores a variable-length value is to keep an address pointer to point to the
offset position relative to the first string to store each individual new string. As the diagram below
shows, each time we are adding a new string, we are appending it to the end of the preceding
string in the string file. In the example below, each number in the string file block indicates the
number of bytes each string occupies. The first address pointer points to the first string of 17
Bytes long and since there is no preceding string, the offset value the pointer stores is 0. When
we try to add the second string of length 23 Bytes long, we use the second address pointer,
which points to the offset distance relative to the first string, which is 17, to point to the second
string, so on and so forth.
As the diagram below shows, AMIHDB needs 2 files to store the String VARSIZE column. One
file (<column_name>.3fhcol) is the array of offset values for each address pointer and the other
file (<column_name>.3fhdat) stores the actual string payload.
VARSIZE Example
Let’s create a historical table which contains VARSIZE storage types
CREATE PUBLIC table VARSExample(AllNames String, ShortNames String VARSIZE3, MediumNames String VARSIZE4, LongNames String VARSIZE5) USE PersistEngine="HISTORICAL";
Insert into VARSExample values("All the names","abc","abcdef","abcdefghijklmnopqrstuvwxyz");
If we diagnose the table, we can see detailed information for each of the string columns.
Diagnose table VARSExample;
We can see that the AMIHDB by default assigns the string variable to VARSIZE5. So if you are
not declaring a column as " ColumnName String VARSIZE", AMIHDB will assume the
worst case scenario by allocating the maximum memory to the string column (AKA VARSIZE5).
FLAT
FLAT is the storage type for data with a known fixed length. Data types that use FLAT are:
BYTE,SHORT,INT,LONG,FLOAT,DOUBLE,CHAR,UTC,UTCN,UUID,COMPLEX,BOOLEAN
There are three varieties of FLAT:
FLAT, FLAT_NONULL, FLAT_NOMIN (default is FLAT)
- FLAT stores all values and null but uses an extra byte.
- FLAT_NONULL can store all values except null.
- FLAT_NOMIN cat store all values except min value and null
- Note, that if an entry is inserted that does not fit in the column type (for example, inserting null
into a FLAT_NONULL column) AMIHDB will automatically reconfigure and extend the type so the entry will fit. AMIHDB will default to FLAT if not explicitly specified in the CREATE TABLE schema.
Here is an example of storage cost per row for INTEGER, which is 4 bytes:
- INTEGER FLAT: 5 bytes in total, which consists of 4 bytes of data + 1 byte for NULL flag.
- INTEGER FLAT_NONULL: 4 bytes in total (since there is no NULL flag to store)
- INTEGER FLAT_NOMIN: 4 bytes of data in total (the minimum value of that data type is
reserved as a placeholder for null hence its not available)
FLAT Example
First let’s create a historical table with the different FLAT types and insert some rows into it.
If we diagnose the table, we can see that once again, the AMIHDB is assuming the worst case
scenario for the Integer column(FLAT) if you don’t declare the storage type for this column.
CREATE PUBLIC table FlatExample(id int, id2 int FLAT,id3 int FLAT_NOMIN, id4 int FLAT_NONULL) USE PersistEngine="HISTORICAL";
Insert into FlatExample values(1,23,NULL,-2147483648);
Diagnose table FlatExample;
BITMAP
A BITMAP is ideal if the cardinality of the data is relatively low (aka the same values are recurring a lot in the table). The ONLY datatype that supports BITMAP is STRING.
There are 2 options for bitmap:
BITMAP1, BITMAP2 (BITMAP2 is the default)
The number indicates how many bytes are used to identify unique values. For example, a
BITMAP1 stores 255 unique values (and NULL) while a BITMAP2 stores 65,535 unique values
(and NULL). If the row has more than 65,535 unique entries, then a VARSIZE storage must be
used instead.
BITMAP Example
First let’s create a historical table with a String BITMAP column and insert some rows into it.
CREATE PUBLIC table BITMAPExample(symbol1 String BITMAP1,symbol2 String BITMAP2) USE PersistEngine="HISTORICAL";
Insert into BITMAPExample values("IBM","AAPL"),("MSFT","TSLA"),("MSFT","FaceBook");
Diagnose table BITMAPExample;}
PARTITION
PARTITION is a special type of column that needs to be specified by the user in the CREATE
TABLE statement. A partition column aggregates the rows with the same value into a single
partition. For example, if the user defines a ”Date” column as a LONG PARTITION, then all the
rows with the same date value will go into the same partition(essentially stored in the same file
path). One can add more PARTITION columns to the table as needed. For example the user
can declare two PARTITION columns:Date LONG and Region STRING. In this case, the rows
with the same Date and the same Region name will go into the same partition and get stored in
the same file path.
Important Note: It is strongly recommended to have at least one Partition column
otherwise all data for the entire table will be stored in a single partition
Important Note: All partition columns must be declared up front, prior to the insertion of
the data and is immutable. Immutability means that:
(a). Once the column has been declared as a partition column, it cannot be altered afterwards.
(b). Vice versa if the column has been initially declared as a non-partition column, you cannot
convert it into the partition column afterwards.
(c). The data in the PARTITION column cannot be updated/deleted
PARTITION Example 1
To illustrate how the data records are split across different partitions based on a PARTITION
column, the following table PartitionDemo(date LONG PARTITION, symbol STRING,
qty INTEGER, comments STRING) is constructed. "PARTITION" keyword is the way we
declare a column as PARTITION. In simple terms, the rows with the same column value will be
placed into the same directory if we declare a particular column to be PARTITION. The
details of different storage types will be elaborated in the section “Data types and storage
types”.
create public table PartitionDemo(date LONG PARTITION, symbol STRING, qty INTEGER, comments STRING) use PersistEngine="HISTORICAL";
If there exists a partition storage type in the table, like the example shown below where date is a
LONG PARTITION column, then the rows with the same partition value will be placed into the
same AMI History Partition, uniquely identified by a Partition Index(PID). For example if we have
three data records:
(20161120,"IBM",30,"very good products"),
(20161122,"MSFT",60,"Nice shopping experience")
(20161122,"MSFT",60,"Very positive customer feedback")
insert into PartitionDemo values(20161120,"IBM",30,"very good products");
insert into PartitionDemo values(20161122,"MSFT",60,"Nice shopping experience");
insert into PartitionDemo values(20161122,"MSFT",60,"Very positive customer feedback");
Since “date” is declared as a LONG PARTITION column, the rows with the same “date” value
will get clustered into one partition. In the case above, The first row and the second row with the
same “date” value of 20161122 will be placed into the same partition. The third row will be
placed into a separate “AMI History Partition”. If we enter the command “Diagnose table”, we
can see detailed information about the AMI History Table.
Diagnose table PartitionDemo;
We can see that there are two “AMI History Partitions” created, each uniquely identified by the
Partition index. In the table diagnosis diagram above, we have the first “AMI History Partition”,
uniquely identified by PID=1, and second uniquely identified by PID=2.
Note that all the rows are currently in append mode, meaning the rows can still be modified. But
once we commit the data, those become read-only (AKA historical).
Once committed (let’s say we have just restarted the AMI ), in the comments section, the
IN_APPEND_MODE and NOT_OPTIMIZED keywords will disappear.
Now let’s insert some more rows into the table and check the table status again.<br>
insert into PartitionDemo values("20161120","AAPL",40,"big blockbuster");
insert into PartitionDemo values("20161124","IBM",100,"extremely user-friendly");
insert into PartitionDemo values("20161124","MSFT",50,"enjoyable experience");
Diagnose table PartitionDemo;