ClickHouse教程 — 第三章 ClickHouse的SQL语句

1 clickhouse 正则匹配

参考文章：在clickhouse中使用正则表达式提取字段信息的方法

1. match：匹配到了则返回1，否则返回0。

select EXTRACT(字段名,'正则表达式规则') AS matchSearch FROM table;

去重：

select DISTINCT(EXTRACT(字段名,'正则表达式规则')) AS matchSearch FROM table;

2 clickhouse按条件查询array中的数据

参考文章：clickhouse - 如何按数组列内容过滤Clickhouse表

测试：

CREATE TABLE test
(
    `id` Int64 COMMENT 'id',
	`class_ids` Array(Int64) COMMENT '分类id集合',
	`capture_time` Datetime COMMENT '被捕获时的时间【Time转换而来】'
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMMDD(capture_time)
PRIMARY KEY id
ORDER BY (id)
COMMENT '测试表';

INSERT INTO test VALUES(1,[1,2,3,4,5,6,7],'2023-03-06 12:10:10')(2,[3,4],'2023-03-06 12:10:10')(3,[3,6,7],'2023-03-06 12:10:10')(4,[3,6,7],'2023-03-06 12:10:10');

SELECT DISTINCT(class_ids) FROM test WHERE arrayExists(x -> x IN (6,9), class_ids) = 1;

SELECT DISTINCT(class_ids) FROM test ARRAY JOIN class_ids as class WHERE class IN (6,9);

3 SQL实现多字段去重

参考文章：sql实现多字段去重

4 清除数据clickhouse日志表

当对clickhouse里的数据表进行时，clickhouse默认会记录执行的查询语句到日志表里，随着查询的增多，日志表里的数据也会逐渐累积，导致占用过多空间。

clickhouse系统里面自带了query_log和query_thread_log表，这两个表不清除的话，时间久了，会挺大的，可以通过设置ttl时间来处理。

保存一个月的数据：

ALTER TABLE query_log MODIFY TTL event_date + toIntervalMonth(1);
 
ALTER TABLE query_thread_log MODIFY TTL event_date + toIntervalMonth(1);

保存15天的数据：

ALTER TABLE query_log MODIFY TTL event_date + toIntervalDay(15);
 
ALTER TABLE query_thread_log MODIFY TTL event_date + toIntervalDay(15);

清除分区数据：

1. 查看分区

SELECT partition FROM system.parts WHERE (database = 'system') AND (table = 'query_thread_log') GROUP BY partition ORDER  BY partition DESC

2. 删除分区

alter table system.query_thread_log drop partition '202110';