AMI Realtime Database

Goals

The 3Forge Realtime database builds on all of the features available in the AMI SQL language. The real-time datamodeling

• High Speed - 100k+ inserts per second, with 1 billion+ rows
• Persistent - Tables can be configured for long-term persistence
• Data Virtualization - Access external sources seamlessly
• Realtime Streaming - Chaining streams of updates into aggregations and other projections
• Source Synchronizing - Synchronizing in-memory tables based on external datasources
• Extendable - Create custom triggers, procedures, etc.

Broadly speaking, the logic steps involved in data-visualization are:

1. Data is "streamed" into the AMI real-time database through a combination of methods
   • High Speed Messaging
   • JDBC
   • Synchronizing from External Datasources
   • User updates, from the front end visualization layer
2. Data is fed through logical components which can aggregate, etc.
3. Visualizations are built on the real-time tables and streams.

About Realtime (PUBLIC) tables

For the AMI Center, by default tables that are created are only visible to the current session and will be cleaned up when the connection is terminated.  When a table is created with the PUBLIC directive (ex CREATE PUBLIC TABLE ...) the table will now be accessible to all sessions (external connections, triggers, etc) and front ends for real-time viewing. Public tables will continue to exist until they are explicitly dropped.  Please see the reserved columns on public tables section for details on specialized columns that can be added.

Realtime Tables

create_public_table_clause

Overview

This command is used to create a new, empty (no rows) public table with a specified set of columns (names, types, no-null restriction).  If another public table already exists with the tblname, the create table will fail.

At least one column must be specified, and each column must have a unique name. Columns with the nonull constraint cannot hold a null value. Updates and inserts in violation of the rule will fail.

The Following Use Options can be applied to a table:

• PersistEngine -
  • "FAST": The table will be persisted to disk using a fast, binary protocol. Additional options include:
    • "persist_dir" = "/path/to/directory/for/storing/file" - this is the directory where the .fast binary file will be saved/loaded from
    • "persist_compact_percent" = ".95" - a number between 0 and 1 which says how much of the file should contain updates & deletes before it's compacted. Because the FAST table uses a journaling strategy, updates and deletes will get appended and can create large files. Setting a value of .95 would indicate that if the file becomes 95% updates and deletes then the file should be rebuilt
  • "TEXT": The table will be persisted to disk using a slow, but easy to read text file
    • "persist_dir" = "/path/to/directory/for/storing/file" - this is the directory where the .txt text file will be saved/loaded from
• PersistEngine="Custom": A custom persist engine will be used (see AMI plugins documentation). Note, that PersistOptions="..." is available to pass options to the custom plugin
• Broadcast="true": Front end visualizations & external listeners will be notified as data is updated in the table
• RefreshPeriodMs="durration_millis": The period that the table will conflate and broadcast changes to front end at.  For example, if a cells' value changes 10 times in one second and the refresh period is 500ms, then only ~2 updates will be broadcast out (the other 8 will be conflated)
• OnUndefColumn - Behaviour to take when a realtime Object record contains an undefined column
  • "REJECT" - The record will be rejected.  This is the default
  • "IGNORE" - The  record will be inserted, but the undefined values will be ignored
  • "ADD" -  The table will automatically have the column added
• InitialCapacity="number_of_rows" - the number of rows to allocate memory for when the table is created. Must be at least 1. The default initial capacity is 1,000 rows

Syntax

CREATE PUBLIC TABLE [IF NOT EXISTS] tblname (col1 public_column_type [, col2 public_column_type ...])

[USE  [PersistEngine="[FAST|TEXT|custom]"]

[PersistOptions="custom_options"]

[Broadcast="[true|false]"]

[RefreshPeriodMs="duration_millis"]

[OnUndefColumn="REJECT|IGNORE|ADD"]

tblname - string name of the table to create

colN - string name of Nth column to create

Example

CREATE PUBLIC TABLE countries (country String NoNull, language String, GDP Double) USE PersistEngine="TEXT" Broadcast="false" InitialCapacity="100"

create_public_table_as_clause

Overview

This command is used to create a new table which has the same schema definition as the result set of a select_clause.  If another table already exists with the tblname, the create table will fail.

All rows from the select_clause's result set will be inserted into the table. For valid USE options, see create_public_table_clause Options.

Syntax

CREATE PUBLIC  TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS select_clause

CREATE PUBLIC  TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS analyze_clause

CREATE PUBLIC  TABLE [IF NOT EXISTS] tblname [(col1 public_column_type [, col2 public_column_type ...])] [USE ... ] AS prepare_clause

CREATE PUBLIC  TABLE [IF NOT EXISTS] tblname [USE ... ]  [, tblname [USE ... ] ...] AS execute_clause

tblname - string name of the table to create

Example

CREATE PUBLIC TABLE names USE PersistEngine="FAST" AS SELECT first_name, last_name FROM employees

alter_public_table_clause

OVERVIEW

This command alters the schema (column names and types) of an existing table.  You can add columns (ADD), rename columns (RENAME … TO), delete columns (DROP), and change the type of columns (MODIFY).  Note, when multiple alterations are supplied, they are evaluated left to right. When changing types, a best effort cast will be applied.

When adding columns, the new cells will default to null. The column will be added to the end (right-most) of the table.

ALTER PUBLIC TABLE tblname alter_public_col_expr [, alter_public_col_expr ...]

tblname - string name of the table to alter

Examples

ALTER PUBLIC TABLE AccountHoldings ADD UserId long, RENAME AccountId TO Account_Id, DROP UserInfo

ALTER PUBLIC TABLE AccountHoldings MODIF UserId AS UserId INT

ALTER PUBLIC TABLE AccountHoldings RENAME UserId TO UserIds, ADD UserId STRING AS

alter_public_column_expr

MODIFY colname AS newname public_column_type |ADD newname public_column_type [BEFORE beforecol]|DROP colname|RENAME colname TO newname

colname - String name of the column to alter

newname - String name of the column to create

beforecol - String name of the existing column to add the new column before

rename_public_table_clause

Overview

This command renames an existing table. If another table already exists with the newname, the existing table will be implicitly dropped and replaced with the renamed table.

Syntax

RENAME PUBLIC TABLE tblname TO newname

tblname - string name of the table to rename

newname - string name of the new name for the table

Example

RENAME PUBLIC TABLE AccountHoldings TO Accounts

drop_public_table_clause

Overview

This command drops an existing table, along with its schema and all rows. You can specify multiple tables, separated by commas.

Syntax

DROP PUBLIC TABLE [IF EXISTS] tblname [, tblname ...]

Examples

DROP PUBLIC TABLE Accounts

public_column_type

The following types are supported for columns in public tables.

Note on Nulls: If the NONULL attribute is included, the column does not support null values, and inserts/updates of null will fail.

Note on NOBROADCAST: Columns with broadcast disabled will not be included in updates sent to front end visualizations & external listeners. This is a more granular version of the Broadcast option at the table level.

BYTE [NONULL] [NOBROADCAST] 8-bit signed number (see java.lang.Byte for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

SHORT [NONULL] [NOBROADCAST] 16-bit signed number (see java.lang.Short for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

INT [NONULL] [NOBROADCAST] 32-bit signed number (see java.lang.Integer for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

LONG [NONULL] [NOBROADCAST] 64-bit signed number (see java.lang.Long for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

DOUBLE [NONULL] [NOBROADCAST] 64-bit signed float (see java.lang.Double for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

FLOAT [NONULL] [NOBROADCAST] 32-bit signed float (see java.lang.Float for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

UTC [NONULL] [NOBROADCAST] 64-bit unix epoc timestamp in milliseconds. If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

UTCN [NONULL] [NOBROADCAST] 64-bit unix epoc timestamp in nanoseconds. If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

CHAR [NONULL] [NOBROADCAST] 16-bit unicode character (see.java.lang.Character for details on range). If NONULL is omitted then an extra 1-bit per row is used (precisely, 64-bits per each 64 rows).

BOOLEAN [NONULL] [NOBROADCAST] 1-bit signed Boolean (see java.lang.Boolean for details on range).

STRING [NONULL] [NOBROADCAST] variable length string. Each cell is backed by a java.lang.String instance

STRING COMPACT [NONULL] [NOBROADCAST] 4-byte index into a continuous char array for all values in the column. Values are limited to 32,766 characters in length. Max total chars per column (include 2 byte EOL) is 2^31.

STRING COMPACT ASCII [NONULL] [NOBROADCAST] 4-byte index into a continuous byte array for all values in the column. Extended ascii not supported. Values are limited to 126 chars. Max total chars per column (include 1 byte EOL) is 2^31

ENUM [NONULL] [NOBROADCAST] 32-bit index into map of global Strings (map is shared across all public tables)

STRING BITMAP [NONULL] [NOBROADCAST] 8-bit, 16-bit, or 32-bit address into an underlying map of strings (local to the column). The addressing depends on the number of unique values encountered in the column: 0 - 255 unique entries is 8-bit, 256 - 65,535 unique entries is 16-bit, otherwise 32-bit addressing is used. Note that as new unique values are added, the addressing will automatically escalate as needed. Note that entries from the underlying map are never removed, even as cells are updated deleted.

BINARY [NONULL] [NOBROADCAST]  variable length byte array, useful for storing images and other media.

STRING ONDISK [NONULL] [CACHE=nnnMB][NOBROADCAST]  Same as a String, but only a 64-bit pointer is kept in memory, which resolves to a Block on disk, meaning that strings of any length will take up the same 64-bits in memory. The optional CACHE directive specifies the maximum RAM to use for caching the most recently used values, supported units are KB,MB,GB and TB (if no unit is specified, then in bytes),  example to cache 2 gigabytes: STRING ONDISK CACHE=2GB. Cache-misses will result in reading from disk which can slow down query performance.

BINARY ONDISK [NONULL] [CACHE=nnnMB][NOBROADCAST]  Same as a Binary, but only a 64-bit pointer is kept in memory, which resolves to a Block on disk, meaning that binaries of any length will take up the same 64-bits in memory. The optional CACHE directive specifies the maximum RAM to use for caching the most recently used values, supported units are KB,MB,GB and TB (if no unit is specified, then in bytes), example to cache ~100 megs: BINARY ONDISK CACHE=100000000. Cache-misses will result in reading from disk which can slow down query performance.

BIGDECIMAL [NONULL] [NOBROADCAST] Unbounded(variable length) signed decimal (see java.lang.BigDecimal for details on range).

BIGINTEGER [NONULL] [NOBROADCAST] Unbounded(variable length) signed number (see java.lang.BigInteger for details on range).

COMPLEX [NONULL] [NOBROADCAST] A 128-bit Complex number, composed of 64-bit signed real and 64-bit signed imaginary parts.

UUID [NONULL] [NOBROADCAST] a 128-bit Universally Unique Identifier.

Choosing the Best Column Type for String

Simple rules: For data that never repeats and is queried often STRING is the best. For columns with data that does not repeat but is not queried often STRING COMPACT (or STRING COMPACT ASCII for non-extended ascii data) offer a lower memory footprint. For repeating data, generally STRING BITMAP is best. Below is a table outlining the advantages/disadvantages of each.

Type	Storage Method	Advantage	Disadvantage	Usage Scenario
STRING	Each cell is stored as a java.lang.String object.	Fastest Retrieval	High Memory Usage. Internally, each entry is stored as a java object.	For columns that have very high cardinality and accessed often or participate in indexes. Another use would be when the total data in the column (adding up the lengths of all cells in the column) exceeds 2 billion chars.
STRING COMPACT	Each cell contains a 32-bit address into a continuous char array.15-bits are used to store string length. Each cell has a unique address.	Low Memory/Object usage. A single large continuous array	Slower Retrieval, as cell values are used a temporary string object may be constructed. Frequent deletes or updates that change the cell content lengths may result in array-data compaction.	For columns that have high cardinality and whose cells are either beyond 126 chars or have extended ascii. But is not accessed frequently (ex, as part of a where clause). For example comment columns
STRING COMPACT ASCII	Each cell contains a 32-bit address into a continuous byte array. 7-bits are used to store string length. Each cell has a unique address	1/2 the memory of a STRING COMPACT.	Slower Retrieval, as cell values are used a temporary string object may be constructed. Frequent deletes or updates that change the cell content lengths may result in array-data compaction.	Same as STRING COMPACT, except limited to shorter strings that are basic asci (not extended asci)
STRING BITMAP	Each cell contains a 8,16 or 32-bit address into a map of unique java.lang.String Objects local to the column.  Multiple cells containing the same value will share an address.	Most Compact.	Only used for repeating data (lower cardinality). Retrieval is slightly slower than a simple STRING column, but much faster than COMPACT type columns.	Any scenario where columns have lower cardinality and fewer than approximately 32,000 unique values.
ENUM	Each cell contains a 32-bit address into a map of unique java.lang.String Objects, global to the entire database.  Multiple cells containing the same value will share an address, even if those cells are on different columns of type ENUM.	Compact.  Note: this is generally for legacy, BITMAP is generally preferred	Must be used only on columns with low cardinality. Note this is mostly for legacy support and STRING BITMAP is preferred.	When several columns all use a similar set of values (ex: BUY,SELL, SHORT) then all columns can be set to use ENUM.
STRING ONDISK	Each cell contains a 64-bit address into a random-access file. And only the most recent values read/wrote are cached in memory	Fixed memory size regardless of string length.	Slower read times for un-cached Strings	For large text documents or those that do not need to be searched on

Reserved columns on public Tables

If any of the following columns are declared they will be automatically populated by the AMI engine. Note that when creating the table, the column type MUST match the type specified below, otherwise table creation will fail. Note all times are since unix epoc in milliseconds.

Name	Type	Label	Mutable	Description
C	Long	Created On		Time that the record was inserted into the table
D	Long	AMI ID		An auto-generated incrementing unique id for the row. Unique across all tables.
E	Long	Expires On	INSERT, UPDATE	Time when the row should be automatically deleted.   * Negative Number - Offset from now. Ex:  -4000 remove four seconds from now   * Zero or Null - don't automatically delete   * Positive number - time since epoc to. Ex: 2510712000000L remove 07/24/2049
I*	String	UniqueID	INSERT	The Id(I) value supplied from the real-time streaming on the Object (O) message
M	Long	Modified On		Most recent time that the record was updated (initially the time the row was inserted)
P	String	Login ID	INSERT	The Login Id (I) value supplied from the realtime messaging api on the Login (L) message
V	Int	Revision		The number of times the record has been updated (initially zero)
T	Enum	Table Name		The name of the table, all cells will contain the same value (this is for backwards compatibility)

*I (and P) column behavior can be thought of as a convenient way of supporting the "upsert" concept:

• If the I column is defined (but not the P column) AMI treats I as a (pseudo) primary key. An attempt to insert a row with the same non-null I value as an existing row will result in an update to the existing row instead of an insert. This is different from a standard PRIMARY key in that two rows can exist in a table with the same key if the key is null. Please see CREATE INDEX ... USE CONSTRAINT="PRIMARY" for standard primary keys.

If both the P and I columns are defined, AMI treats the P + I combination as a (pseudo) primary key. An attempt to insert a row with the same non-null P and I values as an existing row will result in an update to the existing row instead of an insert. This is different from a standard PRIMARY key in that two rows can exist in a table with the same key if the key contains a null value. Please see CREATE INDEX ... USE CONSTRAINT="PRIMARY" for standard primary keys.

Realtime Indexes

create_index clause

Overview

This command allows you to dramatically speed up common queries. Without indexes, a SELECT … WHERE clause needs to scan and consider each row.  But if there is an index on the column referenced in the WHERE clause, then the query optimizer will use the index instead. The first column listed is the "top" level column indexed, and so on.

There are three different methods supported for indexing:

• HASH: Under the hood this is a hashmap. This is the fastest most versatile indexing approach but is only useful for queries that directly use equality, because the data is stored in an unsorted fashion.
• SORT: Under the hood this is a treemap. This has additional overhead to a HASH index for both inserting and querying but can be used with ordinality comparisons, such as less than, greater than as well as equals.
• SERIES: Under the hood this is a sorted array. This is a special purpose and highly optimized index useful for when data will be inserted in ascending order, as entries can quickly be added to the end of the array, and queries (including ordinal searches) can use a binary lookup.  If data is order out of order the array is forced to do a memcopy which can be very expensive.

Constraints can be added to an index to determine the outcome of a key collision. Three different types of constraints are supported:

• NONE: If a constraint is not supplied, this is the default. There is no restriction on having multiple rows with the same key.
• UNIQUE: An attempt to insert (or update) a row such that two rows in the table will have the same key will fail.
• PRIMARY: An attempt to insert a row with the same key as an existing row will cause the existing row to be updated instead of a new row being inserted (specifically, those cells specified and not participating in the index will be updated). This can be thought of as an "UPSERT" in other popular databases. An attempt to update a row such that two rows in the table will have the same key will fail. Each table can have at most one PRIMARY index.

CREATE INDEX [IF NOT EXISTS] idx_name

ON tbl_name(col_name [HASH|SORT|SERIES] [,col_name [HASH|SORT|SERIES] ...])

[USE  CONSTRAINT="[NONE|UNIQUE|PRIMARY]"]

idx_name - name of this index to create, each indexs name must be unique for the table

tbl_name - name of the table to add the index to

col_name - name of the column to put the index on.

Examples

This example will add an index to the MyTable's Account and Price column. Queries that use the Account column or the Account and Price column will be faster.  Note that queries using only the Price column will not sped up by this index. This is an ideal index if we know where going to be running queries of the form: select ... from MyIndex where Account==somevalue and Price[ <, ==, > ] somevalue

CREATE INDEX MyIndex ON MyTable(Account HASH, Price SORT)

CREATE INDEX MyIndex ON MyTable(Name HASH) USE CONSTRAINT="PRIMARY"

drop_index clause

Overview

Removes the specified index from the given table, as created by CREATE INDEX

DROP INDEX [IF EXISTS] index_name ON tbl_name

Example

This example will drop the index named MyIndex from the MyTable table. 

Drop index MyIndex on MyTable

Realtime Triggers

create_trigger clause - custom amiscript

Overview

This command allows you to add script that can be executed as data is inserted, updated or deleted from a specified table.  There are 5 different events that can cause the trigger to be executed, you must specify at least one of:

• onInsertingScript - This script will be executed before a row is inserted into table_name table. The values of the row to be inserted will available as local variables to the executing script.
• onInsertedScript - This script will be executed after a row is inserted into table_name. The values of the row just inserted will available as local variables to the executing script.
• onUpdatingScript - This script will be executed before an update on table_name table's rows. The values of the row prior to the update will be available as local variable with the form old_varname. The values of the row after the update will be available as local variable with the form new_varname.
• onUpdatedScript - This script will be executed after an update on table_name table's rows. The values of the row just updated will be available as local variables to the executing script.
• onDeletingScript - This script will be executed before a row is deleted from the table_name table. The values of the row to be deleted will available as local variables to the executing script.
• canMutateRow - If true, then any values of the row changed inside the onInsertingScript will reflect back on the row to be inserted. For onUpdatingScript, any changes to the new_varname values will reflect on the row to be updated. Note, this only applies to the onInsertingScript and onUpdatingScript options. See Mutating Trigger example below

CREATE TRIGGER trigger_name OFTYPE  AMISCRIPT  ON table_name [PRIORITY priority] USE

[vars=" Variables shared by the trigger, a comma delimited list of type varname"]

[onStartupScript="AmiScript to Run When the trigger is created"]

[onInsertingScript="AmiScript to Run Prior to insert of a row on defined Tables"]

[onInsertedScript="AmiScript to Run After an insert of a row on defined Tables"]

[onUpdatingScript="AmiScript to Run Prior to updating a row on defined Tables"]

[onUpdatedScript="AmiScript to Run After updating a row on defined Tables"]

[onDeletingScript="AmiScript to Run Prior to deleting a row on defined Tables"]

[canMutateRow="true or false"]

trigger_name - name of the trigger to create, must be unique within the database

table_name - name of the table(s) that will cause the trigger to execute

priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table

Examples

In this example let us assume we have a table MyTable(id int,price double) and a MyAlertTable(type String,id int,diff double) . When a row is deleted from the MyTable this trigger will insert an alert into the MyAlert Table including the id and price of the record deleted. In the case of an update, a row will be inserted into the MyAlert table including the id and change in price due to the update.

CREATE TRIGGER MyTrigger OFTYPE AMISCRIPT ON MyTable USE

onDeletingScript="insert into MyAlert(type,id) values(\"RECORD DELETED\",id,0-price)"

onUpdatingScript="insert into MyAlert(type,id,diff) values(\"CHANGE\",id,new_price - old_price)"

Mutating Trigger Example

CREATE PUBLIC TABLE MutateSample(px double,qty int,val double);

CREATE TRIGGER MutateTrigger OFTYPE AMISCRIPT ON MutateSample USE

canMutateRow="true" onInsertingScript="val=px*qty"

onUpdatingScript="new_val=new_px*new_qty";

INSERT INTO MutateSample(px,qty) VALUES(125.2,100);

SELECT * from MutateSample;

MutateSample

px Double	qty Integer	val Double
125.2	100	12520.0

UPDATE MutateSample set qty=qty+10;

SELECT * from MutateSample;

MutateSample

px Double	qty Integer	val Double
125.2	110	13772.0

drop_trigger clause

Overview

This command permanently drops one or more triggers.

DROP TRIGGER trigger_name [, trigger_name ...]

Example

This example will drop the trigger MyTrigger from the database.

DROP TRIGGER MyTrigger

Streaming realtime - aggregation triggers

CREATE TRIGGER mytrigger OFTYPE AGGREGATE ON sourceTable,targetTable USE ...

Overview

This command binds an existing source table and an existing target table together such that the target table will be an aggregate representation of the source table.  The following rules apply:

• As the source table has rows inserted, updated or deleted the target table will be automatically updated accordingly
• The target table is no longer modifiable (inserts, updates and deletes to the target table will not succeed)
• Multiple aggregate triggers can share the same source table
• Multiple aggregate triggers can not share the same target table (a given table can only participate in one aggregate trigger as a target table)
• Streaming triggers can be daisy chained, meaning a given table can be a target for one aggregate trigger and a source for another aggregate trigger
• The creation of the trigger will clear out and rebuild the target table to properly reflect the aggregation of the source table's data

There are two USE options:

• groupBys - A comma delimited list of expressions to group rows by, each expression being of the form:
  • targetTableColumn = expression_on_sourceTableColumns [,targetTableColumn = expression_on_sourceTableColumns ...]
• selects - A comma delimited list of expressions on how to populate target columns from source columns.
  • targetTableColumn = aggregate_on_sourceTableColumns [,targetTableColumn = aggregate_on_sourceTableColumns ...]

The following rules apply for the groupBys and selects use options:

• targetTableColumns must only reference columns in the target table's shcema
• sourceTableColumns must only reference columns in the source table's schema
• A targetTableColumn can only be referenced once
• For aggregate expressions only count, countUnique, first, last, max, min, stdev, stdevs, sum and var functions are supported

CREATE TRIGGER trigger_name OFTYPE AGGREGATE

ON source_table_name, target_table_name [PRIORITY priority] USE groupBys="list of group by expressions" selects="list of aggregate expressions"

trigger_name - name of the trigger to create, must be unique within the database

source_table_name - name of the table that will cause the trigger to execute

target_table_name - name of the table that will be updates by the trigger

priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table

Examples

//Lets assume the two tables exist:

CREATE PUBLIC TABLE Accounts(account String,region String,qty int,px double);

CREATE PUBLIC TABLE Summary(act String,region String,cnt int,value double);

//The below trigger will build a realtime aggregation on accounts reflected in the summary table:

//(try inserting into the Accounts table and then take a look at the Summary table)

CREATE TRIGGER MyTrigger OFTYPE AGGREGATE ON Accounts,Summary USE groupBys="act=account,region=region" selects="cnt=count(*),value=sum(qty*px)";

Streaming realtime - projections and filters triggers

CREATE TRIGGER mytrigger OFTYPE PROJECTION ON sourceTable(s),targetTable USE ...

Overview

This command binds one or more existing source tables and an existing target table together such that the target table will be populated using the source table(s).  The following rules then apply:

• As the source table(s) have rows inserted, updated or deleted the target table will automatically have it's corresponding projected rows inserted, updated, deleted accordingly.
• When there are multiple source tables, the resulting target is a UNION of the source tables (not a join)
• The target table's rows that are a result of this projection can not be modified (inserts, updates and deletes to the target table's managed rows will not succeed)
• Multiple projection triggers can share the same source table
• Multiple projection triggers can share the same target table
• The creation of the trigger will clear out and rebuild the target table to properly reflect the aggregation of the source table's data.
• If a source tables' row is updated such that the evaluation of the wheres clause changes, then the row will be inserted/deleted from the target table accordingly.

There are two USE options:

• wheres - A comma delimited list of boolean expressions that must all be true on a source table's row in order for it to be projected into the target table:
  • expression_on_sourceTableColumns,[ expression_on_sourceTableColumns ...]

• selects - A comma delimited list of expressions on how to populate target columns from source columns:
  • targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]

There following rules apply for the wheres and selects use options (important when there are multiple source tables):

• Using the form tablename.columnname will isolate the expression to only apply to the specified table.
• Using the form columnname means that the expression will apply to all tables with the supplied columnname.

CREATE TRIGGER trigger_name OFTYPE PROJECTION

ON source_table_name[, source_table_name...], target_table_name [PRIORITY priority] USE wheres="list of where expressions" selects="list of assignment expressions"

trigger_name - name of the trigger to create, must be unique within the database

source_table_name(s) - name of the tables that will cause the trigger to execute

target_table_name - name of the table that will be updated by the trigger

priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table

Examples

//Lets assume the three tables exist:

CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);

CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);

CREATE PUBLIC TABLE MissingRegion(account String, value double);

//The below trigger will build a realtime view of accounts that have a null region:

//(try inserting into the Accounts1 table and then take a look at the MissingRegions table)

CREATE TRIGGER MyTrigger OFTYPE PROJECTION ON

Accounts1,Accounts2,MissingRegion USE wheres="region==null" selects="account=account,value=qty * px";

Streaming realtime - join triggers

CREATE TRIGGER mytrigger OFTYPE JOIN ON leftTable,rightTable,targetTable USE ...

Overview

This command allows for realtime joining across two tables.

• Changes to the leftTable or/or RightTable automatically update the targetTable, equivalent to using the ... JOIN ... ON ... syntax of the SELECT statement.  See the Join Examples section for an illustration of the various types of joins
• Effectively, the target table can be considered a read only table, and can no longer be modified directly
• Multiple JOIN triggers can share the same source table
• Multiple JOIN triggers can not share the same target table.
• The creation of the trigger will clear out and rebuild the target table to properly reflect the JOIN of the left and right tables.

There are three USE options:

• type - How to join the left and right tables. Supported types include: LEFT, RIGHT, INNER, OUTER, LEFT ONLY, RIGHT ONLY, OUTER ONLY. (See Join Examples section for details on the behavior of the various configurations).
• on - An expression for how to relate the two tables in the form:   
  • "leftColumn == rightColumn [ && leftColumn == rightColumn ... ]"
• selects - A comma delimited list of expressions on how to populate target columns from columns of the left and right tables.
  • targetTableColumn = aggregate on sourceTableColumns [,targetTableColumn = aggregate on sourceTableColumns ...]

The following rules apply for the wheres and selects use options:

• Using the form tablename.columnname will explicitly determine which table (left or right) is to be referenced.
• Using the form columnname means that the column name must be unique to either the left or right table.

CREATE TRIGGER trigger_name OFTYPE JOIN ON left_table_name, right_table_name, target_table_name [PRIORITY priority] USE type="join type" on="cross table comparison expression" selects="list of assignment expressions"

trigger_name - name of the trigger to create, must be unique within the database

left_table_name - name of the left table to join against

right_table_name - name of the right table to join against

target_table_name - name of the table that will be updated by the trigger

priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table

Examples

//Lets assume the three tables exist:

CREATE PUBLIC TABLE Accounts1(account String,region String,qty int,px double);

CREATE PUBLIC TABLE Accounts2(account String,region String,qty int,px double);

CREATE PUBLIC TABLE UnmatchedAccounts(account1 String, account2 String);

//The below trigger will build a realtime view of accounts that only in the Accounts1 table

//or only in the Accounts2 table.  (see OUTER ONLY for details)

//(try inserting into the Accounts1 table and then take a look at the UnmatchedAccounts table)

CREATE TRIGGER MyTrigger OFTYPE JOIN ON Accounts1,Accounts2, UnmatchedAccounts USE type="OUTER ONLY"

on="Accounts1.account==Accounts2.account" selects="account1=Accounts1.account, account2=Accounts2.account";

Streaming realtime - DECORATE triggers

CREATE TRIGGER mytrigger OFTYPE DECORATE ON sourceTable,targetTable USE...

Overview

This trigger is used to automatically decorate (update) a target table based on changes from a source table

• When a row is updated/inserted in the source table all matching rows (based on the ON clause) in the target table are updated (based on the SELECTS clause).
• Multiple DECORATE triggers can share the same source table.
• Multiple DECORATE triggers can share the same target table.
• The creation of the trigger will immediately update the target table based on source table values.
• When a row is inserted into the target table the last known matching values (if any) from the source table will be used to immediately update the new row.
• Deletes from the source table will not affect the target table.

There are three USE options:

• on - An expression for how to relate the two tables in the form:  "leftColumn == rightColumn [ && leftColumn == rightColumn ... ]"
• selects - A comma delimited list of expressions on how to populate target columns from source columns. Form is: targetTableColumn = sourceTableColumn [,targetTableColumn = sourceTableColumns]
• keysChange- Either true or false. Default is false. If it's expected that columns participating in the ON clause can change, then set to true. Note that setting to true adds additional overhead.

CREATE TRIGGER trigger_name OFTYPE DECORATE

ON source_table_name, target_table_name [PRIORITY priority] USE on="cross table comparison expression" selects="list of assignment expressions"

trigger_name - name of the trigger to create, must be unique within the database

source_table_name - name of the table to listen for updates from

target_table_name - name of the table to update on changes

priority - a number, triggers with lowest value are executed first. Only considered when two or more triggers exist on the same table

Examples

//Let's assume the two tables exist: create public table MktData(sym String,price double); create public table Orders(id String,sym String,price double); 

//When the MktData is updated, all Orders with the same symbol will now have the price updated create trigger tt oftype decorate on MktData,Orders use on="Orders.sym==MktData.sym" selects="price=price"

Realtime Timers

create_timer clause

Overview

This command allows you to add script that will be executed on a periodic basis. A special variable _time will be available as a local variable to the executing script

CREATE TIMER [IF NOT EXISTS] timer_name OFTYPE AMISCRIPT ON "schedule" [PRIORITY priority] USE

script="AmiScript to run when timer is executed"

[vars="Variables shared by the timer, a comma delimited list of type varname"]

[onStartupScript="AmiScript to run when the timer is created"]

timer_name - name of the timer to create,  each timer's name must be unique within the database

priority - a number, triggers with lowest value are executed first. Only considered when two or more timers have the same exact scheduled time.

Schedule - either:

• A positive number defining the period in milliseconds between timer executions.

• Crontab style entry declaring the schedule of when the timer should be execute:

Examples

In these examples assume we have the tables A(a boolean) and table B(time utc, cnt int). These timers, when executed, will insert a row into table B with the count of records in A.

//Executes every 500 milliseconds

CREATE TIMER t1 OFTYPE AMISCRIPT ON "500" USE script="int cnt; select cnt =count(*) from A; INSERT INTO b(_time,cnt)"

//Executes every second, on the second

CREATE TIMER t2 OFTYPE AMISCRIPT ON "* * * * * * UTC" USE script="int cnt; select cnt=count(*) from A; INSERT INTO b(_time,cnt)"

//Executes every fifth minute

CREATE TIMER t3 OFTYPE AMISCRIPT ON "* */5 * * * * UTC" USE script="int cnt; select cnt=count(*) from A; INSERT INTO b(_time,cnt)"

//Executes on the first second of every hour Monday through friday

CREATE TIMER t4 OFTYPE AMISCRIPT ON "0 0 * * * MON-FRI UTC" USE script="int cnt; select cnt=count(*) from A; INSERT INTO b(_time,cnt)"

//Execute every Monday at 9:30 eastern standard time

CREATE TIMER t5 OFTYPE AMISCRIPT ON "0 30 9 * * MON EST5EDT" USE script=" int cnt; select cnt=count(*) from A; INSERT INTO b(_time,cnt)"

drop_timer clause

DROP TIMER [IF EXISTS] timer_name

Example

This example will drop the timer MyTimer from the database

DROP TIMER MyTimer

Realtime Procedures

create_procedure_clause

Overview

This command allows you to create stored procedures that can be called via amiscript to execute a sequence of statements and return a value. Arguments can be supplied when calling the stored procedure (see CALL PROCEDURE):

CREATE PROCEDURE [IF NOT EXISTS] procedure_name OFTYPE AMISCRIPT USE

arguments="type name, type name ... "

script="AmiScript to run when proc is called"

[vars="Variables shared by the procedure, a comma delimited list of type varname"]

[onStartupScript="AmiScript to run When the procedure is created"]

procedure_name - name of the procedure to be created, each procedure's name must be unique within the database

Examples

In this example lets assume we have a table MyTable(id int,price double). We will make a simple procedure that inserts a row into that table and deletes any other rows with an equal or lesser price. (See CALL PROCEDURE clause for example of calling this procedure)

CREATE PROCEDURE MyProcedure OFTYPE AMISCRIPT USE

arguments="int _id,double _price"

script="DELETE FROM MyTable WHERE price<_price;INSERT INTO

MyTable(id,price) VALUES(_id,_price)"

call_procedure_clause

Overview

This command allows you to execute stored procedures, typically declared using the CREATE PROCEDURE clause.  When calling procedures you must supply the appropriate arguments expected for the declared procedure.

CALL proc_name ([arg1 [,arg2 ...]])

Example

In this example we will call the procedure declared in the CREATE PROCEDURE clause.

CALL MyProcedure(17,45.2)

drop_procedure_clause

DROP PROCEDURE [IF EXISTS] proc_name

Example

This example will drop the procedure MyProcedure from the database 

DROP PROCEDURE MyProcedure

Realtime Tools

show_clause

Overview

Provides a table listing all records and relevant Metadata of a particular type with optional filtering and sorting. Note, for tables, only PUBLIC tables are listed.

SHOW object_type [WHERE where_expr ] [ORDER BY expr [ASC|DESC] [, expr [ASC|DESC] ...]]

object_type

Either TABLES, COLUMNS, COMMANDS, DATASOURCES, DATASOURCE_TYPES, PROCEDURES, PROPERTIES, RESOURCES, TRIGGERS, TIMERS, CONNECTIONS, RELAYS, PLUGINS, SESSIONS, VARS, PROCESSES, METHODS

Example

This example will return all tables whose name contains "data" and will order the results by the number of columns in the table. 

SHOW TABLES WHERE TableName =~ "data" ORDER BY ColumnsCount

describe_clause

Overview

Provides a table containing with an "SQL" column containing the statement(s) necessary to reconstruct the supplied table,trigger,timer,procedure or index.

DESCRIBE TABLE table_name

DESCRIBE TRIGGER trigger_name

DESCRIBE TIMER timer_name

DESCRIBE PROCEDURE procedure_name

DESCRIBE INDEX index_name ON table_name

Example

This example will return the create statement that can be used to create the __COLUMN table and its pk index. 

DESCRIBE TABLE __COLUMN;

disable and enable

Overview

Used to disable/enable triggers and timers by name. Disabled triggers and timers will not get executed when they otherwise would. By default, when triggers and timers are created they are enabled. Check the enabled/disabled status using SHOW TRIGGERS and SHOW TIMERS.

DISABLE TRIGGER trigger_name

ENABLE TRIGGER trigger_name

DISABLE TIMER trigger_name

ENABLE TIMER trigger_name

Examples

This example will disable the trigger named my_trigger and enable the timer my_timer 

DISABLE TRIGGER my_trigger;

ENABLE TIMER my_timer;

show table clause

Overview

Provides a table listing all columns and relevant Metadata of a particular table with optional filtering and sorting.

SHOW TABLE table_expr  [WHERE where_expr ] [ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]  ...]]

Example

This example will return all columns for the myorders table, alphabetized by name. 

SHOW TABLE myorders order by ColumnName

setlocal

Overview

Controls local variables for the command line session. These local variables dictate behavior of the command line interface.

Setlocal varname = value

diagnose

Overview

Returns a table providing details on the approximate memory footprint of tables, columns and indexes.

Variables

Variables	Description
limit	The default limit to apply to queries (similar to the limit field in the datamodel), -1 means no default limit
max_print_chars	Total number of characters to print to the console for a query result. This prevents from very large query results overwhelming terminal
multiline	If set to "off" then pressing enter will automatically execute the user entered
	If set to "on" then pressing enter will progress to a new line for additional input.  Pressing enter twice in succession will execute the users entered text.
show_plan	If set to "off" the engine will not show the query plan, this is more efficient as some overhead is necessary to gather and display this importation.
	If set to "on" then the engine will produce verbose output on the steps and time taking for each step in the query. This can be useful for optimizing your queries.
timeout	Amount of time in milliseconds that the console will hang waiting for a response. It must be a positive number
unprintable_chars	Determines how to print unprintable ASCII characters, for example 0x01 (SOH).
	If set to "marker" then an upside down question mark (¿) is printed in place of any unprintable chars (default)
	If set to "show" the raw, unprintable, is sent to stdout.
	If set to "hide" then nothing is printed.
	If set to "show_code" then an upside down question mark (¿) is printed followed by the chars 4 digit hexcode is printed. For example, the SOH would be printed as:  ¿0001
datetime_format	The format to use when printing UTC and UTCN columns. If set to empty string, the Raw long value (unix epoch) is printed. The default is YYYY-MM-DD HH:mm:ss.SSS.z
timezone	Works in conjunction with datetime_format, to determine local time for display. For example EST5EDT would be used for New York. Default is UTC
string_template	Should commands interpret string templates (default is off)
	If set to "on" then ${...} will be evaluated and replaced with the contents
	If set to "off" then ${...} will be treated as a literal.
password_encrypt	Should commands interpret string templates (default is off)
	If set to "on" then login command should be supplied with an encrypted password. The password will be decrypted using the key stored on the AMI server.  Note, use strEncrypt(...) method to get the encrypted version of a plain text password. Ex: select strEncrypt("demopass");
	If set to "off" then login command should be supplied with plain text password.

Example

setlocal timeout=30000

Capacity Planning

AMI In-memory Database Capacity Planning

Below is the formula to use for calculating the amount of memory to expect public tables to consume, based on the types of data being stored. For total capacity planning of the AMI in memory database, one should run this on each table and aggregate the results.

table_size_mb = row_cnt x (128 + data_size + idx_cnt * 100 + null_col_cnt * 1/8 ) / 1,048,576

table_suggested_capacity_mb= total_size_mb * 2

table_size_mb	the total number meg you can expect the table to require
table_suggested_capacity_mb	3Forge Recommendation for hardware capacity planning
row_cnt	number of rows
idx_cnt	number of indexes on the table, composite indexes are larger
null_col_cnt	number of non-string columns that allow null
data_size	sum(column_type_sizes)  See column types below

Column Type	Data Size (bytes)
UTC,UTCN,Double,Long	8
Int,Float	4
Char, Short	2
Byte	1
Boolean	1/8
Enum (legacy)	4
String bitmap under 256 unique values	1
String bitmap 256 to 65536 unique values	2
String bitmap over 65536 unique values	3
String Compact	String Size * 2 + 6 bytes
String Compact Ascii	String Size * 1 + 5 bytes
String	String Size * 2 + 64 bytes
Binary	Length in Bytes + 56 bytes
String OnDisk	8
Binary OnDisk	8
BIGDECIMAL	68+size of number in bytes
BIGINTEGER	64+size of number in bytes
COMPLEX	68
UUID	64

Capacity Planning Example

100,000,000 Executions = ~25 GB RAM USED = Suggested ~50GB of RAM be reserved.

Below, we walk through the math given the below table, assuming 10 million executions and 1 index on symbol.

100,000,000 Executions & Associated Memory Usage

Large Capacity Planning Example

Test of 20,000,000,000 (20 billion) cells using 33 columns x 607,000,000 rows = ~171 GM RAM Used = Suggested ~ 342 GB RAM be reserved.