ClickHouse¶
Server¶
Please refer to Yandex’ official documentation here.
An official image is available from Docker Hub.
Important
Port 8123 must be accessible by the host.
You can do this by adding the --publish 8123:8123 flag to the
Docker command-line.
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_name roq \
--create_database true \
--event_log_dir $EVENT_LOG_DIR
Note
The event-log dir should be an absolute path.
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
:) create database roq
Note
This will automatically be done when using the --create_database=true
command-line flag with roq-clickhouse.
Statistics¶
Measurements¶
Get a list of tables
:) show tables
┌─name───────────────┐
│ cancel_all_orders │
│ cancel_order │
│ create_order │
│ custom_metrics │
│ external_latency │
│ funds │
│ gateway_settings │
│ gateway_status │
│ market_by_order │
│ market_by_price │
│ market_status │
│ modify_order │
│ order_ack │
│ orders │
│ positions │
│ rate_limit_trigger │
│ reference_data │
│ statistics │
│ stream_status │
│ top_of_book │
│ trade_summary │
│ trades │
└────────────────────┘
Fields¶
Get a list of the fields
:) desc statistics
┌─name───────────────────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ gateway │ LowCardinality(String) │ │ │ │ │ │
│ session_id │ UUID │ │ │ │ │ │
│ seqno │ UInt64 │ │ │ │ │ │
│ source │ LowCardinality(String) │ │ │ │ │ │
│ receive_time_utc │ DateTime64(9) │ │ │ │ │ │
│ receive_time │ UInt64 │ │ │ │ │ │
│ source_send_time │ UInt64 │ │ │ │ │ │
│ source_receive_time │ UInt64 │ │ │ │ │ │
│ origin_create_time │ UInt64 │ │ │ │ │ │
│ origin_create_time_utc │ DateTime64(9) │ │ │ │ │ │
│ is_last │ Int8 │ │ │ │ │ │
│ stream_id │ UInt16 │ │ │ │ │ │
│ exchange │ LowCardinality(String) │ │ │ │ │ │
│ symbol │ LowCardinality(String) │ │ │ │ │ │
│ type │ LowCardinality(String) │ │ │ │ │ │
│ value │ Float64 │ │ │ │ │ │
│ begin_time_utc │ DateTime64(0) │ │ │ │ │ │
│ end_time_utc │ DateTime64(0) │ │ │ │ │ │
│ update_type │ LowCardinality(String) │ │ │ │ │ │
│ exchange_time_utc │ DateTime64(9) │ │ │ │ │ │
└────────────────────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Count¶
Get a row-count for a table
:) select count(*) from market_by_price
┌───count()─┐
│ 333875641 │
└───────────┘
Query¶
Reference Data¶
We select the latest values for each column.
TODO
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: 6eb21d1a-20be-4f7c-849e-9ca8f79e66a6
┌────────────────time─┬─bid_price─┬─bid_quantity─┬─ask_price─┬─ask_quantity─┐
│ 2021-12-07 03:48:00 │ 50934 │ 2316 │ 50934.5 │ 14727 │
│ 2021-12-07 03:49:00 │ 50954.5 │ 9 │ 50955 │ 12316 │
│ 2021-12-07 03:50:00 │ 50964 │ 16659 │ 50964.5 │ 634 │
│ 2021-12-07 03:51:00 │ 50980.5 │ 9711 │ 50981 │ 26 │
│ 2021-12-07 03:52:00 │ 50980.5 │ 9808 │ 50981 │ 26 │
│ 2021-12-07 03:53:00 │ 51020 │ 7261 │ 51020.5 │ 602 │
│ 2021-12-07 03:54:00 │ 51013 │ 4142 │ 51013.5 │ 250 │
│ 2021-12-07 03:55:00 │ 51024 │ 15070 │ 51024.5 │ 1 │
│ 2021-12-07 03:56:00 │ 51053 │ 4 │ 51053.5 │ 9743 │
│ 2021-12-07 03:57:00 │ 51040 │ 1 │ 51040.5 │ 500 │
│ 2021-12-07 03:58:00 │ 51049 │ 8233 │ 51049.5 │ 1266 │
└─────────────────────┴───────────┴──────────────┴───────────┴──────────────┘
11 rows in set. Elapsed: 0.780 sec. Processed 12.47 million rows, 356.97 MB (15.98 million rows/s., 457.69 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: b8850ea4-39a9-43fd-8cfb-aaa769b9a6ad
┌────────────────time─┬────────────────mid─┐
│ 2021-12-07 03:49:00 │ 50954.50036511156 │
│ 2021-12-07 03:50:00 │ 50964.48166888336 │
│ 2021-12-07 03:51:00 │ 50980.998664886516 │
│ 2021-12-07 03:52:00 │ 50980.99867805572 │
│ 2021-12-07 03:53:00 │ 51020.461719445506 │
│ 2021-12-07 03:54:00 │ 51013.471539162114 │
│ 2021-12-07 03:55:00 │ 51024.4999668237 │
│ 2021-12-07 03:56:00 │ 51053.00020519134 │
│ 2021-12-07 03:57:00 │ 51040.00099800399 │
│ 2021-12-07 03:58:00 │ 51035.29381443299 │
└─────────────────────┴────────────────────┘
10 rows in set. Elapsed: 0.587 sec. Processed 12.89 million rows, 368.53 MB (21.94 million rows/s., 627.29 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: 342a86d7-c5ff-47ce-a276-1685c9b37d89
┌────────────────time─┬──────────────open─┬────high─┬───────────────low─┬─────────────close─┬─volume─┐
│ 2021-12-07 03:49:00 │ 50964 │ 50964 │ 50954.5 │ 50954.5 │ 26 │
│ 2021-12-07 03:50:00 │ 50954.5 │ 50964 │ 50943.5 │ 50964 │ 2193 │
│ 2021-12-07 03:51:00 │ 50965.03095684803 │ 50970.5 │ 50965.03095684803 │ 50970.5 │ 2044 │
│ 2021-12-07 03:52:00 │ 50980.5 │ 50980.5 │ 50970.5 │ 50976 │ 1458 │
│ 2021-12-07 03:53:00 │ 50981 │ 51062 │ 50981 │ 51019.93382352941 │ 33287 │
│ 2021-12-07 03:54:00 │ 51025.23174316075 │ 51049.5 │ 50991.27149321267 │ 51007 │ 44248 │
│ 2021-12-07 03:55:00 │ 51013.5 │ 51064 │ 51013.5 │ 51024 │ 24942 │
│ 2021-12-07 03:56:00 │ 51024.5 │ 51062 │ 51024.5 │ 51057 │ 43454 │
│ 2021-12-07 03:57:00 │ 51055 │ 51065 │ 51022 │ 51040.5 │ 31499 │
│ 2021-12-07 03:58:00 │ 51041.5 │ 51066.5 │ 51040.06862745098 │ 51040.06862745098 │ 7897 │
└─────────────────────┴───────────────────┴─────────┴───────────────────┴───────────────────┴────────┘
10 rows in set. Elapsed: 0.018 sec. Processed 368.17 thousand rows, 1.68 MB (20.87 million rows/s., 95.02 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: 62b742ea-7a79-4c39-b97c-945484698601
┌────────────────time─┬───────────────vwap─┐
│ 2021-12-07 03:50:00 │ 50959.2018121911 │
│ 2021-12-07 03:51:00 │ 50967.42465753425 │
│ 2021-12-07 03:52:00 │ 50971.43827160494 │
│ 2021-12-07 03:53:00 │ 51040.53448793823 │
│ 2021-12-07 03:54:00 │ 51026.675036159824 │
│ 2021-12-07 03:55:00 │ 51037.22947237591 │
│ 2021-12-07 03:56:00 │ 51046.56853224099 │
│ 2021-12-07 03:57:00 │ 51046.88280580336 │
│ 2021-12-07 03:58:00 │ 51052.71748765354 │
└─────────────────────┴────────────────────┘
9 rows in set. Elapsed: 0.020 sec. Processed 368.17 thousand rows, 1.68 MB (18.05 million rows/s., 82.22 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: ce44238e-ab7f-4232-8a43-97ffbb930ae4
┌────────────────time─┬─index_value─┬─settlement_price─┐
│ 2021-12-07 03:50:00 │ 50927.73 │ 50927.73 │
│ 2021-12-07 03:51:00 │ 50940.41 │ 50940.41 │
│ 2021-12-07 03:52:00 │ 50948.58 │ 50948.58 │
│ 2021-12-07 03:53:00 │ 50998.4 │ 50998.4 │
│ 2021-12-07 03:54:00 │ 50979.03 │ 50979.03 │
│ 2021-12-07 03:55:00 │ 50996.49 │ 50996.49 │
│ 2021-12-07 03:56:00 │ 51015.76 │ 51015.76 │
│ 2021-12-07 03:57:00 │ 51004.35 │ 51004.35 │
│ 2021-12-07 03:58:00 │ 51007.89 │ 51007.89 │
│ 2021-12-07 03:59:00 │ 51014.62 │ 51014.62 │
│ 2021-12-07 04:00:00 │ 51032.08 │ 51032.08 │
└─────────────────────┴─────────────┴──────────────────┘
11 rows in set. Elapsed: 0.062 sec. Processed 1.74 million rows, 33.10 MB (28.12 million rows/s., 533.87 MB/s.)
Options¶
Table: reference_data
SELECT
symbol,
argMax(base_currency, receive_time_utc) AS base_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
quote_currency='BTC'
GROUP BY symbol
ORDER BY symbol
Query id: ecb6f2c0-aff2-4c1e-ace0-0a9fd4763a20
┌─symbol───────────────┬─base_currency─┬─strike_price─┬─────────────tick_size─┬─multiplier─┐
│ BTC-10DEC21-100000-C │ USD │ 100000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-30000-C │ USD │ 30000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-35000-C │ USD │ 35000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-40000-C │ USD │ 40000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-42000-C │ USD │ 42000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-44000-C │ USD │ 44000 │ 0.0005000000000000001 │ 1 │
│ BTC-10DEC21-45000-C │ USD │ 45000 │ 0.0005000000000000001 │ 1 │
...
│ BTC-8DEC21-50000-C │ USD │ 50000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-51000-C │ USD │ 51000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-52000-C │ USD │ 52000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-53000-C │ USD │ 53000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-54000-C │ USD │ 54000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-55000-C │ USD │ 55000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-56000-C │ USD │ 56000 │ 0.0005000000000000001 │ 1 │
│ BTC-8DEC21-58000-C │ USD │ 58000 │ 0.0005000000000000001 │ 1 │
└──────────────────────┴───────────────┴──────────────┴───────────────────────┴────────────┘
267 rows in set. Elapsed: 0.015 sec. Processed 13.96 thousand rows, 327.60 KB (911.31 thousand rows/s., 21.39 MB/s.)