A Cleaner Approach to IP-based Geolocation in ClickHouse

Contents

• Intro
• Datasets Used
• Implementation
  • Server Configuration
  • Dictionary
  • Custom Lookup Functions
• Usage
  • lookupIPv4
  • lookupIPv4All
• The Result

Intro

Since I use ClickHouse for building observability at my company, there are some situations when IP geolocation is needed.

There’s a really great article on the ClickHouse blog explaining in detail how this can be approached.

It’s a good start, but I decided to make my own flavor by adding country names from another dataset in addition to country codes, and squash everything into a single Dictionary definition to eliminate the hustle of maintaining any possible tables backing the dictionary.

Datasets Used

• Base dataset: sapics/ip-location-db/iptoasn-country-ipv4 or sapics/ip-location-db/dbip-country-ipv4-cidr
• Country codes to names mapping: annexare/Countries

Implementation

Server Configuration

By default, ClickHouse expects the default user to be available, when it uses the CLICKHOUSE SOURCE for a dictionary.

For improved security, I usually disable this user, and create separate users as needed, each protected by a password.

1. Disable the default user

   Create this file at /etc/clickhouse-server/users.d/default.xml

   copy

   <clickhouse>
     <users>
         <default remove="remove"></default>
     </users>
   </clickhouse>

2. Create a user for dictionaries

   /etc/clickhouse-server/users.d/dict_updater.xml

   copy

   <clickhouse>
     <users>
         <dict_updater>
             <networks>
                 <ip>127.0.0.1/32</ip>
             </networks>
             <password_sha256_hex>REPLACEME</password_sha256_hex>
         </dict_updater>
     </users>
   </clickhouse>

   Where REPLACEME is a SHA256 hash. It can be generated using openssl:

   copy

   $

   (read P; echo -n $P | openssl dgst -sha256) # Use the `read` builtin with an `-s` option to prevent echoing if your shell supports this

Dictionary

I like to avoid using the default database, and use a separate database for geolocation and other supplementary data.

CREATE DATABASE IF NOT EXISTS meta;

Now, create the Dictionary itself. As of the time writing, I update geolocation data every 24 hours, so LIFETIME is set to 86400 seconds. Don’t forget to replace REPLACEME with your password. It should be the password itself this time, not its hash.

If you are using an interactive ClickHouse client, make sure it doesn’t store command history for the query below. Otherwise, it will leak your dict_updater password.

I usually just connect with the --history-file /dev/null option using clickhouse client for that purpose.

CREATE DICTIONARY meta.ipv4_geo
(
    cidr         String,
    country_code String DEFAULT 'ZZ',
    country_name String DEFAULT 'Unknown'
)
PRIMARY KEY cidr
SOURCE(
    CLICKHOUSE(
        USER 'dict_updater'
        PASSWORD 'REPLACEME'
        QUERY '
            SELECT cidr, country_code, country_name
            FROM
            (
                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
                FROM url(
                    \'https://github.com/sapics/ip-location-db/releases/download/latest/iptoasn-country-ipv4.csv\',
                    \'CSV\',
                    \'ip_range_start IPv4, ip_range_end IPv4, country_code String\'
                )
            ) AS geoip
            JOIN
            (
                SELECT Code AS country_code, Name AS country_name
                FROM url(
                    \'https://raw.githubusercontent.com/annexare/Countries/refs/heads/main/dist/countries.csv\',
                    \'CSVWithNames\'
                )
            ) AS countries ON geoip.country_code = countries.country_code
            '
))
LIFETIME(86400)
LAYOUT(IP_TRIE(access_to_key_from_attributes 1)) -- Allows querying the key itself.
SETTINGS(max_http_get_redirects = 1); -- This one is for GitHub releases links.

This is an alternative simplified implementation based on a CSV with already calculated CIDRs, as opposed to the example in the collapsible section.

CREATE DICTIONARY meta.ipv4_geo
(
    cidr         String,
    country_code String DEFAULT 'ZZ',
    country_name String DEFAULT 'Unknown'
)
PRIMARY KEY cidr
SOURCE(
    CLICKHOUSE(
        USER 'dict_updater'
        PASSWORD 'REPLACEME'
        QUERY '
            SELECT cidr, country_code, country_name
            FROM
            (
                SELECT cidr, country_code
                FROM url(
                    \'https://github.com/sapics/ip-location-db/releases/download/latest/dbip-country-ipv4-cidr.csv\',
                    \'CSV\',
                    \'cidr String, country_code String\'
                )
            ) AS geoip
            JOIN
            (
                SELECT Code AS country_code, Name AS country_name
                FROM url(
                    \'https://raw.githubusercontent.com/annexare/Countries/refs/heads/main/dist/countries.csv\',
                    \'CSVWithNames\'
                )
            ) AS countries ON geoip.country_code = countries.country_code
        '
))
LIFETIME(86400)
LAYOUT(IP_TRIE(access_to_key_from_attributes 1)) -- Allows querying the key itself.
SETTINGS(max_http_get_redirects = 1); -- This one is for GitHub releases links.

Custom Lookup Functions

To make your life easier and queries cleaner, create a couple of custom functions for performing IPv4 lookups:

CREATE FUNCTION IF NOT EXISTS lookupIPv4 AS
    (address, selection) -> dictGet('meta.ipv4_geo', selection, tuple(IPv4StringToNumOrDefault(address)));

CREATE FUNCTION IF NOT EXISTS lookupIPv4All AS
    (address) -> dictGet('meta.ipv4_geo',
                         tuple('country_code', 'country_name', 'cidr'),
                         tuple(IPv4StringToNumOrDefault(address)));

Usage

lookupIPv4

If an IP address is invalid, or there’s no data for this address, default values of each data type are returned.

(String, String or Tuple) -> Tuple or any value

select lookupIPv4('172.67.199.23', 'country_code');
select lookupIPv4('172.67.199.23', ('country_code', 'country_name', 'cidr'));

lookupIPv4All

The behavior is same is above.

(String) -> Tuple

select lookupIPv4All('172.67.199.23');

The Result

Now you have an automatically updating in-memory dictionary that provides you with geoip data. Functions lookupIPv4 and lookupIPv4All can make queries cleaner.

The dictionary will be populated when you first try to access a value from it, so the query will take a long time. Consequent queries will be very fast.

If you restart the server, the dictionary will be wiped from memory.

Even though this covers IPv4 only, I think people will come up with a similar solution for IPv6.