Clickhouse DBA常用的sql语句

clickhouse系统表官方文档https://clickhouse.com/docs/en/operations/system-tables

Clickhouse的语法有点像Mysql

Clickhouse没有delete和update语句，使用alter来替代
https://clickhouse.com/docs/zh/sql-reference/statements/alter/delete
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

https://clickhouse.com/docs/zh/sql-reference/statements/alter/update
ALTER TABLE [db.]table UPDATE column1 = expr1 [, …] WHERE filter_expr

查看集群
show clusters;
或
select cluster,shard_num,shard_weight,replica_num,host_name,host_address from system.clusters;

查看数据库的列表
show databases;

查看数据库对应的磁盘路径
select name,engine,data_path,metadata_path from system.databases;

查看当前数据库下的表的列表
show tables;

查看XX数据库里面的表对应的磁盘路径
select database,name,engine,data_paths,metadata_path from system.tables where database=‘XX’;

查看XX数据库里面所有表对应的引擎
SELECT database, name, engine FROM system.tables WHERE database = ‘XX’

查看XX数据库里面分布式表对应的本地的表名
select name,engine,dependencies_database,dependencies_table from system.tables where database=‘XX’;

查询XX数据库里面里面各表的建表语句
SELECT database, name, create_table_query FROM system.tables WHERE database = ‘XX’

查询XX数据库里面里面各表的总行数
select database,name,engine,total_rows from system.tables where database=‘XX’ order by 4;

查询clickhouse用户名，用default用户执行以下语句，必须先在文件/etc/clickhouse-server/users.xml中对default用户启用<access_management>1</access_management>
SHOW USERS;
或
select * from system.users;

查询某表在zookeeper复制中的信息

DAILACHDBUD001 :) select database,table,zookeeper_path,replica_name,replica_path from system.replicas where table='table_ReplicatedMergeTree1';
┌─database───┬─table──────────────────────┬─zookeeper_path─────────────────────────────────────────────┬─replica_name───┬─replica_path───────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_ReplicatedMergeTree1 │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01 │ DAILACHDBUD001 │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01/replicas/DAILACHDBUD001 │
└────────────┴────────────────────────────┴────────────────────────────────────────────────────────────┴────────────────┴────────────────────────────────────────────────────────────────────────────────────┘

查询某个zookeeper_path对应的信息

DAILACHDBUD001 :) select top 1 * from system.zookeeper where path='/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01';
┌─name────────────────────┬─value─┬─path───────────────────────────────────────────────────────┐
│ alter_partition_version │       │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01 │
└─────────────────────────┴───────┴────────────────────────────────────────────────────────────┘

在zookeeper实例中删除某个zookeeper_path的信息
[zk: DAILACHDBUD002:2181(CONNECTED) 0] deleteall ‘/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01’;

查询clickhouse数据库当前运行状态下的指标信息
select * from system.metrics；

统计ClickHouse服务在运行过程中已经执行过的指标信息
select * from system.events

统计ClickHouse服务运行过程时当前正在后台异步运行的指标信息
select * from system.asynchronous_metrics

查询日志
select * from system.query_log

查询磁盘信息
select name,path,type from system.disks;

查询备份信息
SELECT id,name,status,error,start_time,end_time FROM system.backups