IP Data Type

The ip data type supports both IPv4 and IPv6 addresses. Hydrolix can store ip values in a single column. Using ip instead of string to store IP addresses can significantly reduce the size of partitions.

• IPv4 addresses are returned in IPv4-mapped IPv6 form.
• The type accepts dotted-quad IPv4 strings and all valid IPv6 string formats.
• Hydrolix stores IP addresses in their minimal form.
• All IP addresses are returned in normalized, minimal form.
• Data with the wrong format is rejected at ingest time.

Accepted formats

Though the ip data type can store IPs for both address families in the same column, some incoming formats may track each address family in a different field. Incoming rows with distinct fields for ipv4_field and ipv6_field can be sent to different columns, each with its own default. See the output_columns below.

{
  "name": "ipv4_field",
  "datatype": {
    "type": "ip",
    "default": "127.0.0.1"
  }
}

{
  "name": "ipv6_field",
  "datatype": {
    "type": "ip",
    "default": "fe80::22bb:2ebd:da90:3439"
  }
}

IP column queries

When querying IP columns, the output for IPv6 will be its minimal form, regardless of its format at ingest.

SELECT ip, count() FROM new_datatypes.ip_perf WHERE ip = '2001:0db8:0000:0000:0000:ff00:0042:8329' GROUP BY ip
  UNION ALL 
SELECT ip, count() FROM new_datatypes.ip_perf WHERE ip = '2001:db8::ff00:42:8329' GROUP BY ip

-- Result --
ip, count()
2001:db8::ff00:42:8329	9499
2001:db8::ff00:42:8329	9499

IPv4 is stored with padding: ::ffff: prepended to the address. Queries using the IPv4 address or its padded version will output the padded version.

SELECT ip, count() FROM new_datatypes.ip_perf WHERE ip = '198.51.100.0' GROUP BY ip
  UNION ALL 
SELECT ip, count() FROM new_datatypes.ip_perf WHERE ip = '::ffff:198.51.100.0' GROUP BY ip

-- Result --
ip, count()
::ffff:198.51.100.0	1
::ffff:198.51.100.0	1

Identify IPv4 addresses in queries

The simplest way to identify IPv4 addresses is to use Clickhouse function toIPv4OrDefault(ip) in a statement such as SELECT toIPv4OrDefault(ip)='0.0.0.0'. The ip type can be used with all of the Clickhouse functions for working with IPv4 and IPV6.

For example, the query below will output a boolean where 1 is IPv4.

select  toIPv4OrDefault(ip) != '0.0.0.0' as ipv4_mapped, ip  from new_datatypes.ip_perf where ipv4_mapped limit 3
UNION ALL 
select  toIPv4OrDefault(ip) != '0.0.0.0' as ipv4_mapped, ip  from new_datatypes.ip_perf where not ipv4_mapped limit 3

result

ipv4_mapped ip
1	::ffff:192.0.2.0
1	::ffff:198.51.100.0
1	::ffff:203.0.113.0
0	2001:fef5:aeb1:30a8:c625:b9cb:c718:f0a1
0	2001:feb3:e4b1:5d32:b16d:d304:c03c:6906
0	2001:fd94:7a91:95c0:158:94bf:df99:3044

To display an IPv4 address in dotted-quad notation, use the following query:

WITH
  toIPv6('::ffff:192.0.2.24') AS ip
SELECT
  IF(toIPv4OrDefault(ip) == '0.0.0.0',   /* Is this IPv6 or not? */
     toString(ip),                       /* True, Display normalized IPv6 representation */
     toString(toIPv4OrDefault(ip))       /* False, Display dotted-quad IPv4 representation */
    ) AS display_ip_string
;

Grafana automatically represents IPv4 in typical dotted-quad form.

Compare IP with String

String representations of IP addresses, especially IPv6, aren't always normalized. Unpredictability can result from comparing string representations of IPs from different sources. The ip type returns a normalized, minimal form that can be safely compared.

To compare IPs predictably, convert or cast from a string to a normalized ip type.

WITH
  '2001:0db8:0000:0000:0000:ff00:0042:8329' as ip
SELECT
  ip,
  toIPv6(ip) as ipv6,
  toString(ipv6) as converted_ipv6,
  ip == converted_ipv6 as equals
--- RESULT ---- 
{
  "ip": "2001:0db8:0000:0000:0000:ff00:0042:8329",
  "ipv6": "2001:db8::ff00:42:8329",
  "converted_ipv6": "2001:db8::ff00:42:8329",
  "equals": 0
}

WITH
  '192.0.2.0' as ip
SELECT
  ip,
  toIPv6(ip) as ipv6,
  toString(ipv6) as converted_ipv6,
  ip == converted_ipv6 as equals
--- RESULT ---- 
{
  "ip": "192.0.2.0",
  "ipv6": "::ffff:192.0.2.0",
  "converted_ipv6": "::ffff:192.0.2.0",
  "equals": 0
}