2024年最全Clickhouse One table to rule them all!(1)，2024年最新附面试答案

本文链接：https://blog.youkuaiyun.com/2401_84975387/article/details/138906225

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

实战！先存一个小目标

尽管在 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 来做，我们只要结果。

这是我们后续笔记要发表的内容。

量化数据本地化方案全系列发布在大富翁量化网站的这个合集下，欢迎前往一次性读完！

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

cs/618668825)**