ClickHouse Queries

compression_ratio.sql

SELECT table,
       name,
       formatReadableSize(c) compressed,
       formatReadableSize(u) uncompressed,
       floor((c/u) * 100, 4) percent
FROM (
       SELECT table,
              name,
              sum(data_compressed_bytes) c,
              sum(data_uncompressed_bytes) u
       FROM system.columns
       WHERE database = currentDatabase()
       GROUP BY table, name
       ORDER BY table ASC, name ASC
)

cpu_user_time_1m.sql

SELECT toStartOfMinute(event_time) AS time, 
       sum(ProfileEvent_UserTimeMicroseconds) AS user_time, 
       bar(user_time, 0, 60000000, 80) AS bar 
  FROM system.metric_log 
 WHERE event_date = today() 
 GROUP BY time ORDER BY time

database_disk_space_usage_summary.sql

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

disk_space_usage_detail.sql

SELECT database, table, partition, name part_name, active, formatReadableSize(bytes_on_disk) bytes_on_disk
FROM system.parts
ORDER BY database, table, partition, name

filesystem_stats.sql

SELECT
  formatReadableSize(filesystemCapacity()) capacity,
  formatReadableSize(filesystemFree()) free,
  formatReadableSize(filesystemCapacity() - filesystemFree()) used,
  round(((filesystemCapacity() - filesystemFree()) / filesystemCapacity())*100, 2) `%used`,
  bar(((filesystemCapacity() - filesystemFree()) / filesystemCapacity())*100, 0, 100, 20) `used_bar`;

                     
SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks;

longest_running_queries.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, 
    toDecimal32(result_bytes / 1048576, 6) 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

table_disk_space_usage_summary.sql

SELECT table, formatReadableSize(sum(bytes_on_disk)) table_size
FROM system.parts
WHERE database=currentDatabase()
GROUP BY table