Clickhouse useful queries

clickhouse_useful_queries.sql

select table,
       formatReadableSize(sum(bytes)) as size,
       min(min_date)                  as min_date,
       max(max_date)                  as max_date
from system.parts
where active
group by table;

select table, formatReadableSize(size) as size, rows, days, formatReadableSize(avgDaySize) as avgDaySize
from (select table,
             sum(bytes)                   AS size,
             sum(rows)                    AS rows,
             min(min_date)                AS min_date,
             max(max_date)                AS max_date,
             (max_date - min_date)        AS days,
             size / (max_date - min_date) AS avgDaySize
      from system.parts
      where active
      group by table
      order by rows desc);

select concat(database, '.', table)                         as table,
       formatReadableSize(sum(bytes))                       as size,
       sum(rows)                                            as rows,
       max(modification_time)                               as latest_modification,
       sum(bytes)                                           as bytes_size,
       any(engine)                                          as engine,
       formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size
from system.parts
where active
group by database, table
order by bytes_size desc;

select parts.*,
       columns.compressed_size,
       columns.uncompressed_size,
       columns.compression_ratio,
       columns.compression_percentage
from (select table,
             formatReadableSize(sum(data_uncompressed_bytes))                                    AS uncompressed_size,
             formatReadableSize(sum(data_compressed_bytes))                                      AS compressed_size,
             round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3)                 AS compression_ratio,
             round((100 - (sum(data_compressed_bytes) * 100) / sum(data_uncompressed_bytes)), 3) AS compression_percentage

      from system.columns
      group by table) columns
     right join (select table,
                        sum(rows)                                            as rows,
                        max(modification_time)                               as latest_modification,
                        formatReadableSize(sum(bytes))                       as disk_size,
                        formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
                        any(engine)                                          as engine,
                        sum(bytes)                                           as bytes_size
                 from system.parts
                 where active
                 group by database, table) parts on columns.table = parts.table
order by parts.bytes_size desc;