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
2
3
4
5
 roq-clickhouse \
     --name clickhouse \
     --uri http://localhost:8123 \
     --database roq \
     ~/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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 ┌─name─────────────────┐
 │ cancel_order         │
 │ create_order         │
 │ funds_update         │
 │ market_by_order      │
 │ market_by_price      │
 │ market_data_status   │
 │ market_status        │
 │ modify_order         │
 │ order_ack            │
 │ order_manager_status │
 │ order_update         │
 │ position_update      │
 │ reference_data       │
 │ statistics           │
 │ top_of_book          │
 │ trade_summary        │
 │ trade_update         │
 └──────────────────────┘

Fields

Get a list of the fields

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

Count

Get a row-count for a table

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

Query

Reference Data

We select the latest values for each column.

1
 TODO

Top of Book (ToB)

Table: market_by_price

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 SELECT *
 FROM
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(price_0, receive_time_utc) AS bid_price,
     argMax(quantity_0, receive_time_utc) AS bid_quantity
   FROM market_by_price
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-PERPETUAL' AND
     side = 'BUY'
   GROUP BY time
 ) AS lhs
 FULL OUTER JOIN
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(price_0, receive_time_utc) AS ask_price,
     argMax(quantity_0, receive_time_utc) AS ask_quantity
   FROM market_by_price
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-PERPETUAL' AND
     side = 'SELL'
   GROUP BY time
 ) AS rhs
 USING time
 ORDER BY time
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 Query id: 0a1bc599-fb30-4ea9-89d2-492887cda53d

 ┌────────────────time─┬─bid_price─┬─bid_quantity─┬─ask_price─┬─ask_quantity─┐
 │ 2020-11-24 12:44:00 │     19153 │           30 │   19158.5 │           82 │
 │ 2020-11-24 12:45:00 │   19181.5 │            3 │   19187.5 │          211 │
 │ 2020-11-24 12:46:00 │     19206 │         2592 │   19212.5 │          902 │
 │ 2020-11-24 12:47:00 │   19185.5 │            5 │   19187.5 │        23391 │
 │ 2020-11-24 12:48:00 │     19212 │          451 │     19214 │          200 │
 │ 2020-11-24 12:49:00 │     19226 │        20024 │     19225 │           64 │
 │ 2020-11-24 12:50:00 │   19224.5 │         6000 │   19227.5 │         5000 │
 │ 2020-11-24 12:51:00 │   19210.5 │           10 │     19209 │         4913 │
 │ 2020-11-24 12:52:00 │   19213.5 │         1833 │   19196.5 │          183 │
 │ 2020-11-24 12:53:00 │     19192 │         3750 │   19197.5 │          142 │
 └─────────────────────┴───────────┴──────────────┴───────────┴──────────────┘

 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 SELECT time, (bp * aq + ap * bq) / (bq + aq) AS mid
 FROM
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(price_0, receive_time_utc) AS bp,
     argMax(quantity_0, receive_time_utc) AS bq
   FROM market_by_price
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-PERPETUAL' AND
     side = 'BUY'
   GROUP BY time
 ) AS lhs
 FULL OUTER JOIN
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(price_0, receive_time_utc) AS ap,
     argMax(quantity_0, receive_time_utc) AS aq
   FROM market_by_price
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-PERPETUAL' AND
     side = 'SELL'
   GROUP BY time
 ) AS rhs
 USING time
 ORDER BY time
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 Query id: 4c496adc-bb47-4ab7-908b-14a9c417f8e8

 ┌────────────────time─┬────────────────mid─┐
 │ 2020-11-24 14:50:00 │ 19406.441259360658 │
 │ 2020-11-24 14:51:00 │              19420 │
 │ 2020-11-24 14:52:00 │ 19412.658139534884 │
 │ 2020-11-24 14:53:00 │  19402.64179755672 │
 │ 2020-11-24 14:54:00 │ 19369.948668745503 │
 │ 2020-11-24 14:55:00 │ 19371.732090769765 │
 │ 2020-11-24 14:56:00 │  19369.91850302185 │
 │ 2020-11-24 14:57:00 │ 19392.021276595744 │
 │ 2020-11-24 14:58:00 │ 19376.514453961456 │
 │ 2020-11-24 14:59:00 │ 19410.864810562573 │
 └─────────────────────┴────────────────────┘

 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 SELECT
   toStartOfMinute(receive_time_utc) AS time,
   argMin(price_vwap, receive_time_utc) AS open,
   max(price_vwap) AS high,
   min(price_vwap) AS low,
   argMax(price_vwap, receive_time_utc) AS close,
   sum(quantity) AS volume
 FROM trade_summary
 WHERE
   receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
   symbol = 'BTC-PERPETUAL'
 GROUP BY time
 ORDER BY time
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 Query id: f2bb365c-7156-4473-a100-43225d20fa8f

 ┌────────────────time─┬───────────────open─┬───────────────high─┬────────────────low─┬──────────────close─┬─volume─┐
 │ 2020-11-24 12:51:00 │            19208.5 │            19210.5 │            19198.5 │              19205 │     84 │
 │ 2020-11-24 12:52:00 │              19202 │            19213.5 │            19191.5 │            19209.5 │   4924 │
 │ 2020-11-24 12:53:00 │              19170 │              19197 │              19155 │              19171 │    891 │
 │ 2020-11-24 12:54:00 │            19115.5 │ 19148.002976190477 │ 19101.772727272728 │ 19101.772727272728 │  18887 │
 │ 2020-11-24 12:55:00 │ 19158.090909090908 │  19167.94827586207 │            19134.5 │              19160 │   2900 │
 │ 2020-11-24 12:56:00 │  19190.86111111111 │              19195 │        19160.78125 │            19182.5 │  12513 │
 │ 2020-11-24 12:57:00 │            19196.5 │              19209 │              19189 │            19202.5 │  14413 │
 │ 2020-11-24 12:58:00 │            19203.5 │            19219.5 │            19203.5 │            19214.5 │   6541 │
 │ 2020-11-24 12:59:00 │            19195.5 │            19210.5 │            19193.5 │            19210.5 │   8520 │
 │ 2020-11-24 13:00:00 │ 19204.471698113208 │            19241.5 │              19203 │ 19222.346514047866 │  38626 │
 │ 2020-11-24 13:01:00 │              19246 │            19248.5 │              19240 │              19240 │    470 │
 └─────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────┘

 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 SELECT time, sum(pq) / sum(q) AS vwap
 FROM
 (
 SELECT
   toStartOfMinute(receive_time_utc) AS time,
   price_vwap * quantity AS pq,
   quantity AS q
 FROM trade_summary
 WHERE
   receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
   symbol = 'BTC-PERPETUAL'
 )
 GROUP BY time
 ORDER BY time
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 Query id: ffe10a04-cf0c-4fdb-96cd-c98d9ebc145a

 ┌────────────────time─┬───────────────vwap─┐
 │ 2020-11-24 14:44:00 │            19353.5 │
 │ 2020-11-24 14:45:00 │ 19371.794527515005 │
 │ 2020-11-24 14:46:00 │  19390.92057004472 │
 │ 2020-11-24 14:47:00 │ 19389.726553672317 │
 │ 2020-11-24 14:48:00 │  19179.58668107794 │
 │ 2020-11-24 14:49:00 │ 19401.001082201066 │
 │ 2020-11-24 14:50:00 │ 19396.412028073573 │
 │ 2020-11-24 14:51:00 │ 19418.522510474337 │
 │ 2020-11-24 14:52:00 │ 19418.662779620932 │
 │ 2020-11-24 14:53:00 │  19400.75527900973 │
 │ 2020-11-24 14:54:00 │  19373.95548122349 │
 └─────────────────────┴────────────────────┘

 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 SELECT time, index_value, settlement_price
 FROM
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(value, receive_time_utc) AS index_value
   FROM statistics
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-DERIBIT-INDEX' AND
     type = 'INDEX_VALUE'
   GROUP BY time
 ) AS col1
 FULL OUTER JOIN
 (
   SELECT
     toStartOfMinute(receive_time_utc) AS time,
     argMax(value, receive_time_utc) AS settlement_price
   FROM statistics
   WHERE
     receive_time_utc > toDateTime64(now() - toIntervalMinute(10), 9, 'UTC') AND
     symbol = 'BTC-DERIBIT-INDEX' AND
     type = 'SETTLEMENT_PRICE'
   GROUP BY time
 ) AS col2
 USING time
 ORDER BY time
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 Query id: c40bbcd9-2b80-42a7-bd07-5bedb52ff682

 ┌────────────────time─┬─index_value─┬─settlement_price─┐
 │ 2020-11-24 14:38:00 │       19264 │                0 │
 │ 2020-11-24 14:39:00 │    19262.98 │                0 │
 │ 2020-11-24 14:40:00 │    19295.48 │                0 │
 │ 2020-11-24 14:41:00 │    19295.75 │                0 │
 │ 2020-11-24 14:42:00 │    19287.67 │                0 │
 │ 2020-11-24 14:43:00 │     19307.2 │                0 │
 │ 2020-11-24 14:44:00 │    19316.26 │                0 │
 │ 2020-11-24 14:45:00 │    19321.46 │                0 │
 │ 2020-11-24 14:46:00 │    19373.23 │                0 │
 │ 2020-11-24 14:47:00 │    19373.19 │         19373.19 │
 │ 2020-11-24 14:48:00 │    19371.75 │         19371.75 │
 └─────────────────────┴─────────────┴──────────────────┘

 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 SELECT
   symbol,
   argMax(settlement_currency, receive_time_utc) AS settlement_currency,
   argMax(strike_price, receive_time_utc) AS strike_price,
   argMax(tick_size, receive_time_utc) AS tick_size,
   argMax(multiplier, receive_time_utc) AS multiplier
 FROM reference_data
 WHERE
   exchange='deribit' AND
   security_type='OPTION' AND
   option_type='CALL' AND
   currency='BTC'
 GROUP BY symbol
 ORDER BY symbol
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 Query id: 4327bcd9-0bc4-4b53-a56f-4defdbdf7ea6

 ┌─symbol──────────────┬─settlement_currency─┬─strike_price─┬─────────────tick_size─┬─multiplier─┐
 │ BTC-11DEC20-14250-C │ USD                 │        14250 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-14500-C │ USD                 │        14500 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-14750-C │ USD                 │        14750 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-15000-C │ USD                 │        15000 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-15250-C │ USD                 │        15250 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-15500-C │ USD                 │        15500 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-15750-C │ USD                 │        15750 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-16000-C │ USD                 │        16000 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-16250-C │ USD                 │        16250 │ 0.0005000000000000001 │          1 │
 │ BTC-11DEC20-16500-C │ USD                 │        16500 │ 0.0005000000000000001 │          1 │
 ...
 │ BTC-4DEC20-20000-C  │ USD                 │        20000 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-20250-C  │ USD                 │        20250 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-20500-C  │ USD                 │        20500 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-20750-C  │ USD                 │        20750 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-21000-C  │ USD                 │        21000 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-21250-C  │ USD                 │        21250 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-21500-C  │ USD                 │        21500 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-21750-C  │ USD                 │        21750 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-22000-C  │ USD                 │        22000 │ 0.0005000000000000001 │          1 │
 │ BTC-4DEC20-22250-C  │ USD                 │        22250 │ 0.0005000000000000001 │          1 │
 └─────────────────────┴─────────────────────┴──────────────┴───────────────────────┴────────────┘

 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.)