ClickHouse DB::Exception: Expected end of file

The "DB::Exception: Expected end of file" error in ClickHouse means the parser finished reading what it considers a complete input but found additional data trailing after it. This is the EXPECTED_END_OF_FILE error code, and it is essentially the opposite of UNEXPECTED_END_OF_FILE -- instead of too little data, there is too much. It commonly surfaces during batch inserts when the chosen format does not match the actual structure of the data being sent.

Impact

This error causes the INSERT statement to fail entirely. Since ClickHouse treats inserts atomically, none of the data is committed:

• Data ingestion pipelines halt until the format mismatch is corrected.
• If the root cause is a format configuration issue, every subsequent retry will fail the same way.
• In automated systems, this can lead to growing backlogs of unprocessed data.

Common Causes

1. Wrong format specification -- telling ClickHouse to expect JSONEachRow when the data is actually a single JSON object (or vice versa), so the parser finishes one object and finds another it was not expecting.
2. Multiple SQL statements in a single query -- sending two INSERT statements concatenated together over a single HTTP request.
3. Extra newlines or junk after valid data -- trailing whitespace, extra delimiters, or debug output appended to the end of a file.
4. Using VALUES format with extra data -- after a valid INSERT INTO ... VALUES (...) block, there is additional text that the parser cannot consume.
5. Concatenated files -- accidentally combining two export files into one without adjusting the format boundary (e.g., two JSON arrays back-to-back).

Troubleshooting and Resolution Steps

1. Check the format you specified against the actual data. Open the file or inspect the HTTP body and confirm the structure matches what ClickHouse expects:

   # If using JSONEachRow, each line should be a separate JSON object
   head -5 /path/to/data.json
   

2. Look for trailing data at the end of the file:

   tail -10 /path/to/data.csv
   

   Extra blank lines, stray characters, or a second header row at the end are common culprits.

3. Ensure you are sending exactly one statement per request. If using the HTTP interface, each request should contain a single INSERT. Multiple statements should be sent as separate requests.

4. Strip trailing whitespace or newlines if needed:

   sed -i '' -e :a -e '/^\n*$/{$d;N;ba}' /path/to/data.csv
   

5. Validate JSON structure for JSON formats. A tool like jq can detect issues:

   # For JSONEachRow, each line should parse independently
   while IFS= read -r line; do echo "$line" | jq . > /dev/null || echo "Bad line: $line"; done < data.json
   

6. Check for BOM (byte order mark) or encoding artifacts at the start or end of the file that the parser may interpret as extra data:

   hexdump -C /path/to/data.csv | tail -5
   

7. Use input_format_allow_errors_num only for row-level issues. This setting does not help with structural format problems -- the parser sees extra data at the top level, not a bad row within the stream.

Best Practices

• Always match the FORMAT clause to the actual data structure. When in doubt, test with a small sample first.
• Avoid concatenating multiple export files without verifying the combined output is still valid for the chosen format.
• Use clickhouse-local to validate data files before sending them to a production server.
• Keep HTTP requests to one statement each. Use the clickhouse-client multi-query flag only for DDL, not for data imports.
• Automate pre-import validation in your pipeline to catch trailing-data issues before they reach ClickHouse.

Frequently Asked Questions

Q: What is the difference between UNEXPECTED_END_OF_FILE and EXPECTED_END_OF_FILE?
A: UNEXPECTED_END_OF_FILE means the data ended too soon -- ClickHouse was still expecting more. EXPECTED_END_OF_FILE means the data went on too long -- ClickHouse finished parsing but found leftover bytes it cannot interpret.

Q: I am getting this error when inserting JSON data. What is the most likely cause?
A: You are probably using the wrong JSON format variant. For example, if your data is a JSON array ([{...}, {...}]), use JSONEachRow only if each line is a standalone object. For a wrapped array, use JSONCompactEachRow or preprocess the data to strip the outer brackets.

Q: Can extra blank lines at the end of a CSV file cause this?
A: Yes. Some CSV writers add a trailing newline that results in an empty line at the end. ClickHouse may interpret this as an additional (empty) row and then fail because it does not match the expected column count. Trimming trailing blank lines usually fixes this.

Q: Does this error happen with the Native format?
A: It is less common with the Native binary format because the protocol includes explicit block boundaries. The error is overwhelmingly associated with text-based formats like CSV, TSV, JSON, and VALUES.