Creating MView on Kafka Topic

Skip to first unread message

Sateesh Kommineni

Mar 30, 2021, 1:19:32 PMMar 30
to ClickHouse

  I am trying to create a table and Materialized View on a Kafka Topic. The Data in the Kafka Topic is in JSON Format and it is not a flat structure so i am reading it as "JsonString"

And when i try to create an MView and query the MView it is complaining that the columns are not there in the underlying table.

Here is what i am using to create the Table and the Mview.

CREATE TABLE IF NOT EXISTS test.topic_tab (message String ) ENGINE = Kafka SETTINGS kafka_broker_list = '',
kafka_format = 'JSONAsString';

And this is how i am creating the Materialized View.

CREATE MATERIALIZED VIEW test.my_mv TO test.topic_tab AS 
JSONExtractString(message, 'uuid') AS uuid,
JSONExtractString(message, 'fileName', 'String') AS fileName,
JSONExtractBool(message, 'dataFlag','UInt8') AS dataFlag,
JSONExtract(message, 'createdDate', 'Date') AS createdDate,
JSONExtract(message, 'messageDate', 'Date') AS messageDate,
JSONExtract(message, 'expirationDate', 'Date') AS expirationDate FROM  test.topic_tab ;

And when i run the Query to select the data from MView i am getting the Error:

Code: 10. DB::Exception: Received from localhost:9000. DB::Exception: Column `uuid` not found in table test.topic_tab

When i select the data from "test.topic_tab" this is what i am getting

SELECT * from test.topic_tab limit 5;

│ {"uuid":"97e8e87b-56f4-4319-8009-57cf346665ca","parentUUID":null,"fileName":"97e8e87b-56f4-4319-8009-57cf346665ca.xml","dataFlag":false,"createdDate":1616541591000,"messageDate":1616358353000,"expirationDate":null,"timestamp":1616358353000}

Not sure why it cannot find the properties in "message" ( inferred using JsonExtract) while running the query against MView.


Amit Sharma

Mar 30, 2021, 6:23:02 PMMar 30
to Sateesh Kommineni, ClickHouse
You should create another table as a target for the materialized view. Looks like you have used test.topic_tab which is the source table. 

CREATE MATERIALIZED VIEW test.my_mv TO test.topic_tab AS 

You received this message because you are subscribed to the Google Groups "ClickHouse" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Amit Sharma

Sateesh Kommineni

Mar 31, 2021, 8:44:00 AMMar 31
to ClickHouse
Hi Amit,

 Thanks for your response. That resolved the issue.

Reply all
Reply to author
0 new messages