Creating MView on Kafka Topic

[Sateesh Kommineni]

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]

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]

Hi Amit,

 Thanks for your response. That resolved the issue.

Thanks

Sateesh