English

duckdb_geoip_rs

Downloads

772

GitHub stars

8

Extension repository on GitHub

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`"


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.