How to have IPv4 and IPv6 in The Same Column?

[mai.abd...@gmail.com]

Hello,

Does ClickHouse has any column type that can support IP addresses in v4 and v6?

I get the value from the request headers and some ISPs are supporting IPv6 while the others are using IPv4. Is there a way to support the both values in the same column?

Thank

[Denis Zhuravlev]

Ipv4 can be represented as a subset of ipv6 using :ffff:1

select IPv6NumToString(IPv4ToIPv6(toIPv4('127.0.0.1')));

┌─IPv6NumToString(IPv4ToIPv6(toIPv4('127.0.0.1')))─┐
│ ::ffff:127.0.0.1                                 │
└──────────────────────────────────────────────────┘

Probably it's better to convert in such form before inserting into CH

create table tip6(i IPv6) Engine=Memory;

insert into tip6 values('::ffff:127.0.0.1'), ('2001:44c8:129:2632:33:0:252:2');

select * from tip6　;
┌─i─────────────────────────────┐
│ ::ffff:127.0.0.1              │
│ 2001:44c8:129:2632:33:0:252:2 │
└───────────────────────────────┘

https://en.wikipedia.org/wiki/IPv6_address

During the transition of the Internet from IPv4 to IPv6, it is typical to operate in a mixed addressing environment. For such use cases, a special notation has been introduced, which expresses IPv4-mapped and IPv4-compatible IPv6 addresses by writing the least-significant 32 bits of an address in the familiar IPv4 dot-decimal notation, whereas the 96 most-significant bits are written in IPv6 format. For example, the IPv4-mapped IPv6 address ::ffff:c000:0280 is written as ::ffff:192.0.2.128, thus expressing clearly the original IPv4 address that was mapped to IPv6.

[mai.abd...@gmail.com]

Can you please explain me why the IP is stored at my table in a different value? Does the table engine matter?

CREATE TABLE ips_v6 

(

    client_ip_address IPv6

)

ENGINE = MergeTree()

PRIMARY KEY (client_ip_address)

ORDER BY (client_ip_address);

INSERT INTO ips_v6

(

    client_ip_address

)

SELECT '::ffff:127.0.0.1';

select distinct client_ip_address from ips_v6; -- The output: 3a3a:6666:6666:3a31:3237:2e30:2e30:2e31

[Denis Zhuravlev]

>Can you please explain me why the IP is stored at my table in a different value?

Because you inserted the String (wrong value). You may consider this as Unexpected Behavior issue

https://github.com/ClickHouse/ClickHouse/issues/20098

>Does the table engine matter?

No

CREATE TABLE ips_v6 

(

    client_ip_address IPv6

)

ENGINE = MergeTree()

PRIMARY KEY (client_ip_address)

ORDER BY (client_ip_address);

INSERT INTO ips_v6(client_ip_address) SELECT toIPv6('::ffff:127.0.0.1');
INSERT INTO ips_v6 values ('::ffff:127.0.0.1');

SELECT * FROM ips_v6

┌─client_ip_address─┐
│ ::ffff:127.0.0.1  │
└───────────────────┘
┌─client_ip_address─┐
│ ::ffff:127.0.0.1  │
└───────────────────┘