Simple example flow for extracting JSON data using a landing table with a Materialized View
Question
How do I work with JSON message using a source or landing table to extract with a Materialized View?
How do I work with JSON without the experimental JSON Object?
Answer
A common pattern to work with JSON data is to send the data to a landing table and use JSONExtract functions to pull the data onto a new table using a Materialized View trigger. This is normally done in the following flow and pattern:
The landing table should have a raw string field where you would store the raw json. It should also have one to two other fields that can be used for management of that table so that it could be partitioned and trimmed as the data ages.
*some integrations can add fields to the original data for example if using the ClickHouse Kafka Connector Sink.
Simplified example below:
- create the example database
- create a landing table where your raw json will be inserted:
- create the base table for the materialized view
- create the materialized view to the base table
- insert some sample rows
- view the results from the extraction and the materialized view that would be used in the queries
Additional Reference links:
Materialized Views: https://clickhouse.com/docs/en/guides/developer/cascading-materialized-views
Working with JSON: https://clickhouse.com/docs/en/integrations/data-formats/json#other-approaches
JSON functions: https://clickhouse.com/docs/en/sql-reference/functions/json-functions