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

西木Qi

已于 2023-09-22 10:17:38 修改

阅读量2.3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： clickhouse 文章标签： clickhouse

于 2023-03-10 11:24:42 首次发布

本文链接：https://blog.csdn.net/Mr_XiMu/article/details/129438982

clickhouse 专栏收录该内容

3 篇文章

订阅专栏

本教程介绍了在ClickHouse中使用SQL语句进行正则表达式匹配、按条件查询数组数据、多字段去重以及如何清理日志表的方法。例如，使用`DISTINCT`配合`EXTRACT`进行去重，利用`arrayExists`查询数组内满足条件的元素，以及调整`query_log`和`query_thread_log`表的TTL来自动清理旧数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 clickhouse 正则匹配

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

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);

清除分区数据：

查看分区

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

删除分区

alter table system.query_thread_log drop partition '202110';