簡(jiǎn)介

clickhouse會(huì)將查詢?nèi)罩荆攘咳罩竞投褩２杉罩居涗浵聛?lái)，存儲(chǔ)到自身數(shù)據(jù)庫(kù)的system庫(kù)中， 分別是query_log,query_thread_log,trace_log,metric_log等5個(gè)表，如果長(zhǎng)時(shí)間不清查，該表數(shù)據(jù)會(huì)一直累積。

清理

清理方式

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

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

第2種是使用DDL語(yǔ)句+DML語(yǔ)句刪除指定范圍內(nèi)數(shù)據(jù)

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

字段釋義:

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

圖中沒(méi)有ttl的配置，可以自行添加, event_date是表中的時(shí)間字段,格式是: 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>

實(shí)際操作

方案選擇

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

第1種設(shè)置TTL的方案也是可以的，但是現(xiàn)在不合適，因?yàn)橐呀?jīng)累積了很長(zhǎng)時(shí)間的數(shù)據(jù)，數(shù)據(jù)量比較大，直接設(shè)置會(huì)消耗比較多的資源

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

刪除數(shù)據(jù)

使用DDL+DML語(yǔ)句刪除指定日期內(nèi)的數(shù)據(jù)

先用小一點(diǎn)的表操作一下，通常trace_log比較小，query_log表比較大

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

clickhouse的delete,update等操作是異步的，當(dāng)sql語(yǔ)句一提交，就會(huì)有反饋，這個(gè)時(shí)候是將任務(wù)提交到排隊(duì)執(zhí)行了

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

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

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

隊(duì)列數(shù)據(jù)在mutations表中

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

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

單機(jī)操作

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

show create database system;
show create table trace_log;

因?yàn)閟ystem屬于系統(tǒng)自帶的庫(kù)，集群建好時(shí)已存在，但是創(chuàng)建該庫(kù)的sql文件確沒(méi)有,所以報(bào)錯(cuò)。

但是查看clickhouse元數(shù)據(jù)文件時(shí)發(fā)現(xiàn) data/system/trace_log文件在變小，說(shuō)明本節(jié)點(diǎn)刪除成功了，如果換個(gè)節(jié)點(diǎn)執(zhí)行sql語(yǔ)句又能查出來(lái)。

所以需要在每個(gè)節(jié)點(diǎn)上執(zhí)行刪除數(shù)據(jù)語(yǔ)句

在元數(shù)據(jù)存儲(chǔ)的目錄可以看到有個(gè)mutations_xxxx.log的文件，是刪除語(yǔ)句的記錄日志 data/system/trace_log

元數(shù)據(jù)的配置

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

設(shè)置TTL

大部分?jǐn)?shù)據(jù)刪除完成之后，就可以設(shè)置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);

總結(jié)

通過(guò)刪除表數(shù)據(jù)+設(shè)置TTL的方式，后期就可以0維護(hù)了

系統(tǒng)表日志處理是日常數(shù)據(jù)維護(hù)的一種，業(yè)務(wù)數(shù)據(jù)的清理也可以參考這些方案