ClickHouse DB::Exception: Cannot parse UUID value

The "DB::Exception: Cannot parse UUID value" error occurs when ClickHouse tries to interpret a string as a UUID and the string does not conform to the expected format. The CANNOT_PARSE_UUID error code is specific to the UUID data type, and ClickHouse is quite strict about what it considers a valid UUID string.

Impact

When this error fires, the INSERT or query fails:

• The entire batch is rejected and no rows are written.
• Systems that use UUIDs as primary keys or identifiers (common in distributed systems, event tracking, and user analytics) are especially affected.
• A single invalid UUID in a batch of millions of rows will block the entire import.

Common Causes

1. Missing or extra hyphens -- UUIDs must follow the 8-4-4-4-12 hexadecimal pattern (e.g., 550e8400-e29b-41d4-a716-446655440000). A UUID without hyphens or with hyphens in the wrong positions will fail.
2. Non-hexadecimal characters -- characters outside the 0-9a-fA-F range in the UUID string.
3. Empty strings -- an empty field in a non-Nullable UUID column.
4. Placeholder values -- strings like null, none, N/A, or 00000000-0000-0000-0000-000000000000 (the nil UUID is actually valid, but other placeholders are not).
5. Truncated UUIDs -- a UUID that was cut short, often due to a column width limit in a source database or spreadsheet.
6. Curly braces or URN format -- UUIDs wrapped in braces ({550e8400-e29b-41d4-a716-446655440000}) or prefixed with urn:uuid:.

Troubleshooting and Resolution Steps

1. Check the error message for the offending value. ClickHouse typically reports the exact string that failed to parse.

2. Validate UUID format. A valid UUID for ClickHouse looks like:

   550e8400-e29b-41d4-a716-446655440000
   

   It must be exactly 36 characters: 32 hex digits plus 4 hyphens in the 8-4-4-4-12 pattern.

3. Handle UUIDs without hyphens. If your source produces compact UUIDs (32 hex characters, no hyphens), ClickHouse actually accepts these as well. Make sure the string is exactly 32 hex characters with no extra characters.

4. Strip braces or prefixes in preprocessing:

   sed 's/[{}]//g; s/urn:uuid://g' data.csv > data_clean.csv
   

5. Use toUUIDOrNull for flexible parsing in transformation queries:

   SELECT toUUIDOrNull(raw_uuid) AS uuid_col
   FROM staging_table
   WHERE toUUIDOrNull(raw_uuid) IS NOT NULL;
   

6. Make the column Nullable for optional UUIDs:

   ALTER TABLE my_table MODIFY COLUMN request_id Nullable(UUID);
   

7. Find invalid UUIDs in your data before importing:

   SELECT raw_uuid
   FROM file('data.csv', CSV, 'raw_uuid String')
   WHERE NOT match(raw_uuid, '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$')
     AND raw_uuid != ''
   LIMIT 20;
   

Best Practices

• Standardize UUID generation across your systems to produce the canonical 8-4-4-4-12 hyphenated format.
• Use Nullable(UUID) for columns where the UUID may be absent, rather than inserting placeholder strings.
• Validate UUIDs at the application level before they reach the database.
• If you receive UUIDs in non-standard formats (braces, URN prefix, no hyphens), add a preprocessing step to normalize them.
• Consider using toUUIDOrNull in staging-to-production transformation queries as a safety net.

Frequently Asked Questions

Q: Does ClickHouse accept UUIDs without hyphens (compact format)?
A: Yes, ClickHouse accepts both the standard hyphenated format (550e8400-e29b-41d4-a716-446655440000) and the compact 32-character hex format (550e8400e29b41d4a716446655440000).

Q: Is the nil UUID (all zeros) valid in ClickHouse?
A: Yes, 00000000-0000-0000-0000-000000000000 is a valid UUID in ClickHouse. It is the default value for UUID columns.

Q: Can I store non-standard identifiers (like short IDs) in a UUID column?
A: No. The UUID type strictly requires 128-bit values in the correct format. For non-standard identifiers, use a String or FixedString column.

Q: My UUIDs are uppercase. Does ClickHouse accept that?
A: Yes. ClickHouse accepts both uppercase (550E8400-E29B-41D4-A716-446655440000) and lowercase hex characters in UUIDs.

Q: How do I generate UUIDs within ClickHouse?
A: Use the generateUUIDv4() function to create random version-4 UUIDs directly in SQL, for example as a default column value: id UUID DEFAULT generateUUIDv4().