ClickHouse DB::Exception: Cannot parse escape sequence

The "DB::Exception: Cannot parse escape sequence" error occurs when ClickHouse encounters a backslash (or other escape character) followed by something it does not recognize as a valid escape sequence. This CANNOT_PARSE_ESCAPE_SEQUENCE error is most frequently seen with TSV (tab-separated values) data, since TSV format in ClickHouse uses backslash escaping by default. It can also appear with CSV data under certain configurations.

Impact

The insert or query fails entirely when this error is raised:

The whole batch is rejected, so no rows are committed.
Pipelines that ingest log data, user-generated content, or free-text fields are especially vulnerable since these often contain literal backslashes.
Repeated failures can cause message queues or staging areas to back up.

Common Causes

Literal backslashes in data -- file paths like C:\Users\data or regex patterns containing \d, \w, etc., that ClickHouse interprets as escape sequences.
Data exported from a system that does not escape backslashes -- the source wrote raw text without doubling backslashes for TSV consumption.
Mismatched format -- using TSV format (which uses backslash escaping) when the data was actually prepared for CSV (which uses quote-based escaping).
Encoding conversion artifacts -- character encoding issues that produce stray backslash bytes.
Incomplete escape at end of field -- a trailing backslash with no character following it.

Troubleshooting and Resolution Steps

Locate the offending data. The error message usually includes the position and the problematic sequence. Search for backslashes in your data:
```
grep -n '\\' /path/to/data.tsv | head -20
```
Determine whether your data actually needs TSV escaping. If the data was not prepared with ClickHouse's TSV escape rules in mind, consider switching to a different format:
```
-- Use CSV format instead, which handles escaping via quoting
INSERT INTO my_table FORMAT CSV
```
Double backslashes in the source data. If you need to stay with TSV, preprocess the file to escape literal backslashes:
```
sed 's/\\/\\\\/g' data.tsv > data_escaped.tsv
```
Use input_format_tsv_allow_variable_number_of_columns and related settings to make parsing more lenient, though this alone won't fix escape issues.
Try TabSeparatedRaw (TSVRaw) format if your data contains no escape sequences at all. This format treats backslashes as literal characters:
```
INSERT INTO my_table FORMAT TSVRaw
```
Set input_format_allow_errors_num to skip the affected rows if only a small number of rows have the issue:
```
SET input_format_allow_errors_num = 50;
INSERT INTO my_table FORMAT TSV
```

Validate data before import using clickhouse-local:

clickhouse-local --query="SELECT * FROM file('data.tsv', TSV, 'a String, b String') LIMIT 10"

Best Practices

Use TSVRaw or CSV format when your data contains arbitrary text with backslashes and was not specifically escaped for ClickHouse's TSV parser.
When generating TSV data for ClickHouse, always escape backslashes (\\), tabs (\t), and newlines (\n) in field values.
Prefer CSV with proper quoting for data that contains special characters, as the quote-based escaping model is less error-prone.
Document the expected format and escaping conventions in your data pipeline so that upstream producers and downstream consumers agree.
Test with representative sample data, especially data that includes file paths, URLs, and regex patterns.

Frequently Asked Questions

Q: What escape sequences does ClickHouse's TSV format support?
A: ClickHouse TSV supports \t (tab), \n (newline), \\ (backslash), \r (carriage return), \0 (null byte), \' (single quote), and \b (backspace). Any backslash followed by a character not in this set triggers the CANNOT_PARSE_ESCAPE_SEQUENCE error.

Q: Can I disable backslash escaping in TSV format?
A: Yes. Use the TSVRaw (or TabSeparatedRaw) format, which does not interpret backslash sequences at all. This is ideal for data that was not escaped for ClickHouse.

Q: My log data contains Windows file paths. What's the easiest fix?
A: Switch to CSV or TSVRaw format. If you must use TSV, preprocess the data to double all backslashes (sed 's/\\/\\\\/g').

Q: Does this error apply to CSV format as well?
A: By default, CSV in ClickHouse uses double-quote escaping rather than backslash escaping, so this error is rare with CSV. However, if you enable input_format_csv_allow_single_quotes or other non-standard settings, backslash-related issues can surface.