ClickHouse JSON 解析异常:无法解析输入:预期的“，"之前 [英] ClickHouse JSON parse exception: Cannot parse input: expected ',' before

问题描述

我正在尝试将 JSON 数据从 Kafka 添加到 ClickHouse.这是简化的 JSON:

I'm trying to add JSON data to ClickHouse from Kafka. Here's simplified JSON:

{
  ...
   "sendAddress":{
      "sendCommChannelTypeId":4,
      "sendCommChannelTypeCode":"SMS",
      "sendAddress":"789345345945"},
   ...
}

这里是在ClickHouse中创建表的步骤，使用Kafka Engine创建另一个表并创建MATERIALIZED VIEW来连接这两个表，并将CH与Kafka连接.

Here's the steps for creating table in ClickHouse, create another table using Kafka Engine and creating MATERIALIZED VIEW to connect these two tables, and also connect CH with Kafka.

创建第一个表

CREATE TABLE tab 
(
    ...

    sendAddress Tuple (sendCommChannelTypeId Int32, sendCommChannelTypeCode String, sendAddress String),
     ...

)Engine = MergeTree()
PARTITION BY applicationId
ORDER BY (applicationId);

使用 Kafka 引擎设置创建第二个表:

Creating a second table with Kafka Engine SETTINGS:

CREATE TABLE tab_kfk
(
    ...
    sendAddress Tuple (sendCommChannelTypeId Int32, sendCommChannelTypeCode String, sendAddress String),
    ...
)ENGINE = Kafka
SETTINGS kafka_broker_list = 'localhost:9092',
       kafka_topic_list = 'topk2',
       kafka_group_name = 'group1',
       kafka_format = 'JSONEachRow',
       kafka_row_delimiter = '\n';

创建物化视图

CREATE MATERIALIZED VIEW tab_mv TO tab AS
SELECT ... sendAddress, ...
FROM tab_kfk;

然后我尝试从第一个表 - 选项卡中选择所有或特定项目，但一无​​所获.日志遵循

Then I try to SELECT all or specific items from the first table - tab and get nothing. Logs is following

好的.只需在 sendAddress 中的花括号前添加[]"，如下所示:

OK. Just add '[]' before curly braces in the sendAddress like this:

"authkey":"some_value",
   "sendAddress":[{
      "sendCommChannelTypeId":4,
      "sendCommChannelTypeCode":"SMS",
      "sendAddress":"789345345945"
   }]

我仍然犯了一个错误，但略有不同:我该怎么做才能解决这个问题，谢谢！

And I still get a mistake, but slightly different: What should I do to fix this problem, thanks!

推荐答案

有 3 种方法可以解决:

There are 3 ways to fix it:

1. 在插入到 Kafka 主题之前不要使用嵌套对象和扁平化消息.例如这样的方式:

{
    ..
    "authkey":"key",
    "sendAddress_CommChannelTypeId":4,
    "sendAddress_CommChannelTypeCode":"SMS",
    "sendAddress":"789345345945",
    ..
}

1. 使用嵌套数据结构 需要更改 JSON 消息架构和表架构:

1. Use Nested data structure that required to change the JSON-message schema and table schema:

{
    ..
    "authkey":"key",
    "sendAddress.sendCommChannelTypeId":[4],
    "sendAddress.sendCommChannelTypeCode":["SMS"],
    "sendAddress.sendAddress":["789345345945"],
    ..
}

CREATE TABLE tab_kfk
(
    applicationId Int32,
    ..
    sendAddress Nested(
        sendCommChannelTypeId Int32,
        sendCommChannelTypeCode String,
        sendAddress String),
    ..
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'localhost:9092',
       kafka_topic_list = 'topk2',
       kafka_group_name = 'group1',
       kafka_format = 'JSONEachRow',
       kafka_row_delimiter = '\n',
       input_format_import_nested_json = 1 /* <--- */

考虑设置 input_format_import_nested_json.

1. 将输入的 JSON 消息解释为字符串 &手动解析它(参见 github 问题 #16969):

CREATE TABLE tab_kfk
(
    message String
)
ENGINE = Kafka
SETTINGS 
    ..
    kafka_format = 'JSONAsString', /* <--- */
    ..

CREATE MATERIALIZED VIEW tab_mv TO tab 
AS
SELECT 
    ..
    JSONExtractString(message, 'authkey') AS authkey,
    JSONExtract(message, 'sendAddress', 'Tuple(Int32,String,String)') AS sendAddress,
    ..
FROM tab_kfk;

这篇关于ClickHouse JSON 解析异常:无法解析输入:预期的“，"之前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！