clickhouse系統(tǒng)表日志清理方式詳解

更新時間：2023年02月15日 09:01:11 作者：愛吃紅薯粉

這篇文章主要為大家介紹了clickhouse系統(tǒng)表日志清理方式詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

簡介

clickhouse會將查詢日志，度量日志和堆棧采集日志記錄下來，存儲到自身數據庫的system庫中，分別是query_log,query_thread_log,trace_log,metric_log等5個表，如果長時間不清查，該表數據會一直累積。

清理

清理方式

清理這些日志表有多種方式

第1種是設置表的TTL，clickhouse是支持設置表的TTL，不管是系統(tǒng)表還是業(yè)務表。

第2種是使用DDL語句+DML語句刪除指定范圍內數據

第3種配置文件設置，在config.xml中提供了日志表的配置

字段釋義:

database – 數據庫名
table – 日志存放的系統(tǒng)表名
partition_by — 系統(tǒng)表分區(qū)鍵，如果定義了 engine 則不能使用
engine -系統(tǒng)表表引擎，如果定義了 partition_by 則不能使用
flush_interval_milliseconds – 將數據從內存的緩沖區(qū)刷新到表的時間間隔。
ttl 保存時間

圖中沒有ttl的配置，可以自行添加, event_date是表中的時間字段,格式是: 2020-01-01

<!--
            Table TTL specification: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl
            Example:
                event_date + INTERVAL 1 WEEK
                event_date + INTERVAL 7 DAY DELETE
                event_date + INTERVAL 2 WEEK TO DISK 'bbb'
-->
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>

實際操作

方案選擇

第3種改配置文件方案適合在集群搭建的時候，后期再改就需要重啟服務，因服務不能隨意重啟，需要配置維護期間操作，所以不具靈活性，排除掉了。

第1種設置TTL的方案也是可以的，但是現在不合適，因為已經累積了很長時間的數據，數據量比較大，直接設置會消耗比較多的資源

所以會使用第2種+第1種組合方案，先分批刪除歷史的日志，然后再使用第1種方案設置TTL。這樣后期就不需要維護了

刪除數據

使用DDL+DML語句刪除指定日期內的數據

先用小一點的表操作一下，通常trace_log比較小，query_log表比較大

use system;
#統(tǒng)計一下2020年有多少條數據
select count(*) from trace_log where event_date >= '2020-01-01' and  event_date <= '2020-12-31';
#根據統(tǒng)計，只有幾十萬或者幾百萬的話，可以執(zhí)行刪除
alter table trace_log delete where event_date >= '2020-01-01' and  event_date <= '2020-12-31';

clickhouse的delete,update等操作是異步的，當sql語句一提交，就會有反饋，這個時候是將任務提交到排隊執(zhí)行了

如果表非常小，不想選定時間范圍。也可以一次性清完

ALTER table `system`.trace_log DELETE where event_date is not null;

查看任務執(zhí)行情況

隊列數據在mutations表中

#統(tǒng)計歷史次數
select count(*) from mutations;
#排序，查看最新10條
select database,table,command,create_time,is_done from mutations order by create_time desc limit 10;

表信息顯示了命令發(fā)現的在system庫，表名已經執(zhí)行命令的時間，判斷sql是正在排還是已經執(zhí)行完成的是is_done字段。1表示執(zhí)行完成，0表示正在排隊

單機操作

clickhouse是支持創(chuàng)建集群庫，集群表的，但是system庫屬于系統(tǒng)自帶的，需要查看庫和表的創(chuàng)建信息

show create database system;
show create table trace_log;

因為system屬于系統(tǒng)自帶的庫，集群建好時已存在，但是創(chuàng)建該庫的sql文件確沒有,所以報錯。

但是查看clickhouse元數據文件時發(fā)現 data/system/trace_log文件在變小，說明本節(jié)點刪除成功了，如果換個節(jié)點執(zhí)行sql語句又能查出來。

所以需要在每個節(jié)點上執(zhí)行刪除數據語句

在元數據存儲的目錄可以看到有個mutations_xxxx.log的文件，是刪除語句的記錄日志 data/system/trace_log

元數據的配置

<!-- Path to data directory, with trailing slash. -->
<path>xxxxx/clickhouse/data/</path>

設置TTL

大部分數據刪除完成之后，就可以設置TTL了

#系統(tǒng)表
ALTER table `system`.trace_log MODIFY TTL event_date + toIntervalDay(30);
#業(yè)條表
ALTER TABLE `test`.app_version MODIFY TTL time_stamp + toIntervalYear(30);