ClickHouse is a very fast columnar database which is well-suited to analytics use cases. For example, you might use ClickHouse to log requests made to a website. With IPinfo’s free databases you can enhance ClickHouse abilities by enabling IP to Country and IP to ASN lookups in SQL queries.
In this tutorial we will see how to integrate IPinfo Free Country + ASN into ClickHouse and lookup million of IP addresses within milliseconds.
Prerequisites
- IPinfo Free Country + ASN database in CSV format
- IPinfo CLI to convert IP ranges to CIDR notation
- Docker to run a ClickHouse server
If you already have a running ClickHouse installation, you can use it and skip the Docker section.
Step 1 – Prepare the database
IPinfo’s free database in CSV format contains IP ranges described by the start_ip and end_ip column. However, ClickHouse expects ranges in CIDR notation.
For example, the range 1.0.0.0-1.0.0.10 is equivalent to the following three prefixes in CIDR notation: 1.0.0.0/29, 1.0.0.8/31 and 1.0.0.10/32.
Fortunately, this is easy to do with the IPinfo CLI:
ipinfo range2cidr country_asn.csv > country_asn.cidr.csv
Before
# head country_asn.csv
start_ip,end_ip,country,country_name,continent,continent_name,asn,as_name,as_domain
204.25.255.0,204.25.255.255,US,United States,NA,North America,AS401307,Michigan Statewide Educational Network,mich.net
136.228.60.0,136.228.60.255,US,United States,NA,North America,AS401307,Michigan Statewide Educational Network,mich.net
After
# head country_asn.cidr.csv
cidr,country,country_name,continent,continent_name,asn,as_name,as_domain
204.25.255.0/24,US,United States,NA,North America,AS401307,Michigan Statewide Educational Network,mich.net
136.228.60.0/24,US,United States,NA,North America,AS401307,Michigan Statewide Educational Network,mich.net
We now have a cidr column in place of the start_ip and end_ip columns.
Step 2 – Start a ClickHouse instance
(If you already have a running ClickHouse installation, you can skip this step.)
The easiest way to start a ClickHouse instance is to use the official Docker image:
docker run -d -it --name clickhouse clickhouse/clickhouse-server
To view the server logs run:
docker logs clickhouse
To stop and delete the container run:
docker rm -f clickhouse
Step 3 – Load the database
To enable efficient IP lookups, the database must be loaded in a radix tree. This is possible with ClickHouse by using a dictionary. A dictionary is a table which is stored in-memory with a layout optimized for certain kind of lookups. In our case, we want to use the ip_trie layout.
Dictionaries can be created from multiple sources. To keep things simple, we will create our dictionary from a local file.
To do this, first copy the database, in CIDR notation, inside the container in the user_files directory:
docker cp country_asn.cidr.csv clickhouse:/var/lib/clickhouse/user_files/
Then, start a SQL REPL by running:
docker exec -it clickhouse clickhouse client
And create the dictionary by running the following query:
CREATE DICTIONARY ipinfo
(
cidr String,
country String,
country_name String,
continent String,
continent_name String,
asn String,
as_name String,
as_domain String
)
PRIMARY KEY cidr
SOURCE(FILE(PATH '/var/lib/clickhouse/user_files/country_asn.cidr.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 3600)
LAYOUT(IP_TRIE)
The LIFETIME(MIN 0 MAX 3600) parameter indicates that the dictionary will be kept in memory at-most 3600 seconds without queries. Feel free to increase or reduce these values depending on your setup.
If you are using your own installation, see the user_files_path server settings to know the location of the user_files directory.
Step 4 – Query the database
ClickHouse provides the dict* functions to query dictionaries. In our case we’re interested by the dictGet function.
Note that the first query will take around 30 seconds as the dictionary gets loaded in memory. However subsequent queries will be extremely fast.
SELECT dictGet('ipinfo', 'country_name', IPv4StringToNum('1.1.1.1')) AS country
-- ┌─country───────┐
-- │ United States │
-- └───────────────┘
SELECT dictGet('ipinfo', 'as_name', IPv4StringToNum('1.1.1.1')) AS as_name
-- ┌─as_name──────────┐
-- │ Cloudflare, Inc. │
-- └──────────────────┘
Step 5 – Use with real data
To show how fast ClickHouse dictionaries are, we will load 6.7M IP addresses from the IPv6 Hitlist Service.
To do this, first create the table:
CREATE TABLE ipv6_hitlist (ip IPv6) ENGINE=MergeTree ORDER BY ip
And insert the data (note that ClickHouse can insert data directly from an URL!):
INSERT INTO ipv6_hitlist
SELECT *
FROM url('https://alcatraz.net.in.tum.de/ipv6-hitlist-service/open/responsive-addresses.txt.xz', CSV)
SETTINGS input_format_csv_skip_first_lines = 1
We can then easily lookup countries and autonomous systems:
SELECT
dictGet('ipinfo', 'country_name', ip) AS country,
COUNT(*)
FROM ipv6_hitlist
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
-- ┌─country────────┬─count()─┐
-- │ France │ 2037099 │
-- │ Germany │ 1235990 │
-- │ United States │ 1216283 │
-- │ China │ 379006 │
-- │ Netherlands │ 205644 │
-- │ United Kingdom │ 203483 │
-- │ Japan │ 187566 │
-- │ Russia │ 128275 │
-- │ Brazil │ 110815 │
-- │ Singapore │ 107681 │
-- └────────────────┴─────────┘
-- 10 rows in set. Elapsed: 0.105 sec. Processed 6.79 million rows, 108.69 MB (64.55 million rows/s., 1.03 GB/s.)
SELECT
dictGet('ipinfo', 'as_name', ip) AS as_name,
COUNT(*)
FROM ipv6_hitlist
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
-- ┌─as_name───────────────────────────┬─count()─┐
-- │ Free SAS │ 1880351 │
-- │ DigitalOcean, LLC │ 297871 │
-- │ Deutsche Telekom AG │ 268174 │
-- │ Host Europe GmbH │ 232593 │
-- │ Akamai Connected Cloud │ 192009 │
-- │ Akamai International B.V. │ 146435 │
-- │ Comcast Cable Communications, LLC │ 123381 │
-- │ Deutsche Glasfaser Wholesale GmbH │ 117109 │
-- │ Amazon.com, Inc. │ 103056 │
-- │ OVH SAS │ 98425 │
-- └───────────────────────────────────┴─────────┘
-- 10 rows in set. Elapsed: 0.137 sec. Processed 6.79 million rows, 108.69 MB (49.72 million rows/s., 795.55 MB/s.)
SELECT
dictGet('ipinfo', 'asn', ip) AS asn,
COUNT(*)
FROM ipv6_hitlist
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
-- ┌─asn──────┬─count()─┐
-- │ AS12322 │ 1880351 │
-- │ AS14061 │ 297871 │
-- │ AS3320 │ 268173 │
-- │ AS20773 │ 224090 │
-- │ AS63949 │ 192009 │
-- │ AS20940 │ 145545 │
-- │ AS60294 │ 117109 │
-- │ AS16276 │ 98165 │
-- │ AS16509 │ 93524 │
-- │ AS197540 │ 85089 │
-- └──────────┴─────────┘
-- 10 rows in set. Elapsed: 0.113 sec. Processed 6.79 million rows, 108.69 MB (60.00 million rows/s., 959.99 MB/s.)
6.7M rows processed in 0.1s!
Step 6 – Cleanup
To remove the dictionary:
DROP DICTIONARY ipinfo
To delete the server container:
docker rm -f clickhouse
