ClickHouse DB::Exception: Cannot parse domain value from string

The "DB::Exception: Cannot parse domain value from string" error fires when ClickHouse cannot convert a string into one of its domain-specific types. The CANNOT_PARSE_DOMAIN_VALUE_FROM_STRING error code most commonly appears with the IPv4 and IPv6 column types, where the input string does not represent a valid IP address. It can also surface with other domain types that enforce specific string formats.

Impact

This error blocks the INSERT or query entirely:

• No rows from the batch are committed.
• Systems that store network data (logs, flow records, access logs) are frequently affected because real-world IP data is often messy.
• A single invalid IP address string in a large batch can prevent the entire load from completing.

Common Causes

1. Invalid IP address format -- strings like 192.168.1.999 (octet out of range), 10.0.0 (missing octet), or abc.def.ghi.jkl (non-numeric).
2. Empty strings or null markers -- empty fields, N/A, null, or - in a non-Nullable IPv4/IPv6 column.
3. IPv6 address in an IPv4 column -- a full IPv6 address like ::ffff:192.168.1.1 inserted into an IPv4 column.
4. Hostname instead of IP -- DNS hostnames like example.com in an IP address column.
5. Port numbers appended -- values like 192.168.1.1:8080 where the port is part of the string.
6. CIDR notation -- values like 10.0.0.0/24 in a plain IPv4 column that does not expect a subnet mask.

Troubleshooting and Resolution Steps

1. Check the error message to see the exact string that failed parsing. ClickHouse reports the offending value.

2. Validate IP addresses before import. Use clickhouse-local to find bad values:

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

3. Use safe conversion functions in transformation queries:

   INSERT INTO my_table
   SELECT toIPv4OrDefault(raw_ip) AS ip_col
   FROM staging_table;
   

   toIPv4OrDefault returns 0.0.0.0 for unparseable values. toIPv4OrNull returns NULL.

4. Make the column Nullable if missing IPs are expected:

   ALTER TABLE my_table MODIFY COLUMN src_ip Nullable(IPv4);
   

5. Strip port numbers from IP strings before import:

   sed 's/:[0-9]*$//' data.csv > data_clean.csv
   

6. Strip CIDR notation if present:

   sed 's|/[0-9]*||' data.csv > data_clean.csv
   

7. Use input_format_allow_errors_num to skip rows with invalid IPs:

   SET input_format_allow_errors_num = 100;
   INSERT INTO my_table FORMAT CSV
   

Best Practices

• Validate IP addresses at the application or ETL level before inserting into ClickHouse.
• Use Nullable(IPv4) or Nullable(IPv6) for columns where the IP may be absent or unknown.
• If your data may contain both IPv4 and IPv6 addresses, use the IPv6 column type -- it can store IPv4-mapped IPv6 addresses (e.g., ::ffff:192.168.1.1).
• Use toIPv4OrNull and toIPv6OrNull in transformation queries as safety nets against bad data.
• Separate IP addresses from port numbers and CIDR masks before storage if you need both pieces of information.

Frequently Asked Questions

Q: Can I store IPv4 addresses in an IPv6 column?
A: Yes. ClickHouse will store them as IPv4-mapped IPv6 addresses (e.g., 192.168.1.1 becomes ::ffff:192.168.1.1). This is useful when you have mixed IPv4 and IPv6 data.

Q: What is the default value for an IPv4 column?
A: The default value is 0.0.0.0. For IPv6, it is ::. The toIPv4OrDefault and toIPv6OrDefault functions return these values for unparseable strings.

Q: Can I store a CIDR range like 10.0.0.0/24 in an IPv4 column?
A: No. The IPv4 type stores a single address, not a range. Store the address and prefix length in separate columns (e.g., IPv4 and UInt8), or use the isIPAddressInRange function for CIDR-based lookups.

Q: My data has IP addresses with port numbers like 10.0.0.1:443. How should I handle this?
A: Split the IP and port into separate fields before import. Store the IP in an IPv4 column and the port in a UInt16 column. ClickHouse does not parse ip:port combinations automatically.