Streaming Orders and History into ClickHouse

Overview

This document explains how to configure and use ClickHouse as data warehouse for the Execution Server (ES). ClickHouse is an open source, column-oriented database management system, capable of generating real-time analytical data reports using SQL queries.

Install ClickHouse

To install and run ClickHouse on CentOS, run the following code:

sudo yum install -y curl
curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
sudo yum install -y clickhouse-server clickhouse-client

Configure ClickHouse

When connecting a client to work with the data, allow ClickHouse to accept incoming connections by editing the /etc/clickhouse-server/config.xml configuration file:

Locate the listen_host configuration element.

Uncomment it and set it to the local IP address of your server using the following code:

<listen_host>**_10.0.0.59_**</listen_host>

If you plan to use the Tabbix GUI to access ClickHouse data, find and uncomment the following fragment:

<http_server_default_response><![CDATA[<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"></script></body></html>]]></http_server_default_response>

Define two users:
- ember: For the Execution Server to load data into ClickHouse.
- webviewer: A read-only user that will have remote access to loaded data.

A sample user configuration file can be found in Appendix C. Make sure to change the highlighted areas of the sample.

Security

Like any database, ClickHouse needs to be secured. Ideally, you should only access it from the local network. If you absolutely must connect to ClickHouse via the Internet, follow these guidelines:

Limit your firewall's ClickHouse port to known IP addresses (whitelist source IPs for accessing the ClickHouse web interface). For AWS, this means a Security Group rule with specific source IPs. Do not rely on ClickHouse user network settings alone.
Enable SSL using ClickHouse SSL configuration or by placing it behind an SSL proxy like Amazon Load Balancer with TLS and an AWS-managed certificate.
Use a strong password for the ClickHouse web user.
As an additional measure of security, place a ClickHouse web port behind NGINX. Doing so can provide additional protection from some HTTP attacks and stronger access logs.

Please be advised that a distributed setup of ClickHouse requires a "default" user with an empty password. Thus, the configuration settings for access restriction are applicable only for a single server setup.

Run ClickHouse

To run ClickHouse as a service, use the folllowing code:

sudo service clickhouse-server start

Verify Installation

To verify your installation, execute a simple command like “SELECT 1” using the ClickHouse CLI client:

sudo clickhouse-client

Connect to Tabix

Tabix is an open source SQL editor GUI for ClickHouse.

To connect your production setup to Tabix:

Enter a name for your setup.
For the port, use a public DNS name or the IP address of your ClickHouse installation.
Make sure that this port is open in your server’s firewall and security group.
In the Login field, add a user.
Enter your password.
In the Extend params query field, enter key1=value&key2=value.
Click Sign In.

In the following screenshot, we use default port 8123 and a default user to connect to Tabix.

Clickhose connection form

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 {
  clickhouse {          # 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 = 5s   # repeatedly commits not full batch at the specified period (5s is default)

    messages = [                               
      ${template.warehouse.clickhouse.messages} {   # loader which loads order messages
        filter.settings.inclusion = null       # EVENTS or TRADES, null means no filter
        
        loader.settings {          # default   # description
          host = "10.10.87.123"    # -         # server host or ip
          port = 8123              # 8123      # server port
     
          username = "john"        # -         # connection credentials
          password = "******"      # -         # connection credentials

          databaseName = "ember"   # ember     # database name   
          tableName = "messages"   # messages  # table name

          createDatabase = true    # true      # create database if not exists
          createTable = true       # true      # create table if not exists
          dropTable = false        # false     # drop table if exists

          batchLimit = 64K         # 64K       # gathers a batch and sends it once it is full   
        }
      }
    ]

    orders = [                               
      ${template.warehouse.clickhouse.orders} {     # loader which loads closed orders
        loader.settings {          # default   # description
          host = "10.10.87.123"    # -         # server host or ip
          port = 8123              # 8123      # server port
     
          username = "john"        # -         # connection credentials
          password = "doe"         # -         # connection credentials

          databaseName = "ember"   # ember     # database name   
          tableName = "orders"     # orders    # table name

          createDatabase = true    # true      # create database if not exists
          createTable = true       # true      # create table if not exists
          dropTable = false        # false     # drop table if exists

          batchLimit = 64K         # 64K       # gathers a batch and sends it once it is full      
        }
      }
    ]
  }
}

Start Exporting Data

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

note

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

In batch mode, the following script exits as soon as all recent data is exported:

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

If you re-run the script, it appends any new data that the Execution Server accumulated since its last export.

Clickhouse tables

Sample Queries

select distinct SourceId, DestinationId, OrderType from orders

select sum(( Side == 'BUY' ? CumulativeQuantity : -CumulativeQuantity))
from orders where CumulativeQuantity != 0 and Trader = 'OE51' and Account = 'Gold' limit 100

select * from messages where Type ='OrderTradeReportEvent' limit 100

select distinct SourceId, DestinationId, OrderType from orders

select sum(( Side == 'BUY' ? CumulativeQuantity : -CumulativeQuantity))  
    from orders where CumulativeQuantity != 0 and Trader = 'OE51' and Account = 'Gold'

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.

Real-life Examples

Messages Table Cleanup

In this example, a client accumulated massive amounts of messages in the database and decided to reduce the dataset to trades only instead of scaling ClickHouse up:

create table ember.messagestemp (Type Enum8(''=-1,'OrderNewRequest'=0,'OrderReplaceRequest'=1,'OrderCancelRequest'=2,'OrderStatusRequest'=3,'OrderDiscardRequest'=4,'OrderPendingNewEvent'=5,'OrderNewEvent'=6,'OrderRejectEvent'=7,'OrderPendingCancelEvent'=8,'OrderCancelEvent'=9,'OrderCancelRejectEvent'=10,'OrderPendingReplaceEvent'=11,'OrderReplaceEvent'=12,'OrderReplaceRejectEvent'=13,'OrderTradeReportEvent'=14,'OrderTradeCancelEvent'=15,'OrderTradeCorrectEvent'=16,'OrderStatusEvent'=17,'OrderRestateEvent'=18),Term Int64,Sequence Int64,Timestamp DateTime,SourceId String,DestinationId String,OrderId String,OriginalOrderId String,CorrelationOrderId String,ParentSourceId String,ParentOrderId String,RequestId String,ExternalOrderId String,EventId String,ReferenceEventId String,OrderStatus Enum8(''=-1,'PENDING_NEW'=0,'NEW'=1,'REJECTED'=2,'PENDING_CANCEL'=3,'CANCELED'=4,'PENDING_REPLACE'=5,'REPLACED'=6,'PARTIALLY_FILLED'=7,'COMPLETELY_FILLED'=8,'EXPIRED'=9,'SUSPENDED'=10),Symbol String,InstrumentType Enum8(''=-1,'EQUITY'=0,'ETF'=1,'BOND'=2,'INDEX'=3,'FX'=4,'OPTION'=5,'FUTURE'=6,'SYNTHETIC'=7,'CUSTOM'=8),Currency String,ExchangeId String,TraderId String,Account String,ClearingAccount String,Side Enum8(''=-1,'BUY'=0,'SELL'=1,'SELL_SHORT'=2,'SELL_SHORT_EXEMPT'=3),TimeInForce Enum8(''=-1,'DAY'=0,'GOOD_TILL_CANCEL'=1,'AT_THE_OPENING'=2,'IMMEDIATE_OR_CANCEL'=3,'FILL_OR_KILL'=4,'GOOD_TILL_CROSSING'=5,'GOOD_TILL_DATE'=6,'AT_THE_CLOSE'=7),ExpireTime DateTime,Quantity Nullable(Decimal128(12)),MinQuantity Nullable(Decimal128(12)),DisplayQuantity Nullable(Decimal128(12)),OrderType Enum8(''=-1,'CUSTOM'=0,'MARKET'=1,'LIMIT'=2,'STOP'=3,'STOP_LIMIT'=4,'PEG_TO_MARKET'=5,'PEG_TO_MIDPOINT'=6,'PEG_TO_PRIMARY'=7,'MARKET_ON_CLOSE'=8,'LIMIT_ON_CLOSE'=9,'LIMIT_OR_BETTER'=10,'PREVIOUSLY_QUOTED'=11),LimitPrice Nullable(Decimal128(12)),StopPrice Nullable(Decimal128(12)),PegDifference Nullable(Decimal128(12)),AveragePrice Nullable(Decimal128(12)),CumulativeQuantity Nullable(Decimal128(12)),RemainingQuantity Nullable(Decimal128(12)),TradePrice Nullable(Decimal128(12)),TradeQuantity Nullable(Decimal128(12)),Commission Nullable(Decimal128(12)),CommissionCurrency String,CounterPartySourceId String,CounterPartyOrderId String,SettlementDate DateTime,TradeDate DateTime,Reason String,VendorRejectCode Int32,DeltixRejectCode Int32,MultiLegReportingType Enum8(''=-1,'SINGLE_SECURITY'=0,'INDIVIDUAL_LEG_SECURITY'=1,'MULTI_LEG_SECURITY'=2),AggressorSide Enum8(''=-1,'ORDER_INITIATOR_IS_PASSIVE'=0,'ORDER_INITIATOR_IS_AGGRESSOR'=1),OrderUnknown Enum8('false'=0,'true'=1),CancelType Enum8(''=-1,'GENERAL'=0,'DONE_FOR_DAY'=1,'EXPIRED'=2),ExecRestatementReason Enum8(''=-1,'GT_RENEWAL_RESTATEMENT'=0,'VERBAL_CHANGE'=1,'REPRICING_OF_ORDER'=2,'BROKER_OPTION'=3,'PARTIAL_DECLINE_OF_ORDER_QTY'=4,'CANCEL_ON_TRADING_HALT'=5,'CANCEL_ON_SYSTEM_FAILURE'=6,'MARKET_OR_EXCHANGE_OPTION'=7,'CANCELED_NOT_EST'=8,'WAREHOUSE_RECAP'=9,'OTHER'=10),Flags Int32,UserData String,Attributes Nested (Key Int32,Value String)) engine = MergeTree() order by (CorrelationOrderId)

insert into ember.messagestemp select * from ember.messages where Type = 'OrderTradeReportEvent'

rename table ember.messages to ember.messagestemp2

rename table ember.messagestemp to ember.messages

Appendix: Data Format

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 number of the message that created the order (usually OrderNewRequest). See “Message Identity” section above.
CloseSequence	312321312	Identifies 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: Column Data Types

Orders Table

Column	Type
Sequence	Int64
SourceId	String
DestinationId	String
OrderId	String
ParentSourceId	String
ParentOrderId	String
ExternalOrderId	String
Account	String
TraderId	String
Symbol	String
InstrumentType	Enum8('' = -1, 'EQUITY' = 0, 'ETF' = 1, 'BOND' = 2, 'INDEX' = 3, 'FX' = 4, 'OPTION' = 5, 'FUTURE' = 6, 'SYNTHETIC' = 7, 'CUSTOM' = 8)
ExchangeId	String
Currency	String
CurrencyCode	Nullable(Int16)
Side	Enum8('' = -1, 'BUY' = 0, 'SELL' = 1, 'SELL_SHORT' = 2, 'SELL_SHORT_EXEMPT' = 3)","",""
TimeInForce	Enum8('' = -1, 'DAY' = 0, 'GOOD_TILL_CANCEL' = 1, 'AT_THE_OPENING' = 2, 'IMMEDIATE_OR_CANCEL' = 3, 'FILL_OR_KILL' = 4, 'GOOD_TILL_CROSSING' = 5, 'GOOD_TILL_DATE' = 6, 'AT_THE_CLOSE' = 7)
ExpireTime	DateTime
OrderStatus	Enum8('' = -1, 'PENDING_NEW' = 0, 'NEW' = 1, 'REJECTED' = 2, 'PENDING_CANCEL' = 3, 'CANCELED' = 4, 'PENDING_REPLACE' = 5, 'REPLACED' = 6, 'PARTIALLY_FILLED' = 7, 'COMPLETELY_FILLED' = 8, 'EXPIRED' = 9, 'SUSPENDED' = 10)
OpenTime	DateTime
CloseTime	DateTime
OrderType	Enum8('' = -1, 'CUSTOM' = 0, 'MARKET' = 1, 'LIMIT' = 2, 'STOP' = 3, 'STOP_LIMIT' = 4, 'PEG_TO_MARKET' = 5, 'PEG_TO_MIDPOINT' = 6, 'PEG_TO_PRIMARY' = 7, 'MARKET_ON_CLOSE' = 8, 'LIMIT_ON_CLOSE' = 9, 'LIMIT_OR_BETTER' = 10, 'PREVIOUSLY_QUOTED' = 11)
LimitPrice	Nullable(Decimal(38, 12))
StopPrice	Nullable(Decimal(38, 12))
Quantity	Nullable(Decimal(38, 12))
DisplayQuantity	Nullable(Decimal(38, 12))
MinQuantity	Nullable(Decimal(38, 12))
CumulativeQuantity	Nullable(Decimal(38, 12))
AveragePrice	Nullable(Decimal(38, 12))
Text	String
UserData	String

Messages Table

Column	Type
Type	Enum8('' = -1, 'OrderNewRequest' = 0, 'OrderReplaceRequest' = 1, 'OrderCancelRequest' = 2, 'OrderStatusRequest' = 3, 'OrderDiscardRequest' = 4, 'OrderPendingNewEvent' = 5, 'OrderNewEvent' = 6, 'OrderRejectEvent' = 7, 'OrderPendingCancelEvent' = 8, 'OrderCancelEvent' = 9, 'OrderCancelRejectEvent' = 10, 'OrderPendingReplaceEvent' = 11, 'OrderReplaceEvent' = 12, 'OrderReplaceRejectEvent' = 13, 'OrderTradeReportEvent' = 14, 'OrderTradeCancelEvent' = 15, 'OrderTradeCorrectEvent' = 16, 'OrderStatusEvent' = 17, 'OrderRestateEvent' = 18)
Sequence	Int64
Timestamp	DateTime
SourceId	String
DestinationId	String
OrderId	String
OriginalOrderId	String
CorrelationOrderId	String
ParentSourceId	String
ParentOrderId	String
RequestId	String
ExternalOrderId	String
EventId	String
ReferenceEventId	String
OrderStatus	Enum8('' = -1, 'PENDING_NEW' = 0, 'NEW' = 1, 'REJECTED' = 2, 'PENDING_CANCEL' = 3, 'CANCELED' = 4, 'PENDING_REPLACE' = 5, 'REPLACED' = 6, 'PARTIALLY_FILLED' = 7, 'COMPLETELY_FILLED' = 8, 'EXPIRED' = 9, 'SUSPENDED' = 10)
Symbol	String
InstrumentType	Enum8('' = -1, 'EQUITY' = 0, 'ETF' = 1, 'BOND' = 2, 'INDEX' = 3, 'FX' = 4, 'OPTION' = 5, 'FUTURE' = 6, 'SYNTHETIC' = 7, 'CUSTOM' = 8)
Currency	String
ExchangeId	String
TraderId	String
Account	String
Side	Enum8('' = -1, 'BUY' = 0, 'SELL' = 1, 'SELL_SHORT' = 2, 'SELL_SHORT_EXEMPT' = 3)
TimeInForce	Enum8('' = -1, 'DAY' = 0, 'GOOD_TILL_CANCEL' = 1, 'AT_THE_OPENING' = 2, 'IMMEDIATE_OR_CANCEL' = 3, 'FILL_OR_KILL' = 4, 'GOOD_TILL_CROSSING' = 5, 'GOOD_TILL_DATE' = 6, 'AT_THE_CLOSE' = 7)
ExpireTime	DateTime
Quantity	Nullable(Decimal(38, 12))
MinQuantity	Nullable(Decimal(38, 12))
DisplayQuantity	Nullable(Decimal(38, 12))
OrderType	Enum8('' = -1, 'CUSTOM' = 0, 'MARKET' = 1, 'LIMIT' = 2, 'STOP' = 3, 'STOP_LIMIT' = 4, 'PEG_TO_MARKET' = 5, 'PEG_TO_MIDPOINT' = 6, 'PEG_TO_PRIMARY' = 7, 'MARKET_ON_CLOSE' = 8, 'LIMIT_ON_CLOSE' = 9, 'LIMIT_OR_BETTER' = 10, 'PREVIOUSLY_QUOTED' = 11)
LimitPrice	Nullable(Decimal(38, 12))
StopPrice	Nullable(Decimal(38, 12))
PegDifference	Nullable(Decimal(38, 12))
AveragePrice	Nullable(Decimal(38, 12))
CumulativeQuantity	Nullable(Decimal(38, 12))
RemainingQuantity	Nullable(Decimal(38, 12))
TradePrice	Nullable(Decimal(38, 12))
TradeQuantity	Nullable(Decimal(38, 12))
Commission	Nullable(Decimal(38, 12))
CommissionCurrency	String
CounterpartyId	String
SettlementDate	DateTime
TradeDate	DateTime
Reason	String
VendorRejectCode	Int32
DeltixRejectCode	Int32
MultiLegReportingType	Enum8('' = -1, 'SINGLE_SECURITY' = 0, 'INDIVIDUAL_LEG_SECURITY' = 1, 'MULTI_LEG_SECURITY' = 2)
AggressorSide	Enum8('' = -1, 'ORDER_INITIATOR_IS_PASSIVE' = 0, 'ORDER_INITIATOR_IS_AGGRESSOR' = 1)
OrderUnknown	Enum8('false' = 0, 'true' = 1)
CancelType	Enum8('' = -1, 'GENERAL' = 0, 'DONE_FOR_DAY' = 1, 'EXPIRED' = 2)
ExecRestatementReason	Enum8('' = -1, 'GT_RENEWAL_RESTATEMENT' = 0, 'VERBAL_CHANGE' = 1, 'REPRICING_OF_ORDER' = 2, 'BROKER_OPTION' = 3, 'PARTIAL_DECLINE_OF_ORDER_QTY' = 4, 'CANCEL_ON_TRADING_HALT' = 5, 'CANCEL_ON_SYSTEM_FAILURE' = 6, 'MARKET_OR_EXCHANGE_OPTION' = 7, 'CANCELED_NOT_EST' = 8, 'WAREHOUSE_RECAP' = 9, 'OTHER' = 10)
Flags	Int32
UserData	String
Attributes.Key	Array(Int32)
Attributes.Value	Array(String)

Appendix C: ClickHouse User Database Sample

<?xml version="1.0"?>
<yandex>
  <profiles>
    <default>
      <!-- Maximum memory usage for processing single query, in bytes. -->
      <max_memory_usage>10000000000</max_memory_usage>

      <!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. -->
      <use_uncompressed_cache>0</use_uncompressed_cache>

      <!-- How to choose between replicas during distributed query processing.
         random - choose random replica from set of replicas with minimum number of errors
         nearest_hostname - from set of replicas with minimum number of errors, choose replica
          with minimum number of different symbols between replica's hostname and local hostname
          (Hamming distance).
         in_order - first live replica is chosen in specified order.
      -->
      <load_balancing>random</load_balancing>
    </default>

    <!-- Profile that allows only read queries. -->
    <readonly>
      <readonly>2</readonly> <!-- read data and change settings queries are allowed -->
    </readonly>
  </profiles>
  <users>
    <ember>
      <!-- TODO: Put an actual password here -->
      <password>EMBERUSERPASSWORD</password>
      <networks replace="replace">
        <!-- List of networks with open access.-->
        <!-- TODO: Specify here IP of ember-->
        <ip>127.0.0.1</ip>
      </networks>
      <profile>default</profile>
      <quota>default</quota>
    </ember>
    <webviewer>
      <!-- TODO: Put an actual password here -->
      <password>WEBVIEWERPASSWORD</password>
      <networks replace="replace">
        <!-- List of networks with open access.-->
        <!-- TODO: Specify here company public subnet addresses-->
        <ip>::/0</ip>
        <ip>::1</ip>
        <ip>127.0.0.1</ip>
        <ip>217.23.114.0/24</ip>
      </networks>
      <profile>readonly</profile>
      <quota>default</quota>
    </webviewer>
  </users>

  <!-- Quotas. -->
  <quotas>
    <!-- Name of quota. -->
    <default>
      <!-- Limits for time interval. You could specify many intervals with different limits. -->
      <interval>
        <!-- Length of interval. -->
        <duration>3600</duration>

        <!-- No limits. Just calculate resource usage for time interval. -->
        <queries>0</queries>
        <errors>0</errors>
        <result_rows>0</result_rows>
        <read_rows>0</read_rows>
        <execution_time>0</execution_time>
      </interval>
    </default>
  </quotas>
</yandex>

References

A good resource on ClickHouse installation: https://github.com/Altinity/clickhouse-rpm-install.
Tabix GUI

Change Log

Ember Version 1.4

Added TERM (Int64) column to Orders and Messages tables.

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.

Streaming Orders and History into ClickHouse

Overview​

Install ClickHouse​

Configure ClickHouse​

Security​

Run ClickHouse​

Verify Installation​

Connect to Tabix​

Configure the Execution Server​

Start Exporting Data​

Sample Queries​

Message Identity​

Example​

Real-life Examples​

Messages Table Cleanup​

Appendix: Data Format​

Orders Table​

Messages Table​

Appendix B: Column Data Types​

Orders Table​

Messages Table​

Appendix C: ClickHouse User Database Sample​

References​

Change Log​

Ember Version 1.4​

Ember Version 1.1​

Overview

Install ClickHouse

Configure ClickHouse

Security

Run ClickHouse

Verify Installation

Connect to Tabix

Configure the Execution Server

Start Exporting Data

Sample Queries

Message Identity

Example

Real-life Examples

Messages Table Cleanup

Appendix: Data Format

Orders Table

Messages Table

Appendix B: Column Data Types

Orders Table

Messages Table

Appendix C: ClickHouse User Database Sample

References

Change Log

Ember Version 1.4

Ember Version 1.1