ClickHouse

Server

Please refer to Yandex’ official documentation here.

An official image is available from Docker Hub.

Client

Clickhouse ships with a command-line interface named clickhouse-client.

Adapter

The roq-clickhouse adapter can be started like this

1 roq-clickhouse \
2     --name clickhouse \
3     --uri http://localhost:8123 \
4     --database roq \
5     ~/event-logs/

This assumes a database server running on the same host and gateway event-logs being written to the home directoy.

The adapter will monitor the event-log directory tree and automatically export updates to ClickHouse.

For further details, please refer to the reference documentation here.

Admin

Create

To create a database

1 :) create database roq

Statistics

Measurements

Get a list of tables

1 :) show tables
 1 ┌─name─────────────────┐
 2 │ cancel_order         │
 3 │ create_order         │
 4 │ funds_update         │
 5 │ market_by_order      │
 6 │ market_by_price      │
 7 │ market_data_status   │
 8 │ market_status        │
 9 │ modify_order         │
10 │ order_ack            │
11 │ order_manager_status │
12 │ order_update         │
13 │ position_update      │
14 │ reference_data       │
15 │ statistics           │
16 │ top_of_book          │
17 │ trade_summary        │
18 │ trade_update         │
19 └──────────────────────┘

Fields

Get a list of the fields

1 :) desc statistics
 1 ┌─name───────────────────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
 2 │ source                 │ UInt8                  │              │                    │         │                  │                │
 3 │ source_name            │ LowCardinality(String) │              │                    │         │                  │                │
 4 │ source_session_id      │ UUID                   │              │                    │         │                  │                │
 5 │ source_seqno           │ UInt64                 │              │                    │         │                  │                │
 6 │ receive_time_utc       │ DateTime64(9)          │              │                    │         │                  │                │
 7 │ receive_time           │ DateTime64(9)          │              │                    │         │                  │                │
 8 │ source_send_time       │ DateTime64(9)          │              │                    │         │                  │                │
 9 │ source_receive_time    │ DateTime64(9)          │              │                    │         │                  │                │
10 │ origin_create_time     │ DateTime64(9)          │              │                    │         │                  │                │
11 │ origin_create_time_utc │ DateTime64(9)          │              │                    │         │                  │                │
12 │ is_last                │ Int8                   │              │                    │         │                  │                │
13 │ exchange               │ LowCardinality(String) │              │                    │         │                  │                │
14 │ symbol                 │ LowCardinality(String) │              │                    │         │                  │                │
15 │ type                   │ LowCardinality(String) │              │                    │         │                  │                │
16 │ value                  │ Float64                │              │                    │         │                  │                │
17 │ snapshot               │ Int8                   │              │                    │         │                  │                │
18 │ exchange_time_utc      │ DateTime64(9)          │              │                    │         │                  │                │
19 └────────────────────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Count

Get a row-count for a table

1 :) select count(*) from market_by_price
1 ┌─count()─┐
2 │  515918 │
3 └─────────┘

Query

Reference Data

We select the latest values for each column.

1 TODO

Top of Book (ToB)

Table: market_by_price

 1 SELECT *
 2 FROM
 3 (
 4   SELECT
 5     toStartOfMinute(receive_time_utc) AS time,
 6     argMax(price_0, receive_time_utc) AS bid_price,
 7     argMax(quantity_0, receive_time_utc) AS bid_quantity
 8   FROM market_by_price
 9   WHERE
10     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
11     symbol = 'BTC-PERPETUAL' AND
12     side = 'BUY'
13   GROUP BY time
14 ) AS lhs
15 FULL OUTER JOIN
16 (
17   SELECT
18     toStartOfMinute(receive_time_utc) AS time,
19     argMax(price_0, receive_time_utc) AS ask_price,
20     argMax(quantity_0, receive_time_utc) AS ask_quantity
21   FROM market_by_price
22   WHERE
23     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
24     symbol = 'BTC-PERPETUAL' AND
25     side = 'SELL'
26   GROUP BY time
27 ) AS rhs
28 USING time
29 ORDER BY time
 1 Query id: 0a1bc599-fb30-4ea9-89d2-492887cda53d
 2
 3 ┌────────────────time─┬─bid_price─┬─bid_quantity─┬─ask_price─┬─ask_quantity─┐
 4 │ 2020-11-24 12:44:00 │     19153 │           30 │   19158.5 │           82 │
 5 │ 2020-11-24 12:45:00 │   19181.5 │            3 │   19187.5 │          211 │
 6 │ 2020-11-24 12:46:00 │     19206 │         2592 │   19212.5 │          902 │
 7 │ 2020-11-24 12:47:00 │   19185.5 │            5 │   19187.5 │        23391 │
 8 │ 2020-11-24 12:48:00 │     19212 │          451 │     19214 │          200 │
 9 │ 2020-11-24 12:49:00 │     19226 │        20024 │     19225 │           64 │
10 │ 2020-11-24 12:50:00 │   19224.5 │         6000 │   19227.5 │         5000 │
11 │ 2020-11-24 12:51:00 │   19210.5 │           10 │     19209 │         4913 │
12 │ 2020-11-24 12:52:00 │   19213.5 │         1833 │   19196.5 │          183 │
13 │ 2020-11-24 12:53:00 │     19192 │         3750 │   19197.5 │          142 │
14 └─────────────────────┴───────────┴──────────────┴───────────┴──────────────┘
15
16 10 rows in set. Elapsed: 0.042 sec. Processed 132.41 thousand rows, 3.70 MB (3.18 million rows/s., 88.71 MB/s.)

Weighted Mid Price

Table: market_by_price

InfluxQL

 1 SELECT time, (bp * aq + ap * bq) / (bq + aq) AS mid
 2 FROM
 3 (
 4   SELECT
 5     toStartOfMinute(receive_time_utc) AS time,
 6     argMax(price_0, receive_time_utc) AS bp,
 7     argMax(quantity_0, receive_time_utc) AS bq
 8   FROM market_by_price
 9   WHERE
10     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
11     symbol = 'BTC-PERPETUAL' AND
12     side = 'BUY'
13   GROUP BY time
14 ) AS lhs
15 FULL OUTER JOIN
16 (
17   SELECT
18     toStartOfMinute(receive_time_utc) AS time,
19     argMax(price_0, receive_time_utc) AS ap,
20     argMax(quantity_0, receive_time_utc) AS aq
21   FROM market_by_price
22   WHERE
23     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
24     symbol = 'BTC-PERPETUAL' AND
25     side = 'SELL'
26   GROUP BY time
27 ) AS rhs
28 USING time
29 ORDER BY time
 1 Query id: 4c496adc-bb47-4ab7-908b-14a9c417f8e8
 2
 3 ┌────────────────time─┬────────────────mid─┐
 4 │ 2020-11-24 14:50:00 │ 19406.441259360658 │
 5 │ 2020-11-24 14:51:00 │              19420 │
 6 │ 2020-11-24 14:52:00 │ 19412.658139534884 │
 7 │ 2020-11-24 14:53:00 │  19402.64179755672 │
 8 │ 2020-11-24 14:54:00 │ 19369.948668745503 │
 9 │ 2020-11-24 14:55:00 │ 19371.732090769765 │
10 │ 2020-11-24 14:56:00 │  19369.91850302185 │
11 │ 2020-11-24 14:57:00 │ 19392.021276595744 │
12 │ 2020-11-24 14:58:00 │ 19376.514453961456 │
13 │ 2020-11-24 14:59:00 │ 19410.864810562573 │
14 └─────────────────────┴────────────────────┘
15
16 10 rows in set. Elapsed: 0.035 sec. Processed 64.33 thousand rows, 1.80 MB (1.85 million rows/s., 51.74 MB/s.)

Open-High-Low-Close (OHLC)

Table: trade_summary

 1 SELECT
 2   toStartOfMinute(receive_time_utc) AS time,
 3   argMin(price_vwap, receive_time_utc) AS open,
 4   max(price_vwap) AS high,
 5   min(price_vwap) AS low,
 6   argMax(price_vwap, receive_time_utc) AS close,
 7   sum(quantity) AS volume
 8 FROM trade_summary
 9 WHERE
10   receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
11   symbol = 'BTC-PERPETUAL'
12 GROUP BY time
13 ORDER BY time
 1 Query id: f2bb365c-7156-4473-a100-43225d20fa8f
 2
 3 ┌────────────────time─┬───────────────open─┬───────────────high─┬────────────────low─┬──────────────close─┬─volume─┐
 4 │ 2020-11-24 12:51:00 │            19208.5 │            19210.5 │            19198.5 │              19205 │     84 │
 5 │ 2020-11-24 12:52:00 │              19202 │            19213.5 │            19191.5 │            19209.5 │   4924 │
 6 │ 2020-11-24 12:53:00 │              19170 │              19197 │              19155 │              19171 │    891 │
 7 │ 2020-11-24 12:54:00 │            19115.5 │ 19148.002976190477 │ 19101.772727272728 │ 19101.772727272728 │  18887 │
 8 │ 2020-11-24 12:55:00 │ 19158.090909090908 │  19167.94827586207 │            19134.5 │              19160 │   2900 │
 9 │ 2020-11-24 12:56:00 │  19190.86111111111 │              19195 │        19160.78125 │            19182.5 │  12513 │
10 │ 2020-11-24 12:57:00 │            19196.5 │              19209 │              19189 │            19202.5 │  14413 │
11 │ 2020-11-24 12:58:00 │            19203.5 │            19219.5 │            19203.5 │            19214.5 │   6541 │
12 │ 2020-11-24 12:59:00 │            19195.5 │            19210.5 │            19193.5 │            19210.5 │   8520 │
13 │ 2020-11-24 13:00:00 │ 19204.471698113208 │            19241.5 │              19203 │ 19222.346514047866 │  38626 │
14 │ 2020-11-24 13:01:00 │              19246 │            19248.5 │              19240 │              19240 │    470 │
15 └─────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────┘
16
17 11 rows in set. Elapsed: 0.014 sec. Processed 2.18 thousand rows, 58.22 KB (155.42 thousand rows/s., 4.15 MB/s.)

Volume Weighted Average Price (VWAP)

Table: trade_summary

 1 SELECT time, sum(pq) / sum(q) AS vwap
 2 FROM
 3 (
 4 SELECT
 5   toStartOfMinute(receive_time_utc) AS time,
 6   price_vwap * quantity AS pq,
 7   quantity AS q
 8 FROM trade_summary
 9 WHERE
10   receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
11   symbol = 'BTC-PERPETUAL'
12 )
13 GROUP BY time
14 ORDER BY time
 1 Query id: ffe10a04-cf0c-4fdb-96cd-c98d9ebc145a
 2
 3 ┌────────────────time─┬───────────────vwap─┐
 4 │ 2020-11-24 14:44:00 │            19353.5 │
 5 │ 2020-11-24 14:45:00 │ 19371.794527515005 │
 6 │ 2020-11-24 14:46:00 │  19390.92057004472 │
 7 │ 2020-11-24 14:47:00 │ 19389.726553672317 │
 8 │ 2020-11-24 14:48:00 │  19179.58668107794 │
 9 │ 2020-11-24 14:49:00 │ 19401.001082201066 │
10 │ 2020-11-24 14:50:00 │ 19396.412028073573 │
11 │ 2020-11-24 14:51:00 │ 19418.522510474337 │
12 │ 2020-11-24 14:52:00 │ 19418.662779620932 │
13 │ 2020-11-24 14:53:00 │  19400.75527900973 │
14 │ 2020-11-24 14:54:00 │  19373.95548122349 │
15 └─────────────────────┴────────────────────┘
16
17 11 rows in set. Elapsed: 0.014 sec. Processed 6.37 thousand rows, 163.83 KB (460.50 thousand rows/s., 11.85 MB/s.)

Index Values (Deribit specific)

Note

Maybe there is a better way to create pivot tables?

Table: statistics

 1 SELECT time, index_value, settlement_price
 2 FROM
 3 (
 4   SELECT
 5     toStartOfMinute(receive_time_utc) AS time,
 6     argMax(value, receive_time_utc) AS index_value
 7   FROM statistics
 8   WHERE
 9     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
10     symbol = 'BTC-DERIBIT-INDEX' AND
11     type = 'INDEX_VALUE'
12   GROUP BY time
13 ) AS col1
14 FULL OUTER JOIN
15 (
16   SELECT
17     toStartOfMinute(receive_time_utc) AS time,
18     argMax(value, receive_time_utc) AS settlement_price
19   FROM statistics
20   WHERE
21     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
22     symbol = 'BTC-DERIBIT-INDEX' AND
23     type = 'SETTLEMENT_PRICE'
24   GROUP BY time
25 ) AS col2
26 USING time
27 ORDER BY time
 1 Query id: c40bbcd9-2b80-42a7-bd07-5bedb52ff682
 2
 3 ┌────────────────time─┬─index_value─┬─settlement_price─┐
 4 │ 2020-11-24 14:38:00 │       19264 │                0 │
 5 │ 2020-11-24 14:39:00 │    19262.98 │                0 │
 6 │ 2020-11-24 14:40:00 │    19295.48 │                0 │
 7 │ 2020-11-24 14:41:00 │    19295.75 │                0 │
 8 │ 2020-11-24 14:42:00 │    19287.67 │                0 │
 9 │ 2020-11-24 14:43:00 │     19307.2 │                0 │
10 │ 2020-11-24 14:44:00 │    19316.26 │                0 │
11 │ 2020-11-24 14:45:00 │    19321.46 │                0 │
12 │ 2020-11-24 14:46:00 │    19373.23 │                0 │
13 │ 2020-11-24 14:47:00 │    19373.19 │         19373.19 │
14 │ 2020-11-24 14:48:00 │    19371.75 │         19371.75 │
15 └─────────────────────┴─────────────┴──────────────────┘
16
17 11 rows in set. Elapsed: 0.027 sec. Processed 86.19 thousand rows, 902.06 KB (3.15 million rows/s., 32.93 MB/s.)

Options

Table: reference_data

 1 SELECT
 2   symbol,
 3   argMax(settlement_currency, receive_time_utc) AS settlement_currency,
 4   argMax(strike_price, receive_time_utc) AS strike_price,
 5   argMax(tick_size, receive_time_utc) AS tick_size,
 6   argMax(multiplier, receive_time_utc) AS multiplier
 7 FROM reference_data
 8 WHERE
 9   exchange='deribit' AND
10   security_type='OPTION' AND
11   option_type='CALL' AND
12   currency='BTC'
13 GROUP BY symbol
14 ORDER BY symbol
 1 Query id: 4327bcd9-0bc4-4b53-a56f-4defdbdf7ea6
 2
 3 ┌─symbol──────────────┬─settlement_currency─┬─strike_price─┬─────────────tick_size─┬─multiplier─┐
 4 │ BTC-11DEC20-14250-C │ USD                 │        14250 │ 0.0005000000000000001 │          1 │
 5 │ BTC-11DEC20-14500-C │ USD                 │        14500 │ 0.0005000000000000001 │          1 │
 6 │ BTC-11DEC20-14750-C │ USD                 │        14750 │ 0.0005000000000000001 │          1 │
 7 │ BTC-11DEC20-15000-C │ USD                 │        15000 │ 0.0005000000000000001 │          1 │
 8 │ BTC-11DEC20-15250-C │ USD                 │        15250 │ 0.0005000000000000001 │          1 │
 9 │ BTC-11DEC20-15500-C │ USD                 │        15500 │ 0.0005000000000000001 │          1 │
10 │ BTC-11DEC20-15750-C │ USD                 │        15750 │ 0.0005000000000000001 │          1 │
11 │ BTC-11DEC20-16000-C │ USD                 │        16000 │ 0.0005000000000000001 │          1 │
12 │ BTC-11DEC20-16250-C │ USD                 │        16250 │ 0.0005000000000000001 │          1 │
13 │ BTC-11DEC20-16500-C │ USD                 │        16500 │ 0.0005000000000000001 │          1 │
14 ...
15 │ BTC-4DEC20-20000-C  │ USD                 │        20000 │ 0.0005000000000000001 │          1 │
16 │ BTC-4DEC20-20250-C  │ USD                 │        20250 │ 0.0005000000000000001 │          1 │
17 │ BTC-4DEC20-20500-C  │ USD                 │        20500 │ 0.0005000000000000001 │          1 │
18 │ BTC-4DEC20-20750-C  │ USD                 │        20750 │ 0.0005000000000000001 │          1 │
19 │ BTC-4DEC20-21000-C  │ USD                 │        21000 │ 0.0005000000000000001 │          1 │
20 │ BTC-4DEC20-21250-C  │ USD                 │        21250 │ 0.0005000000000000001 │          1 │
21 │ BTC-4DEC20-21500-C  │ USD                 │        21500 │ 0.0005000000000000001 │          1 │
22 │ BTC-4DEC20-21750-C  │ USD                 │        21750 │ 0.0005000000000000001 │          1 │
23 │ BTC-4DEC20-22000-C  │ USD                 │        22000 │ 0.0005000000000000001 │          1 │
24 │ BTC-4DEC20-22250-C  │ USD                 │        22250 │ 0.0005000000000000001 │          1 │
25 └─────────────────────┴─────────────────────┴──────────────┴───────────────────────┴────────────┘
26
27 203 rows in set. Elapsed: 0.012 sec. Processed 1.05 thousand rows, 58.85 KB (85.43 thousand rows/s., 4.80 MB/s.)