# Lookup IP geolocation and ASN with ClickHouse and IPinfo's free database

**URL:** <https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149>\
**Category:** Database Downloads\
**Tags:** database, clickhouse\
**Created:** [April 28, 2023, 12:58pm UTC](https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149 "2023-04-28T12:58:48Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Max](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/max/32/113_2.png) [@Max](https://community.ipinfo.io/u/Max)\
**Post date:** [April 28, 2023, 12:58pm UTC](https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149/1 "2023-04-28T12:58:48Z")

</div>

[ClickHouse](https://clickhouse.com/) 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](https://ipinfo.io/account/data-downloads#free) in CSV format
- [IPinfo CLI](https://github.com/ipinfo/cli) to convert IP ranges to CIDR notation
- [Docker](https://docs.docker.com/get-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](https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing).

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:

```bash
ipinfo range2cidr country_asn.csv > country_asn.cidr.csv

```

### Before

```bash
# 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

```bash
# 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](https://hub.docker.com/r/clickhouse/clickhouse-server/):

```bash
docker run -d -it --name clickhouse clickhouse/clickhouse-server

```

To view the server logs run:

```bash
docker logs clickhouse

```

To stop and delete the container run:

```bash
docker rm -f clickhouse

```

## Step 3 – Load the database

To enable efficient IP lookups, the database must be loaded in a [radix tree](https://en.wikipedia.org/wiki/Radix_tree). This is possible with ClickHouse by using a [dictionary](https://clickhouse.com/docs/en/sql-reference/dictionaries). 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`](https://clickhouse.com/docs/en/sql-reference/dictionaries#ip_trie) layout.

Dictionaries can be created from multiple [sources](https://clickhouse.com/docs/en/sql-reference/dictionaries#dictionary-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:

```bash
docker cp country_asn.cidr.csv clickhouse:/var/lib/clickhouse/user_files/

```

Then, start a SQL REPL by running:

```bash
docker exec -it clickhouse clickhouse client

```

And create the dictionary by running the following query:

```sql
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`](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-user_files_path) server settings to know the location of the `user_files` directory.

## Step 4 – Query the database

ClickHouse provides the [`dict*`](https://clickhouse.com/docs/en/sql-reference/functions/ext-dict-functions#dictget-dictgetordefault-dictgetornull) 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.

```auto
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](https://ipv6hitlist.github.io/).

To do this, first create the table:

```sql
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!):

```sql
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:

```sql
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.)

```

```sql
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.)

```

```sql
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:

```sql
DROP DICTIONARY ipinfo

```

To delete the server container:

```bash
docker rm -f clickhouse

```

---

<div class="post-metadata">

**Author:** ![Max](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/max/32/113_2.png) [@Max](https://community.ipinfo.io/u/Max)\
**Post date:** [February 21, 2024, 1:43pm UTC](https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149/4 "2024-02-21T13:43:29Z")

</div>

I recently made the [`mmdb-to-clickhouse`](https://github.com/maxmouchet/mmdb-to-clickhouse) tool to automate the above 🙂

### Example usage

First start a ClickHouse instance:

```bash
docker run --name clickhouse --rm -d -p 9000:9000 clickhouse/clickhouse-server

```

Then download the example MMDB file:

```bash
wget https://github.com/maxmouchet/mmdb-to-clickhouse/raw/main/example.mmdb

```

And run `mmdb-to-clickhouse`:

```bash
./mmdb-to-clickhouse -dsn clickhouse://localhost:9000 -mmdb example.mmdb -name example_mmdb -test

```

The output should look like the following:

```auto
2023/12/21 12:18:10 Schema: network String, country String, partition Date
2023/12/21 12:18:10 Creating example_mmdb_history
2023/12/21 12:18:10 Creating example_mmdb
2023/12/21 12:18:10 Dropping partition 2023-12-21
2023/12/21 12:18:10 Inserted 1 rows
2023/12/21 12:18:10 Running test query: SELECT dictGet('example_mmdb', 'country', IPv6StringToNum('1.1.1.1'))
2023/12/21 12:18:10 Test query result: WW

```

This will create two tables:

- `example_mmdb_history`: a [partitioned](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key) table which keeps the last 30 days of history by default (see the `-ttl` option)
- `example_mmdb`: an in-memory IP trie [dictionary](https://clickhouse.com/docs/en/sql-reference/dictionaries) which always uses the latest partition from `example_mmdb_history`. This dictionary enables very fast IP lookups.

Open a REPL and inspect the tables:

```bash
docker exec -it clickhouse clickhouse client

```

```sql
SHOW TABLES
-- ┌─name─────────────────┐
-- │ example_mmdb │
-- │ example_mmdb_history │
-- └──────────────────────┘

SELECT * FROM example_mmdb_history
-- ┌─network───┬─country─┬──partition─┐
-- │ 0.0.0.0/0 │ WW │ 2023-12-21 │
-- └───────────┴─────────┴────────────┘

SELECT * FROM example_mmdb
-- ┌─network───┬─country─┬──partition─┐
-- │ 0.0.0.0/0 │ WW │ 2023-12-21 │
-- └───────────┴─────────┴────────────┘

SELECT dictGet('example_mmdb', 'country', IPv6StringToNum('1.1.1.1')) AS country
-- ┌─country─┐
-- │ WW │
-- └─────────┘

```

To clean up just remove the ClickHouse instance:

```bash
docker rm -f clickhouse

```

---

<div class="post-metadata">

**Author:** ![Max](https://yyz1.discourse-cdn.com/flex035/user_avatar/community.ipinfo.io/max/32/113_2.png) [@Max](https://community.ipinfo.io/u/Max)\
**Post date:** [July 4, 2024, 12:09pm UTC](https://community.ipinfo.io/t/lookup-ip-geolocation-and-asn-with-clickhouse-and-ipinfos-free-database/149/5 "2024-07-04T12:09:09Z")

</div>

The [latest release](https://github.com/maxmouchet/mmdb-to-clickhouse/releases/tag/v1.2.0) of `mmdb-to-clickhouse` takes advantage of the fact that MMDB files are normalized (duplicate values are stored only once) to significantly reduce the memory usage.

Instead of creating a single `(network, values)` table, it now creates a network-to-pointer table and a pointer-to-values table.

Before, with IPinfo’s free Country + ASN database:

```sql
SELECT name, formatReadableSize(bytes_allocated) bytes_allocated
FROM system.dictionaries
-- ┌─name─────────────┬─bytes_allocated─┐
-- │ example_mmdb │ 1.47 GiB │
-- └──────────────────┴─────────────────┘

```

After:

```sql
SELECT name, formatReadableSize(bytes_allocated) bytes_allocated
FROM system.dictionaries
-- ┌─name─────────────┬─bytes_allocated─┐
-- │ example_mmdb_val │ 22.25 MiB │
-- │ example_mmdb_net │ 260.76 MiB │
-- └──────────────────┴─────────────────┘

```

A UDF is created to perform the double dict lookup transparently:

```sql
SELECT example_mmdb('1.1.1.1', 'as_name') AS as_name
-- ┌─as_name──────────┐
-- │ Cloudflare, Inc. │
-- └──────────────────┘

```

This performs the following query behind the hood:

```sql
SELECT dictGet(
  'example_mmdb_val',
  'as_name',
  dictGet('example_mmdb_net', 'pointer', toIPv6('1.1.1.1'))
)

```

The performance is still excellent at ~47M rows/s on a modest ClickHouse setup (i5-12600H, 4 vCPUs, 4GB RAM):

```sql
SELECT
    country_asn(ip, 'as_name') AS as_name,
    COUNT(*) AS ips
FROM ipv6_hitlist_input
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

-- ┌─as_name───────────────────────────────────────┬───────ips─┐
-- │ Amazon.com, Inc. │ 346948113 │
-- │ CHINANET-BACKBONE │ 269368063 │
-- │ Deutsche Telekom AG │ 147248690 │
-- │ Administracion Nacional de Telecomunicaciones │ 87978571 │
-- │ China Telecom (Group) │ 65301616 │
-- └───────────────────────────────────────────────┴───────────┘

-- 5 rows in set. Elapsed: 41.559 sec. Processed 1.96 billion rows, 31.34 GB (47.13 million rows/s., 754.09 MB/s.)

```