Snowflake null java.lang.NullPointerException ERROR when launching Java UDF

Question

I created a Java UDF in a snowflake in order to query GeoIp2 library and get the ISO code of a column list of IP. I have '@AWS_CSV_STAGE/lib/geoip2-2.8.0.jar','@AWS_CSV_STAGE/geodata/GeoLite2-City.mmdb' and 'maxmind-db-1.2.1.jar' already staged so i included their location with full URL from the AWS_CSV_STAGE in the import option, so i can use the GeoLite2 library and querying the database and get the ISO code (US, NL) for each IP provided. When i save the function it's correctly saved, but when i launch the SELECT on my table i get the error:

User Error Report: Java Stack Trace: java.lang.NullPointerException at function_handler_0//MyUDFHandler.isoCode(InlineCode.java:18) in function GEOLOCATOR with handler MyUDFHandler.isoCode

i have a doubt about the following lines of code if they are really using the database:

URL resource = MyUDFHandler.class.getClassLoader().getResource("GeoLite2-City.mmdb");
File file = new File(resource.toURI());
DatabaseReader reader = new DatabaseReader.Builder(file).build();

code:

create or replace function DB_TEST.MAIN_SCHEMA.GEOLOCATOR(IP VARCHAR)
  returns string not null
  language java
  imports=('@DB_TEST.MAIN_SCHEMA.AWS_CSV_STAGE/geodata/GeoLite2-City.mmdb', '@DB_TEST.MAIN_SCHEMA.AWS_CSV_STAGE/lib/maxmind-db-1.2.1.jar', '@DB_TEST.MAIN_SCHEMA.AWS_CSV_STAGE/lib/geoip2-2.8.0.jar')
  handler='MyUDFHandler.isoCode'
as
$$
import com.maxmind.geoip2.DatabaseReader;
import com.maxmind.geoip2.exception.GeoIp2Exception;
import com.maxmind.geoip2.model.CityResponse;
import com.maxmind.geoip2.record.Country;
import java.io.File;
import java.io.IOException;
import java.io.Reader;
import java.net.InetAddress;
import java.net.URL;
import java.util.HashMap;
import java.util.Map;


class MyUDFHandler {
    public static String isoCode (String ip_address) throws Exception {
        URL resource = MyUDFHandler.class.getClassLoader().getResource("GeoLite2-City.mmdb");
        File file = new File(resource.toURI());
        DatabaseReader reader = new DatabaseReader.Builder(file).build();
        CityResponse response = reader.city(InetAddress.getByName(ip_address));
        return response.getCountry().getIsoCode();
    }
}
 
$$;

SELECT IP, GEOLOCATOR(IP) as GEO_IP FROM DANGEROUS_IPS LIMIT 1;

score 1 · Accepted Answer · answered Dec 07 '22 at 16:52

First of all, thank you for sharing the codes. It helped me to repro the issue. As I see, there are 2 problems here. One is how you access the mmdb (database file), and the second one (occurs when the first one is solved) is missing imports.

Instead of using "getResource" and "toURI", I used System.getProperty("com.snowflake.import_directory") to get the directory path (and build the file path).

Then I also added missing jackson-* JAR files.

Here is the script:

create or replace function  GEOLOCATOR(IP VARCHAR)
  returns string not null
  language java
  imports=('@MYSTAGE/GeoLite2-City.mmdb', '@MYSTAGE/maxmind-db-1.2.1.jar', '@MYSTAGE/geoip2-2.8.0.jar', '@MYSTAGE/jackson-core-2.14.1.jar'
          , '@MYSTAGE/jackson-databind-2.14.1.jar', '@MYSTAGE/jackson-annotations-2.14.1.jar')
  handler='MyUDFHandler.isoCode'
as
$$
import com.maxmind.geoip2.DatabaseReader;
import com.maxmind.geoip2.exception.GeoIp2Exception;
import com.maxmind.geoip2.model.CityResponse;
import com.maxmind.geoip2.record.Country;

    
import java.io.File;
import java.io.IOException;
import java.io.Reader;
import java.net.InetAddress;
import java.net.URL;
import java.util.HashMap;
import java.util.Map;


class MyUDFHandler {
    public static String isoCode (String ip_address) throws Exception {
        String importDirectory = System.getProperty("com.snowflake.import_directory");
        String fPath = importDirectory + "GeoLite2-City.mmdb";
        File file = new File( fPath ) ;
        DatabaseReader reader = new DatabaseReader.Builder(file).build();
        CityResponse response = reader.city(InetAddress.getByName(ip_address));
        return response.getCountry().getIsoCode();
    }
}
 
$$;
   
SELECT IP, GEOLOCATOR(IP) as GEO_IP FROM VALUES('62.45.42.100') tmp(IP) ;

+--------------+--------+
|      IP      | GEO_IP |
+--------------+--------+
| 62.45.42.100 | NL     |
+--------------+--------+

Great answer - now, you could get better performance at scale with lazy initialization (just parse the `mmdb` once): https://hoffa.medium.com/free-ip-address-geolocation-with-maxmind-and-snowflake-676e74ea80b8 — Felipe Hoffa, Dec 14 '22 at 21:07

Snowflake null java.lang.NullPointerException ERROR when launching Java UDF

1 Answers1