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.
Advertisement
Answer
Thanks for this question – I wrote a whole post going step-by-step to get this working:
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;
}
}
}
$$;