Convert IP Addresses to Geolocations via MaxCompute UDF - MaxCompute - Alibaba Cloud - MaxCompute

Modern data platforms can process diverse types of unstructured and semi-structured data. A common use case is converting IP addresses to geolocations. This topic describes how to use a MaxCompute user-defined function (UDF) to convert IPv4 or IPv6 addresses to geolocations.

Background information

To convert IPv4 or IPv6 addresses to geolocations, you need an IP address library. You must download the IP address library files and upload them as resources to your MaxCompute project. Then, you can develop a MaxCompute UDF, register it using the IP address library files, and call the function in SQL statements to convert IP addresses to geolocations.

Important

The IP address library files provided in this topic are for demonstration purposes only. You must maintain your own IP address library based on your business requirements.

Prerequisites

You have installed and configured the MaxCompute client.
You have installed MaxCompute Studio.

Procedure

Follow these steps to use a MaxCompute UDF to convert IPv4 or IPv6 addresses to geolocations:

Step 1: Upload the IP address library files

Upload the IP address library files as resources to your MaxCompute project. The MaxCompute UDF that you will create depends on these resources.
Step 2: Create a project connection

Connect to a MaxCompute project and create a MaxCompute Java module.
Step 3: Write the MaxCompute UDF

Write the MaxCompute UDF code in IntelliJ IDEA.
Step 4: Register the MaxCompute UDF

Register the MaxCompute UDF.
Step 5: Call the MaxCompute UDF to convert IP addresses to geolocations

Call the registered function in an SQL statement to convert IP addresses to geolocations.

Step 1: Upload the IP address library files

Download the IP address library files to your local machine, decompress the package to get ipv4.txt and ipv6.txt, and place them in the ...\odpscmd_public\bin installation directory of the MaxCompute client.
Install and log on to the MaxCompute client, and then switch to your target MaxCompute project.
Run the add file command to upload ipv4.txt and ipv6.txt as file resources to the MaxCompute project.
```
ADD file ipv4.txt -f;
ADD file ipv6.txt -f;
```
For more information about how to add resources, see Add resources.
(For local debugging) Copy ipv4.txt and ipv6.txt to the warehouse/example_project/_resources_ directory of your local project.

Step 2: Create a project connection

Connect to a MaxCompute project. For more information, see Manage project connections.
Create a MaxCompute Java module. For more information, see Create a MaxCompute Java module.

Step 3: Write the MaxCompute UDF

Create Java classes.

The UDF code in the following steps uses these Java classes.

In IntelliJ IDEA, in the Project pane, right-click the module source directory (src > main > java) and choose New > Java Class.

In the New Java Class dialog box, enter the class name, press Enter, and then enter the code in the code editor.

Create the following three Java classes in order. You can use the provided code without modification.

IpUtils

package com.aliyun.odps.udf.utils;
import java.math.BigInteger;
import java.net.Inet4Address;
import java.net.Inet6Address;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.Arrays;
public class IpUtils {
    /**
     * Converts an IP address from a string to a long.
     *
     * @param ipInString
     * The IP address as a string.
     * @return The IP address as a long.
     */
    public static long StringToLong(String ipInString) {
        ipInString = ipInString.replace(" ", "");
        byte[] bytes;
        if (ipInString.contains(":"))
            bytes = ipv6ToBytes(ipInString);
        else
            bytes = ipv4ToBytes(ipInString);
        BigInteger bigInt = new BigInteger(bytes);
//        System.out.println(bigInt.toString());
        return bigInt.longValue();
    }
    /**
     * Converts an IP address from a string to a BigInteger string.
     *
     * @param ipInString
     * The IP address as a string.
     * @return A BigInteger representation of the IP address as a string.
     */
    public static String StringToBigIntString(String ipInString) {
        ipInString = ipInString.replace(" ", "");
        byte[] bytes;
        if (ipInString.contains(":"))
            bytes = ipv6ToBytes(ipInString);
        else
            bytes = ipv4ToBytes(ipInString);
        BigInteger bigInt = new BigInteger(bytes);
        return bigInt.toString();
    }
    /**
     * Converts an IP address from a BigInteger to a string.
     *
     * @param ipInBigInt
     * The IP address as a BigInteger.
     * @return The IP address as a string.
     */
    public static String BigIntToString(BigInteger ipInBigInt) {
        byte[] bytes = ipInBigInt.toByteArray();
        byte[] unsignedBytes = Arrays.copyOfRange(bytes, 1, bytes.length);
        // Remove the sign bit.
        try {
            String ip = InetAddress.getByAddress(unsignedBytes).toString();
            return ip.substring(ip.indexOf('/') + 1).trim();
        } catch (UnknownHostException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * Converts an IPv6 address to a signed byte[17] array.
     */
    private static byte[] ipv6ToBytes(String ipv6) {
        byte[] ret = new byte[17];
        ret[0] = 0;
        int ib = 16;
        boolean comFlag = false;// Flag for IPv4-mapped addresses.
        if (ipv6.startsWith(":"))// Remove the leading colon.
            ipv6 = ipv6.substring(1);
        String groups[] = ipv6.split(":");
        for (int ig = groups.length - 1; ig > -1; ig--) {// Scan in reverse.
            if (groups[ig].contains(".")) {
                // An IPv4-mapped address is found.
                byte[] temp = ipv4ToBytes(groups[ig]);
                ret[ib--] = temp[4];
                ret[ib--] = temp[3];
                ret[ib--] = temp[2];
                ret[ib--] = temp[1];
                comFlag = true;
            } else if ("".equals(groups[ig])) {
                // Zero-length compression is found. Calculate the number of missing groups.
                int zlg = 9 - (groups.length + (comFlag ? 1 : 0));
                while (zlg-- > 0) {// Set these groups to 0.
                    ret[ib--] = 0;
                    ret[ib--] = 0;
                }
            } else {
                int temp = Integer.parseInt(groups[ig], 16);
                ret[ib--] = (byte) temp;
                ret[ib--] = (byte) (temp >> 8);
            }
        }
        return ret;
    }
    /**
     * Converts an IPv4 address to a signed byte[5] array.
     */
    private static byte[] ipv4ToBytes(String ipv4) {
        byte[] ret = new byte[5];
        ret[0] = 0;
        // Find the positions of the dots in the IP address string.
        int position1 = ipv4.indexOf(".");
        int position2 = ipv4.indexOf(".", position1 + 1);
        int position3 = ipv4.indexOf(".", position2 + 1);
        // Convert the string segments between the dots to integers.
        ret[1] = (byte) Integer.parseInt(ipv4.substring(0, position1));
        ret[2] = (byte) Integer.parseInt(ipv4.substring(position1 + 1,
                position2));
        ret[3] = (byte) Integer.parseInt(ipv4.substring(position2 + 1,
                position3));
        ret[4] = (byte) Integer.parseInt(ipv4.substring(position3 + 1));
        return ret;
    }
    /**
     * @param ipAddress The IPv4 or IPv6 string.
     * @return 4 for IPv4, 6 for IPv6, or 0 for an invalid address.
     * @throws Exception
     */
    public static int isIpV4OrV6(String ipAddress) throws Exception {
        InetAddress address = InetAddress.getByName(ipAddress);
        if (address instanceof Inet4Address)
            return 4;
        else if (address instanceof Inet6Address)
            return 6;
        return 0;
    }
    /*
     * Checks whether an IP address is within a specified IP range.
     *
     * ipSection The IP range, separated by a hyphen (-).
     *
     * ip The IP address to validate.
     */
    public static boolean ipExistsInRange(String ip, String ipSection) {
        ipSection = ipSection.trim();
        ip = ip.trim();
        int idx = ipSection.indexOf('-');
        String beginIP = ipSection.substring(0, idx);
        String endIP = ipSection.substring(idx + 1);
        return getIp2long(beginIP) <= getIp2long(ip)
                && getIp2long(ip) <= getIp2long(endIP);
    }
    public static long getIp2long(String ip) {
        ip = ip.trim();
        String[] ips = ip.split("\\.");
        long ip2long = 0L;
        for (int i = 0; i < 4; ++i) {
            ip2long = ip2long << 8 | Integer.parseInt(ips[i]);
        }
        return ip2long;
    }
    public static long getIp2long2(String ip) {
        ip = ip.trim();
        String[] ips = ip.split("\\.");
        long ip1 = Integer.parseInt(ips[0]);
        long ip2 = Integer.parseInt(ips[1]);
        long ip3 = Integer.parseInt(ips[2]);
        long ip4 = Integer.parseInt(ips[3]);
        long ip2long = 1L * ip1 * 256 * 256 * 256 + ip2 * 256 * 256 + ip3 * 256
                + ip4;
        return ip2long;
    }
    public static void main(String[] args) {
        System.out.println(StringToLong("2002:7af3:f3be:ffff:ffff:ffff:ffff:ffff"));
        System.out.println(StringToLong("54.38.XX.XX"));
    }
    private class Invalid{
        private Invalid()
        {
        }
    }
}

IpV4Obj

package com.aliyun.odps.udf.objects;
public class IpV4Obj {
    public long startIp ;
    public long endIp ;
    public String city;
    public String province;
    public IpV4Obj(long startIp, long endIp, String city, String province) {
        this.startIp = startIp;
        this.endIp = endIp;
        this.city = city;
        this.province = province;
    }
    @Override
    public String toString() {
        return "IpV4Obj{" +
                "startIp=" + startIp +
                ", endIp=" + endIp +
                ", city='" + city + '\'' +
                ", province='" + province + '\'' +
                '}';
    }
    public void setStartIp(long startIp) {
        this.startIp = startIp;
    }
    public void setEndIp(long endIp) {
        this.endIp = endIp;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public void setProvince(String province) {
        this.province = province;
    }
    public long getStartIp() {
        return startIp;
    }
    public long getEndIp() {
        return endIp;
    }
    public String getCity() {
        return city;
    }
    public String getProvince() {
        return province;
    }
}

IpV6Obj

package com.aliyun.odps.udf.objects;
public class IpV6Obj {
    public String startIp ;
    public String endIp ;
    public String city;
    public String province;
    public String getStartIp() {
        return startIp;
    }
    @Override
    public String toString() {
        return "IpV6Obj{" +
                "startIp='" + startIp + '\'' +
                ", endIp='" + endIp + '\'' +
                ", city='" + city + '\'' +
                ", province='" + province + '\'' +
                '}';
    }
    public IpV6Obj(String startIp, String endIp, String city, String province) {
        this.startIp = startIp;
        this.endIp = endIp;
        this.city = city;
        this.province = province;
    }
    public void setStartIp(String startIp) {
        this.startIp = startIp;
    }
    public String getEndIp() {
        return endIp;
    }
    public void setEndIp(String endIp) {
        this.endIp = endIp;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getProvince() {
        return province;
    }
    public void setProvince(String province) {
        this.province = province;
    }
}

Write the MaxCompute UDF code.

In the Project pane, right-click the module source directory (src > main > java) and choose New > MaxCompute Java.

In the Create new MaxCompute java class dialog box, click UDF, enter a Name, press Enter, and then enter the code in the code editor.

For example, name the Java class IpLocation. You can use the following code without modification.

package com.aliyun.odps.udf.udfFunction;
import com.aliyun.odps.udf.ExecutionContext;
import com.aliyun.odps.udf.UDF;
import com.aliyun.odps.udf.UDFException;
import com.aliyun.odps.udf.utils.IpUtils;
import com.aliyun.odps.udf.objects.IpV4Obj;
import com.aliyun.odps.udf.objects.IpV6Obj;
import java.io.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;
public class IpLocation extends UDF {
    public static IpV4Obj[] ipV4ObjsArray;
    public static IpV6Obj[] ipV6ObjsArray;
    public IpLocation() {
        super();
    }
    @Override
    public void setup(ExecutionContext ctx) throws UDFException, IOException {
        //IPV4
        if(ipV4ObjsArray==null)
        {
            BufferedInputStream bufferedInputStream = ctx.readResourceFileAsStream("ipv4.txt");
            BufferedReader br = new BufferedReader(new InputStreamReader(bufferedInputStream));
            ArrayList<IpV4Obj> ipV4ObjArrayList=new ArrayList<>();
            String line = null;
            while ((line = br.readLine()) != null) {
                String[] f = line.split("\\|", -1);
                if(f.length>=5)
                {
                    long startIp = IpUtils.StringToLong(f[0]);
                    long endIp = IpUtils.StringToLong(f[1]);
                    String city=f[3];
                    String province=f[4];
                    IpV4Obj ipV4Obj = new IpV4Obj(startIp, endIp, city, province);
                    ipV4ObjArrayList.add(ipV4Obj);
                }
            }
            br.close();
            List<IpV4Obj> collect = ipV4ObjArrayList.stream().sorted(Comparator.comparing(IpV4Obj::getStartIp)).collect(Collectors.toList());
            ArrayList<IpV4Obj> basicIpV4DataList=(ArrayList)collect;
            IpV4Obj[] ipV4Objs = new IpV4Obj[basicIpV4DataList.size()];
            ipV4ObjsArray = basicIpV4DataList.toArray(ipV4Objs);
        }
        //IPV6
        if(ipV6ObjsArray==null)
        {
            BufferedInputStream bufferedInputStream = ctx.readResourceFileAsStream("ipv6.txt");
            BufferedReader br = new BufferedReader(new InputStreamReader(bufferedInputStream));
            ArrayList<IpV6Obj> ipV6ObjArrayList=new ArrayList<>();
            String line = null;
            while ((line = br.readLine()) != null) {
                String[] f = line.split("\\|", -1);
                if(f.length>=5)
                {
                    String startIp = IpUtils.StringToBigIntString(f[0]);
                    String endIp = IpUtils.StringToBigIntString(f[1]);
                    String city=f[3];
                    String province=f[4];
                    IpV6Obj ipV6Obj = new IpV6Obj(startIp, endIp, city, province);
                    ipV6ObjArrayList.add(ipV6Obj);
                }
            }
            br.close();
            List<IpV6Obj> collect = ipV6ObjArrayList.stream().sorted(Comparator.comparing(IpV6Obj::getStartIp)).collect(Collectors.toList());
            ArrayList<IpV6Obj> basicIpV6DataList=(ArrayList)collect;
            IpV6Obj[] ipV6Objs = new IpV6Obj[basicIpV6DataList.size()];
            ipV6ObjsArray = basicIpV6DataList.toArray(ipV6Objs);
        }
    }
    public String evaluate(String ip){
        if(ip==null||ip.trim().isEmpty()||!(ip.contains(".")||ip.contains(":")))
        {
            return null;
        }
        int ipV4OrV6=0;
        try {
            ipV4OrV6= IpUtils.isIpV4OrV6(ip);
        } catch (Exception e) {
            return null;
        }
        // If the IP address is an IPv4 address.
        if(ipV4OrV6==4)
        {
            int i = binarySearch(ipV4ObjsArray, IpUtils.StringToLong(ip));
            if(i>=0)
            {
                IpV4Obj ipV4Obj = ipV4ObjsArray[i];
                return ipV4Obj.city+","+ipV4Obj.province;
            }else{
                return null;
            }
        }else if(ipV4OrV6==6)// If the IP address is an IPv6 address.
        {
            int i = binarySearchIPV6(ipV6ObjsArray, IpUtils.StringToBigIntString(ip));
            if(i>=0)
            {
                IpV6Obj ipV6Obj = ipV6ObjsArray[i];
                return ipV6Obj.city+","+ipV6Obj.province;
            }else{
                return null;
            }
        }else{// If the IP address is not in IPv4 or IPv6 format.
            return null;
        }
    }
    @Override
    public void close() throws UDFException, IOException {
        super.close();
    }
    private static int binarySearch(IpV4Obj[] array,long ip){
        int low=0;
        int hight=array.length-1;
        while (low<=hight)
        {
            int middle=(low+hight)/2;
            if((ip>=array[middle].startIp)&&(ip<=array[middle].endIp))
            {
                return middle;
            }
            if (ip < array[middle].startIp)
                hight = middle - 1;
            else {
                low = middle + 1;
            }
        }
        return -1;
    }
    private static int binarySearchIPV6(IpV6Obj[] array,String ip){
        int low=0;
        int hight=array.length-1;
        while (low<=hight)
        {
            int middle=(low+hight)/2;
            if((ip.compareTo(array[middle].startIp)>=0)&&(ip.compareTo(array[middle].endIp)<=0))
            {
                return middle;
            }
            if (ip.compareTo(array[middle].startIp) < 0)
                hight = middle - 1;
            else {
                low = middle + 1;
            }
        }
        return -1;
    }
    private class Invalid{
        private Invalid()
        {
        }
    }
}

Prepare data for local debugging.
1. In the warehouse/example_project/__tables__/wc_in2/p1=2/p2=1/ directory of your local project, open the data file.
2. Change the data in the last column of the data file to any three IP addresses from ipv4.txt, and save the file.
Debug the MaxCompute UDF to ensure that the code runs successfully.

For more information, see Debug a UDF by running it locally.
1. Right-click the completed MaxCompute UDF script and select Run.
2. In the Run/Debug Configurations dialog box, configure the runtime parameters and click OK.
  
  As an example, set Name to IpLocation and Main class to com.aliyun.odps.udf.udfFunction.IpLocation. For the MaxCompute-specific fields, select local and example_project for MaxCompute project, enter wc_in2 for MaxCompute table, p2=1,p1=2 for Table partition (in p1=v1,p2=v2 format), and colc for Table columns (in c1,c2 format). Set Download Record limit to 100 and Data Column Separator to a comma. Then, click OK. If the code runs without errors, you can proceed. Otherwise, troubleshoot the issue by using the error message in IntelliJ IDEA.
  
  Note
  You can refer to the figure for an example of the runtime parameters.

Step 4: Register the MaxCompute UDF

Right-click the successfully compiled MaxCompute UDF script and select Deploy to server….
In the Package a jar, submit resource and register function dialog box, configure the parameters.

For parameter descriptions, see Package, upload, and register. As an example, select your target project for MaxCompute project. For Resource file, select the packaged JAR file. The Resource name is automatically populated. Set Main class to the fully qualified name of the UDF, such as com.aliyun.odps.udf.udfFunction.IpLocation, and for the Function name field, enter a name for the function, such as ipv4_ipv6_aton. Under Extra resources, select the ipv4.txt and ipv6.txt files that you uploaded in Step 1. Select Force update if already exists, and then click OK.

Step 5: Call the UDF to convert IP addresses

Install and log on to the MaxCompute client.
Run a SELECT statement to call the UDF and convert IP addresses to geolocations.
Examples:
- Convert an IPv4 address to a geolocation
```
select ipv4_ipv6_aton('116.11.XX.XX');
```
  Expected output:
```
Beihai,Guangxi Zhuang Autonomous Region
```
- Convert an IPv6 address to a geolocation
```
select ipv4_ipv6_aton('2001:0250:080b:0:0:0:0:0');
```
  Expected output:
```
Baoding,Hebei Province
```