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

Create

To create a database

:) create database roq

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

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