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