Data type for null and string with \ in Clickhouse

Question

{"DEVICE_GROUP":null,"DEVICE":"10.84.130.44","RULE":"check-in-traffic",
"TOPIC1":"interface.statistics","FIELDS_HIGH_THRESHOLD":800000000,
"FIELDS_LOW_THRESHOLD":500000000,"FIELDS_OUT_OCTETS_STATS_VALUE":null,
"FIELDS_TANDINGESTTIMESTAMP":1598127990844870700,
"FIELDS_TANDTIMEOFFSET":"719.508643ms","KEYS_INTERFACE_NAME":"em3",
"KEYS_PLAYBOOK_NAME":"interface-kpis-playbook",
"KEYS_INSTANCE_ID":"[\"i1\"]"

Above is the Json I am getting from Kafka. I am able to create table using most of the keys just want to know what data type should I provide for KEYS_INSTANCE_ID to create table in Clickhouse using MergerTree and Kafka engine using Materialized view. I tried string but didn't worked for me for creating the table.

#to create table using mergetree engine:

CREATE TABLE IF NOT EXISTS readings_hb_trial_11
(
  KEYS_INSTANCE_ID  String
)
ENGINE = MergeTree
ORDER BY KEYS_INSTANCE_ID

#to create table using kafka engine:

CREATE TABLE IF NOT EXISTS readings_queue_hb_trial_11
(
  KEYS_INSTANCE_ID String
)
ENGINE = Kafka
SETTINGS kafka_broker_list = '10########2', kafka_topic_list = 'R########B', kafka_group_name = 'readings_consumer_group3', kafka_format = 'JSONEachRow', kafka_max_block_size = 1048576

#to materialize the table:

CREATE MATERIALIZED VIEW readings_queue_mv_hb_trial_11 TO readings_hb_trial_11 AS
SELECT
  KEYS_INSTANCE_ID
FROM readings_queue_hb_trial_11

is is correct that array type braced double quotes or you misspelled (look at "[\"i1\"]")? it violates json-spec. — vladimir
"didn't worked for me for creating the table" - did you get any exception? could you provide it? — vladimir

vladimir vladimir · Accepted Answer · 2020-08-23T05:41:31

I suspect you made mistake in JSON when braced an array to double quotes so the correct one should look as "KEYS_INSTANCE_ID":["i1"].

In this case, the type Array(String) should help.

Let's test it:

/* Emulate the table with Kafka-engine */
CREATE TABLE readings_queue_hb_trial_11
(
    `KEYS_INSTANCE_ID` Array(String)
)
ENGINE = Memory

/* MV takes just the first item from an array (as I understood it is your case). */
CREATE MATERIALIZED VIEW readings_queue_mv_hb_trial_11 TO readings_hb_trial_11 AS
SELECT
  empty(KEYS_INSTANCE_ID) ? '' : KEYS_INSTANCE_ID[1] AS KEYS_INSTANCE_ID
FROM readings_queue_hb_trial_11

Emulate processing some messages:

INSERT INTO readings_queue_hb_trial_11
SELECT JSONExtractArrayRaw('{"KEYS_INSTANCE_ID":["i1"]}', 'KEYS_INSTANCE_ID')
UNION ALL
SELECT JSONExtractArrayRaw('{"KEYS_INSTANCE_ID":[]}', 'KEYS_INSTANCE_ID')
UNION ALL
SELECT JSONExtractArrayRaw('{"KEYS_INSTANCE_ID":["i1", "i2"]}', 'KEYS_INSTANCE_ID')

The result of processing:

SELECT *
FROM readings_hb_trial_11

┌─KEYS_INSTANCE_ID─┐
│ "i1"             │
│                  │
│ "i1"             │
└──────────────────┘

Data type for null and string with \ in Clickhouse

1 Answers