ClickHouse 日常运维

 
## 数据库操作
创建数据库语句
CREATE DATABASE IF NOT EXISTS db_name [ ENGINE = engine_name ];

数据库支持的引擎：
Ordinary  默认的引擎，此类数据库下面的表可以是任意类型引擎。
Dictionary  字典引擎，此类数据库会自动为所有数据字典创建它们的数据表。
Memory  内存引擎，用于存放临时数据，此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务器重启后数据会被清除。
Lazy  日志引擎，此类数据库下只能使用Log系列的表引擎。
MySQL  MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。


创建默认引擎的数据库
CREATE DATABASE db_test;


查询所有数据库
SHOW DATABASES;


切换数据库
USE  db_test;


删除数据库
DROP DATABASE db_test;


## 表操作
查看表的定义
SHOW CREATE TABLE test034\G


只是复制表结构
CREATE TABLE [ IF NOT EXISTS ] [ db_name1.]table_name1 AS [ db_name2.]table_name2 [ ENGINE = engine_name ];


复制表结构和数据
CREATE TABLE [ IF NOT EXISTS ] [ db_name1.]table_name1  ENGINE = engine_name   AS  SELECT * FROM [ db_name2.]table_name2 ;


删除表
DROP TABLE [ IF EXISTS ] [ db_name.]table_name;


新增列
ALTER TABLE db.test  ADD COLUMN  name String DEFAULT 'MAC' AFTER ID;


修改列
ALTER TABLE db.test  MODIFY COLUMN  name String DEFAULT 'MAC' AFTER ID;


修改列的备注
ALTER TABLE db.test COMMENT COLUMN name '姓名';


删除列
ALTER TABLE db.test DROP COLUMN name;


清空表
TRUNCATE TABLE db.test;


删除数据
ALTER TABLE db.test DELETE WHERE condition!=3;


修改数据
ALTER TABLE db.test UPDATE name='ccc' WHERE createTime='2020/2/13';


将TABLE1重命名为TABLE2
rename 支持*MergeTree和Distributed
rename table TABLE1 to TABLE2;


## 分布式DDL执行
ClickHouse 集群模式中，CREATE、ALTER、DROP、RENAME、TRUNCATE语句都支持分布式DDL执行。
将一条普通DDL转换成分布式DDL，只需加上 ON CLUSTER cluster_name 声明即可。
例如：
ALTER table my_test.table_name_all on cluster cluster_name MODIFY COLUMN id Int64 ;


## 分区操作
删除分区 
ALTER TABLE db.test DROP PARTITION 202007;


复制分区数据
需要满足的条件：1.两张表需要拥有相同的分区键。2.两张表的表结构完全相同。
将test1 表的分区数据复制到test2表
ALTER TABLE test2 REPLACE PARTITION 201908 FROM test1;


重置分区，重置为初始值
ALTER TABLE test CLEAR COLUMN name IN PARTITION 202007;


卸载分区 
ALTER TABLE test DETACH  PARTITION 202007;
 
 
装载分区
ALTER TABLE test ATTACH  PARTITION 202007;


## 常用状态查询
查看正在执行的sql
SHOW PROCESSLIST;
或者
select * from system.processes


杀掉SQL
KILL MUTATION mutation_id = 'trx_id';


当前连接数(分为 TCP 和 HTTP )
SELECT *
FROM system.metrics
WHERE metric LIKE '%Connection'


查看集群分布式信息
select * from system.clusters;


当前正在执行的查询
SELECT 
    query_id, 
    user, 
    address, 
    query
FROM system.processes
ORDER BY query_id ASC;


查询Mutation操作(ALTER DELETE 和 ALTER UPDATE)
SELECT 
    database, 
    table, 
    mutation_id, 
    command, 
    create_time, 
    is_done
FROM system.mutations;


终止语句
KILL QUERY WHERE query_id = 'query_id';
KILL MUTATION WHERE mutation_id = 'mutation_id';


存储空间统计,查询 CH 各个存储路径的空间
SELECT 
    name, 
    path, 
    formatReadableSize(free_space) AS free, 
    formatReadableSize(total_space) AS total, 
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks


各数据库占用空间统计
SELECT 
    database, 
    formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
GROUP BY database;



各个列字段占用空间统计
每个列字段的压缩大小、压缩比率以及该列的每行数据大小的占比
SELECT 
    database, 
    table, 
    column, 
    any(type), 
    sum(column_data_compressed_bytes) AS compressed, 
    sum(column_data_uncompressed_bytes) AS uncompressed, 
    round(uncompressed / compressed, 2) AS ratio, 
    compressed / sum(rows) AS bpr, 
    sum(rows)
FROM system.parts_columns
WHERE active AND database != 'system'
GROUP BY 
    database, 
    table, 
    column
ORDER BY 
    database ASC, 
    table ASC, 
    column ASC;
 
 
慢查询
SELECT 
    user, 
    client_hostname AS host, 
    client_name AS client, 
    formatDateTime(query_start_time, '%T') AS started, 
    query_duration_ms / 1000 AS sec, 
    round(memory_usage / 1048576) AS MEM_MB, 
    result_rows AS RES_CNT, 
    result_bytes / 1048576 AS RES_MB, 
    read_rows AS R_CNT, 
    round(read_bytes / 1048576) AS R_MB, 
    written_rows AS W_CNT, 
    round(written_bytes / 1048576) AS W_MB, 
    query
FROM system.query_log
WHERE type = 2
ORDER BY query_duration_ms DESC
LIMIT 10;


副本预警监控
通过下面的 SQL 语句对副本进行预警监控，其中各个预警的变量可以根据自身情况调整。
SELECT database, table, is_leader, total_replicas, active_replicas 
  FROM system.replicas 
WHERE is_readonly 
OR is_session_expired 
OR future_parts > 30 
OR parts_to_check > 20 
OR queue_size > 30 
OR inserts_in_queue > 20 
OR log_max_index - log_pointer > 20 
OR total_replicas < 2 
OR active_replicas < total_replicas;