Retrieve geo ip data from MMDB database
Maintainer(s):
william-billaud
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL duckdb_geoip_rs FROM community;
LOAD duckdb_geoip_rs;
Example
-- Before, export mmdb path : export MAXMIND_MMDB_DIR="`pwd`"
INSTALL 'duckdb_geoip_rs' FROM community;
LOAD 'duckdb_geoip_rs';
-- Create sample data
CREATE TABLE ip_list (ip VARCHAR);
INSERT INTO ip_list VALUES ('1.1.1.1'), ('8.8.8.8'), ('80.8.8.8'), ('90.9.250.1'), ('not_anip');
-- Get associated geo ip data
SELECT ip, geoip_asn_org(ip),geoip_asn_num(ip),geoip_city(ip), geoip_country_iso(ip) from ip_list;
┌────────────┬───────────────────┬───────────────────┬────────────────┬───────────────────────┐
│ ip │ geoip_asn_org(ip) │ geoip_asn_num(ip) │ geoip_city(ip) │ geoip_country_iso(ip) │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────┼───────────────────┼───────────────────┼────────────────┼───────────────────────┤
│ 1.1.1.1 │ Cloudflare, Inc. │ 13335 │ │ AU │
│ 8.8.8.8 │ Google LLC │ 15169 │ │ US │
│ 80.8.8.8 │ Orange │ 3215 │ Le Tampon │ RE │
│ 90.9.250.1 │ Orange │ 3215 │ Lyon │ FR │
│ not_anip │ NULL │ NULL │ NULL │ NULL │
└────────────┴───────────────────┴───────────────────┴────────────────┴───────────────────────┘
About duckdb_geoip_rs
Path to a directory containing GeoLite2-City.mmdb and GeoLite2-ASN.mmdb files must be exported to MAXMIND_MMDB_DIR environment variable. Defaulting to /usr/share/GeoIP.
This extension export 4 function using MaxminDB database:
- geoip_asn_org(ip : VARCHAR)-> VARCHAR
- geoip_asn_num(ip : VARCHAR)-> VARCHAR
- geoip_city(ip : VARCHAR)-> VARCHAR
- geoip_country_iso(ip : VARCHAR) -> VARCHAR
All the function will return an empty value on empty/non found value
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| geoip_asn_num | scalar | NULL | NULL | |
| geoip_asn_org | scalar | NULL | NULL | |
| geoip_city | scalar | NULL | NULL | |
| geoip_country_iso | scalar | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.