CLICKHOUSE — различия между версиями

Материал из megapuper
Перейти к: навигация, поиск
Строка 32: Строка 32:
  
 
Размер таблиц в базе
 
Размер таблиц в базе
  SELECT parts.*,
+
  select parts.*,
 
         columns.compressed_size,
 
         columns.compressed_size,
 
         columns.uncompressed_size,
 
         columns.uncompressed_size,
 
         columns.ratio
 
         columns.ratio
  FROM (
+
  from (
           SELECT table,
+
           select table,
 
                 formatReadableSize(sum(data_uncompressed_bytes))          AS uncompressed_size,
 
                 formatReadableSize(sum(data_uncompressed_bytes))          AS uncompressed_size,
 
                 formatReadableSize(sum(data_compressed_bytes))            AS compressed_size,
 
                 formatReadableSize(sum(data_compressed_bytes))            AS compressed_size,
Строка 46: Строка 46:
 
           ) columns
 
           ) columns
 
           right join (
 
           right join (
SELECT table,
+
    select table,
          sum(rows)                                            as rows,
+
            sum(rows)                                            as rows,
          max(modification_time)                              as latest_modification,
+
            max(modification_time)                              as latest_modification,
          formatReadableSize(sum(bytes))                      as disk_size,
+
            formatReadableSize(sum(bytes))                      as disk_size,
          formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
+
            formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
          any(engine)                                          as engine,
+
            any(engine)                                          as engine,
          sum(bytes)                                          as bytes_size
+
            sum(bytes)                                          as bytes_size
     FROM system.parts
+
     from system.parts
     WHERE active and database = currentDatabase()
+
     where active and database = currentDatabase()
     GROUP BY database, table
+
     group by database, table
 
     ) parts on columns.table = parts.table
 
     ) parts on columns.table = parts.table
  ORDER BY parts.bytes_size desc;
+
  order by parts.bytes_size desc;
  
  

Версия 14:24, 21 апреля 2022

SHOW DATABASES - выводит список всех баз данных
SHOW TABLES - выводит список таблиц выбранной базы
SHOW PROCESSLIST - выводит список запросов выполняющихся в выбранной базе
SHOW GRANTS - выводит список привилегий пользователя
DESCRIBE TABLE stat - вывести описание столбцов таблицы


Права админа

https://stackoverflow.com/questions/64166492/how-to-setup-an-admin-account-for-clickhouse


Бекап таблицы в Native формате

Dump of metadata
clickhouse-client -h 127.0.0.1 --database=DATABASE --query="SHOW CREATE TABLE TABLE" --format=TabSeparatedRaw > meta.sql

Dump of data
clickhouse-client -h 127.0.0.1 --query="SELECT * FROM DATABASE.TABLE FORMAT Native" > dump.native 
 
Restore of metadata
clickhouse-client --user USER --ask-password < meta.sql

Restore of data
clickhouse-client --user USER --ask-password --query="INSERT INTO DATABASE.TABLE FORMAT Native" < dump.native

https://stackoverflow.com/questions/57005443/clickhouse-how-to-take-incremental-backup-of-clickhouse-db-for-both-partitoned


SELECT query_id,user,elapsed FROM system.processes; - выборка по id, пользователю и времени выполнения
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90' - принудительно останавливает все запросы с указанным query_id
KILL QUERY WHERE elapsed > 100 - принудительно останавливает все запросы с временем выполнения больше 100


Размер таблиц в базе

select parts.*,
       columns.compressed_size,
       columns.uncompressed_size,
       columns.ratio
from (
         select table,
                formatReadableSize(sum(data_uncompressed_bytes))          AS uncompressed_size,
                formatReadableSize(sum(data_compressed_bytes))            AS compressed_size,
                sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS ratio
         from system.columns
         where database = currentDatabase()
         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 and database = currentDatabase()
    group by database, table
    ) parts on columns.table = parts.table
order by parts.bytes_size desc;


Размеры партиции в базе по месяцам, отсортированные по годам/месяцам

select
  toYear(max_date) year,
  toMonth(max_date) month,
  table,
  sum(rows) as rows,
  max(modification_time) as latest_modification,
  formatReadableSize(sum(bytes_on_disk)) as disk_size,
  formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
  formatReadableSize(sum(data_compressed_bytes)) as compressed_size,
  formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed_size,
       any(engine) as engine
from
  system.parts
where
  active
  and database='buymedia_biz'
  and table='statistic'
group by
  table, year, month
order by
  year, month;


Размер партиций всех таблиц за промежуток времени

select
  database,
  table,
  sum(rows) as rows,
  max(modification_time) as latest_modification,
  formatReadableSize(sum(bytes_on_disk)) as disk_size,
  formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
  formatReadableSize(sum(data_compressed_bytes)) as compressed_size,
  formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed_size,
  any(engine) as engine
from system.parts
where active and max_date between '2021-01-01' and '2021-12-31'
group by
  database,
  table;


clickhouse-client -h 127.0.0.1 --query="SELECT * FROM pushads_biz.stat where datetime between '2021-03-01' and '2021-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/pushads_biz--stat--202103.native.gz


https://clickhouse.com/docs/ru/sql-reference/
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015