代码之家 › 专栏 › 技术社区 › jamesrogers93

用于检索时间序列财务数据摘要的高效Cassandra DB设计

cassandra-3.0 finance cassandra-2.0 time-series cassandra

jamesrogers93 · 技术社区 · 6 年前

我希望使用apache cassandra数据库存储1分钟OHLCV财务数据的时间序列 ~1000 符号。这将需要在数据流入时实时更新。所有条目,其中 time>24hr old 不需要,应丢弃。

假设在过去24小时内,每分钟有1000个符号和条目,条目总数将达到 1000*(60*24) = 1,440,000 。

我感兴趣的是设计这个数据库来高效地检索过去所有符号的片段 [30m, 1h, 12h, 24h] 查询速度快。最终,我需要检索总结此片段的OHLCV。结果输出为 {symbol, FIRST(open), MAX(high), MIN(low), LAST(close), SUM(volume)} 每个符号的切片。这基本上总结了1m OHLCV条目,并创建了 [30米、1小时、12小时、24小时] 查询时的OHLCV。E、 g.如果我想从1:32pm检索过去的1h OHLCV,查询将给我一个1h OHLCV,它表示12:32pm-1:32pm的数据。

满足这些要求的好设计是什么?我不关心数据库在硬盘上的内存占用。真正的问题是在cpu和ram上的快速查询时间很短。

我想出了一种简单而天真的方法,通过按时间排序的聚类来存储每条记录:

CREATE TABLE symbols ( 
 time timestamp,
 symbol text,
 open double,
 high double,
 low double,
 close double,
 volume double
 PRIMARY KEY (time, symbol)
) WITH CLUSTERING ORDER BY (time DESC);

但我不知道如何从中选择以满足我的要求。我宁愿为我的查询专门设计它,并在必要时复制数据。

如有任何建议,将不胜感激。

1 回复 | 直到 6 年前

kghamilton Sergei Rodionov 6 年前

虽然不是基于卡桑德拉, Axibase Time Series Database 可能与此特定用例非常相关。它支持带有时间序列语法的SQL extensions 将数据聚合为任意长度的时段。

针对15分钟窗口的OHLCV查询可能如下所示:

SELECT date_format(datetime, 'yyyy-MM-dd HH:mm:ss', 'US/Eastern') AS time, 
  FIRST(t_open.value) AS open, 
  MAX(t_high.value) AS high, 
  MIN(t_low.value) AS low, 
  LAST(t_close.value) AS close, 
  SUM(t_volume.value) AS volume 
FROM stock.open AS t_open
  JOIN stock.high AS t_high
  JOIN stock.low AS t_low
  JOIN stock.close AS t_close  
  JOIN stock.volume AS t_volume  
WHERE t_open.entity = 'ibm'
  AND t_open.datetime >= '2018-03-29T14:32:00Z' AND t_open.datetime < '2018-03-29T15:32:00Z'
GROUP BY PERIOD(15 MINUTE, END_TIME)
  ORDER BY datetime

请注意 GROUP BY PERIOD 上面的条款完成了所有幕后工作。

查询结果:

| time                 | open     | high    | low      | close   | volume | 
|----------------------|----------|---------|----------|---------|--------| 
| 2018-03-29 10:32:00  | 151.8    | 152.14  | 151.65   | 152.14  | 85188  | 
| 2018-03-29 10:47:00  | 152.18   | 152.64  | 152      | 152.64  | 88065  | 
| 2018-03-29 11:02:00  | 152.641  | 153.04  | 152.641  | 152.69  | 126511 | 
| 2018-03-29 11:17:00  | 152.68   | 152.75  | 152.43   | 152.51  | 104068 |

您可以使用类型4 JDBC driver 、API客户端或 curl 运行这些查询。

我在上面的示例中使用了1分钟的示例数据,您可以从Kibot下载这些数据 compression tests 。

此外,ATSD支持 scheduled queries 将每分钟的数据具体化为持续时间更长的OHLCV条,例如用于长期保留。

免责声明:我为Axibase工作。