Most ClickHouse IP geolocation tutorials turn each IP range into a CIDR block with a one-line XOR trick. On a commercial city database, that trick sizes 3 in 4 multi-address ranges wrong and claims almost 10x the addresses those ranges hold. Nothing throws. Your country and city columns just quietly pick up neighbours' answers.
This walkthrough builds the exact version in plain ClickHouse SQL: one function that splits IPv4 and IPv6 ranges into CIDR blocks, an ip_trie dictionary that matched the vendor's own MMDB network for network, enrichment at insert time, and a daily refresh that swaps releases without a gap.
TL;DR
- Use the
ip_triedictionary layout for IP ranges. It is the only layout that does range lookups on IPv6, and it answers one in-memory lookup per row.- Do not convert ranges with the XOR one-liner. Split each range into exact CIDR blocks; the SQL function below does it for IPv4 and IPv6 with integer-only math.
- Keep only the columns you query in the trie. On a 1M-range sample, a trie holding just the country id used 49 MiB; one holding every shipped column used 370 MiB.
- Enrich at insert time with
MATERIALIZEDcolumns, so last month's rows keep last month's location.- Refresh by building
_nexttables, checking row counts, thenEXCHANGE TABLESandSYSTEM RELOAD DICTIONARY. The old dictionary serves until the new one is ready.
The short version: get the CIDR split right, keep the dictionary thin enough to fit in RAM on every replica, and pay for lookups once at insert instead of on every query. Everything below was run on ClickHouse 26.11 against data in a real vendor CSV layout, with the measurements and hardware stated.
Pick the lookup layout before you write SQL
ClickHouse gives you three ways to answer "which range contains this IP": a range join, a range_hashed dictionary, or an ip_trie dictionary.
A BETWEEN join is fine for a one-off question on a small table. For billions of rows you want one in-memory probe per row, which is what dictionaries give you. I measured the two dictionary layouts on a 1M-range sample, each holding only a row id so the comparison is fair:
| Layout | IPv6 | Memory | Load time | 19.2M random IPv4 lookups |
|---|---|---|---|---|
ip_trie (both families) | Yes | 53.18 MiB | 5.7 s | 12.6 s |
range_hashed, constant key (IPv4 part) | No | 71.53 MiB | 1.1 s | 10.9 s |
The range_hashed trick is legitimate and almost nobody documents it: give every row the same key (0) and let RANGE(MIN start MAX end) do the work, and you skip CIDR conversion entirely. It returned identical answers to the trie. The catch is that the lookup value has to convert to Int64, so a UInt128 IPv6 address fails with a type error. If your file has a single IPv6 row, and real geolocation files do, you are on ip_trie. So is the rest of this article.
ip_trie wants CIDR prefixes as keys, not start and end addresses. That conversion is where the popular recipe goes wrong.
The range-to-CIDR bug in the popular recipe
This is the conversion at the heart of most ClickHouse GeoIP setups, from ClickHouse's IP geolocation blog post. The observability schema guide still uses it:
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 It finds the smallest single CIDR block that contains the range. That is only correct when the range already is one block. Take 59.44.215.15 to 59.44.215.17, three addresses. The one-liner turns it into 59.44.215.0/27, which covers 32. The exact answer is two blocks: 59.44.215.15/32 and 59.44.215.16/31. A 144-address range from 202.101.230.144 to 202.101.231.31 becomes a /23 with 512 addresses instead of four exact blocks.
ip_trie returns the most specific matching prefix, so an oversized block only shows up when a neighbour's block is more specific, or when there is no neighbour and the lookup should have returned nothing. That is why it survives code review.
I measured it on a 1M-range sample of ipgeolocation.io's Advanced IP to City database. Nine in ten ranges there are single addresses, which the one-liner handles fine. Of the 104,601 IPv4 ranges that hold more than one address:
- 79,565 (76%) got the wrong block size. Those ranges hold 91,977,222 addresses; the one-liner claimed 898,986,820.
- 980 of the 1,901 two-address ranges shrank to a /32, because
log2(1)is 0. Half of each range disappears. - One random address sampled from every IPv4 range resolved to another range's record 2.4% of the time, and to another city 0.5% of the time. That last number is a floor. The sample is a sparse slice of the full file, so most oversized blocks have no neighbour to collide with. On DB-IP's free city file, which is complete and contiguous, the same test sent 2.06% of sampled addresses to the wrong city.
Step 1: Load the files the way they ship
I'm using ipgeolocation.io's database CSVs for the examples, because every file stores integer ids that point at small reference files (country_id, city_place_id), which is the shape you want in RAM, and the same database also ships as an MMDB you can check the result against. DB-IP Lite publishes start/end ranges too, so the same SQL works for it once you rename the columns. MaxMind's GeoLite2 CSVs already ship CIDR networks, so with those you can skip the split.
The IP to City archive has three files you need: db-ip-location.csv.gz (IPv4 and IPv6 ranges in one file, with a header row), db-place.csv.gz (place names in 12 languages), and db-country.csv.gz. Copy them into an ipgeo/ folder under the server's user_files directory (/var/lib/clickhouse/user_files on a default install), because file() only reads from there.
CREATE DATABASE IF NOT EXISTS geo; -- One row per exact CIDR block. Keep only the columns you will query: -- every column here is held in RAM on every replica once the dictionary loads. CREATE TABLE IF NOT EXISTS geo.ip_cidr ( cidr String, country_id UInt16, state_place_id UInt32, city_place_id UInt32, latitude Float32, longitude Float32, time_zone_name String, connection_type String ) ENGINE = MergeTree ORDER BY cidr; -- Small reference tables: names are resolved by id, not copied into every block. CREATE TABLE IF NOT EXISTS geo.place ( id UInt32, place_english String ) ENGINE = MergeTree ORDER BY id; CREATE TABLE IF NOT EXISTS geo.country ( id UInt16, country_code2 String, country_name_place_id UInt32 ) ENGINE = MergeTree ORDER BY id; Every address in this pipeline is an IPv6 value. IPv4 rows become IPv4-mapped addresses (::ffff:59.44.215.15), so one trie and one code path cover both families.
Step 2: Convert IP ranges to exact CIDRs in SQL
Three parts: a function that does the bit math, one INSERT that uses it, and a check that proves the output is exact.
One function for IPv4 and IPv6
The algorithm is the classic one: start at the range's first address, emit the largest block that is aligned at that address and does not run past the end, jump past it, repeat. In ClickHouse that is an arrayFold over a fixed number of steps, wrapped in SQL UDFs so the load query stays readable. This implementation targets ordinary database ranges, not a range spanning the entire IPv6 address space (::/0), which requires special handling because its address count exceeds UInt128.
-- Integer-only helpers on UInt128, so IPv4 (stored as IPv4-mapped IPv6) and IPv6 share one code path. -- Float64 log2() is fine for 32-bit values but rounds 128-bit ones, so it is avoided on purpose. -- Largest power of two that is <= n (bin() trims leading zero bytes, position() finds the top bit). CREATE OR REPLACE FUNCTION ip_floor_pow2 AS (n) -> if(n = 0, toUInt128(0), bitShiftLeft(toUInt128(1), length(bin(n)) - position(bin(n), '1'))); -- Largest block that can start at x: the value of x's lowest set bit. CREATE OR REPLACE FUNCTION ip_lowbit AS (x) -> if(x = 0, bitShiftLeft(toUInt128(1), 127), toUInt128(bitAnd(x, toUInt128(bitNot(x) + 1)))); -- Next block when walking from x towards e: aligned at x and not past e. CREATE OR REPLACE FUNCTION ip_next_block AS (x, e) -> least(ip_lowbit(x), ip_floor_pow2(toUInt128(e - x + 1))); -- Split the inclusive range [s, e] into exact CIDRs, returned as (start, prefix_length) pairs. -- An IPv4 range needs at most 62 blocks and an IPv6 range at most 254, hence 64 or 256 steps. CREATE OR REPLACE FUNCTION ip_range_to_cidrs AS (s, e) -> arrayFold( (acc, i) -> if(acc.1 > e OR acc.1 < s, -- done, or wrapped past the top of the space acc, (toUInt128(acc.1 + ip_next_block(acc.1, e)), arrayPushBack(acc.2, (acc.1, toUInt8(128 - bitCount(ip_next_block(acc.1, e) - 1)))))), range(if(e <= toUInt128(toIPv6('::ffff:255.255.255.255')), 64, 256)), (s, CAST([], 'Array(Tuple(UInt128, UInt8))')) ).2; Two details matter more than they look. The toUInt128() wrappers stop ClickHouse from promoting e - x + 1 to Int256, which arrayFold would reject because the accumulator type has to stay fixed. And the step count switches to 64 for IPv4-mapped ranges, which keeps the fold cheap for the 96% of rows that are IPv4.
Load and split in one pass
Now read the CSV, split every range, and write one row per block. The file() schema lists every column because the files carry a header row and ClickHouse matches columns by name. Empty id fields, such as a range with no city, load as 0.
INSERT INTO geo.ip_cidr SELECT concat(toString(toIPv6(c.1)), '/', toString(c.2)) AS cidr, country_id, state_place_id, city_place_id, latitude, longitude, time_zone_name, connection_type FROM file('ipgeo/db-ip-location.csv.gz', 'CSVWithNames', 'start_ip String, end_ip String, country_id UInt16, state_place_id UInt32, state_code String, district_place_id UInt32, city_place_id UInt32, accuracy_radius Float32, confidence String, dma_code String, zip_code String, latitude Float32, longitude Float32, geo_name_id UInt32, time_zone_name String, connection_type String') ARRAY JOIN ip_range_to_cidrs(toUInt128(toIPv6(start_ip)), toUInt128(toIPv6(end_ip))) AS c; INSERT INTO geo.place SELECT id, place_english FROM file('ipgeo/db-place.csv.gz', 'CSVWithNames', 'id UInt32, place_english String, place_german String, place_russian String, place_korean String, place_portuguese String, place_japanese String, place_persian String, place_french String, place_chinese String, place_spanish String, place_czech String, place_italian String'); INSERT INTO geo.country SELECT id, country_code2, country_name_place_id FROM file('ipgeo/db-country.csv.gz', 'CSVWithNames', 'id UInt16, continent_code String, continent_name_place_id UInt32, country_code2 String, country_code3 String, country_code_ioc String, country_name_place_id UInt32, country_name_official_place_id UInt32, country_capital_place_id UInt32, currency_code String, currency_name String, currency_symbol String, calling_code String, tld String, languages String'); On the 1M-range sample, the split produced 1,435,478 blocks (1.44 per range) and took about 18 seconds on 2 vCPUs. Want names in German or Japanese instead of English? Select a different place_* column into geo.place.
Check the result against the MMDB
This is the check that convinced me. The same database is also available as an MMDB file, which stores IP mappings in a network tree. Comparing the generated CIDRs with those in the MMDB provides a strong structural regression check when both formats preserve the same network boundaries. Equivalent lookup data can sometimes have different CIDR partitions, so a mismatch should be investigated rather than automatically treated as incorrect conversion. This script dumps the MMDB's networks in the same text form ClickHouse produces:
"""Dump every network in an MMDB file as one CIDR per line, in the same text form ClickHouse produces.""" import sys import maxminddb # pip install maxminddb def main(mmdb_path: str, out_path: str) -> int: try: reader = maxminddb.open_database(mmdb_path) except (OSError, maxminddb.InvalidDatabaseError) as exc: print(f"cannot open {mmdb_path}: {exc}", file=sys.stderr) return 1 count = 0 with reader, open(out_path, "w", encoding="utf-8") as out: for network, _record in reader: # iterates every network that has data if network.version == 4: # ClickHouse keeps IPv4 as IPv4-mapped IPv6, so /n becomes /(96 + n) out.write(f"::ffff:{network.network_address}/{96 + network.prefixlen}\n") else: out.write(f"{network.network_address}/{network.prefixlen}\n") count += 1 print(f"{count} networks written to {out_path}") return 0 if __name__ == "__main__": if len(sys.argv) != 3: sys.exit("usage: mmdb_networks.py <file.mmdb> <out.txt>") sys.exit(main(sys.argv[1], sys.argv[2])) Put the output in user_files/ipgeo/ and compare. If the counts are equal and nothing is missing, the sets are identical:
SELECT (SELECT count() FROM geo.ip_cidr) AS clickhouse_blocks, (SELECT count() FROM file('ipgeo/mmdb_networks.txt', 'LineAsString', 'cidr String')) AS mmdb_blocks, (SELECT count() FROM file('ipgeo/mmdb_networks.txt', 'LineAsString', 'cidr String') WHERE cidr NOT IN (SELECT cidr FROM geo.ip_cidr)) AS in_mmdb_not_in_clickhouse; On the sample: 1,435,478 blocks on both sides, zero missing. The XOR one-liner cannot pass this check, which makes it a useful regression test after every refresh.
Step 3: Build the dictionaries
Three ClickHouse dictionaries: the trie, plus two hashed_array lookups for names. Give the source a dedicated read-only user. The ClickHouse docs ask for one, and a dictionary that reads as default is a password reset waiting to break your inserts.
-- A read-only user for the dictionary source. Pick your own password; do not reuse "default". CREATE USER IF NOT EXISTS geo_dict IDENTIFIED WITH sha256_password BY 'replace-with-a-long-random-secret' HOST LOCAL; GRANT SELECT ON geo.* TO geo_dict; CREATE DICTIONARY IF NOT EXISTS geo.ip_trie ( cidr String, country_id UInt16, state_place_id UInt32, city_place_id UInt32, latitude Float32, longitude Float32, time_zone_name String, connection_type String ) PRIMARY KEY cidr SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'ip_cidr')) LAYOUT(IP_TRIE) LIFETIME(0); -- reload on purpose after a refresh, never on a timer CREATE DICTIONARY IF NOT EXISTS geo.places ( id UInt64, place_english String ) PRIMARY KEY id SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'place')) LAYOUT(HASHED_ARRAY()) LIFETIME(0); CREATE DICTIONARY IF NOT EXISTS geo.countries ( id UInt64, country_code2 String, country_name_place_id UInt32 ) PRIMARY KEY id SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'country')) LAYOUT(HASHED_ARRAY()) LIFETIME(0); LIFETIME(0) is deliberate. A timer reload would pick up a half-built table; the refresh in Step 6 reloads explicitly once the new data is in place. SHOW CREATE DICTIONARY prints the password as [HIDDEN], so it does not leak through the catalog, though anyone who can read your migration files still sees it.
Step 4: Look up IPs, distinct ones first
Two small wrappers keep queries and column definitions readable:
-- Wrappers so queries and column definitions stay readable. Input is an IPv6 value; -- IPv4 addresses work as IPv4-mapped IPv6 (toIPv6('8.8.8.8') = ::ffff:8.8.8.8). -- Unknown, private, and bogon addresses fall through to '' instead of throwing. CREATE OR REPLACE FUNCTION geo_country_code AS (ip) -> dictGetOrDefault('geo.countries', 'country_code2', toUInt64(dictGetOrDefault('geo.ip_trie', 'country_id', ip, toUInt16(0))), ''); CREATE OR REPLACE FUNCTION geo_city AS (ip) -> dictGetOrDefault('geo.places', 'place_english', toUInt64(dictGetOrDefault('geo.ip_trie', 'city_place_id', ip, toUInt32(0))), ''); A private address like 10.0.0.1 matches no range, so the trie returns the default you pass in, and geo_country_code comes back empty instead of guessing. If your log stores IPs as strings, convert with toIPv6OrDefault(ip) so one malformed value does not fail the whole query; it becomes ::, which also matches nothing.
Query-time lookups have a cost worth knowing. On 2 vCPUs, 20M lookups over random IPs took about 19 seconds, roughly a million rows per second. The same lookups over sorted IPs took 1.7 seconds, because neighbouring keys stay in CPU cache. Logs ordered by time look random to the trie.
So when you do look up at query time, collapse to distinct IPs first. This runs against the log table from Step 5, but any table with an IPv6 client column works:
SELECT dictGetOrDefault('geo.ip_trie', 'time_zone_name', client_ip, '') AS time_zone, sum(hits) AS requests FROM ( -- One lookup per distinct client instead of one per request SELECT client_ip, count() AS hits FROM logs.http_requests WHERE ts >= now() - INTERVAL 1 DAY GROUP BY client_ip ) GROUP BY time_zone ORDER BY requests DESC LIMIT 20; On 30M log rows over 500,000 distinct IPs, this ran in 1.77 seconds against 15.97 seconds for the per-row version. Same totals, 9x faster.
Step 5: Enrich at insert time
This is the part I'd push back on in most setups: don't geolocate historical logs at query time. IP blocks get reassigned, sold, and re-announced. A row written last March describes a request from last March, and it should keep the country that address had then. Query-time dictGet quietly rewrites history every time you refresh the database.
MATERIALIZED columns compute the value once, when the row is inserted, from whatever dictionary release is live at that moment:
CREATE DATABASE IF NOT EXISTS logs; CREATE TABLE IF NOT EXISTS logs.http_requests ( ts DateTime, client_ip IPv6, -- IPv4 clients land here as ::ffff:a.b.c.d method LowCardinality(String), path String, status UInt16, bytes UInt64, -- Computed once, at insert, from the dictionary version that was live at that moment. country_code LowCardinality(String) MATERIALIZED geo_country_code(client_ip), city String MATERIALIZED geo_city(client_ip), -- tens of thousands of values: plain String connection_type LowCardinality(String) MATERIALIZED dictGetOrDefault('geo.ip_trie', 'connection_type', client_ip, '') ) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (ts, client_ip); Three behaviours to know. Inserts never mention these columns; ClickHouse fills them. SELECT * does not return them, so name them explicitly. And ClickHouse expands the UDFs into the column definition when the table is created (check SHOW CREATE TABLE), so replacing a function later does not change existing columns.
Store finished values like a country code, never a dictionary row id or a place id you plan to resolve later. Ids belong to a release; the string you store today should mean the same thing in a year.
Adding enrichment to a table that already holds data takes two statements:
ALTER TABLE logs.http_requests_archive ADD COLUMN IF NOT EXISTS country_code LowCardinality(String) MATERIALIZED geo_country_code(client_ip); -- Write the values into one partition at a time; runs as a background mutation. ALTER TABLE logs.http_requests_archive MATERIALIZE COLUMN country_code IN PARTITION 202610; Until a partition is materialized, ClickHouse computes the column on read, from today's dictionary. After MATERIALIZE COLUMN, the value is written into the part and stops moving. Be honest about what a backfill gives you: today's answer for an old row, not the historical one. If you need true point-in-time geography for old data, keep the old database releases and backfill each month from its matching release. Track progress in system.mutations.