【clickhouse】常用查询语句

正在执行SQL

select query_id, user, address, query  FROM system.processes ORDER BY query_id;



SELECT query_id, query,elapsed  FROM system.processes order by elapsed desc limit 20 FORMAT Vertical;


select * FROM system.merges ;

查询存储

查询存储Top10表，可以用来数据治理

SELECT
    database,
    table,
    ((sum(bytes_on_disk) / 1024) / 1024) / 1024 AS size_gb
FROM system.parts
GROUP BY
    database,
    table
ORDER BY size_gb DESC
LIMIT 10

获取节点(不是分片)存储使用情况

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) AS `压缩率`
FROM system.parts

SELECT 
dt, database, `table`, storage
FROM 
(SELECt toYYYYMMDD(modification_time) as dt, database, `table` ,sum(data_compressed_bytes) as storage
FROM `system`.parts 
WHERE 
modification_time >'2022-10-01' and active=1
GROUP BY toYYYYMMDD(modification_time), database,`table` 
order BY toYYYYMMDD(modification_time), database,`table` )
ORDER BY dt, storage desc

查询CPU

SELECT query_start_time, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000000 FROM system.query_log WHERE arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000000 > 10 AND query_start_time >= '2022-09-20 15:50:41' AND query_start_time <= '2022-09-20 16:40:41' and event_date = '2022-09-20' ORDER BY arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000000 DESC limit 20 FORMAT Vertical;




SELECT query_start_time, query_duration_ms, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000, query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 FROM system.query_log WHERE query_start_time >= '2022-10-19 02:20:00' AND query_start_time <= '2022-10-19 02:40:00' and event_date = '2022-10-19'  ORDER BY query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 DESC limit 20 FORMAT Vertical;

SELECT time, countIf(query_duration_ms>60000), count(1) FROM  (SELECT formatDateTime(query_start_time, '%R') as time , query_start_time, query_duration_ms, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000, query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 as diff FROM system.query_log WHERE query_start_time >= '2022-10-19 02:20:00' AND query_start_time <= '2022-10-19 02:40:00' and event_date = '2022-10-19'  ) group by time order by time 

SELECT time, countIf(query_duration_ms>60000), count(1) FROM  (SELECT formatDateTime(query_start_time, '%R') as time , query_start_time, query_duration_ms, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000, query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 as diff FROM system.query_log WHERE query_start_time >= '2022-10-19 02:20:00' AND query_start_time <= '2022-10-19 02:40:00' and event_date = '2022-10-19'  and query_kind='Insert'  ) group by time order by time 

SELECT time, countIf(query_duration_ms>60000), count(1) FROM  (SELECT formatDateTime(query_start_time, '%R') as time , query_start_time, query_duration_ms, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000, query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 as diff FROM system.query_log WHERE query_start_time >= '2022-10-19 02:20:00' AND query_start_time <= '2022-10-19 02:40:00' and event_date = '2022-10-19' and query_kind!='Insert'  ) group by time order by time 

SELECT time, countIf(query_duration_ms>60000),countIf(cpuTime>60000), count(1) FROM  (SELECT formatDateTime(query_start_time, '%R') as time , query_start_time, query_duration_ms, query, memory_usage, ProfileEvents.Names, ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds'), arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 as cpuTime, query_duration_ms - arrayElement(ProfileEvents.Values, indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')) / 1000 as diff FROM system.query_log WHERE query_start_time >= '2022-10-19 02:20:00' AND query_start_time <= '2022-10-19 02:40:00' and event_date = '2022-10-19'  ) group by time order by time 

查询insert 语句

SELECT
	tables,
	count(1)
FROM
	system.query_log
WHERE
	query_start_time >= '2022-10-22 18:00:41'
	AND query_start_time <= '2022-10-22 18:59:41'
	and event_date = '2022-10-22'
	AND query_kind ='Insert'
GROUP BY
	tables 
ORDER BY 
	count(1) desc 

查询是否有阻塞

select * from clusterAllReplicas(default,system.mutations）where is_done=0

排查小文件

SELECT
    table,
    database,
    toStartOfMinute(event_time),
    count() / 60 AS count
FROM system.part_log_all
WHERE ((event_time >= '2023-05-15 08:30:00') AND (event_time <= '2023-05-15 08:40:00')) AND (event_type = 1) AND (database = 'dddd')
GROUP BY
    table,
    database,
    toStartOfMinute(event_time)
ORDER BY count DESC