system.dead_letter_queue - Clickhouse

Contains information about messages received via a streaming engine and parsed with errors. Currently implemented for Kafka and RabbitMQ.

Logging is enabled by specifying dead_letter_queue for the engine specific handle_error_mode setting.

The flushing period of data is set in flush_interval_milliseconds parameter of the dead_letter_queue server settings section. To force flushing, use the SYSTEM FLUSH LOGS query.

ClickHouse does not delete data from the table automatically. See Introduction for more details.

Columns:

table_engine (Enum8) - Stream type. Possible values: Kafka and RabbitMQ.
event_date (Date) - Message consuming date.
event_time (DateTime) - Message consuming date and time.
event_time_microseconds (DateTime64) - Message consuming time with microseconds precision.
database (LowCardinality(String)) - ClickHouse database the streaming table belongs to.
table (LowCardinality(String)) - ClickHouse table name.
error (String) - Error text.
raw_message (String) - Message body.
kafka_topic_name (String) - Kafka topic name.
kafka_partition (UInt64) - Kafka partition of the topic.
kafka_offset (UInt64) - Kafka offset of the message.
kafka_key (String) - Kafka key of the message.
rabbitmq_exchange_name (String) - RabbitMQ exchange name.
rabbitmq_message_id (String) - RabbitMQ message id.
rabbitmq_message_timestamp (DateTime) - RabbitMQ message timestamp.
rabbitmq_message_redelivered (UInt8) - RabbitMQ redelivered flag.
rabbitmq_message_delivery_tag (UInt64) - RabbitMQ delivery tag.
rabbitmq_channel_id (String) - RabbitMQ channel id.

Example

Query:

sql

SELECT * FROM system.dead_letter_queue LIMIT 1 \G;

Result:

text

Row 1:
──────
table_engine:                  Kafka
event_date:                    2025-05-01
event_time:                    2025-05-01 10:34:53
event_time_microseconds:       2025-05-01 10:34:53.910773
database:                      default
table:                         kafka
error:                         Cannot parse input: expected '\t' before: 'qwertyuiop': (at row 1)
:
Row 1:
Column 0,   name: key,   type: UInt64, ERROR: text "qwertyuiop" is not like UInt64
raw_message:                   qwertyuiop
kafka_topic_name:              TSV_dead_letter_queue_err_1746095689
kafka_partition:               0
kafka_offset:                  0
kafka_key:
rabbitmq_exchange_name:
rabbitmq_message_id:
rabbitmq_message_timestamp:    1970-01-01 00:00:00
rabbitmq_message_redelivered:  0
rabbitmq_message_delivery_tag: 0
rabbitmq_channel_id:

Row 2:
──────
table_engine:                  Kafka
event_date:                    2025-05-01
event_time:                    2025-05-01 10:34:53
event_time_microseconds:       2025-05-01 10:34:53.910944
database:                      default
table:                         kafka
error:                         Cannot parse input: expected '\t' before: 'asdfghjkl': (at row 1)
:
Row 1:
Column 0,   name: key,   type: UInt64, ERROR: text "asdfghjkl" is not like UInt64
raw_message:                   asdfghjkl
kafka_topic_name:              TSV_dead_letter_queue_err_1746095689
kafka_partition:               0
kafka_offset:                  0
kafka_key:
rabbitmq_exchange_name:
rabbitmq_message_id:
rabbitmq_message_timestamp:    1970-01-01 00:00:00
rabbitmq_message_redelivered:  0
rabbitmq_message_delivery_tag: 0
rabbitmq_channel_id:

Row 3:
──────
table_engine:                  Kafka
event_date:                    2025-05-01
event_time:                    2025-05-01 10:34:53
event_time_microseconds:       2025-05-01 10:34:53.911092
database:                      default
table:                         kafka
error:                         Cannot parse input: expected '\t' before: 'zxcvbnm': (at row 1)
:
Row 1:
Column 0,   name: key,   type: UInt64, ERROR: text "zxcvbnm" is not like UInt64
raw_message:                   zxcvbnm
kafka_topic_name:              TSV_dead_letter_queue_err_1746095689
kafka_partition:               0
kafka_offset:                  0
kafka_key:
rabbitmq_exchange_name:
rabbitmq_message_id:
rabbitmq_message_timestamp:    1970-01-01 00:00:00
rabbitmq_message_redelivered:  0
rabbitmq_message_delivery_tag: 0
rabbitmq_channel_id:
 (test.py:78, dead_letter_queue_test)

See Also

Kafka - Kafka Engine
system.kafka_consumers — Description of the kafka_consumers system table which contains information like statistics and errors about Kafka consumers.