Retrieve geo ip data from MMDB database
Maintainer(s):
william-billaud
Installing and Loading
INSTALL duckdb_geoip_rs FROM community;
LOAD duckdb_geoip_rs;
Example
-- Before, export mmdb path : export MAXMIND_MMDB_DIR="`pwd`"
-- 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 │ CLOUDFLARENET │ 13335 │ │ │
│ 8.8.8.8 │ GOOGLE │ 15169 │ │ US │
│ 80.8.8.8 │ Orange │ 3215 │ │ RE │
│ 90.9.250.1 │ Orange │ 3215 │ Lyon │ FR │
│ not_anip │ │ │ │ │
└────────────┴───────────────────┴───────────────────┴────────────────┴───────────────────────┘
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 depends on the inet core extensions, which will be automatically installed, except if you do not have access to the internet.
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 |