前面几篇笔记我们讨论了存储海量行情数据的个人技术方案。它们之所以被称之为个人方案,并不是因为性能弱,而是指在这些方案中,数据都存储在本地,也只适合单机查询。
数据源很贵 – 在这个冬天,我们已经听说,某些上了规模的机构,也在让员工共享万得账号了。所以,共享网络存储,从而只需要一个数据账号,就成为合理的需求。更不必说,集中管理才可能让 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时,使用的是物理机,48CPU+96GB内存,总记录是30多亿条。
改天再找机会在同样的环境下进行对比测试。不过,clickhouse 员工已经在类似的行情数据库上进行了测试:
在一台 macbook pro 上,在 2.4 亿条记录中,进行 argmax 的查询,只用了 0.9 秒!这个速度虽然不够跑高频,但已足够多数场景下使用了。
不过,clickhouse 的测试与我们的测试有很大差别:
在 clickhouse 的测试中,它返回的数据量很小;而在我们的测试中,要求查询返回了 2 万条数据。
这是另一个优化方向。把能做的事情放到 clickhouse server 端做。也就是,很多因子的计算,之前我们需要取数据回 python 端再计算的,现在如果有可能,直接让 clickhouse 来做,我们只要结果。
这是我们后续笔记要发表的内容。文章来源:https://www.toymoban.com/news/detail-802917.html
量化数据本地化方案全系列发布在大富翁量化网站的这个合集下,欢迎前往一次性读完!文章来源地址https://www.toymoban.com/news/detail-802917.html
到了这里,关于Clickhouse: One table to rule them all!的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!