Skip to content

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.

Type Indexed Nullable Description Stored as
ip Yes (Default) Yes A 128-bit datatype designed to support both IPv4 and IPv6 addresses. IPv6
  • 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⚓︎

Version Ingest Format Returned Representation
IPv4 198.51.100.0 ::ffff:198.51.100.0
IPv6 9AA7:EE8E:6C4:8AAE:260B:C304:CA2D:8EE4 9aa7:ee8e:6c4:8aae:260b:c304:ca2d:8ee4
IPv6 9aa7:ee8e:6c4:8aae:260b:c304:ca2d:8ee4 9aa7:ee8e:6c4:8aae:260b:c304:ca2d:8ee4
IPv6 (can be compressed) 2001:0db8:0000:0000:0000:ff00:0042:8329 2001:db8::ff00:42:8329

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
{
  "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.

1
2
3
4
5
6
7
8
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.

1
2
3
4
5
6
7
8
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.

1
2
3
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

1
2
3
4
5
6
7
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:

1
2
3
4
5
6
7
8
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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
}