ClickHouse cannot parse json message's timestamp filed from Kafka

Question

I have table in ClickHouse configured to read messages in JSON format from Kafka but there is error with parsing time field, when I try to read table:

SELECT *
FROM mydb.kafka

Error TCPHandler: Code: 27. DB::Exception: Cannot parse input: expected '"' before: '.753844305Z"}': (while reading the value of key created_at): while parsing Kafka message (topic: mytopic, partition: 0, offset: 0)': While executing SourceFromInputStream. (CANNOT_PARSE_INPUT_ASSERTION_FAILED)

JSON message has this field "created_at":"2021-10-17T14:33:19.753844305Z"

How I created table:

CREATE TABLE IF NOT EXISTS mydb.kafka　(　 id bigint,　 name String,　 created_at DateTime　) ENGINE = Kafka()　SETTINGS　 kafka_broker_list = 'localhost:9094',　 kafka_topic_list = 'mytopic',　 kafka_group_name = 'sample_group',　 kafka_format = 'JSONEachRow';

Denny Crane Denny Crane · Accepted Answer · 2021-10-17T15:56:39

There are two types:

DateTime 2021-10-17T14:33:19 (32 bits)
DateTime64(n) 2021-10-17T14:33:19.753 (n=3) (64 bits)

But anyway, you have to enable date_time_input_format=best_effort because default formatting for DateTime is 2021-10-17 14:33:19

$ cat /etc/clickhouse-server/users.d/date_time_input_format.xml
<?xml version="1.0"?>
<yandex>
   <profiles>
       <default>
           <date_time_input_format>best_effort</date_time_input_format>
       </default>
   </profiles>
</yandex>

ClickHouse cannot parse json message's timestamp filed from Kafka

1 Answers