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
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¶
Statistics¶
Measurements¶
Get a list of tables
:) show tables
┌─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
:) desc statistics
┌─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
:) select count(*) from market_by_price
┌─count()─┐
│ 515918 │
└─────────┘
Query¶
Top of Book (ToB)¶
Table: market_by_price
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
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
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
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
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
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
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
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
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
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
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
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.)