3

this error messge:

SELECT *
FROM queue2
Received exception from server (version 20.9.3):
Code: 26. DB::Exception: Received from xxxx:xxxx. DB::Exception: Cannot parse JSON string: expected opening quote: (while read the value of key data): (at row 1)
: While executing SourceFromInputStream.

I had created a kafka table to get data from kafka topic , and this is data structure:

{
  "deviceId": 0,
  "data": [
    {
      "port_id": "2137",
      "time":1617103821,
      "in":200.5496688741722,
      "out":319.4801324503311
    }
  ]
}

this is kafka table :

CREATE TABLE queue 
(
    deviceId Int8,
    data Nested(
    port_id String,
    time Int64,
    in Float32,
    out Float32)
) 
ENGINE = Kafka 
SETTINGS kafka_broker_list = 'xxx,xxx,xxx',
    kafka_topic_list = 'librenms_flow',
    kafka_group_name = 'niop-billing-test-group',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 1;

What should I do about create kafka table and MATERIALIZED VIEW ?

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
Yanpeng
  • 89
  • 2
  • 4
  • The error suggests some record in your topic cannot be parsed, so did you look at all the records in the topic? – OneCricketeer Mar 30 '21 at 13:47
  • 4
    It's not JSONEachRow. JSONEachRow is special subset of JSON. Your JSON is impossible to parse using JSONEachRow. You can use JSONAsString Format and JSONExtract funcitons. – Denny Crane Mar 30 '21 at 21:44
  • 2
    https://stackoverflow.com/a/65105292/11644308 – Denny Crane Mar 30 '21 at 21:45
  • the records in the topic: { "deviceId": 0, "data": [ { "port_id": "2137", "time":1617103821, "in":200.5496688741722, "out":319.4801324503311 } ] } – Yanpeng Mar 31 '21 at 02:16
  • @vladimir Could you mind help me? thx – Yanpeng Mar 31 '21 at 02:22
  • But there is no JSONExtract funcitons :https://clickhouse.tech/docs/en/interfaces/formats/#jsoneachrow – Yanpeng Mar 31 '21 at 03:03
  • You're on the formats page, not functions https://clickhouse.tech/docs/en/sql-reference/functions/json-functions/#jsonextractjson-indices-or-keys-return-type – OneCricketeer Mar 31 '21 at 14:24

0 Answers0