一、官网文档地址
数据库引擎 Database Engines | ClickHouse Docs
表引擎 Table Engines | ClickHouse Docs
二、数据库引擎
1、Atomic
ClickHouse默认数据库引擎,支持non-blocking DROP TABLE、RENAME TABLE 、atomic EXCHANGE TABLES 查询。
创建数据库
CREATE DATABASE test ENGINE = Atomic;
Table UUID
Atomic作为库引擎的数据库下所有的表都会有唯一的UUID,且是自动生成的,用户也可以显示指定UUID,但是不建议。
RENAME TABLE
不更改UUID/不移动表数据/立即执行/非原子方式
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
EXCHANGE TABLES
原子方式交换表
EXCHANGE TABLES new_table AND old_table;
2、Lazy
在最后一次访问之后,只在RAM中保存expiration_time_in_seconds秒。仅用于Log表,它是为存储许多小的Log表而优化的,对于这些表,访问之间有很长的时间间隔。
创建数据库
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
3、Replicated
复制引擎基于原子引擎,它支持通过将DDL日志写入ZooKeeper并在给定数据库的所有副本上执行的元数据复制
一个ClickHouse服务端可以同时运行或更新多个该引擎的数据库,但是同一个该引擎的数据库不能有多个副本
目前该引擎在实验阶段,不建议在生产环境中使用
创建数据库
CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]
zoo_path:ZooKeeper路径,相同路径对应相同数据库
shard_name:分组名称,数据库副本按照shard_name来分组
replica_name:副本名称,副本名称在同一个分组中必须唯一
4、PostgreSQL
允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询),以在ClickHouse和PostgreSQL之间交换数据。
在SHOW TABLES和DESCRIBE TABLE查询的帮助下,从远程PostgreSQL实时访问表列表和表结构。
支持表结构修改(ALTER TABLE ... ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1,则会缓存表结构,不会检查是否被修改,但可以用DETACH和ATTACH查询进行更新。
使用总体上与mysql引擎类似
创建数据库
CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `schema`, `use_table_cache`]);
host:port — PostgreSQL服务地址
database — 远程数据库名次
user — PostgreSQL用户名称
password — PostgreSQL用户密码
schema - PostgreSQL 模式
use_table_cache — 定义数据库表结构是否已缓存或不进行。可选的。默认值: 0
数据类型对应
PostgreSQL | ClickHouse |
DATE | Date |
TIMESTAMP | DateTime |
REAL | Float32 |
DOUBLE | Float64 |
DECIMAL | Decimal |
NUMERIC | Decimal |
SMALLINT | Int16 |
INTEGER | Int32 |
BIGINT | Int64 |
SERIAL | UInt32 |
BIGSERIAL | UInt64 |
TEXT | String |
CHAR | String |
INTEGER | Nullable(Int32) |
ARRAY | Array |
5、MySQL
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中,并允许您对表进行INSERT和SELECT查询,以方便您在ClickHouse与MySQL之间进行数据交换。
MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中,因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。
但无法对其执行操作:RENAME、CREATE TABLE和ALTER。
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
host:port — MySQL服务地址
database — MySQL数据库名称
user — MySQL用户名
password — MySQL用户密码
数据类型对应
MySQL | ClickHouse |
UNSIGNED TINYINT | UInt8 |
TINYINT | Int8 |
UNSIGNED SMALLINT | UInt16 |
SMALLINT | Int16 |
UNSIGNED INT | UInt32 |
UNSIGNED MEDIUMINT | UInt32 |
INT,MEDIUMINT | Int32 |
UNSIGNED BIGINT | UInt64 |
BIGINT | Int64 |
FLOAT | Float32 |
DOUBLE | Float64 |
DATE | Date |
DATETIME,TIMESTAMP | DateTime |
BINARY | FixedString |
6、SQLite
允许连接到SQLite数据库,并支持ClickHouse和SQLite交换数据, 执行INSERT和SELECT查询。
SQLite将整个数据库(定义、表、索引和数据本身)存储为主机上的单个跨平台文件。在写入过程中,SQLite会锁定整个数据库文件,因此写入操作是顺序执行的。读操作可以是多任务的。SQLite不需要服务管理(如启动脚本)或基于GRANT和密码的访问控制。访问控制是通过授予数据库文件本身的文件系统权限来处理的。
创建数据库
CREATE DATABASE sqlite_database ENGINE = SQLite('db_path')
db_path:SQLite 数据库文件的路径
数据类型对应
SQLite | ClickHouse |
INTEGER | Int32 |
REAL | Float32 |
TEXT | String |
BLOB | String |
7、MaterializedPostgreSQL
使用PostgreSQL数据库表的初始数据转储创建ClickHouse数据库,并启动复制过程,即执行后台作业,以便在远程PostgreSQL数据库中的PostgreSQL数据库表上发生新更改时应用这些更改。
ClickHouse服务器作为PostgreSQL副本工作。它读取WAL并执行DML查询。DDL不是复制的,但可以处理(如下所述)。
目前该引擎在实验阶段,不建议在生产环境中使用
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]
8、MaterializedMySQL
创建ClickHouse数据库,包含MySQL中所有的表,以及这些表中的所有数据。
ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。
目前该引擎在实验阶段,不建议在生产环境中使用
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
[TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]
host:port
— MySQL 服务器地址.
database
— MySQL 数据库名称.
user
— MySQL 连接用户名.
password
— User 连接密码.
三、表引擎
1、MergeTree Family
MergeTree表引擎
MergeTree在写入一批数据时,数据总会以数据片段的形式写入磁盘,且数据片段不可修改。为了避免片段过多,ClickHouse会通过后台线程,定期合并这些数据片段,属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点,也正是合并树名称的由来
创建表
ENGINE:ENGINE = MergeTree(),MergeTree引擎没有参数,必选
ORDER BY:排序字段。比如ORDER BY (Col1, Col2),值得注意的是,如果没有指定主键,默认情况下 sorting key(排序字段)即为主键。如果不需要排序,则可以使用ORDER BY tuple()语法,这样的话,创建的表也就不包含主键。这种情况下,ClickHouse会按照插入的顺序存储数据。必选。
PARTITION BY:分区字段,按照某个字段进行分区,插入数据时会放在一个新的分区,等着后台线程定期合并分区,也可以手动执行(OPTIMIZE TABLE final;)命令合并,可选。
PRIMARY KEY:指定主键,如果排序字段与主键不一致,可以单独指定主键字段。否则默认主键是排序字段,默认不会以主键进行去重(可插入主键相同的多条数据)。可选。
SAMPLE BY:采样字段,如果指定了该字段,那么主键中也必须包含该字段。比如SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。可选。
TTL:数据的存活时间。在MergeTree中,可以为某个列字段或整张表设置TTL。当时间到达时,如果是列字段级别的TTL,则会删除这一列的数据;如果是表级别的TTL,则会删除整张表的数据。可选。
SETTINGS:额外的参数配置。可选。
ReplacingMergeTree表引擎
ClickHouse提供了ReplacingMergeTree引擎,可以针对相同主键的数据进行去重,它能够在合并分区时删除重复的数据。值得注意的是,ReplacingMergeTree只是在一定程度上解决了数据重复问题,但是并不能完全保障数据不重复。
创建表
[ver]:可选参数,列的版本,可以是UInt、Date或者DateTime类型的字段作为版本号。该参数决定了数据去重的方式。
当没有指定[ver]参数时,保留最新的数据;如果指定了具体的值,保留最大的版本数据
去重规则:以ORDER BY排序键为准,此字段重复则会执行删除,这是基于同一个分区,不同分区可能也会存在重复数据,这也是不能完全去重的原因
删除规则:保留[ver]参数指定字段的最大值,未指定[ver]时保留最后一次插入的数据
SummingMergeTree表引擎
该引擎继承了MergeTree引擎,当合并 SummingMergeTree 表的数据片段时,ClickHouse 会把所有具有相同主键的行合并为一行,该行包含了被合并的行中具有数值数据类型的列的汇总值,即如果存在重复的数据,会对对这些重复的数据进行合并成一条数据,类似于group by的效果。
推荐将该引擎和 MergeTree 一起使用。例如,将完整的数据存储在 MergeTree 表中,并且使用 SummingMergeTree 来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失数据。
如果用户只需要查询数据的汇总结果,不关心明细数据,并且数据的汇总条件是预先明确的,即GROUP BY的分组字段是确定的,可以使用该表引擎。
创建表
columns:指定合并汇总字段,未指定字段时,所有非主键的数值类型的字段都会聚合;其他非合并汇总字段将保留最开始插入的数据,舍弃新插入的数据
合并规则:在同一个分区内,用ORDER BY排序键作为聚合数据的条件Key,如果排序key相同,则会合并成一条数据,并对指定的合并字段进行聚合
AggregatingMergeTree表引擎
该表引擎继承自MergeTree,可以使用 AggregatingMergeTree 表来做增量数据统计聚合。如果要按一组规则来合并减少行数,则使用 AggregatingMergeTree 是合适的。
AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。与SummingMergeTree的区别在于:SummingMergeTree对非主键列进行sum聚合,而AggregatingMergeTree则可以指定各种聚合函数。
创建表
官网示例:
CollapsingMergeTree表引擎
CollapsingMergeTree就是一种通过以增代删的思路,支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段,记录数据行的状态。如果sign标记为1,则表示这是一行有效的数据;如果sign标记为-1,则表示这行数据需要被删除。当CollapsingMergeTree分区合并时,同一数据分区内,sign标记为1和-1的一组数据会被抵消删除。
每次需要新增数据时,写入一行sign标记为1的数据;需要删除数据时,则写入一行sign标记为-1的数据。
创建表
分区合并:分区数据折叠不是实时的,需要后台进行Compaction操作,用户也可以使用手动合并命令,但是效率会很低,一般不推荐在生产环境中使用。当进行汇总数据操作时,可以通过改变查询方式,来过滤掉被删除的数据,只有相同分区内的数据才有可能被折叠。其实,当我们修改或删除数据时,这些被修改的数据通常是在一个分区内的,所以不会产生影响。
数据写入顺序:值得注意的是:CollapsingMergeTree对于写入数据的顺序有着严格要求,否则导致无法正常折叠。如果数据的写入程序是单线程执行的,则能够较好地控制写入顺序;如果需要处理的数据量很大,数据的写入程序通常是多线程执行的,那么此时就不能保障数据的写入顺序了。在这种情况下,CollapsingMergeTree的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。
VersionedCollapsingMergeTree表引擎
上面提到CollapsingMergeTree表引擎对于数据写入乱序的情况下,不能够实现数据折叠的效果。VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同,它们的不同之处在于,VersionedCollapsingMergeTree对数据的写入顺序没有要求,在同一个分区内,任意顺序的数据都能够完成折叠操作
创建表
该引擎除了需要指定一个sign标识之外,还需要指定一个UInt8类型的version版本号
GraphiteMergeTree表引擎
该引擎用来对 Graphite数据进行(图数据)'瘦身'及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。
如果不需要对Graphite数据做汇总,那么可以使用任意的CH表引擎;但若需要,那就采用 GraphiteMergeTree 引擎。它能减少存储空间,同时能提高Graphite数据的查询效率。
2、Log Family
公共属性
将数据存储在磁盘上
写入时将数据附加到文件末尾
支持并发数据访问的锁
在INSERT查询期间,表被锁定,其他用于读取和写入数据的查询都在等待表解锁。如果没有数据写入查询,则可以同时执行任意数量的数据读取查询
不支持突变
不支持索引
对数据范围查询效率不高
不能以原子方式写入数据
服务器异常关闭导致写入中断时,表中的数据不完整
StripeLog表引擎
当您需要用少量数据(少于100万行)写入许多表时,请在场景中使用此引擎。
创建表
写操作:此引擎将所有列存储在一个文件中,每一个插入,都会将数据追加到文件末尾,不支持修改表结构(ALTER UPDATE and ALTER DELETE)
读操作:不加ORDER BY执行查询时,顺序是无法预料的。
Log表引擎
Log和TinyLog的不同之处在于,列文件中有一个由“标记”组成的小文件。这些标记写在每个数据块上,并包含偏移量,这些偏移量指示从哪里开始读取文件,以便跳过指定的行数。这使得在多个线程中读取表数据成为可能。对于并发数据访问,读取操作可以同时执行,而写入操作则阻止读取并相互阻止。日志引擎不支持索引。类似地,如果对表的写入失败,则表将被破坏,从中读取将返回错误。日志引擎适用于临时数据、一次写入表以及测试或演示目的。
TinyLog表引擎
此表引擎通常与一次写入方法一起使用:一次写入数据,然后根据需要多次读取数据。例如,可以将TinyLog类型表用于小批量处理的中间数据。请注意,将数据存储在大量的小表中是低效的。查询在单个流中执行。换言之,此引擎适用于相对较小的表(最多约1000000行)。如果您有很多小表,那么使用这个表引擎是有意义的,因为它比日志引擎更简单(需要打开的文件更少)。
3、Integrations
Table Engines for Integrations | ClickHouse Docs
ODBC
创建配置文件
创建表
ENGINE = ODBC('DSN=配置文件组名', '数据库名称', '表名')
JDBC
在MySQL中创建表
在ClickHouse中创建表
ENGINE = JDBC('jdbc:mysql://ip:端口/?user=用户名&password=密码','数据库名','表名')
MySQL
在MySQL中创建表
在ClickHouse中创建表
ENGINE = MySQL('localhost:3306', '库名', '表名', '用户名', '密码')
MongoDB
创建表
ENGINE = MongoDB(host:port, database, table, user, password);
HDFS
ENGINE = HDFS(URI, format)
创建表
S3
创建表
Kafka
创建表
ENGINE = Kafka('host:port','主题','组名','格式化'),这四个是必传参。
格式化详解:Formats for Input and Output Data | ClickHouse Docs
EmbeddedRocksDB
创建表
RabbitMQ
创建表
PostgreSQL
创建表
SQLite
创建表
ENGINE = SQLite('db_path', 'table')
Hive
创建表
ExternalDistributed
创建表
MaterializedPostgreSQL
创建表
NATS
创建表
DeltaLake
创建表
Hudi
创建表
4、Special
Memory表引擎
Memory表引擎直接将数据保存在内存中,数据既不会被压缩也不会被格式转换。当ClickHouse服务重启的时候,Memory表内的数据会全部丢失。一般在测试时使用。
创建表
CREATE TABLE table_memory (
id UInt64,
name String
) ENGINE = Memory();
Distributed表引擎
Distributed表引擎是分布式表的代名词,它自身不存储任何数据,数据都分散存储在某一个分片上,能够自动路由数据至集群中的各个节点,所以Distributed表引擎需要和其他数据表引擎一起协同工作。