Transform data with Materialized Views.
Illustration of data flowing from a source Data Pool, through a Materialized View, and into a destination Data Pool.
Diagram of (1) an insert to a source Data Pool, (2) the insert triggering a Materialized View, (3) the Materialized View executing its SQL query on the newly inserted data, and (4) the Materialized View's query results being written to the destination Data Pool.
events
with two columns:
_propel_received_at
(TIMESTAMP)_propel_payload
(JSON)_propel_received_at
and _propel_payload
columns. Then click on the Data Pool, click on “Schema” tab and paste the event below to create sample records.
The JSON events in the _propel_payload
column are of the form:
parseDateTimeBestEffort
function to parse the timestamp from a string to ClickHouse timestamp.
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | created_at |
TacoOrders
with the following schema:
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | orderDate |
stores
with two columns,
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | created_at |
taco_count
times the price
column.
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | created_at |
customer_id
and month
. This Materialized View uses the SummingMergeTree table engine to incrementally aggregate rows as they are written. To learn more, read our guide on How to select a table engine and sorting key.
Destination Data Pool | |
---|---|
Table Engine | SummingMergeTree |
Sorting Key | month |
checkout_time
column instead of the _propel_received_at
column of the source Data Pool.
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | checkout_time |
Destination Data Pool | |
---|---|
Table Engine | MergeTree |
Sorting Key | created_at |
Destination Data Pool | |
---|---|
Table Engine | ReplacingMergeTree |
Sorting Key | created_at , order_ids |
What is the difference between materialized views and views?
Do Materialized Views transform data in real-time or on a schedule?
How much do Materialized Views cost?
What happens if I delete a Materialized View?
Can a Materialized View be modified?
What happens if I update or delete data in the source Data Pool with the update or delete API?