Streaming Orders into AWS RedShift

Overview

This document explains how to configure and use AWS RedShift, Amazon’s fully managed petabyte scale data warehouse, for the Ember.

Deltix recommends this warehouse to clients who have a relatively small order rate (few thousands per second) and want the simplicity of an AWS managed database with the analytical power of RedShift.

Please be warned that RedShift is not very good at storing elevated rates of trading activity. See the Performance section for more information.

RedShift Setup

To launch a RedShift cluster, follow the description in the AWS RedShift tutorials.

Practical Considerations

Disk Space

Storage for one order takes approximately 700 - 1000 bytes. Multiple cancellation attempts or an unusually high amount of trades per order can increase the order footprint.

Please make sure you select the appropriate storage sizes. We recommend starting with 1TB.

VPC Network

For improved performance, consider running your RedShift cluster in the same VPC as Ember.

If you plan to use the RedShift Query Editor, you need to use an Advanced VPC configuration.

Make sure AWS VPC settings would allow the Ember data warehouse to connect to your RedShift cluster.

Create a Cluster

To create a cluster in the AWS RedShift console, follow these steps:

1. Use Create Cluster.

2. On the Cluster Details tab, fill out the fields.

   

3. On the Node Configuration tab, select a node type.

   

4. On the Additional Configuration tab, select Enhanced VPC and the correct VPC.
   You can edit the VPC configuration to enable access to the RedShift port later on.

note

If you set up a test database and test it over the Internet, in the network settings, enable public access.

5. Once the database cluster starts up, on the RedShift Cluster Configuration tab, look up "JDBC URL" and copy the cluster hostname.
   You will need to enter it into Ember’s data warehouse streaming configuration.

   

Configure the Ember

The Ember can export data into RedShift 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.

In the configuration fragment below, the highlighted portions reflect the RedShift cluster created in the previous section. Here we also show how to control read and write batch sizes to improve tool responsiveness.

If you want to limit streaming to either the Orders block or the Messages block, only include the one you want to use.

To configure the Ember, add the following sections to $EMBER_HOME/ember.conf.

warehouse {
  redshift { # unit id, you will use it when you run the app, it might be any
   live = true # keep checking for new messages when end of journal is reached
   readBatchLimit = 10 # reduce batch size - redshift is slow
   
   messages = [                               
      ${template.warehouse.redshift.messages} { # loader which loads order messages
        loader.settings {          
          host = "ember-redshift-cluster-1.cau6u2ckbpk1.us-east-2.redshift.amazonaws.com"     
          port = 5439              
          username = "awsuser"        
          password = "data#WAREZ#1"        

          databaseName = "ember"   
          tableName = "messages"   

          createDatabase = false # Redshift: database must already exist; creation is not supported
          createTable = true    # create table if not exists
          dropTable = false     # drop table if exists
          batchLimit = 10            
        }
      }
    ]

    orders = [                               
      ${template.warehouse.redshift.orders} { # loader which loads closed orders
        loader.settings {          
          host = "ember-redshift-cluster-1.cau6u2ckbpk1.us-east-2.redshift.amazonaws.com"
          port = 5439              

          username = "awsuser"        
          password = "data#WAREZ#1"        

          databaseName = "ember"   
          tableName = "orders"     

          createDatabase = false   
          createTable = true       
          dropTable = false
          batchLimit = 500            
        }
      }
    ]
  }
}

danger

For production setups, store connection passwords in a Hashicorp Vault or hash them using the Mangle tool. For more information on storing passwords, refer to the Ember Configuration Guide.

Start Exporting Data

To begin streaming Ember data into RedShift, use the data-warehouse service. This service reads the Ember journal and converts all trading messages and completed orders into RedShift Messages and Orders tables.

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 RedShift data warehouse:

export EMBER_HOME=/deltix/emberhome
/deltix/ember/bin/data-warehouse redshift

In batch mode, this script exits as soon as all recent data exports. If you re-run the script, it appends any new data that the Ember accumulated since the last export.

Verify Installation

To verify your setup, use the AWS RedShift Query Editor:

You should see some content in the messages table.

Message Identity

Messages table: Each row can be identified by 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 (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.

Orders table: There is no column named sequence. Each row stores opensequence (message that created the order) and closesequence (message that completed the order). Use {term, closesequence} when you need an ordering comparable to message sequence; use {sourceid, orderid} together with term to refer to a specific order lifecycle in the journal.

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.

Performance

Amazon RedShift is designed for analytics queries, rather than transaction processing.

The cost of COMMIT is relatively high. Deltix benchmarking shows that RedShift data loading is limited to about 500 messages per second. This was observed with default settings of a two-node dc2.large cluster.

On the data loading side, Deltix uses configurable batched inserts. Aggressive batching using the batchLimit parameter helps.

The following graph shows data from the experiment where we loaded trade report events into RedShift.

The Deltix data warehouse can handle trading activity spikes, but if your sustained load exceeds 10 orders per second, you may want to consider using another data warehouse. For example, ClickHouse can handle more than 100,000 orders per second.

Appendix: Data Format

This section describes the format of the two tables used to warehouse Ember's trading history. To find a more detailed explanation of trading requests and events used by Ember, refer to the Order Entry API document.

Identifier naming (tables vs columns)

Redshift follows PostgreSQL-style rules for unquoted identifiers: names are folded to lowercase in the catalog (information_schema, svv_columns, etc.). The lists below use lowercase names—the same ones you use in SELECT and WHERE without double quotes.

Configured tableName values are not rewritten by the loader (the examples use "messages" and "orders"). Use the same spelling you configure when qualifying tables.

Note: The Redshift warehouse orders table uses columns named trader and exchange (CHAR(10) for exchange). That differs from the Microsoft SQL Server and PostgreSQL pipelines, which use TraderId / ExchangeId with a wider exchange identifier column.

Orders Table

The Orders table captures the final state of each order.

Column	Type	Example	Description
term	INT8	1546300800	Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
opensequence	INT8	312321304	Identifies sequence number of the message that created the order (usually OrderNewRequest).  See “Message Identity” section above.  Since: Ember 1.4
closesequence	INT8	312321312	Identifies sequence number of the message that completed (closed) the order (usually OrderCancelEvent, OrderRejectEvent, OrderTradeReportEvent, etc.).  See “Message Identity” section above.  Since: Ember 1.4
sourceid	CHAR(10)	CLIENT52	Order source, ALPHANUMERIC(10)
destinationid	CHAR(10)	TWAP	Order destination, ALPHANUMERIC(10)
orderid	VARCHAR	ICAP1983EE	Identifies each order for Ember, unique per-source. OrderID is assigned by order source.
parentsourceid	CHAR(10)	CONTROL	Identifies source of parent order (optional)
parentorderid	VARCHAR	ICAP321XX1	Identifies parent order (optional)
externalorderid	VARCHAR	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	VARCHAR	Gold	Identifies order account
clearingaccount	VARCHAR		Clearing account (when applicable)
trader	VARCHAR	jdoe	Identifies trader who submitted this order
symbol	VARCHAR	EUR/USD	Order symbol (in symbology configured inside Deltix system)
instrumenttype	VARCHAR	FX	Instrument type (string; allowed values are defined in the trading model)
exchange	CHAR(10)	HOTSPOT	Destination exchange (if available)
currency	CHAR(10)	USD	Order currency. Optional. Usually used only for orders that use term currency (rather than base currency).
side	VARCHAR	BUY	Order side (string; allowed values are defined in the trading model)
timeinforce	VARCHAR	DAY	Order time in force (string; allowed values are defined in the trading model)
expiretime	TIMESTAMP	2019-02-27 17:00:00.000	Order expiration time (only for GOOD_TILL_DATE orders)
orderstatus	VARCHAR	CANCELLED	Final state of the order (string; allowed values are defined in the trading model)
opentime	TIMESTAMP	2019-02-27 16:51:48.002	Order submission time
closetime	TIMESTAMP	2019-02-27 16:51:48.120	Order completion time
ordertype	VARCHAR	PEG_TO_MIDPOINT	Order type (string; allowed values are defined in the trading model)
limitprice	FLOAT8	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
stopprice	FLOAT8	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
quantity	FLOAT8	10000	Order quantity
displayquantity	FLOAT8	1000	Order display quantity (sometimes described as “max show quantity” or “max floor quantity”), where applicable.
minquantity	FLOAT8	1000	Minimum fill quantity (where applicable).
cumulativequantity	FLOAT8	1500.50	Cumulative filled quantity
averageprice	FLOAT8	132.56	Average fill price
reason	VARCHAR(max)	“Cancelled by user request”	For cancelled or rejected orders this field contains textual reason.
vendorrejectcode	INT4	1003	Vendor specific reject code. For example CME’s. Since Ember 1.4.
deltixrejectcode	INT4	120	Reject code in Deltix classification. Since Ember 1.4.
modulekey	VARCHAR(128)		Module key
portfoliokey	VARCHAR(128)		Portfolio key
party	VARCHAR(128)		Party
clearingbroker	VARCHAR(128)		Clearing broker
settlementdate	TIMESTAMP		Settlement date (when reported)
userdata	VARCHAR(max)		User-provided order tag
attributes	VARCHAR(max)	[{"key":6001,"value":"4h"}]	Custom order attributes

Messages Table

The Messages table records all order-related activity in real time. See Identifier naming (tables vs columns) above for how names appear in Redshift.

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 Ember, refer to the Trading Data Model document.

Column	Type	Example	Description
type	VARCHAR	OrderTradeReportEvent	Identifies type of message (string; allowed values are defined in the trading model). See Trading Data Model for list of event types.
term	INT8	1546300800	Identifies sequence term. See “Message Identity” section above. Since: Ember 1.4
sequence	INT8	312321312	Unique number that represents ES message sequence, can be used as unique synthetic timestamp. See “Message Identity” section above.
timestamp	TIMESTAMP	2019-02-27 16:51:48.123	Message timestamp
sourceid	CHAR(10)	CLIENT52	Order source, ALPHANUMERIC(10)
destinationid	CHAR(10)	TWAP	Order destination, ALPHANUMERIC(10)
orderid	VARCHAR	ICAP1983EE23	Identifies order for Ember, unique per-source.
originalorderid	VARCHAR	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	VARCHAR	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	CHAR(10)	CONTROL	Identifies source of parent order (optional)
parentorderid	VARCHAR	ICAP321XX1	Identifies parent order (optional)
requestid	VARCHAR	XCL#554	For order cancel request, as well as cancel ACK and cancel NACK events this field identifies specific cancel request.
externalorderid	VARCHAR	ZZ132131	Optional order identifier assigned to the order by execution venue
eventid	VARCHAR	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 the context of single order. NOTE: OMS is responsible for filtering out duplicate events before they reach data warehouse or other downstream consumers.
referenceeventid	VARCHAR		Used by trade correction and cancellation events to identify previously communicated event that has to be corrected or cancelled.
orderstatus	VARCHAR	PARTIALLY_FILLED	Order status (available for order events only; string; allowed values are defined in the trading model).
symbol	VARCHAR	EUR/USD	Order symbol (in symbology configured inside Deltix system)
instrumenttype	VARCHAR	FX	Instrument type (string; allowed values are defined in the trading model)
currency	CHAR(10)	USD	Order currency. Optional. Usually used only for orders that use term currency (rather than base currency).
exchange	CHAR(10)	HOTSPOT	Destination exchange (if available) for outbound messages and source exchange for inbound messages. For example, fills will report their exchange in this field.
trader	VARCHAR	jdoe	Identifies trader who submitted this order
account	VARCHAR	Gold	Identifies order account
clearingaccount	VARCHAR		Clearing account (when applicable)
side	VARCHAR	BUY	Order side (string; allowed values are defined in the trading model)
timeinforce	VARCHAR	GOOD_TILL_CANCEL	Order time in force condition (string; allowed values are defined in the trading model)
expiretime	TIMESTAMP	2019-02-27 17:00:00.000	Order expiration time (only for GOOD_TILL_DATE orders)
quantity	FLOAT8	100.50	Order quantity
minquantity	FLOAT8	10	Minimum quantity to execute (optional order request attribute)
displayquantity	FLOAT8	5	Display quantity / max floor (optional order request attribute; venue-dependent semantics)
ordertype	VARCHAR	LIMIT	Order type (string; allowed values are defined in the trading model)
limitprice	FLOAT8	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
stopprice	FLOAT8	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
pegdifference	FLOAT8	0.03	Peg offset, in order money. Optional attribute for PEGGED order types.
averageprice	FLOAT8	1.325	Average execution price (order events only).
cumulativequantity	FLOAT8	25	Cumulative executed quantity  (order events only).
remainingquantity	FLOAT8	75.50	Remaining order quantity (part of original order quantity that is still working on the market)
tradeprice	FLOAT8	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).
tradequantity	FLOAT8	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).
commission	FLOAT8	0.0001	Trade commission (when known)
commissioncurrency	CHAR(10)	USD	Trade commission currency (when known, by default assume order currency)
counterpartysourceid	CHAR(10)	JOHN	Identifies source of other side of the trade (when reported)
counterpartyorderid	VARCHAR	FED76123155	Identifies other side of the trade (when reported)
settlementdate	TIMESTAMP		Trade settlement date (when reported)
tradedate	TIMESTAMP		Trade date (when reported)
reason	VARCHAR(max)	Market is closed	Reason communicated for cancel or reject events.
vendorrejectcode	INT4	1003	Vendor specific reject code. For example CME’s.
deltixrejectcode	INT4	120	Reject code in Deltix classification
multilegreportingtype	VARCHAR		Used for trade reports when order instrument is exchange traded-synthetic. Identifies single-leg trade or whole contract trade of multi-legged security (string; allowed values are defined in the trading model).
aggressorside	VARCHAR		Reports our side as passive or aggressive role in this trade (string; allowed values are defined in the trading model).
orderunknown	BOOLEAN	false	Flag used by order Cancel Reject events.
canceltype	VARCHAR		Used by Cancel events (string; allowed values are defined in the trading model).
execrestatementreason	VARCHAR		Used by Order Restate Events to classify restate type (string; allowed values are defined in the trading model)
flags	INT4	3	Order flags. Bitmask containing various order flags. For example, bit 0 marks manual order.
userdata	VARCHAR(max)	Foo152	User-provided order tag
modulekey	VARCHAR(128)		Module key
portfoliokey	VARCHAR(128)		Portfolio key
party	VARCHAR(128)		Party
clearingbroker	VARCHAR(128)		Clearing broker
attributes	VARCHAR(max)	[{"key":6001,"value":"4h"},{"key":6002,"value":"FAST"}]	Custom order attributes (key corresponds to custom FIX tags specified during order submission)

Changelog

Ember Version 1.4

• Added the TERM (Int64) column to Orders and Messages tables.
• Added fields OPENSEQUENCE (Int64), DeltixRejectCode (Int32), VendorRejectCode (Int32) columns into Orders table.
• Renamed field Text to Reason in Orders table.
• Rename field Sequence to CloseSequence in Orders table.

Ember Version 1.1

• Table Orders had datatype of CURRENCY field changed from VARCHAR to CHAR(10).
• Table Messages had field CounterpartyId split into CounterPartySourceId and CounterPartyOrderId fields.