如何在ClickHouse中实现主表URL匹配过滤表子串的查询

需求说明 #

现有两张表：

• main表（示例表名：dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh）：存储完整URL字符串
• filter表（示例表名：zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh）：存储需要匹配的URL子串

需要判断main表中的每个URL是否包含filter表中的任意子串，以下是两种实现方案：

建表语句（示例） #

-- 创建main表
create table dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh(urls String) 
Engine=Memory 
as select * from values( ('ixbt.com'), ('google.com'), ('gen_204'));

-- 创建filter表
create table zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh(filter String) 
Engine=Memory 
as select * from values( ('204'), ('gen'));

方案一：找出所有匹配的URL #

通过JOIN关联两张表，利用position函数判断子串是否存在，最终返回去重后的匹配URL：

SELECT DISTINCT m.urls
FROM dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh m
JOIN zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh f
ON position(m.urls, f.filter) > 0;

方案二：给每条URL标记匹配状态 #

将filter表的子串转为数组，使用arrayExists函数判断每个URL是否包含任意子串，返回每条URL的匹配结果：

SELECT
    urls,
    arrayExists(
        x -> position(urls, x) > 0,
        (SELECT groupArray(filter) FROM zxcvfvbghfntyj6r7uertgsdfhy456u7rtumdft68tyuksey5425tgasddfgdfgdfgrty4566465756474656574567465yerh)
    ) AS is_matched
FROM dfgsdfgdfgdfghdfhj56uy567yrthfdghdfgujrtyu456ueyhghfjfghjfghu567ueryththjfghjgh;

注意事项 #

• 大小写敏感：position函数默认区分大小写，如需忽略大小写，可统一转小写后判断：position(lower(m.urls), lower(f.filter)) > 0
• 数据量适配：若filter表数据量极大，groupArray可能引发内存问题，此时优先使用方案一的JOIN方式

内容的提问来源于stack exchange，提问作者Jerry