- Categories:
String & binary functions (General)
PARSE_IP¶
Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.
Syntax¶
PARSE_IP(<expr>, '<type>' [, <permissive>])
Arguments¶
Required:
exprA string expression.
typeA string that identifies the type of IP address. Supports either
INETorCIDR; the value is case-insenstitive.
Optional:
permissiveFlag that determines how parse errors are handled:
If set to 0, parse errors cause the function to fail.
If set to 1, parse errors result in an object with the
errorfield set to the respective error message (and no other fields set).
Default value is 0.
Returns¶
Usage notes¶
The function parses an IP address and returns a JSON object.
The following elements are always returned:
familyNumeric value.
4(IPv4) or6(IPv6).ip_typeString value.
inetorcidrfrom the input.hostString value. Host address from the input expression.
ip_fieldsArray of 4 numeric fields, each a value between 0 and 4,294,967,295 (2^32 - 1), inclusive. The bit values from this array are mapped to the raw bits in the host address.
IPv4 addresses: Displays only the rightmost 32 bits of the host address.
IPv6 addresses: Displays each of the 32-bit fields that map to the raw 128-bit host address from left to right.
If a subnet mask is input, the results include
network_prefix_length, a numeric value that identifies the length of the subnet mask.The following elements are returned for IPv4 addresses:
ipv4Numeric IP address that matches the first field in
ip_fields.ipv4_range_startNumeric start address of the network, displayed when a subnet mask is included in the input.
ipv4_range_endNumeric end address of the network, displayed when a subnet mask is included in the input.
The following elements are returned for IPv6 addresses:
hex_ipv6IP address expressed as a fully padded, fixed-size hexadecimal value.
hex_ipv6_range_startFully padded fixed-size hexadecimal start address of the network, displayed when a subnet mask is included in the input.
hex_ipv6_range_endFully padded fixed-size hexadecimal end address of the network, displayed when a subnet mask is included in the input.
The
snowflake$typeelement is reserved for internal Snowflake usage.For IP address range calculations or subnet mask searches, query the individual JSON elements directly. See the examples, below.
When inputting a subnet mask, Snowflake recommends storing the function output in a VARIANT column and querying against the generated elements for better performance. See the examples.
Examples¶
SELECT column1, PARSE_IP(column1, 'INET') FROM VALUES('192.168.242.188/24'), ('192.168.243.189/24'); --------------------+-----------------------------------+ COLUMN1 | PARSE_IP(COLUMN1, 'INET') | --------------------+-----------------------------------| 192.168.242.188/24 | { | | "family": 4, | | "host": "192.168.242.188", | | "ip_fields": [ | | 3232297660, | | 0, | | 0, | | 0 | | ], | | "ip_type": "inet", | | "ipv4": 3232297660, | | "ipv4_range_end": 3232297727, | | "ipv4_range_start": 3232297472, | | "netmask_prefix_length": 24, | | "snowflake$type": "ip_address" | | } | 192.168.243.189/24 | { | | "family": 4, | | "host": "192.168.243.189", | | "ip_fields": [ | | 3232297917, | | 0, | | 0, | | 0 | | ], | | "ip_type": "inet", | | "ipv4": 3232297917, | | "ipv4_range_end": 3232297983, | | "ipv4_range_start": 3232297728, | | "netmask_prefix_length": 24, | | "snowflake$type": "ip_address" | | } | --------------------+-----------------------------------+SELECT PARSE_IP('fe80::20c:29ff:fe2c:429/64', 'INET'); ----------------------------------------------------------------+ PARSE_IP('FE80::20C:29FF:FE2C:429/64', 'INET') | ----------------------------------------------------------------| { | "family": 6, | "hex_ipv6": "FE80000000000000020C29FFFE2C0429", | "hex_ipv6_range_end": "FE80000000000000FFFFFFFFFFFFFFFF", | "hex_ipv6_range_start": "FE800000000000000000000000000000", | "host": "fe80::20c:29ff:fe2c:429", | "ip_fields": [ | 4269801472, | 0, | 34351615, | 4264297513 | ], | "ip_type": "inet", | "netmask_prefix_length": 64, | "snowflake$type": "ip_address" | } | ----------------------------------------------------------------+WITH lookup AS ( SELECT column1 AS tag, PARSE_IP(column2, 'INET') AS obj FROM VALUES('San Francisco', '192.168.242.0/24'), ('New York', '192.168.243.0/24') ), entries AS ( SELECT PARSE_IP(column1, 'INET') AS ipv4 FROM VALUES('192.168.242.188/24'), ('192.168.243.189/24') ) SELECT lookup.tag, entries.ipv4:host, entries.ipv4 FROM lookup, entries WHERE lookup.tag = 'San Francisco' AND entries.IPv4:ipv4 BETWEEN lookup.obj:ipv4_range_start AND lookup.obj:ipv4_range_end; ---------------+-------------------+-----------------------------------+ TAG | ENTRIES.IPV4:HOST | IPV4 | ---------------+-------------------+-----------------------------------| San Francisco | "192.168.242.188" | { | | | "family": 4, | | | "host": "192.168.242.188", | | | "ip_fields": [ | | | 3232297660, | | | 0, | | | 0, | | | 0 | | | ], | | | "ip_type": "inet", | | | "ipv4": 3232297660, | | | "ipv4_range_end": 3232297727, | | | "ipv4_range_start": 3232297472, | | | "netmask_prefix_length": 24, | | | "snowflake$type": "ip_address" | | | } | ---------------+-------------------+-----------------------------------+CREATE OR REPLACE TABLE ipv6_lookup (tag String, obj VARIANT); -----------------------------------------+ status | -----------------------------------------| Table IPV6_LOOKUP successfully created. | -----------------------------------------+ INSERT INTO ipv6_lookup SELECT column1 AS tag, parse_ip(column2, 'INET') AS obj FROM VALUES('west', 'fe80:12:20c:29ff::/64'), ('east', 'fe80:12:1:29ff::/64'); -------------------------+ number of rows inserted | -------------------------| 2 | -------------------------+ CREATE OR REPLACE TABLE ipv6_entries (obj VARIANT); ------------------------------------------+ status | ------------------------------------------| Table IPV6_ENTRIES successfully created. | ------------------------------------------+ INSERT INTO ipv6_entries SELECT parse_ip(column1, 'INET') as obj FROM VALUES ('fe80:12:20c:29ff:fe2c:430:370:2/64'), ('fe80:12:20c:29ff:fe2c:430:370:00F0/64'), ('fe80:12:20c:29ff:fe2c:430:370:0F00/64'), ('fe80:12:20c:29ff:fe2c:430:370:F000/64'), ('fe80:12:20c:29ff:fe2c:430:370:FFFF/64'), ('fe80:12:1:29ff:fe2c:430:370:FFFF/64'), ('fe80:12:1:29ff:fe2c:430:370:F000/64'), ('fe80:12:1:29ff:fe2c:430:370:0F00/64'), ('fe80:12:1:29ff:fe2c:430:370:00F0/64'), ('fe80:12:1:29ff:fe2c:430:370:2/64'); -------------------------+ number of rows inserted | -------------------------| 10 | -------------------------+ SELECT lookup.tag, entries.obj:host FROM ipv6_lookup AS lookup, ipv6_entries AS entries WHERE lookup.tag = 'east' AND entries.obj:hex_ipv6 BETWEEN lookup.obj:hex_ipv6_range_start AND lookup.obj:hex_ipv6_range_end; ------+------------------------------------+ TAG | ENTRIES.OBJ:HOST | ------+------------------------------------| east | "fe80:12:1:29ff:fe2c:430:370:FFFF" | east | "fe80:12:1:29ff:fe2c:430:370:F000" | east | "fe80:12:1:29ff:fe2c:430:370:0F00" | east | "fe80:12:1:29ff:fe2c:430:370:00F0" | east | "fe80:12:1:29ff:fe2c:430:370:2" | ------+------------------------------------+