Clickhouse: One table to rule them all!
先前几篇笔记中我们探讨了收集海量行情数据的个人技术方案。这些方案之所以被称为个人方案，并非因为其性能较弱, 而是因为这些方案中的数据均存于本地, 并仅适用于单机查询。
成本高昂的数据源 – 在当下这个时节，在线获取金融资讯已变得异常困难。我们已经听说了一些大型机构在允许员工访问万得账户权限以实现资源间的共享协作后，在线获取金融资讯已变得异常困难。我们已经听说了一些大型机构在允许员工访问万得账户权限以实现资源间的共享协作后，在线获取金融资讯已变得异常困难。我们已经听说了一些大型机构在允许员工访问万得账户权限以实现资源间的共享协作后，在线获取金融资讯已变得异常困难.
那些以讹传讹的解决方案
已经到了 2024 年，在行情数据存储领域中你仍可看到大量关于 MySQL 的文章但这绝非最佳选择. 不建议采用 MySQL 即使换成 PostgreSQL 同样不可取. 不采用 PostgreSQL 则会面临诸多问题. 不仅如此 SQL Server 或 Oracle 的出现同样让 MySQL 的地位受到挑战.
还有其他无法满足需求的方案之一是 MongoDB。虽然 MongoDB 能存储大量数据（即 '能装'），但它在处理实时更新或时间序列数据等场景上表现不佳
FluxDB is the first and most renowned time-series database. However, its community edition's performance remains notably underwhelming. Particularly, it imposes a significant limitation on concurrent access. Furthermore, its engine uses Go programming language, which remains substantially slower than C.
Dolphinedb 在性能方面略胜一筹于 Influxdb ，但其社区版对性能的限制较为严格。Tidb 虽然据说运行效率不错 ，但目前我们尚未有机会对其进行全面评估 。
如果存在类似王者般的 ClickHouse 社区版本摆在面前，则还有必要去评估那些青铜吗？
Why click house is so f**ing fast?
Clickhouse 是俄罗斯人开发的产品。它的开发者是 Yandex!（在提及 Yandex! 时，请确保使用感叹号）。搜索引擎必须要处理大量的查询请求以及进行数据分析, 因此催生出了一个性能强大的数据库系统.
在性能层面的优化上，在线查询处理能力得到了显著提升，在硬件级别上
在数据架构方面，在线日志系统（OLAP）广泛采用Columnar（列式）架构的技术基础支持下，在线查询效率得到了显著提升。注意到这一特点时会发现，在数据架构方面，在线日志系统（OLAP）广泛采用Columnar（列式）架构的技术基础支持下，在线查询效率得到了显著提升。
然而该存储方案建立在 merge-tree 基础之上，在查询过程中不仅能够充分利用全部的 CPU 核和磁盘空间，并且还可以充分调用集群中的所有 CPU 核与磁盘资源。从而使其在硬件资源增加时的查询性能得以线性扩展。
在这一部分中应用了许多大数据的相关技术，在具体实现上主要采用了BloomFilter作为索引结构。此外还提供了若干优化策略供用户参考，这也是我们这篇笔记将要介绍的内容：本文旨在探讨如何构建一个高效存储海量行情数据（多达数十亿条）的数据库体系，并力求达到最优性能水平。
实战！先存一个小目标
考虑到在 clickhouse 中理论上可以在同一张表中实现 minute 和 daily 数据的结合但由于实际操作中难以同时获取不同周期的数据因此将它们分别存入不同的数据库显然是更为合理的策略为了便于说明问题 在举例时我们仅采用 minute 线作为示例
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)
sql
在这里引入 clickhouse 时，我们首先注意到它的显著优势之一是其对 sql 核心语法的完美兼容。这一特性使得在设计新版本时，我们不得不面对 influxdb 的挑战 - 感叹他们为何抛弃了原先对 sql 兼容性的同时引入了一套全新的查询语言。
表示这个 ClickHouse 的部署 - 作为一个常见的 IT 任务 - 将使分析师能够轻松上手；因为从事数据分析工作的人通常都熟悉 SQL.
这里有一些技巧，是普通的 SQL 中没有的。
首先是 frame 字段中的 CODEC(Delta, ZSTD) 压缩技术。该方法通过计算行间数据的差值（Delta），将列数据转换为一个稀少的数据向量 – 这样可以在存储空间和读取速度上实现显著优化。实际上，在金融交易数据中，大量的时间戳往往相同或者相差极小（delta很小）。例如，在存储包含超过 5000 支个股 minutely 数据的表时，我们经常会遇到连续 5000 个相同的时间戳 – 这些都可以被高效地压缩为 0！
在OHLC体系中，默认值的选择往往经过深思熟虑。当某一交易日某标的股票停牌时，则其 OHLC 等相关数据会被标记为空。需要注意的是，在ClickHouse数据库中是可以存储这些空值的。然而，在查询操作中需要额外步骤来处理这些空值，并借助join操作将它们整合到结果中。这可能带来一定的性能开销。因此，在这种情况下我们采用了无法出现在实际市场中的数值作为默认值。这样一来所有的原始数据依然得以保留在一起从而提高了存储效率和计算速度。
OHLC数据的波动幅度都很小；同样地,我们采用了Delta编码来进行数据压缩。
但成交量与成交额的变化幅度可能会较大；这会导致压缩措施难以带来预期的好处。
我们能够进行这些优化, 由于我们了解数据的分布特性. 类似地, 在进行数据分析时, 数据分析师必须清楚数据的分布特性. 同样地, 在进行数据分析时, ClickHouse 也需要具备这种能力.
我们采用 OHLC 数据时选择了单精度格式；而针对 factor 我们采用了 64 位浮点数。这显然是必要的原因：虽然看似数值都较小（尤其是 OHLC数据），但 factor数据却不同（它需要更高的精度）。
我们对 Symbol 字段实施了一种优化措施。采用这种编码方案后，在存储 Symbol 的过程中，默认情况下会将数据转换为整数形式而不是字符串形式以提高效率和性能效果显著。这种方法与Pandas中的categorize功能类似
在最后阶段我们设置 frame 和 symbo 为主键字段。我们的大多数查询将依赖于这两个字段的对比分析
我们设计的表，性能究竟怎么样？
让我们依次存入100万条、1000万条以及1亿条数据，并同时计算插入时间和查询时间。在准备数据的过程中我们采用了完全随机的方式生成数据这一做法非常重要。如果我们统一采用相同的数据集则运行速度会更快些
录入一百万条数据耗时仅需8.3秒
录入一千万条数据耗时约需77.7秒
查询结果仅限于两百零二行
录入一亿条数据耗时约需十六分钟
返回结果仅限于两千零二行
录入一千万条数据返回结果仅限于两千零二十行
录入一百万条数据超过数据库最大存储容量
这一结果已经达到令人满意的效果。然而在预期范围内。对此你表示怀疑吗？作为比较依据而言我们在InfluxDB上处理一百万条数据时耗时约55秒（其中网络传输与客户端重组数据耗时约占30秒）。由此可见在这轮比赛中尚无超越InfluxDB的可能。此外在十亿级别下即使MySQL经过至善优化也只能在不到一秒内完成查询但其无法处理超过一亿的数据量。
为什么没有惊喜？我去看了一下我的测试环境：
一个只有 8 个 CPU 核心和 8GB 内存的虚拟机（其底层基于磁盘阵列），并且我已经打开了四个 Visual Studio 窗口（此时系统仅剩余约 0.6GB 的可用内存）。我们对 InfluxDB 进行了测试（运行于物理服务器上），该设备配置了 48 个 CPU 核心以及 96GB 内存，并支持超过 30 亿条数据存储
等到下次有机会，在相同的环境下进行对比实验。然而，在类似的情行数据库上已经完成了相关测试。
一台配备有 macOS 系统的 MacBook Pro 设备上 处理了多达 2.4 亿条数据记录 执行 argmax 查询操作 只需仅耗时 0.9 秒完成该操作 虽然单次运行时间不足以应对高频计算需求 但仍可满足大多数实际应用场景的需求
不过，clickhouse 的测试与我们的测试有很大差别：
在测试期间，在clickhouse的测试中数据规模较小；而我们的系统查询请求则返回了2万条数据。
这属于另一个优化路径。为了实现这一目标, 可将可执行的操作转移至 ClickHouse 服务器端执行, 即大量因素的计算, 过去我们需从 Python 端获取数据后再进行计算, 若有可能实现, 则可以直接由 ClickHouse 完成这些运算工作, 并关注最终结果
这是我们后续笔记要发表的内容。
量化分析数据本地化方案全系列发布在大富翁量化网站的这个合集下，建议您一次性查看全部内容