引擎
- 数据库引擎
- index
- 表引擎
数据库引擎
数据库引擎允许您处理数据表。
默认情况下，ClickHouse使用Atomic数据库引擎。它提供了可配置的table engines和SQL dialect。
您还可以使用以下数据库引擎：
MaterializedMySQL
这是一个实验性的特性，不应该在生产中使用。
创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。
ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。
这个功能是实验性的。
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
引擎参数
host:port— MySQL服务地址database— MySQL数据库名称user— MySQL用户名password— MySQL用户密码
引擎配置
max_rows_in_buffer— 允许数据缓存到内存中的最大行数(对于单个表和无法查询的缓存数据)。当超过行数时，数据将被物化。默认值:65505。max_bytes_in_buffer— 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。当超过行数时，数据将被物化。默认值:1048576.max_rows_in_buffers— 允许数据缓存到内存中的最大行数(对于数据库和无法查询的缓存数据)。当超过行数时，数据将被物化。默认值:65505.max_bytes_in_buffers— 允许在内存中缓存数据的最大字节数(对于数据库和无法查询的缓存数据)。当超过行数时，数据将被物化。默认值:1048576.max_flush_data_time— 允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。当超过这个时间时，数据将被物化。默认值:1000.max_wait_time_when_mysql_unavailable— 当MySQL不可用时重试间隔(毫秒)。负值禁止重试。默认值:1000.allows_query_when_mysql_lost— 当mysql丢失时，允许查询物化表。默认值:0(false).
CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***')
SETTINGS
allows_query_when_mysql_lost=true,
max_wait_time_when_mysql_unavailable=10000;
MySQL服务器端配置
为了MaterializeMySQL正确的工作，有一些强制性的MySQL侧配置设置应该设置:
default_authentication_plugin = mysql_native_password，因为MaterializeMySQL只能使用此方法授权。gtid_mode = on，因为要提供正确的MaterializeMySQL复制，基于GTID的日志记录是必须的。注意，在打开这个模式On时，你还应该指定enforce_gtid_consistency = on。
虚拟列
当使用MaterializeMySQL数据库引擎时，ReplacingMergeTree表与虚拟的_sign和_version列一起使用。
支持的数据类型
| MySQL | ClickHouse |
|---|---|
| TINY | Int8 |
| SHORT | Int16 |
| INT24 | Int32 |
| LONG | UInt32 |
| LONGLONG | UInt64 |
| FLOAT | Float32 |
| DOUBLE | Float64 |
| DECIMAL, NEWDECIMAL | Decimal |
| DATE, NEWDATE | Date |
| DATETIME, TIMESTAMP | DateTime |
| DATETIME2, TIMESTAMP2 | DateTime64 |
| ENUM | Enum |
| STRING | String |
| VARCHAR, VAR_STRING | String |
| BLOB | String |
| BINARY | FixedString |
不支持其他类型。如果MySQL表包含此类类型的列，ClickHouse抛出异常"Unhandled data type"并停止复制。
Nullable已经支持
使用方式
兼容性限制
除了数据类型的限制外，与MySQL数据库相比，还存在一些限制，在实现复制之前应先解决这些限制：
-
MySQL中的每个表都应该包含PRIMARY KEY -
对于包含
ENUM字段值超出范围（在ENUM签名中指定）的行的表，复制将不起作用。
DDL查询
MySQL DDL查询转换为相应的ClickHouse DDL查询(ALTER, CREATE, DROP, RENAME)。如果ClickHouse无法解析某个DDL查询，则该查询将被忽略。
Data Replication
MaterializeMySQL不支持直接INSERT, DELETE和UPDATE查询. 但是，它们是在数据复制方面支持的：
-
MySQL的
INSERT查询转换为INSERT并携带_sign=1. -
MySQL的
DELETE查询转换为INSERT并携带_sign=-1. -
MySQL的
UPDATE查询转换为INSERT并携带_sign=-1,INSERT和_sign=1.
查询MaterializeMySQL表
SELECT查询MaterializeMySQL表有一些细节:
-
如果
_version在SELECT中没有指定，则使用FINAL修饰符。所以只有带有MAX(_version)的行才会被选中。 -
如果
_sign在SELECT中没有指定，则默认使用WHERE _sign=1。因此，删除的行不会包含在结果集中。 -
结果包括列中的列注释，因为它们存在于SQL数据库表中。
Index Conversion
MySQL的PRIMARY KEY和INDEX子句在ClickHouse表中转换为ORDER BY元组。
ClickHouse只有一个物理顺序，由ORDER BY子句决定。要创建一个新的物理顺序，使用materialized views。
表重写
表覆盖可用于自定义ClickHouse DDL查询，从而允许您对应用程序进行模式优化。这对于控制分区特别有用，分区对MaterializedMySQL的整体性能非常重要。
这些是你可以对MaterializedMySQL表重写的模式转换操作:
- 修改列类型。必须与原始类型兼容，否则复制将失败。例如，可以将
UInt32列修改为UInt64，不能将String列修改为Array(String)。 - 修改 column TTL.
- 修改 column compression codec.
- 增加 ALIAS columns.
- 增加 skipping indexes
- 增加 projections. 请注意，当使用
SELECT ... FINAL(MaterializedMySQL默认是这样做的) 时，预测优化是被禁用的，所以这里是受限的，INDEX ... TYPE hypothesis[在v21.12的博客文章中描述]](https://clickhouse.com/blog/en/2021/clickhouse-v21.12-released/)可能在这种情况下更有用。 - 修改 PARTITION BY
- 修改 ORDER BY
- 修改 PRIMARY KEY
- 增加 SAMPLE BY
- 增加 table TTL
Notes
- 带有
_sign=-1的行不会从表中物理删除。 MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。- 复制很容易被破坏。
- 禁止对数据库和表进行手工操作。
MaterializeMySQL受optimize_on_insert设置的影响。当MySQL服务器中的表发生变化时，数据会合并到MaterializeMySQL数据库中相应的表中。
使用示例
MySQL操作:
mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, b INT);
mysql> INSERT INTO db.test VALUES (1, 11), (2, 22);
mysql> DELETE FROM db.test WHERE a=1;
mysql> ALTER TABLE db.test ADD COLUMN c VARCHAR(16);
mysql> UPDATE db.test SET c='Wow!', b=222;
mysql> SELECT * FROM test;
+---+------+------+
| a | b | c |
+---+------+------+
| 2 | 222 | Wow! |
+---+------+------+
ClickHouse中的数据库，与MySQL服务器交换数据:
创建的数据库和表:
CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***');
SHOW TABLES FROM mysql;
┌─name─┐
│ test │
└──────┘
然后插入数据:
SELECT * FROM mysql.test;
┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘
删除数据后，添加列并更新:
SELECT * FROM mysql.test;
┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘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' | database], 'user', 'password') [SETTINGS ...]
Engine参数
host:port— PostgreSQL服务地址database— PostgreSQL数据库名user— PostgreSQL用户名password— 用户密码
设置
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_max_block_size = 65536,
materialized_postgresql_tables_list = 'table1,table2,table3';
SELECT * FROM database1.table1;
必备条件
-
在postgresql配置文件中将wal_level设置为
logical，将max_replication_slots设置为2。 -
每个复制表必须具有以下一个replica identity:
-
default (主键)
-
index
postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;
总是先检查主键。如果不存在，则检查索引(定义为副本标识索引)。 如果使用index作为副本标识，则表中必须只有一个这样的索引。 你可以用下面的命令来检查一个特定的表使用了什么类型:
postgres# SELECT CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;
注意
- TOAST不支持值转换。将使用数据类型的默认值。
使用示例
CREATE DATABASE postgresql_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');
SELECT * FROM postgresql_db.postgres_table;MySQL
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换
MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。
但您无法对其执行以下操作：
RENAMECREATE TABLEALTER
创建数据库
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, UNSIGNED MEDIUMINT | UInt32 |
| INT, MEDIUMINT | Int32 |
| UNSIGNED BIGINT | UInt64 |
| BIGINT | Int64 |
| FLOAT | Float32 |
| DOUBLE | Float64 |
| DATE | Date |
| DATETIME, TIMESTAMP | DateTime |
| BINARY | FixedString |
其他的MySQL数据类型将全部都转换为String.
Nullable已经支持
全局变量支持
为了更好地兼容，您可以在SQL样式中设置全局变量，如@@identifier.
支持这些变量：
versionmax_allowed_packet
!!! warning "警告" 到目前为止，这些变量是存根，并且不对应任何内容。
示例:
SELECT @@version;
使用示例
MySQL操作:
mysql> USE test;
Database changed
mysql> CREATE TABLE `mysql_table` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `float` FLOAT NOT NULL,
-> PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from mysql_table;
+------+-----+
| int_id | value |
+------+-----+
| 1 | 2 |
+------+-----+
1 row in set (0,00 sec)
ClickHouse中的数据库，与MySQL服务器交换数据:
CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')
SHOW DATABASES
┌─name─────┐
│ default │
│ mysql_db │
│ system │
└──────────┘
SHOW TABLES FROM mysql_db
┌─name─────────┐
│ mysql_table │
└──────────────┘
SELECT * FROM mysql_db.mysql_table
┌─int_id─┬─value─┐
│ 1 │ 2 │
└────────┴───────┘
INSERT INTO mysql_db.mysql_table VALUES (3,4)
SELECT * FROM mysql_db.mysql_table
┌─int_id─┬─value─┐
│ 1 │ 2 │
│ 3 │ 4 │
└────────┴───────┘Lazy
在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于*Log表。
它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔。
创建数据库
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
Atomic
它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。
创建数据库
CREATE DATABASE test[ ENGINE = Atomic];
使用方式
Table UUID
数据库Atomic中的所有表都有唯一的UUID，并将数据存储在目录/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的UUID。
通常，UUID是自动生成的，但用户也可以在创建表时以相同的方式显式指定UUID(不建议这样做)。可以使用 show_table_uuid_in_table_create_query_if_not_nil设置。显示UUID的使用SHOW CREATE查询。例如:
CREATE TABLE name UUID '28f1c61c-2970-457a-bffe-454156ddcfef' (n UInt64) ENGINE = ...;
RENAME TABLES
RENAME查询是在不更改UUID和移动表数据的情况下执行的。这些查询不会等待使用表的查询完成，而是会立即执行。
DROP/DETACH TABLES
在DROP TABLE上，不删除任何数据，数据库Atomic只是通过将元数据移动到/clickhouse_path/metadata_dropped/将表标记为已删除，并通知后台线程。最终表数据删除前的延迟由database_atomic_delay_before_drop_table_sec设置指定。
可以使用SYNC修饰符指定同步模式。使用database_atomic_wait_for_drop_and_detach_synchronously设置执行此操作。在本例中，DROP等待运行 SELECT, INSERT和其他使用表完成的查询。表在不使用时将被实际删除。
EXCHANGE TABLES
EXCHANGE以原子方式交换表。因此，不是这种非原子操作：
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
可以使用一个原子查询：
EXCHANGE TABLES new_table AND old_table;
ReplicatedMergeTree in Atomic Database
对于ReplicatedMergeTree表，建议不要在ZooKeeper和副本名称中指定engine-path的参数。在这种情况下，将使用配置的参数default_replica_path和default_replica_name。如果要显式指定引擎的参数，建议使用{uuid}宏。这是非常有用的，以便为ZooKeeper中的每个表自动生成唯一的路径。
SQLite
允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据， 执行 INSERT 和 SELECT 查询。
创建一个数据库
CREATE DATABASE sqlite_database
ENGINE = SQLite('db_path')
引擎参数
db_path— SQLite 数据库文件的路径.
数据类型的支持
| SQLite | ClickHouse |
|---|---|
| INTEGER | Int32 |
| REAL | Float32 |
| TEXT | String |
| BLOB | String |
技术细节和建议
SQLite将整个数据库(定义、表、索引和数据本身)存储为主机上的单个跨平台文件。在写入过程中，SQLite会锁定整个数据库文件，因此写入操作是顺序执行的。读操作可以是多任务的。 SQLite不需要服务管理(如启动脚本)或基于GRANT和密码的访问控制。访问控制是通过授予数据库文件本身的文件系统权限来处理的。
使用示例
数据库在ClickHouse，连接到SQLite:
CREATE DATABASE sqlite_db ENGINE = SQLite('sqlite.db');
SHOW TABLES FROM sqlite_db;
┌──name───┐
│ table1 │
│ table2 │
└─────────┘
展示数据表中的内容:
SELECT * FROM sqlite_db.table1;
┌─col1──┬─col2─┐
│ line1 │ 1 │
│ line2 │ 2 │
│ line3 │ 3 │
└───────┴──────┘
从ClickHouse表插入数据到SQLite表:
CREATE TABLE clickhouse_table(`col1` String,`col2` Int16) ENGINE = MergeTree() ORDER BY col2;
INSERT INTO clickhouse_table VALUES ('text',10);
INSERT INTO sqlite_db.table1 SELECT * FROM clickhouse_table;
SELECT * FROM sqlite_db.table1;
┌─col1──┬─col2─┐
│ line1 │ 1 │
│ line2 │ 2 │
│ line3 │ 3 │
│ text │ 10 │
└───────┴──────┘PostgreSQL
允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。
在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。
支持表结构修改(ALTER TABLE ... ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1，则会缓存表结构，不会检查是否被修改，但可以用DETACH和ATTACH查询进行更新。
创建数据库
CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);
引擎参数
host:port— PostgreSQL服务地址database— 远程数据库名次user— PostgreSQL用户名称password— PostgreSQL用户密码schema- PostgreSQL 模式use_table_cache— 定义数据库表结构是否已缓存或不进行。可选的。默认值：0.
支持的数据类型
| PostgerSQL | ClickHouse |
|---|---|
| DATE | Date |
| TIMESTAMP | DateTime |
| REAL | Float32 |
| DOUBLE | Float64 |
| DECIMAL, NUMERIC | Decimal |
| SMALLINT | Int16 |
| INTEGER | Int32 |
| BIGINT | Int64 |
| SERIAL | UInt32 |
| BIGSERIAL | UInt64 |
| TEXT, CHAR | String |
| INTEGER | Nullable(Int32) |
| ARRAY | Array |
使用示例
ClickHouse中的数据库，与PostgreSQL服务器交换数据:
CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 1);
SHOW DATABASES;
┌─name──────────┐
│ default │
│ test_database │
│ system │
└───────────────┘
SHOW TABLES FROM test_database;
┌─name───────┐
│ test_table │
└────────────┘
从PostgreSQL表中读取数据：
SELECT * FROM test_database.test_table;
┌─id─┬─value─┐
│ 1 │ 2 │
└────┴───────┘
将数据写入PostgreSQL表：
INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;
┌─int_id─┬─value─┐
│ 1 │ 2 │
│ 3 │ 4 │
└────────┴───────┘
在PostgreSQL中修改了表结构：
postgre> ALTER TABLE test_table ADD COLUMN data Text
当创建数据库时，参数use_table_cache被设置为1，ClickHouse中的表结构被缓存，因此没有被修改:
DESCRIBE TABLE test_database.test_table;
┌─name───┬─type──────────────┐
│ id │ Nullable(Integer) │
│ value │ Nullable(Integer) │
└────────┴───────────────────┘
分离表并再次附加它之后，结构被更新了:
DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;
┌─name───┬─type──────────────┐
│ id │ Nullable(Integer) │
│ value │ Nullable(Integer) │
│ data │ Nullable(String) │
└────────┴───────────────────┘Replicated
该引擎基于Atomic引擎。它支持通过将DDL日志写入ZooKeeper并在给定数据库的所有副本上执行的元数据复制。
一个ClickHouse服务器可以同时运行和更新多个复制的数据库。但是同一个复制的数据库不能有多个副本。
创建数据库
CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]
引擎参数
zoo_path— ZooKeeper地址，同一个ZooKeeper路径对应同一个数据库。shard_name— 分片的名字。数据库副本按shard_name分组到分片中。replica_name— 副本的名字。同一分片的所有副本的副本名称必须不同。
!!! note "警告" 对于ReplicatedMergeTree表，如果没有提供参数，则使用默认参数:/clickhouse/tables/{uuid}/{shard}和{replica}。这些可以在服务器设置default_replica_path和default_replica_name中更改。宏{uuid}被展开到表的uuid， {shard}和{replica}被展开到服务器配置的值，而不是数据库引擎参数。但是在将来，可以使用Replicated数据库的shard_name和replica_name。
使用方式
使用Replicated数据库的DDL查询的工作方式类似于ON CLUSTER查询，但有细微差异。
首先，DDL请求尝试在启动器(最初从用户接收请求的主机)上执行。如果请求没有完成，那么用户立即收到一个错误，其他主机不会尝试完成它。如果在启动器上成功地完成了请求，那么所有其他主机将自动重试，直到完成请求。启动器将尝试在其他主机上等待查询完成(不超过distributed_ddl_task_timeout)，并返回一个包含每个主机上查询执行状态的表。
错误情况下的行为是由distributed_ddl_output_mode设置调节的，对于Replicated数据库，最好将其设置为null_status_on_timeout - 例如，如果一些主机没有时间执行distributed_ddl_task_timeout的请求，那么不要抛出异常，但在表中显示它们的NULL状态。
system.clusters系统表包含一个名为复制数据库的集群，它包含数据库的所有副本。当创建/删除副本时，这个集群会自动更新，它可以用于Distributed表。
当创建数据库的新副本时，该副本会自己创建表。如果副本已经不可用很长一段时间，并且已经滞后于复制日志-它用ZooKeeper中的当前元数据检查它的本地元数据，将带有数据的额外表移动到一个单独的非复制数据库(以免意外地删除任何多余的东西)，创建缺失的表，如果表名已经被重命名，则更新表名。数据在ReplicatedMergeTree级别被复制，也就是说，如果表没有被复制，数据将不会被复制(数据库只负责元数据)。
允许ALTER TABLE ATTACH|FETCH|DROP|DROP DETACHED|DETACH PARTITION|PART查询，但不允许复制。数据库引擎将只向当前副本添加/获取/删除分区/部件。但是，如果表本身使用了Replicated表引擎，那么数据将在使用ATTACH后被复制。
使用示例
创建三台主机的集群:
node1 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','shard1','replica1');
node2 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','shard1','other_replica');
node3 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','other_shard','{replica}');
运行DDL:
CREATE TABLE r.rmt (n UInt64) ENGINE=ReplicatedMergeTree ORDER BY n;
┌─────hosts────────────┬──status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ shard1|replica1 │ 0 │ │ 2 │ 0 │
│ shard1|other_replica │ 0 │ │ 1 │ 0 │
│ other_shard|r1 │ 0 │ │ 0 │ 0 │
└──────────────────────┴─────────┴───────┴─────────────────────┴──────────────────┘
显示系统表:
SELECT cluster, shard_num, replica_num, host_name, host_address, port, is_local
FROM system.clusters WHERE cluster='r';
┌─cluster─┬─shard_num─┬─replica_num─┬─host_name─┬─host_address─┬─port─┬─is_local─┐
│ r │ 1 │ 1 │ node3 │ 127.0.0.1 │ 9002 │ 0 │
│ r │ 2 │ 1 │ node2 │ 127.0.0.1 │ 9001 │ 0 │
│ r │ 2 │ 2 │ node1 │ 127.0.0.1 │ 9000 │ 1 │
└─────────┴───────────┴─────────────┴───────────┴──────────────┴──────┴──────────┘
创建分布式表并插入数据:
node2 :) CREATE TABLE r.d (n UInt64) ENGINE=Distributed('r','r','rmt', n % 2);
node3 :) INSERT INTO r SELECT * FROM numbers(10);
node1 :) SELECT materialize(hostName()) AS host, groupArray(n) FROM r.d GROUP BY host;
┌─hosts─┬─groupArray(n)─┐
│ node1 │ [1,3,5,7,9] │
│ node2 │ [0,2,4,6,8] │
└───────┴───────────────┘
向一台主机添加副本:
node4 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','other_shard','r2');
集群配置如下所示:
┌─cluster─┬─shard_num─┬─replica_num─┬─host_name─┬─host_address─┬─port─┬─is_local─┐
│ r │ 1 │ 1 │ node3 │ 127.0.0.1 │ 9002 │ 0 │
│ r │ 1 │ 2 │ node4 │ 127.0.0.1 │ 9003 │ 0 │
│ r │ 2 │ 1 │ node2 │ 127.0.0.1 │ 9001 │ 0 │
│ r │ 2 │ 2 │ node1 │ 127.0.0.1 │ 9000 │ 1 │
└─────────┴───────────┴─────────────┴───────────┴──────────────┴──────┴──────────┘
分布式表也将从新主机获取数据:
node2 :) SELECT materialize(hostName()) AS host, groupArray(n) FROM r.d GROUP BY host;
┌─hosts─┬─groupArray(n)─┐
│ node2 │ [1,3,5,7,9] │
│ node4 │ [0,2,4,6,8] │
└───────┴───────────────┘表引擎
表引擎（即表的类型）决定了：
- 数据的存储方式和位置，写到哪里以及从哪里读取数据
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用（如果存在）。
- 是否可以执行多线程请求。
- 数据复制参数。
引擎类型
MergeTree
适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。
该类型的引擎：
- MergeTree
- ReplacingMergeTree
- SummingMergeTree
- AggregatingMergeTree
- CollapsingMergeTree
- VersionedCollapsingMergeTree
- GraphiteMergeTree
日志
具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。
该类型的引擎：
集成引擎
用于与其他的数据存储与处理系统集成的引擎。 该类型的引擎：
用于其他特定功能的引擎
该类型的引擎：
虚拟列
虚拟列是表引擎组成的一部分，它在对应的表引擎的源代码中定义。
您不能在 CREATE TABLE 中指定虚拟列，并且虚拟列不会包含在 SHOW CREATE TABLE 和 DESCRIBE TABLE 的查询结果中。虚拟列是只读的，所以您不能向虚拟列中写入数据。
如果想要查询虚拟列中的数据，您必须在SELECT查询中包含虚拟列的名字。SELECT * 不会返回虚拟列的内容。
若您创建的表中有一列与虚拟列的名字相同，那么虚拟列将不能再被访问。我们不建议您这样做。为了避免这种列名的冲突，虚拟列的名字一般都以下划线开头。
MergeTree
Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。
MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。
主要特点:
-
存储的数据按主键排序。
这使得您能够创建一个小型的稀疏索引来加快数据检索。
-
如果指定了 分区键 的话，可以使用分区。
在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
-
支持数据副本。
ReplicatedMergeTree系列的表提供了数据副本功能。更多信息，请参阅 数据副本 一节。 -
支持数据采样。
需要的话，您可以给表设置一个采样方法。
!!! note "注意" 合并 引擎并不属于 *MergeTree 系列。
建表
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, ...]
对于以上参数的描述，可参考 CREATE 语句 的描述 。
子句
-
ENGINE- 引擎名和参数。ENGINE = MergeTree().MergeTree引擎没有参数。 -
ORDER BY— 排序键。可以是一组列的元组或任意的表达式。 例如:
ORDER BY (CounterID, EventDate)。如果没有使用
PRIMARY KEY显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用
ORDER BY tuple(). 参考 选择主键 -
PARTITION BY— 分区键 ，可选项。要按月分区，可以使用表达式
toYYYYMM(date_column)，这里的date_column是一个 Date 类型的列。分区名的格式会是"YYYYMM"。 -
PRIMARY KEY- 如果要 选择与排序键不同的主键，在这里指定，可选项。默认情况下主键跟排序键（由
ORDER BY子句指定）相同。 因此，大部分情况下不需要再专门指定一个PRIMARY KEY子句。 -
SAMPLE BY- 用于抽样的表达式，可选项。如果要用抽样表达式，主键中必须包含这个表达式。例如：
SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。 -
TTL- 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。表达式中必须存在至少一个
Date或DateTime类型的列，比如：TTL date + INTERVAl 1 DAY规则的类型
DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。更多细节，请查看 表和列的 TTL
-
SETTINGS— 控制MergeTree行为的额外参数，可选项：index_granularity— 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。index_granularity_bytes— 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。min_index_granularity_bytes- 允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。参考数据存储enable_mixed_granularity_parts— 是否启用通过index_granularity_bytes控制索引粒度的大小。在19.11版本之前, 只有index_granularity配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT查询的性能。use_minimalistic_part_header_in_zookeeper— ZooKeeper中数据片段存储方式 。如果use_minimalistic_part_header_in_zookeeper=1，ZooKeeper 会存储更少的数据。更多信息参考[服务配置参数](Server Settings | ClickHouse Documentation)这章中的 设置描述 。min_merge_bytes_to_use_direct_io— 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了min_merge_bytes_to_use_direct_io设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT选项）对磁盘读写。如果设置min_merge_bytes_to_use_direct_io = 0，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024字节。<a name="mergetree_setting-merge_with_ttl_timeout"></a>merge_with_ttl_timeout— TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。write_final_mark— 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。merge_max_block_size— 在块中进行合并操作时的最大行数限制。默认值：8192storage_policy— 存储策略。 参见 使用具有多个块的设备进行数据存储.min_bytes_for_wide_part,min_rows_for_wide_part在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。参考：数据存储max_parts_in_total- 所有分区中最大块的数量(意义不明)max_compress_block_size- 在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值(参见max_compress_block_size)。建表时指定该值会覆盖全局设置。min_compress_block_size- 在数据压缩写入表前，未压缩数据块的最小大小。您可以在全局设置中设置该值(参见min_compress_block_size)。建表时指定该值会覆盖全局设置。max_partitions_to_read- 一次查询中可访问的分区最大数。您可以在全局设置中设置该值(参见max_partitions_to_read)。
示例配置
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192
在这个例子中，我们设置了按月进行分区。
同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了 SAMPLE 子句。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。
index_granularity 可省略因为 8192 是默认设置 。
已弃用的建表方法
数据存储
表由按主键排序的数据片段（DATA PART）组成。
当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按 Date 排序。
不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。
数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能。
数据存储格式由 min_bytes_for_wide_part 和 min_rows_for_wide_part 表引擎参数控制。如果数据片段中的字节数或行数少于相应的设置值，数据片段会以 Compact 格式存储，否则会以 Wide 格式存储。
每个数据片段被逻辑的分割成颗粒（granules）。颗粒是 ClickHouse 中进行数据查询时的最小不可分割数据集。ClickHouse 不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记， ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse 都会存储类似标记。这些标记让您可以在列文件中直接找到数据。
颗粒的大小通过表引擎参数 index_granularity 和 index_granularity_bytes 控制。颗粒的行数的在 [1, index_granularity] 范围中，这取决于行的大小。如果单行的大小超过了 index_granularity_bytes 设置的值，那么一个颗粒的大小会超过 index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。
主键和索引在查询中的表现
我们以 (CounterID, Date) 以主键。排序好的索引的图示会是下面这样：
全部数据 : [-------------------------------------------------------------------------]
CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date: [1111111222222233331233211111222222333211111112122222223111112223311122333]
标记: | | | | | | | | | | |
a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3
标记号: 0 1 2 3 4 5 6 7 8 9 10
如果指定查询如下：
CounterID in ('a', 'h')，服务器会读取标记号在[0, 3)和[6, 8)区间中的数据。CounterID IN ('a', 'h') AND Date = 3，服务器会读取标记号在[1, 3)和[7, 8)区间中的数据。Date = 3，服务器会读取标记号在[1, 10]区间中的数据。
上面例子可以看出使用索引通常会比全表描述要高效。
稀疏索引会引起额外的数据读取。当读取主键单个区间范围的数据时，每个数据块中最多会多读 index_granularity * 2 行额外的数据。
稀疏索引使得您可以处理极大量的行，因为大多数情况下，这些索引常驻于内存。
ClickHouse 不要求主键唯一，所以您可以插入多条具有相同主键的行。
您可以在PRIMARY KEY与ORDER BY条件中使用可为空的类型的表达式，但强烈建议不要这么做。为了启用这项功能，请打开allow_nullable_key，NULLS_LAST规则也适用于ORDER BY条件中有NULL值的情况下。
主键的选择
主键中列的数量并没有明确的限制。依据数据结构，您可以在主键包含多些或少些列。这样可以：
-
改善索引的性能。
-
如果当前主键是
(a, b)，在下列情况下添加另一个c列会提升性能： -
查询会使用
c列作为条件 -
很长的数据范围（
index_granularity的数倍）里(a, b)都是相同的值，并且这样的情况很普遍。换言之，就是加入另一列后，可以让您的查询略过很长的数据范围。 -
改善数据压缩。
ClickHouse 以主键排序片段数据，所以，数据的一致性越高，压缩越好。
-
在CollapsingMergeTree 和 SummingMergeTree 引擎里进行数据合并时会提供额外的处理逻辑。
在这种情况下，指定与主键不同的 排序键 也是有意义的。
长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响 SELECT 查询的性能。
可以使用 ORDER BY tuple() 语法创建没有主键的表。在这种情况下 ClickHouse 根据数据插入的顺序存储。如果在使用 INSERT ... SELECT 时希望保持数据的排序，请设置 max_insert_threads = 1。
想要根据初始顺序进行数据查询，使用 单线程查询
选择与排序键不同的主键
Clickhouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元组的前缀(即主键为(a,b)，排序列必须为(a,b,**))。
当使用 SummingMergeTree 和 AggregatingMergeTree 引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分两种：维度 和 度量 。典型的查询会通过任意的 GROUP BY 对度量列进行聚合并通过维度列进行过滤。由于 SummingMergeTree 和 AggregatingMergeTree 会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法。但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断的更新。
在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。
对排序键进行 ALTER 是轻量级的操作，因为当一个新列同时被加入到表里和排序键里时，已存在的数据片段并不需要修改。由于旧的排序键是新排序键的前缀，并且新添加的列中没有数据，因此在表修改时的数据对于新旧的排序键来说都是有序的。
索引和分区在查询中的应用
对于 SELECT 查询，ClickHouse 分析是否可以使用索引。如果 WHERE/PREWHERE 子句具有下面这些表达式（作为完整WHERE条件的一部分或全部）则可以使用索引：进行相等/不相等的比较；对主键列或分区列进行IN运算、有固定前缀的LIKE运算(如name like 'test%')、函数运算(部分函数适用)，还有对上述表达式进行逻辑运算。
因此，在索引键的一个或多个区间上快速地执行查询是可能的。下面例子中，指定标签；指定标签和日期范围；指定标签和日期；指定多个标签和日期范围等执行查询，都会非常快。
当引擎配置如下时：
ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192
这种情况下，这些查询：
SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))
ClickHouse 会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。
上文的查询显示，即使索引用于复杂表达式，因为读表操作经过优化，所以使用索引不会比完整扫描慢。
下面这个例子中，不会使用索引。
SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'
要检查 ClickHouse 执行一个查询时能否使用索引，可设置 force_index_by_date 和 force_primary_key 。
使用按月分区的分区列允许只读取包含适当日期区间的数据块，这种情况下，数据块会包含很多天（最多整月）的数据。在块中，数据按主键排序，主键第一列可能不包含日期。因此，仅使用日期而没有用主键字段作为条件的查询将会导致需要读取超过这个指定日期以外的数据。
部分单调主键的使用
考虑这样的场景，比如一个月中的天数。它们在一个月的范围内形成一个单调序列 ，但如果扩展到更大的时间范围它们就不再单调了。这就是一个部分单调序列。如果用户使用部分单调的主键创建表，ClickHouse同样会创建一个稀疏索引。当用户从这类表中查询数据时，ClickHouse 会对查询条件进行分析。如果用户希望获取两个索引标记之间的数据并且这两个标记在一个月以内，ClickHouse 可以在这种特殊情况下使用到索引，因为它可以计算出查询参数与索引标记之间的距离。
如果查询参数范围内的主键不是单调序列，那么 ClickHouse 无法使用索引。在这种情况下，ClickHouse 会进行全表扫描。
ClickHouse 在任何主键代表一个部分单调序列的情况下都会使用这个逻辑。
跳数索引
此索引在 CREATE 语句的列部分里定义。
INDEX index_name expr TYPE type(...) GRANULARITY granularity_value
*MergeTree 系列的表可以指定跳数索引。 跳数索引是指数据片段按照粒度(建表时指定的index_granularity)分割成小块后，将上述SQL的granularity_value数量的小块组合成一个大的块，对这些大块写入索引信息，这样有助于使用where筛选时跳过大量不必要的数据，减少SELECT需要读取的数据量。
示例
CREATE TABLE table_name
(
u64 UInt64,
i32 Int32,
s String,
...
INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,
INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...
上例中的索引能让 ClickHouse 执行下面这些查询时减少读取数据量。
SELECT count() FROM table WHERE s < 'z'
SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234
可用的索引类型
-
minmax存储指定表达式的极值（如果表达式是tuple，则存储tuple中每个元素的极值），这些信息用于跳过数据块，类似主键。 -
set(max_rows)存储指定表达式的不重复值（不超过max_rows个，max_rows=0则表示『无限制』）。这些信息可用于检查数据块是否满足WHERE条件。 -
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)存储一个包含数据块中所有 n元短语（ngram） 的 布隆过滤器 。只可用在字符串上。 可用于优化equals，like和in表达式的性能。n– 短语长度。size_of_bloom_filter_in_bytes– 布隆过滤器大小，字节为单位。（因为压缩得好，可以指定比较大的值，如 256 或 512）。number_of_hash_functions– 布隆过滤器中使用的哈希函数的个数。random_seed– 哈希函数的随机种子。
-
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)跟ngrambf_v1类似，但是存储的是token而不是ngrams。Token是由非字母数字的符号分割的序列。 -
bloom_filter(bloom_filter([false_positive])– 为指定的列存储布隆过滤器可选参数
false_positive用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)，默认值：0.025支持的数据类型：
Int*,UInt*,Float*,Enum,Date,DateTime,String,FixedString,Array,LowCardinality,Nullable。
INDEX sample_index (u64 * length(s)) TYPE minmax GRANULARITY 4
INDEX sample_index2 (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4
INDEX sample_index3 (lower(str), str) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4
函数支持
WHERE 子句中的条件可以包含对某列数据进行运算的函数表达式，如果列是索引的一部分，ClickHouse会在执行函数时尝试使用索引。不同的函数对索引的支持是不同的。
set 索引会对所有函数生效，其他索引对函数的生效情况见下表
| 函数 (操作符) / 索引 | primary key | minmax | ngrambf_v1 | tokenbf_v1 | bloom_filter |
|---|---|---|---|---|---|
| equals (=, ==) | ✔ | ✔ | ✔ | ✔ | ✔ |
| notEquals(!=, <>) | ✔ | ✔ | ✔ | ✔ | ✔ |
| like | ✔ | ✔ | ✔ | ✔ | ✔ |
| notLike | ✔ | ✔ | ✗ | ✗ | ✗ |
| startsWith | ✔ | ✔ | ✔ | ✔ | ✗ |
| endsWith | ✗ | ✗ | ✔ | ✔ | ✗ |
| multiSearchAny | ✗ | ✗ | ✔ | ✗ | ✗ |
| in | ✔ | ✔ | ✔ | ✔ | ✔ |
| notIn | ✔ | ✔ | ✔ | ✔ | ✔ |
| less (\<) | ✔ | ✔ | ✗ | ✗ | ✗ |
| greater (>) | ✔ | ✔ | ✗ | ✗ | ✗ |
| lessOrEquals (\<=) | ✔ | ✔ | ✗ | ✗ | ✗ |
| greaterOrEquals (>=) | ✔ | ✔ | ✗ | ✗ | ✗ |
| empty | ✔ | ✔ | ✗ | ✗ | ✗ |
| notEmpty | ✔ | ✔ | ✗ | ✗ | ✗ |
| hasToken | ✗ | ✗ | ✗ | ✔ | ✗ |
常量参数小于 ngram 大小的函数不能使用 ngrambf_v1 进行查询优化。
!!! note "注意" 布隆过滤器可能会包含不符合条件的匹配，所以 ngrambf_v1, tokenbf_v1 和 bloom_filter 索引不能用于结果返回为假的函数，例如：
- 可以用来优化的场景
s LIKE '%test%'NOT s NOT LIKE '%test%'s = 1NOT s != 1startsWith(s, 'test')
- 不能用来优化的场景
NOT s LIKE '%test%'s NOT LIKE '%test%'NOT s = 1s != 1NOT startsWith(s, 'test')
并发数据访问
对于表的并发访问，我们使用多版本机制。换言之，当一张表同时被读和更新时，数据从当前查询到的一组片段中读取。没有冗长的的锁。插入不会阻碍读取。
对表的读操作是自动并行的。
列和表的 TTL
TTL用于设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置。表级别的 TTL 还会指定数据在磁盘和卷上自动转移的逻辑。
TTL 表达式的计算结果必须是 日期 或 日期时间 类型的字段。
示例：
TTL time_column
TTL time_column + interval
要定义interval, 需要使用 时间间隔 操作符。
TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR
列 TTL
当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。
TTL子句不能被用于主键字段。
示例:
创建表时指定 TTL
CREATE TABLE example_table
(
d DateTime,
a Int TTL d + INTERVAL 1 MONTH,
b Int TTL d + INTERVAL 1 MONTH,
c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;
为表中已存在的列字段添加 TTL
ALTER TABLE example_table
MODIFY COLUMN
c String TTL d + INTERVAL 1 DAY;
修改列字段的 TTL
ALTER TABLE example_table
MODIFY COLUMN
c String TTL d + INTERVAL 1 MONTH;
表 TTL
表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。
TTL expr
[DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
[WHERE conditions]
[GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]
TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时（到达指定时间时）应当执行的操作：
DELETE- 删除过期的行（默认操作）;TO DISK 'aaa'- 将数据片段移动到磁盘aaa;TO VOLUME 'bbb'- 将数据片段移动到卷bbb.GROUP BY- 聚合过期的行
使用WHERE从句，您可以指定哪些过期的行会被删除或聚合(不适用于移动)。GROUP BY表达式必须是表主键的前缀。如果某列不是GROUP BY表达式的一部分，也没有在SET从句显示引用，结果行中相应列的值是随机的(就好像使用了any函数)。
示例:
创建时指定 TTL
CREATE TABLE example_table
(
d DateTime,
a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
d + INTERVAL 1 WEEK TO VOLUME 'aaa',
d + INTERVAL 2 WEEK TO DISK 'bbb';
修改表的 TTL
ALTER TABLE example_table
MODIFY TTL d + INTERVAL 1 DAY;
创建一张表，设置一个月后数据过期，这些过期的行中日期为星期一的删除：
CREATE TABLE table_with_where
(
d DateTime,
a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE WHERE toDayOfWeek(d) = 1;
创建一张表，设置过期的列会被聚合。列x包含每组行中的最大值，y为最小值，d为可能任意值。
CREATE TABLE table_for_aggregation
(
d DateTime,
k1 Int,
k2 Int,
x Int,
y Int
)
ENGINE = MergeTree
ORDER BY (k1, k2)
TTL d + INTERVAL 1 MONTH GROUP BY k1, k2 SET x = max(x), y = min(y);
删除数据
ClickHouse 在数据片段合并时会删除掉过期的数据。
当ClickHouse发现数据过期时, 它将会执行一个计划外的合并。要控制这类合并的频率, 您可以设置 merge_with_ttl_timeout。如果该值被设置的太低, 它将引发大量计划外的合并，这可能会消耗大量资源。
如果在两次合并的时间间隔中执行 SELECT 查询, 则可能会得到过期的数据。为了避免这种情况，可以在 SELECT 之前使用 OPTIMIZE 。
使用多个块设备进行数据存储
介绍
MergeTree 系列表引擎可以将数据存储在多个块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。最新数据被定期的查询但只需要很小的空间。相反，详尽的历史数据很少被用到。如果有多块磁盘可用，那么“热”的数据可以放置在快速的磁盘上（比如 NVMe 固态硬盘或内存），“冷”的数据可以放在相对较慢的磁盘上（比如机械硬盘）。
数据片段是 MergeTree 引擎表的最小可移动单元。属于同一个数据片段的数据被存储在同一块磁盘上。数据片段会在后台自动的在磁盘间移动，也可以通过 ALTER 查询来移动。
术语
-
磁盘 — 挂载到文件系统的块设备
-
默认磁盘 — 在服务器设置中通过 path 参数指定的数据存储
-
卷 — 相同磁盘的顺序列表 （类似于 JBOD）
-
存储策略 — 卷的集合及他们之间的数据移动规则
以上名称的信息在Clickhouse中系统表system.storage_policies和system.disks体现。为了应用存储策略，可以在建表时使用
storage_policy设置。
配置
磁盘、卷和存储策略应当在主配置文件 config.xml 或 config.d 目录中的独立文件中的 <storage_configuration> 标签内定义。
配置结构：
<storage_configuration>
<disks>
<disk_name_1> <!-- disk name -->
<path>/mnt/fast_ssd/clickhouse/</path>
</disk_name_1>
<disk_name_2>
<path>/mnt/hdd1/clickhouse/</path>
<keep_free_space_bytes>10485760</keep_free_space_bytes>
</disk_name_2>
<disk_name_3>
<path>/mnt/hdd2/clickhouse/</path>
<keep_free_space_bytes>10485760</keep_free_space_bytes>
</disk_name_3>
...
</disks>
...
</storage_configuration>
标签：
<disk_name_N>— 磁盘名，名称必须与其他磁盘不同.path— 服务器将用来存储数据 (data和shadow目录) 的路径, 应当以 ‘/’ 结尾.keep_free_space_bytes— 需要保留的剩余磁盘空间.
磁盘定义的顺序无关紧要。
存储策略配置：
<storage_configuration>
...
<policies>
<policy_name_1>
<volumes>
<volume_name_1>
<disk>disk_name_from_disks_configuration</disk>
<max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
</volume_name_1>
<volume_name_2>
<!-- configuration -->
</volume_name_2>
<!-- more volumes -->
</volumes>
<move_factor>0.2</move_factor>
</policy_name_1>
<policy_name_2>
<!-- configuration -->
</policy_name_2>
<!-- more policies -->
</policies>
...
</storage_configuration>
标签：
policy_name_N— 策略名称，不能重复。volume_name_N— 卷名称，不能重复。disk— 卷中的磁盘。max_data_part_size_bytes— 卷中的磁盘可以存储的数据片段的最大大小。move_factor— 当可用空间少于这个因子时，数据将自动的向下一个卷（如果有的话）移动 (默认值为 0.1)。prefer_not_to_merge- 禁止在这个卷中进行数据合并。该选项启用时，对该卷的数据不能进行合并。这个选项主要用于慢速磁盘。
配置示例：
<storage_configuration>
...
<policies>
<hdd_in_order> <!-- policy name -->
<volumes>
<single> <!-- volume name -->
<disk>disk1</disk>
<disk>disk2</disk>
</single>
</volumes>
</hdd_in_order>
<moving_from_ssd_to_hdd>
<volumes>
<hot>
<disk>fast_ssd</disk>
<max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
</hot>
<cold>
<disk>disk1</disk>
</cold>
</volumes>
<move_factor>0.2</move_factor>
</moving_from_ssd_to_hdd>
<small_jbod_with_external_no_merges>
<volumes>
<main>
<disk>jbod1</disk>
</main>
<external>
<disk>external</disk>
<prefer_not_to_merge>true</prefer_not_to_merge>
</external>
</volumes>
</small_jbod_with_external_no_merges>