IP Address Functions

Pinot provides functions for working with IP addresses and subnets. These functions support both IPv4 and IPv6 addresses and use CIDR notation for subnet prefixes (e.g., 192.168.0.0/16, 2001:db8::/32).

isSubnetOf

isSubnetOf(ipPrefix, ipAddress)

Returns true if the given IP address belongs to the specified subnet prefix. The first argument must be a CIDR prefix (e.g., '192.168.1.0/24') and the second must be a plain IP address without a prefix.

SELECT isSubnetOf('192.168.1.0/24', clientIp) AS is_internal
FROM accessLog
WHERE isSubnetOf('10.0.0.0/8', clientIp)
-- Filters rows where clientIp is in the 10.0.0.0/8 private range

ipPrefix

ipPrefix(ipAddress, prefixBits)

Returns the CIDR prefix for the given IP address and prefix length in bits. The IP address must not already contain a prefix. The prefix length must be between 0 and 32 for IPv4, or 0 and 128 for IPv6.

SELECT ipPrefix(clientIp, 24) AS subnet
FROM accessLog
LIMIT 5
-- For clientIp='192.168.1.100', returns '192.168.1.0/24'

ipSubnetMin

Returns the lowest (first) IP address in the given subnet.

ipSubnetMax

Returns the highest (last) IP address in the given subnet.

isIPv4String

Returns true when the input is a valid IPv4 address string without a CIDR prefix.

isIPv6String

Returns true when the input is a valid IPv6 address string without a CIDR prefix.

isPrivateIp / is_private_ip

Returns true when the input is a plain IPv4 or IPv6 address without a CIDR prefix and it falls into one of these ranges:

• RFC 1918 IPv4: 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16

• IPv4 loopback: 127.0.0.0/8

• IPv4 link-local: 169.254.0.0/16

• IPv6 loopback: ::1

• IPv6 link-local: fe80::/10

• IPv6 ULA: fc00::/7

ipFamily / ip_family

Returns the address family for a plain IP address string: 4 for IPv4 and 6 for IPv6.

ipv4ToLong

Converts an IPv4 address string to its unsigned 32-bit integer representation as a LONG.

longToIpv4

Converts an unsigned 32-bit LONG value in the IPv4 range back to dotted-decimal IPv4 text.

ipv6ToBytes

Converts an IPv6 address string to a 16-byte BYTES value.

bytesToIpv6

Converts a 16-byte IPv6 BYTES value to canonical IPv6 text.

ipv4ToIpv6

Maps an IPv4 address to its IPv4-mapped IPv6 representation.

ipMaskLen / ip_mask_len

Returns the prefix length from a CIDR string.

ipNetmask / ip_netmask

Returns the network mask for a CIDR prefix as an IP address string.

ipHostmask / ip_hostmask

Returns the host mask for a CIDR prefix as an IP address string.

ipv4CIDRToRange

Returns a two-element STRING[] containing the minimum and maximum IPv4 addresses covered by the CIDR range.