Clickhouse useful queries

clickhouse_useful_queries.sql

-- Most heavy queries:
select type,
       event_time,
       initial_query_id,
       query_id,
       formatReadableSize(memory_usage) as memory,
       used_functions,
       used_aggregate_functions,
       current_database,
       result_rows,
       query_duration_ms,
       query
from system.query_log o
where type = 'QueryFinish'
order by memory_usage desc
limit 20;

------------------------

select name,
       formatReadableSize(sum(data_compressed_bytes))                      as compressed_size,
       formatReadableSize(sum(data_uncompressed_bytes))                    as uncompressed_size,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) as ratio
from system.columns
where table = 'deals'
group by name
order by sum(data_compressed_bytes) desc
-----------------------------------------------------------------------------------------------------------------------
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;

-----------------------------------------------------------------------------------------------------------------------
-- set short TTL for system metric tables:
alter table system.session_log modify ttl event_date + interval 1 day;
alter table system.asynchronous_metric_log modify ttl event_date + interval 1 day;
alter table system.latency_log modify ttl event_date + interval 1 day;
alter table system.query_log modify ttl event_date + interval 1 day;
alter table system.trace_log modify ttl event_date + interval 1 day;
alter table system.error_log modify ttl event_date + interval 1 day;
alter table system.text_log modify ttl event_date + interval 1 day;
alter table system.metric_log modify ttl event_date + interval 1 day;
alter table system.part_log modify ttl event_date + interval 1 day;
alter table system.processors_profile_log modify ttl event_date + interval 1 day;