How to check if an string IP belongs to subnet in Hive

user_2277244 0
Viewed 64

I am working on a Hive code to check if an IP belongs to a subnet. But the IP that I have is in string format. The usual way of doing it in SQL is :

ip::inet << '1.2.3.4'::inet

How do I do the same in Hive?

hiveql hive
1 Answers

As pointed out by @[Z4-tier], there is no in-built method for Hive for doing this. I was able to write a udf in python and then call the function over the ip in hive.

def subnet_to_mask(subnet):
    c = int(subnet)
    mask = (0xffffffff >> (32 - c)) << (32 - c)
    return str((0xff000000 & mask) >> 24) + '.' + str((0x00ff0000 & mask) >> 16) + '.' + str((0x0000ff00 & mask) >> 8) + '.' + str((0x000000ff & mask))


def ip_to_number(ip):
    ip_no = 0
    for i, octet in enumerate(ip.split('.')):
        ip_no += int(octet) << (24 - (8 * i))
    return ip_no


def ip_in_subnet(ip, subnet):
    if len(subnet.split('/')) < 2:
        return ip == subnet.split('/')[0]
    else:
        network_ip, subnet = subnet.split('/')
        subnet = subnet_to_mask(subnet)
        return (ip_to_number(ip) & ip_to_number(subnet)) == (ip_to_number(network_ip) & ip_to_number(subnet))

You can then use it in hiveql like:

select ip, ip_in_subnet(ip, subnet) from tableABC;
user_22772440
Related