Skip to main content

ipv4

IPv4​

IPv4 is a domain based on UInt32 type and serves as a typed replacement for storing IPv4 values. It provides compact storage with the human-friendly input-output format and column type information on inspection.

Basic Usage​

CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY url;

DESCRIBE TABLE hits;
β”Œβ”€name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┐
β”‚ url β”‚ String β”‚ β”‚ β”‚ β”‚ β”‚
β”‚ from β”‚ IPv4 β”‚ β”‚ β”‚ β”‚ β”‚
β””β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

OR you can use IPv4 domain as a key:

CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY from;

IPv4 domain supports custom input format as IPv4-strings:

INSERT INTO hits (url, from) VALUES ('https://wikipedia.org', '116.253.40.133')('https://clickhouse.com', '183.247.232.58')('https://clickhouse.com/docs/en/', '116.106.34.242');

SELECT * FROM hits;
β”Œβ”€url────────────────────────────────┬───────────from─┐
β”‚ https://clickhouse.com/docs/en/ β”‚ 116.106.34.242 β”‚
β”‚ https://wikipedia.org β”‚ 116.253.40.133 β”‚
β”‚ https://clickhouse.com β”‚ 183.247.232.58 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Values are stored in compact binary form:

SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;
β”Œβ”€toTypeName(from)─┬─hex(from)─┐
β”‚ IPv4 β”‚ B7F7E83A β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Domain values are not implicitly convertible to types other than UInt32. If you want to convert IPv4 value to a string, you have to do that explicitly with IPv4NumToString() function:

SELECT toTypeName(s), IPv4NumToString(from) as s FROM hits LIMIT 1;
β”Œβ”€toTypeName(IPv4NumToString(from))─┬─s──────────────┐
β”‚ String β”‚ 183.247.232.58 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Or cast to a UInt32 value:

SELECT toTypeName(i), CAST(from as UInt32) as i FROM hits LIMIT 1;
β”Œβ”€toTypeName(CAST(from, 'UInt32'))─┬──────────i─┐
β”‚ UInt32 β”‚ 3086477370 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Original article