ClickHouse DB::Exception: Cannot parse IPv6 address

The "DB::Exception: Cannot parse IPv6 address" error in ClickHouse occurs when a string cannot be interpreted as a valid IPv6 address. The CANNOT_PARSE_IPV6 error code is specific to the IPv6 data type and fires when the input does not conform to any of the accepted IPv6 notation formats (full, abbreviated, or IPv4-mapped).

Impact

This error causes the INSERT or query to fail:

• The entire batch is rejected, with no partial writes.
• Networking and security analytics workloads that store source/destination addresses are commonly affected.
• IPv6 format complexity (multiple valid representations of the same address) makes this error more likely than its IPv4 counterpart when data comes from diverse sources.

Common Causes

1. Non-hexadecimal characters -- IPv6 addresses use hex digits (0-9, a-f) and colons. Characters like g, z, or spaces are invalid.
2. Too many or too few groups -- a full IPv6 address has eight groups of four hex digits. More groups or malformed abbreviation triggers the error.
3. Incorrect use of :: abbreviation -- the double-colon shorthand for consecutive zero groups can only appear once in an address. Using it twice (e.g., 2001::abcd::1) is invalid.
4. Empty or placeholder values -- empty strings, N/A, null, or other non-IP text in an IPv6 column.
5. Bare IPv4 addresses without mapping prefix -- plain 192.168.1.1 is not valid IPv6; it needs to be ::ffff:192.168.1.1 for IPv4-mapped representation.
6. Zone ID suffixes -- addresses with zone identifiers like fe80::1%eth0 are not accepted by ClickHouse.
7. Brackets -- URL-style bracketed addresses like [::1] include characters ClickHouse does not expect.

Troubleshooting and Resolution Steps

1. Inspect the offending value from the error message. ClickHouse will show the string that failed to parse.

2. Find invalid addresses in your data:

   SELECT raw_ip FROM file('data.csv', CSV, 'raw_ip String')
   WHERE toIPv6OrNull(raw_ip) IS NULL AND raw_ip != ''
   LIMIT 20;
   

3. Use safe conversion functions:

   INSERT INTO my_table
   SELECT toIPv6OrDefault(raw_ip) AS ip_col
   FROM staging;
   

   toIPv6OrDefault returns :: for invalid addresses. toIPv6OrNull returns NULL.

4. Strip zone IDs and brackets in preprocessing:

   # Remove zone IDs (%interface)
   sed 's/%[a-zA-Z0-9]*//g' data.csv > data_clean.csv
   # Remove brackets
   sed 's/\[//g; s/\]//g' data_clean.csv > data_final.csv
   

5. Convert plain IPv4 addresses to IPv4-mapped IPv6 if your column is IPv6 but your data has mixed addresses:

   SELECT
       if(isIPv4String(raw_ip), toIPv6(toIPv4(raw_ip)), toIPv6(raw_ip)) AS ip_col
   FROM staging;
   

6. Make the column Nullable:

   ALTER TABLE my_table MODIFY COLUMN dst_ip Nullable(IPv6);
   

7. Use error tolerance for batch imports:

   SET input_format_allow_errors_num = 100;
   INSERT INTO my_table FORMAT CSV
   

Best Practices

• Use the IPv6 column type when your data may contain both IPv4 and IPv6 addresses, since IPv6 can represent IPv4-mapped addresses.
• Normalize IPv6 addresses to a consistent format before import to avoid issues with varying representations.
• Strip zone IDs, brackets, and other metadata from IP address strings before inserting.
• Use Nullable(IPv6) for columns where the address may be absent.
• Validate IP address format in your application or ETL pipeline before sending data to ClickHouse.

Frequently Asked Questions

Q: Can I store IPv4 addresses in an IPv6 column?
A: Yes. Use the IPv4-mapped format ::ffff:192.168.1.1, or let ClickHouse convert automatically with toIPv6(toIPv4('192.168.1.1')). A plain IPv4 string like 192.168.1.1 inserted directly into an IPv6 column will also be automatically mapped in recent ClickHouse versions.

Q: What is the default value for an IPv6 column?
A: The default value is :: (the all-zeros address). The toIPv6OrDefault function returns this value when it cannot parse the input.

Q: Does ClickHouse accept abbreviated IPv6 addresses?
A: Yes. ClickHouse accepts full notation (2001:0db8:0000:0000:0000:0000:0000:0001), abbreviated notation (2001:db8::1), and IPv4-mapped notation (::ffff:192.168.1.1).

Q: My addresses have zone IDs like fe80::1%eth0. How do I handle them?
A: ClickHouse does not support zone IDs. Strip the %interface suffix before inserting. You can store the zone ID in a separate String column if needed.