如何在ClickHouse中通过IP/CIDR-ASN表为sFlow数据表添加ASN？

核心思路 #

1. 先从sFlow表的IPv6类型IPv4映射地址中提取对应IPv4地址
2. 用isIPAddressInRange()函数匹配IP/CIDR-ASN表的CIDR段，关联获取ASN

具体操作步骤 #

1. 明确表结构

假设两张表结构如下：

• ip_asn_table：存储IP段与ASN映射，字段为cidr（String类型，如10.0.0.0/8）、asn（UInt32类型）
• sflow_table：存储sFlow数据，字段包含src_ipv6（IPv6类型或String类型的IPv6地址）

2. 提取IPv4并关联ASN

用extractIPv4FromIPv6()函数从IPv6映射地址中提取IPv4数值，再通过isIPAddressInRange()判断归属CIDR段，完成关联：

SELECT
    s.*,
    a.asn
FROM sflow_table s
LEFT JOIN ip_asn_table a
ON isIPAddressInRange(
    -- 若src_ipv6是字符串类型，先转成IPv6类型
    extractIPv4FromIPv6(toIPv6(s.src_ipv6)),
    a.cidr
)
-- 可选：过滤非IPv4映射的IPv6地址
WHERE extractIPv4FromIPv6(toIPv6(s.src_ipv6)) != 0

如果sflow_table的src_ipv6本身是IPv6类型，可省略toIPv6()转换：

SELECT
    s.*,
    a.asn
FROM sflow_table s
LEFT JOIN ip_asn_table a
ON isIPAddressInRange(extractIPv4FromIPv6(s.src_ipv6), a.cidr)
WHERE extractIPv4FromIPv6(s.src_ipv6) != 0

3. 大表查询性能优化

若ip_asn_table数据量较大，直接JOIN会变慢，建议转成ClickHouse字典加速：

创建字典

CREATE DICTIONARY ip_asn_dict (
    cidr String,
    asn UInt32
)
PRIMARY KEY cidr
SOURCE(CLICKHOUSE(TABLE ip_asn_table))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600) -- 字典自动刷新时间（秒）

用字典查询

SELECT
    s.*,
    (
        SELECT asn
        FROM ip_asn_dict
        WHERE isIPAddressInRange(extractIPv4FromIPv6(s.src_ipv6), cidr)
        LIMIT 1
    ) AS asn
FROM sflow_table s
WHERE extractIPv4FromIPv6(s.src_ipv6) != 0

关键函数说明 #

• extractIPv4FromIPv6(ipv6)：从IPv4映射的IPv6地址（如::ffff:192.168.1.1）中提取对应IPv4数值（UInt32类型）
• isIPAddressInRange(ip, cidr)：判断IP地址（支持字符串或数值型）是否落在指定CIDR网段内

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