Creating MView on Kafka Topic

27 views
Skip to first unread message

Sateesh Kommineni

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

  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 
SELECT 
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;

┌─message───────────────────────────────────────────────
────────────────────────────────────────────┐
│ {"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.

Thanks
Sateesh

Amit Sharma

unread,
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 clickhouse+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clickhouse/ac6b571e-9426-47ce-b2e1-003d72df947dn%40googlegroups.com.


--
Regards,
Amit Sharma
 

Sateesh Kommineni

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

 Thanks for your response. That resolved the issue.

Thanks
Sateesh 
Reply all
Reply to author
Forward
0 new messages