网站备案 万网,成都人才网站建设,微信小程序云服务器价格,logo设计在线生成 免费前面几篇笔记我们讨论了存储海量行情数据的个人技术方案。它们之所以被称之为个人方案#xff0c;并不是因为性能弱#xff0c;而是指在这些方案中#xff0c;数据都存储在本地#xff0c;也只适合单机查询。
数据源很贵 – 在这个冬天#xff0c;我们已经听说#xff0…
前面几篇笔记我们讨论了存储海量行情数据的个人技术方案。它们之所以被称之为个人方案并不是因为性能弱而是指在这些方案中数据都存储在本地也只适合单机查询。
数据源很贵 – 在这个冬天我们已经听说某些上了规模的机构也在让员工共享万得账号了。所以共享网络存储从而只需要一个数据账号就成为合理的需求。更不必说集中管理才可能让 IT 来进行数据维护而分析师只需要专注于策略就好。 那些以讹传讹的解决方案
都已经 2024 年了但说到行情数据的存储你仍然能看到推荐 mysql 的文章。这完全是错误的。不要说 mysql就是 postgres 来了也不行。不要说 postgres就是 sqlserver 甚至 oracle 来了都不行。
其它不 work 的方案还包括 mongodb。mongodb 是挺能装的但是它不适合行情数据这类时序数据的查询。
Influxdb 是最早和最出名的时序数据库。但是它的社区版本性能还是偏弱特别是它限制了查询的并发度。此外它的引擎是 Go 语言这仍然要比 C 慢好几倍。
Dolphinedb 可能性能上强于 Influxdb 不少但缺点也是社区版对性能的限制太多。Tidb 据说性能不错但我们没有机会评测过它。
不过如果有犹如王者般的 clickhouse 社区版摆在面前还有什么必要去评估那些青铜呢
Why click house is so f**ing fast?
Clickhouse 是战斗民族开发的产品。它的开发者是俄国的搜索引擎 Yandex!提到 Yandex! 时不能漏了这个感叹号。搜索引擎天生要处理很多查询和统计分析所以就催生了这个性能怪兽。 Clickhouse 的优化是全方位的。在硬件级别上它利用了 SIMD CPU 指令。Clickhouse 特别强调他们使用了 SIMD 指令来进行并行优化当你安装 clickhouse 时它提供了一个检测工具让你检测 SIMD 指令优化能否开启。 数据结构上Clickhouse 使用了列存储这一点其实像 parquet, hdf5 都是这样存储的。基于列存储就有很多很好的压缩方案可用一旦存盘的数据量变小显然易见 IO 效率也会提升。
但是它基于 merge-tree 的存储引擎使得在查询上不仅可以利用所有的 CPU 核和磁盘、还可以利用集群的所有 CPU 核和磁盘。这使得它的查询性能可以随硬件增加线性扩展。 在这一块确实用了很多大数据的技巧比如使用了 bloomfilter 的索引。它还留了一些优化技巧给使用者这也是我们这篇笔记将要介绍的如何设计一个能存储上百亿条行情数据的数据库并达到最佳性能。
实战先存一个小目标
尽管在 clickhouse 中我们可以把分钟线与日线存在一张表里但是考虑到我们几乎不可能同时取两个不同周期的数据所以把它们分别按表存储显然更合理。所以我们在举例时就只以分钟线为例
CREATE TABLE if not exists bars_1m
(frame DateTime64 CODEC(Delta, ZSTD),symbol LowCardinality(String),open Float32 DEFAULT -1 CODEC(Delta, ZSTD),high Float32 DEFAULT -1 CODEC(Delta, ZSTD),low Float32 DEFAULT -1 CODEC(Delta, ZSTD),close Float32 DEFAULT -1 CODEC(Delta, ZSTD),volume Float64 DEFAULT -1 ,money Float64 DEFAULT -1 ,factor Float64 DEFAULT -1 CODEC(Delta, ZSTD)
)
ENGINE MergeTree
ORDER BY (frame, symbol)这里我们看到使用 clickhouse 的第一个好处。它完全兼容了 sql 的核心语法。要知道在设计 zillionare 2.0 版时我们被 influxdb 折磨的不行 – 天知道他们为什么抛弃了原先对 sql 的兼容而独出心裁地设计了一种全新的查询语言
这意味着如果我们在团队内安装了 clickhouse – 这常常是 IT 的活分析师也就能直接上手 – 因为做数据分析的人你们都是懂 sql 的。
这里有一些技巧是普通的 SQL 中没有的。
首先是 frame 字段中的 CODEC(Delta, ZSTD) 压缩。它巧妙地通过行间数据的差值将列数据转换成为一个稀疏的数据向量 – 这样可以大大减少存储空间和读取时间。实际上在行情数据中大量的时间戳都是相同的或者只有很小的 delta。比如如果我们把 5000 多支个股的分钟数据存入一张表那么我们常常会看到连续 5000 个相同的时间戳这些都可以存为 0
OHLC 中的 default 值也给得颇有讲究。如果某天某个标的停牌那么它的 OHLC 等数据就是空值。clickhouse 允许我们存空值。但这样一来clickhouse 必须使用另外的文件来存储空值并在查询时再通过 join 把空值连接起来。这会花掉一些时间。所以这里我们使用了一个不可能的值作为默认值这样所有的数据仍然存在一起将会加快存储和运算速度。
OHLC 数据的变化都很小所以我们也通过 DELTA 编码压缩它们。而成交量和成交额的跳动则可能很大启用压缩就会得不偿失。
我们能做这些优化是因为我们知道数据的分布特性 – 就像数据分析师也必须懂得数据的分布特性一样 – clickhouse 也是这样才能做好优化。
我们在 OHLC 数据上使用了单精度但对 factor 却使用了 64 位浮点数。这是必要的尽管看上去它们都很小但是OHLC 数据的取值范围很小不会有精度问题而 factor 数据则不一样它必须更准确。
Symbol 字段我们也使用了一种优化。通过这种编码我们实际上在存储 Symbol 时存储的是整数而非字符串这样会大大提高存储效率和查询速度。这个方法如果你熟悉 Pandas 的性能优化就应该已经见过了 – 它就是类似于 pandas 的 categorize 优化。
最后我们把 frame 和 symbo 设为主键。我们的大多数查询将基于这样两个字段的比较。 我们设计的表性能究竟怎么样
让我们先分别存入 100 万条、1000 万条和 1 亿条数据并且分别计算插入时间和查询时间。在准备数据时我们使用了全随机的数据这点很重要。如果我们都使用相同的数据那么速度会快一些。
写入 1 百万条数据用时 8.3 秒查询结果为 200 行时用时 0.1 秒。写入 1 千万条数据用时 77.7 秒查询结果为 2000 行时用时 0.7 秒。写入 1 亿条数据用时 16 分钟左右查询结果为 20200 行时用时 13.7 秒。
这个结果已经很优秀了。但还看上去并没有超出预期对吧作为对比我们在 influxdb 上返回 100 万条记录时花费 55 秒左右其中网络传输和客户端重新组装数据占约 30 秒这样看来clickhouse在这一局很可能还没有超过influxdb。另外在 1000 万级别下优化到极致的 mysql 也能做到0.7秒以内的查询不过它处理不了上亿条数据。
为什么没有惊喜我去看了一下我的测试环境
一个只有 8CPU,8GB 的虚拟机当然底层是磁盘阵列并且我已经开了 4 个 vscode 窗口这样系统只剩下0.6GB的自由内存。我们测试Influxdb时使用的是物理机48CPU96GB内存总记录是30多亿条。
改天再找机会在同样的环境下进行对比测试。不过clickhouse 员工已经在类似的行情数据库上进行了测试
在一台 macbook pro 上在 2.4 亿条记录中进行 argmax 的查询只用了 0.9 秒这个速度虽然不够跑高频但已足够多数场景下使用了。
不过clickhouse 的测试与我们的测试有很大差别
在 clickhouse 的测试中它返回的数据量很小而在我们的测试中要求查询返回了 2 万条数据。
这是另一个优化方向。把能做的事情放到 clickhouse server 端做。也就是很多因子的计算之前我们需要取数据回 python 端再计算的现在如果有可能直接让 clickhouse 来做我们只要结果。
这是我们后续笔记要发表的内容。
量化数据本地化方案全系列发布在大富翁量化网站的这个合集下欢迎前往一次性读完
