Snowflake Java UDF开发指南：解决文件访问与依赖管理中的常见陷阱

// 错误的文件路径解析方式
URL resource = MyUDFHandler.class.getClassLoader().getResource("GeoLite2-City.mmdb");
File file = new File(resource.toURI()); // 此处resource可能为null，导致NullPointerException
DatabaseReader reader = new DatabaseReader.Builder(file).build();

import java.io.File;
import com.maxmind.geoip2.DatabaseReader;
import java.net.InetAddress;
import com.maxmind.geoip2.model.CityResponse;

class MyUDFHandler {
    public static String isoCode (String ip_address) throws Exception {
        // 获取导入文件所在的目录路径
        String importDirectory = System.getProperty("com.snowflake.import_directory");
        // 构建GeoLite2-City.mmdb文件的完整路径
        String fPath = importDirectory + "GeoLite2-City.mmdb";
        File file = new File( fPath ); // 现在可以正确创建File对象

        // 使用DatabaseReader加载数据库
        DatabaseReader reader = new DatabaseReader.Builder(file).build();
        // 查询IP地址信息并返回ISO国家代码
        CityResponse response = reader.city(InetAddress.getByName(ip_address));
        return response.getCountry().getIsoCode();
    }
}

-- 示例：上传文件到名为MYSTAGE的阶段
-- 请将本地路径替换为实际文件路径，并确保版本与您的项目兼容
PUT file:///path/to/GeoLite2-City.mmdb @MYSTAGE/GeoLite2-City.mmdb;
PUT file:///path/to/maxmind-db-1.2.1.jar @MYSTAGE/maxmind-db-1.2.1.jar;
PUT file:///path/to/geoip2-2.8.0.jar @MYSTAGE/geoip2-2.8.0.jar;
PUT file:///path/to/jackson-core-2.14.1.jar @MYSTAGE/jackson-core-2.14.1.jar;
PUT file:///path/to/jackson-databind-2.14.1.jar @MYSTAGE/jackson-databind-2.14.1.jar;
PUT file:///path/to/jackson-annotations-2.14.1.jar @MYSTAGE/jackson-annotations-2.14.1.jar;

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.net.InetAddress;


class MyUDFHandler {
    public static String isoCode (String ip_address) throws Exception {
        // 获取Snowflake UDF运行时导入文件的目录
        String importDirectory = System.getProperty("com.snowflake.import_directory");
        // 构建GeoLite2-City.mmdb文件的完整路径
        String fPath = importDirectory + "GeoLite2-City.mmdb";
        File file = new File( fPath );

        // 创建DatabaseReader实例
        DatabaseReader reader = new DatabaseReader.Builder(file).build();

        // 查询IP地址信息并返回ISO国家代码
        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'), ('8.8.8.8') tmp(IP);