AMI Historical Database

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

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_HISTORICAL_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/Data Size

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

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.

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;

Although the first row in the second insertion has exactly the same date value ”20161120” as the value in the AMI History Partition 1, AMIHDB will create a separate partition to store these rows (in this case stored in AMI History Partition 3, uniquely identified by PID=3).

Now if you commit the data (either by restarting AMI or calling Optimization procedure), the data will be written to disk and become historical. AMIHDB will check if the rows you want to commit contain the same partition value in the historical partitions and collapse them into one partition accordingly.

After the second commit, since partition 3 and partition 1 have the same partition value of “20161120”, we can see that they collapse into one partition, (AKA partition 3 has been merged into partition 1 and thereby increasing the COUNT by 1). Every time we commit the partition, in the “COMMENTS” field, they will appear as “HISTORICAL”, meaning that all the data has been properly written to disk and become read-only.

PARTITION Summary

To summarize:

(a). Each AMI History Partition is uniquely identified by one AMI History Partition Index, also known as PID.
(b). Historical data and data to be committed will live in separate AMI History Partitions.

If the historical table contains a PARTITION column:

(a). Rows with different PARTITION values will be placed into different AMI History Partitions.
(b). Before the commit, the new rows with the same PARTITION value as the historical rows will be placed in a separate AMI History Partition.
(c). Once we commit the data, AMI History Partitions with the same PARTITION value will collapse into one single AMI History Partition.

If the historical table does not contain a PARTITION column:
(a). All historical rows will live in one AMI History Partition and all rows to be committed will live in another AMI History Partition. In other words, there can be at most 2 AMI History Partitions.

Choosing the Best Column Type For Your Variable

There are some general rules of thumb for choosing your optimal storage type for each column based on the nature of your data. The detailed guidelines for a string variable and an integer variable are given as below. These are explained further in the section “Optimization of existing data”.AMIHDB will dynamically adjust the storage type for you to make sure your existing data follows the best practices and assigns the optimal storage type to each of the columns.

Choosing the Best Column Type For a String Variable

Below is a general comparison of different VARSIZE options when declaring a string column.

String Variable Storage Options

Type	Storage Method	Address pointer size	Max File Size	Advantage	Disadvantage	Usage Scenario
String VARSIZE3	Each address pointer has 3 bytes of storage, resulting in max file size of 16777216 Bytes	3 Bytes = 24 Bits	Maximum file size = 2^24 Bytes = 16777216 Bytes = 16MB	Takes up least amount of storage	Cannot store strings over 16MB total size	To store strings not too long in total size
String VARSIZE4	Each address pointer has 4 bytes of storage, resulting in max file size of 4096 MegaBytes	4 Bytes = 32 Bits	Maximum file size = 2^32 Bytes = 4294967296 Bytes = 4096MB	Takes up medium amount of storage	Cannot store strings over 4096MB total size. There is some memory overhead	To store strings whose total size does not exceed 4096MB
String VARSIZE5	Each address pointer has 5 bytes of storage, resulting in max file size of 1024 GigaBytes	5 Bytes = 40 Bits	Maximum file size = 2^40 Bytes = 1099511600000 Bytes = 1024GB	Can store a huge amount of strings up to 1024GB	Takes up most amount of storage and therefore a lot of memory overhead	To store strings that can be astronomically long in size(up tp 1TB in size)
String (By Default)	Assume the worst case scenario, allocating the maximum space to each address pointer (i.e.VARSIZE5)	5 Bytes = 40 Bits	Maximum file size = 2^40 Bytes = 1099511600000 Bytes = 1024GB	Can store a huge amount of strings up to 1024GB	Takes up most amount of storage and therefore a lot of memory overhead	To store strings that can be astronomically long in size(up tp 1TB in size)
String BITMAP1	If the number of unique strings is between 1-255	NOT relevant	NOT relevant	Very memory efficient and fastest retrieval	NAN	For example we only have 10 unique strings out of a large number of rows
String BITMAP2	If the number of unique strings is between 255-65,535	NOT relevant	NOT relevant	Very memory efficient and fastest retrieval	NAN	For example we have 10,000 unique values in the column

Choosing the Best Column Type For an Integer Variable

The similar principles can also be applied to BYTE, SHORT, LONG and BOOLEAN columns that have fixed size.

Integer Variable Storage Options

Type	Storage	Advantage	Disadvantage
Integer FLAT	Can store NULL+MINVALUE	Big memory space. Can store both NULL and MINVALUE	Can be memory inefficient if the actual data set does not actually need this much memory space.
Integer FLAT_NOMIN	Can store NULL	Can store NULL flag	Cannot store MINVALUE
Integer FLAT_NONULL	Can store MINVALUE	Can store MINVALUE	Cannot store NULL flag
Integer (By default)	Default setting for Integer. Assume worst case scenario, same as "Integer FLAT"	Big memory space. Can store both NULL and MINVALUE	Can be memory inefficient if the actual data set does not need both min and null values.

Advanced

This section will go more in-depth into how AMI historical database optimizes itself for more efficient data storage, how the table schema can be altered, how the index can be created as well as how to perform update/delete operations.

Optimization on Existing Data

New partitions are, by default, optimized for flexible insertion of data. After all data has been added for a partition, the partition can be “Optimized” which will reorganize the partition data to improve read performance and minimize disk storage. Additionally, the table’s schema can provide “hints” on how to optimize columns ahead of time, avoiding the cost of optimization later.

The AMIHDB dynamically adjusts the storage type according to how much space your existing data needs for each of your data columns. One could specify the schema of the table at the very beginning, for example we have a column manually specified as quantity Integer FLAT_NONULL. Later on while we are inserting data, if we suddenly need to insert a NULL flag to the table, although the schema says FLAT_NONULL, the AMIHDB is smart enough to alter the schema to FLAT_NOMIN so that we can store the NULL flag in the table. There are other optimization examples that will elaborate how optimization works in various scenarios.

FLAT Optimization Example

First let’s create a historical table with an integer id column.

CREATE PUBLIC TABLE test(id int) USE PersistEngine="HISTORICAL";
Insert into test values(1),(2),(3);

• By default, because we did not specify a storage type before we optimized the table, AMIHDB

will assume the worst case scenario for data storage and assign FLAT to the integer column. If we diagnose the table, we will see that their id storage type is Flat.

• Next, let’s optimize the table and DIAGNOSE TABLE again to see how the storage type for the

id column will change. We optimize the historical table by calling the __OPTIMIZE_HISTORICAL_TABLE(TableName) stored procedure. We see the storage type for the id column has changed to FLAT_NOMIN since AMIHDB scans and confirms that a minimum value to store does not exist.

CALL __OPTIMIZE_HISTORICAL_TABLE("test");
DIAGNOSE TABLE test;

• Then let’s truncate the table and try inserting the minimum value. Let’s see how the storage type

changes accordingly. Since the minimum value -2147483648 exists, which is the most negative value Java can store, the storage type will change to FLAT_NONULL to adapt to the data size in the table.

TRUNCATE TABLE test;
INSERT INTO test VALUES(1),(-2147483648);
CALL __OPTIMIZE_HISTORICAL_TABLE("test");
DIAGNOSE TABLE test;

• Finally, let’s truncate the table again and try inserting the NULL value. Since a NULL value

exists, the storage type is optimized to FLAT_NOMIN.

TRUNCATE TABLE test;
INSERT INTO test VALUES(1),(NULL);
CALL __OPTIMIZE_HISTORICAL_TABLE("test");
DIAGNOSE TABLE test;

VARSIZE Optimization Example

• First let’s create a testing table with a string column. Before optimization, once again the

AMIHDB will assume the greatest storage type requirement by assigning VARSIZE5 to the string variable

CREATE PUBLIC TABLE varTest(name STRING) USE PersistEngine="HISTORICAL";
INSERT INTO varTest VALUES("test1"),("test2"),("test3");
DIAGNOSE TABLE varTest;

• Now, let’s optimize the table. We can see that the storage type for the string variable name

has been changed to VARSIZE3 because the database realizes that VARSIZE3 is sufficient to store all the strings test1, test2, test3

CALL __OPTIMIZE_HISTORICAL_TABLE("varTest");
DIAGNOSE TABLE varTest;

• Next, let’s insert some more strings into the table and optimize it to see how the storage

type will change. As we insert 1 million rows into the table and do the optimization, we can see that the storage type has been changed from VARSIZE3 to VARSIZE4.

for (int i =0;i<1000000;i++) {INSERT INTO varTest VALUES("test"+rand());}
DIAGNOSE TABLE varTest;

BITMAP Optimization Example

• Let’s create a table with a string column and insert 10,000 rows into the table. Before we do the

optimization, we can see the storage type for the string is VARSIZE5

CREATE PUBLIC TABLE BmapTest(name string) USE PersistEngine="HISTORICAL";
For (int i =0;i<10000;i++) {INSERT INTO BmapTest VALUES("test"+rand(10));}
DIAGNOSE TABLE BmapTest;

• After the optimization, the AMIHDB realizes that there are only 10 unique values in the table for the string and adjusts the storage type to BITMAP1 accordingly.

CALL __OPTIMIZE_HISTORICAL_TABLE("BmapTest");
DIAGNOSE TABLE BmapTest;

PARTITION Optimization Example

• Now let’s create a historical table with one column as a string partition and another column as

an integer. We can see that the rows with the same value are placed into the same partition. All rows with the string value equal to test1 are placed into one partition and all rows with the string value equal to test2 are placed into the other partition.

CREATE PUBLIC TABLE PTest(name string partition, qty int) USE PersistEngine="HISTORICAL";
INSERT INTO PTest VALUES("test1",20),("test1",30),("test1",40),("test2",20),("test2",120);
DIAGNOSE TABLE PTest;

• If we do the optimization on the table, all the rows that we inserted will get optimized and be in

read-only mode. This is confirmed by the lack of IN_APPEND_MODE and NOT_OPTIMIZED in the comments section. This means that all data records are properly written to disk and marked as HISTORICAL. The optimization automatically happens after the session has been closed, which results in all data writing to disk.

CALL __OPTIMIZE_HISTORICAL_TABLE("PTest");
DIAGNOSE TABLE PTest;

• If the user wants to insert more data into the historical database afterwards in a new session

(Here calling optimization procedure is equivalent to starting a new session). We can see from diagnosing the table that, instead of appending the new data records into the historical partition columns, we create brand new partitions for them.

INSERT INTO PTest VALUES("test1",60),("test2",35),("test2",25),("test2",10);
DIAGNOSE TABLE PTest;

• Now if we call the optimization procedure against the historical table, the AMIHDB will look to

see if there are partitions with the same partition keys that can be merged with the historical partitions and will do so if there are. Here we can see the new partition with the partition key of test1 and test2 get merged with the historical partitions with the same keys.

CALL __OPTIMIZE_HISTORICAL_TABLE("PTest");
DIAGNOSE TABLE PTest;

File Directory

• Back to the very first example where we explained how data gets partitioned across different

columns, we used the table PartitionDemo(date LONG PARTITION, symbol STRING, qty INTEGER, comments STRING).

In this section, from a file directory perspective, we will take a closer look at how data in each column is stored across different files.

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

• In the table PartitionDemo,we have these rows:
  

And we have 3 Partitions, each uniquely identified by a PID.

• If we go to /ami/amione/hdb directory, we will see all the folders, named as Your_Historical_Table_Name.3fhtab , where all the rows from the table are stored.
  

• If we go to the folder that corresponds to our PartitionDemo historical table, we will see all the partition folders that store the rows for each partition
  

• Next if we go to one of the partition folders, 0001.3fhpar, we can see different binary data files that store the actual values from each column in the corresponding partition.
  

Alter Table Schemas

Oftentimes, we might want to change the schema of our historical database if the nature of the data changes from a particular point onwards. For example, we may want to add a new column to the original table schema because the new data has an extra column. We can go ahead and do so in AMIHDB and set the entries for the old data to be the NULL flag. This section will elaborate on several use cases where the user may want to change the table schema to cater for the need to store new data.

Adding a Column

• Let’s create a historical table called AddColDemo,with (date LONG, symbol STRING) as the original schema.
  

CREATE PUBLIC TABLE AddColDemo(date LONG, symbol STRING) USE PersistEngine="HISTORICAL";
INSERT INTO AddColDemo values(20221209,"IBM"),(20221208,"MSFT");
CALL __OPTIMIZE_HISTORICAL_TABLE("AddColDemo");

• Let’s say a few days later, we have a new column "qty int" from our new data. So we need to add this column to our existing table schema.
  

ALTER TABLE AddColDemo ADD qty INT;
DIAGNOSE TABLE AddColDemo;

• When we diagnose the table, we will see that it does not create a new file directory for "qty int" immediately after we alter the schema
  
  

• Let’s now try inserting some rows into the altered table. We can see after we alter the schema,

AMIHDB does not create a new file directory in the original partition. Instead, it will create a brand new partition and create a new file directory for qty INT there. When AMIHDB goes to the original partition and does not find the file that stores "qty int", it will just assign the NULL flag to these entries.

INSERT INTO AddColDemo VALUES(20221212,"IBM",40),(20221228,"MSFT",50);
DIAGNOSE TABLE AddColDemo;

Modify Existing Data: DELETE/UPDATE

There are times when we want to modify specific data records using the “where” clause, either with DELETE or UPDATE. In this section, we are going to demonstrate how to update and delete rows using UPDATE … WHERE and DELETE … WHERE clauses.

UPDATE Clause

First let’s create a table UpdateDemo(date LONG, symbol STRING) and insert some data records into it. We optimize the table and perhaps a few days later we decide to change the symbol name from TSLA to TSLANew.

CREATE PUBLIC TABLE UpdateDemo(date LONG, symbol STRING) USE PersistEngine="HISTORICAL";
INSERT INTO UpdateDemo VALUES(20221209,"AAPL"),(20221208,"TSLA"),(20221212,"TSLA"),(20221215,"MSFT"),(20221228,"TSLA");
CALL __OPTIMIZE_HISTORICAL_TABLE("UpdateDemo");
DIAGNOSE TABLE UpdateDemo;

UPDATE UpdateDemo SET symbol = symbol+"test" WHERE symbol=="TSLA";
SELECT * from UpdateDemo;
DIAGNOSE TABLE UpdateDemo;

We can see that after performing an update on the rows where sym==”TSLA, this process automatically un-optimizes the AMI History partition columns from which the values are being modified. The AMI History partition columns that don’t participate in the UPDATE ... WHERE clause will remain intact (AKA the optimization status will not change).

Important Note: Values in a PARTITION column CANNOT be updated

If we create the same table except that we declare the "symbol" to be a "String PARTITION" column, we cannot update the values in the "symbol" column. If we do so, it will throw us an exception:

DELETE Clause

Let’s stick with the same example above and create a table DeleteDemo(date LONG, symbol STRING)and insert some data records into it.

CREATE PUBLIC TABLE DeleteDemo(date LONG, symbol STRING) USE PersistEngine="HISTORICAL";
INSERT INTO DeleteDemo VALUES(20221209,"AAPL"),(20221208,"TSLA"),(20221212,"TSLA"),(20221215,"MSFT"),(20221228,"TSLA");
CALL __OPTIMIZE_HISTORICAL_TABLE("DeleteDemo");
DIAGNOSE TABLE DeleteDemo;

If later, we decide to delete all the row records where symbol is equal to "TSLA". We can use the 'DELETE FROM ... WHERE clause to achieve this.

DELETE FROM DeleteDemo WHERE symbol=="TSLA";
SELECT * FROM DeleteDemo;
DIAGNOSE TABLE DeleteDemo;

We can see that because the modification of the data takes place at a row level, which means all AMI History Partition Columns are participating in the DELETE FROM … WHERE clause. As a result, all AMI History Partition Columns in that partition will get affected and get un-optimized.

Summary

To summarize, updating or deleting certain row records in the historical table causes the table to un-optimize. The AMI History Partition Columns that participate in the updating/deleting clause will get un-optimized. Below is an illustrative diagram that shows what happens with the optimization status before and after the clause is applied.

Let’s say we have a table transaction(date LONG PARTITION, symbol STRING, qty INTEGER, comments STRING),and this table is fully optimized. We use the green tick to mark each individual AMI History Partition Column as Optimized and red cross as un-optimized.

Now, let’s say we want to update the symbol and set the symbol to "Deprecated" for partition 1 and partition 5. We can do this:

UPDATE transaction SET symbol = "Deprecated" WHERE date==2019 OR date==2023;

Since only the symbol AMI History Partition Column from Partition 1 and Partition 5 are participating in the clause, they will get un-optimized, marked as red cross.

Next, we will delete some rows from the table:

DELETE FROM transaction WHERE date==2021;

Since the DELETE FROM clause performs a row-wise deletion, all the AMI History partition columns in Partition 3 are participating in the clause. They will be marked as un-optimized.

Create Index on a Particular Column

We can create indexes on the historical table to enhance query performance. For AMIHDB, the only type of index supported is SORT, which creates a b-tree to sort the data. The example below shows how to create an index on a specific column.

First let’s create a historical table indexTest and create an index on the “date” column. We can see as we insert more data into the table, the index is kept in memory but the columns are stored on disk.

CREATE PUBLIC TABLE indexTest(name string, date long, qty int) USE PersistEngine="HISTORICAL";
INSERT INTO indexTest VALUES("AAPL",20221023,20),("AAPL",20221011,30),("TSLA",20221030,40),("MSFT",20220930,24),("MSFT",20220925,120);
CREATE INDEX i on indexTest(date);
DIAGNOSE TABLE indexTest;

After we do the optimization and commit the partition, the AMIHDB then builds the index and writes it to disk.

CALL __OPTIMIZE_HISTORICAL_TABLE("indexTest");
DIAGNOSE TABLE indexTest;

Restrictions

The AMIHDB is very versatile and flexible in terms of dynamic optimization of the storage type and is efficient on how the data is partitioned across different file directories for faster query execution and retrieval of data. However, no database is perfect and there are some restrictions for AMIHDB.

Immutability of PARTITION Column

A PARTITION column is the core of how the rows are partitioned across different partition file directories and therefore must be declared upfront upon creating the schema of the historical table. Once a PARTITION column has been declared, there is no way to alter it by any means. Similarly, a non-PARTITION column cannot be altered to a PARTITION column afterwards because mutating PARTITION columns requires us to change the whole structure of the file system and therefore is very expensive. Thus, the users are strongly advised to plan wisely on how they intend to partition their database.

Index Support

For the time being, the HDB does not support composite indexes. The only type of index we support is a sort index over a particular column, which employs a B tree to sort the entire column of data for faster search and retrieval.

Features Supported/Not Supported on HDB

Features Supported/Not Supported on HDB

Features	Supported on HDB?	Example
Triggers	Not supported**	**Can be referenced inside triggers on Realtime tables.
Timers	Not Supported
Procedures	Supported	CREATE PROCEDURE ARCHIVE_ORDERS OFTYPE AMISCRIPT USE script="{ int rowCount=select count(*) from RealtimeOrders; for(int n=0;n<rowCount;n+=batchSize){ INSERT INTO HistoricalOrders SELECT * FROM ORDERS LIMIT n, batchSize; } };" arguments="int batchSize";
Indexes	ONLY SORT is Supported	CREATE INDEX OrderIdLookupIndex ON HistoricalOrders (OrderId SORT);
Table joins	Not Supported,need to resort to temp table	CREATE TABLE temp_a AS SELECT * FROM HDB_a; CREATE TABLE temp_b AS SELECT * FROM HDB_b; SELECT * FROM temp_a,temp_b WHERE temp_a.id==temp_b.id;
Inner query	Not Supported	SELECT * FROM hdb1 WHERE date IN (SELECT date FROM hdb2); //NOT supported, will throw UnsupportedOperationException