Streaming Orders into PostgreSQL

Overview

This document explains how to configure and use PostgreSQL as data warehouse for the Ember. The information presented here also applies to the Amazon RDS Postgres.

caution

Please be advised that the SQL-based storage is not the best data warehouse option when your order rates consistently exceed 1,000 orders per second.

Configure the Ember

The Ember can export data into the PostgreSQL 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 example below, we 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 PostgreSQL data warehouse pipeline, add the following sections to $EMBER_HOME/ember.conf.

warehouse {
  postgres { # 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
   storeActiveOrders = false # optional field. If it's set to true, ORDERS table contains active orders set and final orders set
   
   messages = [                               
      ${template.warehouse.postgres.messages} { # loader which loads order messages
        loader.settings {          
          host = localhost     
          port = 1433 
          instanceName = EMBERDB            
          username = "dbuser"        
          password = "hackMeS00N"   # use secrets store      

          databaseName = ember   
          tableName = MESSAGES   
          createDatabase = true # create database if not exists
          createTable = true    # create table if not exists
          dropTable = false     # drop table if exists
          batchLimit = 64000            
        }
      }
    ]

    orders = [                               
      ${template.warehouse.postgres.orders} { # loader which loads closed orders
        loader.settings {          
          host = localhost     
          port = 1433 
          instanceName = EMBERDB            
          username = "dbuser"        
          password = "hackMeS00N"   # use secrets store 
          databaseName = ember   
          tableName = ORDERS     

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

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.

Start Exporting Data

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

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

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

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.

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} (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.

Appendix: Data Format

This appendix provides a brief description of each column. 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)

In Ember configuration you can set table names such as MESSAGES or ORDERS. The data-warehouse export normalizes those names to lowercase, so the relations you query are typically messages and orders.

Column names are unquoted in the table definitions PostgreSQL receives, so they are stored in lowercase (for example in information_schema.columns). The column lists below use those lowercase names—the same ones you use in SELECT and WHERE without double quotes.

Orders Table

The Orders table captures the final state of each order.

Column	Type	Example	Description
term	BIGINT	1546300800	Identifies sequence term. See "Message Identity" section above. Since: Ember 1.4
opensequence	BIGINT	312321304	Identifies sequence number of the message that created the order (usually OrderNewRequest). See "Message Identity" section above. Since: Ember 1.4
closesequence	BIGINT	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(256)	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(256)	ICAP321XX1	Identifies parent order (optional)
externalorderid	VARCHAR(256)	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(256)	Gold	Identifies order account
clearingaccount	VARCHAR(256)		Clearing account (when applicable)
traderid	VARCHAR(256)	jdoe	Identifies trader who submitted this order
symbol	VARCHAR(256)	EUR/USD	Order symbol (in symbology configured inside Deltix system)
instrumenttype	VARCHAR	FX	Instrument type (string; allowed values are defined in the trading model)
exchangeid	VARCHAR(256)	HOTSPOT	Destination / venue exchange identifier (when available)
currency	VARCHAR(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 WITH TIMEZONE	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 WITH TIMEZONE	2019-02-27 16:51:48.002	Order submission time
closetime	TIMESTAMP WITH TIMEZONE	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	DECIMAL(38,12)	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
stopprice	DECIMAL(38,12)	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
quantity	DECIMAL(38,12)	10000	Order quantity
displayquantity	DECIMAL(38,12)	1000	Order display quantity (sometimes described as "max show quantity" or "max floor quantity"), where applicable.
minquantity	DECIMAL(38,12)	1000	Minimum fill quantity (where applicable).
cumulativequantity	DECIMAL(38,12)	1500.50	Cumulative filled quantity
averageprice	DECIMAL(38,12)	132.56	Average fill price
vendorrejectcode	INT	1003	Vendor specific reject code. For example CME's. Since Ember 1.4.
deltixrejectcode	INT	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 WITH TIMEZONE		Settlement date (when reported)
reason	VARCHAR	"Cancelled by user request"	For cancelled or rejected orders this field contains textual reason (unlimited varchar).
userdata	VARCHAR		User-provided order tag
attributes	VARCHAR	6002=00:15:00,6013=3.52	Custom attributes, similar to FIX tags. Comma-separated key=value pairs where keys are integers. Commas and backslashes in values are escaped with \ (e.g., a value a,b\c is stored as a\,b\\c).

Messages Table

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

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	BIGINT	1546300800	Identifies sequence term. See "Message Identity" section above. Since: Ember 1.4
sequence	BIGINT	312321312	Unique number that represents ES message sequence, can be used as unique synthetic timestamp. See "Message Identity" section above.
timestamp	TIMESTAMP WITH TIMEZONE	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(256)	ICAP1983EE23	Identifies order for Ember, unique per-source.
originalorderid	VARCHAR(256)	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(256)	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(256)	ICAP321XX1	Identifies parent order (optional)
requestid	VARCHAR(256)	XCL#554	For order cancel request, as well as cancel ACK and cancel NACK events this field identifies specific cancel request.
externalorderid	VARCHAR(256)	ZZ132131	Optional order identifier assigned to the order by execution venue
eventid	VARCHAR(256)	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(256)		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(256)	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	VARCHAR(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(256)	jdoe	Identifies trader who submitted this order
account	VARCHAR(256)	Gold	Identifies order account
clearingaccount	VARCHAR(256)		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 WITH TIMEZONE	2019-02-27 17:00:00.000	Order expiration time (only for GOOD_TILL_DATE orders)
quantity	DECIMAL(38,12)	100.50	Order quantity
minquantity	DECIMAL(38,12)	10	Minimum quantity to execute (optional order request attribute)
displayquantity	DECIMAL(38,12)	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	DECIMAL(38,12)	1.33	Limit price. Can be specified for LIMIT, STOP_LIMIT, PEGGED, or CUSTOM order types
stopprice	DECIMAL(38,12)	1.20	Stop price. Can be specified for STOP and STOP_LIMIT order types.
pegdifference	DECIMAL(38,12)	0.03	Peg offset, in order money. Optional attribute for PEGGED order types.
averageprice	DECIMAL(38,12)	1.325	Average execution price (order events only).
cumulativequantity	DECIMAL(38,12)	25	Cumulative executed quantity (order events only).
remainingquantity	DECIMAL(38,12)	75.50	Remaining order quantity (part of original order quantity that is still working on the market)
tradeprice	DECIMAL(38,12)	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	DECIMAL(38,12)	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	DECIMAL(38,12)	0.0001	Trade commission (when known)
commissioncurrency	VARCHAR(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(256)	FED76123155	Identifies other side of the trade (when reported)
settlementdate	TIMESTAMP WITH TIMEZONE		Trade settlement date (when reported)
tradedate	TIMESTAMP WITH TIMEZONE		Trade date (when reported)
reason	VARCHAR	Market is closed	Reason communicated for cancel or reject events (unlimited varchar).
vendorrejectcode	INT	1003	Vendor specific reject code. For example CME's.
deltixrejectcode	INT	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 (column is NOT NULL).
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	INT	3	Order flags. Bitmask containing various order flags. For example, bit 0 marks manual order.
userdata	VARCHAR(256)	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	6002=00:15:00,6013=3.52	Custom attributes, similar to FIX tags. Comma-separated key=value pairs where keys are integers. Commas and backslashes in values are escaped with \ (e.g., a value a,b\c is stored as a\,b\\c).

Change Log

N/A