clickhouse 系统表 --查看连接和进程

ClickHouse自带用于记录系统信息的系统库system，通过processes表，我们可以查看当前连接的进程信息，也就是正在运行的sql的信息。

SELECT
query_id,
read_rows,
total_rows_approx,
memory_usage,
initial_user,
initial_address,
elapsed,
query,
client_hostname
FROM system.processes;

 

# 字段含义
# query_id 查询id，
# read_rows 从表中读取的行数，
# total_rows_approx 应读取的行总数的近似值，
# memory_usage 请求使用的内存量
# initial_user 进行查询的用户
# initial_address 请求的 IP 地址
# elapsed 求执行开始以来的秒数
# query 查询语句

 

1：查询当前clickhouse的查看当前实时连接数

 select * from metrics where metric like '%Connection'

 

2.杀死进程

KILL QUERY WHERE query_id='e9395abd-9367-4796-a6ec-a4e8a639aaea';

 

3：查看各个数据库占用空间统计

 SELECT database, formatReadableSize(sum(bytes_on_disk)) AS on_disk FROM system.parts GROUP BY database

 

4：查询执行完的日志，这个可以看每个执行的sql的耗时，客户端是什么等。

 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) AND (query LIKE '%insert%') ORDER BY query_duration_ms DESC LIMIT 10 

 

5：查询当前库表资源占用情况：

 select database, table, sum(rows) AS "总行数", formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小", formatReadableSize(sum(data_compressed_bytes)) AS "压缩大小", round( ( sum(data_compressed_bytes) / sum(data_uncompressed_bytes) ) * 100., 2 ) AS "压缩率/%" from system.parts group by database, table order by database 

 

6: 查看数据表分区信息

SELECT 
    partition AS `分区`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE (database IN ('default')) AND (table IN ('temp_1')) AND (partition LIKE '2019-12-%')
GROUP BY partition
ORDER BY partition ASC

 

7: 查看表容量 行数 压缩率

 

SELECT
table AS `表名`,
sum(rows) AS `总行数`,
formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE table IN ('temp_1')
GROUP BY table

 

8 查看表数据信息

SELECT 
    column AS `字段名`,
    any(type) AS `类型`,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,
    sum(rows) AS `行数`
FROM system.parts_columns
WHERE (database = 'default') AND (table = 'temp_1')
GROUP BY column
ORDER BY column ASC