clickhouse

最新推荐文章于 2026-05-03 14:21:40 发布

原创最新推荐文章于 2026-05-03 14:21:40 发布 · 2.6k 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#clickhouse

clickhouse 专栏收录该内容

1 篇文章

订阅专栏

什么是ClickHouse

ClickHouse是俄罗斯的Yandex于2016年开源的⼀个⽤于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System)，简称CH , 主要⽤于在线分析处理查询（OLAP），能够使⽤ SQL查询实时⽣成分析数据报告。

ClickHouse是⼀个完全的列式数据库管理系统，允许在运⾏时创建表和数据库，加载数据和运⾏查询，⽽⽆需重新配置和重新启动服务器，⽀持线性扩展，简单⽅便，⾼可靠性，容错。

它⽐较快

ClickHouse 快的原因

四个⽅⾯

它的数据剪枝能⼒⽐较强，分区剪枝在执⾏层，⽽存储格式⽤局部数据表示，就可以更细粒度地做⼀些数据的剪枝。它的引擎在实际使⽤中应⽤了⼀种现在⽐较流⾏的 LSM ⽅式。
它对整个资源的垂直整合能⼒做得⽐较好，并发 MPP+ SMP 这种执⾏⽅式可以很充分地利⽤机器的集成资源。它的实现⼜做了很多性能相关的优化，它的⼀个简单的汇聚操作有很多不同的版本，会根据不同 Key 的组合⽅式有不同的实现。对于⾼级的计算指令，数据解压时，它也有少量使⽤
ClickHouse 是⼀套完全由 C++ 模板 Code 写出来的实现，代码还是⽐较优雅的。
ClickHouse是⼀个完全的列式数据库

什么是OLAP

联机分析处理OLAP是⼀种软件技术，它使分析⼈员能够迅速、⼀致、交互地从各个⽅⾯观察信息，以达到深⼊理解数据的⽬的。它具有FASMI(Fast Analysis of Shared Multidimensional Information)，即共享多维信息的快速分析的特征

OLAP展现在⽤户⾯前的是⼀幅幅多维视图。

维的层次观察数据的某个特定⻆度

维的成员数据项在某维中位置的描述

度量多维数组的取值

钻取改变维的层次，变换分析的粒度

切⽚和切块关⼼度量数据在剩余维上的分布维只有两个切⽚三个或以上切块

旋转在表格中重新安排维的放置

ClickHouse特征

真正的列式数据库管理系统

在⼀个真正的列式数据库管理系统中，除了数据本身外不应该存在其他额外的数据。

它允许在运⾏时创建表和数据库、加载数据和运⾏查询，⽽⽆需重新配置或重启服务。

数据压缩

ClickHouse提供了两种数据压缩⽅式供我们选择:LZ4和ZSTD。

数据的磁盘存储

ClickHouse被设计⽤于⼯作在传统磁盘上的系统，它提供每GB更低的存储成本，但如果有可以使⽤SSD和内存，它也会合理的利⽤这些资源。

多核⼼并⾏处理

ClickHouse会使⽤服务器上⼀切可⽤的资源，从⽽以最⾃然的⽅式并⾏处理⼤型查询。

多服务器分布式处理

在ClickHouse中，数据可以保存在不同的shard上，每⼀个shard都由⼀组⽤于容错的replica组成，查询可以并⾏地在所有shard上进⾏处理。这些对⽤户来说是透明的

⽀持SQL

ClickHouse⽀持基于SQL的声明式查询语⾔

⽀持的查询包括 GROUP BY，ORDER BY，IN，JOIN以及⾮相关⼦查询。

不⽀持窗⼝函数和相关⼦查询。

向量引擎

为了⾼效的使⽤CPU，数据不仅仅按列存储，同时还按向量(列的⼀部分)进⾏处理，这样可以更加⾼效地使⽤CPU。

实时的数据更新

ClickHouse⽀持在表中定义主键。为了使查询能够快速在主键中进⾏范围查找，数据总是以增量的⽅式有序的存储在MergeTree中。因此，数据可以持续不断地⾼效的写⼊到表中，并且写⼊的过程中不会存在任何加锁的⾏为。

索引

按照主键对数据进⾏排序，这将帮助ClickHouse在⼏⼗毫秒以内完成对数据特定值或范围的查找。

适合在线查询

在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到⽤户的⻚⾯中。

⽀持近似计算

ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进⾏加速的⽅法：

⽤于近似计算的各类聚合函数
基于数据的部分样本进⾏近似查询。
不使⽤全部的聚合条件，通过随机选择有限个数据聚合条件进⾏聚合。

⽀持数据复制和数据完整性

ClickHouse使⽤异步的多主复制技术。当数据被写⼊任何⼀个可⽤副本后，系统会在后台将数据分发给其他副本，以保证系统在不同副本上保持相同的数据。

ClickHouse性能

优点

1，为了⾼效的使⽤CPU，数据不仅仅按列存储，同时还按向量进⾏处理

2，数据压缩空间⼤，减少IO；处理单查询⾼吞吐量每台服务器每秒最多数⼗亿⾏

3，索引⾮B树结构，不需要满⾜最左原则；只要过滤条件在索引列中包含即可；即使在使⽤的数据不在索引中，由于各种并⾏处理机制ClickHouse全表扫描的速度也很快；

4，写⼊速度⾮常快，50-200M/s，对于⼤量的数据更新⾮常适⽤。

缺点

不⽀持事务，不⽀持真正的删除/更新；
不⽀持⾼并发，官⽅建议qps为100，可以通过修改配置⽂件增加连接数，但是在服务器⾜够好的情况下；
不⽀持真正的删除/更新⽀持不⽀持事务
不支持二级索引
有限的支持sql,join实现与众不同
不支持窗口功能,
元数据需要人工干预维护
sql满足日常80%语法,新版支持类sqljoin,性能不好
尽量做1000条以上的批量写入,避免逐行insert,或者小批量的insert,update,delete操作,因为clickhouse底层会不断的做异步的数据合并,会影响查询新能,在这个做实时数据写入的时候尽量要避开
clickhouse块是因为采用了并行处理机制,即使一个查询也会用去服务器一半的cpu去执行,所以clickhouse 不能支持高并发的使用场景,默认但查询使用cpu和数作为服务器核数的一般,安装时会自动识别服务器的和核数,可以通过配置文件修改该参数

基本概念

列式存储

相⽐于⾏式存储，列式存储在分析场景下有着许多优良的特性。

分析场景中往往需要读⼤量⾏但是少数⼏个列

列存模式下，只需要读取参与计算的列即可，极⼤的减低了IO cost，加速了查询。

列存往往有着⾼达⼗倍甚⾄更⾼的压缩⽐，节省了⼤量的存储空间，降低了存储成本。

更⾼的压缩⽐意味着更⼩的data size，从磁盘中读取相应数据耗时更短。

可以针对不同列类型，选择最合适的压缩算法。

⾼压缩⽐，意味着同等⼤⼩的内存能够存放更多数据，系统cache效果更好。

向量化(算法)

ClickHouse不仅将数据按列存储，⽽且按列进⾏计算

对每⼀⾏数据都要调⽤相应的函数，函数调⽤开销占⽐⾼；

存储层按列存储数据，在内存中也按列组织，但是计算层按⾏处理，⽆法充分利⽤CPU cache的预读能⼒，造成CPU Cache miss严重；

按⾏处理，⽆法利⽤⾼效的SIMD指令；

ClickHouse实现了向量执⾏引擎（Vectorized execution engine），对内存中的列式数据，⼀个batch调⽤⼀次SIMD指令（⽽⾮每⼀⾏调⽤⼀次），不仅减少了函数调⽤次数、降低了cache miss，⽽且可以充分发挥SIMD指令的并⾏能⼒，⼤幅缩短了计算耗时。向量执⾏引擎，通常能够带来数倍的性能提升。

SIMD是采⽤⼀个指令流处理多个数据流。

Intel处理器实现的MMXTM、SSE（Streaming SIMD Extensions）、SSE2及SSE3扩展指令集，都能在单个时钟周期内处理多个数据单元。也就是说我们现在⽤的单核计算机基本上都属于SIMD机器。

表

表名和表结构

分⽚

ClickHouse的集群由分⽚ ( Shard ) 组成，⽽每个分⽚⼜通过副本 ( Replica ) 组成。

ClickHouse的1个节点只能拥有1个分⽚，也就是说如果要实现1分⽚、1副本，则⾄少需要部署2个服务节点

分⽚只是⼀个逻辑概念(类似于Hbase中的region的概念,表的范围数据)，其物理承载还是由副本承担的。

分区

ClickHouse⽀持PARTITION BY⼦句，在建表时可以指定按照任意合法表达式进⾏数据分区操作，⽐如通过toYYYYMM()将数据按⽉进⾏分区、toMonday()将数据按照周⼏进⾏分区、对Enum类型的列直接每种取值作为⼀个分区等。

类似于hive中的分区表

副本

数据存储副本,在集群模式下实现⾼可⽤

引擎

就是表的类型,不同的表有不同的特点

服务启动

systemctl status/start/stop clickhouse-server

客户端连接

clickhouse-client [--host=leetom]

⼊⻔操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
CREATE DATABASE IF NOT EXISTS chtest; --使⽤默认库引擎

默认情况下，ClickHouse使⽤的是原⽣的数据库引擎Ordinary(在此数据库下可以使⽤任意类型的表引擎，在绝⼤多数情况下都只需使⽤默认的数据库引擎)。当然也可以使⽤Lazy引擎和MySQL引擎，⽐如使⽤MySQL引擎，可以直接在ClickHouse中操作MySQL对应数据库中的表。假设MySQL中存在⼀个名为clickhouse的数据库，可以使⽤下⾯的⽅式连接MySQL数据库。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

CREATE DATABASE mysql_db ENGINE = MySQL('192.168.10.88:3306','leejerry', 'root', '123456');

mysql数据库引擎的库下是不⽀持创建表。

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]( name1 [type1][DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1], name2 [type2][DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2], ...) ENGINE = engine

create table test1(id Int32,name String) engine=Memory; --内存引擎表
insert into test1(id,name) values(110,'goudaner'); -- 字符串不能使用双引号
select * from test1

创建了⼀张内存表，即使⽤Memory引擎。数据只会被保存在内存中，在服务重启时数据会丢失。⼀般应⽤于中间表或者临时表。

分布式DDL操作

默认情况下，CREATE、DROP、ALTER、RENAME操作仅仅在当前执⾏该命令的server上⽣效

在集群环境下，可以使⽤ON CLUSTER语句，这样就可以在整个集群发挥作⽤。

create table if not exists user_cluster on cluster news_ck_cluster(
id Int32,
name String) engine=Distributed(news_ck_cluster,default user_local,id)

Distributed表引擎的定义形式如下所示

Distributed(cluster_name, database_name, table_name[, sharding_key])

cluster_name：集群名称，与集群配置中的⾃定义名称相对应。

database_name：数据库名称

table_name：表名称,映射到每台服务器中的表名称。

sharding_key：可选的，⽤于分⽚的key值，在数据写⼊的过程中，分布式表会依据分⽚key的规则，将数据分布到各个节点的本地表。

--创建本地表
create table if not exists user_local \
( \
    id Int32, \
    name String \
)engine = MergeTree() \
order by id \
partition by id \
primary key id;

-- 插入数据
insert into user_local values(1,'tom'),(2,'jack');

1、创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。

2、语句中使⽤了ON CLUSTER分布式DDL，这意味着在集群的每个分⽚节点上，都会创建⼀张Distributed表，这样便可以从其中任意⼀端发起对所有分⽚的读、写请求。

3、分布式表映射到每台服务器⼀张本地表。

uuid

-- 创建表
create table t_uuid(id UUID,name String) engine=TinyLog
-- 插入数据
insert into t_uuid select generateUUIDv4(),'张三'
-- 查询数据
select * from t_uuid;
-- 测试函数
select min(id),name from t_uuid group by id ,name;

UUID数据类型仅⽀持以下功能字符串数据类型也⽀持（例如, min, max，和计数).

算术运算不⽀持UUID数据类型（例如, abs）或聚合函数，例如 sum 和 avg.

Datetime64

-- 创建表
create table dt4 (`timestamp` DateTime64(3,'Asia/Shanghai'),`event_id` UInt8) engine=TinyLog
-- 插入时间
insert into dt4 values(1604321946067,1),('2020-11-02 20:58:54',2);
-- 查询
select * from dt4;
-- 获取一个时区 datatime64-类型值
select toDateTime64(now(),3,'Asia/Shanghai') as column ,toTypeName(column)as x
-- 时区转换
select toDateTime64(timestamp,3,'Europe/Moscow') as mos_time from dt4;
-- 过滤出 datatime64值
select * from dt4 where timestamp = toDateTime64('2020-11-02 20:58:54', 3,'Asia/Shanghai')

Decimal

有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍⼊）

P - 精度。有效范围：[1:38]，决定可以有多少个⼗进制数字（包括分数）。

S - 规模。有效范围：[0：P]，决定数字的⼩数部分中包含的⼩数位数。

select toDecimal32(200,4)as x,x/3;

Float32,Float64

将固定精度的数字转换为整数值，例如货币数量或⻚⾯加载时间⽤毫秒为单位表示 ;

Float32 - float

Float64 - double

对浮点数进行计算可能引起四舍五入的误差
select 1-0.9

Tuple

元组，其中每个元素都有单独的类型。

不能在表中存储元组（除了内存表）。它们可以⽤于临时列分组。

-- 创建元组
select tuple(1,'a') as x,toTypeName(x)
-- 自动数据类型检测
select tuple(1,NULL)as x,toTypeName(x)

UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64

整型范围

Int8-[-128:127]

Int16-[-32768:32767]

Int32-[-2147483648:2147483647]

Int64-[-9223372036854775808:9223372036854775807]

⽆符号整型范围

UInt8-[0:255]

UInt16-[0:65535]

UInt32-[0:4294967295]

UInt64-[0:18446744073709551615]

可为空（类型名称)

允许⽤特殊标记 (NULL) 表示«缺失值»，可以与 TypeName 的正常值存放⼀起。例如， Nullable(Int8) 类型的列可以存储 Int8 类型值，⽽没有值的⾏将存储 NULL 。

-- 创建表
create table t_null4(x Int8,y Nullable(Int8))engine=TinyLog
-- 插入数据
insert into t_null4 values(1,NULL),(2,3)
-- 查询
select x+y from t_null4

字符串

字符串可以任意⻓度的。它可以包含任意的字节集，包含空字节

布尔值

没有单独的类型来存储布尔值。可以使⽤ UInt8 类型，取值限制为 0 或 1。

数组

由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。但不推荐使⽤多维数组，ClickHouse 对多维数组的⽀持有限。

-- 创建数组

array(T)或者[]
select array(1,2)as x ,toTypeName(x)
--自动类型检测
select array(1,2,NULL)as x ,toTypeName(x)
-- 如果数据值不同会出错,
select array(1,'a')

select [1,'a']

表引擎

表引擎(table engine)。类似mysql中的InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引⽅式、锁定⽔平等功能，也可以称之为表类型。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的表类型。⽐如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。

表引擎作⽤

决定表存储在哪里以及以何种方式存储

支持哪些查询以及如何支持

并发数据访问

索引的使用

是否可以执行多线程请求

数据复制参数

表引擎分类

引擎分类	引擎名称
mergetree	MergeTree 、ReplacingMergeTree 、SummingMergeTree 、 AggregatingMergeTree CollapsingMergeTree 、 VersionedCollapsingMergeTree 、GraphiteMergeTree
Log系列	TinyLog 、StripeLog 、Log
Integrationengines	Kafka 、MySQL、ODBC 、JDBC、HDFS
Special Engines	Distributed 、MaterializedView、 Dictionary 、Merge 、File、Null 、Set 、Join 、 URL View、Memory 、 Buffer

log系列表引擎

应⽤场景

Log系列表引擎功能相对简单，主要⽤于快速写⼊⼩表(1百万⾏左右的表)，然后全部读出的场景。即⼀次写⼊多次查询。

Log系列表引擎的特点

共性特点

数据存储在磁盘上

当写数据时，将数据追加到⽂件的末尾

不⽀持并发读写，当向表中写⼊数据时，针对这张表的查询会被阻塞，直⾄写⼊动作结束

不⽀持索引

不⽀持原⼦写：如果某些操作(异常的服务器关闭)中断了写操作，则可能会获得带有损坏数据的表

不⽀持ALTER操作(这些操作会修改表设置或数据，⽐如delete、update等等)

区别

TinyLog

TinyLog是Log系列引擎中功能简单、性能较低的引擎。它的存储结构由数据⽂件和元数据两部分组成。其中，数据⽂件是按列独⽴存储的，也就是说每⼀个列字段都对应⼀个⽂件。除此之外，TinyLog不⽀持并发数据读取。

StripLog⽀持并发读取数据⽂件，当读取数据时，ClickHouse会使⽤多线程进⾏读取，每个线程处理⼀个单独的数据块。另外，StripLog将所有列数据存储在同⼀个⽂件中，减少了⽂件的使⽤数量。

Log⽀持并发读取数据⽂件，当读取数据时，ClickHouse会使⽤多线程进⾏读取，每个线程处理⼀个单独的数据块。Log引擎会将每个列数据单独存储在⼀个独⽴⽂件中。

TinyLog表引擎使⽤

该引擎适⽤于⼀次写⼊，多次读取的场景。对于处理⼩批数据的中间表可以使⽤该引擎。值得注意的是，使⽤⼤量的⼩表存储数据，性能会很低。

StripLog表引擎

相⽐TinyLog⽽⾔，StripeLog拥有更⾼的查询性能（拥有.mrk标记⽂件，⽀持并⾏查询），同时其使⽤了更少的⽂件描述符（所有数据使⽤同⼀个⽂件保存）。

1、 StripeLog 引擎将所有数据都存储在了⼀个⽂件中，对于每次的INSERT操作，ClickHouse会将数据块追加到表⽂件的末尾

2、StripeLog引擎同样不⽀持 ALTER UPDATE 和 ALTER DELETE 操作

Log表引擎

Log引擎表适⽤于临时数据，⼀次性写⼊、测试场景。Log引擎结合了TinyLog表引擎和StripeLog表引擎的⻓处，是Log系列引擎中性能最⾼的表引擎。

Log表引擎会将每⼀列都存在⼀个⽂件中，对于每⼀次的INSERT操作，都会对应⼀个数据块

库表引擎操作

MergeTree表引擎

MergeTree在写⼊⼀批数据时，数据总会以数据⽚段的形式写⼊磁盘，且数据⽚段不可修改。为了避免⽚段过多ClickHouse会通过后台线程，定期合并这些数据⽚段，属于相同分区的数据⽚段会被合成⼀个新的⽚段。这种数据⽚段往复合并的特点，也正是合并树名称的由来。

存储的数据按照主键排序：允许创建稀疏索引，从⽽加快数据查询速度

⽀持分区，可以通过PARTITION BY语句指定分区字段。

⽀持数据副本

⽀持数据采样

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
 ...
 INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
 INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ENGINE：ENGINE = MergeTree()，MergeTree引擎没有参数

ORDER BY：排序字段。⽐如ORDER BY (Col1, Col2)，值得注意的是，如果没有指定主键，默认情况下排序字段即为主键。如果不需要排序，则可以使⽤ORDER BY tuple()语法，这样的话，创建的表也就不包含主键。这种情况下，ClickHouse会按照插⼊的顺序存储数据。必选。

PARTITION BY：分区字段，可选。

PRIMARY KEY：指定主键，如果排序字段与主键不⼀致，可以单独指定主键字段。否则默认主键是排序字段。可选。

SAMPLE BY：采样字段，如果指定了该字段，那么主键中也必须包含该字段。⽐如 SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。可选

TTL：数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这⼀列的数据；如果是表级别的TTL，则会删除整张表的数据。可选

SETTINGS：额外的参数配置。可选。

create table emp_mergetree4(emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment'工资')engine=MergeTree() order by emp_id partition by work_place;
-- 插入数据
insert into emp_mergetree4 values (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
insert into emp_mergetree4 values (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);
-- 查询数据
select * from emp_mergetree4;

checksums.txt：校验⽂件，使⽤⼆进制格式存储。它保存了余下各类⽂件(primary. idx、count.txt等)的 size⼤⼩及size的哈希值，⽤于快速校验⽂件的完整性和正确性。

columns.txt：列信息⽂件，使⽤明⽂格式存储

count.txt：计数⽂件，使⽤明⽂格式存储。⽤于记录当前数据分区⽬录下数据的总⾏数

primary.idx：⼀级索引⽂件，使⽤⼆进制格式存储。⽤于存放稀疏索引，⼀张MergeTree表只能声明⼀次⼀级索引，即通过ORDER BY或者PRIMARY KEY指定字段。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据⽂件，从⽽有效减少数据扫描范围，加速查询速度。

列.bin：数据⽂件，使⽤压缩格式存储，默认为LZ4压缩格式，⽤于存储某⼀列的数据。由于MergeTree采⽤列式存储，所以每⼀个列字段都拥有独⽴的 .bin 数据⽂件，并以列字段名称命名。

列.mrk2：列字段标记⽂件，使⽤⼆进制格式存储。标记⽂件中保存了 .bin ⽂件中数据的偏移量信息

partition.dat与minmax_[Column].idx：如果指定了分区键，则会额外⽣成partition.dat与minmax索引⽂件，它们均使⽤⼆进制格式存储。partition.dat⽤于保存当前分区下分区表达式最终⽣成的值，即分区字段值；⽽minmax索引⽤于记录当前分区下分区字段对应原始数据的最⼩和最⼤值。

多次插⼊数据，会⽣成多个分区⽂件

-- 新插入两条数据
insert into emp_mergetree4 values(5,'robin','北京',35,'财务部',50000),(6,'lilei','北京',38,'销售事部',50000);
select * from emp_mergetree4;

数据分区介绍：

1.数据的分区规则：
MergeTree数据分区规则有ID决定，⽽具体到每个数据分区所对应的ID则是由分区键的取值决定的，分区键⽀持使⽤
任何⼀个或者⼀组字段表达式声明，其业务语义可以使年⽉⽇或者组织单位等任何⼀种规则，针对取值数据类型的不同，分区ID的⽣
成逻辑⽬前
有四种规则：
1.不指定分区键：不使⽤partition by 声明任何分区表达式 则分区ID默认取名为all，所有数据写⼊all分区。
2.使⽤整型：若分区键取值属于整型（兼容Uint64包含有符号整型和⽆符合整型）且⽆法转换为⽇期类型YYYYMMDD格
式则
直接按照整型的字符形式输出，作为分区ID的取值。
3.使⽤⽇期类型：若分区键取值属于⽇期类型，或者可以转为YYYYMMDD格式的整型则按照使⽤YYYYMMDD进⾏格式化
后的字符形式
输出，并作为分区ID的取值。
4.使⽤其他类型：若分区键取值不属于整型或者⽇期类型，如String，float则通过128位的Hash算法取其Hash值作
为分区ID的取值。
数据在写⼊时，会对照分区ID落⼊相应的数据分区。
2.分区⽬录的命名规则：
对于MergeTree最核⼼的特点是其分区⽬录的合并动作，⽽分区⽬录的命名中可以解读出合并逻辑。
⼀个完整分区⽬录的命名公式如下：
PartitionID_MinBlockNum_MaxBlockNum_Level
PartitionID:分区ID
MinBlockNum和MaxBlockNum：最⼩数据块编号和最⼤数据块编号，这⾥的BlockNum是⼀个整型的⾃增⻓编号。
⼀个MergeTree表
在内部全局累加，从1开始每当新创建⼀个分区⽬录，计数器就嫁1.对⼀个新分区MinBlockNum和MaxBlockNum⼀
样，当分区⽬录
发⽣合并的时候，新产⽣的合并⽬录inBlockNum和MaxBlockNum有另外的取值规则。
Level:合并的层级，也可以理解为某个分区被合并过的次数，数值越⼤则合并的次数越多。
对于⼀个新创建的
分区⽬录初始值是0 ，此后以分区为单位若相同分区发⽣合并动作则在相应分区内计数器加1.
3.分区⽬录的合并过程：
1.MergeTree的分区⽬录是在数据写⼊过程中被创建的
2.MergeTree的分区⽬录伴随着每⼀批数据的写⼊(⼀次insert语句),mergetree都会⽣成⼀批新的分区⽬录，即便
不同批次写⼊
的数据属于相同分区，也会⽣成不同的分区⽬录。
对于同⼀分区也会存在多个分区⽬录的情况，在此之后的时刻，（写⼊10--15分钟，也可以⼿动执⾏optimize查询语
句），clickhouse会通过后台任务再将属于相同分区的多个⽬录合并成⼀个新的⽬录。
已经存在的旧⽬录并不会⽴即被删除，⽽是在之后的某个时刻通过后台被删除(默认8分钟)。
同属于⼀个分区的多个⽬录，在合并之后会形成⼀个全新的⽬录，⽬录中的索引和数据⽂件也会相应的进⾏合并。新⽬
录名称的合并规则如下：
MinBlockNum：取同⼀分区内所有⽬录中最⼩的MinBlockNum值
MaxBlockNum：取同⼀分区内所有⽬录中最⼤的MaxBlockNum值
Level：取同⼀分区内最⼤Level值并加1

可以看出，新插⼊的数据新⽣成了⼀个数据块，并没有与原来的分区数据在⼀起，我们可以执⾏optimize命令，执⾏合并操作

-- 执行和并操作
optimize table emp_mergetree4 partition '北京';
-- 再次查询
select * from emp_mergetree4;

合并操作之后，会新⽣成⼀个该分区的⽂件夹，原来的分区⽂件夹不变。

在MergeTree中主键(Order by=Primary Key)并不⽤于去重，⽽是⽤于索引，加快查询速度

-- 插入一条相同主键的数据
insert into emp_mergetree4 values(1,'sam','杭州',35,'财务部',50000);
-- 不会对主键进行去重

ReplacingMergeTree表引擎

MergeTree表引擎⽆法对相同主键的数据进⾏去重，ClickHouse提供了ReplacingMergeTree引擎，可以针对相同主键的数据进⾏去重，它能够在合并分区时删除重复的数据。值得注意的是，ReplacingMergeTree只是在⼀定程度上解决了数据重复问题，但是并不能完全保障数据不重复。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

[ver]：可选参数，列的版本，可以是UInt、Date或者DateTime类型的字段作为版本号。该参数决定了数据去重的⽅式。当没有指定[ver]参数时，保留最新的数据；如果指定了具体的值，保留最⼤的版本数据。

create table emp_replacingmergetree4(emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资') engine=ReplacingMergeTree() order by emp_id primary key emp_id partition by work_place;
-- 插入数据
insert into emp_replacingmergetree4 values (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
insert into emp_replacingmergetree4 values(3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

select * from emp_replacingmergetree4;

insert into  emp_replacingmergetree4 values(1,'tom','上海',25,'技术部',50000);
-- 查询数据,由于没有进行合并,所以存在主键重复的数据
select * from emp_replacingmergetree4;
-- 执行合并操作
optimize table emp_replacingmergetree4 final;
-- 再次查询,相同主键的数据,保留最近插入的数据,旧的数据被清除
select * from emp_replacingmergetree4;

从上⾯的示例中可以看出，ReplacingMergeTree是⽀持对数据去重的，那么是根据什么进⾏去重呢？答案是： ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，⽽不是PRIMARY KEY。

create table emp_replacingmergetree5(emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资') engine=ReplacingMergeTree() order by (emp_id,name) primary key emp_id partition by work_place;

order by (emp_id,name) -- 注意排序是两个字段
primary key emp_id -- 主键是一个字段

-- 插入数据
insert into emp_replacingmergetree5 values(1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
insert into emp_replacingmergetree5 values (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

再次向该表中插⼊相同emp_id和name的数据，并执⾏合并操作，再观察数据

-- 插入数据
insert into emp_replacingmergetree5 values (1,'tom','上海',25,'技术部',50000),(1,'sam','上海',25,'技术部',20000);
-- 执行合并操作
optimize table emp_replacingmergetree5 final;
--再次查询,可见相同的emp_id和name数据被去重,而形成的主键emp_id不会去重,
-- ReplacingMergeTree在去除重复时,是以order by排序键为基准,而不是primarykey
select * from emp_replacingmergetree5

⾄此，我们知道了ReplacingMergeTree是⽀持去重的，并且是按照ORDERBY排序键为基准进⾏去重的。

-- 插入数据
insert into emp_replacingmergetree5 values (1,'tom','北京',26,'技术部',10000);
-- 执行合并操作
optimize table emp_replacingmergetree5 final;
-- 再次查询
-- replacingmergetree是以分区为单位删除数据
-- 只有在相同的数据分区内重复的数据才可以被删除,而不同的数据分区之间的重复数据依然不能被剔除
select * from emp_replacingmergetree5;

如何判断数据重复

ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，⽽不是PRIMARY KEY。

何时删除重复数据

在执⾏分区合并时，会触发删除重复数据。optimize的合并操作是在后台执⾏的，⽆法预测具体执⾏时间点，除⾮是⼿动执⾏。

不同分区的重复数据不会被去重

ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，⽽不同数据分区之间的重复数据依然不能被剔除。

数据去重的策略是什么

如果没有设置[ver]版本号，则保留同⼀组重复数据中的最新插⼊的数据；如果设置了[ver]版本号，则保留同⼀组重复数据中ver字段取值最⼤的那⼀⾏。

optimize命令使⽤

⼀般在数据量⽐较⼤的情况，尽量不要使⽤该命令。因为在海量数据场景下，执⾏optimize要消耗⼤量时间

SummingMergeTree表引擎

该引擎继承了MergeTree引擎，当合并 SummingMergeTree 表的数据⽚段时，ClickHouse 会把所有具有相同主键的⾏合并为⼀⾏，该⾏包含了被合并的⾏中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进⾏合并成⼀条数据，类似于group by的效果。

如果⽤户只需要查询数据的汇总结果，不关⼼明细数据，并且数据的汇总条件是预先明确的，即GROUP BY的分组字段是确定的，可以使⽤该表引擎。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = SummingMergeTree([columns]) -- 指定合并汇总字段
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

create table emp_summingmergetree4(emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工姓名',depart String comment '部门',salary Decimal32(2) comment '工资') engine=SummingMergeTree(salary) order by (emp_id,name) primary key emp_id partition by work_place;
-- 插入数据
insert into emp_summingmergetree4 values (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
insert into emp_summingmergetree4 values (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

再次插⼊具有相同emp_id,name的数据时，观察结果

insert into emp_summingmergetree4 values (1,'tom','上海',25,'信息部',10000),(1,'tom','北京',26,'人事部',10000);

select * from emp_summingmergetree4;
--执行合并操作
optimize table emp_summingmergetree4 final;
select * from emp_summingmergetree4;

要保证PRIMARY KEY expr指定的主键是ORDER BY expr 指定字段的前缀

-- 允许
order by (A,B,C)
primary key A


-- 会报错
-- DB::Exception:Primary key must be a prefix of the sorting key
order by (A,B,C)
primary key B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

SummingMergeTree是根据什么对两条数据进⾏合并的

⽤ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成⼀条数据，并对指定的合并字段进⾏聚合。

仅对分区内的相同排序key的数据⾏进⾏合并

以数据分区为单位来聚合数据。当分区合并时，同⼀数据分区内聚合Key相同的数据会被合并汇总，⽽不同分区之间的数据则不会被汇总。

如果没有指定聚合字段，会怎么聚合

如果没有指定聚合字段，则会按照⾮主键的数值类型字段进⾏聚合

对于⾮汇总字段的数据，该保留哪⼀条

如果两⾏数据除了排序字段相同，其他的⾮聚合字段不相同，那么在聚合发⽣时，会保留最初的那条数据，新插⼊的数据对应的那个字段值会被舍弃

AggregatingMergeTree表引擎

该表引擎继承⾃MergeTree，可以使⽤ AggregatingMergeTree 表来做增量数据统计聚合。如果要按⼀组规则来合并减少⾏数，则使⽤ AggregatingMergeTree 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过⼆进制的格式存⼊表内。

与SummingMergeTree的区别在于：SummingMergeTree对⾮主键列进⾏sum聚合，⽽AggregatingMergeTree 则可以指定各种聚合函数。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

create table emp_aggregatingmergeTree4 (emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary AggregateFunction(sum,Decimal32(2)) comment '工资') engine=AggregatingMergeTree() order by (emp_id,name) primary key emp_id partition by work_place;

对于AggregateFunction类型的列字段，在进⾏数据的写⼊和查询时与其他的表引擎有很⼤区别，在写⼊数据时，需要调⽤-State函数；⽽在查询数据时，则需要调⽤相应的-Merge函数。对于上⾯的建表语句⽽⾔，需要使⽤sumState函数进⾏数据插⼊

注意：这⾥我们的sumState和sumMerge是AggregateFtunction的内部函数，相⽐较SummingMergeTree函数更为灵活，因为可以⾃⾏选择字段聚合。

-- 插入数据
-- 注意:需要使用insert .... select 语句进行数据插入
insert into table emp_aggregatingmergeTree4 select 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
insert into table emp_aggregatingmergeTree4 select 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
--查询数据
select emp_id,name,sumMerge(salary) from emp_aggregatingmergeTree4 group by emp_id,name;

AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使⽤

-- 创建一个MereTree引擎的明细表
-- 用于存储全量的明细数据
-- 对外提供实时查询
create table emp_mergetree_base6 (emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资') engine=MergeTree() order by (emp_id,name) partition by work_place;
--创建一张物化表
--使用AggregatingMergeTree表引擎
create MATERIALIZED view view_emp_agg6 engine = AggregatingMergeTree() partition by emp_id order by (emp_id,name) as select emp_id,name,sumState(salary) as salary from emp_mergetree_base6 group by emp_id,name;

--向基础的明细表emp_mergetree_base插入数据
insert into emp_mergetree_base6 values (1,'tom','上海',25,'技术部',20000),(1,'tom','上海',26,'人事部',10000);

-- 查询物化视图
select emp_id,name,sumMerge(salary) from view_emp_agg6 group by emp_id,name;

CollapsingMergeTree表引擎

CollapsingMergeTree就是⼀种通过以增代删的思路，⽀持⾏级数据修改和删除的表引擎。它通过定义⼀个sign标记位字段，记录数据⾏的状态。如果sign标记为1，则表示这是⼀⾏有效的数据；如果sign标记为-1，则表示这⾏数据需要被删除。当CollapsingMergeTree分区合并时，同⼀数据分区内，sign标记为1和-1的⼀组数据会被抵消删除。

每次需要新增数据时，写⼊⼀⾏sign标记为1的数据；需要删除数据时，则写⼊⼀⾏sign标记为-1的数据

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

create table emp_collapsingmergetree4(emp_id UInt16 comment '员工姓名',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资',sign Int8) engine=CollapsingMergeTree(sign) order by (emp_id,name) partition by work_place;

使用方式

CollapsingMergeTree同样是以ORDER BY排序键作为判断数据唯⼀性的依据

-- 插入新增数据,sign表示正常数据
insert into emp_collapsingmergetree4 values (1,'tom','上海',25,'技术部',20000,1);
--更新以上数据
--首先插入一条与原来相同的数据(order by 字段一致)并将sign置位-1
insert into emp_collapsingmergetree4 values (1,'tom','上海',25,'技术部',20000,-1);
-- 再插入更新之后的数据
insert into emp_collapsingmergetree4 values (1,'tom','上海',25,'技术部',30000,1)
-- 查看结果
select * from emp_collapsingmergetree4;
-- 执行分区合并操作
optimize table emp_collapsingmergetree4;
-- 再次查询
select * from emp_collapsingmergetree4;

分区合并

分区数据折叠不是实时的，需要后台进⾏Compaction操作，⽤户也可以使⽤⼿动合并命令，但是效率会很低，⼀般不推荐在⽣产环境中使⽤。当进⾏汇总数据操作时，可以通过改变查询⽅式，来过滤掉被删除的数据

select emp_id,name,sum(salary * sign) from emp_collapsingmergetree4 group by emp_id,name having sum(sign)>0;

只有相同分区内的数据才有可能被折叠。其实，当我们修改或删除数据时，这些被修改的数据通常是在⼀个分区内的，所以不会产⽣影响。

数据写⼊顺序

CollapsingMergeTree对于写⼊数据的顺序有着严格要求，否则导致⽆法正常折叠。

-- 建表
create table emp_collapsingmergetree_order (emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资',sign Int8)engine=CollapsingMergeTree(sign) order by (emp_id,name) partition by work_place;
--先插入需要被删除的数据,即sign=-1的数据
insert into emp_collapsingmergetree_order values (1,'tom','上海',25,'技术部',20000,-1);
-- 再插入sign=1的数据
insert into emp_collapsingmergetree_order values (1,'tom','上海',25,'技术部',20000,1);
-- 查询表
select * from emp_collapsingmergetree_order;
--执行合并操作
optimize table emp_collapsingmergetree_order;
--再次查询表
--旧数据依然存在
select * from emp_collapsingmergetree_order;

如果数据的写⼊程序是单线程执⾏的，则能够较好地控制写⼊顺序；如果需要处理的数据量很⼤，数据的写⼊程序通常是多线程执⾏的，那么此时就不能保障数据的写⼊顺序了。在这种情况下，CollapsingMergeTree的⼯作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。

VersionedCollapsingMergeTree表引擎

上⾯提到CollapsingMergeTree表引擎对于数据写⼊乱序的情况下，不能够实现数据折叠的效果。 VersionedCollapsingMergeTree表引擎的作⽤与CollapsingMergeTree完全相同，它们的不同之处在于， VersionedCollapsingMergeTree对数据的写⼊顺序没有要求，在同⼀个分区内，任意顺序的数据都能够完成折叠操作。 VersionedCollapsingMergeTree使⽤version列来实现乱序情况下的数据折叠。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

可以看出：该引擎除了需要指定⼀个sign标识之外，还需要指定⼀个UInt8类型的version版本号。

create table emp_versioned4 (emp_id UInt16 comment '员工id',name String comment '员工姓名',work_place String comment '工作地点',age UInt8 comment '员工年龄',depart String comment '部门',salary Decimal32(2) comment '工资',sign Int8,version Int8)engine=VersionedCollapsingMergeTree(sign,version) order  by (emp_id,name) partition by work_place;
-- 先插入需要被删除的数据,
insert into emp_versioned4 values (1,'tom','上海',25,'技术部',20000,-1,1);
-- 再插入sign=1的数据
insert into emp_versioned4 values (1,'tom','上海',25,'技术部',20000,1,1);
-- 再插入一个新版本的数据
insert into emp_versioned4 values (1,'tom','上海',25,'技术部',30000,1,2);
-- 先不执行合并,先看表
select * from emp_versioned4;
-- 获取正确的结果
select emp_id,name,sum(salary*sign) from emp_versioned4 group by emp_id,name having sum(sign) > 0;
-- 手动合并
optimize table emp_versioned4;
-- 再次查询
select * from emp_versioned4;

可⻅上⾯虽然在插⼊数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到这种效果，是因为在定义 version字段之后，VersionedCollapsingMergeTree会⾃动将version作为排序条件并增加到ORDER BY的末端，就上述的例⼦⽽⾔，最终的排序字段为ORDER BY emp_id,name，version desc。

外部集成表引擎

clickhouse提供外部引擎集成的方法

odbc:通过指定odbc连接读取数据源

jdbc:通过指定jdbc连接读取数据源

mysql:将mysql作为数据源直接查询数据

hdfs:直接读取hdfs上的特定格式的数据文件

kafk:将kafka数据导入Clickhouse

RabbitMQ:与kafka类似

hdfs

使用方式

engine=HDFS(URI,format)

URI:HDFS文件路径

format:文件格式如csv,json,TSV等

-- 建表
CREATE TABLE hdfs_engine_table5(emp_id UInt16 COMMENT '员⼯id',name String COMMENT '员⼯
姓名',work_place String COMMENT '⼯作地点',age UInt8 COMMENT '员⼯年龄',depart String
COMMENT '部⻔',salary Decimal32(2) COMMENT '⼯资')
ENGINE=HDFS('hdfs://leetom:9000/ch/hdfs_engine_table5', 'CSV');
-- 写⼊数据
INSERT INTO hdfs_engine_table5 VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上
海',26,'⼈事部',10000);
-- 查询数据
select * from hdfs_engine_table;
-- 建表
CREATE TABLE hdfs_engine_table5(emp_id UInt16 COMMENT '员⼯id',name String COMMENT '员⼯
姓名',work_place String COMMENT '⼯作地点',age UInt8 COMMENT '员⼯年龄',depart String
COMMENT '部⻔',salary Decimal32(2) COMMENT '⼯资')
ENGINE=HDFS('hdfs://node1:9000/ch/hdfs_engine_table5', 'CSV');
-- 写⼊数据
INSERT INTO hdfs_engine_table5 VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上
海',26,'⼈事部',10000);
-- 查询数据
select * from hdfs_engine_table;

如果报错

要在hdfs-site.xml中添加如下配置
<property>
<name>
dfs.client.block.write.replace-datanode-on-failure.enable
</name>
<value>true</value>
</property>
<property>
<name>
dfs.client.block.write.replace-datanode-on-failure.policy
</name>
<value>never</value>
</property>

clickhouse只是映射,并不能直接删除hdfs上的数据,

msyql

只是映射

对于MySQL表引擎，不⽀持UPDATE和DELETE操作

-- 执⾏更新
ALTER TABLE mysql_engine_table UPDATE name = 'hanmeimei' WHERE id = 1;
-- 执⾏删除
ALTER TABLE mysql_engine_table DELETE WHERE id = 1;
-- 报错
DB::Exception: Mutations are not supported by storage MySQL.

kafka

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
 name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 ...
) ENGINE = Kafka()
SETTINGS
 kafka_broker_list = 'host:port',
 kafka_topic_list = 'topic1,topic2,...',
 kafka_group_name = 'group_name',
 kafka_format = 'data_format'[,]
 [kafka_row_delimiter = 'delimiter_symbol',]
 [kafka_schema = '',]
 [kafka_num_consumers = N,]
 [kafka_max_block_size = 0,]
 [kafka_skip_broken_messages = N,]
 [kafka_commit_every_batch = 0,]
 [kafka_thread_per_consumer = 0]

kafka_broker_list ：逗号分隔的brokers地址 (localhost:9092).

kafka_topic_list ：Kafka 主题列表，多个主题⽤逗号分隔.

kafka_group_name ：消费者组.

kafka_format – Message format. ⽐如 JSONEachRow 、JSON、CSV等等

在kafka中创建ck_topic主题，并向该主题写⼊数据

在kafka中创建ck_topic主题，并向该主题写⼊数据

当我们⼀旦查询完毕之后，ClickHouse会删除表内的数据，其实Kafka表引擎只是⼀个数据管道，我们可以通过物化视图的⽅式访问Kafka中的数据。

⾸先创建⼀张Kafka表引擎的表，⽤于从Kafka中读取数据

然后再创建⼀张普通表引擎的表，⽐如MergeTree，⾯向终端⽤户使⽤

最后创建物化视图，⽤于将Kafka引擎表实时同步到终端⽤户所使⽤的表中

-- 创建Kafka引擎表
CREATE TABLE kafka_table_consumer (id UInt64,name String) ENGINE = Kafka() SETTINGS
kafka_broker_list = 'leetom:9092',kafka_topic_list = 'test',kafka_group_name =
'group2',kafka_format = 'JSONEachRow';
-- 创建⼀张终端⽤户使⽤的表
CREATE TABLE kafka_table_mergetree (id UInt64 ,name String)ENGINE=MergeTree() ORDER BY
id;
 
-- 创建物化视图，同步数据
CREATE MATERIALIZED VIEW consumer1 TO kafka_table_mergetree AS SELECT id,name FROM
kafka_table;
-- 查询，多次查询，已经被查询的数据依然会被输出
select * from kafka_table_mergetree;

memory表引擎

把数据保存在内存中,不会压缩不会格式转换,clickhouse重启数据全部丢失,一般在测试的时候用

CREATE TABLE table_memory(id UInt64,name String) ENGINE = Memory();

distributed表引擎

分布式表,本身不存储任何数据,数据存在某一个分片上,能自动路由数据至集群中的各个节点,所以distributed须有和其他的数据表引擎一起协同工作,数据表和分片是一对多关系

Distributed(cluster_name, database_name, table_name[, sharding_key])

各个参数的含义分别如下：

cluster_name：集群名称，与集群配置中的⾃定义名称相对应。

database_name：数据库名称

table_name：表名称

sharding_key：可选的，⽤于分⽚的key值，在数据写⼊的过程中，分布式表会依据分⽚key的规则，将数据分布到各个节点的本地表。

创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。同样值得注意的是，在上⾯的语句中使⽤了ON CLUSTER分布式DDL，这意味着在集群的每个分⽚节点上，都会创建⼀张Distributed表，这样便可以从其中任意⼀端发起对所有分⽚的读、写请求。

函数操作

clickhouse主要提供两种函数,普通函数和聚合函数

普通函数没有状态,不会一行一行的执行,采用向量化方式直接用于一整列

聚合函数,支持序列化和反序列化,在分布之节点间进行传输,以实现增量计算

普通类型

类型转换

toXX()

toXX()ORxx()

cast(exp,'type')

日期函数

SELECT
 toDateTime('2016-06-15 23:00:00') AS time,
 toDate(time) AS date_local,
 toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
 toString(time, 'US/Samoa') AS time_samoa

条件函数

create table if not exists tb_if(
 uid Int16,
 name String ,
 gender String
)engine = TinyLog ;
insert into tb_if values(1,'zss1','M') ;
insert into tb_if values(2,'zss2','M') ;
insert into tb_if values(3,'zss3','F') ;
insert into tb_if values(4,'zss4','O') ;
insert into tb_if values(5,'zss5','F') ;

多条件判断语法

SELECT
 *,
 multiIf(gender = 'M', '男', gender = 'F', '⼥', '保密') AS sex
FROM tb_if;

数组函数

SELECT range(2, 10, 2) -- 定义⼀个范围数组

select array(2 , 10 , 2) ; -- 定义数组

select arrayconcat(['a','b'],['hello','jim'],['e','f']); -- 合并数组

添加元素

select arrayPushBack(['a'],'b') as res
select arrayPushFront(['b'],'a') as res

数组排序

arrayReverseSort([func,] arr, …)

数组去重

select arrayDistinct([1,2,2,3,1])

数组拉链操作

select arrayZip(['a','b','c'],[5,2,1])

字符串切割

select splitByChar(',','1,2,3,abcde')
select splitByString(',','1,2,3,4,5,abcde')
select splitByString('','abcde')

随机数据和随机字符串

select rand(),rand(1),rand(number),randConstant(),randConstant(1),randCOnstant(number) from numbers(3)

json

select visitParamExtractString('{"name":"zss","age":21}' , 'name') ;

高阶函数

.[arrayMap]
.[arrayFilter]
.[arrayFill]
.[arrayReverseFill]
.[arrayReverseSplit]
.[arrayFirst]
.[arrayFirstIndex]

⾏转列

create table tb_array_join(id Int8 , msg String) engine=TinyLog ;
insert into tb_array_join values (1, 'a,b,c') ;
insert into tb_array_join values (2, 'h,j,k') ;
select id, arrayJoin(splitByChar(',',msg)) from tb_array_join ;
SELECT
 id,
 arrayJoin(splitByChar(',', msg))
FROM tb_array_join

列转⾏

select groupArray(name) from tb_user ; -- 将所有的列聚合成⼀个数组!
│
['Hang','Jie','Li','Ying','Jie','Li','Ying','ADA','ADA','ADA','Jie','Li','Ying','Jie','
Li','hhhh'] │
select arrayStringConcat(groupArray(name),'|') from tb_user ; -- 实现需求

聚合函数

计算[加权算术平均值]

SELECT avgWeighted(x, y)
FROM values('x Int8,y Int8', (4, 1), (4, 1), (8, 3))

链路函数

这组函数可以做动态规划,漏⽃模型... 1. sequenceMatch(pattern)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条件n都成⽴的链路.若满⾜返回 1,否则为 0 . 2. sequenceCount(pattern)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条件n都成⽴的链路若满⾜返回出现的次数 3. windowFunnel(window)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条件 n都成⽴的链路. 并且设置链路的窗⼝时间. ⼀般⽤于计算漏⽃模型

这组函数可以做动态规划,漏⽃模型...
1. sequenceMatch(pattern)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条
件n都成⽴的链路.若满⾜ 返回 1,否则为 0 .
2. sequenceCount(pattern)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条
件n都成⽴的链路 若满⾜ 返回出现的次数
3. windowFunnel(window)(time, cond1, cond2, ...) 判断是否存在满⾜根据时间排序且依次条件1,条件2,...条件
n都成⽴的链路. 并且设置链路的窗⼝时间. ⼀般⽤于计算漏⽃模型

统计每个⽤户在40秒内最⼤连续⼦序列 ; 返回⼀个Int类型的数字 ,说明执⾏到符合规则的第⼏步

select
uid,windowFunnel(40)(toDateTime(eventTime),eventid = 'A' ,eventid = 'B',eventid = 'C')
as funnel
from tb_test
group by uid ;

架构原理

ClickHouse为何如此之快

着眼硬件,先想后做

在动手计算前已经算出粗略的性能,将硬件功效最大化,

算法在前,抽象在后

对于常量采用Volnitsky算法,对于非常量使用cpu的向量执行SIMD,暴力优化,政策匹配使用了re2和hyperscan算法,新能是算法选择的首要考量指标

用于尝鲜,不行就换

新算法效果不错,保留使用,性能不行,将其抛弃

特定场景,特殊优化

对于结构清晰的场景,通过diamante循环展开,减少循环次数,然后是向量化执行

持续测试,持续改进

保持内容新鲜,时刻更新

架构设计

Column与Field

Column和Field是ClickHouse数据最基础的映射单元

DataType

数据的序列化和反序列化⼯作由DataType负责

BloCK与BloCK流

ClickHouse内部的数据操作是⾯向BloCK对象进⾏的，并且采⽤了流的形式

Table

在数据表的底层设计中并没有所谓的Table对象，它直接使⽤IStorage接⼝指代数据表。

Parser与Interpreter

Parser和Interpreter是⾮常重要的两组接⼝：Parser分析器负责创建AST对象；⽽Interpreter解释器则负责解释 AST，并进⼀步创建查询的执⾏管道。

分⽚与副本

ClickHouse的集群由分⽚ ( Shard ) 组成，⽽每个分⽚⼜通过副本 ( Replica ) 组成。

ClickHouse的1个节点只能拥有1个分⽚，也就是说如果要实现1分⽚、1副本，则⾄少需要部署2个服务节点。分⽚只是⼀个逻辑概念，其物理承载还是由副本承担的。

数据存储特点

ClickHouse⽀持在建表时，指定将数据按照某些列进⾏sort by。

主键索引

ClickHouse⽀持主键索引，它将每列数据按照index granularity（默认8192⾏）进⾏划分，每个index granularity 的开头第⼀⾏被称为⼀个mark⾏

稀疏索引

ClickHouse⽀持对任意列创建任意数量的稀疏索引。

数据分⽚

ClickHouse⽀持单机模式，也⽀持分布式集群模式。在分布式模式下，ClickHouse会将数据分为多个分⽚，并且分布到不同节点上。不同的分⽚策略在应对不同的SQL Pattern时，各有优势。ClickHouse提供了丰富的sharding策略，让业务可以根据实际需求选⽤。 1） random随机分⽚：写⼊数据会被随机分发到分布式集群中的某个节点上。 2） constant固定分⽚：写⼊数据会被分发到固定⼀个节点上。 3）column value分⽚：按照某⼀列的值进⾏hash分⽚。 4）⾃定义表达式分⽚：指定任意合法表达式，根据表达式被计算后的值进⾏hash分⽚

数据Partitioning

ClickHouse⽀持PARTITION BY⼦句，在建表时可以指定按照任意合法表达式进⾏数据分区操作，⽐如通过 toYYYYMM()将数据按⽉进⾏分区、toMonday()将数据按照周⼏进⾏分区、对Enum类型的列直接每种取值作为⼀个分区等。数据Partition在ClickHouse中主要有两⽅⾯应⽤：在partition key上进⾏分区裁剪，只查询必要的数据。灵活的partition expression设置，使得可以根据SQL Pattern进⾏分区设置，最⼤化的贴合业务特点。对partition进⾏TTL管理，淘汰过期的分区数据。

数据TTL

在分析场景中，数据的价值随着时间流逝⽽不断降低，多数业务出于成本考虑只会保留最近⼏个⽉的数据， ClickHouse通过TTL提供了数据⽣命周期管理的能⼒。 ClickHouse⽀持⼏种不同粒度的TTL： 1）列级别TTL：当⼀列中的部分数据过期后，会被替换成默认值；当全列数据都过期后，会删除该列。 2）⾏级别TTL：当某⼀⾏过期后，会直接删除该⾏。 3）分区级别TTL：当分区过期后，会直接删除该分区。

⾼吞吐写⼊能⼒

ClickHouse采⽤类LSM Tree的结构，数据写⼊后定期在后台Compaction。通过类LSM tree的结构，ClickHouse 在数据导⼊时全部是顺序append写，写⼊后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利⽤了磁盘的吞吐能⼒，即便在HDD上也有着优异的写⼊性能。官⽅公开benchmark测试显示能够达到50MB-200MB/s的写⼊吞吐能⼒，按照每⾏100Byte估算，⼤约相当于 50W-200W条/s的写⼊速度。

有限⽀持delete、update

在分析场景中，删除、更新操作并不是核⼼需求。ClickHouse没有直接⽀持delete、update操作，⽽是变相⽀持了mutation操作，语法为alter table delete where filter_expr,alter table update col=val where filter_expr。⽬前主要限制为删除、更新操作为异步操作，需要后台compation之后才能⽣效。

主备同步

ClickHouse通过主备复制提供了⾼可⽤能⼒，主备架构下⽀持⽆缝升级等运维操作。⽽且相⽐于其他系统它的实现有着⾃⼰的特⾊： 1）默认配置下，任何副本都处于active模式，可以对外提供查询服务； 2）可以任意配置副本个数，副本数量可以从0个到任意多个； 3）不同shard可以配置不同的副本个数，⽤于解决单个shard的查询热点问题；

ClickHouse计算层

ClickHouse在计算层做了⾮常细致的⼯作，竭尽所能榨⼲硬件能⼒，提升查询速度。它实现了单机多核并⾏、分布式计算、向量化执⾏与SIMD指令、代码⽣成等多种重要技术

多核并⾏

ClickHouse将数据划分为多个partition，每个partition再进⼀步划分为多个index granularity，然后通过多个CPU 核⼼分别处理其中的⼀部分来实现并⾏数据处理。在这种设计下，单条Query就能利⽤整机所有CPU。极致的并⾏处理能⼒，极⼤的降低了查询延时。

分布式计算

除了优秀的单机并⾏处理能⼒，ClickHouse还提供了可线性拓展的分布式计算能⼒。ClickHouse会⾃动将查询拆解为多个task下发到集群中，然后进⾏多机并⾏处理，最后把结果汇聚到⼀起。在存在多副本的情况下，ClickHouse提供了多种query下发策略：随机下发：在多个replica中随机选择⼀个；最近hostname原则：选择与当前下发机器最相近的hostname节点，进⾏query下发。在特定的⽹络拓扑下，可以降低⽹络延时。⽽且能够确保query下发到固定的replica机器，充分利⽤系统cache。 in order：按照特定顺序逐个尝试下发，当前⼀个replica不可⽤时，顺延到下⼀个replica。 first or random：在In Order模式下，当第⼀个replica不可⽤时，所有workload都会积压到第⼆个Replica，导致负载不均衡。first or random解决了这个问题：当第⼀个replica不可⽤时，随机选择⼀个其他replica，从⽽保证其余replica间负载均衡。另外在跨region复制场景下，通过设置第⼀个replica为本region内的副本，可以显著降低⽹络延时。