Streaming Orders and History into Amazon S3 / Athena

Overview

This document explains how to configure and use the Amazon Simple Storage Service (Amazon S3) and Amazon Athena as data warehouses for the Execution Server (ES).

Amazon S3 is a scalable, high-speed, web-based cloud storage service designed for online backup and archiving data and applications on Amazon Web Services.

Once you setup streaming for historical orders and trades into S3, you can use serverless services like Athena and Quicksight to run data analytics.

Configure S3

To configure S3, follow these steps:

1. Create an S3 Bucket using the AWS Management Console, as described in the Amazon S3 Guide. You will need a bucket name and region for the Execution Server (ES) configuration.
2. Create a separate AWS user called "ember-s3-loader" for the ES to load data into the S3 bucket. Only grant the user read and write permissions for this bucket.
3. Log in to the AWS Management Console as "ember".
4. Click on the username > My Security Credentials.
5. Under Access Keys for CLI, SDK, & API Access, click on Create Access Key. Note the API access key and secret key generated by AWS.

note

Instead of using API keys method described here you can use IAM instance profiles described later.

Configure the Execution Server

The Execution Server can export data into S3 in two modes:

• Live Mode: A special daemon service exports data in near real-time.
• Batch Mode: A periodic process exports all recently accumulated data in batches.

To configure the ES, add the following section to $EMBER_HOME/ember.conf:

warehouse {
  s3 {          # unit id, you will use it when you run the app, it might be any
    live = true         # false for batch mode (true is default)
    commitPeriod = 1h   # repeatedly commits not full batch at the specified period (5s is default)

    messages = [                               
      ${template.warehouse.s3.messages} {      # loader which loads order messages
        filter.settings.inclusion = null       # EVENTS or TRADES, null means no filter
        loader.settings {               # default   # description
            bucket = "ember-s3-data"   # -         # AWS S3 bucket name   
            region = "us-east-2"       # -         # AWS S3 bucket region
            accessKey = "******"       # -         # AWS API access key
            secretKey = "******"       # -         # AWS API secret key
            minBatchSize = 0           # 0         # min number of records in uploaded batch
            maxBatchSize = 0           # 0         # max number of records in uploaded batch
        }
      }
    ]

    orders = [                               
      ${template.warehouse.s3.orders} {        # loader which loads closed orders
         loader.settings {               # default   # description
            bucket = "ember-s3-data"   # -         # AWS S3 bucket name   
            region = "us-east-2"       # -         # AWS S3 bucket region
            accessKey = “******"       # -         # AWS API access key
            secretKey = "******"       # -         # AWS API secret key
            minBatchSize = 0           # 0         # min number of records in uploaded batch
            maxBatchSize = 0           # 0         # max number of records in uploaded batch
        }
      }
    ]
  }
}

danger

For production setups, store the API key and secret key in the Hashicorp Vault or hash them using the Mangle tool. For more information, refer to the Ember Configuration Guide.

Alternatively, consider using instance profiles method described in the next section.

Using AWS instance profile instead of API keys

Instead of using API keys to access target S3 bucket you can rely on AWS instance profile. With this method there is no need to reference S3 access credentials in ember configuration. On a flip side, any software running on this instance will share write access to the bucket.

How instance profile method works:

• You create an IAM role that specifies the permissions (i.e., write access to specific S3 bucket).
• You assign the role to the instance profile when launching your EC2 instance that hosts Ember.
• The AWS SDK (in running inside ember data warehouse service) retrieves credentials automatically from the instance metadata service.

note

Please note that when Ember data warehouse is running as docker on older AWS instance types that have IMDSv1 you may need to adjust hop limit. By default, new instances use IMDSv2, and have hop limit set to 2. This allows to inherit permissions set by IAM instance profiles for containerized workloads (like ember warehouse). More information about this can be found here.

Control S3 Object Size

minBatchSize & maxBatchSize

Parameters minBatchSize and maxBatchSize are optional attributes that can be used in combination with commitLimit to control the size of S3 objects uploaded by the utility.

• When the maxBatchSize value is > 0, the utility uploads the next batch as soon as it reads the maxBatchSize messages without waiting for the current commitPeriod to end. 
• When minBatchSize is specified, and the number of records in the batch is less than minBatchSize, the utility does not write the batch at the end of the commitPeriod.
• In the batch mode, after retrieving the last message from the journal, the utility uploads all the remaining messages in the batch regardless of the minBatchSize value.

storageClass

To facilitate managing exported S3 objects so that they are stored cost effectively throughout their lifecycle, we also support an optional storageClass attribute that allows setting the Amazon S3 storage class of created S3 objects.

For instance, to use Intelligent Tiering instead of the default Standard storage class, the storageClass attribute in the S3 warehouse message or order configuration would need to be set like this:

loader.settings {    
    …
    storageClass = “INTELLIGENT_TIERING”
}

Note that instead of setting the S3 storage class when data is exported, users have an option to transition S3 objects to different storage classes during their lifecycle by configuring the S3 bucket lifecycle policy.

For example, the policy could be to transition objects to the S3 Intelligent Tiering storage class 30 days after they were exported or to archive them to the S3 Glacier storage class after one year.

For more information, see the Object Lifecycle Management section in the Amazon Simple Storage Service Developer Guide.

Start Exporting Data

To begin streaming Execution Server data into S3, use the data-warehouse service. This service reads the Ember Journal and uploads all trading messages and completed orders into an Amazon S3 bucket in JSON format.

note

Active orders are not exported until they are complete (completely filled, cancelled, or rejected). However, messages concerning active orders are exported immediately.

Run Ember's data-warehouse script with single argument that specifies the S3 data warehouse:

export EMBER_HOME=/deltix/emberhome
/deltix/ember/bin/data-warehouse **s3**

Journal term

On special occasions, clients may delete the Ember Journal. When this happens, the entire trading history on which Ember OMS operates, including message sequence numbers, resets. This presents a problem for existing data warehouse pipelines, where the sequence numbers of stored messages might seem to emerge from the future. To remedy this situation, Ember introduces the concept of a journal term, which increases each time the journal is created.

S3 data warehouse directory structure uses journal term to organize stored data:

Types of stored data

Ember S3 Data Warehouse can be configured to store:

• Messages: Encompasses all trading flow messages, including trading requests (order submissions, cancellations, modifications) and trading events (order ACKs/NACKs, cancellation ACKs/NACKs, fills, etc.). You can configure a filter to reduce this data to trade events only (fills).

• Orders: Records the final state of each order that Ember processed.

Batches & Objects

The utility collects messages and orders into a batch according to the batchLimit and batchPeriod values specified in ember.conf. As soon as the number of records collected reaches the number specified by batchLimit, or the time spanned by collected records reaches batchPeriod, the utility uploads them to S3 and stores them in an S3 object.

The S3 objects are created under keys in this format, where records_type is messages or orders:

<records_type>/term=<journal_id>/date=<date>/hour=<hour>/<date>_<time>_<sequence>.json.gz

date, time and sequence refer to the timestamp and sequence number of the last message or order included in the S3 object. For example:

messages/term=1672826718396/date=2023-11-30/hour=15/2023-11-30_15-03-57_3258881654.json.gz

note

File and folder names use UTC time zone.

The following screenshot shows a sample of data warehouse directory layout in AWS Console:

In batch mode, the data-warehouse utility exits as soon as all recent data has been exported. If you re-run the script, it uploads only new data that the Execution Server accumulated since the last export.

JSON Samples

Here are some sample JSON records in uploaded S3 objects.

JSON Sample of MESSAGE:

{"Term":1568250585001,"Type":"OrderTradeReportEvent","Sequence":576312,"Timestamp":"2019-09-12 09:48:44.472","SourceId":"SIMULATOR","DestinationId":"SOR","OrderId":"1568250646326","OriginalOrderId":null,"CorrelationOrderId":"1568250646326","ParentSourceId":"TUSER1","ParentOrderId":"1dkhg9sl7:31131","RequestId":null,"ExternalOrderId":null,"EventId":"BA813ECD-A757-41E8-8390-C01AF464279D","ReferenceEventId":null,"OrderStatus":"PARTIALLY_FILLED","Symbol":"BTC/USD","InstrumentType":"FX","Currency":null,"ExchangeId":"COINBASE","TraderId":"mtwain","Account":"Silver","ClearingAccount":null,"Side":"SELL","TimeInForce":"IMMEDIATE_OR_CANCEL","ExpireTime":null,"Quantity":70.0,"MinQuantity":null,"DisplayQuantity":null,"OrderType":"LIMIT","LimitPrice":1.34237,"StopPrice":null,"PegDifference":null,"AveragePrice":10128.39227872884,"CumulativeQuantity":55.28729508,"RemainingQuantity":14.71270492,"TradePrice":10127.53,"TradeQuantity":2.464,"Commission":null,"CommissionCurrency":null,"CounterPartySourceId":null,"CounterPartyOrderId":null,"SettlementDate":null,"TradeDate":null,"Reason":null,"VendorRejectCode":-2147483648,"DeltixRejectCode":-2147483648,"MultiLegReportingType":null,"AggressorSide":"ORDER_INITIATOR_IS_AGGRESSOR","OrderUnknown":false,"CancelType":null,"ExecRestatementReason":null,"Flags":-2147483648,"UserData":null,"Attributes":[{"Key":1115,"Value":"Quote"}]}

JSON Sample of ORDER:

{"Term":1568250585001,"OpenSequence":69826,"CloseSequence":69833,"SourceId":"SOR","DestinationId":"SIMULATOR","OrderId":"1568250593156","ParentSourceId":"TUSER1","ParentOrderId":"1dkhg9sl7:3595","ExternalOrderId":null,"Account":"Gold","ClearingAccount":null,"TraderId":"slem","Symbol":"ETH/BTC","InstrumentType":"FX","ExchangeId":"POLONIEX","Currency":"ETH","Side":"SELL","TimeInForce":"IMMEDIATE_OR_CANCEL","ExpireTime":null,"OrderStatus":"CANCELED","OpenTime":"2019-09-12 02:09:45.503","CloseTime":"2019-09-12 02:09:45.511","OrderType":"LIMIT","LimitPrice":1.37123,"StopPrice":null,"Quantity":8.0,"DisplayQuantity":null,"MinQuantity":null,"CumulativeQuantity":0.0,"AveragePrice":0.0,"Reason":null,"VendorRejectCode":-2147483648,"DeltixRejectCode":-2147483648,"UserData":null,"Attributes":null}

Recovering data from warehouse

We provide a Python sample that demonstrates how extract trades that fall in given time range from S3 data warehouse into simple CSV file.

Alternatively you can use Journal Importer tool to recover journal from S3 data warehouse.

Athena Queries

Amazon Athena is a service that enables a data analyst to perform interactive queries in the Amazon Web Services public cloud on data stored in Amazon S3.

To run Athena queries on the message and order data you exported to S3 and create your database and tables, follow the steps in the Athena User Guide. 

Create Tables

You can create Messages and Orders tables using JSON data exported to the deltix-ember-algotest-s3 bucket in the deltixdb database.

To create the tables:

1. Copy the queries in the Messages Table Query and Orders Table Query sections to the Athena Query Editor tab.

2. Update the database and bucket names in the S3 URL to match your database and bucket.

3. Run the queries:

   CREATE DATABASE deltixdb

   

4. Run the following command to load the partitions:

   MSCK REPAIR TABLE deltixdb.messages
   MSCK REPAIR TABLE deltixdb.orders

The data is ready for querying.

You may need to periodically reload partitions to see newly added dates:

For more information on working with partitioned tables, see the Athena documentation.

Messages Table Query

The following statement creates the Messages table:

CREATE EXTERNAL TABLE IF NOT EXISTS deltixdb.messages (
  `type` string,
  `sequence` bigint,
  `timestamp` timestamp,
  `sourceid` string,
  `destinationid` string,
  `orderid` string,
  `originalorderid` string,
  `correlationorderid` string,
  `parentsourceid` string,
  `parentorderid` string,
  `requestid` string,
  `externalorderid` string,
  `eventid` string,
  `referenceeventid` string,
  `orderstatus` string,
  `symbol` string,
  `instrumenttype` string,
  `currency` string,
  `exchangeid` string,
  `traderid` string,
  `account` string,
  `clearingaccount` string,
  `side` string,
  `timeinforce` string,
  `expiretime` timestamp,
  `quantity` double,
  `minquantity` double,
  `displayquantity` double,
  `ordertype` string,
  `limitprice` double,
  `stopprice` double,
  `pegdifference` double,
  `averageprice` double,
  `cumulativequantity` double,
  `remainingquantity` double,
  `tradeprice` double,
  `tradequantity` double,
  `commission` double,
  `commissioncurrency` string,
  `counterpartysourceid` string,
  `counterpartyorderid` string,
  `settlementdate` timestamp,
  `tradedate` timestamp,
  `reason` string,
  `vendorrejectcode` int,
  `deltixrejectcode` int,
  `multilegreportingtype` string,
  `aggressorside` string,
  `orderunknown` boolean,
  `canceltype` string,
  `execrestatementreason` string,
  `flags` int,
  `userdata` string,
  `attributes` array<struct<key:int, value:string>> 
)
PARTITIONED BY (
  `term` bigint,
  `date` date,
  `hour` tinyint
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://deltix-ember-algotest-s3/messages/'
TBLPROPERTIES ('has_encrypted_data'='false');

Orders Table Query

The following statement creates the Orders table:

CREATE EXTERNAL TABLE IF NOT EXISTS deltixdb.orders (
  `opensequence` bigint,
  `closesequence` bigint,
  `sourceid` string,
  `destinationid` string,
  `orderid` string,
  `parentsourceid` string,
  `parentorderid` string,
  `externalorderid` string,
  `account` string,
  `clearingaccount` string,
  `traderid` string,
  `symbol` string,
  `instrumenttype` string,
  `exchangeid` string,
  `currency` string,
  `side` string,
  `timeinforce` string,
  `expiretime` timestamp,
  `orderstatus` string,
  `opentime` timestamp,
  `closetime` timestamp,
  `ordertype` string,
  `limitprice` double,
  `stopprice` double,
  `quantity` double,
  `displayquantity` double,
  `minquantity` double,
  `cumulativequantity` double,
  `averageprice` double,
  `reason` string,
  `vendorrejectcode` int,
  `deltixrejectcode` int,
  `userdata` string,
  `attributes` array<struct<key:int, value:string>> 
)
PARTITIONED BY (
  `term` bigint,
  `date` date,
  `hour` tinyint
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://deltix-ember-algotest-s3/orders/'
TBLPROPERTIES ('has_encrypted_data'='false');

Query Examples

Total count of filled orders:

select count(*) as "# filled orders" 
from orders 
where CumulativeQuantity > 0

Total count of filled orders for 3 days grouped by order source:

select date, sourceId, count(*) as "# filled orders" 
from orders 
where date between date('2022-11-07') and date('2022-11-09')
and CumulativeQuantity > 0
group by date, sourceId 
order by date

Count of matches: Here we count all trade event messages issued by the matching engine and divide results by 2 since every match emits two trade events.

select date, count(*)/2 as "# matches" 
from messages 
where date between date('2022-09-04') and date('2022-09-10')
and type = 'OrderTradeReportEvent'
group by date
order by date

note

Where possible, use partition keys like date as the first condition of your WHERE query. This limits the amount of data Athena has to scan and saves you money.

The cumulative size of buy orders for BTC/USD:

select sum(CumulativeQuantity) 
from orders 
where side = 'BUY' and symbol='BTC/USD'

Show what kind of order each source sends to the KRAKEN exchange:

select distinct SourceId, OrderType 
from orders 
where ExchangeId = 'KRAKEN'

Show the last 100 orders with their submission timestamp in America/New_York timezone:

select SourceId, DestinationId, OrderId, OpenTime AT TIME ZONE 'America/New_York' as OpenTime from orders
order by OpenTime desc 
limit 100

Show the last 100 trades:

select * 
from messages 
where Type ='OrderTradeReportEvent' 
order by timestamp desc
limit 100

Please share your queries with us!

Adding a Quicksight Dashboard

To add a dashboard for the Orders table, follow these steps:

1. Open the Quicksight Console. If you are accessing it for the first time, a basic subscription will do.
2. Define a dataset:
   1. Click Datasets > New.
   2. For the dataset type, click Athena > Next.
   3. Provide a name for the dataset (for example: "orders-prod") and click Next.
   4. Select the Athena database you created
   5. Select the Orders table > Next.
   6. Select Directly query your data > Visualize.
      Quicksight brings you to the dashboard editor.
3. Edit the dashboard:
   1. Switch the dashboard Visual type to Line chart:
      
   2. For the X axis, choose date.
   3. For the Y axis, choose count(orderId).
   4. For Color, specify the order sourceId.
      This displays the order count by order source on any given date:
      

Athena Views

In some cases, it may be helpful to prepare data analytics.

For example, it may be useful to chart order value in Amazon Quicksight. In such cases Athena Views may be very helpful:

CREATE OR REPLACE VIEW orders_view AS
SELECT *, averagePrice*cumulativeQuantity as totalValue
FROM orders
WHERE cumulativeQuantity > 0;

Performance

The following performance was observed on a standard (fastest) S3 storage class using the compressed JSON format.

Upload Performance

An Ember Stress test was used to generate a realistic sample of trading activity (matching engine).

The S3 data warehouse pipeline peaked at around 15000 orders/second, which produced around 45000 trading messages/second.

Query Performance

Test dataset:

• 10 GB of compressed JSON in Orders (roughly 300 million orders).
• 30 GB of compressed JSON in Messages (roughly 850 million messages).

A typical query of the Orders table took 2 minutes 15 seconds, while a query of the Messages table took about 11 minutes.

Partitioning data into sub-folders according to typical use cases may drastically improve these query times.

Deltix is collecting client feedback. Please let us know what are your load patterns.

Message Identity

You can identify records in Orders and Messages using the composite key {Term, Sequence}.

• Sequence comes from the upstream Ember Order Management System (OMS).
  • Each message processed by the OMS is assigned a unique sequence number.
  • Not all messages are stored into the data warehouse. For example, some internal system control messages are skipped. Hence, you may see gaps in sequence numbers which otherwise increase monotonously.
  • System operators may periodically clear Ember’s journal. This action restarts message sequence numbers (and increases Term). When this happens, new messages start at a different term.
• Term identifies journal creation time.
  • Term remains the same for all messages written since Ember journal creation.
  • Each time the journal is cleared and re-created, the value specified as Term increases.

Example

Term	Sequence	Data
1546300800	1	OrderNewRequest
1546300800	3	OrderNewEvent
...	...	...
1546300800	413891	The last message before journal is reset
		Operator resets Ember’s journal (will result in new term and message sequence reset)
1561939200	1	OrderCancelRequest
1561939200	2	OrderCancelRequest
...	...	...

note

You can rely on the fact that {Term, Sequence} are always increasing to implement the before-after ordering of messages.

Recommended IAM Permissions

The following Amazon IAM permission can be given to the IAM user (if you use API keys) or role to access S3 bucket deltix-ember-algotest-s3 used in the examples of this document:

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "s3:ListBucket",
            "s3:GetBucketLocation"
         ],
         "Resource":"arn:aws:s3:::deltix-ember-algotest-s3"
      },
      {
         "Effect":"Allow",
         "Action":[
            "s3:PutObject",
            "s3:PutObjectAcl",
            "s3:GetObject",
            "s3:GetObjectAcl",
            "s3:DeleteObject"
         ],
         "Resource":"arn:aws:s3:::deltix-ember-algotest-s3/*"
      }
   ]
}

Appendix A: Data Format

This section describes the format of the two tables used to warehouse Ember trading history.

Orders Table

The Orders table captures the final state of each order.

Column	Example	Description
Term	1546300800	Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
OpenSequence	312321304	Identifies sequence sequence number of the message that created the order (usually OrderNewRequest).  See “Message Identity” section above.
CloseSequence	312321312	Identifies sequence sequence number of the message that completed (closed) the order (usually OrderCancelEvent, OrderRejectEvent, OrderTradeReportEvent, etc.).  See “Message Identity” section above.
SourceId	CLIENT52	Order source, ALPHANUMERIC(10)
DestinationId	TWAP	Order destination, ALPHANUMERIC(10)
OrderId	ICAP1983EE23	Identifies each order for Execution Server, unique per-source. OrderID is assigned by order source.
ParentSourceId	CONTROL	Identifies source of parent order (optional)
ParentOrderId	ICAP321XX1	Identifies parent order (optional)
ExternalOrderId	ZZ132131	Optional order identifier assigned to the order by execution venue. For example, if we send order to execution venue, like CME they assign their own order identifier. This identifier can be subsequently used to locate this order on CME.
Account	Gold	Identifies order account
TraderId	jdoe	Identifies trader who submitted this order
Symbol	EUR/USD	Order symbol (in symbology configured inside Deltix system)
InstrumentType	FX	Instrument type
ExchangeId	HOTSPOT	Destination exchange (if available)
Currency	USD	Order currency. Optional. Usually used only for orders that use term currency (rather than base currency).
CurrencyCode	840	Same as Currency field but represented as ISO currency code
Side	BUY	Order side
TimeInForce	DAY	Order time In force
ExpireTime	2019-02-27 17:00:00.000	Order expiration time (only for GOOD_TILL_DATE orders)
OrderStatus	CANCELLED	Final state of the order
OpenTime	2019-02-27 16:51:48.002	Order submission time
CloseTime	2019-02-27 16:51:48.120	Order completion time
OrderType	PEG_TO_MIDPOINT	Order type
LimitPrice	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
StopPrice	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
Quantity	10000	Order quantity
DisplayQuantity	1000	Order display quantity (some time described as “max show quantity” or “max floor quantity”), where applicable.
MinQuantity	1000	Minimum fill quantity (where applicable).
CumulativeQuantity	1500.50	Cumulative filled quantity
AveragePrice	132.56	Average fill price
Text	“Cancelled by user request”	For cancelled or rejected orders this field contains textual reason.
VendorRejectCode	1003	Vendor specific reject code. For example CME’s.
DeltixRejectCode	120	Reject code in Deltix classification
UserData	Algo123	User-defined cookie provided at order submission time.

Messages Table

The Messages table records all order-related activity in real time.

More specifically, this table records order requests (original submission, cancellation, and order modification requests) and order events (for example, order acknowledgement, cancellation confirmation, or traders).

To get a better understanding of trading workflows in the Execution Server, refer to the Trading Data Model document.

Column	Example	Description
Type	OrderTradeReportEvent	Identities type of message. See Trading Data Model for list of event types.
Term	1546300800	Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
Sequence	312321312	Unique number that represents ES message sequence, can be used as unique synthetic timestamp. See “Message Identity” section above.
Timestamp	2019-02-27 16:51:48.123	Message timestamp
SourceId	CLIENT52	Order source, ALPHANUMERIC(10)
DestinationId	TWAP	Order destination, ALPHANUMERIC(10)
OrderId	ICAP1983EE23	Identifies order for Execution Server, unique per-source.
OriginalOrderId	ICAP1983EE22	For order replacement request, as well as events that relate to cancel replace workflow (such as PendingReplace, ReplaceReject, and Replace ACK) this field identifies original order in cancel-replace chain.
CorrelationOrderId	ICAP1983EE00	Identity of the first order in cancel-replace chain. Same as OrderID for orders that do not (yet) participate in cancel-replace workflow.
ParentSourceId	CONTROL	Identifies source of parent order (optional)
ParentOrderId	ICAP321XX1	Identifies parent order (optional)
RequestId	XCL#554	For order cancel request, as well as cancel ACK and cancel NACK events this field identifies specific cancel request.
ExternalOrderId	ZZ132131	Optional order identifier assigned to the order by execution venue
EventId	AAAT31231	Optional attribute available for events coming from some venues. Allow identifying duplicate events. May have different uniqueness scope, but must be unique at least in context of single order. NOTE: OMS is responsible for filtering out duplicate events before they reach data warehouse or other downstream consumers.
ReferenceEventId		Used by trade correction and cancellation events to identify previously communicated event that has to be corrected or cancelled.
OrderStatus	PARTIALLY_FILLED	Order status (available for order events only).
Symbol	EUR/USD	Order symbol (in symbology configured inside Deltix system)
InstrumentType	FX	Instrument type
Currency	USD	Order currency. Optional. Usually used only for orders that use term currency (rather than base currency).
ExchangeId	HOTSPOT	Destination exchange (if available) for outbound messages and source exchange for inbound messages. For example, fills will report their exchange in this field.
Account	Gold	Identifies order account
TraderId	jdoe	Identifies trader who submitted this order
Side	BUY	Order side
TimeInForce	GOOD_TILL_CANCEL	Order time in force condition
ExpireTime	2019-02-27 17:00:00.000	Order expiration time (only for GOOD_TILL_DATE orders)
Quantity	100.50	Order quantity
MinQuantity	10	Minimum quantity to execute (optional order request attribute)
DisplayQuantity	5	Minimum quantity to display on exchange floor (optional order request attribute)
OrderType	LIMIT	Order Type
LimitPrice	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
StopPrice	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
PegDifference	0.03	Peg offset, in order money. Optional attribute for PEGGED order types.
AveragePrice	1.325	Average execution price (order events only).
CumulativeQuantity	25	Cumulative executed quantity (order events only).
RemainingQuantity	75.50	Remaining order quantity (part of original order quantity that is still working on the market)
TradePrice	1.321	Trade events only: price of individual trade described by this event. Not to be confused with average price of all trade events reported so far for an order (AveragePrice field).
TradeQuantity	5	Trade events only: size of individual trade described by this event. Not to be confused with total executed size reported so far by all trade events of an order (CumulativeQuantity field).
Commission	0.0001	Trade commission (when known)
CommissionCurrency	USD	Trade commission currency (when known, by default assume order currency)
CounterPartySourceId	CLIENT17	Identifies source of  other side of the trade (when reported)
CounterPartyOrderId	FKA12312312	Identifies order that took the other side of the trade (when reported).
SettlementDate		Trade settlement date (when reported)
TradeDate		Trade date (when reported)
Reason	Market is closed	Reason communicated for cancel or reject events.
VendorRejectCode	1003	Vendor specific reject code. For example CME’s.
DeltixRejectCode	120	Reject code in Deltix classification
MultiLegReportingType		Used for trade reports when order instrument is exchange traded-synthetic. Identifies single-leg trade or whole contract trade of multi-legged security.
AggressorSide		Reports our side as passive or aggressive role in this trade.
OrderUnknown		Flag used by order Cancel Reject events.
CancelType		Enum used by Cancel events.
ExecRestatementReason		Used by Order Restate Events to classify restate type
Flags	3	Order flags. Bitmask containing various order flags. For example, bit 0 marks manual order.
UserData	Foo152	User-provided order tag
\=> Attributes.Key	6201	Custom order attribute key (numeric, corresponds to custom FIX tags specified during order submission)
\=> Attributes.Value	QuantX	Custom order attribute value (text, corresponds to custom FIX tags specified during order submission)

Appendix B: Athena Table Column Data Types

Orders Table

Column	Type
Type	String ('OrderNewRequest', 'OrderReplaceRequest', 'OrderCancelRequest', 'OrderStatusRequest', 'OrderDiscardRequest', 'OrderPendingNewEvent', 'OrderNewEvent', 'OrderRejectEvent', 'OrderPendingCancelEvent', 'OrderCancelEvent', 'OrderCancelRejectEvent', 'OrderPendingReplaceEvent', 'OrderReplaceEvent', 'OrderReplaceRejectEvent', 'OrderTradeReportEvent', 'OrderTradeCancelEvent', 'OrderTradeCorrectEvent', 'OrderStatusEvent', 'OrderRestateEvent')
Sequence	Bigint
Timestamp	Timestamp
SourceId	String
DestinationId	String
OrderId	String
OriginalOrderId	String
CorrelationOrderId	String
ParentSourceId	String
ParentOrderId	String
RequestId	String
ExternalOrderId	String
EventId	String
ReferenceEventId	String
OrderStatus	String ('PENDING_NEW', 'NEW', 'REJECTED', 'PENDING_CANCEL, 'CANCELED', 'PENDING_REPLACE', 'REPLACED', 'PARTIALLY_FILLED', 'COMPLETELY_FILLED', 'EXPIRED', 'SUSPENDED')
Symbol	String
InstrumentType	String ('EQUITY', 'ETF', 'BOND', 'INDEX', 'FX', 'OPTION', 'FUTURE', 'SYNTHETIC', 'CUSTOM')
Currency	String
ExchangeId	String
TraderId	String
Account	String
Side	String ('BUY', 'SELL', 'SELL_SHORT', 'SELL_SHORT_EXEMPT')
TimeInForce	String('DAY', 'GOOD_TILL_CANCEL', 'AT_THE_OPENING', 'IMMEDIATE_OR_CANCEL', 'FILL_OR_KILL', 'GOOD_TILL_CROSSING', 'GOOD_TILL_DATE', 'AT_THE_CLOSE')
ExpireTime	Timestamp
Quantity	Double
MinQuantity	Double
DisplayQuantity	Double
OrderType	String ('CUSTOM', 'MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT', 'PEG_TO_MARKET', 'PEG_TO_MIDPOINT', 'PEG_TO_PRIMARY', 'MARKET_ON_CLOSE', 'LIMIT_ON_CLOSE', 'LIMIT_OR_BETTER', 'PREVIOUSLY_QUOTED')
LimitPrice	Double
StopPrice	Double
PegDifference	Double
AveragePrice	Double
CumulativeQuantity	Double
RemainingQuantity	Double
TradePrice	Double
TradeQuantity	Double
Commission	Double
CommissionCurrency	String
CounterpartyId	String
SettlementDate	Timestamp
TradeDate	Timestamp
Reason	String
VendorRejectCode	Int
DeltixRejectCode	Int
MultiLegReportingType	String ('SINGLE_SECURITY', 'INDIVIDUAL_LEG_SECURITY', 'MULTI_LEG_SECURITY')
AggressorSide	String ('ORDER_INITIATOR_IS_PASSIVE', 'ORDER_INITIATOR_IS_AGGRESSOR')
OrderUnknown	Boolean ('false', 'true')
CancelType	String ('GENERAL', 'DONE_FOR_DAY', 'EXPIRED')
ExecRestatementReason	String ('GT_RENEWAL_RESTATEMENT', 'VERBAL_CHANGE', 'REPRICING_OF_ORDER', 'BROKER_OPTION', 'PARTIAL_DECLINE_OF_ORDER_QTY', 'CANCEL_ON_TRADING_HALT', 'CANCEL_ON_SYSTEM_FAILURE', 'MARKET_OR_EXCHANGE_OPTION', 'CANCELED_NOT_EST' , 'WAREHOUSE_RECAP', 'OTHER')
Flags	Int
UserData	String
Attributes	Array<Struct<Key: Int, Value: String>>

Messages Table

Column	Type
Type	String ('OrderNewRequest', 'OrderReplaceRequest', 'OrderCancelRequest', 'OrderStatusRequest', 'OrderDiscardRequest', 'OrderPendingNewEvent', 'OrderNewEvent', 'OrderRejectEvent', 'OrderPendingCancelEvent', 'OrderCancelEvent', 'OrderCancelRejectEvent', 'OrderPendingReplaceEvent', 'OrderReplaceEvent', 'OrderReplaceRejectEvent', 'OrderTradeReportEvent', 'OrderTradeCancelEvent', 'OrderTradeCorrectEvent', 'OrderStatusEvent', 'OrderRestateEvent')
Sequence	Bigint
Timestamp	Timestamp
SourceId	String
DestinationId	String
OrderId	String
OriginalOrderId	String
CorrelationOrderId	String
ParentSourceId	String
ParentOrderId	String
RequestId	String
ExternalOrderId	String
EventId	String
ReferenceEventId	String
OrderStatus	String ('PENDING_NEW', 'NEW', 'REJECTED', 'PENDING_CANCEL, 'CANCELED', 'PENDING_REPLACE', 'REPLACED', 'PARTIALLY_FILLED', 'COMPLETELY_FILLED', 'EXPIRED', 'SUSPENDED')
Symbol	String
InstrumentType	String ('EQUITY', 'ETF', 'BOND', 'INDEX', 'FX', 'OPTION', 'FUTURE', 'SYNTHETIC', 'CUSTOM')
Currency	String
ExchangeId	String
TraderId	String
Account	String
Side	String ('BUY', 'SELL', 'SELL_SHORT', 'SELL_SHORT_EXEMPT')
TimeInForce	String('DAY', 'GOOD_TILL_CANCEL', 'AT_THE_OPENING', 'IMMEDIATE_OR_CANCEL', 'FILL_OR_KILL', 'GOOD_TILL_CROSSING', 'GOOD_TILL_DATE', 'AT_THE_CLOSE')
ExpireTime	Timestamp
Quantity	Double
MinQuantity	Double
DisplayQuantity	Double
OrderType	String ('CUSTOM', 'MARKET', 'LIMIT', 'STOP', 'STOP_LIMIT', 'PEG_TO_MARKET', 'PEG_TO_MIDPOINT', 'PEG_TO_PRIMARY', 'MARKET_ON_CLOSE', 'LIMIT_ON_CLOSE', 'LIMIT_OR_BETTER', 'PREVIOUSLY_QUOTED')
LimitPrice	Double
StopPrice	Double
PegDifference	Double
AveragePrice	Double
CumulativeQuantity	Double
RemainingQuantity	Double
TradePrice	Double
TradeQuantity	Double
Commission	Double
CommissionCurrency	String
CounterpartyId	String
SettlementDate	Timestamp
TradeDate	Timestamp
Reason	String
VendorRejectCode	Int
DeltixRejectCode	Int
MultiLegReportingType	String ('SINGLE_SECURITY', 'INDIVIDUAL_LEG_SECURITY', 'MULTI_LEG_SECURITY')
AggressorSide	String ('ORDER_INITIATOR_IS_PASSIVE', 'ORDER_INITIATOR_IS_AGGRESSOR')
OrderUnknown	Boolean ('false', 'true')
CancelType	String ('GENERAL', 'DONE_FOR_DAY', 'EXPIRED')
ExecRestatementReason	String ('GT_RENEWAL_RESTATEMENT', 'VERBAL_CHANGE', 'REPRICING_OF_ORDER', 'BROKER_OPTION', 'PARTIAL_DECLINE_OF_ORDER_QTY', 'CANCEL_ON_TRADING_HALT', 'CANCEL_ON_SYSTEM_FAILURE', 'MARKET_OR_EXCHANGE_OPTION', 'CANCELED_NOT_EST' , 'WAREHOUSE_RECAP', 'OTHER')
Flags	Int
UserData	String
Attributes	Array<Struct<Key: Int, Value: String>>