DuckDB IP Geolocation in SQL with IPGeolocation.io Databases

Overview

DuckDB can join IP intelligence onto your data with nothing but SQL. A community extension adds two functions that read IPGeolocation.io MMDB files: one looks up a single address per row, the other turns a whole database into a table. Point them at a CSV export, a folder of Parquet logs or a table you already have, and you get country, city, VPN and Tor flags, threat scores and network owners alongside your own columns.

The examples combine three IPGeolocation.io databases. The IP Geolocation Database places each address in a country, region and city, with coordinates and a time zone. The IP Security Database flags VPNs, proxies and Tor and provides a threat score of each address. The IP to ASN Database identifies the network, by its Autonomous System Number and owner.

Nothing in the extension is specific to those three. You can query the IP to Country, IP to City and IP to ASN databases, the IP to Company Database, IP Abuse Contact Database, IP WHOIS Database, IP to Hosting Database and Residential Proxy Database in exactly the same way, along with combined databases.

DuckDB runs inside your process, and the databases are local files, so analysis needs no API key, no rate limit and no network access once the extension is installed. For when a hosted API is the better fit, see choosing between an IP geolocation API and a database.

Two ways to query a database

FunctionKindUse it to
mmdb_record(file, ip, key) ScalarAdd data for the address in each row of your own table or file.
read_mmdb(file) TableRead the database itself, one row per network, to filter, count or export it.

Questions you can answer

  • Where do our sign-in attempts come from? Group a login log by country and network owner.
  • How much of our traffic is anonymized? Count requests from VPN, proxy and Tor addresses, per day or per endpoint.
  • Which hosting networks hit our API? Join firewall or API logs with network owners to find automated sources.
  • Which Tor exit networks fall inside a given range? Scan the IP Security Database for one subnet or for all of them.
  • Can this dataset be shared enriched? Write the enriched result to Parquet for a data warehouse or a notebook.

About DuckDB and the extension

DuckDB is an in-process analytical database. It reads CSV, Parquet and JSON files directly, runs in a command-line shell or inside Python, R, Java and other languages, and needs no server.

The IPGeolocation.io lookups come from the open source community extension named maxmind . Of its functions, mmdb_record and read_mmdb are generic MMDB readers, and they are the ones to use with IPGeolocation.io files. DuckDB downloads the extension from its community repository the first time you install it, and every session loads it with LOAD .

How a lookup comes back

mmdb_record returns one top-level key of the record, wrapped in a JSON object. These are real results for 37.120.202.92 :

CallResult
mmdb_record('databases/db-ip-security.mmdb', '37.120.202.92', 'is_vpn') {"is_vpn":"true"}
mmdb_record('databases/db-ip-security.mmdb', '37.120.202.92', 'threat_score') {"threat_score":50}
mmdb_record('databases/db-ip-asn.mmdb', '37.120.202.92', 'asn') {"asn":{"as_number":"9009","country_code":"RO","domain":"m247.com","organization":"M247 Europe SRL","type":"HOSTING"}}

DuckDB's ->> operator reads a value out of that JSON as text. mmdb_record(..., 'asn') ->> '$.asn.organization' returns M247 Europe SRL . Security flags arrive as the text true or false , scores as JSON numbers, and provider names as JSON arrays.

Requirements

ComponentDetails
DuckDB1.5.6 (tested), as the CLI or any client that can install community extensions. Python was tested too.
maxmind extensionv0.10.0 (tested), installed with INSTALL maxmind FROM community .
IPGeolocation.io databasesThe MMDB edition of each database you want to query, downloaded from your IPGeolocation.io account. Database plans are listed on the IP database pricing page.

Quick start

Enrich a six-line access log in the DuckDB shell.

1. Step 1: Lay out the files

Create a working folder with the three MMDB files in a databases subfolder:

Response Preview
1databases/
2├── db-ip-asn.mmdb
3├── db-ip-location.mmdb
4└── db-ip-security.mmdb
5access_log.csv

2. Step 2: Create a sample log

Save this as access_log.csv :

Response Preview
1ts,client_ip,method,path,status
22026-10-06 10:15:32,37.120.202.92,POST,/login,401
32026-10-06 10:15:40,5.45.96.188,POST,/login,401
42026-10-06 10:16:02,131.229.141.0,GET,/,200
52026-10-06 10:16:09,5.9.12.205,GET,/pricing,200
62026-10-06 10:16:15,2001:1540::,GET,/,200
72026-10-06 10:16:21,203.0.113.10,GET,/,200

3. Step 3: Run the query

Start duckdb in the working folder and run:

INSTALL maxmind FROM community;
LOAD maxmind;

SELECT
    client_ip,
    path,
    mmdb_record('databases/db-ip-location.mmdb', client_ip, 'location')
        ->> '$.location.country.code2'                                   AS country,
    mmdb_record('databases/db-ip-location.mmdb', client_ip, 'location')
        ->> '$.location.city.name.en'                                    AS city,
    (mmdb_record('databases/db-ip-security.mmdb', client_ip, 'is_anonymous')
        ->> '$.is_anonymous') = 'true'                                   AS anonymous,
    (mmdb_record('databases/db-ip-security.mmdb', client_ip, 'threat_score')
        ->> '$.threat_score')::INTEGER                                   AS threat_score,
    mmdb_record('databases/db-ip-asn.mmdb', client_ip, 'asn')
        ->> '$.asn.organization'                                         AS network
FROM read_csv('access_log.csv')
ORDER BY threat_score DESC NULLS LAST;

4. Step 4: Read the result

Response Preview
1┌───────────────┬──────────┬─────────┬─────────────┬───────────┬──────────────┬─────────────────────┐
2│   client_ip   │   path   │ country │    city     │ anonymous │ threat_score │       network       │
3│    varchar    │ varchar  │ varchar │   varchar   │  boolean  │    int32     │       varchar       │
4├───────────────┼──────────┼─────────┼─────────────┼───────────┼──────────────┼─────────────────────┤
5│ 5.45.96.188   │ /login   │ DE      │ Nuremberg   │ true      │           80 │ netcup GmbH         │
6│ 37.120.202.92 │ /login   │ US      │ Secaucus    │ true      │           50 │ M247 Europe SRL     │
7│ 5.9.12.205    │ /pricing │ DE      │ Falkenstein │ false     │            5 │ Hetzner Online GmbH │
8│ 2001:1540::   │ /        │ NL      │ Amsterdam   │ false     │            5 │ Equinix, Inc.       │
9│ 131.229.141.0 │ /        │ US      │ Ashburn     │ false     │            0 │ Skyhigh Security    │
10│ 203.0.113.10  │ /        │ NULL    │ NULL        │ NULL      │         NULL │ NULL                │
11└───────────────┴──────────┴─────────┴─────────────┴───────────┴──────────────┴─────────────────────┘

Both failed sign-ins came from anonymizing networks: a Tor exit in a Nuremberg data center and a VPN exit hosted by M247. The IPv6 address works like any other, and 203.0.113.10 , a documentation address, has no record, so its columns are NULL . Values change as the databases are updated. The ASN browser entry for AS9009 shows more about the VPN's network.

This direct form is fine for small files. For large ones, use the pattern in " Enrich a large log file" below.

Reference

1. Functions

FunctionArgumentsReturns
mmdb_record Database file, IP address, top-level keyThe key and its value as JSON text, or NULL
read_mmdb Database file; optional network := 'CIDR' A table with network and record (the full record as JSON text)

2. What mmdb_record returns

SituationResult
The address has a record and the key existsJSON such as {"is_vpn":"true"}
The key does not exist at the top level, for example 'location.country.code2' {}
The address has no record NULL
The IP argument is NULL , empty, a list such as 203.0.113.7, 10.0.0.1 , or has a port NULL
The file cannot be found Invalid Input Error: FileNotFound

3. Reading values out of the JSON

NeedExpression
Text mmdb_record(f, ip, 'location') ->> '$.location.city.name.en'
Boolean (mmdb_record(f, ip, 'is_vpn') ->> '$.is_vpn') = 'true'
Integer (mmdb_record(f, ip, 'threat_score') ->> '$.threat_score')::INTEGER
Coordinate (mmdb_record(f, ip, 'location') ->> '$.location.coordinates.latitude')::DOUBLE
First list entry mmdb_record(f, ip, 'vpn_provider_names') ->> '$.vpn_provider_names[0]'
Whole list as VARCHAR[] from_json(mmdb_record(f, ip, 'vpn_provider_names') -> '$.vpn_provider_names', '["VARCHAR"]')

Names are stored in several languages. Change en in a path to cs , de , es , fa , fr , it , ja , ko , pt , ru or zh ; a name without a translation is an empty string.

4. An expression for each database

Here f is the database file and ip the address column:

DatabaseSample expression
IP Geolocation Database or IP to City Database mmdb_record(f, ip, 'location') ->> '$.location.city.name.en'
IP to Country Database mmdb_record(f, ip, 'location') ->> '$.location.country.code2'
IP Security Database mmdb_record(f, ip, 'is_vpn') ->> '$.is_vpn'
IP to ASN Database mmdb_record(f, ip, 'asn') ->> '$.asn.as_number'
IP to Company Database mmdb_record(f, ip, 'company') ->> '$.company.name.en'
IP to ISP Database mmdb_record(f, ip, 'isp') ->> '$.isp'
IP Abuse Contact Database mmdb_record(f, ip, 'abuse') ->> '$.abuse.emails'
IP WHOIS Database mmdb_record(f, ip, 'whois') ->> '$.whois.rir'
IP to Hosting Database mmdb_record(f, ip, 'hosting_provider') ->> '$.hosting_provider'
Residential Proxy Database mmdb_record(f, ip, 'proxy_provider') ->> '$.proxy_provider'

In the IP to ISP Database, the AS number is a top-level key of its own ( mmdb_record(f, ip, 'asn') ->> '$.asn' ), and so is the country ( 'country' ). An ISP and the AS that routes its traffic are not always the same organization; how an ISP differs from an ASN explains the difference.

The IP Security Database documentation describes every security key, including the residential proxy and relay flags and the confidence scores.

5. Reusable macros

Macros keep long queries readable. Define them once per session, or put them in a file and load it with .read :

CREATE OR REPLACE MACRO ipgeo_location(ip) AS
    mmdb_record('databases/db-ip-location.mmdb', ip, 'location')::JSON -> 'location';

CREATE OR REPLACE MACRO ipgeo_security(ip, field) AS
    mmdb_record('databases/db-ip-security.mmdb', ip, field) ->> ('$.' || field);

CREATE OR REPLACE MACRO ipgeo_asn(ip) AS
    mmdb_record('databases/db-ip-asn.mmdb', ip, 'asn')::JSON -> 'asn';

With them, the quick start columns become one-liners:

SELECT
    client_ip,
    ipgeo_location(client_ip) ->> '$.country.code2'      AS country,
    ipgeo_security(client_ip, 'is_vpn') = 'true'         AS is_vpn,
    ipgeo_security(client_ip, 'threat_score')::INTEGER   AS threat_score,
    ipgeo_asn(client_ip) ->> '$.as_number'               AS asn
FROM read_csv('access_log.csv');

Relative file paths resolve from the folder DuckDB runs in. In scripts and scheduled jobs, use absolute paths.

Recipes

1. Enrich a large log file

Logs repeat the same addresses many times. Look each distinct address up once, store the results in a table, and join them back. This uses the macros above:

CREATE OR REPLACE TABLE ip_geo AS
SELECT
    client_ip,
    ipgeo_location(client_ip) ->> '$.country.code2'      AS country,
    ipgeo_location(client_ip) ->> '$.city.name.en'       AS city,
    ipgeo_security(client_ip, 'is_anonymous') = 'true'   AS anonymous,
    ipgeo_security(client_ip, 'threat_score')::INTEGER   AS threat_score
FROM (SELECT DISTINCT client_ip FROM read_parquet('logs/*.parquet'));

COPY (
    SELECT logs.*, g.country, g.city, g.anonymous, g.threat_score
    FROM read_parquet('logs/*.parquet') AS logs
    LEFT JOIN ip_geo AS g USING (client_ip)
) TO 'enriched.parquet' (FORMAT parquet);

The LEFT JOIN keeps rows whose address has no record. Swap read_parquet for read_csv , or for a table name, to match your source.

2. Summarize traffic by country

Once the data is enriched, ordinary SQL answers the questions:

SELECT
    country,
    count(*)                                                       AS requests,
    round(100.0 * count(*) FILTER (WHERE anonymous) / count(*), 1) AS anonymous_pct
FROM 'enriched.parquet'
GROUP BY country
ORDER BY requests DESC
LIMIT 10;

3. Scan a whole database

read_mmdb returns one row per network, with the full record as JSON in the record column.

Every Tor exit network in the IP Security Database:

SELECT network
FROM read_mmdb('databases/db-ip-security.mmdb')
WHERE record ->> '$.is_tor' = 'true';

Networks per country in the IP Geolocation Database:

SELECT record ->> '$.location.country.code2' AS country, count(*) AS networks
FROM read_mmdb('databases/db-ip-location.mmdb')
GROUP BY country
ORDER BY networks DESC;

A full database holds millions of networks, so a complete scan takes a while. To read one range only, pass it as network :

SELECT network, record ->> '$.is_vpn' AS is_vpn
FROM read_mmdb('databases/db-ip-security.mmdb', network := '37.120.202.0/24');

For that range, the result starts with 37.120.202.0/30 , 37.120.202.7/32 and 37.120.202.8/29 , all with is_vpn set to true .

4. From Python

The same SQL runs through DuckDB's Python package:

import duckdb

con = duckdb.connect()
con.sql("INSTALL maxmind FROM community")
con.sql("LOAD maxmind")

rows = con.sql("""
    SELECT client_ip,
           mmdb_record('databases/db-ip-location.mmdb', client_ip, 'location')
               ->> '$.location.country.code2' AS country
    FROM read_csv('access_log.csv')
""").fetchall()

for client_ip, country in rows:
    print(client_ip, country)

With the quick start files, it prints 37.120.202.92 US , 5.45.96.188 DE and so on, and 203.0.113.10 None for the address without a record.

Keeping the databases up to date

Depending on your plan, IPGeolocation.io publishes new releases every day or every week. DuckDB needs no reload: in testing, the next query after a file was replaced already returned the new data, even in the same session.

A release is a ZIP archive. Inside are the MMDB files of your plan, a README.md and a checksum.txt with one SHA-256 hash per file. To install one, unpack it beside the current files, confirm the hashes and the databases, and rename the new files over the old ones, so a query never reads a half-written file. The script below does exactly that with curl , unzip , sha256sum and the mmdbio command-line tool. Fill in DOWNLOAD_URL with the MMDB download link from your IPGeolocation.io account:

#!/bin/sh
# Install a new IPGeolocation.io database release for DuckDB queries.
set -eu

DB_DIR=/data/ipgeolocation
DOWNLOAD_URL="<MMDB download link from your IPGeolocation.io account>"

# 1. Unpack the release in a temporary folder beside the live databases.
WORK=$(mktemp -d "$DB_DIR/.release.XXXXXX")
trap 'rm -rf "$WORK"' EXIT
curl -fsSL -o "$WORK/release.zip" "$DOWNLOAD_URL"
# -DD stamps the unpacked files with the current time, not the archive's.
unzip -q -DD "$WORK/release.zip" -d "$WORK"
rm "$WORK/release.zip"

# 2. Keep the current files if a hash or a database check fails.
(cd "$WORK" && sha256sum --quiet -c checksum.txt)
for db in "$WORK"/*.mmdb; do
    mmdbio verify --db "$db"
done

# 3. Swap the databases in. The next query reads the new release.
for db in "$WORK"/*.mmdb; do
    mv "$db" "$DB_DIR/"
done

The archive keeps the same file names in every release, so the paths in your queries and macros stay the same.

Enriched tables and Parquet files keep the values from the release they were built with. Rebuild them when you need current data.

Troubleshooting

Catalog Error: Scalar Function with name mmdb_record does not exist! The extension is not loaded in this session. Run LOAD maxmind; , after INSTALL maxmind FROM community; if it was never installed.

Binder Error: No function matches the given name and argument types 'mmdb_record(STRING_LITERAL, STRING_LITERAL)' . mmdb_record needs three arguments: the file, the address and a top-level key.

mmdb_record returns {} . The key is not a top-level key. Pass the first part only, such as 'location' , and read the rest with ->> '$.location.country.code2' .

mmdb_record returns NULL . The address has no record, or the value is not a single valid IP address. Check an address with mmdbio:

mmdbio read --db databases/db-ip-location.mmdb --ip 37.120.202.92

Invalid Input Error: FileNotFound . The path to the database is wrong. Relative paths start from the folder DuckDB runs in.

DuckDB exits with Segmentation fault . This happened in testing when mmdb_record ran on every row of a large file read directly with read_csv or read_parquet . Look up distinct addresses into a table first, as in " Enrich a large log file".

Enriched results show old values. They were built from an earlier release. Rerun the enrichment after you install a new file.

FAQ

No. DuckDB runs inside the shell or your program, and the databases are ordinary files next to it.
No. Lookups read the local MMDB files. The only network access is the one-time extension download from DuckDB's community repository, which does not include your data.
All IP databases in their MMDB edition, and combined databases too. An expression for each database above has a starting point for each.
Yes. The quick start includes one, and the databases cover IPv6 alongside IPv4.
Yes. read_mmdb turns a database into a table, so a WHERE clause can find, for example, every Tor exit network.
No. The next query reads the new file. Rebuild any tables or files you enriched earlier.

Related

Subscribe to Our Newsletter

Get the latest in geolocation tech, straight to your inbox.