CODEWITHSUNDEEP
javasnowflake-cloud-data-platformuser-defined-functionssnowpark
Kaido
Kaido

Reputation: 127

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

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;

Upvotes: 2

Views: 328

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10099

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     |
+--------------+--------+

Upvotes: 1

Related Questions