ClickHouse 中基于 IP 的地理定位

ClickHouse 是一种开源的列式数据库管理系统，最初是为了为网络分析服务（如 Google Analytics）提供支持而开发的。ClickHouse 的许多原始功能是为了帮助用户实时了解他们的网络分析数据而开发的。其中一项功能就是其强大的基于 IP 的查找功能。

在这篇博文中，我们将探讨如何使用 ClickHouse 的 ip_trie 结构化字典执行基于 IP 的地理定位。我们将从将开源 GeoIP 数据集导入 ClickHouse 并使用位函数将数据转换为可使用格式开始。然后，我们将从数据集中创建一个 ip_trie 字典，并使用 dictGet() 函数确定给定 IP 地址的大致位置。

最后，我们将探讨如何使用地理哈希来聚合地理空间数据并在 Grafana 等工具中创建丰富的可视化。有了这些工具，我们可以深入了解用户的地缘分布，并更好地了解他们如何与我们的网站和应用程序互动。所以让我们开始学习如何利用 ClickHouse 的力量进行基于 IP 的地理定位！

虽然我们使用 ClickHouse Cloud 来演示我们的示例，但它们应该可以在自管理集群上复制。或者，立即开始 ClickHouse Cloud 试用，获得 300 美元的免费信用额度，将基础设施留给我们，开始查询吧！

对于这篇博文，我们将使用 DB-IP 城市级数据集，该数据集由 DB-IP.com 根据 CC BY 4.0 许可 公开提供。

从 自述文件 中，我们可以看到数据结构如下

| ip_range_start | ip_range_end | country_code | state1 | state2 | city | postcode | latitude | longitude | timezone |

给定此结构，让我们使用 ClickHouse Cloud 中的 SQL 控制台，使用 url() 表函数先查看一下数据

select
    *
from
    url(
        'https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz',
        'CSV',
        '
            ip_range_start IPv4, 
            ip_range_end IPv4, 
            country_code Nullable(String), 
            state1 Nullable(String), 
            state2 Nullable(String), 
            city Nullable(String), 
            postcode Nullable(String), 
            latitude Float64, 
            longitude Float64, 
            timezone Nullable(String)
        '
    )
limit
    20;

为了方便起见，让我们使用 URL() 表引擎来创建一个带有我们字段名称的 ClickHouse 表对象

create table geoip_url(
    ip_range_start IPv4, 
    ip_range_end IPv4, 
    country_code Nullable(String), 
    state1 Nullable(String), 
    state2 Nullable(String), 
    city Nullable(String), 
    postcode Nullable(String), 
    latitude Float64, 
    longitude Float64, 
    timezone Nullable(String)
) engine=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV');

我们也检查一下数据集中包含多少条记录

select count() from geoip_url;

因为我们的 ip_trie 字典要求 IP 地址范围以 CIDR 表示法表示，所以我们需要转换 ip_range_start 和 ip_range_end。作为参考，这里是从维基百科上简要 介绍 CIDR 表示法

这些组，通常称为 CIDR 块，在它们的 IP 地址的二进制表示中共享一个初始位的序列。IPv4 CIDR 块使用与 IPv4 地址类似的语法来标识：一个点分十进制地址，后面跟着一个斜杠，然后是一个介于 0 到 32 之间的数字，即 a.b.c.d/n。点分十进制部分是 IPv4 地址。斜杠后的数字是前缀长度，即共享的初始位数，从地址的最高有效位开始计数。当只强调网络的大小时，通常会省略表示法的地址部分。因此，/20 块是一个前缀为 20 位的 CIDR 块，其地址未指定。

一个 IP 地址是一个 CIDR 块的一部分，如果该地址的初始 n 位与 CIDR 前缀相同，则称该地址与 CIDR 前缀匹配。一个 IPv4 地址是 32 位，因此一个 n 位 CIDR 前缀留下 32 − n 位不匹配，这意味着 232−n 个 IPv4 地址与给定的 n 位 CIDR 前缀匹配。

一个 IP 地址是一个 CIDR 块的一部分，如果该地址的初始 n 位与 CIDR 前缀相同，则称该地址与 CIDR 前缀匹配。一个 IPv4 地址是 32 位，因此一个 n 位 CIDR 前缀留下 32 − n 位不匹配，这意味着 232−n 个 IPv4 地址与给定的 n 位 CIDR 前缀匹配。

为了为我们的 IP 范围生成 CIDR 表达式，我们需要比较每个范围以确定每个地址中不匹配的位，然后确定包含整个范围的最小可能的 CIDR 范围。这里有一个图来演示

在此图中，我们可以看到我们的示例 IP 地址范围 (161.123.218.0 → 161.123.220.255) 包含 768 个可能的地址，跨越三个 /24 CIDR 块，两个 /23 块和两个 /22 块。因此，包含该范围的最小可能的 CIDR 块是一个 /21 块（包含 2048 个地址）。令人惊讶的是，整个计算可以通过 ClickHouse 支持的少数几个按位和数学函数来完成。

首先，我们将使用 ClickHouse 中的 bitXor() 函数，该函数对两个数字执行按位异或运算，并返回异或运算的结果。异或运算比较两个数字的每一位，如果两位数字不同，则输出 1，如果相同，则输出 0。例如，表达式 bitXor(5,3) 将计算结果为 6，因为 5 在二进制中表示为 101，3 表示为 011。比较每一位得到 110，它等效于十进制的 6。让我们看看当我们将 IP 转换为整数并应用 bitXor() 函数时会发生什么——请注意，ClickHouse 自动将 bitXor() 的输出转换为十进制形式，因此我们添加了一列来演示底层的二进制表达式

select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary
from
    geoip_url
limit
    20;

从这里，我们可以看到 bitXor() 将返回我们范围中的地址数（例如，1.0.0.0 和 1.0.0.255 之间的 255 个地址）。

接下来，我们将使用 log2() 和 ceil() 来计算从第一个不匹配位开始的二进制数字数量。我们还需要处理我们的 IP 范围的开始和结束相等（因此产生 0 的异或输出）的情况，因为 log2(0) 是未定义的

select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32-unmatched as cidr_suffix
from
    geoip_url
limit
    20;

在上面的结果中，我们可以看到 unmatched 字段有效地计算了从我们的 bitXor() 输出中的第一个 1 位开始的位置。从 32 中减去此值即可得出我们的 CIDR 块大小（后缀）。

接下来，我们将确定 CIDR 块的起始地址，因为该地址通常与我们范围的起始地址不同，特别是对于较大的块。为此，我们需要对 CIDR 块中包含的地址总数执行 按位取反运算 bitNot()，该总数可以表示为 pow(2, n) - 1，其中 n 等于上面 unmatched 字段的值。然后，我们将使用 按位与运算 bitAnd() 将结果与我们的 IP 范围的开始进行比较，以确定两个表达式中哪些位等于 1。然后可以通过先将其转换为 UInt64，然后应用 toIPv4() 函数，将结果转换回 IPv4 地址。

select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
from
    geoip_url
limit
    20;

最后，我们可以将我们的 CIDR 地址和后缀连接成一个字符串。为了使它更简洁，我们还将所有中间表达式移动到 WITH 子句中。

with 
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
    ip_range_start,
    ip_range_end,
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr    
from
    geoip_url
limit
    20;

出于我们的目的，我们只需要 IP 范围、国家代码和坐标，因此让我们创建一个新表并插入我们的 GeoIP 数据。

create table geoip (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
) 
engine = MergeTree() 
order by cidr;

insert into 
    geoip
with 
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
    latitude,
    longitude,
    country_code    
from
    geoip_url

为了在 ClickHouse 中执行低延迟 IP 查询，我们将利用字典来存储 GeoIP 数据的 键 -> 属性 映射。ClickHouse 提供了一个 ip_trie 字典结构 来将我们的网络前缀（CIDR 块）映射到坐标和国家代码。

create dictionary ip_trie (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
) 
primary key cidr
source(clickhouse(table ‘geoip’))
layout(ip_trie)
lifetime(3600);

只是为了确认所有内容都已正确加载到字典中，让我们快速看一下。

select * from ip_trie limit 20;

ClickHouse 中的字典会根据底层表数据和上面使用的 生存期子句 定期刷新。为了更新我们的 GeoIP 字典以反映 DB-IP 数据集中最新的更改，我们只需要将 geoip_url 远程表中的数据重新插入到我们的 geoip 表中，并应用转换。

现在我们已经将 GeoIP 数据加载到我们的 ip_trie 字典（也巧妙地命名为 ip_trie）中，我们可以开始使用它进行 IP 定位。这可以通过使用 dictGet() 函数来完成，如下所示：

dictGet(
   ‘<dictionary name>’,
   (‘field1’,’field2’, .. .’fieldN’),
   tuple(<ip address in type IPV4 or UInt>)
)

首先，让我们尝试查找单个 IP。

正如我们所见，dictGet() 返回一个包含我们字典中该 IP 的所有指定字段的元组。现在让我们将此查询应用于包含许多 IP 地址的表。对于此演示，我们将使用来自美国证券交易委员会发布的“EDGAR 日志文件数据集” 的摘录。

select
    datetime,
    ip,
    accession,
    extention,
    dictGet(
        'ip_trie',
        ('country_code', 'latitude', 'longitude'),
        tuple(ip)
    ) as geo_info
from
    edgar
where
    geo_info.1 != ''
limit
    1000;

不可避免地，这些数据使用聚合来解释会更好，因此让我们编写一个查询来确定访问日志事件的国家级细分。

select 
    dictGet(
        'ip_trie',
        ('country_code', 'latitude', 'longitude'),
        tuple(ip)
    ).1 as country,
    formatReadableQuantity(count()) as num_requests
from edgar
where country != ''
group by country
order by count() desc

像 Grafana 和 Metabase 这样的工具可以帮助我们从我们的纬度/经度坐标生成地理空间可视化，但有一个难题：在地图上绘制数千万个点成本高昂。因此，我们需要在渲染可视化之前预先聚合我们的地理数据结果集。为此，我们将使用 geohashEncode() 从我们的坐标创建地理哈希，并使用较低的 precision 参数值，在此哈希上进行聚合，并将 count() 函数的结果与对数归一化 log10() 一起使用，以显示密度，同时确保我们的热评分很好地缩放。

with coords as (
    select 
        dictGet(
            'ip_trie',
            ('latitude', 'longitude'),
            tuple(ip)
        ) as coords,
        coords.1 as latitude,
        coords.2 as longitude,
        geohashEncode(longitude,latitude,4) as hash
    from 
        edgar
    where
        longitude != 0 
        and latitude != 0
) 
select 
    hash, 
    count() as heat, 
    round(log10(heat),2) as adj_heat 
from 
    coords 
group by 
    hash

使用地理哈希，我们现在已将 2000 多万条记录汇总到约 6300 条结果中。将此查询加载到 Grafana 中以驱动 Geo Map 图表面板将生成丰富的可视化效果。

注意：我们还可以在 Grafana 中的查询中添加 $__timeFilter() 宏，将我们的结果范围限定到特定的时间过滤器。您可以阅读有关将 Grafana 与 ClickHouse 结合使用的更多信息。类似的结果也可以 在 Metabase 中实现。