ClickHouse 中基于 IP 的地理位置定位

ClickHouse 是一个开源的面向列的数据库管理系统，最初开发用于支持 Web 分析服务，如 Google Analytics。ClickHouse 的许多原始功能都是为了帮助用户实时理解其 Web 分析数据而开发的。其中一个功能是其强大的基于 IP 的查找功能。

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

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

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

在这篇博文中，我们将使用 DB-IP.com 根据 CC BY 4.0 许可条款提供的公开可用的 DB-IP 城市级数据集。

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

| 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 块。

如果地址的初始 n 位与 CIDR 前缀相同，则 IP 地址是 CIDR 块的一部分，并且据说与 CIDR 前缀匹配。一个 IPv4 地址是 32 位，因此 n 位 CIDR 前缀留下 32 − n 位不匹配，这意味着 2³²⁻ⁿ 个 IPv4 地址与给定的 n 位 CIDR 前缀匹配。

如果地址的初始 n 位与 CIDR 前缀相同，则 IP 地址是 CIDR 块的一部分，并且据说与 CIDR 前缀匹配。一个 IPv4 地址是 32 位，因此 n 位 CIDR 前缀留下 32 − n 位不匹配，这意味着 2³²⁻ⁿ 个 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 数据的 key -> attributes 映射。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 中的字典会根据底层表数据和上面使用的 lifetime 子句定期刷新。为了更新我们的 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 地址的表上应用此查找。对于此演示，我们将使用 SEC 发布的 ‘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 图表面板将产生丰富的可视化效果

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