GeoIP2 Snowflake Java UDF Integration issue

I want to create a Java UDF in a snowflake worksheet in order to query GeoIp2 library and get the ISO code of a given IP. I have ‘@AWS_CSV_STAGE/lib/geoip2-2.8.0.jar’,’@AWS_CSV_STAGE/geodata/GeoLite2-City.mmdb’ already staged. How can i direct the function handler to the method that creates the Database Reader as explained here in the documentation for Java: https://dev.maxmind.com/geoip/geolocate-an-ip/databases?lang=en#1-install-the-geoip2-client-library in general how can i achieve this whole thing below in my udf?

File database = new File("/path/to/maxmind-database.mmdb")
DatabaseReader reader = new DatabaseReader.Builder(database).build();
InetAddress ipAddress = InetAddress.getByName("128.101.101.101");
CityResponse response = reader.city(ipAddress);
Country country = response.getCountry();

so far i wrote this but of course it’s not working: anyway i couldn’t find much material about how to tackle this kind of problem.

CREATE OR REPLACE FUNCTION GEO()
  returns varchar not null
  language java
  imports = ('@AWS_CSV_STAGE/lib/geoip2-2.8.0.jar','@AWS_CSV_STAGE/geodata/GeoLite2-City.mmdb')
  handler = 'DatabaseReader.Builder';

SELECT GEO();

basically what i want to achieve is to call the UDF on a column of ip address table and get the country code in another column for each ip address.

Answer

Thanks for this question – I wrote a whole post going step-by-step to get this working:

• https://hoffa.medium.com/free-ip-address-geolocation-with-maxmind-and-snowflake-676e74ea80b8

You had the right idea in the code you posted, a full working UDF would be:

create or replace function geoip2_country(ip String)
returns string
language java
handler = 'X.x'
imports = ('@fh_jars/geoip2-4.0.0.jar'
         , '@fh_jars/maxmind-db-3.0.0.jar'
         , '@fh_jars/jackson-annotations-2.14.1.jar'
         , '@fh_jars/jackson-core-2.14.1.jar'
         , '@fh_jars/jackson-databind-2.14.1.jar')
as $$
import java.io.File;
import java.net.InetAddress;

import com.snowflake.snowpark_java.types.SnowflakeFile;
import com.maxmind.geoip2.model.*;
import com.maxmind.geoip2.DatabaseReader;
import com.maxmind.geoip2.exception.AddressNotFoundException;

class X {
    DatabaseReader _reader;
    
    public String x(String ip) throws Exception {
        if (null == _reader) {
            // lazy initialization
            _reader = new DatabaseReader.Builder(SnowflakeFile.newInstance("@fh_jars/GeoLite2-Country.mmdb").getInputStream()).build();
        }
        try {
            return _reader.country(InetAddress.getByName(ip)).getCountry().getIsoCode();
        } catch (AddressNotFoundException e) {
            return null;
        }
    }
}
$$;