ClickHouse DB::Exception: No data to insert

The "DB::Exception: No data to insert" error in ClickHouse happens when the server receives an INSERT query but no actual data accompanies it. The error code is NO_DATA_TO_INSERT. The server expects data to follow the INSERT statement (either inline VALUES or streamed through the request body), and raises this exception when none arrives.

Impact

This error affects only the specific INSERT operation that triggered it:

• No rows are written to the target table
• ETL pipelines or application code issuing the INSERT will receive an error response
• Replicated tables will not generate any replication log entries for the failed insert
• There is no risk of data corruption or impact on existing data

Common Causes

1. Empty request body in HTTP interface -- An INSERT query is sent via the HTTP API with Content-Length: 0 or an empty POST body.
2. INSERT ... SELECT returning zero rows -- While ClickHouse typically handles this gracefully, certain edge cases or older versions may raise this error.
3. Client library bug or misconfiguration -- The client sends the INSERT statement but fails to stream the data portion due to a serialization error or premature connection close.
4. Empty file as input -- Using INSERT INTO ... FROM INFILE with an empty file, or piping empty stdin into clickhouse-client.
5. Data format mismatch -- The FORMAT clause does not match the actual data, and the parser consumes nothing, interpreting it as zero rows.
6. Middleware or proxy stripping the body -- A reverse proxy, load balancer, or API gateway between the client and ClickHouse drops the request body.

Troubleshooting and Resolution Steps

1. Verify that data is actually being sent. For HTTP interface usage, inspect the request:

   # Check what is being sent
   curl -v 'http://localhost:8123/?query=INSERT+INTO+test+FORMAT+JSONEachRow' \
     --data-binary @data.json
   

   Look for Content-Length: 0 or an empty body in the verbose output.

2. Check the input file or stream:

   wc -l data.json
   stat data.json
   

   An empty file or one with only whitespace will trigger this error.

3. Ensure the FORMAT clause matches the data. If you specify FORMAT CSV but send JSON, the parser may not extract any valid rows:

   -- Make sure formats match
   INSERT INTO my_table FORMAT JSONEachRow
   {"col1": "value1", "col2": 42}
   

4. Test with a minimal example:

   -- Via clickhouse-client
   INSERT INTO my_table VALUES (1, 'test');
   

   If this works, the issue is with how the original data is being sent, not with the table itself.

5. Inspect client library behavior. If using a client library, enable debug logging to confirm the data payload is being serialized and sent:

   # Example: Python clickhouse-connect
   import logging
   logging.basicConfig(level=logging.DEBUG)
   

6. Check for proxy interference. If ClickHouse is behind a reverse proxy:

   # Send directly to ClickHouse, bypassing the proxy
   curl 'http://clickhouse-host:8123/?query=INSERT+INTO+test+FORMAT+JSONEachRow' \
     --data-binary '{"col1":"value"}'
   

   If the direct request succeeds but the proxied request fails, investigate the proxy configuration.

Best Practices

• Validate that data is non-empty before issuing INSERT queries in application code.
• When reading from files, check file size before sending to avoid unnecessary error handling.
• Use appropriate error handling in ETL pipelines to distinguish between "no data to insert" (which may be a normal condition) and actual failures.
• Match the FORMAT clause precisely to the data being sent to avoid silent parse failures.
• Log request and response details when debugging INSERT issues, especially in distributed architectures with proxies.

Frequently Asked Questions

Q: Is it safe to ignore this error if my source sometimes produces empty batches?
A: Yes. The error is informational in the sense that no partial or corrupt data is written. If your pipeline occasionally produces empty batches, you can catch this error and skip it. Alternatively, add a pre-check in your code to avoid sending empty INSERTs altogether.

Q: Does this error apply to INSERT ... SELECT queries?
A: Generally, an INSERT ... SELECT that returns zero rows completes successfully without inserting anything and without error. However, in certain scenarios or older ClickHouse versions, you might see this error. Upgrading to a recent version typically resolves this.

Q: Can network issues cause this error?
A: Indirectly, yes. If the connection is interrupted after the INSERT statement is sent but before the data is transmitted, the server may interpret this as an INSERT with no data.

Q: Does the error code NO_DATA_TO_INSERT differ from RECEIVED_EMPTY_DATA?
A: Yes. NO_DATA_TO_INSERT specifically relates to INSERT operations where no rows were provided. RECEIVED_EMPTY_DATA is a more general error indicating that an empty data block was received where data was expected, which can occur in contexts beyond just INSERTs.