InfluxDB

Server

Please refer to InfluxData’s official documentation here.

An official image is available from Docker Hub.

Note

The roq-influxdb adapter makes use of the Flux query language. This must be enabled when launching the InfluxDB service. For example, set the INFLUXDB_HTTP_FLUX_ENABLED environment variable to true.

Client

InfluxDB ships with a command-line interface named influx which can support either InfluxQL or Flux.

InfluxQL

influx -precision rfc3339

Note

The roq-ansible playbook installs influxql shell script so you can conveniently launch the client with InfluxQL support.

You will be greeted by

Connected to http://localhost:8086 version 1.8.3
InfluxDB shell version: 1.8.3

You should now select your database

> use roq
Using database roq

Important

The following examples have all been shown multiline for a better experience. However, the InfluxQL client does not support multiline input (see issue here). You should therefore take care to copy-paste the following examples onto a single line.

Flux

influx -precision rfc3339 -type flux -path-prefix /api/v2/query

Note

The roq-ansible playbook installs flux shell script so you can conveniently launch the client with Flux support.

You will be greeted by

Connected to http://localhost:8086/api/v2/query version 1.8.3
InfluxDB shell version: 1.8.3

Note

There is no equivalent to InfluxQL’s use roq. Flux queries require explicit selection of the bucket (see samples below).

Adapter

The roq-influxdb adapter can be started like this

roq-influxdb \
    --name influxdb \
    --uri http://localhost:8086 \
    ~/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 InfluxDB.

For further details, please refer to the reference documentation here.

Admin

The following examples are shown using InfluxQL.

Create

To create a database

> create database roq

Statistics

Get an overview of cardinality and total number of series managed by the database

> show stats for 'database'
name: database
tags: database=_internal
numMeasurements numSeries
--------------- ---------
12              276

name: database
tags: database=roq
numMeasurements numSeries
--------------- ---------
10              11481

Measurements

Get a list of measurements indexed by the database

> show measurements
name: measurements
name
----
funds_update
market_by_price
market_data_status
market_status
order_manager_status
reference_data
statistics
top_of_book
trade_summary

Fields

Get a list of the fields (series) maintained by the database

> show field keys from market_by_price
name: market_by_price
fieldKey               fieldType
--------               ---------
exchange_time_utc      float
is_last                boolean
origin_create_time     float
origin_create_time_utc float
price_0                float
price_1                float
price_2                float
quantity_0             float
quantity_1             float
quantity_2             float
receive_time           float
snapshot               boolean
source_receive_time    float
source_send_time       float
source_seqno           float

Note

Market by Price is only limited view of an underlying order book maintained by price. The underlying order book can potentially be an aggregate view of all orders on the exchange. It is not efficient to save all price levels into a single table and the choice is therefore to only save a limited view. This example has saved the best 3 levels on either side. However, the roq-influxdb adapter can be configured differently by using the --market-by-price-view-depth flag.

Count

Get a row-count for a single series

> select count(price_0) from market_by_price
name: market_by_price
time                 count
----                 -----
1970-01-01T00:00:00Z 18123864

Note

The row-count for the union of all series belonging to a measurement will likely be much higher (since we only save changed values to each series).

Query

The following query examples are demonstrated using both InfluxQL and Flux.

Reference Data

We select the latest values for each column.

InfluxQL

Note

You should only apply the LAST() operator once. It will automatically be applied to the remaining fields, including * (all).

SELECT
  symbol,
  LAST(description),
  option_type,
  strike_price
FROM reference_data
WHERE
  gateway='deribit' AND
  symbol=~ /BTC-*/ AND
  security_type='OPTION' AND
  option_type='PUT' AND
  strike_price>19000
GROUP BY symbol

Flux

TODO

Top of Book (ToB)

Table: market_by_price

Important

InfluxDB does not allow NaN. It is therefore very important that you fetch the available quantity to infer if the last price is valid or not. You may use a quantity of zero to replace the last known price with a NaN in your own code.

InfluxQL

SELECT
  LAST(bp) AS bid_price,
  LAST(bq) AS bid_quantity,
  LAST(ap) AS ask_price,
  LAST(aq) AS ask_quantity
FROM (
  SELECT
    price_0 AS bp,
    quantity_0 AS bq
  FROM market_by_price
  WHERE
    time > NOW() - 10m AND
    symbol='BTC-PERPETUAL' AND
    side='BUY'
), (
  SELECT
    price_0 AS ap,
    quantity_0 AS aq
  FROM market_by_price
  WHERE
    time > NOW() - 10m AND
    symbol='BTC-PERPETUAL' AND
    side='SELL'
)
GROUP BY TIME(1m)
name: market_by_price
time                 bid_price bid_quantity ask_price ask_quantity
----                 --------- ------------ --------- ------------
2020-11-25T04:29:00Z 18912     6130         18912.5   162
2020-11-25T04:30:00Z 18867     1647         18867.5   4
2020-11-25T04:31:00Z 18872     5247         18872.5   100
2020-11-25T04:32:00Z 18921.5   1956         18922     83
2020-11-25T04:33:00Z 18931     3884         18931.5   59
2020-11-25T04:34:00Z 18935.5   7969         18936     3550
2020-11-25T04:35:00Z 18942     114          18942.5   29572
2020-11-25T04:36:00Z 18919     2787         18919.5   917
2020-11-25T04:37:00Z 18914     119          18914.5   4324
2020-11-25T04:38:00Z 18916.5   14           18917     18540
2020-11-25T04:39:00Z

Flux

bid = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "market_by_price" and
        r.symbol == "BTC-PERPETUAL" and r.side == "BUY" and
        (r._field == "price_0" or r._field == "quantity_0"))
    |> window(every: 1m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_0: "bid_price", quantity_0: "bid_quantity"})

ask = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "market_by_price" and
        r.symbol == "BTC-PERPETUAL" and r.side == "SELL" and
        (r._field == "price_0" or r._field == "quantity_0"))
    |> window(every: 1m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_0: "ask_price", quantity_0: "ask_quantity"})

join(tables: {bid:bid, ask:ask}, on: ["_time"])
Result: _result
Table: keys: []
                    _time:time               ask_price:float            ask_quantity:float               bid_price:float            bid_quantity:float
------------------------------  ----------------------------  ----------------------------  ----------------------------  ----------------------------
2020-11-25T11:59:00.000000000Z                       19342.5                         10161                       19338.5                          2804
2020-11-25T12:00:00.000000000Z                         19334                            14                       19333.5                         18194
2020-11-25T12:01:00.000000000Z                         19348                           310                       19347.5                         13196
2020-11-25T12:02:00.000000000Z                       19360.5                             2                         19360                          1945
2020-11-25T12:03:00.000000000Z                       19343.5                          2958                       19340.5                          3722
2020-11-25T12:04:00.000000000Z                         19339                          5000                         19335                          6540
2020-11-25T12:05:00.000000000Z                       19321.5                          1967                         19321                          9308
2020-11-25T12:06:00.000000000Z                         19320                          1760                       19316.5                           988
2020-11-25T12:07:00.000000000Z                       19306.5                          5000                       19303.5                           480
2020-11-25T12:08:00.000000000Z                         19269                          6730                       19266.5                           200

Weighted Mid Price

Table: market_by_price

InfluxQL

SELECT
  (LAST(bp) * LAST(aq) + LAST(ap) * LAST(bq)) /
  (LAST(bq) + LAST(aq)) AS mid
FROM (
  SELECT
    price_0 AS bp,
    quantity_0 AS bq
  FROM market_by_price
  WHERE
    time > NOW() - 10m AND
    symbol='BTC-PERPETUAL' AND
    side='BUY'
), (
  SELECT
    price_0 AS ap,
    quantity_0 AS aq
  FROM market_by_price
  WHERE
    time > NOW() - 10m AND
    symbol='BTC-PERPETUAL' AND
    side='SELL'
)
GROUP BY TIME(1m)
name: market_by_price
time                 mid
----                 ---
2020-11-25T04:31:00Z 18872.490648962033
2020-11-25T04:32:00Z 18921.979646885728
2020-11-25T04:33:00Z 18931.492518387015
2020-11-25T04:34:00Z 18935.84590676274
2020-11-25T04:35:00Z 18942.001920097016
2020-11-25T04:36:00Z 18919.376214902808
2020-11-25T04:37:00Z 18914.013391852353
2020-11-25T04:38:00Z 18914.52166969682
2020-11-25T04:39:00Z 18879.31935338006
2020-11-25T04:40:00Z 18881.005558009423
2020-11-25T04:41:00Z

Flux

bid = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "market_by_price" and
        r.symbol == "BTC-PERPETUAL" and r.side == "BUY" and
        (r._field == "price_0" or r._field == "quantity_0"))
    |> window(every: 1m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_0: "bid_price", quantity_0: "bid_quantity"})

ask = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "market_by_price" and
        r.symbol == "BTC-PERPETUAL" and r.side == "SELL" and
        (r._field == "price_0" or r._field == "quantity_0"))
    |> window(every: 1m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_0: "ask_price", quantity_0: "ask_quantity"})

join(tables: {bid:bid, ask:ask}, on: ["_time"])
    |> map(fn: (r) => ({
        r with
        mid: (r.bid_price * r.ask_quantity + r.ask_price * r.bid_quantity) /
             (r.bid_quantity + r.ask_quantity)}))
    |> keep(columns: ["_time", "mid"])
Result: _result
Table: keys: []
                    _time:time                     mid:float
------------------------------  ----------------------------
2020-11-25T12:06:00.000000000Z            19317.758369723437
2020-11-25T12:07:00.000000000Z             19303.76277372263
2020-11-25T12:08:00.000000000Z            19266.572202166066
2020-11-25T12:09:00.000000000Z             19256.67275419546
2020-11-25T12:10:00.000000000Z            19271.009404388715
2020-11-25T12:11:00.000000000Z             19257.65465674341
2020-11-25T12:12:00.000000000Z             19275.99003984064
2020-11-25T12:13:00.000000000Z                     19275.715
2020-11-25T12:14:00.000000000Z             19249.91371308017
2020-11-25T12:15:00.000000000Z             19230.99547140516
2020-11-25T12:15:38.890947633Z             19227.99694364852

Open-High-Low-Close (OHLC)

Table: trade_summary

InfluxQL

SELECT
  FIRST(price_vwap) AS open,
  MAX(price_min) AS high,
  MIN(price_max) AS low,
  LAST(price_vwap) AS close,
  SUM(quantity) AS volume
FROM trade_summary
WHERE
  time > NOW() - 10m AND
  symbol='BTC-PERPETUAL'
GROUP BY TIME(1m)
time                 open               high    low     close       volume
----                 ----               ----    ---     -----       ------
2020-11-25T04:31:00Z
2020-11-25T04:32:00Z 18872.5            18921.5 18872.5 18921.5     10807
2020-11-25T04:33:00Z 18917.5            18929   18917.5 18929       9639
2020-11-25T04:34:00Z 18931.5            18937.5 18928   18928       2480
2020-11-25T04:35:00Z 18938.494175888176 18940.5 18938.5 18940.5     2739
2020-11-25T04:36:00Z 18942              18942   18923   18923       11200
2020-11-25T04:37:00Z 18918              18927   18914.5 18914.5     3335
2020-11-25T04:38:00Z 18919              18922.5 18917   18917       2023
2020-11-25T04:39:00Z 18914.5            18914.5 18890   18810.01239 300687
2020-11-25T04:40:00Z 19086              19086   18843   18883       1514687
2020-11-25T04:41:00Z

Flux

open = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL" and
        r._field == "price_vwap")
    |> window(every: 1m)
    |> first()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_vwap: "open"})

high = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL" and
        r._field == "price_vwap")
    |> window(every: 1m)
    |> max()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_vwap: "high"})

low = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL" and
        r._field == "price_vwap")
    |> window(every: 1m)
    |> min()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_vwap: "low"})

close = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL" and
        r._field == "price_vwap")
    |> window(every: 1m)
    |> last()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_vwap: "close"})

volume = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL" and
        r._field == "price_vwap")
    |> window(every: 1m)
    |> sum()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> rename(columns: {price_vwap: "volume"})

tmp1 = join(tables: {open:open, high:high}, on: ["_time"])
tmp2 = join(tables: {tmp1:tmp1, low:low}, on: ["_time"])
tmp3 = join(tables: {tmp2:tmp2, close:close}, on: ["_time"])
join(tables: {tmp3:tmp3, volume:volume}, on: ["_time"])
Result: _result
Table: keys: []
                    _time:time                   close:float                    high:float                     low:float                    open:float                  volume:float
------------------------------  ----------------------------  ----------------------------  ----------------------------  ----------------------------  ----------------------------
2020-11-25T12:24:00.000000000Z                         19270                         19275                         19270                         19275                         57816
2020-11-25T12:25:00.000000000Z                       19261.5                       19272.5                       19261.5                         19270                        154144
2020-11-25T12:26:00.000000000Z                         19245                         19258                         19245                         19258                      115501.5
2020-11-25T12:27:00.000000000Z            19231.617647058825                         19238            19231.617647058825                         19238            38469.617647058825
2020-11-25T12:28:00.000000000Z                         19267                       19278.5                       19259.5                       19259.5                         96360
2020-11-25T12:29:00.000000000Z                       19267.5                         19273                       19267.5                         19273                       38540.5
2020-11-25T12:30:00.000000000Z                         19279                         19279                         19279                         19279                         19279
2020-11-25T12:31:00.000000000Z                       19257.5                         19284                       19257.5                         19284            115645.33333333333
2020-11-25T12:32:00.000000000Z                         19254                         19258                         19254                         19258                       57768.5
2020-11-25T12:33:00.000000000Z                         19271                         19279                         19271                         19279                         38550
2020-11-25T12:33:28.343007973Z                       19272.5                       19272.5                       19272.5                       19272.5                       19272.5

Volume Weighted Average Price (VWAP)

Table: trade_summary

InfluxQL

SELECT SUM(pq) / SUM(q) AS vwap
FROM (
  SELECT
    price_vwap * quantity AS pq,
    quantity AS q
  FROM trade_summary
  WHERE
    time > NOW() - 10m AND
    symbol='BTC-PERPETUAL'
)
GROUP BY TIME(1m)
time                 vwap
----                 ----
2020-11-25T04:33:00Z 18925.925795053005
2020-11-25T04:34:00Z 18937.23870967742
2020-11-25T04:35:00Z 18938.843738590727
2020-11-25T04:36:00Z 18924.03888392857
2020-11-25T04:37:00Z 18917.02068965517
2020-11-25T04:38:00Z 18922.33934750371
2020-11-25T04:39:00Z 18810.21011217645
2020-11-25T04:40:00Z 18933.421864385182
2020-11-25T04:41:00Z 18877.573863636364
2020-11-25T04:42:00Z 18878.916666666668
2020-11-25T04:43:00Z

Flux

tmp = from(bucket: "roq")
    |> range(start: -10m)
    |> filter(fn: (r) =>
        r._measurement == "trade_summary" and
        r.symbol == "BTC-PERPETUAL")
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> keep(columns: ["_time", "price_vwap", "quantity"])
    |> map(fn: (r) => ({
        r with
        price_quantity: r.price_vwap * r.quantity}))
    |> window(every: 1m)

sum_pq = tmp
    |> sum(column: "price_quantity")
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "price_quantity"])

sum_q = tmp
    |> sum(column: "quantity")
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: inf)
    |> keep(columns: ["_time", "quantity"])

join(tables: {sum_pq:sum_pq, sum_q:sum_q}, on: ["_time"])
    |> map(fn: (r) => ({
        _time: r._time,
        vwap: r.price_quantity / r.quantity}))
Result: _result
Table: keys: []
                    _time:time                    vwap:float
------------------------------  ----------------------------
2020-11-25T14:25:00.000000000Z             19217.21732026144
2020-11-25T14:26:00.000000000Z             19196.63203012338
2020-11-25T14:27:00.000000000Z            19167.523919842515
2020-11-25T14:28:00.000000000Z               19127.497840323
2020-11-25T14:29:00.000000000Z            19191.380108991827
2020-11-25T14:30:00.000000000Z            19178.091573258607
2020-11-25T14:31:00.000000000Z            19092.462819249406
2020-11-25T14:32:00.000000000Z             19102.66111208946
2020-11-25T14:33:00.000000000Z            19199.734523809522
2020-11-25T14:34:00.000000000Z            19197.043752556066

Index Values (Deribit specific)

Table: statistics

Note

Deribit will publish index values to tradeable instruments. This is drectly from Deribit’s documentation

  • Index Value (value of Index for INDEX instruments like BTC-DERIBIT-INDEX)

  • Settlement Price (Estimated Delivery Price for INDEX instruments like BTC-DERIBIT-INDEX)

The point is not to discuss Deribit here – merely to show how to extract information from the market statistics table.

InfluxQL

SELECT
  LAST(index_value) AS index_value,
  LAST(settlement_price) AS settlement_price
FROM statistics
WHERE
  time > NOW() - 10m AND
  symbol='BTC-DERIBIT-INDEX'
GROUP BY time(1m)
name: statistics
time                 index_value settlement_price
----                 ----------- ----------------
2020-11-25T04:33:00Z 18921.33    18921.33
2020-11-25T04:34:00Z 18922.14    18922.14
2020-11-25T04:35:00Z 18927.5     18927.5
2020-11-25T04:36:00Z 18906.35    18906.35
2020-11-25T04:37:00Z 18905.11    18905.11
2020-11-25T04:38:00Z 18902.83    18902.83
2020-11-25T04:39:00Z 18878.46    18878.46
2020-11-25T04:40:00Z 18866.85    18866.85
2020-11-25T04:41:00Z 18868.91    18868.91
2020-11-25T04:42:00Z 18856.64    18856.64
2020-11-25T04:43:00Z

Flux

from(bucket: "roq")
  |> range(start: -10m)
  |> filter(fn: (r) =>
    r._measurement == "statistics" and
    r.symbol == "BTC-DERIBIT-INDEX" and
    (r._field == "index_value" or r._field == "settlement_price"))
  |> window(every: 1m)
  |> last()
  |> duplicate(column: "_stop", as: "_time")
  |> window(every: inf)
  |> keep(columns: ["_time", "_field", "_value"])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
Result: _result
Table: keys: []
                    _time:time             index_value:float        settlement_price:float
------------------------------  ----------------------------  ----------------------------
2020-11-25T14:28:00.000000000Z                      19176.66                      19176.66
2020-11-25T14:29:00.000000000Z                      19154.51                      19154.51
2020-11-25T14:30:00.000000000Z                      19163.38                      19163.38
2020-11-25T14:31:00.000000000Z                      19193.99                      19193.99
2020-11-25T14:32:00.000000000Z                      19197.52                      19197.52
2020-11-25T14:33:00.000000000Z                      19161.75                      19161.75
2020-11-25T14:34:00.000000000Z                      19174.04                      19174.04
2020-11-25T14:35:00.000000000Z                      19153.84                      19153.84
2020-11-25T14:36:00.000000000Z                      19200.48                      19200.48
2020-11-25T14:37:00.000000000Z                      19188.59                      19188.59
2020-11-25T14:37:49.749109131Z                      19199.74                      19199.74

Options

Table: reference_data

InfluxQL

Note

InfluxQL can’t order by symbol (only time).

SELECT
  LAST(settlement_currency) AS settlement_currency,
  LAST(strike_price) AS strike_price,
  LAST(tick_size) AS tick_size,
  LAST(multiplier) AS multiplier
FROM reference_data
WHERE
  exchange='deribit' AND
  security_type='OPTION' AND
  option_type='CALL' AND
  currency='BTC'
GROUP BY symbol
name: reference_data
tags: symbol=BTC-11DEC20-14250-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     14250        0.0005000000000000001 1

name: reference_data
tags: symbol=BTC-11DEC20-14500-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     14500        0.0005000000000000001 1

name: reference_data
tags: symbol=BTC-11DEC20-14750-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     14750        0.0005000000000000001 1

...

name: reference_data
tags: symbol=BTC-4DEC20-21750-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     21750        0.0005000000000000001 1

name: reference_data
tags: symbol=BTC-4DEC20-22000-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     22000        0.0005000000000000001 1

name: reference_data
tags: symbol=BTC-4DEC20-22250-C
time                 settlement_currency strike_price tick_size             multiplier
----                 ------------------- ------------ ---------             ----------
1970-01-01T00:00:00Z                     22250        0.0005000000000000001 1

Flux

from(bucket: "roq")
  |> range(start: -1w)
  |> filter(fn: (r) =>
    r._measurement == "reference_data" and
    r.exchange == "deribit" and
    r.security_type == "OPTION" and
    r.option_type == "CALL" and
    r.currency == "BTC")
  |> window(every: 1w)
  |> last()
  |> duplicate(column: "_stop", as: "_time")
  |> window(every: inf)
  |> keep(columns: ["_time", "symbol", "_field", "_value"])
  |> pivot(rowKey:["_time","symbol"], columnKey: ["_field"], valueColumn: "_value")
  |> keep(columns: ["_time", "symbol", "strike_price", "tick_size", "multiplier"])
Result: _result
Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
   BTC-11DEC20-14250-C  2020-11-25T14:45:11.614884320Z                             1                         14250         0.0005000000000000001
Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
   BTC-11DEC20-14500-C  2020-11-25T14:45:11.614884320Z                             1                         14500         0.0005000000000000001
Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
   BTC-11DEC20-14750-C  2020-11-25T14:45:11.614884320Z                             1                         14750         0.0005000000000000001

...

Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
    BTC-4DEC20-21750-C  2020-11-25T14:45:11.614884320Z                             1                         21750         0.0005000000000000001
Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
    BTC-4DEC20-22000-C  2020-11-25T14:45:11.614884320Z                             1                         22000         0.0005000000000000001
Table: keys: [symbol]
         symbol:string                      _time:time              multiplier:float            strike_price:float               tick_size:float
----------------------  ------------------------------  ----------------------------  ----------------------------  ----------------------------
    BTC-4DEC20-22250-C  2020-11-25T14:45:11.614884320Z                             1                         22250         0.0005000000000000001