Converting IP Ranges to CIDR Blocks for ClickHouse Geolocation Lookups
A common ClickHouse IP geolocation recipe converts each IP range into a single CIDR block with a one-line XOR trick, which miscalculates most multi-address ranges and overstates their size. The article demonstrates an exact conversion in plain ClickHouse SQL covering both IPv4 and IPv6, splitting each range into the precise set of CIDR blocks. It recommends the ip_trie dictionary layout, the only option that supports IPv6 range lookups, and notes that keeping only queried columns sharply reduces memory use. Enrichment is applied at insert time via MATERIALIZED columns so historical rows keep their original location data. Daily refreshes are handled by building replacement tables, verifying row counts, swapping them, and reloading the dictionary without downtime.
This is an AI-generated summary. ShortSingh links to the original source for the complete article.


Discussion (0)
Log in to join the discussion and vote.
Log in