clickhouse使用入门

,写于2023年5月，请注意时效性。

简要介绍

ClickHouse是一个用于联机分析处理(OLAP)的列式数据库管理系统(DBMS)，拥有着及其卓越的查询速度。OLAP是一种面向分析的处理，用于处理大量的数据并支持复杂的分析和查询操作。诸如BI系统等重数据分析的场景，都应重点考虑使用OLAP数据库，而其中ClickHouse又是OLAP数据库星海中最璀璨的一颗星。

ClickHouse的场景特点

纵使ClickHouse有千般万好，但是能真正契合系统需求的数据库，才是最合适的。因此，在我们正式迈进ClickHouse使用大门之前，我想有必要先识其长短。

上图截自ClickHouse官方文档，与其说这是olap的场景，不妨说是ClickHouse的常见场景。其中我觉得有必要指出的是：

1.”查询相对较少”，这意味着ClickHouse并发查询能力不强（官方建议每秒最多查询100次），原因在于对于每条查询，ClickHouse都会尽可能动用服务器的CPU、内存资源等，而不同于MySQL单条SQL是单线程的，资源消耗更不可控（当然ClickHouse本身也有相关参数可以配置查询消耗的资源情况）。

2.”结果适合于单个服务器的RAM中”，结合上面所说，每条查询都会消耗ClickHouse不少的（内存）资源，因此不要无脑join大表，否则Memorylimitexceeded警告。

在开始更有意义的赞美之前，让我再对ClickHouse进行一些”自由的批评”：

1.尽管ClickHouse与mysql等数据库一样支持标准SQL语法（甚至兼容了mysql的\G语法）以及窗口函数等，但是相关子查询暂未支持，但将来会实现。

2.稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。（稀疏索引只存储非零值，因此在进行点查询时需要遍历整个索引才能找到对应的行，这会导致点查询的性能较低）

ClickHouse基础

连接及数据格式

连接方式

ClickHouse提供了HTTP和TCP以及gRPC三种方式的接口，非常方便，其中ClickHouse-client是基于TCP方式的，不同的client和服务器版本彼此兼容。

以HTTP接口方式访问时，需注意使用GET方法请求时是默认readonly的。换句话说，若要作修改数据的查询，只能使用POST方法。

此外，除了上述的接口形式，ClickHouse甚至支持了MySQL wire通讯协议，生怕像我一样的MySQL boy难以上手。简单的配置之后，就能轻松使用mysqlclient连接ClickHouse服务器，颇有import pytorch as tf之感。不过也有一些限制，不支持prepared查询以及某些数据类型会以字符串形式发送。同样命运的还有PostgreSQL。

当然，更常见的使用方式还是各语言实现的client库。如今ClickHouse的生态早已成熟，无论是各类编程语言亦或是常见的InfrastructureProducts（如kafka、k8s、grafana等），都有现成的库将其结合起来使用。

 

数据格式

ClickHouse支持丰富的输入/输出格式，简单来说就是TSV、CSV、JSON、XML、Protobuf、二进制格式以及一些Hadoop生态下常见的数据格式。此外ClickHouse本身也有一些模式推断相关的函数，能从文件/hdfs等数据源推断出表的结构，算是个有趣的功能。

数据类型

flatten_nested设为0（非默认值）可以无限套娃Nested类型。Alter命令操作Nested类型会受限。

 

SQL语句

ClickHouse支持的SQL语句如上所示，内容太多了。。只简单挑些重点看下，先留个坑。

SELECT

小技巧：

select取最终列时，可以使用COLUMNS表达式来以re2的正则表达式语法查找匹配的列，如COLUMNS(‘a’)可以匹配aa，ab列，效果类似python的re.search方法，查询大宽表的时，这个功能还是非常好用的。

此外，配合APPLY(<func>)，EXCEPT(col_name..)，REPLACE(<expr>ascol_name)这三个语法糖，有时能大大简化SQL，如：

SELECT COLUMNS(‘_w’) EXCEPT(‘test’) APPLY(max) from my_table

就能迅速找出带_w且不带test的列，并计算他们的最大值。

有时需要对单独某个查询设置特殊配置时，也可在语句最后直接加上SETTINGS xx，这样配置就只会对本次查询生效。

主键列不可指定。

当表的TTL过期时，过期行会被操作（删除或转移），还可通过WHERE和GROUP BY条件指定符合条件的行。GROUP BY表达式必须是表主键的前缀。

数据副本

MergeTree系列的引擎的表都支持数据副本，只需在引擎名前加上Replicated。

 

ReplacingMergeTree

该引擎和MergeTree的不同之处在于它会删除排序键值相同的重复项，适用于在后台清除重复的数据以节省空间。但只会在数据合并期间进行，而合并会在后台一个不确定的时间进行。虽然可以调用OPTIMIZE语句发起计划外的合并，但须知OPTIMIZE语句会引发对数据的大量读写。

SummingMergeTree

当合并SummingMergeTree表的数据片段时，ClickHouse会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的sum值。即便如此，当需要聚合数据时仍应该使用sum函数来聚合，因为后台合并的时间是不确定的。

对于AggregateFunction 类型的列，ClickHouse 根据对应函数表现为AggregatingMergeTree引擎的聚合。

而对于Nested类型的列，ClickHouse会将第一列视作key，其他列视作values进行聚合。

AggregatingMergeTree

将一个数据片段内所有具有相同排序键的行替换成一行，这一行会存储一系列聚合函数的状态。引擎使用AggregateFunction和SimpleAggregateFunction类型来处理所有列。可以看做SummingMergeTree是AggregatingMergeTree的特化（表现上而言）。

可以使用AggregatingMergeTree表来做增量数据的聚合统计，包括物化视图的数据聚合。

要插入数据，需使用带有-State-聚合函数的INSERT SELECT语句。从AggregatingMergeTree表中查询数据时，需使用GROUP BY子句并且要使用与插入时相同的聚合函数，但后缀要改为-Merge。

CollapsingMergeTree

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行将会被保留。

Sign为1和-1的行应按照一定的顺序写入，合并相当取决于记录的一致性，否则实现不了预期的折叠效果（即先Sign=1后Sign=-1），聚合统计时也应考虑上Sign字段对结果的影响。可以使用Final修饰符强制进行折叠而不聚合，但是效率低下。

此外，插入时Sign=1和Sign=-1的记录应该在两次insert语句中分别插入，以保证他们在不同的数据片段（part），否则也不会执行合并操作。

个人觉得，难用，或者是我没找到正确的打开方式。

VersionedCollapsingMergeTree

顾名思义，是上面那位的兄弟，只不过多了一个Version列，允许以多个线程的任何顺序插入数据。Version列有助于正确折叠行，即使它们以错误的顺序插入。

当ClickHouse合并数据部分时，它会删除具有相同主键和版本但Sign值不同的一对行。

当ClickHouse插入数据时，它会按主键对行进行排序。 如果Version列不在主键中，ClickHouse将其隐式添加到主键作为最后一个字段并使用它进行排序。

由于ClickHouse具有不保证具有相同主键的所有行都将位于相同的结果数据片段中，甚至位于相同的物理服务器上的特性，以及上面说的数据合并时机的不确定性，所以想要最终的数据还是免不了group by等聚合操作。

GraphiteMergeTree

该引擎用来对Graphite类型数据进行瘦身及汇总。如果不需要对Graphite数据做汇总，那么可以使用任意的表引擎；但若需要，那就采用GraphiteMergeTree引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

Log引擎系列

共同特点：

• 数据存储在磁盘上。
• 写入时将数据追加在文件末尾。
• 支持并发访问数据时上锁。（执行insert语句时，表会被上写锁）
• 不支持突变操作。（参见alter）
• 不支持索引。（表明范围查询效率不高）
• 非原子地写入数据。

各引擎差异：

Log引擎为表中的每一列使用不同的文件。StripeLog将所有的数据存储在一个文件中。因此StripeLog引擎在操作系统中使用更少的描述符，但是Log引擎提供更高的读性能。两者都支持并发的数据读取。

TinyLog引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TinyLog引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。

Log

Log与TinyLog的不同之处在于，”标记” 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。Log引擎适用于临时数据。

StripeLog

需要写入许多小数据量（小于一百万行）的表的场景下使用这个引擎。

在这种情况下，分布式表会跨服务器分发插入数据。为了写入分布式表，必须要配置分片键（最后一个参数）。当然，如果只有一个分片，则写操作在没有分片键的情况下也能工作，因为这种情况下分片键没有意义。

数据是异步写入的。对于分布式表的 INSERT，数据块只写本地文件系统。之后会尽快地在后台发送到远程服务器。

如果在 INSERT 到分布式表时服务器节点丢失或重启（如，设备故障），则插入的数据可能会丢失。如果在表目录中检测到损坏的数据分片，则会将其转移到broken子目录，并不再使用。

Merge

本身不存储数据，但可用于同时从任意多个其他的表中读取数据。 读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。

创建语法：

如果tables_regexp命中了Merge 表本身，也不会真正引入，以免循环引用，但创建两个表递归读取对方数据是可行的。

Merge引擎的一个典型应用是可以像使用一张表一样使用大量的TinyLog表。

Executable和ExecutablePool

这两个引擎用于关联脚本和具体表，表中的数据将由执行脚本后生成。脚本被放在”users_scripts”目录下。创建表时不会立即调用脚本，脚本将在表被查询时调用。

刚开始感觉这个引擎没什么用，为什么我不直接单独跑脚本把数据收集好之后再将它们插入表呢？转念想到脚本代码仓库里的几百个（无名）脚本及对应的（无名）表，瞬间感觉这功能还怪有用的。（查找表对应的生成脚本）

应用及可能的坑点

应用

ClickHouse典型应用场景主要包括以下几个方面：

1. 大数据存储和分析：ClickHouse能够高效地存储和处理海量数据，支持PB级别的数据存储和分析，可以快速地处理大规模数据分析和数据挖掘任务。
2. 实时数据分析和查询：ClickHouse支持实时查询和分析，具有高速的数据读取和计算能力，可以在秒级别内返回查询结果，适用于需要快速响应数据查询和分析的业务场景。
3. 日志处理和分析：ClickHouse能够高效地处理日志数据，支持实时的日志分析和查询，可以帮助企业快速地发现和解决问题。
4. 业务智能分析：ClickHouse支持复杂的数据分析和计算，可以进行高级的数据挖掘和机器学习算法，帮助企业进行业务智能分析和决策。

总的来说，ClickHouse适用于需要处理大规模数据和实时查询的业务场景，例如数据报表、日志分析、业务智能分析、广告平台等。

其他要说的

。比如中文文档中说ClickHouse不支持窗口函数，但英文文档中表示已经支持；中文文档中没有projection的介绍；中文文档中表示ClickHouse使用ZooKeeper维护元数据，然而在英文文档中表示使用ClickHouse Keeper维护元数据；等等等等。同样的，你的生产环境的ClickHouse版本也许与ClickHouse最新版有不小差距，所以在你考虑使用某个功能时，记得先看下当前版本是否已经支持。

。

此外ClickHouse本身引擎对子查询的SQL优化效率不高，应尽量避免复杂的子查询语句。否则这些”cool cooler coolest”的SQL，在集群负载压力逐渐上来之后，可能会变成半夜里响个不停的业务告警通知。

后记

原本打算从头到尾细看一遍官方文档+搜索对应关键词的文章来完整系统地了解一下ClickHouse。但内容之多，懒癌晚期发作加上别的原因最后写得有些虎头蛇尾。后面有人看、有心情、，理解不到位之处，还请大佬『务必回复！』🐶，让我知道文章错哪了。

什么，对你有帮助，甚至你都忍不住点了收藏却不愿意点个赞？

转载请注明出处(～￣▽￣)～严禁用于商业目的的转载～