How to get a variant/object out of a Java UDF in Snowflake? (in this case, while geocoding IP addresses)

Question

This post has a class that can output a String with the geolocation of an address:

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

The relevant part is:

public String x(String ip) throws Exception {

   CityResponse r = _reader.city(InetAddress.getByName(ip));
   return r.getCity().getName() + ", " + r.getMostSpecificSubdivision().getIsoCode() + ", "+ r.getCountry().getIsoCode();
}

But I want to return a variant instead with all the information. How can I do that?

Answer 1

You can ask the UDF to return variant and within the Java code just return a JSON string, as in:

CityResponse r = _reader.city(InetAddress.getByName(ip));
return r.toJson();

My teammate Steven Maser wrote this solution, thanks to it you can ask the UDF for all the details and parse the results as needed in SQL, as in:

select geoip2_all('156.33.241.5');
select geoip2_all('156.33.241.5'):city:names:en::varchar;

Full UDF code:

create or replace function geoip2_all(ip String)
returns variant
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-City.mmdb").getInputStream()).build();
        }
        try {
            CityResponse r = _reader.city(InetAddress.getByName(ip));
            return r.toJson();
        } catch (AddressNotFoundException e) {
            return null;
        }
    }
}
$$;