Clickhouse常见运维操作

集群现状

ck集群信息

clickhouse集群三个节点

集群拓扑

ck集群，3分片1副本
集群名称 default

监控

日常运维

命令行连接

查询日志 query_log

query_log是最常用的查询日志，它记录了ClickHouse服务中所有已经执行的查询记录

select * from system.query_log limit 10;

分布式表

Distributed表引擎是分布式表的代名词，它自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。

注意事项：

1. 对于分布式表与本地表之间表结构的一致性检查，Distributed表引擎采用了读时检查的机制，这意味着如果它们的表结构不兼容，只有在查询时才会抛出错误，而在创建表时并不会进行检查。
2. 不同ClickHouse节点上的本地表之间，使用不同的表引擎也是可行的，但是通常不建议这么做，保持它们的结构一致，有利于后期的维护并避免造成不可预计的错误。

分布式DDL

默认情况下，CREATE、DROP、ALTER和RENAME查询仅影响执行它们的当前服务器。 在集群设置中，可以使用ON CLUSTER子句以分布式方式运行此类查询。

示例：使用分布式DDL创建分布式表和本地表

# 使用ON CLUSTER在集群的每个节点上都创建分布式表
CREATE TABLE test.lan_test ON CLUSTER default
(
    `date` Date,
    `id` UInt8,
    `name` String,
    `value` UInt16,
    `point` DateTime
)
ENGINE = Distributed('default', 'test', 'lan_test_local', rand());

# 使用ON CLUSTER在集群的每个节点上都创建本地表
CREATE TABLE test.lan_test_local ON CLUSTER default
(
    `date` Date,
    `id` UInt8,
    `name` String,
    `value` UInt16,
    `point` DateTime
)
ENGINE = ReplacingMergeTree(point)
PARTITION BY date
ORDER BY (id, name)
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

建库

语法

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)] [COMMENT 'Comment']

ck支持许多数据库引擎，如下所示：

• Ordinary：原默认引擎，在此数据库下可以使用任意类型的表引擎。
• Atomic：新的默认引擎。提供了一些原子操作和异步处理
• Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
• Lazy：Lazy引擎，用于优化量级较小的Log表，原理是每次访问之后，将该表保存在内存中一段时间，此类数据库下只能使用Log系列的表引擎。
• MySQL：MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。
• MaterializedMySQL：物化MySQL引擎，实验性的。相当于使用ClickHouse作为MySQL的副本
• MaterializedPostgreSQL：类似物化MySQL引擎
• PostgreSQL：类似MySQL引擎
• SQLite: 类似MySQL引擎
• Replicated：副本引擎，实验性的

示例

# 在当前节点新建数据库
CREATE DATABASE test_db;

# 在当前节点新建数据库，并填写注释
CREATE DATABASE test_db ENGINE = Memory COMMENT '测试数据库';

# 在集群cluster_1st的所有节点，新建数据库
CREATE DATABASE test_db ON CLUSTER cluster_1st ENGINE = Memory COMMENT '测试数据库';

删库

语法

DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]

示例

# 删除当前节点的 test_db 数据库
DROP DATABASE test_db;

# 删除 cluster_1st 所有节点的 test_db 数据库
DROP DATABASE test_db ON CLUSTER cluster_1st;

建表

语法

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

示例

不同的表引擎选项不同，这里以 MergeTree() 引擎作为示例。

CREATE TABLE partition_v1 (
	ID String,
	URL String,
	EventTime Date)
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

分布式建表与建库用法类似， 只需要加上 ON CLUSTER

CREATE TABLE partition_v1 ON CLUSTER cluster_1st (
	ID String,
	URL String,
	EventTime Date)
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

删表

语法

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

示例

# 删除当前节点的 partition_v1 表
DROP TABLE default.partition_v1;

# 删除 cluster_1st 所有节点的 partition_v1 表
DROP TABLE default.partition_v1 ON CLUSTER cluster_1st;

列操作

语法

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...

• ADD COLUMN — 向表中添加一列
• DROP COLUMN — 删除表中的一列
• RENAME COLUMN — 重命名已存在的列
• CLEAR COLUMN — 重置列的值
• COMMENT COLUMN — 添加列的备注
• MODIFY COLUMN — 更改列的类型、默认表达式和TTL

添加列

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST]

示例

ALTER TABLE alter_test ADD COLUMN Added1 UInt32 FIRST;
ALTER TABLE alter_test ADD COLUMN Added2 UInt32 AFTER NestedColumn;
ALTER TABLE alter_test ADD COLUMN Added3 UInt32 AFTER ToDrop;

删除列

DROP COLUMN [IF EXISTS] name

示例

ALTER TABLE visits DROP COLUMN browser

列重命名

RENAME COLUMN [IF EXISTS] name to new_name

示例

ALTER TABLE t RENAME COLUMN col1 to col1_new;

清空列

CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name

示例

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

更改列的属性

MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST]
ALTER COLUMN [IF EXISTS] name TYPE [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST]

可以更改列的属性：

• 类型
• 默认值
• 压缩类型
• TTL

更改列类型是一个复杂的操作，它会更改包含数据的文件内容。对于大表，可能需要很长时间。
示例：

# 将uk_price_paid表的is_new列类型从UInt8改为String
ALTER TABLE uk_price_paid
    modify COLUMN is_new String;

分区操作

查询分区信息

ClickHouse内置了许多system系统表，用于查询自身的状态信息。其中 parts系统表专门用于查询数据表的分区信息。例如执行下面的语句，就能够得 到数据表partition_v2的分区状况:

SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2'

卸载分区

ALTER TABLE table_name [ON CLUSTER cluster] DETACH PARTITION|PART partition_expr

示例

ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0';

删除分区

ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr

示例

ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0';

删除已卸载的分区

ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART partition_expr

装载分区

ALTER TABLE table_name [ON CLUSTER cluster] ATTACH PARTITION|PART partition_expr

示例

ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;

分区备份

ALTER TABLE table_name [ON CLUSTER cluster] FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']

附录

相关参考文档：

Clickhouse Docs