clickhouse存储过程语法（ClickHouse存储结构及索引详解）

本文目录

• ClickHouse存储结构及索引详解
• ClickHouse 备份恢复
• clickhouse分片数据和all表不一致
• clickhouse
• where别名clickhouse
• clickhouse与kafka集成
• clickhouse常见的一些问题

ClickHouse存储结构及索引详解

本文基于ClickHouse 20.8.5.45版本编写，操作系统使用的是CentOS 7.5，主要介绍MergeTree表引擎的存储结构以及索引过程。

刚刚创建的表只在数据目录下生成了一个名为 test_merge_tree 文件夹(具体路径为data/default/test_merge_tree)，并没有任何数据，接下来往该表里面插入一条数据，看看会生成哪些文件。

在test_merge_tree目录下使用tree命令可以看到刚刚的那条命令生成了一个名为 200002_1_1_0 的文件夹。

在介绍这些文件之前先介绍一下200002_1_1_0这个目录的命名规则

当分区发生合并时，新的分区目录名称命名规则将会在接下来介绍，这里不做详述。

在介绍这部分之前，需要先将min_compress_block_size配置改小，以方便分析mrk2和bin文件，其默认值为65535。

修改方法为在 users.xml 文件的 profiles 里面增加以下配置

修改完后重启clickhouse-server服务，然后再用以下命令查看是否修改成功

刚刚已经插入了一条数据，但是那一条数据不具有代表性，所以这次决定多插入几条数据再来分析。

上面这条命令产生了个新的分区目录 200002_2_2_0 ，此目录下的文件前面已经讲过，现在重点分析以下几个文件的存储格式

MergeTree表会按照主键字段生成primary.idx，用于加快表查询。前面创建表时使用的是(Id, Name)两个字段作为主键，所以每隔index_granularity行数据就会取(Id, Name)的值作为索引值，由于index_granularity被设置为2，所以每隔两行数据就会生成一个索引。也就是说会使用(3,’Lisa’), (6,’Meimei’), (31,’vincent’)作为索引值。

这里我只介绍第一个索引(3,’Lisa’)的存储格式，剩下的可以自己去梳理。Id是UInt64类型的，所以使用8字节来存储。从上图可以看出前8个字节为0x03，以小端模式来存储，接下来我们可以看到其它文件都是以小端模式来存储。Name是String类型，属于变长字段，所以会先使用1个字节来描述String的长度，由于Lisa的长度是4，所以第9个字节为0x04，再接下来就是Lisa的ASCII码。

mrk2文件格式比较固定，primary.idx文件中的每个索引在此文件中都有一个对应的Mark，Mark的格式如下图所示：

通过primary.idx中的索引寻找mrk2文件中对应的Mark非常简单，如果要寻找第n(从0开始)个index，则对应的Mark在mrk2文件中的偏移为n*24，从这个偏移处开始读取24 Bytes即可得到相应的Mark。

bin文件由若干个Block组成，由上图可知Id.bin文件中包含两个Block。每个Block主要由头部的Checksum以及若干个Granule组成，Block的格式如下图所示：

每个Block都会包含若干个Granule，具体有多少个Granule是由参数min_compress_block_size控制，每次Block中写完一个Granule的数据时，它会检查当前Block Size是否大于等于min_compress_block_size，如果满足则会把当前Block进行压缩然后写到磁盘中，不满足会继续等待下一个Granule。结合上面的INSERT语句，当插入第一个Granule(3, 4)时，数据的的size为16，由于16 《 24所以会等第二个Granule，当插入第二个Granule(6, 12)后数据的size为32，由于32 》 24所以会把(3, 4, 6, 12)压缩放到第一个Block里面。最后面的那个31由于是最后一条数据，就放到第二个Block里面。

partition.dat文件里面存放的是分区表达式的值，该分区表达式生成的值为200002，UInt32类型，转换成16进制就是0x00030d42。

minmax文件里面存放的是该分区里分区字段的最小最大值。分区字段Birthday的类型为Date，其底层由UInt16实现，存的是从1970年1月1号到现在所经过的天数。通过上面的INSERT语句我们可以知道Birthday的最小值为2000-02-03，最大值为2000-02-08。这两个时间转换成天数分别为10990和10995，再转换成16进制就是0x2aee和0x2af3。

属于同一个分区的不同目录，ClickHouse会在分区目录创建后的一段时间自动进行合并，合并之后会生成一个全新的目录，以前老的分区目录不会立马删除，而是在合并后过一段时间再删除。新的分区目录名称遵循以下规则：

所以上面的两个分区目录200002_1_1_0和200002_2_2_0在过一段时间后最终会变成一个新的分区目录200002_1_2_1。由此可见如果你频繁插入数据会产生很多分区目录，在合并的时候会占用很多资源。所以最好一次插入很多条数据，尽量降低插入的频率。

通过上面的介绍相信大家已经对ClickHouse的索引结构有所了解，接下来用一张图简要描述Id字段的索引过程。

其它列的索引过程类似，这里就不一一赘述了，有兴趣的朋友可以自己去研究。

本文通过一个简单的例子来分析ClickHouse的存储结构，整个逻辑力求简洁明了，希望通过本文能够让喜欢ClickHouse的朋友对它的索引有个清晰的认识。

ClickHouse 备份恢复

目前Clickhouse的备份方式有以下几种：

clickhouse中，数据文件大小为 900M，实际导出会远大于900M，语句 20G左右【测试时没执行完成】

需要先建好表，因此最好备份下metadata数据

语法：

该操作为指定分区创建一个本地备份。
如果 PARTITION 语句省略，该操作会一次性为所有分区创建备份。整个备份过程不需要停止服务

默认位置：/var/lib/clickhouse/shadow/,若没执行过则还不存在此文件,会生成在设置的数据路径下

查看备份

因为 /shadow/ 目录下次备份时候需要清空，因此将备份迁移到指定路径

vi /etc/clickhouse-backup/config.yml

备份语法：
clickhouse-backup create 《backup_name》

恢复语法：
clickhouse-backup restore 备份名称

当前测试恢复数据的版本是 20.5.4.40，备份是直接存在 metadata 信息的

当前备份

由于clickhouse-backup 当前版本不会备份metadata,因此自己复制一份metadata数据

clickhouse分片数据和all表不一致

ClickHouse支持My**L大多数语法，迁移成本低，可以使用建表同时导入数据来实现从My**L迁移到ClickHouse: CREATE TABLE table_name ENGINE = Mergetree AS SELECT * FROM mysql(’host:port’, ’db’, ’database’, ’user’, ’password’)

clickhouse

算数：

|运算符|示例|注释|

|----|----|----|

|加（a，b）|a + b|计算数字的总和。您还可以添加带有日期或日期和时间的整数。在日期的情况下，添加整数意味着添加相应的天数。对于具有时间的日期，这意味着添加相应的秒数。|

|减（a，b）|a - b|您还可以根据时间计算日期或日期的整数|

|乘以（a，b）|a * b|计算数字的乘积|

|除（a，b）|a / b|计算数字的商。结果类型始终是浮点类型。它不是整数除法。对于整数除法，请使用’intDiv’函数。当除以零时，你得到’inf’，’ - inf’或’nan’。|

|整数除|intDiv（a，b）|计算数字的商。分为整数，向下舍入（按绝对值）。除以零或将最小负数除以-1时抛出异常。|

|异常除|intDivOrZero（a，b）|与’intDiv’的不同之处在于它在除以零或将最小负数除以-1时返回零。|

|取余数|a % b|计算除法后的余数。如果参数是浮点数，则通过删除小数部分将它们预转换为整数。截断除法用于负数。除以零或将最小负数除以-1时抛出异常。|

|取反a| -a | 用反转符号计算一个数字。|

|取绝对值|abs（a）|计算数字（a）的绝对值。也就是说，如果a 《0，则返回-a。对于无符号类型，它不执行任何操作。对于有符号整数类型，它返回无符号数。|

|取最大公约数|***（a，b）|返回数字的最大公约数。除以零或将最小负数除以-1时抛出异常|

|取最小公倍数|lcm（a，b）|返回数字的最小公倍数。除以零或将最小负数除以-1时抛出异常。|

比较

|运算符|示例|注释|

|----|----|----|

|相等|a == b||

|不等|a！=b,a《》b||

|小于|a《b||

|大于|a》b||

|小于等于|a《=b ||

|大于等于|a》=b ||

逻辑运算符

接受任何数字类型，但返回等于0或1的UInt8数。

零作为参数被认为是“假”，而任何非零值被认为是“真”。

|运算符|示例|

|----|----|

|并|AND|

|或|OR|

|否|NOT|

类型转换

转换为数值类型：

toUInt8，toUInt16，toUInt32，toUInt64

toInt8，toInt16，toInt32，toInt64

toFloat32，toFloat64

TODATE，toDateTime

toUInt8OrZero，toUInt16OrZero，toUInt32OrZero，toUInt64OrZero，toInt8OrZero，toInt16OrZero，toInt32OrZero，toInt64OrZero，toFloat32OrZero，toFloat64OrZero，toDateOrZero，toDateTimeOrZero

toUInt8OrNull，toUInt16OrNull，toUInt32OrNull，toUInt64OrNull，toInt8OrNull，toInt16OrNull，toInt32OrNull，toInt64OrNull，toFloat32OrNull，toFloat64OrNull，toDateOrNull，toDateTimeOrNull

toDecimal32（value，S），toDecimal64（value，S），toDecimal128（value，S）

该value可以是一个数字或一个字符串。S参数指定小数位的数量。

转换为字符串类型：

toString

用于在数字、字符串(但不是固定字符串)、日期和日期与时间之间进行转换的函数。所有这些函数都接受一个参数。

在转换为字符串或从字符串转换时，将使用与选项卡分隔格式(以及几乎所有其他文本格式)相同的规则对值进行格式化或解析。如果无法解析字符串，则抛出异常并取消请求。

当将日期转换为数字或反之亦然时，日期对应自Unix纪元开始以来的天数。当将日期与时间转换为数字或反之亦然时，日期与时间对应自Unix纪元开始以来的秒数。

toDate/toDateTime

如果从UInt32、Int32、UInt64或Int64数字类型转换到日期，并且该数字大于或等于65536，则该数字将被解释为Unix时间戳(而不是天数)，并四舍五入到日期。这允许支持写入“toDate(unix_timestamp)”的常见情况，否则这将是一个错误，需要编写更麻烦的“toDate(toDateTime(unix_timestamp))”。

日期和带时间的日期之间的转换以一种自然的方式执行:添加一个空时间或删除时间。

DateTime参数的toString函数可以接受包含时区名称的第二个字符串参数。例如:Asia/Yekaterinburg在本例中，时间是按照指定的时区格式化的。

toString(now(), ’Asia/Yekaterinburg’)

toFixedString(s, N)

将字符串类型参数转换为FixedString(N)类型(长度为N的字符串)。N必须是常量。如果字符串的字节数小于N，则向右传递null字节。如果字符串的字节数超过N，则抛出异常。

toStringCutToZero(s)

接受字符串或FixedString参数。返回在找到的第一个零字节处截断内容的字符串。

reinterpretAsUInt8, reinterpretAsUInt16, reinterpretAsUInt32, reinterpretAsUInt64

reinterpretAsInt8, reinterpretAsInt16, reinterpretAsInt32, reinterpretAsInt64

reinterpretAsFloat32, reinterpretAsFloat64

reinterpretAsDate, reinterpretAsDateTime

这些函数接受字符串并将字符串开头的字节解释为按主机顺序排列的数字(小端)。如果字符串不够长，函数的工作方式就像用必要的空字节数填充字符串一样。如果字符串比需要的长，则忽略额外的字节。日期被解释为自Unix纪元开始以来的天数，带有时间的日期被解释为自Unix纪元开始以来的秒数。

reinterpretAsString

该函数接受一个数字、日期或带时间的日期，并返回一个字符串，其中包含以主机顺序(小端)表示相应值的字节。空字节从末尾删除。

reinterpretAsFixedString

该函数接受一个数字、日期或带时间的日期，并返回一个FixedString，其中包含以主机顺序(小端)表示相应值的字节。空字节从末尾删除。

CAST(x, t)

将“x”转换为“t”数据类型

转换为FixedString(N)只适用于String或FixedString(N)类型的参数。

toIntervalYear, toIntervalQuarter, toIntervalMonth, toIntervalWeek, toIntervalDay, toIntervalHour, toIntervalMinute, toIntervalSecond

将数字类型参数转换为区间类型(持续时间)。interval类型实际上非常有用，您可以使用这种类型的数据直接对Date或DateTime执行算术操作。同时，ClickHouse为声明间隔类型数据提供了更方便的语法。例如:

```

WITH

    toDate(’2019-01-01’) AS date,

    INTERVAL 1 WEEK AS interval_week,

    toIntervalWeek(1) AS interval_to_week

SELECT

    date + interval_week,

    date + interval_to_week

```

parseDateTimeBestEffort

将数字类型参数解析为日期或DateTime类型。与toDate和toDateTime不同，parseDateTimeBestEffort可以改进更复杂的日期格式

parseDateTimeBestEffortOrNull

与parseDateTimeBestEffort相同，只是当遇到无法处理的日期格式时返回null。

parseDateTimeBestEffortOrZero

与parseDateTimeBestEffort相同，只是当遇到无法处理的日期格式时，它返回零日期或零日期时间。

where别名clickhouse

ClickHouse是一个完全面向列式的分布式数据库。数据通过列存储，在查询过程中，数据通过数组来处理(向量或者列Chunk)。当进行查询时，操作被转发到数组上，而不是在特定的值上。因此被称为”向量化查询执行”，相对于实际的数据处理成本，向量化处理具有更低的转发成本。
这个设计思路并不是新的思路理念。历史可以追溯到``APL``编程语言时代：``A+``, ``J``, ``K``, and ``Q``。数组编程广泛用于科学数据处理领域。而在关系型数据库中：也应用了``向量化``系统。
在加速查询处理上，有两种的方法：向量化查询执行和运行时代码生成。为每种查询类型都进行代码生成，去除所有的间接和动态转发处理。这些方法并不比其他方法好，当多个操作一起执行时，运行时代码生成会更好，可以充分累用CPU执行单元和Pipeline管道。
向量化查询执行实用性并不那么高，因为它涉及到临时向量，必须写到缓存中，并读取回来。如果临时数据并不适合L2缓存，它可能是一个问题。但是向量化查询执行更容易利用CPU的SIMD能力。一个研究论文显示将两个方法结合到一起效果会更好。ClickHouse主要使用向量化查询执行和有限的运行时代码生成支持(仅GROUP BY内部循环第一阶段被编译)。

clickhouse与kafka集成

clickhouse支持与多种存储引擎集成，可以从集成的引擎里面读取消息，然后写到真正的数据存储表里。

clickhouse批量写入的性能比较好，我们的业务场景下会大批量的产生数据，如果使用clickhouse-jdbc去写的，写入时机和每批次写入的数量不好把控，最终选择了先将消息写入kafka，然后由clickhouse从kafka消费数据，clickhouse server消费到数据之后写入真正的数据表。

clickhouse集成kafka引擎见官方文档：
***隐藏网址***

下面的介绍会与官方文档有重复，然后补充一些集成过程中遇到的坑。

下面介绍clickhouse与kafka集成的步骤，clickhouse版本是22.1.3.7

必要参数

可选参数

关于必选参数中的kafka_format参数，参见Formats部分，format具体解释如下
***隐藏网址***

JSONEachRow, JSONStringsEachRow, JSONCompactEachRow, JSONCompactStringsEachRow
这几种格式，ClickHouse会将行输出为用换行符分隔的JSON值，这些输出数据作为一个整体时，由于没有分隔符(,)因而不是有效的JSON文档。
官方文档给了一些示例。

由于我的真实的数据表，有一个字段是json类型的字符串，但是一开始设置kafka_format的类型为JSONEachRow时，从kafka消费数据会报错，所以kafka_format格式设置成了JSONAsString,具体的错误后面贴出来。

创建kafka引擎表，用于从kafka消费数据

由于我的数据结构里有嵌套json，如果使用JSONEachRow，有个字段是json类型的字符串，带转义字符，导致clickhouse解析失败，没找到解决办法，所以使用了JSONAsString格式。

一个简单的MergeTree引擎的表，其中content是json格式的字符串。

创建的物化视图用于把从kafka消费到的数据，写到真实的数据表里，在这个例子里，msg_json_source从kafka消费到数据，然后通过物化视图msg_json_source_c***umer将消费到的数据写到真实的数据表msg_target中。

由于从kafka消费到的数据就是一个json字符串，在这里使用JSONExtractString等json字段提取工具，提取msg里的字段，比如biz,sender_id,content等字段。

status_time原本计划用DatTime64类型的，但是这个时间格式有坑，最终选择了使用UInt64存毫秒级时间戳，具体的问题下面再介绍。

在clickhouse创建好3张表之后(kafka引擎表，真实数据表，物化视图表)，往kafka发消息
本地安装一个简易的kafka服务端，然后创建topic

创建好topic之后，使用Java客户端往kafka发消息，使用confluent client发也可以。
添加kafka依赖

实体类,使用fastjson的@JSONField注解，实体类转字符串的时候，将驼峰转换为下划线

测试类

最终发送完，我们查看一下clickhouse里的数据表的数据,可以发现我们发送到kakfa里的数据，已经成功的消费，并且写入到真实的数据表里了。

当时测试环境部署的版本是21.9，但是这个版本有问题，不推荐安装，建议直接部署22以上的clickhouse

我一开始就是使用的JSONEachRow格式，但是我的消息体里还有嵌套的json，类似下面这种格式，里面有个字段还是个json，转行成字符串带转义字符。
然后消息体的string字符串贴一条在这里

然后clickhouse解析消息体报错，当时的错找不到了，现在复现不出来了，非常的难顶。。。。
后来因为赶版本的原因把kafka_format换成了JSONAsString。

clickhouse是支持DateTime64格式的，可以到毫秒级，但是实际使用过程中却有些坑在，

首先是有的客户端解析毫秒字符串有问题，其次是使用JSONExtract*的方法，会有差异，再然后是jdbc查询的时候，也会导致时间查询有问题。
拿毫秒时间戳和秒级时间戳做试验，clickhouse-server版本是22.3.1.1

把上面的kafka引擎表拿出来改一下

其中status_time这个字段的类型改成DateTime64(3, ’Asia/Shanghai’)，使用JSONExtractUInt提取时间，看下效果

首先发条数据，数据内容如下

传入的是毫秒级时间戳，然后数据表存储的时候就变成了2282年

然后如果传入秒级的时间戳，真实的数据是这样

clickhouse存储的时候看着时间正常了，但是毫秒丢失了

然后修改一下物化视图的字段提取方式，之前是 JSONExtractUInt(msg,’status_time’) as status_time，现在改成使用 JSONExtractString(msg,’status_time’) as status_time提取时间
会发现时间类型又正常了。

这一条数据内容如下

最终使用JSONExtractString提取毫秒时间戳，得到了正确的DateTime64的时间，非常的神奇

最终我决定来了个釜底抽薪的方法，时间直接用UInt64存，因为我发送出去的数据是毫秒级时间戳，最终存时间戳，查询时间范围的时候直接用long类型的数据between好了。

这也是无奈之举，万一哪天server更新版本，导致时间出现问题，那就完蛋了，希望后面时间可以稳定一点吧。

clickhouse常见的一些问题

一般情况下，如果不是主动使用systemctl stop clickhouse-server 停止clickhouse
而是使用kill -9 pid关闭clickhouse，或者异常奔溃，那么如果一切正常的情况下clickhouse server 10s检测进程，自动重启。

登录机器cat /etc/cron.d/clickhouse-server
*/10 * * * * root (which service 》 /dev/null 2》&1 && (service clickhouse-server condstart ||:)) || /etc/init.d/clickhouse-server condstart 》 /dev/null 2》&1

默认会10s检测一下服务进程是否正常，否则重启，检测时间可以调。/etc/init.d/clickhouse-server

在执行分布式DDL的时候出现这个问题一般是有一个节点处于假死状态，但是节点又没有完全奔溃，一般报错如下
Code: 159. DB::Exception: Received from xxxxx:29000. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000xxxxxxx is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 1 unfinished hosts (0 of them are currently active), they are going to execute the query in background.
distributed_ddl_task_timeout 执行超过了默认的180s。
首先检查异常节点机器网络，磁盘等信息，然后检查ck状态。一般都是磁盘满了或者网络问题，很少有zk集群出问题。处理方式的话都是清理磁盘和修复网络。

Code: 458, e.displayText() = DB::ErrnoException: Cannot unlink file /data2/clickhouse/store/488/488da1e0-a9ee-4191-8376-0daaa4e0314d/format_version.txt, errno: 2, strerror: No such file or directory (version 21.3.4.25 (official build))

clickhouse 集群在建分布式表的时候出现 clickhouse zookeeper All connection tries failed
如果配置没啥问题，zk和ck集群也没啥问题，重启下zk即可恢复
查询出现AST is too big. Maximum: 500000
程序报错AST is too big. Maximum: 500000，语法树元素个数超过限制错误，说明查询sql很长很复杂，一般情况不会有，要木优化sql，要木修改集群配置
在user.xml 添加
《max_ast_elements》10000000《/max_ast_elements》
《max_expanded_ast_elements》10000000《/max_expanded_ast_elements》

报错 DB::Exception: Replica xxxxx already exists 。

CK会对同一个block保证重复插入的insert的幂等性，会检测重复，默认会去重，使用 insert_deduplicate 配置。如果不需要去重则可以使用 SET insert_deduplicate=0 ，但不推荐这样做。

查询超过了限制的时间（60s）,需要优化sql，或者做预聚合

一次写入的分区数超过100，一般情况下不会出现一次写操作写100个分区的情况，解决方法1：查看写入的数据是否异常，为啥会写100个分区，一般是按时间分区，是不是时间解析错误了。解决方案2：在user.xml配置文件中添加《max_partiti***_per_insert_block》配置项