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

Материал из megapuper
Перейти к: навигация, поиск
 
(не показано 45 промежуточных версий этого же участника)
Строка 6: Строка 6:
 
  DESCRIBE TABLE stat - вывести описание столбцов таблицы
 
  DESCRIBE TABLE stat - вывести описание столбцов таблицы
  
 +
SELECT * FROM system.parts - все партиции для всех таблиц
 +
SELECT partition, name, table FROM system.parts WHERE active - более лаконичная форма запроса исключающая неактивные части
  
−
Бекап в Native формате
+
 
−
  clickhouse-client --query="SELECT * FROM table FORMAT Native" > table.native
+
Права админа
−
Native is the most efficient format
+
https://stackoverflow.com/questions/64166492/how-to-setup-an-admin-account-for-clickhouse
−
CSV, TabSeparated, JSONEachRow are more portable: you may import/export data to another DBMS.
+
 
 +
 
 +
Бекап таблицы в Native формате
 +
Dump of metadata
 +
  clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --database=<font color=blue>DATABASE</font> --query="SHOW CREATE TABLE <font color=blue>TABLE</font>" --format=TabSeparatedRaw > meta.sql
 
   
 
   
−
  Dump of metadata:
+
  Dump of data
−
  clickhouse-client --query="SHOW CREATE TABLE table" --format=TabSeparatedRaw > table.sql
+
  clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --query="SELECT * FROM <font color=blue>DATABASE.TABLE</font> FORMAT Native" > dump.native
 +
 
 +
Restore of metadata
 +
clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password < meta.sql
 
   
 
   
−
  Restore of metadata:
+
  Restore of data
−
  clickhouse-client < table.sql
+
clickhouse-client -h 127.0.0.1 -u <font color=blue>USER</font> --ask-password --query="INSERT INTO <font color=blue>DATABASE.TABLE</font> 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
 +
 
 +
 
 +
Удаление партиции.<br>
 +
Сначала находим нужную партицию
 +
SELECT * FROM system.parts LIMIT <font color=blue>1</font> FORMAT Vertical;
 +
 
 +
 
 +
Перемещаем заданную партицию в директорию detached. Сервер не будет знать об этой партиции до тех пор, пока не будет выполнен запрос ATTACH.
 +
  ALTER TABLE <font color=blue>DATABASE.TABLE</font> DETACH PARTITION '<font color=blue>PARTITION</font>'
 +
 
 +
 
 +
Удаляем партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут(SETTINGS allow_drop_detached=1 если в конфиге запрещено удалять)
 +
ALTER TABLE <font color=blue>DATABASE.TABLE</font> DROP PARTITION '<font color=blue>PARTITION</font>'
 +
 
 +
 
 +
Удаляет из detached кусок или все куски, принадлежащие партиции.
 +
ALTER TABLE <font color=blue>DATABASE.TABLE</font> DROP DETACHED PARTITION '<font color=blue>PARTITION</font>' SETTINGS allow_drop_detached=1;
 +
https://stackoverflow.com/questions/64382732/can-i-delete-the-detached-folder-on-clickhouse-data<br>
 +
https://stackoverflow.com/questions/61950278/how-to-enable-allow-drop-detached-in-system-settings
 +
 
 +
 
 +
Размер таблиц в базе
 +
USE <font color=blue>DATABASE</font>
 
   
 
   
−
  Restore of data:
+
  SELECT
−
  clickhouse-client --query="INSERT INTO table FORMAT Native" < table.native
+
    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
 +
) AS 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
 +
) AS parts ON columns.table = parts.table
 +
ORDER BY parts.bytes_size DESC
  
  
−
  KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90' - принудительно останавливает все запросы с указанным query_id
+
Размеры партиций в базе по месяцам, отсортированные по годам/месяцам
 +
SELECT
 +
    toYear(max_date) AS year,
 +
    toMonth(max_date) AS 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 ASC,
 +
    month ASC
 +
 
 +
 
 +
Размер партиций всех баз.таблиц за промежуток времени
 +
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 >= '2021-01-01') AND (max_date <= '2021-12-31'))
 +
GROUP BY
 +
    database,
 +
    table
 +
 
  
 +
clickhouse-client -h 127.0.0.1 -u root --ask-password --query="SELECT * FROM buymedia_biz.statistic where event_date between '2023-03-01' and '2023-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/buymedia_biz--statistic--202303.native.gz
  
−
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;
 
  
 
https://clickhouse.com/docs/ru/sql-reference/<br>
 
https://clickhouse.com/docs/ru/sql-reference/<br>
−
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0
+
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015<br>
−
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015
+
 
 +
Backup<br>
 +
https://stupin.su/wiki/clickhouse_move_db/<br>
 +
https://habr.com/ru/post/569282/<br>
 +
https://it-lux.ru/clickhouse-backup-and-recovery/<br>
 +
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0<br>
 +
 
 +
MergeTree<br>
 +
https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/mergetree<br>
 +
https://programming.vip/docs/clickhouse-multi-disk-storage.html<br>
 +
https://russianblogs.com/article/87522205920/

Текущая версия на 11:15, 25 декабря 2023

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

DESCRIBE TABLE stat - вывести описание столбцов таблицы

SELECT * FROM system.parts - все партиции для всех таблиц
SELECT partition, name, table FROM system.parts WHERE active - более лаконичная форма запроса исключающая неактивные части


Права админа 

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


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

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

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

Restore of data
clickhouse-client -h 127.0.0.1 -u 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 * FROM system.parts LIMIT 1 FORMAT Vertical;


Перемещаем заданную партицию в директорию detached. Сервер не будет знать об этой партиции до тех пор, пока не будет выполнен запрос ATTACH. 

ALTER TABLE DATABASE.TABLE DETACH PARTITION 'PARTITION'


Удаляем партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут(SETTINGS allow_drop_detached=1 если в конфиге запрещено удалять) 

ALTER TABLE DATABASE.TABLE DROP PARTITION 'PARTITION'


Удаляет из detached кусок или все куски, принадлежащие партиции. 

ALTER TABLE DATABASE.TABLE DROP DETACHED PARTITION 'PARTITION' SETTINGS allow_drop_detached=1;

https://stackoverflow.com/questions/64382732/can-i-delete-the-detached-folder-on-clickhouse-data
https://stackoverflow.com/questions/61950278/how-to-enable-allow-drop-detached-in-system-settings


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

USE DATABASE

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
) AS 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
) AS parts ON columns.table = parts.table
ORDER BY parts.bytes_size DESC


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

SELECT
   toYear(max_date) AS year,
   toMonth(max_date) AS 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 ASC,
   month ASC


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

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 >= '2021-01-01') AND (max_date <= '2021-12-31'))
GROUP BY
   database,
   table



clickhouse-client -h 127.0.0.1 -u root --ask-password --query="SELECT * FROM buymedia_biz.statistic where event_date between '2023-03-01' and '2023-03-31' FORMAT Native" | pigz > /srv/storage/01-r1tb4/buymedia_biz--statistic--202303.native.gz


https://clickhouse.com/docs/ru/sql-reference/
https://gist.github.com/sanchezzzhak/511fd140e8809857f8f1d84ddb937015

Backup
https://stupin.su/wiki/clickhouse_move_db/
https://habr.com/ru/post/569282/
https://it-lux.ru/clickhouse-backup-and-recovery/
https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0

MergeTree
https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/mergetree
https://programming.vip/docs/clickhouse-multi-disk-storage.html
https://russianblogs.com/article/87522205920/

Источник — «http://megapuper.ru/index.php?title=CLICKHOUSE&oldid=159898»