clickhouse常用sql

1、慢查询监控

SELECT
    user,
    formatDateTime(query_start_time, '%Y%m%d %T') AS start_time,
    query_duration_ms / 1000 AS query_duration_s,
     query,
    memory_usage / 1024 / 1024 / 1024 AS memory_usage_g,
    result_rows ,
    formatReadableSize(result_bytes) AS result_bytes,
    read_rows ,
    read_bytes / 1024 / 1024 /1024 AS read_bytes_g,
    written_rows ,
    written_bytes / 1024 / 1024 /1024 AS written_bytes_g
   
FROM system.query_log
WHERE type = 2
and query_start_time>=today()
ORDER BY query_duration_s DESC
LIMIT 10

2、清空查询日志

ALTER table system.query_log delete WHERE 1=1;

3、查看数据压缩率

select
    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) "压缩率"
from system.parts;

4、查看表大小

SELECT
	table,
	formatReadableSize(sum(data_compressed_bytes)) AS compressed_size ,
	formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_bytes
FROM
	system.parts
WHERE
	active
	AND (table LIKE 'data_%')
	GROUP BY table
order by
	uncompressed_bytes desc ;

5、 查看当前连接数

SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

6、手动合并分区

OPTIMIZE TABLE table [PARTITION partition] [FINAL]

7、添加或者更新TTL

ALTER TABLE table1 MODIFY TTL toDate(time) + toIntervalDay(10)

8、添加多个列

ALTER TABLE table1 ADD COLUMN longcol1 Int64 AFTER col111, ADD COLUMN longcol2 Int64 AFTER longcol1;

9、卸载装载分区

ALTER TABLE table1 DETACH PARTITION '1629111600';
ALTER TABLE table1 ATTACH PARTITION '1629111600';

10、查看表资源占用情况

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 ASC

11、个列字段占用空间统计

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

12、查看后台执行的更新语句

SELECT
	database,
	table,
	mutation_id,
	command,
	create_time,
	parts_to_do_names,
	parts_to_do,
	latest_fail_reason
FROM
	system.mutations
where
	is_done <> 1

13、查看正在执行的查询语句

SELECT
	query_id,
	user,
	address,
	elapsed,
	query
FROM
	system.processes
ORDER BY
	query_id ASC

14、kill指定的查询语句

KILL QUERY WHERE query_id='query_id';

15、查看集群分布式信息

select * from system.clusters;

16、改名

rename table TABLE1 to TABLE2;

17、更新数据

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

18、系统事件、查询总次数、insert次数、失败次数

select * from system.events

19、查看建表ddl

SHOW CREATE TABLE table1 

20、查看当前查询执行列表

show processlist;

21、压缩率

select
    database as `数据库`,
    table as `表名`,
    formatReadableSize(size) as `所占磁盘大小`,
    formatReadableSize(data_uncompressed_bytes)  as `原始大小`,
    formatReadableSize(data_compressed_bytes)  as `压缩大小`,
    compress_rate as `压缩率`,
    rows as `行数`,
    days as `存在天数`,
    formatReadableSize(avgDaySize) as `平均每天的大小`
from
(
    select
        database,
        table,
        sum(bytes) as size,
        sum(rows) as rows,
        min(min_date) as min_date,
        max(max_date) as max_date,
        sum(data_uncompressed_bytes) as data_uncompressed_bytes,
        sum(data_compressed_bytes) as data_compressed_bytes,
        (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
        max_date - min_date as days,
        size / (max_date - min_date) as avgDaySize
    from system.parts
    where active 
     and database = 'ds'
     and table = 'table'
    group by
        database,
        table
)