ClickHouse DB::Exception: UDF execution failed

The "DB::Exception: UDF execution failed" error in ClickHouse indicates that a user-defined function (UDF) encountered a runtime failure during execution. The UDF_EXECUTION_FAILED error code is raised when the external process or script backing a UDF crashes, returns an error, times out, or produces output that ClickHouse cannot interpret.

Impact

Queries that call the failing UDF will abort with this error, and no results are returned. If the UDF is used in a materialized view or a scheduled query, those pipelines will also stall. The underlying table data is not affected since UDFs are evaluated at query time, but dependent workflows may accumulate a backlog until the issue is resolved.

Common Causes

1. The external executable or script referenced by the UDF is missing, has incorrect permissions, or fails to start
2. The UDF script encounters a runtime error (e.g., a Python exception, a segfault, or a missing dependency)
3. The UDF process times out because it takes too long to produce output
4. Input data contains values the UDF does not handle, causing it to crash or return malformed output
5. The UDF output format does not match what ClickHouse expects (wrong number of columns, incorrect types)
6. Resource limits (memory, file descriptors) are exhausted by the UDF process

Troubleshooting and Resolution Steps

1. Check the ClickHouse server logs for details about the UDF failure:

   grep -i 'UDF\|executable\|user.*function' /var/log/clickhouse-server/clickhouse-server.log | tail -30
   

2. Verify the UDF configuration file exists and is valid. UDF configs are typically in /etc/clickhouse-server/ or a subdirectory:

   cat /etc/clickhouse-server/*_function.xml
   

3. Test the UDF executable manually outside of ClickHouse to verify it works:

   echo '{"arg1": "test_value"}' | /path/to/your/udf_script.py
   

4. Check that the executable has the correct permissions and is accessible by the ClickHouse user:

   ls -la /path/to/your/udf_script.py
   sudo -u clickhouse /path/to/your/udf_script.py --help
   

5. Verify the function is registered correctly:

   SELECT name, create_query
   FROM system.functions
   WHERE origin = 'ExecutableUserDefined' OR origin = 'SQLUserDefined';
   

6. If the UDF works in isolation but fails under load, check for resource constraints or concurrency issues. Review the command_termination_timeout and max_command_execution_time settings in the UDF config.

7. Ensure the output format matches the declaration. If the UDF is declared to return UInt64, the script must output a valid integer on each line, with no extra whitespace or headers.

Best Practices

• Always test UDF executables manually before registering them with ClickHouse.
• Include error handling and logging in your UDF scripts so failures produce useful diagnostics.
• Set appropriate timeouts in the UDF configuration to prevent hung processes from blocking queries indefinitely.
• Use the send_chunk_header and format settings in the UDF config to ensure ClickHouse and the script agree on the data exchange format.
• Pin dependencies for UDF scripts (e.g., use a virtual environment for Python) so that system updates do not break them.
• Monitor UDF execution times and error rates through system.query_log.

Frequently Asked Questions

Q: How do I see which UDFs are registered in my ClickHouse instance?
A: Query the system.functions table and filter for user-defined origins. Functions created via SQL will have origin SQLUserDefined, while executable UDFs show as ExecutableUserDefined.

Q: Can a failing UDF corrupt data in my tables?
A: No. UDFs are evaluated at query time and do not modify stored data. A failing UDF will cause the query to fail but leaves all table data intact.

Q: Is there a way to set a timeout for UDF execution?
A: Yes. In the UDF XML configuration, you can set command_termination_timeout to control how long ClickHouse waits for the process to finish. If the process exceeds this timeout, it is killed and the query fails with UDF_EXECUTION_FAILED.