一个查询ip归属地的hive udf

背景

工作中会用到查询ip归属地的场景，购买了ip库数据，因官方不提供数据遍历方案，所以无法全量导入到hive中，只能使用udf进行查询。

数据源来自https://www.ipip.net/，数据格式为.ipdb，如：ipv4_china_cn.ipdb。

udf编码

maven pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.companya.bd</groupId>
    <artifactId>companyaudf</artifactId>
    <version>1.0</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>1.2.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.zookeeper</groupId>
                    <artifactId>zookeeper</artifactId>
                </exclusion>
<!--                <exclusion>-->
<!--                    <groupId>org.apache.hadoop</groupId>-->
<!--                    <artifactId>hadoop-hdfs</artifactId>-->
<!--                </exclusion>-->
                <exclusion>
                    <groupId>org.apache.httpcomponents</groupId>
                    <artifactId>httpcore</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.httpcomponents</groupId>
                    <artifactId>httpclient</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.logging.log4j</groupId>
                    <artifactId>log4j-1.2-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.logging.log4j</groupId>
                    <artifactId>log4j-slf4j-impl</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>net.ipip</groupId>
            <artifactId>ipdb</artifactId>
            <version>1.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.6.0</version>
            <scope>test</scope>
        </dependency>-->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.8.1</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <!--这部分可有可无,加上的话则直接生成可运行jar包-->
                    <!--<archive>-->
                    <!--<manifest>-->
                    <!--<mainClass>${exec.mainClass}</mainClass>-->
                    <!--</manifest>-->
                    <!--</archive>-->
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>

        </plugins>
    </build>
</project>

编写udf

继承UDF类，重写evaluate方法：

package com.companya.bd.hive.companyaudf;

import net.ipip.ipdb.City;
import net.ipip.ipdb.CityInfo;
import net.ipip.ipdb.IPFormatException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FSDataInputStream;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;

import java.io.IOException;
import java.net.URI;
import java.util.ArrayList;

/**
 * @program: companyaudtf
 * @author: MuChen
 * @create: 2021-06-30 19:24
 **/
@Description(
    name = "ip2loc",
    value = "_FUNC_(str) - covert ip to location",
    extended = "Example:\n" +
            "  > SELECT ip2loc(ip) FROM ips;\n" +
            "  [中国,广东,东莞]")
public class IPToLocation extends UDF {
//    private static final InputStream stream = IPToLocation.class.getClassLoader().getResourceAsStream("hdfs:///upload/udf_data/ipv4_china_cn.ipdb");
    private static String dsf = "hdfs:///upload/udf_data/ipv4_china_cn.ipdb";
    private static Configuration conf = new Configuration();
    private static FileSystem fs;
    private static FSDataInputStream hdfsInStream;
    private static City db = null;

    static {
        try {
            fs = FileSystem.get(URI.create(dsf),conf);
            hdfsInStream = fs.open(new Path(dsf));
            db = new City(hdfsInStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public ArrayList<String> evaluate(String ip) throws IOException, IPFormatException {
        ArrayList<String> allTexts = new ArrayList<>();
        if (ip != null) {
            CityInfo info = db.findInfo(ip, "CN");
            allTexts.add(info.getCountryName());
            allTexts.add(info.getRegionName());
            allTexts.add(info.getCityName());
        }
        return allTexts;
    }

}

打包

mvn clean compile assembly:single -DskipTests  mvn clean compile assembly:single -DskipTests

udf部署

上传jar到hdfs

hadoop fs -put companyaudf.jar /lib

创建hive函数

create function ip2loc as 'com.companya.bd.hive.companyaudf.IPToLocation' USING JAR 'hdfs:///lib/companyaudf.jar';

使用udf

查看函数描述

函数描述：

hive> describe function ip2loc;
OK
ip2loc(str) - covert ip to location
Time taken: 0.189 seconds, Fetched: 1 row(s)

函数扩展表述：

hive> describe function extended ip2loc;
OK
ip2loc(str) - covert ip to location
Synonyms: default.ip2loc
Example:
  > SELECT ip2loc(ip) FROM ips;
  [中国,广东,东莞]
Function class:com.companya.bd.hive.companyaudf.IPToLocation

使用udf

-- 返回值为国家、省、市组成的数组
hive> select ip2loc('1.2.3.4');
OK
["中国","广东","东莞"]

-- 只取数组返回值中的城市部分
hive> select ip2loc('1.2.3.4')[2];
OK
东莞